How can I replace a string in a MySQL database for all tables in all fields in all rows?

Question

I have a Moodle installation that I migrated to another server and I need to change several references to the old domain.

How can I replace a string for another string in MySQL for a given database searching all tables, all fields, and all rows?

I don't need to change the field names, just the values.

---

Related: How can I use mySQL replace() to replace strings in multiple records?

But the marked as answer solution implies I strongly type the table name and I need to fire this into an entire database, not manually work on each table running the script N times.

Solution 1

I would consider querying INFORMATION_SCHEMA.COLUMNS and dynamically searching the columns and tables. Try something like creating a cursor of all the columns and tables in the db:

DECLARE col_names CURSOR FOR
SELECT column_name, table_name
FROM INFORMATION_SCHEMA.COLUMNS

Then iterate through each of the columns in each of the tables and run dynamic/prepared sql to update where the string exists.

Here are a couple of good posts to get you in the right direction:

https://stackoverflow.com/a/4951354/1073631

https://stackoverflow.com/a/5728155/1073631

Solution 2

This may seem a bit ... ugly. But maybe simply dump the database to a sql/txt file, replace all strings and recreate the database using the modified dump.

Solution 3

You could run the following code to create all the udpate statements you would need to run to do your updates. It would update every field in every table within your database. You would need to run this code, and copy the results and run them.

WARNING - Be sure to test this in a test environment. You don't want any unpleasant surprises. Modify as needed.

SELECT concat('UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME, ' = REPLACE(', COLUMN_NAME, ', ''STRING_TO_REPLACE'', ''REPLACE_STRING'')')
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
      AND DATA_TYPE IN ('char', 'varchar')
;

I limited this to only char and varchar fields. You may need to add additional data types.

Comments

[sergserg]

I have that as last resort, doing a mysqldump and import will take several hours as the database is about 170GB big.

[sergserg]

Do you know what command on Linux I should look into for replacing text inside a .sql file directly from terminal?

[Dariusz]

What makes you think that doing that using built-in functions will be faster? Full text search is what it is whatever the tool used. I daresay using linux sed on text file will be faster than any built-in tool. //edit: you posted after I wrote - 'sed' is what you are looking for

[sergserg]

sed 's/old/new/g' input.txt' backup.sql - Would something like this work? The documentation shows this example with the s and g in the front and back of the new and old strings, but doesn´t mention what they do?