方法一

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”