解决MySQL乱码问题
解决MySQL乱码问题
五大三粗 发表于2年前
解决MySQL乱码问题
  • 发表于 2年前
  • 阅读 13
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云实验室 1小时搭建人工智能应用,让技术更容易入门 免费体验 >>>   

MySQL出现乱码的原因

要了解为什么会出现乱码,我们就先要理解:从客户端发起请求,到MySQL存储数据,再到下次从表取回客户端的过程中,哪些环节会有编码/解码的行为。为了更好的解释这个过程,博主制作了两张流程图,分别对应存入和取出两个阶段。

存入MySQL经历的编码转换过程

mysqlflow
上图中有3次编码/解码的过程(红色箭头)。三个红色箭头分别对应:客户端编码,MySQL Server解码,Client编码向表编码的转换。其中Terminal可以是一个Bash,一个web页面又或者是一个APP。本文中我们假定Bash是我们的Terminal,即用户端的输入和展示界面。图中每一个框格对应的行为如下:

  • 在terminal中使用输入法输入
  • terminal根据字符编码转换成二进制流
  • 二进制流通过MySQL客户端传输到MySQL Server
  • Server通过character-set-client解码
  • 判断character-set-client和目标表的charset是否一致
  • 如果不一致则进行一次从client-charset到table-charset的一次字符编码转换
  • 将转换后的字符编码二进制流存入文件中

从MySQL表中取出数据经历的编码转换过程

mysqlflow
上图有3次编码/解码的过程(红色箭头)。上图中三个红色箭头分别对应:客户端解码展示,MySQL Server根据character-set-client编码,表编码向character-set-client编码的转换。

  • 从文件读出二进制数据流
  • 用表字符集编码进行解码
  • 将数据转换为character-set-client的编码
  • 使用character-set-client编码为二进制流
  • Server通过网络传输到远端client
  • client通过bash配置的字符编码展示查询结果

造成MySQL乱码的原因

1. 存入和取出时对应环节的编码不一致
这个会造成乱码是显而易见的。我们把存入阶段的三次编解码使用的字符集编号为C1,C2,C3(图一从左到右);取出时的三个字符集依次编号为C1’,C2’,C3’(从左到右)。那么存入的时候bash C1用的是UTF-8编码,取出的时候,C1'我们却使用了windows终端(默认是GBK编码),那么结果几乎一定是乱码。又或者存入MySQL的时候set names utf8(C2),而取出的时候却使用了set names gbk(C2'),那么结果也必然是乱码

2. 单个流程中三步的编码不一致
即上面任意一幅图中的同方向的三步中,只要两步或者两部以上的编码有不一致就有可能出现编解码错误。如果差异的两个字符集之间无法进行无损编码转换(下文会详细介绍),那么就一定会出现乱码。例如:我们的shell是UTF8编码,MySQL的character-set-client配置成了GBK,而表结构却又是charset=utf8,那么毫无疑问的一定会出现乱码。
这里我们就简单演示下这种情况

master [localhost] {msandbox} (test) > create table charset_test_utf8 (id int primary key auto_increment, char_col varchar(50)) charset = utf8; Query OK, 0 rows affected (0.04 sec) master [localhost] {msandbox} (test) > set names gbk; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > insert into charset_test_utf8 (char_col) values ('中文'); Query OK, 1 row affected, 1 warning (0.01 sec) master [localhost] {msandbox} (test) > show warnings; +---------+------+---------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\xAD\xE6\x96\x87' for column 'char_col' at row 1 | +---------+------+---------------------------------------------------------------------------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > select id,hex(char_col),char_col from charset_test_utf8; +----+----------------+----------+ | id | hex(char_col) | char_col | +----+----------------+----------+ | 1 | E6B6933FE69E83 | ?�� | +----+----------------+----------+ 1 row in set (0.01 sec)

关于MySQL的编/解码

既然系统之间是按照二进制流进行传输的,那直接把这串二进制流直接存入表文件就好啦。为什么在存储之前还要进行两次编解码的操作呢?

  • Client to Server的编解码的原因是MySQL需要对传来的二进制流做语法和词法解析。如果不做编码解析和校验,我们甚至没法知道传来的一串二进制流是insert还是update。
  • File to Engine的编解码是为知道二进制流内的分词情况。举个简单的例子:我们想要从表里取出某个字段的前两个字符,执行了一句形如select left(col,2) from table的语句,存储引擎从文件读入该column的值是E4B8ADE69687。那么这个时候如果我们按照GBK把这个值分割成E4B8,ADE6,9687三个字,并那么返回客户端的值就应该是E4B8ADE6;如果按照UTF8分割成E4B8AD,E69687,那么就应该返回E4B8ADE69687两个字。可见,如果在从数据文件读入数据后,不进行编解码的话在存储引擎内部是无法进行字符级别的操作的。

关于错进错出

在MySQL中最常见的乱码问题的起因就是把错进错出神话。所谓的错进错出就是,客户端(web或shell)的字符编码和最终表的字符编码格式不同,但是只要保证存和取两次的字符集编码一致就仍然能够获得没有乱码的输出的这种现象。但是,错进错出并不是对于任意两种字符集编码的组合都是有效的。我们假设客户端的编码是C,MySQL表的字符集编码是S。那么为了能够错进错出,需要满足以下两个条件

