Description\n-----------\n\nThere is no RENAME DATABASE statement. To rename a database, use one of the following procedures.\n\nUsing RENAME TABLE\n\nUse this procedure only if your tables have neither triggers nor views or events.\n\nTo move your tables from a database named db1 to one named db2, run these statements:\n\n``sql\nCREATE DATABASE db2;\nDo this for every table in database db1\nRENAME TABLE db1.t TO db2.t;\nWhen no table is left in database db1, optionally drop it\nDROP DATABASE db1;\n`\n\nPrivileges 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.\n\nRenaming Databases Manually\n\nTo overcome the limitations of the previous procedure, you can do the following.\n\nIn the following steps, the source database is named PROD , and the destination database TEST.\n\n* 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.)\n\nDump Logical Objects.\n\nRENAME TABLE does not work for triggers, events, and routines. You need to dump these logical objects separately.\n\n`bash\nmariadb-dump PROD --no-data --routines --triggers --events \\\n> PROD_routines_triggers_events.sql\n`\n\nGenerate RENAME TABLE Commands.\n\nRun the following query to generate a script with the necessary RENAME TABLE statements. This is much faster than a full logical dump.\n\n`sql\nmysql -ss -e"SELECT CONCAT('RENAME TABLE PROD.', TABLE_NAME, ' TO TEST.', \\\nTABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PROD'" \\\n> PROD_rename_table.sql\n`\n\nList all Existing Objects.\n\n`sql\nSELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \n       WHERE TABLE_SCHEMA = 'PROD';\nSELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS \n       WHERE EVENT_SCHEMA = 'PROD';\nSELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES \n       WHERE ROUTINE_SCHEMA = 'PROD';\nSELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS \n WHERE TRIGGER_SCHEMA = 'PROD';\n`\n\nCreate the new Database.\n\n`sql\nCREATE DATABASE TEST;\n`\n\nRun the rename_table Script.\n\n`bash\nmysql TEST < PROD_rename_table.sql\n`\n\nRestore Logical Objects.\n\nAfter the rename script completes, restore the triggers, routines, and events into the new database.\n\n`bash\nmysql TEST < PROD_routines_triggers_events.sql\n`\n\nVerify all Objects are Restored.\n\nVerify that all objects have been correctly moved to the new TEST database.\n\n`sql\nSELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \n       WHERE TABLE_SCHEMA = 'TEST';\nSELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS \n       WHERE EVENT_SCHEMA = 'TEST';\nSELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES \n       WHERE ROUTINE_SCHEMA = 'TEST';\nSELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS \n       WHERE TRIGGER_SCHEMA = 'TEST';\n`\n\nCleanup.\n\nOnce you have confirmed everything looks good, you can drop the old PROD database.\n\nPrivileges 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`.\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-definition/renaming-databases