In one of the recent blog posts we have discussed how to rename a table in MySQL. Another common question is: how to rename a database? Let’s say we have one database with one table in it:
mysql> use sbtest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +------------------+ | Tables_in_sbtest | +------------------+ | sbtest1 | +------------------+ 1 row in set (0.00 sec)
Let’s say we want to rename the schema to ‘otherschema’. We cannot just run a RENAME DATABASE command:
mysql> RENAME DATABASE sbtest TO otherschema;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE sbtest TO otherschema' at line 1
A fun fact – back in the days of MySQL 5.1, up to MySQL 5.1.22, such a command existed. It was removed due to the fact that it, occasionally, removed some data in the process.
So, the simplest solution has been removed long ago. Let us go through options that remain available.
Use RENAME TABLE to rename a database
One solution that we can use is to use the same technique as for renaming the tables. First, we would have to create a new database:
mysql> CREATE DATABASE othershema;
Query OK, 1 row affected (0.01 sec)
Then we can use the RENAME TABLE to move the table from one database (schema) to another.
mysql> RENAME TABLE sbtest.sbtest1 TO othershema.sbtest1; Query OK, 0 rows affected (0.04 sec) mysql> SHOW TABLES FROM sbtest; Empty set (0.00 sec) mysql> SHOW TABLES FROM othershema; +----------------------+ | Tables_in_othershema | +----------------------+ | sbtest1 | +----------------------+ 1 row in set (0.00 sec)
While, technically, one may argue we haven’t renamed the database but instead we created a new one and moved the tables from old database to new, but as long as we move all of the tables and, at the end, we DROP the old, empty database, the outcome would be exactly the same.
If you would need to move more than one table, you may want to write a script that will extract the existing tables from the existing database and then build and, eventually, execute a list of RENAME TABLE commands to move the tables around. One way to list all of the tables in the given schema is to query information_schema:
SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA='sbtest';
You can execute it via shell or any programming language, parse the output and generate a list of queries to move the tables.
The solution is fast, RENAME TABLE is atomic and instant in most of the cases. We have covered the gotchas that may bite you in the previous blog.
Modify the logical backup file
While the RENAME TABLE is probably the best solution to change the database where tables are located, there are also other options available which might work as good as RENAME TABLE, or even better in some particular cases. One of such situations might be when we have to restore a logical backup. Logical backups are backups stored in a text, human-readable format. There are different types of tools that can be used to create such backup, we will focus on the one most commonly used – mysqldump. Other tools may store data in a different format and, as such, will require another approach to make the change.
Let’s say we have a database backup created using this command:
mysqldump --extended-insert --all-databases --events --routines --triggers --single-transaction --lock-for-backup > alldb.sql
If we take a look at the file, we can see that it uses following pattern:
-- -- Current Database: `sbtest` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */; USE `sbtest`; -- -- Table structure for table `sbtest1` -- DROP TABLE IF EXISTS `sbtest1`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sbtest1` --
So, the database is created, then the context is switched to the newly created database and the tool proceeds by deleting the existing tables, recreating them and then preparing a set of INSERTs to load the data into newly created tables.
It just happens that the database is defined in those two lines only – CREATE DATABASE and then, later, context switch using USE. It is enough to change the database name in those two lines and then, once the backup will be restored, all data will end up in the new database. Please keep in mind that the old database won’t be dropped so you will end up with two databases, each containing the full dataset. We can prevent this from happening by adding:
DROP DATABASE `sbtest` WHEN EXISTS;
to the last row of the backup file. Once the changes are made we can restore the backup:
root@vagrant:~# mysql -ppass < alldb.sql mysql: [Warning] Using a password on the command line interface can be insecure.
The backup has been loaded. Let’s check what do we have in the database:
root@vagrant:~# mysql -ppass mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 447782 Server version: 8.0.25-15.1 Percona XtraDB Cluster (GPL), Release rel15, Revision 8638bb0, WSREP version 26.4.3 Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show schemas; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | otherschema | | performance_schema | | sbtest | | sys | +--------------------+ 6 rows in set (0.01 sec)
As you can see, we have both ‘sbtest’ and ‘otherschema’ databases. This is because, for this time, we skipped appending the DROP DATABASE to the backup file.
mysql> SHOW TABLES FROM otherschema; +-----------------------+ | Tables_in_otherschema | +-----------------------+ | sbtest1 | | sbtest10 | | sbtest11 | | sbtest12 | | sbtest13 | | sbtest14 | | sbtest15 | | sbtest16 | | sbtest17 | | sbtest18 | | sbtest19 | | sbtest2 | | sbtest20 | | sbtest21 | | sbtest22 | | sbtest23 | | sbtest24 | | sbtest25 | | sbtest26 | | sbtest27 | | sbtest28 | | sbtest29 | | sbtest3 | | sbtest30 | | sbtest31 | | sbtest32 | | sbtest4 | | sbtest5 | | sbtest6 | | sbtest7 | | sbtest8 | | sbtest9 | +-----------------------+ 32 rows in set (0.01 sec)
As you can see, all of the tables are now available in the ‘otherschema’ database. The only step to finish the process will be to drop the old database.
As you can see, even though there is no quick and easy way of renaming the database in MySQL, there are still different options on how to accomplish that. Depending on the situation, you should be able to find a solution that will work for you. If you have your own experience with this topic, we would like to hear from you in the comments below.
