1. RENAME DATABASE db_name to New_db_name
This one.. This syntax was added in MySQL 5.1.7, and 5.1.23 was removed.
It is said that data may be lost. It's better not to use it.
See: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
2. If all tables are MyISAM type, you can change the name of the folder
Close Mysqld
Rename the Db_name directory in the data directory to New_db_name
Open mysqld
3. Rename all Tables
Copy CodeThe code is as follows: CREATE DATABASE new_db_name;
RENAME TABLE Db_name.table1 to New_db_name.table1,
Db_name.table2 to New_db_name.table2;
DROP DATABASE db_name;
4. Mysqldump Export Data and import
Copy CodeThe code is as follows: 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"
5. Use shell scripts to rename all tables
Copy CodeThe code is as follows: #!/bin/bash
mysqlconn= "Mysql-u xxxx-pxxxx-s/var/lib/mysql/mysql.sock-h localhost"
olddb= "Db_name"
newdb= "New_db_name"
# $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"
is the optimized version of Method 3.
Note: These actions are dangerous, so please back up your database before performing the operation!!
5 ways to safely and quickly modify MySQL database names