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

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


Published at - Aug 13, 2021

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.
  • If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:mysqldump old_db | mysql new_db
  • If you have stored procedures, you can copy them afterwards:mysqldump -R old_db | mysql new_db




About author

Harendra
Harendra Kanojiya

Hello, I am Harendra Kumar Kanojiya - Owner of this website and a Fullstack web developer. I have expertise in full-stack web development using Angular, PHP, Node JS, Python, Laravel, Codeigniter and, Other web technologies. I also love to write blogs on the latest web technology to keep me and others updated. Thank you for reading the articles.



Related Posts -

How to get the size of the MySQL database?
How to get the size of the ...

Run this query and you’ll probably get what you’re looking for...



How do I import an SQL file using the command line in MySQL?
How do I import an SQL file ...

Try: mysql -u username -p database_name < file.sql Check MyS...



Follow Us

Follow us on facebook Click Here

Facebook QR
Scan from mobile
Join our telegram channel Click Here
Telegram QR
Scan from mobile