MySQL接收请求时,从C编码后的二进制流在被S解码时能够无损 
MySQL返回数据是,从S编码后的二进制流在被C解码时能够无损

编码无损转换

那么什么是有损转换,什么是无损转换呢?假设我们要把用编码A表示的字符X,转化为编码B的表示形式,而编码B的字形集中并没有X这个字符,那么此时我们就称这个转换是有损的。那么,为什么会出现两个编码所能表示字符集合的差异呢?如果大家看过博主之前的那篇 十分钟搞清字符集和字符编码,或者对字符编码有基础理解的话,就应该知道每个字符集所支持的字符数量是有限的,并且各个字符集涵盖的文字之间存在差异。UTF8和GBK所能表示的字符数量范围如下

  • GBK单个字符编码后的取值范围是:8140 - FEFE 其中不包括**7E,总共字符数在27000左右
  • UTF8单个字符编码后,按照字节数的不同,取值范围如下表:

由于UTF-8编码能表示的字符数量远超GBK。那么我们很容易就能找到一个从UTF8到GBK的有损编码转换。我们用字符映射器(见下图)找出了一个明显就不在GBK编码表中的字符,尝试存入到GBK编码的表中。并再次取出查看有损转换的行为
字符信息具体是:ਅ GURMUKHI LETTER A Unicode: U+0A05, UTF-8: E0 A8 85

在MySQL中存储的具体情况如下:

master [localhost] {msandbox} (test) > create table charset_test_gbk (id int primary key auto_increment, char_col varchar(50)) charset = gbk; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > set names utf8; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > insert into charset_test_gbk (char_col) values ('ਅ'); Query OK, 1 row affected, 1 warning (0.01 sec) master [localhost] {msandbox} (test) > show warnings; +---------+------+-----------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\xE0\xA8\x85' for column 'char_col' at row 1 | +---------+------+-----------------------------------------------------------------------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > select id,hex(char_col),char_col,char_length(char_col) from charset_test_gbk; +----+---------------+----------+-----------------------+ | id | hex(char_col) | char_col | char_length(char_col) | +----+---------------+----------+-----------------------+ | 1 | 3F | ? | 1 | +----+---------------+----------+-----------------------+ 1 row in set (0.00 sec)

出错的部分是在编解码的第3步时发生的。具体见下图
flow2

可见MySQL内部如果无法找到一个UTF8字符所对应的GBK字符时,就会转换成一个错误mark(这里是问号)。而每个字符集在程序实现的时候内部都约定了当出现这种情况时的行为和转换规则。例如:UTF8中无法找到对应字符时,如果不抛错那么就将该字符替换成� (U+FFFD)

那么是不是任何两种字符集编码之间的转换都是有损的呢?并非这样,转换是否有损取决于以下几点:

  • 被转换的字符是否同时在两个字符集中
  • 目标字符集是否能够对不支持字符,保留其原有表达形式

关于第一点,刚才已经通过实验来解释过了。这里来解释下造成有损转换的第二个因素。从刚才的例子我们可以看到由于GBK在处理自己无法表示的字符时的行为是:用错误标识替代,即0x3F。而有些字符集(例如latin1)在遇到自己无法表示的字符时,会保留原字符集的编码数据,并跳过忽略该字符进而处理后面的数据。如果目标字符集具有这样的特性,那么就能够实现这节最开始提到的错进错出的效果。
我们来看下面这个例子

master [localhost] {msandbox} (test) > create table charset_test (id int primary key auto_increment, char_col varchar(50)) charset = latin1; Query OK, 0 rows affected (0.03 sec) master [localhost] {msandbox} (test) > set names latin1; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > insert into charset_test (char_col) values ('中文'); Query OK, 1 row affected (0.01 sec) master [localhost] {msandbox} (test) > select id,hex(char_col),char_col from charset_test; +----+---------------+----------+ | id | hex(char_col) | char_col | +----+---------------+----------+ | 2 | E4B8ADE69687 | 中文 | +----+---------------+----------+ 2 rows in set (0.00 sec)

具体流程图如下。可见在被MySQL Server接收到以后实际上已经发生了编码不一致的情况。但是由于Latin1字符集对于自己表述范围外的字符不会做任何处理,而是保留原值。这样的行为也使得错进错出成为了可能。
flow4


如何避免乱码

理解了上面的内容,要避免乱码就显得很容易了。只要做到“三位一体”,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。而对于已经出现乱码,或者已经遭受有损转码的数据,如何修复相对来说就会有些困难。下一节我们详细介绍具体方法。

如何修复已经编码损坏的数据

在介绍正确方法前,我们先科普一下那些网上流传的所谓的“正确方法”可能会造成的严重后果。

错误方法一

无论从语法还是字面意思来看:ALTER TABLE ... CHARSET=xxx 无疑是最像包治乱码的良药了!而事实上,他对于你已经损坏的数据一点帮助也没有,甚至连已经该表已经创建列的默认字符集都无法改变。我们看下面这个例子

