MySQL replace text

From time to time there is a need to replace some text inside database, there is no problem when its inside only one table, you can do something like this (from inside mysql cli):

UPDATE `table_name` SET `field_name` = replace(field_name, 'old_text', 'new_text');

But problem occurse when the text is spread onto multiply tables. But there is a clever trick for this, we can dump database to file, replace text and then import edited database:

mysqldump -u user -p databasename > ./db.sql
sed -i 's/old_text/new_text/g' ./db.sql
mysql -u user -p databasename < ./db.sql

Leave a Reply

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