Mysql: Renaming an InnoDB table without updating foreign key references to it?

I am trying to replace an InnoDB table with a new table, and I want all foreign key references that point to the old table to point to the new table.

So I tried this:

SET foreign_key_checks = 0;
ALTER TABLE foo RENAME foo_old;
ALTER TABLE foo_new RENAME foo;

Unfortunately, even with foreign_key_checks disabled, all references pointing to foo are changed to point to foo_old. Now I am looking for either

• a way to change the foreign key references back without rebuilding the entire table, OR
• a way to rename a table without updating foreign key references.

I tried dropping the foreign keys and recreating them, but since the tables are huge, it takes hours. The whole point of replacing the table was to make a schema change with limited downtime.

 Answers

Old questions, but following is a possible way around. Basically move the data rather than renaming the tables. You need to of course make sure the new data adhere to the foreign key rules.

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS foo_old LIKE foo;
INSERT INTO foo_old SELECT * FROM foo;
TRUNCATE foo;
INSERT INTO foo SELECT * FROM foo_new;

Make sure you run it as one query so the foreign_key_checks applies to the whole thing. Hope this helps.

---

On MySQL 5.6 with innodb_file_per_table=ON allows you to swap the table spaces on the fly. This can't be done completely using SQL as file operations need to be performed separately. First prepare the foo_new table to be copied and drop the foo data:

SET foreign_key_checks = 0;
ALTER TABLE foo DISCARD TABLESPACE;
FLUSH TABLES foo_new FOR EXPORT;

At this point you need to copy the relevant InnoDB files to correct name. Files are stored in your data directory. On Debian, for example, they are by default in /var/lib/mysql/yourdatabase and files are foo_new.ibd, foo_new.cfg and foo_new.frm. Copy them to foo.ibd, foo.cfg and foo.frm, respectively. For example:

$ cp foo_new.ibd foo.ibd
$ cp foo_new.frm foo.frm
$ cp foo_new.cfg foo.cfg

Pay attention that MySQL has access to the new files (e.g. they have correct owner, access rights). Once done you can import the table again and enable foreign keys:

UNLOCK TABLES;
ALTER TABLE foo IMPORT TABLESPACE;
SET foreign_key_checks = 1;

This only copies foo_new to foo. Repeat the steps if you need to copy foo to foo_old.

---

I found a way around this... you just drop the source table instead of renaming it.

For this example, we'll call the table 'mytbl'.

1. create copy of the source table, e.g. 'mytbl_new'
2. copy data into the new table
3. drop the source table 'mytbl'
4. rename 'mytbl_new' to 'mytbl'

The only downside is you can't keep a backup of your original table, but you could mysqldump it before hand. Alternatively, you could create an additional table copy if you wanted a verbatim copy of the original table.

Move table from one database to another in MySQL

How to move a table from one Database to another Database without using phpMyAdmin? It will be better if it is possible by PHP.

 Answers

ALTER TABLE .. can be used to move tables from one database to another :

alter table my_old_db.mytable rename my_new_db.mytable

Warning : as you asked, this is a move, not a copy to the new database!
But you will keep table data (and not integrity constraints if they apply in your case)

Regarding php, php is able to run sql commands so it won't be a problem (i can be more precise if you wish).

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

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.

 Answers

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.

Also, if you have stored procedures, you can copy them afterwards:

mysqldump -R old_db | mysql new_db

---

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.

---

Three options:

1. Create the new database, bring down the server, move the files from one database folder to the other, and restart the server. Note that this will only work if ALL of your tables are MyISAM.
2. Create the new database, use CREATE TABLE ... LIKE statements, and then use INSERT ... SELECT * FROM statements.
3. Use mysqldump and reload with that file.

---

I've only recently came across a very nice way to do it, works with MyISAM and InnoDB and is very fast:

RENAME TABLE old_db.table TO new_db.table;

I don't remember where I read it but credit goes to someone else not me.

---

This is what I use:

$ mysqldump -u root -p olddb >~/olddb.sql
$ mysql -u root -p
mysql> create database newdb;
mysql> use newdb
mysql> source ~/olddb.sql
mysql> drop database olddb;

---

Simplest bullet-and-fool-proof way of doing a complete rename (including dropping the old database at the end so it's a rename rather than a copy):

mysqladmin -uroot -pmypassword create newdbname
mysqldump -uroot -pmypassword --routines olddbname | mysql -uroot -pmypassword newdbname
mysqladmin -uroot -pmypassword drop olddbname

Steps:

1. Copy the lines into Notepad.
2. Replace all references to "olddbname", "newdbname", "mypassword" (+ optionally "root") with your equivalents.
3. Execute one by one on the command line (entering "y" when prompted).

---

For those who are Mac users, Sequel Pro has a Rename Database option in the Database menu. http://www.sequelpro.com/

---

You may use this shell script:

Reference: How to rename a MySQL database?

#!/bin/bash
set -e # terminate execution on command failure

mysqlconn="mysql -u root -proot"
olddb=$1
newdb=$2
$mysqlconn -e "CREATE DATABASE $newdb"
params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \
                           WHERE table_schema='$olddb'")
for name in $params; do
      $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name";
done;
$mysqlconn -e "DROP DATABASE $olddb"

It's working:

$ sh rename_database.sh oldname newname

---

Here is a batch file I wrote to automate it from the command line, but it for Windows/MS-DOS.

Syntax is rename_mysqldb database newdatabase -u [user] -p[password]

:: ***************************************************************************
:: FILE: RENAME_MYSQLDB.BAT
:: ***************************************************************************
:: DESCRIPTION
:: This is a Windows /MS-DOS batch file that automates renaming a MySQL database 
:: by using MySQLDump, MySQLAdmin, and MySQL to perform the required tasks.
:: The MySQL\bin folder needs to be in your environment path or the working directory.
::
:: WARNING: The script will delete the original database, but only if it successfully
:: created the new copy. However, read the disclaimer below before using.
::
:: DISCLAIMER
:: This script is provided without any express or implied warranties whatsoever.
:: The user must assume the risk of using the script.
::
:: You are free to use, modify, and distribute this script without exception.
:: ***************************************************************************

:INITIALIZE
@ECHO OFF
IF [%2]==[] GOTO HELP
IF [%3]==[] (SET RDB_ARGS=--user=root) ELSE (SET RDB_ARGS=%3 %4 %5 %6 %7 %8 %9)
SET RDB_OLDDB=%1
SET RDB_NEWDB=%2
SET RDB_DUMPFILE=%RDB_OLDDB%_dump.sql
GOTO START

:START
SET RDB_STEP=1
ECHO Dumping "%RDB_OLDDB%"...
mysqldump %RDB_ARGS% %RDB_OLDDB% > %RDB_DUMPFILE%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=2
ECHO Creating database "%RDB_NEWDB%"...
mysqladmin %RDB_ARGS% create %RDB_NEWDB%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=3
ECHO Loading dump into "%RDB_NEWDB%"...
mysql %RDB_ARGS% %RDB_NEWDB% < %RDB_DUMPFILE%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=4
ECHO Dropping database "%RDB_OLDDB%"...
mysqladmin %RDB_ARGS% drop %RDB_OLDDB% --force
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=5
ECHO Deleting dump...
DEL %RDB_DUMPFILE%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
ECHO Renamed database "%RDB_OLDDB%" to "%RDB_NEWDB%".
GOTO END

:ERROR_ABORT
IF %RDB_STEP% GEQ 3 mysqladmin %RDB_ARGS% drop %NEWDB% --force
IF %RDB_STEP% GEQ 1 IF EXIST %RDB_DUMPFILE% DEL %RDB_DUMPFILE%
ECHO Unable to rename database "%RDB_OLDDB%" to "%RDB_NEWDB%".
GOTO END

:HELP
ECHO Renames a MySQL database.
ECHO Usage: %0 database new_database [OPTIONS]
ECHO Options: Any valid options shared by MySQL, MySQLAdmin and MySQLDump.
ECHO          --user=root is used if no options are specified.
GOTO END    

:END
SET RDB_OLDDB=
SET RDB_NEWDB=
SET RDB_ARGS=
SET RDB_DUMP=
SET RDB_STEP=

---

I potsed a question on Server Fault trying to get around downtime when restoring very large databases by using MySQL Proxy. I didn't have any success, but I realized in the end what I wanted was RENAME DATABASE functionality because dump/import wasn't an option due to the size of our database.

There is a RENAME TABLE functionality built in to MySQL so I ended up writing a simple Python script to do the job for me. I've posted it on GitHub in case it could be of use to others.

---

The simplest method is to use HeidiSQL software. It's free and open source. It runs on Windows and on any Linux with Wine (run Windows applications on Linux, BSD, Solaris and Mac OS X).

To download HeidiSQL, goto http://www.heidisql.com/download.php.

To download Wine, goto http://www.winehq.org/.

To rename a database in HeidiSQL, just right click on the database name and select 'Edit'. Then enter a new name and press 'OK'.

It is so simple.

---

Here is a quick way to generate renaming sql script, if you have many tables to move.

SELECT DISTINCT CONCAT('RENAME TABLE ', t.table_schema,'.', t.table_name, ' TO ',     
t.table_schema, "_archive", '.', t.table_name, ';' ) as Rename_SQL 
FROM information_schema.tables t
WHERE table_schema='your_db_name' ;

---

If you are using phpMyAdmin you can go to the "operations" tab once you have selected the database you want to rename. Then go to the last section "copy database to" (or something like that), give a name, and select the options below. In this case, I guess you must select "structure and data" and "create database before copying" checkboxes and, finally, press the "go" button in that section.

By the way, I'm using phpMyAdmin in Spanish so I'm not sure what the names of the sections are in English.

---

Seems noone mentioned this but here is another way:

create database NewDatabaseName like OldDatabaseName;

then for each table do:

create NewDatabaseName.tablename like OldDatabaseName.tablename;
insert into NewDataBaseName.tablename select * from OldDatabaseName.tablename;

then, if you want to,

drop database OldDatabaseName;

This approach would have the advantage of doing the entire transfer on server with near zero network traffic, so it will go a lot faster than a dump/restore.

If you do have stored procedures/views/etc you might want to transfer them as well.

---

In MySQL Administrator do the following:

1. Under Catalogs, create a new database schema.
2. Go to Backup and create a backup of the old schema.
3. Execute backup.
4. Go to Restore and open the file created in step 3.
5. Select 'Another Schema' under Target Schema and select the new database schema.
6. Start Restore.
7. Verify new schema and, if it looks good, delete the old one.

Rename or change name of MySQL table

If you change your mind and want to rename an existing MySQL table, with or without data in it, it is no problem. One simple command will change the table’s name.

To change the name of an existing table first to second, use this command as a user with adequate privileges:

RENAME TABLE first TO second;

It is good DBA manners to make sure that no one and no program are using this table before making the name change.

Rename multiple tables in MySQL

Some time back I posted how to rename a table in MySQL, either using a SQL query or using phpMyAdmin. In this post I will show how to rename multiple MySQL tables from a single query, including how you would swap two table names. Note that it is not generally advised to do this sort of thing on busy production servers.
As a recap, to rename a table from e.g. "products" to "products_old" run this SQL query:

RENAME TABLE products TO products_old

To rename more than one table at once, simply comma separate the tables to be renamed as in the following example, where we rename the example tables "products" and "categories" to "products_old" and "categories_old":

RENAME TABLE
  products TO products_old,
  categories TO categories_old

If you wanted to switch the table names of two tables you can also do this in a single query. The renaming is done from left to right in sequence, so you'd rename the first table to a temporary name, the second table to the first table's name and then the temporary table to the original name. Obviously it's not going to work if the temporary name is already being used.
The following example demonstrates this, where we rename "products" to "products_bak", and the existing "products_bak" to "products":

RENAME TABLE
  products TO products_tmp,
  products_bak TO products,
  products_tmp TO products_bak

Note as I mentioned at the top of this post it's not something you'd want to do on a busy production website, unless you take the site down temporarily first, as per the following note from the MySQL documentation: "The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running".

Related posts:

• Insert multiple records into MySQL with a single query
• Run a single MySQL query from the command line
• Optimize a table in MySQL from the command line interface
• Renaming a table in MySQL
• Cross Table Delete with MySQL
• Cross Table Update with MySQL

Renaming a table in MySQL

Sometimes you need to rename a table in MySQL. You either run an SQL query to do this or do it with phpMyAdmin if you don't want to bother remembering the SQL to do so. This post looks at how to rename a table  in MySQL either with a SQL query or using phpMyAdmin.
The example SQL below renames the MySQL table "tablename" to "tablename_renamed":

RENAME TABLE tablename TO tablename_renamed

To rename a MySQL table with phpMyAdmin, select the database then table, and click the "Operations" tab. Then put the new name into the text box labelled "rename table to" and click the "Go" button. The relevent buttons etc are highlighted with red circles in the screenshot below.

After clicking the "Go" button the table will be renamed and the same page as shown above will be loaded again, showing the rename query that was executed.

Related posts:

• How to enable remote access to a MySQL database server
• Maximum length for MySQL TEXT field types
• How to tell which storage engine a MySQL table uses
• Rename multiple tables in MySQL
• Insert multiple records into MySQL with a single query
• Cross Table Update with MySQL