master [localhost] {msandbox} (test) > show create table charset_test; +--------------+--------------------------------+ | Table | Create Table | +--------------+--------------------------------+ | charset_test | CREATE TABLE `charset_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `char_col` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 | +--------------+--------------------------------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > alter table charset_test charset=gbk; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 master [localhost] {msandbox} (test) > show create table charset_test; +--------------+--------------------------------+ | Table | Create Table | +--------------+--------------------------------+ | charset_test | CREATE TABLE `charset_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `char_col` varchar(50) CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk | +--------------+--------------------------------+ 1 row in set (0.00 sec)

可见该语法紧紧修改了表的默认字符集,即只对以后创建的列的默认字符集产生影响,而对已经存在的列和数据没有变化。

错误方法二

ALTER TABLE … CONVERT TO CHARACTER SET … 的相较于方法一来说杀伤力更大,因为从官方文档的解释 他的作用就是用于对一个表的数据进行编码转换。下面是文档的一小段摘录:

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:
ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

而实际上,这句语法只适用于当前并没有乱码,并且不是通过错进错出的方法保存的表。。而对于已经因为错进错出而产生编码错误的表,则会带来更糟的结果。我们用一个实际例子来解释下,这句SQL实际做了什么和他会造成的结果。假设我们有一张编码是latin1的表,且之前通过错进错出存入了UTF-8的数据,但是因为通过terminal仍然能够正常显示。即上文错进错出章节中举例的情况。一段时间使用后我们发现了这个错误,并打算把表的字符集编码改成UTF-8并且不影响原有数据的正常显示。这种情况下使用alter table convert to character set会有这样的后果:

master [localhost] {msandbox} (test) > create table charset_test_latin1 (id int primary key auto_increment, char_col varchar(50)) charset = latin1; Query OK, 0 rows affected (0.01 sec) master [localhost] {msandbox} (test) > set names latin1; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > insert into charset_test_latin1 (char_col) values ('这是中文'); Query OK, 1 row affected (0.01 sec) master [localhost] {msandbox} (test) > select id,hex(char_col),char_col,char_length(char_col) from charset_test_latin1; +----+--------------------------+--------------+-----------------------+ | id | hex(char_col) | char_col | char_length(char_col) | +----+--------------------------+--------------+-----------------------+ | 1 | E8BF99E698AFE4B8ADE69687 | 这是中文 | 12 | +----+--------------------------+--------------+-----------------------+ 1 row in set (0.01 sec) master [localhost] {msandbox} (test) > alter table charset_test_latin1 convert to character set utf8; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 master [localhost] {msandbox} (test) > set names utf8; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > select id,hex(char_col),char_col,char_length(char_col) from charset_test_latin1; +----+--------------------------------------------------------+-----------------------------+-----------------------+ | id | hex(char_col) | char_col | char_length(char_col) | +----+--------------------------------------------------------+-----------------------------+-----------------------+ | 1 | C3A8C2BFE284A2C3A6CB9CC2AFC3A4C2B8C2ADC3A6E28093E280A1 | 这是中文 | 12 | +----+--------------------------------------------------------+-----------------------------+-----------------------+ 1 row in set (0.00 sec)

从这个例子我们可以看出,对于已经错进错出的数据表,这个命令不但没有起到“拨乱反正”的效果,还会彻底将数据糟蹋,连数据的二进制编码都改变了。

正确的方法一 Dump & Reload

这个方法比较笨,但也比较好操作和理解。简单的说分为以下三步:

  1. 通过错进错出的方法,导出到文件
  2. 用正确的字符集修改新表
  3. 将之前导出的文件导回到新表中

还是用上面那个例子举例,我们用UTF-8将数据“错进”到latin1编码的表中。现在需要将表编码修改为UTF-8可以使用以下命令

shell> mysqldump -u root -p -t --skip-set-charset --default-character-set=utf8 test charset_test_latin1 > data.sql #确保导出的文件用文本编辑器在UTF-8编码下查看没有乱码 shell> mysql -uroot -p -e 'create table charset_test_latin1 (id int primary key auto_increment, char_col varchar(50)) charset = utf8' test shell> mysql -uroot -p  --default-character-set=utf8 test < data.sql

正确的方法二 Convert to Binary & Convert Back

这种方法比较取巧,用的是将二进制数据作为中间数据的做法来实现的。由于,MySQL再将有编码意义的数据流,转换为无编码意义的二进制数据的时候并不做实际的数据转换。而从二进制数据准换为带编码的数据时,又会用目标编码做一次编码转换校验。通过这两个特性就相当于在MySQL内部模拟了一次“错出”,将乱码“拨乱反正”了。

还是用上面那个例子举例,我们用UTF-8将数据“错进”到latin1编码的表中。现在需要将表编码修改为UTF-8可以使用以下命令


mysql> ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col VARBINARY(50); mysql> ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col varchar(50) charac

MySQL用户密码是如何生成和保存的

如果你已经接触MySQL一段时间了,那么想必你一定知道MySQL把所有用户的用户名和密码的密文存放在mysql.user表中。大致的形式如下:

