Table prefix Batch Replacements in Mysql

Step 1:

Enable the “allowMultiQueries” function is you utilize any SSH database tool, such as Dbeaver, Navicat and so on. Take the Dbeaver for instance, the routine is right click you SSH connection–Edit Connection–Drive properties–allowMultiQueries (change “false” to “true”)

Step 2:

Utilize the  sql query as bellow to output the sql queries for step3;
Database Name = AB, old table prefix = wp_ , new table prefix = sky
Note: because “wp_” contains 3 characters, so the number must be 4, if the old prefix = “wind_”, the number must be 6, only by doing this can ensure only change the prefix such as “sky_links” replace “wp_links”.

SELECT
CONCAT(
'ALTER TABLE ',
table_name,
' RENAME TO sky_',
substring(table_name,4),
';'
)
FROM
information_schema. TABLES
WHERE
table_schema = 'AB'
AND table_name LIKE 'wp_%';

Step 3:

After step 2, sever sql queries will be outputted, it is necessary to modify them with a batch replacement in text editor (e.g.: sublime, VS Code, Notepad++, Atom, etc. ) before running them, examples as bellow:
Before edited:

ALTER TABLE wp_users RENAME TO sky_users;

After edited:

ALTER TABLE AB.wp_users RENAME TO AB.sky_users;

Step 4:

UPDATE sky_options SET option_name = REPLACE(option_name, 'wp_', 'sky_') WHERE option_name LIKE 'wp_%';
UPDATE sky_usermeta SET meta_key = REPLACE(meta_key, 'wp_', 'sky_') WHERE meta_key LIKE 'wp_%';
FLUSH PRIVILEGES;

Step 5:
Execute the following statement in the Server

sudo systemctl restart nginx && systemctl status nginx