|
对于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"
|