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
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;
-pand the password. If your database has no password, remove the
-u username -ppasswordpart.
Trigger in wrong schemaerror). 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
mysqldump -R old_db | mysql new_db
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.