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.tablename to bar.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;"