Advertisement
Advertisement


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


Question

The MySQL manual at MySQL covers this.

Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exist only in a handful of versions, and is a bad idea overall.

This needs to work with InnoDB, which stores things very differently than MyISAM.

2018/10/15
1
977
10/15/2018 12:53:14 PM

Accepted Answer

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

2020/04/19
848
4/19/2020 1:51:43 PM

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
2015/11/09

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

2015/11/09

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).

2018/02/24

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.

2015/11/09

The simple way

Change to the database directory:

cd /var/lib/mysql/

Shut down MySQL... This is important!

/etc/init.d/mysql stop

Okay, this way doesn't work for InnoDB or BDB-Databases.

Rename database:

mv old-name new-name

...or the table...

cd database/

mv old-name.frm new-name.frm

mv old-name.MYD new-name.MYD

mv old-name.MYI new-name.MYI

Restart MySQL

/etc/init.d/mysql start

Done...

OK, this way doesn't work with InnoDB or BDB databases. In this case you have to dump the database and re-import it.

2020/06/20

Source: https://stackoverflow.com/questions/67093
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]