mysql [localhost] {msandbox} (mysql) > select user,password from mysql.user; +----------------+-------------------------------------------+ | user | password | +----------------+-------------------------------------------+ | root | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | | plain_password | *861D75A7F79DE84B116074893BBBA7C4F19C14FA | | msandbox | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | | msandbox | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | | rsandbox | *B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD | +----------------+-------------------------------------------+ 9 rows in set (0.01 sec)*

可见MySQL在其内部是不存放用户的明文密码的(这个也是一般程序对于敏感信息的最基础保护)。一般来说密文是通过不可逆加密算法得到的。这样即使敏感信息泄漏,除了暴力破解是无法快速从密文直接得到明文的。

MySQL用的是哪种不可逆算法来加密用户密码的

MySQL实际上是使用了两次SHA1夹杂一次unhex的方式对用户密码进行了加密。具体的算法可以用公式表示:password_str = concat('*', sha1(unhex(sha1(password))))
我们可以用下面的方法做个简单的验证。

mysql [localhost] {msandbox} (mysql) > select password('mypassword'),concat('*',sha1(unhex(sha1('mypassword')))); +-------------------------------------------+---------------------------------------------+ | password('mypassword') | concat('*',sha1(unhex(sha1('mypassword')))) | +-------------------------------------------+---------------------------------------------+ | *FABE5482D5AADF36D028AC443D117BE1180B9725 | *fabe5482d5aadf36d028ac443d117be1180b9725 | +-------------------------------------------+---------------------------------------------+ 1 row in set (0.01 sec)

MySQL用户密码的不安全性

其实MySQL在5.6版本以前,对于对于安全性的重视度非常低,对于用户密码也不例外。例如,MySQL对于binary log中和用户密码相关的操作是不加密的。如果你向MySQL发送了例如create user,grant user ... identified by这样的携带初始明文密码的指令,那么会在binary log中原原本本的被还原出来。我们通过下面的例子来验证

创建一个用户

mysql [localhost] {msandbox} (mysql) > create user plain_password identified by 'plain_pass'; Query OK, 0 rows affected (0.00 sec)

用mysqlbinlog查看二进制日志

shell> mysqlbinlog binlog.000001 # at 106 #150227 23:37:59 server id 1 end_log_pos 223 Query thread_id=1 exec_time=0 error_code=0 use mysql/*!*/; SET TIMESTAMP=1425051479/*!*/; SET @@session.pseudo_thread_id=1/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create user plain_password identified by 'plain_pass' /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

MySQL5.6中对于用户密码的安全性加强

好在MySQL5.6开始对安全性有了一定的重视,为了杜绝明文密码出现在binlog中的情况,MySQL引入了一系列会以密文方式记录二进制日志的命令:

  • REATE USER … IDENTIFIED BY …
  • GRANT … IDENTIFIED BY …
  • SET PASSWORD …
  • SLAVE START … PASSWORD = … (as of 5.6.4)
  • CREATE SERVER … OPTIONS(… PASSWORD …) (as of 5.6.9)
  • ALTER SERVER … OPTIONS(… PASSWORD …) (as of 5.6.9)

细心你的也许会发现,change master to master_password=''命令不在这个范畴中。这也就意味着MySQL5.6中仍然使用这样的语法来启动replication时有安全风险的。这也就是为什么5.6中使用带有明文密码的change master to时会有warning提示,具体如下:

slave1 [localhost] {msandbox} ((none)) > change master to master_host='127.0.0.1',master_port =21288,master_user='rsandbox',master_password='rsandbox',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.04 sec) slave1 [localhost] {msandbox} ((none)) > show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. | | Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

GTID简介

什么是GTID

GTID(Global Transaction ID)是对于一个已提交事务的编号,并且是一个全局唯一的编号。
GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增。下面是一个GTID的具体形式

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

更详细的介绍可以参见:官方文档

GTID的作用

那么GTID功能的目的是什么呢?具体归纳主要有以下两点:

  • 根据GTID可以知道事务最初是在哪个实例上提交的
  • GTID的存在方便了Replication的Failover

这里详细解释下第二点。我们可以看下在MySQL 5.6的GTID出现以前replication failover的操作过程。假设我们有一个如下图的环境
failover
此时,Server A的服务器宕机,需要将业务切换到Server B上。同时,我们又需要将Server C的复制源改成Server B。复制源修改的命令语法很简单即CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=nnnn。而难点在于,由于同一个事务在每台机器上所在的binlog名字和位置都不一样,那么怎么找到Server C当前同步停止点,对应Server B的master_log_file和master_log_pos是什么的时候就成为了难题。这也就是为什么M-S复制集群需要使用MMM,MHA这样的额外管理工具的一个重要原因。
这个问题在5.6的GTID出现后,就显得非常的简单。由于同一事务的GTID在所有节点上的值一致,那么根据Server C当前停止点的GTID就能唯一定位到Server B上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现,我们都不需要知道GTID的具体值,直接使用CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令就可以直接完成failover的工作。 So easy不是么?

搭建

