今天主要介绍下mysqldump怎么迁移编码不同的验之演示数据库,这里用一个实验来演示一下。
1. 创建测试数据库
create database t1 default character set gbk collate gbk_chinese_ci; create database t2 default character set utf8 collate utf8_general_ci; use t1; create table t11(id int,不同步骤name varchar(10),primary key(id)) engine=innodb default charset=utf8; create table t12(id int,name varchar(10),primary key(id)) engine=innodb default charset=gb2312; use t2; create table t21(id int,name varchar(10),primary key(id)) engine=innodb default charset=utf8; create table t22(id int,name varchar(10),primary key(id)) engine=innodb default charset=gb2312;
2. 模拟数据
use t1; insert into t11 values(1,hwb); insert into t11 values(2,hwb2); insert into t12 values(1,hwb); insert into t12 values(2,hwb2); use t2; insert into t21 values(1,hwb); insert into t21 values(2,hwb2); insert into t22 values(1,hwb); insert into t22 values(2,hwb2);
3. 导出数据库
mysqldump -u root -p t1 > /tmp/t1_old.sql mysqldump -u root -p t2 > /tmp/t2_old.sql
4. 转换编码
如果你是想转成utf-8,就在t参数后面写上。亿华云以前字符集是字符gb2312就在-f参数后面写上。
iconv -t utf-8 -f gb2312 -c /tmp/t1_old.sql > /tmp/t1_new_utf8.sql iconv -t utf-8 -f gb2312 -c /tmp/t2_old.sql > /tmp/t2_new_utf8.sql 
5. 修改 /tmp/new_utf8.sql文件的高防服务器集数据库字符编码
sed s/CHARSET=gbk/CHARSET=utf8/g /tmp/t1_new_utf8.sql > /tmp/t1_new_utf8_new.sql sed s/CHARSET=gb2312/CHARSET=utf8/g /tmp/t1_new_utf8_new.sql > /tmp/t1_new_utf8_new2.sql sed s/CHARSET=gbk/CHARSET=utf8/g /tmp/t2_new_utf8.sql > /tmp/t2_new_utf8_new.sql sed s/CHARSET=gb2312/CHARSET=utf8/g /tmp/t2_new_utf8_new.sql > /tmp/t2_new_utf8_new2.sql
6. 建新库
create database t3 default character set utf8 collate utf8_general_ci; create database t4 default character set utf8 collate utf8_general_ci; 
7. 导入
use t3; source /tmp/t1_new_utf8_new2.sql; use t4; source /tmp/t2_new_utf8_new2.sql; 8. 测试
到这里就完成了数据库及表不同字符集的迁移测试。
迁移云服务器