MariaDB - How to rename a database
Given you have a database foo with a bunch of tables and you’d want to rename that database to bar.
MariaDB has no RENAME DATABASE command.
But there is a little work-around to achieve the same:
- list all tables
- rename each single table from
foo.tablenametobar.tablename
With 2, 3 or even 10 tables, this can be done manually per table.
But your database could contain 100+ tables, then doing it manually table for table becomes tedious.
That’s the reason to write out commands to a *.sql-file first, then execute the commands therein.
Performance
The operation of moving even larger tables from one to the other database runs rather quick. Tested with a database consisting of 440 tables, 24GB data in total: Renaming took around 16s.
File-System
One could optionally also move files in e.g. /var/lib/mysql/ (on Debian servers), but that’s risky and I prefer letting MariaDB handle it’s own data files and only interact with the database daemon instead.
Rename a database
Make sure to connect to the server and/or the database with a user that has all needed permissions on both databases:
ssh root@FQDN_OF_SERVER_WITH_THE_DATABASE
Check that both databases exist, output should show foo and bar (or of course your real two database names):
mysql -e "SHOW DATABASES;"
Select all tables, then write out the needed renaming command to a *.sql file (replace foo and bar):
mysql -ss -u foo -p foo -e "SELECT CONCAT('RENAME TABLE foo.\`', TABLE_NAME, '\` TO bar.\`', TABLE_NAME, '\`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'foo' AND TABLE_TYPE <> 'VIEW'" > db_rename.sql
Check the resulting file to be plausible and what you want:
cat db_rename.sql
Create a backup or file-system snapshot (depends on the server/environment you’re running this):
snapshot-create
Now execute the actual renaming:
mysql foo < db_rename.sql
And finally, list all tables in the two databases to verify they got moved to the proper database:
mysql foo -e "SHOW TABLES;"
mysql bar -e "SHOW TABLES;"