I had to perform a task which required moving tables between schemas on the same MySQL server.
One way of doing this would be:
- Dumping the original table to disk.
- Creating new empty table on the target schema.
- 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 useRENAME TABLE
to move a table from one database to another:RENAME TABLEcurrent_db.tbl_name
TOother_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.
- Create the new schema.
- Perform a query to generate the move commands from the source to the target 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.SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name,
' TO ','new_schema.',table_name,';')
FROM information_schema.TABLES
WHERE table_schema LIKE 'old_schema'; - 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.
Don't forget stored procedures, functions and triggers!
ReplyDeleteAlso, 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;
Two reasons we still don't have RENAME DATABASE statement:
ReplyDelete- 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.
Thank you for your comments!
ReplyDelete@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.
How about:
ReplyDeletemysql -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 :)
@Kedar:
ReplyDeleteThe 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.
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.
ReplyDeleteSo 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.
To avoid the cut and paste job, this should work (but pipe to 'less' first as a sanity check before piping to mysql)
ReplyDeletemysql -e "SELECT CONCAT('RENAME TABLE'...) FROM ... WHERE ... LIKE ..." | grep -v CONCAT | mysql