本次搭建使用了mysql_sandbox脚本为基础,先创建了一个一主三从的基于位置复制的环境。然后通过配置修改,将整个架构专为基于GTID的复制。如果你还不熟悉mysql_sandbox,可以阅读博客之前的文章博客之前的文章一步步的安装。
根据MySQL官方文档给出的GTID搭建建议。需要一次对主从节点做配置修改,并重启服务。这样的操作,显然在production环境进行升级时是不可接受的。Facebook,Booking.com,Percona都对此通过patch做了优化,做到了更优雅的升级。具体的操作方式会在以后的博文当中介绍到。这里我们就按照官方文档,进行一次实验性的升级。
主要的升级步骤会有以下几步:

  • 确保主从同步
  • 在master上配置read_only,保证没有新数据写入
  • 修改master上的my.cnf,并重启服务
  • 修改slave上的my.cnf,并重启服务
  • 在slave上执行change master to并带上master_auto_position=1启用基于GTID的复制

由于是实验环境,read_only和服务重启并无大碍。只要按照官方的GTID搭建建议做就能顺利完成升级,这里就不赘述详细过程了。下面列举了一些在升级过程中容易遇到的错误。

常见错误

gtid_mode=ON,log_slave_updates,enforce_gtid_consistency这三个参数一定要同时在my.cnf中配置。否则在mysql.err中会出现如下的报错

2015-02-26 17:11:08 32147 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates
2015-02-26 17:13:53 32570 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 requires --enforce-gtid-consistency

change master to 后的warnings

在按照文档的操作change master to后,会发现有两个warnings。其实是两个安全性警告,不影响正常的同步(有兴趣的读者可以看下关于该warning的具体介绍。warning的具体内容如下:

slave1 [localhost] {msandbox} ((none)) > stop slave; Query OK, 0 rows affected (0.03 sec) slave1 [localhost] {msandbox} ((none)) > change master to master_host='127.0.0.1',master_port =21288,master_user='rsandbox',master_password='rsandbox',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.04 sec) slave1 [localhost] {msandbox} ((none)) > show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. | | Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

实验一:如果slave所需要事务对应的GTID在master上已经被purge了

根据show global variables like '%gtid%'的命令结果我们可以看到,和GTID相关的变量中有一个gtid_purged。从字面意思以及 官方文档可以知道该变量中记录的是本机上已经执行过,但是已经被purge binary logs to命令清理的gtid_set。
本节中我们就要试验下,如果master上把某些slave还没有fetch到的gtid event purge后会有什么样的结果。

以下指令在master上执行

master [localhost] {msandbox} (test) > show global variables like '%gtid%'; +---------------------------------+----------------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_executed | 24024e52-bd95-11e4-9c6d-926853670d0b:1 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+----------------------------------------+ 7 rows in set (0.01 sec) master [localhost] {msandbox} (test) > flush logs;create table gtid_test2 (ID int) engine=innodb; Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected (0.02 sec) master [localhost] {msandbox} (test) > flush logs;create table gtid_test3 (ID int) engine=innodb; Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected (0.04 sec) master [localhost] {msandbox} (test) > show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000005 | 359 | | | 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > purge binary logs to 'mysql-bin.000004'; Query OK, 0 rows affected (0.03 sec) master [localhost] {msandbox} (test) > show global variables like '%gtid%'; +---------------------------------+------------------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------------------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_executed | 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | 24024e52-bd95-11e4-9c6d-926853670d0b:1 | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+------------------------------------------+ 7 rows in set (0.00 sec)

在slave2上重新做一次主从,以下命令在slave2上执行

slave2 [localhost] {msandbox} ((none)) > change master to master_host='127.0.0.1',master_port =21288,master_user='rsandbox',master_password='rsandbox',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.04 sec) slave2 [localhost] {msandbox} ((none)) > start slave; Query OK, 0 rows affected (0.01 sec) slave2 [localhost] {msandbox} ((none)) > show slave status\G *************************** 1. row *************************** ...... Slave_IO_Running: No Slave_SQL_Running: Yes ...... Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 151 ...... Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' Last_SQL_Errno: 0 Last_SQL_Error: ...... Auto_Position: 1 1 row in set (0.00 sec)

实验二:忽略purged的部分,强行同步

那么实际生产应用当中,偶尔会遇到这样的情况:某个slave从备份恢复后(或者load data infile)后,DBA可以人为保证该slave数据和master一致;或者即使不一致,这些差异也不会导致今后的主从异常(例如:所有master上只有insert没有update)。这样的前提下,我们又想使slave通过replication从master进行数据复制。此时我们就需要跳过master已经被purge的部分,那么实际该如何操作呢?
我们还是以实验一的情况为例:

先确认master上已经purge的部分。从下面的命令结果可以知道master上已经缺失24024e52-bd95-11e4-9c6d-926853670d0b:1这一条事务的相关日志

master [localhost] {msandbox} (test) > show global variables like '%gtid%'; +---------------------------------+------------------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------------------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_executed | 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | 24024e52-bd95-11e4-9c6d-926853670d0b:1 | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+------------------------------------------+ 7 rows in set (0.00 sec)

在slave上通过set global gtid_purged='xxxx'的方式,跳过已经purge的部分

