概述

今天主要介紹下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、測試

到這裡就完成了數據庫及表不同字符集的遷移測試。

覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~