How to rename a database in MySQL?

Send to Kindle

Today we will talk how do you can easily rename a database in MySQL.
At the MySQL version 5.1.7 was added a command to do this job named RENAME DATABASE

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

But it was removed on the MySQL version 5.1.23 because maybe it would result in data lost’s like you can see on this link of MySQL documentation

Then, lets see some ways which we can do this:

1. Using the RENAME TABLE command

As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

RENAME TABLE Syntax na Documentacao do MySQL

mysql> show tables;
+---------------------+
| Tables_in_oldSchema |
+---------------------+
| tb1                 |
| tb10                |
| tb2                 |
| tb3                 |
| tb4                 |
| tb5                 |
| tb6                 |
| tb7                 |
| tb8                 |
| tb9                 |
+---------------------+
10 rows in set (0.00 sec)

Now, let’s do a query to get our ALTER TABLE command:

mysql> SELECT CONCAT('RENAME TABLE ', GROUP_CONCAT( table_schema,'.',table_name, ' TO ','new_schema.',table_name,' '),';') as stmt FROM information_schema.TABLES WHERE table_schema LIKE 'oldSchema' GROUP BY table_schema\G
*************************** 1. row ***************************
stmt: RENAME TABLE oldSchema.tb10 TO new_schema.tb10 ,oldSchema.tb1 TO new_schema.tb1 ,oldSchema.tb9 TO new_schema.tb9 ,oldSchema.tb8 TO new_schema.tb8 ,oldSchema.tb7 TO new_schema.tb7 ,oldSchema.tb6 TO new_schema.tb6 ,oldSchema.tb5 TO new_schema.tb5 ,oldSchema.tb4 TO new_schema.tb4 ,oldSchema.tb3 TO new_schema.tb3 ,oldSchema.tb2 TO new_schema.tb2 ;
1 row in set (0.01 sec)

UPDATE:
if the output of the above query isn’t complete, that is because GROUP_CONCAT() function limit the result length, to change it run the bellow query:

SET SESSION group_concat_max_len = 4294967295;

It’s this, now we just need to run the returned statment as a new query to move all tables from the oldSchema to new one, don’t forget, first you need to create your new database after this you can run the returned statment as a new query.

2. using mysqldump

mysqldump [OPTIONS] --database oldSchema > oldSchema.sql
mysql new_schema < oldSchema.sql

Good, it’s this for today. enjoy!!!

Send to Kindle
This entry was posted in MySQL, mysqldump, Uncategorized. Bookmark the permalink.

6 Responses to How to rename a database in MySQL?

  1. mrtvt says:

    Option 1: Beautiful!! Worked like a charm. Learned something new — the GROUP_CONCAT function — that will be might handy to know. Thanks!

  2. tim says:

    This sounds like a good idea,

    My problem is that my database has ~200 tables and the output query string breaks before that.

    • marceloaltmann says:

      That is because GROUP_CONCAT() function by default limit the output to 1024 characters, run the bellow command to increase this limit:
      SET SESSION group_concat_max_len = 4294967295;

  3. ma says:

    hi, I’m new to mysql.

    is it possible to script all of the steps into a file that can be run repeatedly?

    what I mean is that generating the query and then running it automatically without any user input.

    thanks.

    • marceloaltmann says:

      Hi ma
      yes, you can do it, in bash it will be something like:

      #!/bin/bash
      OLD_SCHEMA=$1
      NEW_SCHEMA=$2
      MYSQL_USER="root"
      MYSQL_PWD="rootpwd"

      GET_QUERY="SET SESSION group_concat_max_len = 4294967295;SELECT CONCAT('RENAME TABLE ', GROUP_CONCAT( table_schema,'.',table_name, ' TO ','$NEW_SCHEMA.',table_name,' '),';') FROM information_schema.TABLES WHERE table_schema LIKE '$OLD_SCHEMA' GROUP BY table_schema;"

      QUERY=`echo $GET_QUERY | mysql -u $MYSQL_USER -p$MYSQL_PWD -N`
      COMPLETE_QUERY="CREATE DATABASE IF NOT EXISTS $NEW_SCHEMA;$QUERY"
      echo $COMPLETE_QUERY | mysql -u $MYSQL_USER -p$MYSQL_PWD

      then save it and call the script passing the old schema and new schema:

      ./rename_db.sh old_schema new_schema

      • ma says:

        thanks marceloaltmann. much appreciated.

        just wondering if it’s easy to rename multiple schemas in one go?

        what I mean is rename all schemas but …

        select distinct table_schema from information_schema.tables where table_schema not in ( ‘information_schema’
        , ‘mysql’
        , ‘innodb_memcache’
        , ‘performance_schema’);

Leave a Reply

Your email address will not be published. Required fields are marked *


− 3 = one

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>