slave2 [localhost] {msandbox} ((none)) > stop slave; Query OK, 0 rows affected (0.04 sec) slave2 [localhost] {msandbox} ((none)) > set global gtid_purged = '24024e52-bd95-11e4-9c6d-926853670d0b:1'; Query OK, 0 rows affected (0.05 sec) slave2 [localhost] {msandbox} ((none)) > start slave; Query OK, 0 rows affected (0.01 sec) slave2 [localhost] {msandbox} ((none)) > show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event ...... Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 359 Relay_Log_File: mysql_sandbox21290-relay-bin.000004 Relay_Log_Pos: 569 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...... Exec_Master_Log_Pos: 359 Relay_Log_Space: 873 ...... Master_Server_Id: 1 Master_UUID: 24024e52-bd95-11e4-9c6d-926853670d0b Master_Info_File: /data/mysql/rsandbox_mysql-5_6_23/node2/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it ...... Retrieved_Gtid_Set: 24024e52-bd95-11e4-9c6d-926853670d0b:2-3 Executed_Gtid_Set: 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 Auto_Position: 1 1 row in set (0.00 sec)

可以看到此时slave已经可以正常同步,并补齐了24024e52-bd95-11e4-9c6d-926853670d0b:2-3范围的binlog日志。

情况一:逻辑误操作,误删部分数据

这种情况可以用来自同一台机器的的最近一次物理备份中的ibd恢复覆盖,且备份后table没有被recreate过。
这种情况是最简单的,备份时的ibd文件(后称老ibd)中的space id和index id 与 新ibd的space id 和index id一致。
且和ibdata文件中的space id和index id一致。因此,物理文件可以直接覆盖做恢复。

以下是详细步骤

准备阶段

Step pre 1: 物理备份

innobackupex --defaults-file=/usr/local/mysql3321/my.cnf --socket=/xfs/mysql3321/mysql.sock --user=root --password=password /xfs/backup/

Step pre 2 : 停止数据库对外服务

service mysqld restart #(起在另外一个端口上)

或者 停止所有业务连接并且set global innodb_max_dirty_pages_pct =0

操作阶段

Step 0 : apply log

innobackupex --apply-log --defaults-file=/usr/local/mysql3321/my.cnf  /xfs/backup/2012-10-17_11-29-20/

Step 1 : 备份现在的ibd文件(可选)

cp -a testibd.ibd testibd.bak

Step 2 : 舍弃现在ibd文件

mysql> alter table testibd discard tablespace

Step 3 : 复制备份ibd文件

shell> cp /xfs/backup/2012-10-17_11-29-20/test/testibd.ibd /xfs/mysql3321/test/ 
shell> chown mysql:mysql /xfs/mysql3321/test/testibd.ibd

Step 4 : 导入ibd文件

mysql> alter table testibd import tablespace

情况二:误删 table,表结构已经被drop了

这种情况稍复杂,不过恢复过程还是比较容易操作的。由于table被drop后的space id会留空因此备份文件的space id不会被占用。

我们只需要重建表结构,然后把ibdata中该表的space id还原,物理文件可以直接覆盖做恢复了。

Step 1 : 重建表

mysql> alter table testibd import tablespace

mysql> create table testibd (UserID int);

Step 2 : 关闭mysql服务(必须)

shell> service mysqld3321 stop

Step 3: 准备ibd文件 apply log

shell> innobackupex --apply-log --defaults-file=/usr/local/mysql3321/my.cnf  /xfs/backup/2012-10-17_11-29-20/

Step 4 : 备份现在的ibd文件(可选)

cp -a testibd.ibd testibd.bak

Step 5 : 复制备份ibd文件

shell> cp -a /xfs/backup/2012-10-17_11-29-20/test/testibd.ibd /xfs/mysql3321/test/ 
shell> chown mysql:mysql /xfs/mysql3321/test/testibd.ibd

Step 6 : 使用percona recovery tool 修改ibdata

shell> /root/install/percona-data-recovery-tool-for-innodb-0.5/ibdconnect -o /xfs/mysql3321/ibdata1 -f /xfs/mysql3321/test/testibd.ibd -d test -t testibd

输出结果

Initializing table definitions...
Processing table: SYS_TABLES
 - total fields: 10
 - nullable fields: 6
 - minimum header size: 5
 - minimum rec size: 21
 - maximum rec size: 555

Processing table: SYS_INDEXES
 - total fields: 9
 - nullable fields: 5
 - minimum header size: 5
 - minimum rec size: 29
 - maximum rec size: 165

Setting SPACE=1 in SYS_TABLE for `test`.`testibd` Check if space id 1 is already used
Page_id: 8, next page_id: 4294967295
Record position: 65
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0 Db/table: infimum
Space id: 1768842857 (0x696E6669) Next record at offset: 8D
Record position: 8D
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52 Db/table: SYS_FOREIGN
Space id: 0 (0x0) Next record at offset: D5
Record position: D5
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57 Db/table: SYS_FOREIGN_COLS
Space id: 0 (0x0) Next record at offset: 122
Record position: 122
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 12 18 25 33 37 41 49 53 53 Db/table: test/testibd
Space id: 2 (0x2) Next record at offset: 74
Space id 1 is not used in any of the records in SYS_TABLES
Page_id: 8, next page_id: 4294967295
Record position: 65
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0 Db/table: infimum
Space id: 1768842857 (0x696E6669) Next record at offset: 8D
Record position: 8D
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52 Db/table: SYS_FOREIGN
Space id: 0 (0x0) Next record at offset: D5
Record position: D5
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57 Db/table: SYS_FOREIGN_COLS
Space id: 0 (0x0) Next record at offset: 122
Record position: 122
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 12 18 25 33 37 41 49 53 53 Db/table: test/testibd
Space id: 2 (0x2) Updating test/testibd (table_id 17) with id 0x01000000
SYS_TABLES is updated successfully
Initializing table definitions...
Processing table: SYS_TABLES
 - total fields: 10
 - nullable fields: 6
 - minimum header size: 5
 - minimum rec size: 21
 - maximum rec size: 555

