Renaming Databases

Learn the supported methods for renaming a database. Since RENAME DATABASE is not available, this guide outlines safe workarounds like dumping and reloading or moving tables.

There is no RENAME DATABASE statement. To rename a database, use one of the following procedures.

Using RENAME TABLE

Use this procedure only if your tables have neither triggers nor views or events.

To move your tables from a database named db1 to one named db2, run these statements:

CREATE DATABASE db2;
# Do this for every table in database db1
RENAME TABLE db1.t TO db2.t;
# When no table is left in database db1, optionally drop it
DROP DATABASE db1;

Privileges are neither dropped for db1, nor are they "copied" to db2. Use SHOW PRIVILEGES to see which privileges are related to db1, then apply those privileges to db2.

Renaming Databases Manually

To overcome the limitations of the previous procedure, you can do the following.

In the following steps, the source database is named PROD , and the destination database TEST.

• Full Backup and Restore: If restoring to a different server, use mariadb-backup to create a full backup, then restore it to the intended server. (This step is optional. It is not necessary if your renamed database is to reside on the same computer.)

1

Dump Logical Objects.

RENAME TABLE does not work for triggers, events, and routines. You need to dump these logical objects separately.

mariadb-dump PROD --no-data --routines --triggers --events \
> PROD_routines_triggers_events.sql

2

Generate RENAME TABLE Commands.

Run the following query to generate a script with the necessary RENAME TABLE statements. This is much faster than a full logical dump.

mysql -ss -e"SELECT CONCAT('RENAME TABLE PROD.', TABLE_NAME, ' TO TEST.', \
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PROD'" \
> PROD_rename_table.sql

3

List all Existing Objects.

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
       WHERE TABLE_SCHEMA = 'PROD';
SELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS 
       WHERE EVENT_SCHEMA = 'PROD';
SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES 
       WHERE ROUTINE_SCHEMA = 'PROD';
SELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS 
 WHERE TRIGGER_SCHEMA = 'PROD';

4

Create the new Database.

CREATE DATABASE TEST;

5

Run the rename_table Script.

mysql TEST < PROD_rename_table.sql

6

Restore Logical Objects.

After the rename script completes, restore the triggers, routines, and events into the new database.

mysql TEST < PROD_routines_triggers_events.sql

7

Verify all Objects are Restored.

Verify that all objects have been correctly moved to the new TEST database.

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
       WHERE TABLE_SCHEMA = 'TEST';
SELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS 
       WHERE EVENT_SCHEMA = 'TEST';
SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES 
       WHERE ROUTINE_SCHEMA = 'TEST';
SELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS 
       WHERE TRIGGER_SCHEMA = 'TEST';

8

Cleanup.

Once you have confirmed everything looks good, you can drop the old PROD database.

Privileges are neither dropped for PROD, nor are they "copied" to TEST. Use SHOW PRIVILEGES to see which privileges are related to PROD, then apply those privileges to TEST.