对于MySQL数据库,并未提供修改库名的SQL方法,那如何修改库名呢?处理方法如下:
1、重命名旧库下边的所有表到新库:
create databases `test2`;
RENAME TABLE `test1`.`t_user` TO `test2`.`t_user`;
使用如下SQL导出上述SQL:
SELECT CONCAT('RENAME TABLE `test1`.`',TABLE_NAME,'` TO `test2`.`',TABLE_NAME,'`;') as statement
from `information_schema`.`TABLES` where TABLE_SCHEMA='test1';
使用shell脚本如下:
#!/bin/bash
HOST='localhost'
PORT='3306'
USER='root'
PASS='123456'
OLD_DB='tang'
NEW_DB='test'
if [ -z "$PASS" ]
then
mysqlconn="mysql -h ${HOST} -P ${PORT} "
else
mysqlconn="mysql -u\"${USER}\" -p\"${PASS}\" -h ${HOST} -P ${PORT} "
fi
echo "$mysqlconn -e 'CREATE DATABASE if not exists ${NEW_DB};'"
eval "$mysqlconn -e 'CREATE DATABASE if not exists ${NEW_DB};'"
echo "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='${OLD_DB}' and TABLE_TYPE!='VIEW' "
params=$($mysqlconn -BN -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='${OLD_DB}' and TABLE_TYPE!='VIEW' ")
for name in $params; do
echo "$mysqlconn -e 'RENAME TABLE ${OLD_DB}.${name} to ${NEW_DB}.${name};'"
eval "$mysqlconn -e 'RENAME TABLE ${OLD_DB}.${name} to ${NEW_DB}.${name};'"
done;
echo "$mysqlconn -e 'DROP DATABASE ${OLD_DB};'"
eval "$mysqlconn -e 'DROP DATABASE ${OLD_DB};'"
echo "over!"
2、建新库,使用mysqldump工具导出老库数据并导入新库
mysqldump -u user -ppass -h host -P port --single-transaction old_db >old_db.sql
mysql -u user -ppass -h host -P port -e "create database new_db"
mysql -u user -ppass -h host -P port new_db < old_db.sql
mysql -u user -ppass -h host -P port -e "drop database old_db"