Processing table: SYS_INDEXES
 - total fields: 9
 - nullable fields: 5
 - minimum header size: 5
 - minimum rec size: 29
 - maximum rec size: 165

Setting SPACE=1 in SYS_INDEXES for TABLE_ID = 17
Page_id: 11, next page_id: 4294967295
Record position: 65
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 50 7 2 0 0 0 0 TABLE_ID: 3798561113125514496
SPACE: 1768842857
Next record at offset: 8C
Record position: 8C
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47 TABLE_ID: 11
SPACE: 0
Next record at offset: CE
Record position: CE
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 TABLE_ID: 11
SPACE: 0
Next record at offset: 111
Record position: 111
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 TABLE_ID: 11
SPACE: 0
Next record at offset: 154
Record position: 154
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47 TABLE_ID: 12
SPACE: 0
Next record at offset: 22C
Record position: 22C
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 44 48 52 56 TABLE_ID: 17
SPACE: 2
Updating SPACE(0x00000001 , 0x01000000) for TABLE_ID: 17
sizeof(s)=4
Next record at offset: 74
SYS_INDEXES is updated successfully

Step 7 : 使用percona recovery tool 重新checksum ibdata

重复执行以下命令,直到程序没有输出为止。

shell> /root/install/percona-data-recovery-tool-for-innodb-0.5/innochecksum -f /xfs/mysql3321/ibdata1

输出结果

page 8 invalid (fails old style checksum) page 8: old style: calculated = 0xF4AD74CB; recorded = 0xEECB309D
fixing old checksum of page 8
page 8 invalid (fails new style checksum) page 8: new style: calculated = 0x6F0C29B4; recorded = 0x3D02308C
fixing new checksum of page 8
page 11 invalid (fails old style checksum) page 11: old style: calculated = 0x3908087C; recorded = 0xF9E8D30C
fixing old checksum of page 11
page 11 invalid (fails new style checksum) page 11: new style: calculated = 0xB26CFD77; recorded = 0xDB25D39D
fixing new checksum of page 11

Step 8 : 启动mysql服务

shell> service mysqld3321 start

行列转换常见场景

由于很多业务表因为历史原因或者性能原因,都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值(具体结构见下表)。 这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果。

表数据:

ID Value
1 tiny,small,big
2 small,medium
3 tiny,big

期望得到结果:

ID Value
1 tiny
1 small
1 big
2 small
2 medium
3 tiny
3 big

具体方法

先从一个具体实例开始我们的介绍:

#准备示例数据 create table tbl_name (ID int ,mSize varchar(100)); insert into tbl_name values (1,'tiny,small,big'); insert into tbl_name values (2,'small,medium'); insert into tbl_name values (3,'tiny,big'); #用于行列转换循环的自增表 create table incre_table (AutoIncreID int); insert into incre_table values (1); insert into incre_table values (2); insert into incre_table values (3); #实现行列转换的SQL select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1) from tbl_name a join incre_table b on b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,',',''))+1) order by a.ID;

原理分析:
这个join最基本原理是笛卡尔积。通过这个方式来实现循环。
以下是具体问题分析:
length(a.Size) - length(replace(a.mSize,',',''))+1 表示了,按照逗号分割后,改列拥有的数值数量,下面简称n
join过程的伪代码:

根据ID进行循环 { 判断:i 是否 <= n { 获取最靠近第 i 个逗号之前的数据, 即 substring_index(substring_index(a.mSize,',',b.ID),',',-1) i = i +1 } ID = ID +1 }

改进版本

上面一种方法方法的缺点在于,我们需要一个拥有连续数列的独立表(也就是上文中的incre_table)。并且连续数列的最大值一定要大于符合分割的值的个数。 例如有一行的mSize 有100个逗号分割的值,那么我们的incre_table 就需要有至少100个连续行。 当然,mysql内部也有现成的连续数列表可用。如mysql.help_topic, help_topic_id 共有504个数值,一般能满足于大部分需求了。

改写后如下:

select a.ID,substring_index(substring_index(a.mSize,',',b.help_topic_id+1),',',-1) from tbl_name a join mysql.help_topic b on b.help_topic_id < (length(a.mSize) - length(replace(a.mSize,',',''))+1) order by a.ID;

参数介绍

interactive_timeout

The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option tomysql_real_connect(). See alsowait_timeout.

wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory.
On thread startup, the session wait_timeout value is initialized from the globalwait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option tomysql_real_connect()). See also interactive_timeout.

CLIENT_INTERACTIVE

Permit interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection. The client’s session wait_timeout variable is set to the value of the session interactive_timeout variable.

