What are the steps of database migration with different character sets in MySQL experiment

What are the migration steps of different character set databases in MySQL experiment? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Today, we will mainly introduce how mysqldump migrates databases with different codes, which is demonstrated by an experiment here.

1. Create a test database

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

two。 Simulated data

Use T1; insert into T11 values; insert into T11 values; insert into T12 values; insert into T12 values; use T2; insert into t21 values (1m Meng Hwb2'); insert into T21 values (2m Meng HWB 2'); insert into T22 values (1m Meng HWB'); insert into T22 values (2m Meng HWB 2')

3. Export database

Mysqldump-u root-p T1 > / tmp/t1_old.sql mysqldump-u root-p T2 > / tmp/t2_old.sql

4. Transcoding

If you want to convert to utf-8, write it after the t parameter. The previous character set was gb2312, written right after the-f parameter.

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. Modify the character encoding of / tmp/new_utf8.sql file

Sed's Universe CHARSETwithutf8Universe g' / tmp/t1_new_utf8.sql > / tmp/t1_new_utf8_new.sql sed'sAccording to CHARSETwithutf8CPG'/ tmp/t1_new_utf8_new.sql > / tmp/t1_new_utf8_new2.sql sed's, CHARSETwithutf8CPG'/ tmp/t2_new_utf8.sql > / tmp/t2_new_utf8_new.sql sed's pedigree New_utf8_new.sql > / tmp/t2_new_utf8_new2.sql

6. Build a new database

Create database t3 default character set utf8 collate utf8_general_ci; create database t4 default character set utf8 collate utf8_general_ci

7. Import

Use T3; source / tmp/t1_new_utf8_new2.sql; use T4; source / tmp/t2_new_utf8_new2.sql

8. test

At this point, the migration test of different character sets of database and table is completed.

The answers to the questions about the migration steps of different character set databases in the MySQL experiment are shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.