Quick and secure modification of Mysql database names

1. rename database db_name TO new_db_name

This .. This syntax is added to mysql 5.1.7 and removed from mysql 5.1.23.
Data may be lost. Or don't use it.
See: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html

2. If all tables are of the MyISAM type, you can change the folder name.
Disable mysqld
Rename the db_name directory in the data directory to new_db_name
Enable 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 then 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. 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 operations are dangerous. Please back up your database before performing the operation !!