mysql 重命名，你试过吗？

晓幕晨雪 2017-02-24 14:35

1.若库中所有表为MyISAM引擎，则可改库对应的文件夹的名字

关闭MySQL Server：

/etc/init.d/mysql stop

改目录名：

cd /data/mysql; mv old_db new_db;

开启MySQL Server

/etc/init.d/mysql start

2.重命名旧库下边的所有表

create databases new_db;rename table old_db.table1 to new_db.table1,old_db.table2 to new_db.table2,...;drop database old_db;

eg:mysql> create database old_db;
Query OK, 1 row affected (0.00 sec)

mysql> use old_db;Database changedmysql> create table a ( a int);
Query OK, 0 rows affected (0.24 sec)

mysql> insert into a select 1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> create database new_db;
Query OK, 1 row affected (0.00 sec)

mysql> rename table old_db.a to new_db.a;
Query OK, 0 rows affected (0.18 sec)

mysql> use new_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from a;+------+| a |+------+| 1 |+------+1 row in set (0.18 sec)

mysql> use old_db;Database changedmysql> select * from a;
ERROR 1146 (42S02): Table 'old_db.a' doesn't existmysql> drop database old_db;
Query OK, 0 rows affected (0.01 sec)

3.建新库，使用mysqldump工具导出老库数据并导入新库

mysqldump
 -uuser -ppass -hhost -Pport --single-transaction old_db 
>old_db.sqlmysql -uuser -ppass -hhost -Pport -e "create database 
new_db"mysql -uuser -ppass -hhost -Pport new_db < old_db.sqlmysql 
-uuser -ppass -hhost -Pport -e "drop database old_db"

其中方法2中表比较多的情况下，一个个写这些名称未免太痛苦了，可用shell脚本或者存储过程来完成

#!/bin/bashmysqlconn="
 mysql -uuser -ppass -hhost -Pport ”old_db=”old_db ”new_db=”new_db 
”$mysqlconn -e “CREATE DATABASE $new_db”params=$($mysqlconn -N -e 
“SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 
table_schema=’$old_db’”)for name in $params; do$mysqlconn -e “RENAME 
TABLE $old_db.$name to $new_db.$name”;done;$mysqlconn -e “DROP DATABASE 
$old_db”