MySQL multi-character set backup recovery test

Objective: To test the garbled problem of backup and recovery of single MySQL instance in multi-character set database environment

Preparatory work:

create DATABASE ' utf8_db '/*!40100 DEFAULT CHARACTER SET UTF8 */;

create DATABASE ' latin_db '/*!40100 DEFAULT CHARACTER SET latin1*/;

CREATE TABLE utf8_db. ' Utab ' (  ' id ' int ( ) NOT NULL auto_increment,   ' name ' varchar (() default NULL,  primary KEY (' id ')) engine=innodb default Charse T=utf8
CREATE TABLE latin_db. ' Ltab ' (  ' id ' int (one) not NULL auto_increment,   ' name ' varchar (() default NULL,  primary KEY (' id ')) engine=innodb default charset=latin1

#模拟utf8客户端插入数据

SELECT @ @character_set_client, @ @character_set_connection

SET character_set_client = ' UTF8 ';

SET character_set_connection= ' UTF8 ';

Insert into Utf8_db.utab (name) VALUES (' Tom '), (' Lao Zhang ');

#模拟latin1客户端插入数据

SELECT @ @character_set_client, @ @character_set_connection

SET character_set_client = latin1;

SET character_set_connection= latin1;

SELECT @ @character_set_client, @ @character_set_connection;

Insert into Latin_db.ltab (name) VALUES (' Jery '),(' Xiao Li ');

#mysqldump Export

#对于mysql实例存在不同字符集的数据库, SET options--default-character-set different values, export results are different

#使用latin1 Export

Mysqldump--default-character-set=latin1-n-B latin_db uft8_db >/tmp/latin1.sql

Export Results ltab table Chinese data is normal, Utab table Chinese is garbled

#使用utf8 Export

Mysqldump--default-character-set=utf8-n-B latin_db uft8_db >/tmp/utf8.sql

Export Results utab table Chinese data is normal, Ltab table Chinese is garbled

#使用biarny导出

Mysqldump--default-character-set=binary-n-B latin_db uft8_db >/tmp/utf8.sql

Export results utab The Chinese data in the table is normal, the Chinese data in the Ltab table is normal

#mysqldump文件导入

Because the client code for the MySQL DB instance is UTF8 (already configured in MY.CNF), the mysqldump export file generates the following hint (bold section):

DROP TABLE IF EXISTS ' Utab ';

/*!40101 SET @saved_cs_client = @ @character_set_client */;

/*!40101 SET character_set_client = UTF8 */;

CREATE TABLE ' Utab ' (

' id ' int (one) not NULL auto_increment,

' Name ' varchar (DEFAULT NULL),

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=5 DEFAULT Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

These hint do not affect the data import of the UTF8 table, but will affect the data import of the table with the character latin1 (in which case the Chinese characters in the Latin1 table will be garbled ).

Conclusion:

For MySQL instances with different character set databases, when using mysqldump export, be sure to export the database separately according to the different database character set and set the corresponding--default-character-set value. In the case of data import, for databases that do not match the default character of the MySQL instance,

Be sure to modify the character_set_client value of hint in the mysqldump file

#使用binary导出, instance-based recovery

#mysql实例备份

Innobackupex--user=root--no-lock--defaults-file=/etc/my.cnf/backup/

#mysql实例还原

Innobackupex--apply-log--defaults-file=/etc/mysql/my6006.cnf/backup/2014-08-12_13-12-30/

Innobackupex--copy-back--defaults-file=/etc/mysql/my6006.cnf/backup/2014-08-12_13-12-30/

After the instance is restored, the databases of different character sets and the clients with different encodings are not garbled.

MySQL multi-character set backup recovery test