Replace string in the all tables of DB I'm moving my website to new domain, need to replace all references of old domain to a new, in fact actually replace one string on another in all tables of MySQL DB.

Please help me build the query to do this.

Thanks.
Assuming you have access to the sed command line application, doing something like:

mysqladmin create newdb
mysqldump --routines olddb | sed 's/olddomain.com/newdomain.com/' | mysql newdb


will convert all strings with "olddomain.com" into "newdomain.com" from a dump we get from the database olddb and will import it into a newdb database, which was just created.

You may need to add extra parameters or a defaults client .my.cnf file for the connection options.

Be careful, this is an easy process but a bit dangerous, as it will change the string wherever it is found, be it in on table or column names and comments, and without checking if it is part of a longer url or not. This is why I have make it load it on a separate database so you can check that it is working as intended.

I use this every time to convert between table engines from mysql dumps:

sed 's/ENGINE=PERFORMANCE_SCHEMA/ENGINE=InnoDB/g' p_s_57.sql


Once you have seen that it is correct, just point your production database to the new place or rename your tables (the safest way to do that is again using mysqldump).

There are other options, that could involve scripting by querying information_schema.columns and using UPDATE table SET column = REPLACE(column, 'olddomain.com', 'newdomain.com');. Those could give you more control, but would require a bit of extra scripting.