How to rename a MySQL database (change schema name)?
P粉320361201
2023-08-23 13:09:22
<p>How to quickly rename a MySQL database (change its schema name)? </p>
<p>Usually I just dump the database and re-import it with a new name. For very large databases this is not an option. Apparently <code>RENAMED {DATABASE|SCHEMA} db_name TO new_db_name;</code> does something bad, only exists in a few versions, and is overall a bad idea. </p>
<p>This needs to be used with InnoDB, which stores very different content than MyISAM. </p>
Use the following simple commands:
Or to reduce I/O, use the following as suggested by @Pablo Marin-Garcia:
For InnoDB, the following seems to work: Create a new, empty database, then rename each table in turn to the new database:
You need to adjust the permissions later.
To write scripts in the shell, you can use any of the following methods:
or
Comments:
There is no space between- option
-
-
-p
and the password. If your database does not have a password, remove the-u username -ppassword
part.If a table has triggers, it cannot be moved to another database using the above method (will result in a
Trigger Error Schema
error). If this is the case, use traditional methods to clone the database and then delete the old database:mysqldump old_db | mysql new_db
If you have stored procedures, you can then copy them:
mysqldump -R old_db | mysql new_db