How To Safely Rename a Database in MySql
by bernt & torsten
So you want to Safely Rename your MySql Database, here is a tip on how to do it. If you do not have phpmyadmin installed on your server, then below are the steps to safely rename a MySql database.
Create a new database and rename all tables in the old database to be in the new database:
CREATE database new_db_name; RENAME TABLE db_name.table1 TO new_db_name, db_name.table2 TO new_db_name; DROP database db_name;
In Linux shell, use mysqldump to back up the old database, then restore the dumped database under a new name using the MySQL utility. Finally, use then drop database command to drop the old database. This option does not work that well on a large database.
mysqldump -uxxxx -pxxxx -h xxxx db_name > db_name_dump.sql mysql -uxxxx -pxxxx -h xxxx -e "CREATE DATABASE new_db_name" mysql -uxxxx -pxxxx -h xxxx new_db_name < db_name_dump.sql mysql -uxxxx -pxxxx -h xxxx -e "DROP DATABASE db_name"
Write a simple Linux script (my favorite solution)
#!/bin/bash mysqlconn="mysql -u xxxx -pxxxx -S /var/lib/mysql/mysql.sock -h localhost" olddb=xxxx newdb=xxxx #$mysqlconn -e "CREATE DATABASE $newdb" params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='$olddb'") for name in $params; do $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name"; done; #$mysqlconn -e "DROP DATABASE $olddb"
If you found this information useful, please comment below.
Why It’s Important to Exercise When You’re Over 60
Many of us find ourselves in a pickle as the years pile up. Once reliable sidekicks, our...
A Poem: The Last Time
You never know when it will be,
The last time you ski down slopes of snow,
A Poem: Time Millionaire
When the morning wakes, still and clear,
No more alarms, no more rush....