mysql同步字符集故障处理

mysql同步字符集故障处理

      

       近期发现，我的mysql数据库主从同步停止了，在从机slave status里面发现报告如下错误：

[ERROR] Slave SQL: Column 1 of table 'DB.t_tb' cannot be converted from type 'varchar(180)' to type 'varchar(60)', Internal MariaDB error code: 1677
140912 11:50:57 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000031' position 523817727

对此问题，起初我以为是版本bug，利用skip忽略一下问题试试：

stop slave;
SET GLOBAL sql_slave_skip_counter = 1;
start slave;

但过后不久，又会出现如此问题，我就开始怀疑是我主从数据库的设置问题了，肯定是有什么不同的地方，导致的如此错误，后来想到了字符集的问题，进库查看一番：

MariaDB [(none)]> show global variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

果不其然，就是字符集的问题。 造成此问题的原因是 我的MariaDB是RPM包安装的，默认字体是latin1，而UTF8中文占用字节是不同的，从而造成以上的错误，下面是通过char_length() 和leng()不同字符集的对照表。

函数 char_length()  length()

char_length 统计字符的个数

CHAR(30)在UTF-8字符集下，最多可以写入几个英文(ASCII码)  -- 30个英文字符
CHAR(30)在UTF-8字符集下，最多可以写入几个中文字符 --- 30个中文字符
CHAR(30)，UTF-8字符集，存储了30个汉字，字节数应该是：90个字节
CHAR(30)，UTF-8字符集，存储了30个英文，字节数应该是：30个字节
CHAR(30)，GBK字符集，存储了30个汉字，字节数应该是：60个字节
CHAR(30)，GBK字符集，存储了30个英文，字节数应该是：30个字节
CHAR(30)，LATIN1字符集，存储了15个汉字，字节数应该是：30个字节
CHAR(30)，LATIN1字符集，存储了30个英文，字节数应该是：30个字节

第一次处理这种问题，心里也没跟，所以在操作之前 mysqldump了一下（建议），这样会比较稳妥。

下面是处理过程：

MariaDB [(none)]> show global variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> set global character_set_server='utf8';
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> set global character_set_database='utf8';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global character_set_connection='utf8';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global character_set_client='utf8';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global character_set_results='utf8';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

全局变量修改好了，下面进入数据库里面去修改：

MariaDB [(none)]> use member
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [member]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

下面是处理语句：

set variables character_set_server='utf8'; 

alter database DB_name default character set utf8; 

处理完毕，进数据库查看，数据一切ok，一直数据库里面没出现乱码的原因是，我在创建数据库的时候，默认使用了UTF8字体，所以一直没出现问题。

以上为动态修改，还有种方法，那就是在mysql的my.cnf里面加上：

character_set_server = utf8

default-character-set = utf8 

加入这种设置，会保证你重启mysql之后不会变回去，不然重启之后有可能还是最初默认的字符集。

对于数据库，字符集也是比较重要的一个环节，rpm安装，yum安装mysql的情况下容易被大家忽略，在此提醒大家每次搭建数据库的时候一定要关注一下。