简单的说 interactive就是交互式的终端,例如在shell里面直接执行mysql,出现形如mysql>的提示符后就是交互式的连接。而mysql -e ‘select 1’ 这样的直接返回结果的方式就是非交互式的连接。


测试及验证

继承关系

Q:通过socket连接 timeout会从哪个global timeout继承
A:由下例可见,通过socket登录,timeout 继承于global.interactive_timeout;

mysql> set global interactive_timeout = 11111; Query OK, 0 rows affected (0.00 sec) mysql> set global wait_timeout = 22222; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 11111 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 22222 | +----------------------------+----------+ 10 rows in set (0.00 sec) mysql -uroot -ppassword -S /usr/local/mysql3310/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show session variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 11111 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 11111 | +----------------------------+----------+ 10 rows in set (0.00 sec)

Q:通过TCP/IP client 连接, timeout会从哪个global timeout继承
A:由下例可见,通过TCP/IP client 连接后的wait_timeout 仍然继承于global.interactive_timeout

mysql -uroot -ppassword -h 127.0.0.1 --port 3310 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show session variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 11111 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 11111 | +----------------------------+----------+ 10 rows in set (0.00 sec)

起效关系

Q:timeout值,对于处于运行状态SQL语句是否起效(即是否等价于执行超时)?
A:由下例可见SQL正在执行状态的等待时间不计入timeout时间。即SQL运行再久也不会因为timeout的配置而中断

mysql> set session wait_timeout=10; Query OK, 0 rows affected (0.00 sec) mysql> set session interactive_timeout=10; Query OK, 0 rows affected (0.00 sec) mysql> select 1,sleep(20) from dual; +---+-----------+ | 1 | sleep(20) | +---+-----------+ | 1 | 0 | +---+-----------+ 1 row in set (20.00 sec) mysql> mysql> show session variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 10 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 10 | +----------------------------+----------+


Q:同一个session中,wait_timeout 和 interacitve_timeout是否都会生效。
A:只有wait_timeout 会真正起到超时限制的作用

mysql> set session interactive_timeout=10; Query OK, 0 rows affected (0.00 sec) mysql> set session wait_timeout=20; Query OK, 0 rows affected (0.00 sec) mysql> show full processlist; +----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | 1 | system user | | NULL | Connect | 103749 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0 | 0 | 1 | | 2 | system user | | NULL | Connect | 103750 | Connecting to master | NULL | 0 | 0 | 1 | | 3 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0 | 0 | 11 | | 10 | root | localhost:58946 | NULL | Sleep | 20 | | NULL | 0 | 0 | 11 | +----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ 4 rows in set (0.00 sec) mysql> show full processlist; +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | 1 | system user | | NULL | Connect | 103749 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0 | 0 | 1 | | 2 | system user | | NULL | Connect | 103750 | Connecting to master | NULL | 0 | 0 | 1 | | 3 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0 | 0 | 11 | +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ 3 rows in set (0.00 sec)


Q:global timeout和session timeout是否都会作为超时判断依据?
A:只有session级别 timeout 会起作用。即一个session开始后,无论如何修改global级别的timeout都不会影响该session

  • 测试1:
mysql> set session interactive_timeout = 10; Query OK, 0 rows affected (0.00 sec) mysql> set session wait_timeout = 10; Query OK, 0 rows affected (0.00 sec) mysql> show session variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | interactive_timeout | 10 | | wait_timeout | 10 | +----------------------------+----------+ 10 rows in set (0.00 sec) mysql> show global variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | interactive_timeout | 20 | | wait_timeout | 20 | +----------------------------+----------+ 10 rows in set (0.00 sec) mysql> show full processlist; +----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | 3 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0 | 0 | 11 | | 17 | root | localhost:60585 | NULL | Sleep | 10 | | NULL | 10 | 10 | 11 | +----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ 2 rows in set (0.00 sec) mysql> show full processlist; +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | 3 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0 | 0 | 11 | +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ 1 rows in set (0.00 sec)
  • 测试2:
mysql> show session variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | interactive_timeout | 20 | | wait_timeout | 20 | +----------------------------+----------+ 10 rows in set (0.00 sec) mysql> show global variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | interactive_timeout | 10 | | wait_timeout | 10 | +----------------------------+----------+ 10 rows in set (0.00 sec) mysql> show full processlist; +----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | 3 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0 | 0 | 11 | | 19 | root | localhost:50276 | NULL | Sleep | 19 | | NULL | 10 | 10 | 11 | +----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ 2 rows in set (0.00 sec) mysql> show full processlist; +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ | 3 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0 | 0 | 11 | +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+ 1 rows in set (0.00 sec)


总结

由以上的阶段测试可以获得以下结论。

  1. 超时时间只对非活动状态的connection进行计算。
  2. 超时时间只以session级别的wait_timeout 为超时依据,global级别只决定session初始化时的超时默认值。
  3. 交互式连接的wait_timeout 继承于global的interactive_timeout。非交互式连接的wait_timeout继承于global的wait_timeout
  4. 继承关系和超时对 TCP/IP 和 Socket 连接均有效果

共有 人打赏支持
粉丝 151
博文 890
码字总数 4479466
×
五大三粗
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: