2010-02-17

Emulating The Missing RENAME DATABASE Command in MySQL

I'll approach this topic from another direction.
I had to perform a task which required moving tables between schemas on the same MySQL server.
One way of doing this would be:
  1. Dumping the original table to disk.
  2. Creating new empty table on the target schema.
  3. Importing table from disk into the target schema.
But, a simpler way exists!
The manual entry for RENAME TABLE casually describes this feature:
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;
Which works almost instantaneously and does what it is supposed to do. This command could also be called MOVE TABLE.

Using this command, it is easy to emulate the missing RENAME DATABASE command.
  1. Create the new schema.
  2. Perform a query to generate the move commands from the source to the target schema:
    SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name,
    ' TO ','new_schema.',table_name,';')
    FROM information_schema.TABLES
    WHERE table_schema LIKE 'old_schema';
    Run those by copying them from your editor, or whatnot. If you're using SQLyog, CTRL-L switches the results tab to text mode for easy copying.
  3. Drop the old schema
Some notes:
If you're using InnoDB foreign keys, worry not. The constraints are kept across databases, and moving the tables does not affect them - no need to move the tables in any specific order.
The move command also moves the .idb files between the database directories, if you're using innodb_file_per_table.

Hope this helps.

8 comments:

  1. I have used this technique in the past and it works quite well.

    However, if you have triggers, you will need to drop them and create them on the new schema after everything is moved.

    ReplyDelete
  2. Don't forget stored procedures, functions and triggers!

    Also, you might as well make it one atomic operation, instead of separate statements:

    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 'old_schema' GROUP BY table_schema;

    ReplyDelete
  3. Two reasons we still don't have RENAME DATABASE statement:
    - there is no way to lock out an entire database. You can only lock individual tables in the server, and that is race-prone if you do a RENAME DB: while you are renaming the tables you see, someone may add new tables.
    - our data dictionary is not in a transactional storage, thus a rename becomes a huge hassle: you need to move the .frm files for tables (i.e. rename the individual tables), then move the trigger files, then change trigger definitions to point at the new names, then move stored procedures, functions and events (in other words, everything stored in mysql.* system tables).

    Now, in MySQL 5.5 we're quite close to solving problem #1. But a solution for problem #2 will cause screams, since many people like the fact that we keep tables in .frms :( and not in an internal data dictionary, like InnoDB.

    ReplyDelete
  4. Thank you for your comments!

    @Tom,@gtowey: You're right, I did ignore triggers and Stored Procedures, the reason was that the focus for me was saving time moving tables around schemas, when the tables are 10s of GBs in size. Procedures and Triggers were no problem to re-create :)
    And I like the atomic rename-all-at-once!

    @kostja: Regarding point #1 - how is that going to be performed in 5.5? A new type of lock on the database level? What is done now in case someone uses "ALTER DATABASE" to change the collation?
    And regarding point #2 - For innodb, the change would not really matter, since the table dictionary is in the ibdata file anyway. We only have things to gain in removing the frm files, like they are doing in Drizzle.

    ReplyDelete
  5. How about:
    mysql -uroot -ppassword -e "drop database DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;" | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword DB_TO_BE_CREATED

    further with 1 more pipe you may delete db as well :)

    ReplyDelete
  6. @Kedar:
    The problem with this method, which I mentioned in the start of the post is the length of the operation. If the database is several tens of GBs in size, it can take quite a while to dump & re-import it - not worthwhile just for changing the name of the schema.

    ReplyDelete
  7. In 5.5 we extended the concept of "name lock" in the server to be able to lock any kinds of names -- including database names.
    So in 5.5 we will be able to, so to speak, latch a single database name, without affecting any other database.

    ALTER DATABASE does nothing like that currently, and is race-prone. Since it does very little, the effects of the race are not observable externally.

    ReplyDelete
  8. To avoid the cut and paste job, this should work (but pipe to 'less' first as a sanity check before piping to mysql)

    mysql -e "SELECT CONCAT('RENAME TABLE'...) FROM ... WHERE ... LIKE ..." | grep -v CONCAT | mysql

    ReplyDelete