MySQL多字符集备份恢复测试

目的：测试单mysql实例在多字符集的数据库环境中备份恢复的乱码问题

准备工作：

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(11) NOT NULL AUTO_INCREMENT,  `name` varchar(50) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE latin_db.`ltab` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(50) 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‘）,（‘老张‘);

#模拟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‘),(‘小李‘);

#mysqldump 导出

#对于mysql实例存在不同字符集的数据库，设置选项--default-character-set不同的值，导出的结果不同

#使用latin1 导出

mysqldump --default-character-set=latin1 -N -B latin_db uft8_db > /tmp/latin1.sql

导出结果ltab表中中文的数据正常，utab表中文是乱码

#使用utf8 导出

mysqldump --default-character-set=utf8 -N -B latin_db uft8_db > /tmp/utf8.sql

导出结果utab表中中文的数据正常，ltab表中文是乱码

#使用biarny导出

mysqldump --default-character-set=binary -N -B latin_db uft8_db > /tmp/utf8.sql

导出结果utab表中中文的数据正常，ltab表中中文的数据正常

#mysqldump文件导入

由于mysql数据库实例的client编码为utf8（已经配置在my.cnf中），所以mysqldump导出文件会生成如下的hint（加粗部分）：

DROP TABLE IF EXISTS `utab`;

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `utab` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

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

这些hint并不会影响字符为utf8表的数据导入，但是会影响字符为latin1的表的数据导入（在这种情况下，latin1表中的中文字符会变成乱码）。

结论：

对于mysql实例存在不同字符集的数据库，使用mysqldump的导出的时候，一定要根据数据库字符集的不同单独导出数据库并设置对应的--default-character-set值。在数据导入时，对于数据库与mysql实例默认字符不一致的数据库，

一定要修改mysqldump文件中hint的character_set_client值

#使用binary导出,基于实例的恢复

#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/

实例还原后，不同字符集的数据库和在各自不同编码的客户端下，都没有乱码