mysql 重命名_mysql 重命名数据库

方法一

mysql -s -N -uroot -p

set sql_log_bin=0;

select concat('create table ', TABLE_NAME,' like xxx.',TABLE_NAME,';') from information_schema.tables where TABLE_SCHEMA='xxx';

select concat('insert into ', TABLE_NAME,' select * from xxx.',TABLE_NAME,';') from information_schema.tables where TABLE_SCHEMA='xxx';

set sql_log_bin=1;

方法二 mysqldump

mysqldump -uuser -ppass -hhost -Pport--single-transaction old_db >old_db.sql

mysql -uuser -ppass -hhost -Pport -e "create database new_db"

mysql -uuser -ppass -hhost -Pport new_db < old_db.sql

mysql -uuser -ppass -hhost -Pport -e "drop database old_db"

方法三 rename table

create database NEWDB;

use NEWDB;

select concat('rename table DB1.',TABLE_NAME,' to DB2.' ,TABLE_NAME,';') from information_schema.tables where TABLE_SCHEMA='TEST';

mysql -uroot -p -s -N < rename_database.sql > rename.sql

修改权限

库名修改之后，记得加权限，将新库的权限加入到原用户中。