MySQL ALTER TABLE Renaming a MySQL database
Example
There is no single command to rename a MySQL database but a simple workaround can be used to achieve this by backing up and restoring:
mysqladmin -uroot -p<password> create <new name>
mysqldump -uroot -p<password> --routines <old name> | mysql -uroot -pmypassword <new name>
mysqladmin -uroot -p<password> drop <old name>
Steps:
- Copy the lines above into a text editor.
- Replace all references to
<old name>,<new name>and<password>(+ optionallyrootto use a different user) with the relevant values. - Execute one by one on the command line (assuming the MySQL "bin" folder is in the path and entering "y" when prompted).
Alternative Steps:
Rename (move) each table from one db to the other. Do this for each table:
RENAME TABLE `<old db>`.`<name>` TO `<new db>`.`<name>`;
You can create those statements by doing something like
SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO ',
'new_db.', table_name)
FROM information_schema.TABLES
WHERE table_schema = 'old_db';
Warning. Do not attempt to do any sort of table or database by simply moving files around on the filesystem. This worked fine in the old days of just MyISAM, but in the new days of InnoDB and tablespaces, it won't work. Especially when the "Data Dictionary" is moved from the filesystem into system InnoDB tables, probably in the next major release. Moving (as opposed to just DROPping) a PARTITION of an InnoDB table requires using "transportable tablespaces". In the near future, there won't even be a file to reach for.
