Tag: mysql replace

To rename many mysql/mariadb tables with a same prefix, you can uso this script (in a terminal)

mysql -u root -p -N -e " SELECT CONCAT('RENAME TABLE \', table_name, '\ TO \', REPLACE(table_name, 'oldprefix_', 'newprefix_'), '\;') FROM information_schema.tables WHERE table_schema = 'your-database' AND table_name LIKE 'oldprefix_%'; " > renamed.sql 

In this way will created the file renamed.sql (same tables, with different names), that you then can use to replace your database, with

mysql -u root -p yourdatabase < renamed.sql

If you have a wordpress database you have also to check options and meta tables, removing oldprefixes, with:

UPDATE oldprefix_options
SET option_name = REPLACE(option_name, 'oldprefix_', 'newprefix_')
WHERE option_name LIKE 'oldprefix\_%';

and

UPDATE abc_usermeta
SET meta_key = REPLACE(meta_key, 'oldprefix_', 'newprefix_')
WHERE meta_key LIKE 'oldprefix\_%';

And finally, you could check if there are any oldprefix, in options with

SELECT option_name FROM newprefix_options WHERE option_name LIKE 'oldprefix\_%';

same for meta:

SELECT meta_key FROM newprefix_usermeta WHERE meta_key LIKE 'oldprefix\_%';