How do I quickly rename a MySQL database (change schema name)?

ID : 1323

viewed : 214

Tags : mysqldatabaseinnodbrenamemysql

Top 5 Answer for How do I quickly rename a MySQL database (change schema name)?

vote vote

93

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table; 

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \      do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done 

OR

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done; 

Notes:

  • There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
  • If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

    mysqldump old_db | mysql new_db

  • If you have stored procedures, you can copy them afterwards:

    mysqldump -R old_db | mysql new_db

vote vote

80

Use these few simple commands:

mysqldump -u username -p -v olddatabase > olddbdump.sql mysqladmin -u username -p create newdatabase mysql -u username -p newdatabase < olddbdump.sql 

Or to reduce I/O use the following as suggested by @Pablo Marin-Garcia:

mysqladmin -u username -p create newdatabase mysqldump -u username -v olddatabase -p | mysql -u username -p -D newdatabase 
vote vote

78

I think the solution is simpler and was suggested by some developers. phpMyAdmin has an operation for this.

From phpMyAdmin, select the database you want to select. In the tabs there's one called Operations, go to the rename section. That's all.

It does, as many suggested, create a new database with the new name, dump all tables of the old database into the new database and drop the old database.

Enter image description here

vote vote

62

You can use SQL to generate an SQL script to transfer each table in your source database to the destination database.

You must create the destination database before running the script generated from the command.

You can use either of these two scripts (I originally suggested the former and someone "improved" my answer to use GROUP_CONCAT. Take your pick, but I prefer the original):

SELECT CONCAT('RENAME TABLE $1.', table_name, ' TO $2.', table_name, '; ') FROM information_schema.TABLES  WHERE table_schema='$1'; 

or

SELECT GROUP_CONCAT('RENAME TABLE $1.', table_name, ' TO $2.', table_name SEPARATOR '; ') FROM information_schema.TABLES  WHERE table_schema='$1'; 

($1 and $2 are source and target respectively)

This will generate a SQL command that you'll have to then run.

Note that GROUP_CONCAT has a default length limit that may be exceeded for databases with a large number of tables. You can alter that limit by running SET SESSION group_concat_max_len = 100000000; (or some other large number).

vote vote

58

Emulating the missing RENAME DATABASE command in MySQL:

  1. Create a new database

  2. Create the rename queries with:

     SELECT CONCAT('RENAME TABLE ',table_schema,'.`',table_name,      '` TO ','new_schema.`',table_name,'`;')  FROM information_schema.TABLES  WHERE table_schema LIKE 'old_schema'; 
  3. Run that output

  4. Delete old database

It was taken from Emulating The Missing RENAME DATABASE Command in MySQL.

Top 3 video Explaining How do I quickly rename a MySQL database (change schema name)?

Related QUESTION?