How to replace a string in your mysql database with another string.

Hi there.

It always kept pissing me off… For example… I had 150 entries in my RSS from domain website.com but every few months that domain keeps changing… either to website.eu or website.ie or website.nl… Try changing 150 urls by hand every few weeks… Its daunting and downright just freaking annoying…

Today I’ve learned HOW to change the URL’s in the entire database with few simple commands. Here is how:

First – dump the database example:

mysqldump -u root -p database_name > ./db.sql

You will be asked for root’s mysql password. Provide it.

Now use sed command to replace the string:

sed -i 's/website.com/website.ie/g' ./db.sql

Now re-upload the database back:

mysql -u root -p database_name < ./db.sql

You will be asked for root’s mysql password. Provide it.

Now what I like to do is force the mysql update:

mysql_upgrade -p -u root --force

You will be asked for root’s mysql password. Provide it.

And when that’s done – restart mysqld service.

systemctl restart mysqld

And remove the dump file:

rm ./db.sql

That’s it – the string has been replaced in the entire database and the database was re-uploaded / upgraded / restarted and the dump was removed.

Cheers.

Andrzej

AndrzejL

"Never meet Your heroes. Most of the time you'll only end up disappointed." White Polak Male Husband Employee Hetero Carnivorous Fugly Geek @$$hole with ADD Catholic “Some men just want to watch the world burn.”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.