MySQL超时参数以及相关DataX数据同步案例分享

一、背景

MySQL系统变量提供关于服务器的一些配置和能力信息，大部分变量可在mysqld服务进程启动时设置，部分变量可在mysqld服务进程运行时设置。合理的系统变量设值范围，是保障MySQL稳定提供服务的重要因素。本文主要描述MySQL数据库的超时timeout相关的一些系统变量，部分参数同程序应用中常见到的CommunicationsException: Communications link failure异常息息相关。

本文也结合数据同步的场景，对使用DataX3进行MySQL数据同步时遇到的几个经典案例进行阐述，期望对遇到类似问题的同学有一定的参考作用。

安利一下：DataX 是一个异构数据源离线同步工具，致力于实现包括关系型数据库(MySQL、Oracle等)、HDFS、Hive、ODPS、HBase、FTP等各种异构数据源之间稳定高效的数据同步功能。详细介绍可见：阿里云开源离线同步工具DataX3.0介绍、开源代码仓库WIKI地址

二、超时系统变量介绍

MySQL同超时相关的系统变量名大都包含timeout关键字，使用MySQL show variables 即可查询当前变量值状态，查询SQL以及结果如下：

show variables like '%timeout%';

下面具体介绍各个超时系统变量的含义，内容主要翻译和参考了MySQL官方文档。

1.connect_timeout

connect_timeout是MySQL服务端进程mysqld等待连接建立完成的时间，单位为秒。如果超过connect_timeout时间范围内，仍然无法完成协议握手话，MySQL客户端会收到异常，异常消息类似于:Lost connection to MySQL server at 'XXX', system error: errno. 遇到此类错误，一般可尝试性增加connect_timeout时间的大小。

顺便介绍下MySQL应用协议握手发包过程: MySQL协议握手

以Plain Handshake为例，在TCP三次握手建立后，进入MySQL应用层协议握手阶段，过程如下:

• server sending Initial Handshake Packet
  
• client replying with Handshake Response Packet

MySQL服务端会有监听线程循环接收请求，当有客户端请求过来时，会创建线程（或者从线程池中取）来处理这个客户端请求（一般基于网络的应用都是这个套路，监听线程不具体处理客户请求）。由于MySQL连接基于TCP协议，在MySQL应用层协议建立前，需要完成TCP三次握手，在TCP三次握手成功之后，客户端会进入阻塞，等待服务端的消息，进入应用层协议握手阶段。后续服务端会创建线程（或者从线程池中取）来处理请求，这里会进行权限相关的验证，比如host、用户名、密码验证等。host验证即用grant命令授权用户的时候是有指定host的（比如设置了仅允许指定ip的客户端连接数据库）；用户名密码认证则是服务端先生成一个随机数发送给客户端，客户端用该随机数和密码进行多次sha1加密后发送给服务端验证。如果在connect_timeout时间范围内无法完成握手，则报告建立连接异常。

相对于MySQL数据查询Query来说，连接握手相对耗时较少，系统默认的10秒一般足够了，不太会出现需要修改的场景。

2.delayed_insert_timeout

delayed_insert_timeout用于控制MySQL的延迟插入（DELAYED inserts）超时时间，由于在MySQL 5.6.7后，延迟插入不在支持，此系统变量也不建议修改或使用。

3.innodb_lock_wait_timeout

innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用事务失败。参数的时间单位是秒，最小可设置为1s(一般不会设置得这么小)，最大可设置1073741824秒（34年），默认值是50秒。当锁等待超过设置时间的时候，类似会报如下的错误； ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。

innodb_lock_wait_timeout只针对InnoDB存储引擎下的行锁，不影响或限制表锁，可以通过SET GLOBAL 或者 SET SESSION语句在运行时修改此值。

在数据同步场景下，MySQL写出使用了批量batch提交模式，每个batch会开启一个事务的，innodb引擎下，会受到此参数的影响。

4.innodb_rollback_on_timeout

innodb_rollback_on_timeout只针对InnoDB存储引擎，默认为为OFF。ON表示回滚整个事务，OFF表示只回滚最后一条报错的sql。如果事务因为加锁超时，根据innodb_rollback_on_timeout进行回滚（完整事务或最后一条出错SQL），客户端会收到报错异常，用户可以选择是继续提交，或者回滚之前的操作，由用户选择是否进一步提交或者回滚事务。

此系统变量是一个布尔状态的参量，不是超时时间。

5.interactive_timeout

interactive_timeout表示服务器关闭交互式连接前等待活动的秒数，参数默认值28800秒（8小时）。交互式客户端（如mysql命令行）定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。如果某个交互式客户连接在interactive_timeout秒内没有操作动作，MySQL服务器就认为该客户连接不再有保留的必要并自动关闭这个连接。对于非交互式的客户连接，MySQL服务器将使用wait_timeout变量的值作为这种超时等待时间。

一个交互式客户连接断开并重连的例子（mysql命令行客户端）如下：

DataX进行MySQL数据同步时，使用的MySQL驱动是mysql-connector-java 5.1.34版本，属于非交互式客户端。

6.wait_timeout

wait_timeout表示服务器关闭非交互连接之前等待活动的秒数，参数默认值：28800秒（8小时）。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。如果某个非交互的客户连接在wait_timeout秒内没有操作动作，MySQL服务器就将认为该客户连接不再有保留的必要并自动关闭这个连接。对于交互式的客户连接，MySQL服务器使用interactive_timeout变量的值作为这种超时等待的秒数。这个变量仅使用于TCP/IP连接和Unix系统上的套接字文件连接。

DataX进行MySQL数据同步时，使用的MySQL驱动是mysql-connector-java 5.1.34版本，属于非交互式客户端。

这里介绍和wait_timeout相关的2个场景。

6.1MySQL经典的8小时连接超时问题

假设你的数据库是MySQL，如果数据源配置不当，将可能发生经典的8小时问题。原因是MySQL在默认情况下，如果发现一个连接的空闲时间超过8小时，将会在数据库端自动关闭这个连接。而数据源并不知道这个连接已经关闭了，当它将这个无用的连接返回给应用时，应用在此连接上执行的SQL Statement都会失败。一般数据库连接池(如c3p0、dbcp)都有保活机制、或者返回连接前检查连接的可用性。

此问题的相关异常栈一般如下所示：

com.mysql.jdbc.excepti