It’s surprisingly easy to perform a find and replace on an entire MySQL database. With some command line magic and help from our good old friend sed, you can replace a string across every single table in your database.
No experience necessary. I’ll walk you though every step of the process.
MySQL Find and Replace
The easiest way to search and replace a string across all tables in a MySQL database is with mysqldump and sed. Please see the following example.
mysqldump mydatabase > db.sql sed -i 's/stringtofind/stringtoreplace/g' db.sql mysql mydatabase < db.sql
In the example above, we first dump our database named mydatabase into a file called db.sql.
Next, we use the sed command to replace stringtofind with stringtoreplace. The -i option to the sed command edits the file in place. Some implementations of sed like the Mac version don’t support the -i option. Instead, you can do something like sed 's/stringtofind/stringtoreplace/g' db.sql > tmp and then mv tmp db.sql to “manually edit in place” 🙂
Finally, we take our modified db.sql file and apply it back to the MySQL database. This will overwrite the entire database. If this scares you, don’t sweat it. You already have a copy of your database!
What About Slashes?
What if you need to find and replace a string with a slash like a URL? This might initially seem impossible since the sed command itself uses a forward slash as a delimiter to begin with.
Well, the cool thing about sed is that whatever character follows the leading s is the delimiter. In the example below, instead of a slash, we use a hashtag as our delimiter to find and replace a URL in our MySQL database.
sed -i 's#http:\/\/tonyflorida\.com#https:\/\/tonyflorida\.com#g' db.sql
Also notice how we escape the forward slashes in the URL with backward slashes. It’s not very pretty, but it gets the job done.
If you have any questions about replacing strings in your MySQL database, don’t hesitate to drop a line down below in the comments.
