记一次mysql故障: is marked as crashed and should be repaired

Not see︶ 发表了文章 • 1 个评论 • 425 次浏览 • 2016-09-24 21:03 • 来自相关话题

机器断电导致mysql不正常停服, 日志报错:
160924 20:24:17 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:17 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:57 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:57 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:58 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:58 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:09 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:09 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:23 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:23 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:24 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:24 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:29 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:29 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:40 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:40 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:42 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
这段错误提示  ./Zblog/zbp_post 有问题

于是进行修复

找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:
[root@VM_132_47_centos ~]# myisamchk -c -r /var/lib/mysql/Zblog/zbp_post.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql/Zblog/zbp_post.MYI'
Data records: 77
- Fixing index 1
- Fixing index 2 查看全部
机器断电导致mysql不正常停服, 日志报错:
160924 20:24:17 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:17 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:57 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:57 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:58 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:24:58 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:09 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:09 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:23 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:23 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:24 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:24 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:29 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:29 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:40 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:40 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
160924 20:25:42 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired
这段错误提示  ./Zblog/zbp_post 有问题

于是进行修复

找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:
[root@VM_132_47_centos ~]# myisamchk -c -r /var/lib/mysql/Zblog/zbp_post.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql/Zblog/zbp_post.MYI'
Data records: 77
- Fixing index 1
- Fixing index 2

MySQL Server has gone away报错分析

being 发表了文章 • 0 个评论 • 466 次浏览 • 2016-08-21 23:59 • 来自相关话题

在平时和开发的交流以及在论坛回答问题的或称中会发现这个问题被问及的频率非常高。 程序中报错: MySQL server has gone away 是什么意思? 如何避免? 因此,感觉有必要总结一下发生这个问题的原因。
 

一、MySQL 服务宕了​

判断是否属于这个原因的方法很简单,执行以下命令,查看mysql的运行时长
$ mysql -uroot -p -e "show global status like 'uptime';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 68928 |
+---------------+-------+
1 row in set (0.04 sec)或者查看MySQL的报错日志,看看有没有重启的信息
$ tail /var/log/mysql/error.log
130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0M
130101 22:22:30 InnoDB: Completed initialization of buffer pool
130101 22:22:30 InnoDB: highest supported file format is Barracuda.
130101 22:22:30 InnoDB: 1.1.8 started; log sequence number 63444325509
130101 22:22:30 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130101 22:22:30 [Note] - '127.0.0.1' resolves to '127.0.0.1';
130101 22:22:30 [Note] Server socket created on IP: '127.0.0.1'.
130101 22:22:30 [Note] Event Scheduler: Loaded 0 events
130101 22:22:30 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.28-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)如果uptime数值很大,表明mysql服务运行了很久了。说明最近服务没有重启过。 如果日志没有相关信息,也说明mysql服务最近没有重启过,可以继续检查下面几项内容。
 

二、连接超时

如果程序使用的是长连接,则这种情况的可能性会比较大。 即,某个长连接很久没有新的请求发起,达到了server端的timeout,被server强行关闭。 此后再通过这个connection发起查询的时候,就会报错server has gone away
$ mysql -uroot -p -e "show global variables like '%timeout';"
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 30 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+
mysql> SET SESSION wait_timeout=5;


# Wait 10 seconds

mysql> SELECT NOW();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 132361
Current database: *** NONE ***

+---------------------+
| NOW() |
+---------------------+
| 2013-01-02 11:31:15 |
+---------------------+
1 row in set (0.00 sec)

三、进程在server端被主动kill

这种情况和情况2相似,只是发起者是DBA或者其他job。发现有长时间的慢查询执行kill xxx导致。
$ mysql -uroot -p -e "show global status like 'com_kill'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill | 0 |
+---------------+-------+

四、Your SQL statement was too large.

当查询的结果集超过 max_allowed_packet 也会出现这样的报错。定位方法是打出相关报错的语句。 用select * into outfile 的方式导出到文件,查看文件大小是否超过max_allowed_packet ,如果超过则需要调整参数,或者优化语句。
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

# 修改参数:

mysql> set global max_allowed_packet=1024*1024*16;
mysql> show global variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)英文原文:http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/ 查看全部
在平时和开发的交流以及在论坛回答问题的或称中会发现这个问题被问及的频率非常高。 程序中报错: MySQL server has gone away 是什么意思? 如何避免? 因此,感觉有必要总结一下发生这个问题的原因。
 


一、MySQL 服务宕了​


判断是否属于这个原因的方法很简单,执行以下命令,查看mysql的运行时长
$ mysql -uroot -p -e "show global status like 'uptime';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 68928 |
+---------------+-------+
1 row in set (0.04 sec)
或者查看MySQL的报错日志,看看有没有重启的信息
$ tail /var/log/mysql/error.log
130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0M
130101 22:22:30 InnoDB: Completed initialization of buffer pool
130101 22:22:30 InnoDB: highest supported file format is Barracuda.
130101 22:22:30 InnoDB: 1.1.8 started; log sequence number 63444325509
130101 22:22:30 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130101 22:22:30 [Note] - '127.0.0.1' resolves to '127.0.0.1';
130101 22:22:30 [Note] Server socket created on IP: '127.0.0.1'.
130101 22:22:30 [Note] Event Scheduler: Loaded 0 events
130101 22:22:30 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.28-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
如果uptime数值很大,表明mysql服务运行了很久了。说明最近服务没有重启过。 如果日志没有相关信息,也说明mysql服务最近没有重启过,可以继续检查下面几项内容。
 


二、连接超时


如果程序使用的是长连接,则这种情况的可能性会比较大。 即,某个长连接很久没有新的请求发起,达到了server端的timeout,被server强行关闭。 此后再通过这个connection发起查询的时候,就会报错server has gone away
$ mysql -uroot -p -e "show global variables like '%timeout';"
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 30 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+
mysql> SET SESSION wait_timeout=5;


# Wait 10 seconds

mysql> SELECT NOW();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 132361
Current database: *** NONE ***

+---------------------+
| NOW() |
+---------------------+
| 2013-01-02 11:31:15 |
+---------------------+
1 row in set (0.00 sec)


三、进程在server端被主动kill


这种情况和情况2相似,只是发起者是DBA或者其他job。发现有长时间的慢查询执行kill xxx导致。
$ mysql -uroot -p -e "show global status like 'com_kill'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill | 0 |
+---------------+-------+


四、Your SQL statement was too large.


当查询的结果集超过 max_allowed_packet 也会出现这样的报错。定位方法是打出相关报错的语句。 用select * into outfile 的方式导出到文件,查看文件大小是否超过max_allowed_packet ,如果超过则需要调整参数,或者优化语句。
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

# 修改参数:

mysql> set global max_allowed_packet=1024*1024*16;
mysql> show global variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)
英文原文:http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/

Redis慢日志查询系统slowlog

chris 发表了文章 • 0 个评论 • 623 次浏览 • 2016-07-07 20:27 • 来自相关话题

一、什么是SlowLog

SlowLog是Redis用来记录慢查询执行时间的日志系统。由于SlowLog只保存在内存中,所以SlowLog的效率非常高,
所以你不用担心会影响到你Redis的性能问题。SlowLog是Redis 2.2.12版本之后才引入的一条命令。

二、SlowLog设置

SlowLog两种设置方式如下:
 
1、redis.conf配置文件设置
在配置文件redis.conf中设置:slowlog-log-slower-than 10000
slowlog-max-len 128其中slowlog-log-slower-than表示slowlog的划定界限,只有query执行时间大于slowlog-log-slower-than的才会定义成慢查询,才会被slowlog进行记录。slowlog-log-slower-than设置的单位是微秒,默认是10000微秒,也就是10毫秒。

slowlog-max-len表示慢查询最大的条数,当slowlog超过设定的最大值后,会将最早的slowlog删除,是个FIFO队列。
 
2、使用config方式动态设置slowlog
如下,可以通过config方式动态设置slowlog:- 查看当前slowlog-log-slower-than设置
127.0.0.1:6379> CONFIG GET slowlog-log-slower-than
1) "slowlog-log-slower-than"
2) "10000"
- 设置slowlog-log-slower-than为100ms
127.0.0.1:6379> CONFIG SET slowlog-log-slower-than 100000
OK
- 设置slowlog-max-len为1000
127.0.0.1:6379> CONFIG SET slowlog-max-len 1000
OK参考:http://redisdoc.com/server/slowlog.html
 

三、SlowLog 查看

1、查看slowlog总条数127.0.0.1:6379> SLOWLOG LEN
(integer) 4
2、查看slowlog127.0.0.1:6379> SLOWLOG GET
1) 1) (integer) 25
2) (integer) 1440057769
3) (integer) 6
4) 1) "SLOWLOG"
2) "LEN"
2) 1) (integer) 24
2) (integer) 1440057756
3) (integer) 36
4) 1) "CONFIG"
2) "GET"
3) "slowlog-log-slower-than"
3) 1) (integer) 23
2) (integer) 1440057752
3) (integer) 11
4) 1) "CONFIG"
2) "SET"
3) "slowlog-log-slower-than"
4) "1"
4) 1) (integer) 22
2) (integer) 1440057493
3) (integer) 27
4) 1) "CONFIG"
2) "GET"
3) "slowlog-log-slower-than"
5) 1) (integer) 21
2) (integer) 1440057133
3) (integer) 7
4) 1) "monitor"如果要获取指定的条数可以使用SLOWLOG GET N命令127.0.0.1:6379> SLOWLOG GET 1
1) 1) (integer) 26 // slowlog唯一编号id
2) (integer) 1440057815 // 查询的时间戳
3) (integer) 47 // 查询的耗时(微秒),如表示本条命令查询耗时47微秒
4) 1) "SLOWLOG" // 查询命令,完整命令为 SLOWLOG GET,slowlog最多保存前面的31个key和128字符
2) "GET" 查看全部


一、什么是SlowLog


SlowLog是Redis用来记录慢查询执行时间的日志系统。由于SlowLog只保存在内存中,所以SlowLog的效率非常高,
所以你不用担心会影响到你Redis的性能问题。SlowLog是Redis 2.2.12版本之后才引入的一条命令。


二、SlowLog设置


SlowLog两种设置方式如下:
 
1、redis.conf配置文件设置
在配置文件redis.conf中设置:
slowlog-log-slower-than 10000
slowlog-max-len 128
其中slowlog-log-slower-than表示slowlog的划定界限,只有query执行时间大于slowlog-log-slower-than的才会定义成慢查询,才会被slowlog进行记录。slowlog-log-slower-than设置的单位是微秒,默认是10000微秒,也就是10毫秒。

slowlog-max-len表示慢查询最大的条数,当slowlog超过设定的最大值后,会将最早的slowlog删除,是个FIFO队列。
 
2、使用config方式动态设置slowlog
如下,可以通过config方式动态设置slowlog:
- 查看当前slowlog-log-slower-than设置
127.0.0.1:6379> CONFIG GET slowlog-log-slower-than
1) "slowlog-log-slower-than"
2) "10000"
- 设置slowlog-log-slower-than为100ms
127.0.0.1:6379> CONFIG SET slowlog-log-slower-than 100000
OK
- 设置slowlog-max-len为1000
127.0.0.1:6379> CONFIG SET slowlog-max-len 1000
OK
参考:http://redisdoc.com/server/slowlog.html
 


三、SlowLog 查看


1、查看slowlog总条数
127.0.0.1:6379> SLOWLOG LEN 
(integer) 4

2、查看slowlog
127.0.0.1:6379> SLOWLOG GET
1) 1) (integer) 25
2) (integer) 1440057769
3) (integer) 6
4) 1) "SLOWLOG"
2) "LEN"
2) 1) (integer) 24
2) (integer) 1440057756
3) (integer) 36
4) 1) "CONFIG"
2) "GET"
3) "slowlog-log-slower-than"
3) 1) (integer) 23
2) (integer) 1440057752
3) (integer) 11
4) 1) "CONFIG"
2) "SET"
3) "slowlog-log-slower-than"
4) "1"
4) 1) (integer) 22
2) (integer) 1440057493
3) (integer) 27
4) 1) "CONFIG"
2) "GET"
3) "slowlog-log-slower-than"
5) 1) (integer) 21
2) (integer) 1440057133
3) (integer) 7
4) 1) "monitor"
如果要获取指定的条数可以使用SLOWLOG GET N命令
127.0.0.1:6379> SLOWLOG GET 1
1) 1) (integer) 26 // slowlog唯一编号id
2) (integer) 1440057815 // 查询的时间戳
3) (integer) 47 // 查询的耗时(微秒),如表示本条命令查询耗时47微秒
4) 1) "SLOWLOG" // 查询命令,完整命令为 SLOWLOG GET,slowlog最多保存前面的31个key和128字符
2) "GET"

ERROR 1 (HY000): Can't create/write to file '/sql/cluster_user.sql' (Errcode: 13)

being 发表了文章 • 0 个评论 • 556 次浏览 • 2016-07-03 21:27 • 来自相关话题

使用outfile方法把查询结果导出:
select * from db_main.cluster_user into outfile '/sql/cluster_user.sql'错误如下:
ERROR 1 (HY000): Can't create/write to file '/sql/cluster_user.sql' (Errcode: 13)看到Can't create/write 我想大家应该一般首先想到的是权限的问题。要不是selinux的问题,要不就是目录mysql没有写权限。
 
网上说把文件导出到/tmp目录就可以,我试了一下是OK的,这是为什么呢?
 
因为select into outfile的命令是mysql的daemon来负责写文件操作的,需要对文件具有写的权限,而/sql目录的权限为755,mysql不具有对文件写的权限,所以就报不能create/write了,而/tmp是777的权限,所以也就是为什么能够写入的原因。 查看全部
使用outfile方法把查询结果导出:
select * from db_main.cluster_user into outfile '/sql/cluster_user.sql'
错误如下:
ERROR 1 (HY000): Can't create/write to file '/sql/cluster_user.sql' (Errcode: 13)
看到Can't create/write 我想大家应该一般首先想到的是权限的问题。要不是selinux的问题,要不就是目录mysql没有写权限。
 
网上说把文件导出到/tmp目录就可以,我试了一下是OK的,这是为什么呢?
 
因为select into outfile的命令是mysql的daemon来负责写文件操作的,需要对文件具有写的权限,而/sql目录的权限为755,mysql不具有对文件写的权限,所以就报不能create/write了,而/tmp是777的权限,所以也就是为什么能够写入的原因。

安装配置Haproxy代理MySQL Galera集群

koyo 发表了文章 • 0 个评论 • 959 次浏览 • 2016-07-02 13:51 • 来自相关话题

这篇文章,我讲介绍如何为MariaDB Galera 集群添加负载均衡,大致步骤如下:
确认所有Galera节点作为一个单一集群运行(所有节点都是主节点而且相互同步)
[]安装Haproxy(你可以安装到独立的服务器或者应用服务器)[/][]配置集群监控检测脚本,用于检测每个backend server服务器健康[/][]配置Haproxy统计页面[/][]将应用程序指向Haproxy[/]
 
大致架构图如下:




 
配置集群检测脚本
**本节中所描述的步骤应该是所有数据库节点上执行,除非另有指定。
 
1、首先,我们需要配置后端健康检查报告。 我们将使用现有的Percona的clustercheck脚本 。 拿到脚本,并把它放在/usr/local/bin目录运行以下命令:
$ git clone https://github.com/olafz/percona-clustercheck
$ cp percona-clustercheck/clustercheck /usr/local/bin
 2、 clustercheck脚本执行通过监控MySQL的几个变量/状态 Galera节点上定期检查。 它会产生一个相应的HTTP返回代码一个简单的HTML输出(无论是503 - 服务不可用或200 - OK)。 为了让事情更容易为HAProxy的触发脚本,并获得后端的最新状态,我们必须让它侦听的端口。 我们可以使用xinetd打开脚本到一个服务进程,并使其听一个自定义的端口,在这种情况下,我将使用9200。
 
创建一个名为/etc/xinet.d/mysqlchk的新文件,并添加以下几行:
# default: on
# description: mysqlchk
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/local/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}
3、添加Mysqlchk服务到xinetd
echo 'mysqlchk 9200/tcp # MySQL check' >> /etc/services
4、默认情况下,该脚本将使用MySQL用户名为“clustercheckuser”,密码为“clustercheckpassword!”。 我们需要确保这个MySQL用户与对应的密码存在的脚本将能够执行健康检查之前。 运行下面的DB节点之一(Galera应该复制该语句到其他节点)DDL语句:
mysql> GRANT PROCESS ON [i].[/i] TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';
mysql> FLUSH PRIVILEGES;你可以改变clustercheck script的32,33行,这篇文章我们使用默认用户名和密码。
 
5、验证脚本返回一个正确的值
$ /usr/local/bin/clustercheck > /dev/null
$ echo $?
0如果DB节点在已同步,你应该得到0,否则1应该是输出。 后端健康检查配置。
 
安装Haproxy
1、最简单的安装方法就是使用(yum/apt)包管理器,然而,强烈建议使用上HAProxy的网站提供的最新版本。无论哪种方式,我下面都会介绍到。
 
A)如果您选择通过软件包管理器安装HAProxy的:
$ yum install haproxy # Redhat/CentOS
$ sudo apt-get install haproxy # Debian/Ubuntu
B)通过Haproxy官网下载源码
$ yum install php-curl gcc make # Redhat/CentOS
$ apt-get install php5-curl gcc make # Debian/Ubuntu
$ wget http://www.haproxy.org/download/1.5/src/
$ tar xvzfz
$ cd
$ make TARGET=linux26
$ cp -f haproxy /usr/sbin/haproxy从源代码(选项B)安装带有没有初始化脚本。所以你需要手动的通过命令行启动。
 
配置HAproxy
现在,我们已经安装了HAProxy的。 我们需要将其配置为在端口3307监听MySQL服务,并执行后端健康检查。 在/etc/haproxy/haproxy.cfg,确保以下行存在:
global
pidfile /var/run/haproxy.pid
daemon
user haproxy
group haproxy
stats socket /var/run/haproxy.socket user haproxy group haproxy mode 600 level admin

maxconn 8192
spread-checks 3
quiet
defaults
mode tcp
option dontlognull
option tcp-smart-accept
option tcp-smart-connect
retries 3
option redispatch
maxconn 8192
timeout check 3500ms
timeout queue 3500ms
timeout connect 3500ms
timeout client 10800s
timeout server 10800s

userlist STATSUSERS
group admin users admin
user admin insecure-password admin
user stats insecure-password yourpassword

listen admin_page 0.0.0.0:9600
mode http
stats enable
stats refresh 60s
stats uri /
acl AuthOkay_ReadOnly http_auth(STATSUSERS)
acl AuthOkay_Admin http_auth_group(STATSUSERS) admin
stats http-request auth realm admin_page unless AuthOkay_ReadOnly

listen mysql_3307
bind *:3307
mode tcp
timeout client 10800s
timeout server 10800s
balance leastconn
option httpchk
option allbackups
default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100
server db1 10.0.0.187:3306 check
server db2 10.0.0.188:3306 check
server db3 10.0.0.189:3306 check现在开机启用该服务,让其工作。
 
RHEL/CentOS 6:
$ chkconfig haproxy on # RHEL6
$ service haproxy start # RHEL6
Ubuntu 14.04 and lower, Debian 7 and lower:
$ update-rc.d haproxy defaults
$ sudo service haproxy start
RHEL/CentOS 7, Debian 8, Ubuntu 15.04:
$ systemctl enable haproxy
$ systemctl start haproxy
 
验证Haproxy端口监听是否正常:
sudo netstat -tulpn | grep haproxy
tcp 0 0 0.0.0.0:9600 0.0.0.0:* LISTEN 370/haproxy
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 370/haproxy
 3307是MySQL的负载平衡端口,而9600是HAProxy的统计页面。 您可以登录访问http检查状态:// haproxy_ip_address:9600 /,以用户名“管理员”和密码登录“你的密码”作为配置的内部haproxy.cfg。 你应该看到类似下面的内容:




现在你可以把你的应用程序或者Mysql的客户端执行harproxy代理的3307端口,用于Mysql的负载均衡和故障自动转移。 查看全部
这篇文章,我讲介绍如何为MariaDB Galera 集群添加负载均衡,大致步骤如下:
确认所有Galera节点作为一个单一集群运行(所有节点都是主节点而且相互同步)
    []安装Haproxy(你可以安装到独立的服务器或者应用服务器)[/][]配置集群监控检测脚本,用于检测每个backend server服务器健康[/][]配置Haproxy统计页面[/][]将应用程序指向Haproxy[/]

 
大致架构图如下:
galera_haproxy_secaserver.png

 
配置集群检测脚本
**本节中所描述的步骤应该是所有数据库节点上执行,除非另有指定。
 
1、首先,我们需要配置后端健康检查报告。 我们将使用现有的Percona的clustercheck脚本 。 拿到脚本,并把它放在/usr/local/bin目录运行以下命令:
$ git clone https://github.com/olafz/percona-clustercheck
$ cp percona-clustercheck/clustercheck /usr/local/bin

 2、 clustercheck脚本执行通过监控MySQL的几个变量/状态 Galera节点上定期检查。 它会产生一个相应的HTTP返回代码一个简单的HTML输出(无论是503 - 服务不可用或200 - OK)。 为了让事情更容易为HAProxy的触发脚本,并获得后端的最新状态,我们必须让它侦听的端口。 我们可以使用xinetd打开脚本到一个服务进程,并使其听一个自定义的端口,在这种情况下,我将使用9200。
 
创建一个名为/etc/xinet.d/mysqlchk的新文件,并添加以下几行:
# default: on
# description: mysqlchk
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/local/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}

3、添加Mysqlchk服务到xinetd
echo 'mysqlchk      9200/tcp    # MySQL check' >> /etc/services

4、默认情况下,该脚本将使用MySQL用户名为“clustercheckuser”,密码为“clustercheckpassword!”。 我们需要确保这个MySQL用户与对应的密码存在的脚本将能够执行健康检查之前。 运行下面的DB节点之一(Galera应该复制该语句到其他节点)DDL语句:
mysql> GRANT PROCESS ON [i].[/i] TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';
mysql> FLUSH PRIVILEGES;
你可以改变clustercheck script的32,33行,这篇文章我们使用默认用户名和密码。
 
5、验证脚本返回一个正确的值
$ /usr/local/bin/clustercheck > /dev/null
$ echo $?
0
如果DB节点在已同步,你应该得到0,否则1应该是输出。 后端健康检查配置。
 
安装Haproxy
1、最简单的安装方法就是使用(yum/apt)包管理器,然而,强烈建议使用上HAProxy的网站提供的最新版本。无论哪种方式,我下面都会介绍到。
 
A)如果您选择通过软件包管理器安装HAProxy的:
$ yum install haproxy # Redhat/CentOS
$ sudo apt-get install haproxy # Debian/Ubuntu

B)通过Haproxy官网下载源码
$ yum install php-curl gcc make # Redhat/CentOS
$ apt-get install php5-curl gcc make # Debian/Ubuntu
$ wget http://www.haproxy.org/download/1.5/src/
$ tar xvzfz
$ cd
$ make TARGET=linux26
$ cp -f haproxy /usr/sbin/haproxy
从源代码(选项B)安装带有没有初始化脚本。所以你需要手动的通过命令行启动。
 
配置HAproxy
现在,我们已经安装了HAProxy的。 我们需要将其配置为在端口3307监听MySQL服务,并执行后端健康检查。 在/etc/haproxy/haproxy.cfg,确保以下行存在:
global
pidfile /var/run/haproxy.pid
daemon
user haproxy
group haproxy
stats socket /var/run/haproxy.socket user haproxy group haproxy mode 600 level admin

maxconn 8192
spread-checks 3
quiet
defaults
mode tcp
option dontlognull
option tcp-smart-accept
option tcp-smart-connect
retries 3
option redispatch
maxconn 8192
timeout check 3500ms
timeout queue 3500ms
timeout connect 3500ms
timeout client 10800s
timeout server 10800s

userlist STATSUSERS
group admin users admin
user admin insecure-password admin
user stats insecure-password yourpassword

listen admin_page 0.0.0.0:9600
mode http
stats enable
stats refresh 60s
stats uri /
acl AuthOkay_ReadOnly http_auth(STATSUSERS)
acl AuthOkay_Admin http_auth_group(STATSUSERS) admin
stats http-request auth realm admin_page unless AuthOkay_ReadOnly

listen mysql_3307
bind *:3307
mode tcp
timeout client 10800s
timeout server 10800s
balance leastconn
option httpchk
option allbackups
default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100
server db1 10.0.0.187:3306 check
server db2 10.0.0.188:3306 check
server db3 10.0.0.189:3306 check
现在开机启用该服务,让其工作。
 
RHEL/CentOS 6:
$ chkconfig haproxy on # RHEL6
$ service haproxy start # RHEL6

Ubuntu 14.04 and lower, Debian 7 and lower:
$ update-rc.d haproxy defaults
$ sudo service haproxy start

RHEL/CentOS 7, Debian 8, Ubuntu 15.04:
$ systemctl enable haproxy
$ systemctl start haproxy

 
验证Haproxy端口监听是否正常:
sudo netstat -tulpn | grep haproxy
tcp 0 0 0.0.0.0:9600 0.0.0.0:* LISTEN 370/haproxy
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 370/haproxy

 3307是MySQL的负载平衡端口,而9600是HAProxy的统计页面。 您可以登录访问http检查状态:// haproxy_ip_address:9600 /,以用户名“管理员”和密码登录“你的密码”作为配置的内部haproxy.cfg。 你应该看到类似下面的内容:
haproxyStatus.png

现在你可以把你的应用程序或者Mysql的客户端执行harproxy代理的3307端口,用于Mysql的负载均衡和故障自动转移。

设置MySQL复制最好的方式

koyo 发表了文章 • 0 个评论 • 533 次浏览 • 2016-07-02 00:57 • 来自相关话题

如上图案例,在Centos5 版本的系统的2台服务器上安装了Mysql5。假设服务器Server1是Mysql Master节点并且有一个叫db1的数据库,服务器Server2是Mysql Slave节点,并且什么数据库都没有。
 
在这个实例使用的变量如下:

Server1 IP=192.168.1.1
Server2 IP=192.168.1.2
Database name=db1
Replication user=replicator
Replication user password=slavepass

首先我们配置服务器Server1,登录服务器并进行如下操作。
 
1、登录服务器Server1,编辑Mysql配置文件/etc/my.cnf,在[mysqld]下面添加如下配置:
[mysqld]
server-id=100
log-bin=master-bin2、重启Mysql服务
service mysqld restart3、创建用于Mysql Slave服务器连接Mysql Master复制用户
mysql> GRANT REPLICATION SLAVE on [i].[/i] to 'replicator'@'%' IDENTIFIED BY 'slavepass';4、备份数据库,提供给Slave Server使用
mysqldump -u[root] -p[password] --master-data --single-transaction db1 > db1.sql5、把备份出来的数据库文件通过网络或者移动存储设备传输到Slave Server。
 
接下来配置服务器Server2,确保Master Mysql备份数据库文件已经放置到Server2服务器上,我们把它放到/root目录下。
 
1、登录服务器Server2,编辑Mysql配置文件/etc/my.cnf,在[mysqld]下面添加如下配置:
[mysqld]
server-id=101
replicate-wild-do-table=db1.%2、重启Mysql服务器
service mysqld restart3、导入备份的数据库文件,并且开启Slave服务器复制进程
mysql> CREATE DATABASE db1;
mysql> USE db1;
mysql> SOURCE /root/db1.sql;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='slavepass';
mysql> START SLAVE;4、检查Slave正常工作已否
mysql> SHOW SLAVE STATUS\G确保你看到如下内容就好
***********************[i][b][/i] 1. row [/b]*************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes以上案例适合最初创建Mysql集群,如果有多个数据库,则需要从主库导入多个数据库数据到Slave服务器,然后开始做复制操作。
 
翻译原文:http://blog.secaserver.com/2011/06/the-best-way-to-setup-mysq-replication/  查看全部
mysql_replicate.jpg

如上图案例,在Centos5 版本的系统的2台服务器上安装了Mysql5。假设服务器Server1是Mysql Master节点并且有一个叫db1的数据库,服务器Server2是Mysql Slave节点,并且什么数据库都没有。
 
在这个实例使用的变量如下:


Server1 IP=192.168.1.1
Server2 IP=192.168.1.2
Database name=db1
Replication user=replicator
Replication user password=slavepass


首先我们配置服务器Server1,登录服务器并进行如下操作。
 
1、登录服务器Server1,编辑Mysql配置文件/etc/my.cnf,在[mysqld]下面添加如下配置:
[mysqld]
server-id=100
log-bin=master-bin
2、重启Mysql服务
service mysqld restart
3、创建用于Mysql Slave服务器连接Mysql Master复制用户
mysql> GRANT REPLICATION SLAVE on [i].[/i] to 'replicator'@'%' IDENTIFIED BY 'slavepass';
4、备份数据库,提供给Slave Server使用
mysqldump -u[root] -p[password] --master-data --single-transaction db1 > db1.sql
5、把备份出来的数据库文件通过网络或者移动存储设备传输到Slave Server。
 
接下来配置服务器Server2,确保Master Mysql备份数据库文件已经放置到Server2服务器上,我们把它放到/root目录下。
 
1、登录服务器Server2,编辑Mysql配置文件/etc/my.cnf,在[mysqld]下面添加如下配置:
[mysqld]
server-id=101
replicate-wild-do-table=db1.%
2、重启Mysql服务器
service mysqld restart
3、导入备份的数据库文件,并且开启Slave服务器复制进程
mysql> CREATE DATABASE db1;
mysql> USE db1;
mysql> SOURCE /root/db1.sql;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='slavepass';
mysql> START SLAVE;
4、检查Slave正常工作已否
mysql> SHOW SLAVE STATUS\G
确保你看到如下内容就好
***********************[i][b][/i] 1. row [/b]*************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上案例适合最初创建Mysql集群,如果有多个数据库,则需要从主库导入多个数据库数据到Slave服务器,然后开始做复制操作。
 
翻译原文:http://blog.secaserver.com/2011/06/the-best-way-to-setup-mysq-replication/ 

Mysql多线程复制方案

koyo 发表了文章 • 0 个评论 • 553 次浏览 • 2016-06-27 22:03 • 来自相关话题

背景    
Mysql的复制方案为一个进程负责读取复制数据,另一个进程负责执行sql,这种方案很好的保护了数据的安全和准确性,但效率偏低。当主机的操作量比较大的时候,会引起数据复制延迟。已经严重的影响了应用。
 
方案
利用mysql的复制协议,采用锁表方式,来进行多线程复制。以下为方案的详细信息。
数据流程图:




流程图说明:
[]利用mysql复制协议,从主机获得复制数据。[/][]复制进程负责按事物写进共享内存。[/][]管理进程负责根据sql语句,解析出数据库和表名,回写共享内存。并按事物派生一个执行线程。[/][]执行线程在执行sql语句前,搜索当前共享内存中之前的语句是否有同库同表的语句,有就等待,没有就执行。[/]
 
可行性分析:
该方案可以简单的理解为对mysql的操作锁到表这一级,会带来一定的管理成本。
 
目前完成已经完成了DEMO程序。下面是从DEMO采集的一些数据
          1、管理成本平均为一条sql语句1/5000秒,一条sql语句平均为1/100秒左右,管理成本占比2%
          2、在执行一段sql语句,但进程方式执行时间为2分20秒左右,多线程方式为1分10秒左右。
          3、从磁盘io情况看,单进程为20M/秒左右,多线程为60M/秒左右。
          4、锁等待概率为2%左右。
 
难点和可能存在的问题
[]数据的准确性,需要长期的运行观察,通过对帐来检查。[/][]数据的安全性,在发生灾难的时候,如何确保数据操作信息被正确保存并且在灾难消除后可以快速继续复制。[/][]易用性,需要提供和mysql复制功能的同样的操作。[/]
 
结论
从以上数据和分析,可以看出该方案可行。 查看全部
背景    
Mysql的复制方案为一个进程负责读取复制数据,另一个进程负责执行sql,这种方案很好的保护了数据的安全和准确性,但效率偏低。当主机的操作量比较大的时候,会引起数据复制延迟。已经严重的影响了应用。
 
方案
利用mysql的复制协议,采用锁表方式,来进行多线程复制。以下为方案的详细信息。
数据流程图:
mysqldxc.png

流程图说明:
    []利用mysql复制协议,从主机获得复制数据。[/][]复制进程负责按事物写进共享内存。[/][]管理进程负责根据sql语句,解析出数据库和表名,回写共享内存。并按事物派生一个执行线程。[/][]执行线程在执行sql语句前,搜索当前共享内存中之前的语句是否有同库同表的语句,有就等待,没有就执行。[/]

 
可行性分析:
该方案可以简单的理解为对mysql的操作锁到表这一级,会带来一定的管理成本。
 
目前完成已经完成了DEMO程序。下面是从DEMO采集的一些数据
          1、管理成本平均为一条sql语句1/5000秒,一条sql语句平均为1/100秒左右,管理成本占比2%
          2、在执行一段sql语句,但进程方式执行时间为2分20秒左右,多线程方式为1分10秒左右。
          3、从磁盘io情况看,单进程为20M/秒左右,多线程为60M/秒左右。
          4、锁等待概率为2%左右。
 
难点和可能存在的问题
    []数据的准确性,需要长期的运行观察,通过对帐来检查。[/][]数据的安全性,在发生灾难的时候,如何确保数据操作信息被正确保存并且在灾难消除后可以快速继续复制。[/][]易用性,需要提供和mysql复制功能的同样的操作。[/]

 
结论
从以上数据和分析,可以看出该方案可行。

Mysql备份目录mysqldump与mydumper性能对比

koyo 发表了文章 • 0 个评论 • 758 次浏览 • 2016-06-25 22:57 • 来自相关话题

介绍:
mysqldump是mysql官方自带的备份工具,是一个很好用的mysql数据转移工具,具有兼容强强、跨版本等特点。
 
mydumper是一个针对MySQL的高性能多线程备份和恢复工具,它提供了并发备份功能,备份效率有很大提高,并且按照单表进行备份,表恢复更加方便。
 
mydumper主要特性有:
[]轻量级C语言写的[/]
       • 执行速度比mysqldump快10倍
       • 事务性和非事务性表一致的快照(适用于0.2.2以上版本)
       • 快速的文件压缩
       • 支持导出binlog
       • 多线程恢复(适用于0.2.1以上版本)
       • 以守护进程的工作方式,定时快照和连续二进制日志(适用于0.5.0以上版本)
       • 开源 (GNU GPLv3)
 
对比测试结果:
测试方式:分别采用mysqldump分表备份方式和mydumper的方式,mydumper默认4个线程,对单个或多个库进行备份,主要观察备份时间以及备份期间机器的CPU使用情况。

对单个数据库进行备份情况,默认采用4个线程同时备份:




从上图可以看出mydumper的效率基本上为mydumper效率的1.5倍,时间为它的一半。
 
对多个数据库连续备份,总共约8G大小的库




在测试过程中,观察机器的CPU使用情况,采用mysqldump的CPU使用约在98%左右,采用mydumper的情况下CPU使用约在200%-400%之间。所以对于消耗CPU的游戏来说,采用mydumper无疑会增大CPU消耗负担,并且可能对游戏产生影响,所以并不太建议采用mydumper,而如果是专门的数据库并且CPU较为空闲的情况,可以采用,适当增多线程数,可以更快的完成备份。

另外在标配的单盘的机器上测试的时候发下,线程数设置为2的时候的时候,IO已经跑满了,相对于线程数为1的时候速度有显著提升,但是在IO跑满之后,设置线程数为4、8、10,线程数越大,CPU消耗会越大,但是对整个备份时间并没有太大影响了,所以在cpu足够空闲的情况下,IO是影响mydumper性能的瓶颈所在,使用的时候根据实际IO情况合理设置线程数。
  查看全部
介绍:
mysqldump是mysql官方自带的备份工具,是一个很好用的mysql数据转移工具,具有兼容强强、跨版本等特点。
 
mydumper是一个针对MySQL的高性能多线程备份和恢复工具,它提供了并发备份功能,备份效率有很大提高,并且按照单表进行备份,表恢复更加方便。
 
mydumper主要特性有:
    []轻量级C语言写的[/]

       • 执行速度比mysqldump快10倍
       • 事务性和非事务性表一致的快照(适用于0.2.2以上版本)
       • 快速的文件压缩
       • 支持导出binlog
       • 多线程恢复(适用于0.2.1以上版本)
       • 以守护进程的工作方式,定时快照和连续二进制日志(适用于0.5.0以上版本)
       • 开源 (GNU GPLv3)
 
对比测试结果:
测试方式:分别采用mysqldump分表备份方式和mydumper的方式,mydumper默认4个线程,对单个或多个库进行备份,主要观察备份时间以及备份期间机器的CPU使用情况。

对单个数据库进行备份情况,默认采用4个线程同时备份:
backuptype.png

从上图可以看出mydumper的效率基本上为mydumper效率的1.5倍,时间为它的一半。
 
对多个数据库连续备份,总共约8G大小的库
backupdb.png

在测试过程中,观察机器的CPU使用情况,采用mysqldump的CPU使用约在98%左右,采用mydumper的情况下CPU使用约在200%-400%之间。所以对于消耗CPU的游戏来说,采用mydumper无疑会增大CPU消耗负担,并且可能对游戏产生影响,所以并不太建议采用mydumper,而如果是专门的数据库并且CPU较为空闲的情况,可以采用,适当增多线程数,可以更快的完成备份。

另外在标配的单盘的机器上测试的时候发下,线程数设置为2的时候的时候,IO已经跑满了,相对于线程数为1的时候速度有显著提升,但是在IO跑满之后,设置线程数为4、8、10,线程数越大,CPU消耗会越大,但是对整个备份时间并没有太大影响了,所以在cpu足够空闲的情况下,IO是影响mydumper性能的瓶颈所在,使用的时候根据实际IO情况合理设置线程数。
 

Mysql主从复制Seconds_Behind_Master说明

koyo 发表了文章 • 0 个评论 • 591 次浏览 • 2016-06-25 22:35 • 来自相关话题

在MySQL的主从复制环境中,我们可以通过在slave上执行show slave status来查看slave的一些状态信息,其中有一个比较重要的参数Seconds_Behind_Master。然而通常有人会忽略这个参数,下面我就给大家介绍一些这个参数的含义和计算过程。
 
在之前我一直误以为Seconds_Behind_Master是表示slave比master落后多少,如果这个值为0的表示主从已经处于一致了(在非同步模式下,现在官方最多也只在5.5中增加了半同步复制)。但是最近我终于认识到之前的错误理解。
 
首先我们需要明白的一点:Seconds_Behind_Master表示slave上SQL thread与IO thread之间的延迟,我们都知道在MySQL的复制环境中,slave先从master上将binlog拉取到本地(通过IO thread),然后通过SQL thread将binlog重放,而Seconds_Behind_Master表示本地relaylog中未被执行完的那部分的差值。手册上的定义:
In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.所以如果slave拉取到本地的relaylog(实际上就是binlog,只是在slave上习惯称呼relaylog而已)都执行完,此时通过show slave status看到的会是0,那么Seconds_Behind_Master的值为0是否表示主从已经处于一致了呢?
 
答案几乎是否定的!为什么几乎是否定的?因为绝大部分的情况下复制都是异步的,异步就意味着master上的binlog不是实时的发送到slave上,所以即使Seconds_Behind_Master的值为0依然不能肯定主从处于一致,这也是我之前强调非同步复制的原因(现在已经有公司在做同步复制了,比如网易自己实现了VSR,VirtualSynchronized Replication,由于同步复制性能较差,所以网易再实现同步复制的同时还打了group commit的补丁)。所以如果我们要以这个参数来估计主从延迟多久的话至少得在一个比较好的网络环境中,这样才能保证几乎master上的binlog都已经发送到slave上。
 
上面解释了Seconds_Behind_Master这个值的真正含义,那么它的值到底是怎么计算出来的呢?实际上在binlog中每个binlog events都会附上执行时的timestamp,所以在在确定Seconds_Behind_Master的值时MySQL是通过比较当前系统的时间戳与当前SQL thread正在执行的binlog event的上的时间戳做比较,这个差值就是Seconds_Behind_Master的值。也许你会有疑问那要是两台服务器之间的时钟不一致怎么办?确实会存在这种情况,那么此时这个值的可靠性就不大了,手册上对此也进行了说明:
This time difference computation works even if the master and slave do not have identical clock times, provided that the difference,
computed when the slave I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock
skews that can make calculation of Seconds_Behind_Master less reliableSeconds_Behind_Master的值除了是非负数之外还可能是NULL,它是由如下几种情况导致的:
[]SQL thread没运行[/][]IO thread没运行[/][]slave没有连接到master。[/]
 
接下来再简单介绍一下异步复制/半同步复制之间的区别。
 
         异步复制,master上的操作记录binlog的同时不关心binlog是否已经被slave接收。

         半同步复制,master上的操作记录binlog的同时会关心binlog是否被slave接受。但是由于它的处理逻辑问题可能丢一个事务,如下图所示:




这样的处理流程存在一个问题,当存储引擎提交(storage commit)后,此时如果master挂了那么会存在主从不一致,对于这个问题orczhou好像自己对源码进行了修改更改storage commit的顺序来达到一个增强的半同步复制。 查看全部
MySQL的主从复制环境中,我们可以通过在slave上执行show slave status来查看slave的一些状态信息,其中有一个比较重要的参数Seconds_Behind_Master。然而通常有人会忽略这个参数,下面我就给大家介绍一些这个参数的含义和计算过程。
 
在之前我一直误以为Seconds_Behind_Master是表示slave比master落后多少,如果这个值为0的表示主从已经处于一致了(在非同步模式下,现在官方最多也只在5.5中增加了半同步复制)。但是最近我终于认识到之前的错误理解。
 
首先我们需要明白的一点Seconds_Behind_Master表示slave上SQL thread与IO thread之间的延迟,我们都知道在MySQL的复制环境中,slave先从master上将binlog拉取到本地(通过IO thread),然后通过SQL thread将binlog重放,而Seconds_Behind_Master表示本地relaylog中未被执行完的那部分的差值。手册上的定义:
In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.
所以如果slave拉取到本地的relaylog(实际上就是binlog,只是在slave上习惯称呼relaylog而已)都执行完,此时通过show slave status看到的会是0,那么Seconds_Behind_Master的值为0是否表示主从已经处于一致了呢?
 
答案几乎是否定的!为什么几乎是否定的?因为绝大部分的情况下复制都是异步的,异步就意味着master上的binlog不是实时的发送到slave上,所以即使Seconds_Behind_Master的值为0依然不能肯定主从处于一致,这也是我之前强调非同步复制的原因(现在已经有公司在做同步复制了,比如网易自己实现了VSR,VirtualSynchronized Replication,由于同步复制性能较差,所以网易再实现同步复制的同时还打了group commit的补丁)。所以如果我们要以这个参数来估计主从延迟多久的话至少得在一个比较好的网络环境中,这样才能保证几乎master上的binlog都已经发送到slave上。
 
上面解释了Seconds_Behind_Master这个值的真正含义,那么它的值到底是怎么计算出来的呢?实际上在binlog中每个binlog events都会附上执行时的timestamp,所以在在确定Seconds_Behind_Master的值时MySQL是通过比较当前系统的时间戳与当前SQL thread正在执行的binlog event的上的时间戳做比较,这个差值就是Seconds_Behind_Master的值。也许你会有疑问那要是两台服务器之间的时钟不一致怎么办?确实会存在这种情况,那么此时这个值的可靠性就不大了,手册上对此也进行了说明:
This time difference computation works even if the master and slave do not have identical clock times, provided that the difference,
computed when the slave I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock
skews that can make calculation of Seconds_Behind_Master less reliable
Seconds_Behind_Master的值除了是非负数之外还可能是NULL,它是由如下几种情况导致的:
    []SQL thread没运行[/][]IO thread没运行[/][]slave没有连接到master。[/]

 
接下来再简单介绍一下异步复制/半同步复制之间的区别。
 
         异步复制,master上的操作记录binlog的同时不关心binlog是否已经被slave接收。

         半同步复制,master上的操作记录binlog的同时会关心binlog是否被slave接受。但是由于它的处理逻辑问题可能丢一个事务,如下图所示:
CommitSql.jpg

这样的处理流程存在一个问题,当存储引擎提交(storage commit)后,此时如果master挂了那么会存在主从不一致,对于这个问题orczhou好像自己对源码进行了修改更改storage commit的顺序来达到一个增强的半同步复制。

MySQL 复制夯住排查以及原理探讨

koyo 发表了文章 • 0 个评论 • 502 次浏览 • 2016-06-25 21:36 • 来自相关话题

一、引子
研发反应,有台从库和主库不同步。由于业务读操作是针对从库的,数据不同步必定会带来数据的不一致,业务获取的结果会受影响,所以这个问题必须尽快解决。

登上服务器,查看 MySQL 的从库状态,并没有任何报错信息。刷新从库状态,发现状态没有任何变化,Exec_Master_Log_Pos 卡住不动。

二、故障分析
为了安全起见,此文略去 MySQL 版本以及其他可能会带来安全问题的信息,接下来逐步分析问题。

首先查看从库状态mysql> SHOW SLAVE STATUS \G
***********************[i][b][/i] 1. row [/b]*************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: masterIP
Master_User: replUser
Master_Port: masterPort
Connect_Retry: 60
Master_Log_File: binlog.000296
Read_Master_Log_Pos: 364027786
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 250
Relay_Master_Log_File: binlog.000283
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 594374863
Relay_Log_Space: 13803486573
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 256219
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: masterID
1 row in set (0.00 sec)此时的 Slave_IO_State 为 Queueing master event to the relay log,而不是正常状态下的 Waiting for master to send event。刷新多次,状态没有任何变化,Exec_Master_Log_Pos 不变,从而导致 Seconds_Behind_Master 一直不变。
 
接下来查看 PROCESSLIST 状态:mysql> SHOW FULL PROCESSLIST;
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
| 51378 | system user | | NULL | Connect | 1121183 | Waiting for master to send event | NULL |
| 88917 | system user | | NULL | Connect | 245327 | Reading event from the relay log | NULL |
| 106029 | userA | xxx.xxx.xxx.xxx:14057 | NULL | Sleep | 14504 | | NULL |
| 106109 | userA | xxx.xxx.xxx.xxx:15077 | databaseA | Sleep | 79 | | NULL |
| 106110 | userA | xxx.xxx.xxx.xxx:15081 | databaseA | Sleep | 13000 | | NULL |
| 106116 | userB | xxx.xxx.xxx.xxx:15096 | databaseA | Sleep | 357 | | NULL |
| 106117 | userB | xxx.xxx.xxx.xxx:15097 | NULL | Sleep | 12964 | | NULL |
| 106119 | root | localhost | NULL | Query | 0 | NULL | SHOW FULL PROCESSLIST |
| 106126 | userB | xxx.xxx.xxx.xxx:15173 | NULL | Sleep | 12856 | | NULL |
| 106127 | userB | xxx.xxx.xxx.xxx:15180 | databaseA | Sleep | 12849 | | NULL |
| 106766 | userA | xxx.xxx.xxx.xxx:17960 | databaseA | Sleep | 64 | | NULL |
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
11 rows in set (0.00 sec)从以上的结果来看,没有任何异常。

既然从上述信息中得不到任何对排查问题有帮助的信息,那么我们可以试着分析 MySQL 的 binlog,看 Pos 为 594374863 的点发生了什么操作。

分析日志我们可以使用 mysqlbinlog 命令,指定 start-position 为夯住的那个点,并重定向到文件。
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v --start-position="594374863" \
binlog.000283 > /XXX/binlog.sql查看输出结果,发现端倪了,以下是摘抄的部分结果:/[i]!40019 SET @@session.max_insert_delayed_threads=0[/i]/;
/[i]!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0[/i]/;
DELIMITER /[i]![/i]/;
# at 4
#150814 17:43:15 server id 21 end_log_pos 107 Start: binlog v 4, server v x.x.xx-log created 150814 17:43:15
BINLOG '
M7jNVQ8VAAAAZwAAAGsAAAAAAAQANS41LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/[i]![/i]/;
# at 594374863
#150814 18:09:36 server id 21 end_log_pos 594374945 Query thread_id=210702841 exec_time=43 error_code=0
SET TIMESTAMP=1439546976/[i]![/i]/;
SET @@session.pseudo_thread_id=210702841/[i]![/i]/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/[i]![/i]/;
SET @@session.sql_mode=0/[i]![/i]/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/[i]![/i]/;
/[i]!\C utf8 [/i]//[i]![/i]/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/[i]![/i]/;
SET @@session.lc_time_names=0/[i]![/i]/;
SET @@session.collation_database=DEFAULT/[i]![/i]/;
BEGIN
/[i]![/i]/;
# at 594374945
# at 594375036
# at 594376047
# at 594377085
# at 594378123
# at 594379152
# at 594380187
# at 594381165
# at 594382194
# at 594383223
# at 594384252
# at 594385269
# at 594386307
# at 594387282
# at 594388299
# at 594389265
# at 594390270
# at 594391299
# at 594392310
# at 594393327
# at 594394344
# at 594395340
# at 594396336
# at 594397332从以上输出中,我们可以知道,从夯住的那个点开始,binlog 记录的信息就出现了异常,可以推测在主库有大操作。另外,针对出现问题库,查看主库和从库的表数量,发现从库的表数量多于主库,有几个临时表出现。可以推测的,主库有删表的操作,从库同步夯住,导致同步异常,主库删表的操作还没来得及同步到从库。

经过和研发沟通,确认了两点。第一,确实有大操作,程序有大量的批量插入,而且是用的 LOAD DATA LOCAL INFILE;第二,主库确实有删表的操作,这几张表都是临时表。
 
三、故障解决
既然问题找到了,那解决办法自然就有了。既然从库的表多于主库,而且这几张表是临时数据,我们可以过滤掉对这几张表的同步操作。具体思路如下:在主库备份临时表(虽然研发说数据不重要,但还是以防万一,DBA 还是谨慎为好),然后通知研发临时切走从库的流量,修改配置文件,指定 replicate-ignore-table 参数,重启 MySQL。

接下来就是具体的解决步骤,首先备份数据。备份时不加 –master-data 参数和 –single-transaction。究其原因,–master-data 禁用 –lock-tables 参数,在和 –single-transaction 一起使用时会禁用 –lock-all-tables。在备份开始时,会获取全局 read lock。 –single-transaction 参数设置默认级别为 REPEATABLE READ,并且在开始备份时执行 START TRANSACTION。在备份期间, 其他连接不能执行如下语句:ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE。MySQL 同步夯住,如果加了上述参数,mysqldump 也会夯住。mysqldump 会 FLUSH TABLES、LOCK TABLES,如果有 –master-data 参数,会导致 Waiting for table flush。同样,有 –single-transaction 参数,仍然会导致 Waiting for table flush。另外,还可以看到 Waiting for table metadata lock,此时做了 DROP TABLE 的操作。此时可以停掉 MySQL 同步来避免这个问题。

为了保险起见,我们在主库加大 expire_logs_days 参数,避免 binlog 丢失。
mysql> SHOW VARIABLES LIKE '%expire%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 3 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL expire_logs_days=5;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%expire%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 5 |
+------------------+-------+
1 row in set (0.00 sec)接着修改从库的配置文件:vim /xxx/xxxx/xxx/my.cnf在 mysqld 后,加入如下配置:replicate-ignore-table=databaseA.tableA
replicate-ignore-table=databaseA.tableB
replicate-ignore-table=databaseA.tableC
replicate-ignore-table=databaseA.tableD然后重启 MySQL:/xxx/xxx/xxx/xxx/mysqld restart登录 MySQL 从库,查看从库状态,并定时刷新状态,我们可以看到的是,Exec_Master_Log_Pos 在递增,Seconds_Behind_Master 在递减,证明问题已经解决了。mysql> SHOW SLAVE STATUS \G
***********************[i][b][/i] 1. row [/b]*************************
Slave_IO_State: Waiting for master to send event
Master_Host: masterIP
Master_User: replUser
Master_Port: masterPort
Connect_Retry: 60
Master_Log_File: binlog.000319
Read_Master_Log_Pos: 985656691
Relay_Log_File: relaylog.000004
Relay_Log_Pos: 709043542
Relay_Master_Log_File: binlog.000284
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: databaseA.tableA,databaseA.tableB,databaseA.tableC,databaseA.tableD
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 709043399
Relay_Log_Space: 38579192969
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 258490
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: masterID
1 row in set (0.00 sec)需要注意的是,待同步完成后,需要把从库配置文件中增加的 replicate-ignore-table参数注释,并重启 MySQL。
 
四、原理探讨
在主库运行 LOAD DATA LOCAL INFILE,主库和从库时这样同步的:
在主节点:
[]执行 LOAD DATA LOCAL INFILE;[/][]拷贝使用的整个文本文件内容到二进制日志;[/][]添加 LOAD DATA LOCAL INFILE 到最新的二进制日志。[/]

复制所有主库的二进制日志到从库的中继日志;
在从节点:
[]检查中继日志中的文本文件;[/][]从多个中继日志文件中读取所有的块;[/][]文本文件存放在 /tmp 文件夹中;[/][]从中继日志中读取 LOAD DATA LOCAL INFILE;[/][]在 SQL 线程中执行 LOAD DATA LOCAL INFILE。[/]
 
在从节点执行的 1-4 步骤中,IO 线程会呈现 Reading event from the relay log 状态,持续地为下一个 LOAD DATA LOCAL INFILE 命令提取 CSV 行。此时从库会持续落后,一旦从库落后时间较长,会导致 SQL 线程阻塞,呈现 Queueing master event to the relay log 状态,从而复制夯住。




 
五、小结
这样的故障,归根结底还是研发写的程序还有优化的余地。大批量的数据插入,这在 MySQL 中是不推荐使用的。我们可以这样:第一,一条 SQL 语句插入多条数据;第二,在事务中进行插入处理;第三,分批插入,在程序中设置 auto_commit 为 0,分批插入完成后,手动 COMMIT;第四,需要使用 LOAD DATA LOCAL INFILE 时,设置 sync_binlog 为 1。
 
分享原文:https://dbarobin.com/2015/08/22/mysql-replication-hanging/
作者:温国兵老师 查看全部
一、引子
研发反应,有台从库和主库不同步。由于业务读操作是针对从库的,数据不同步必定会带来数据的不一致,业务获取的结果会受影响,所以这个问题必须尽快解决。

登上服务器,查看 MySQL 的从库状态,并没有任何报错信息。刷新从库状态,发现状态没有任何变化,Exec_Master_Log_Pos 卡住不动。

二、故障分析
为了安全起见,此文略去 MySQL 版本以及其他可能会带来安全问题的信息,接下来逐步分析问题。

首先查看从库状态
mysql> SHOW SLAVE STATUS \G
***********************[i][b][/i] 1. row [/b]*************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: masterIP
Master_User: replUser
Master_Port: masterPort
Connect_Retry: 60
Master_Log_File: binlog.000296
Read_Master_Log_Pos: 364027786
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 250
Relay_Master_Log_File: binlog.000283
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 594374863
Relay_Log_Space: 13803486573
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 256219
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: masterID
1 row in set (0.00 sec)
此时的 Slave_IO_State 为 Queueing master event to the relay log,而不是正常状态下的 Waiting for master to send event。刷新多次,状态没有任何变化,Exec_Master_Log_Pos 不变,从而导致 Seconds_Behind_Master 一直不变。
 
接下来查看 PROCESSLIST 状态:
mysql> SHOW FULL PROCESSLIST;
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
| 51378 | system user | | NULL | Connect | 1121183 | Waiting for master to send event | NULL |
| 88917 | system user | | NULL | Connect | 245327 | Reading event from the relay log | NULL |
| 106029 | userA | xxx.xxx.xxx.xxx:14057 | NULL | Sleep | 14504 | | NULL |
| 106109 | userA | xxx.xxx.xxx.xxx:15077 | databaseA | Sleep | 79 | | NULL |
| 106110 | userA | xxx.xxx.xxx.xxx:15081 | databaseA | Sleep | 13000 | | NULL |
| 106116 | userB | xxx.xxx.xxx.xxx:15096 | databaseA | Sleep | 357 | | NULL |
| 106117 | userB | xxx.xxx.xxx.xxx:15097 | NULL | Sleep | 12964 | | NULL |
| 106119 | root | localhost | NULL | Query | 0 | NULL | SHOW FULL PROCESSLIST |
| 106126 | userB | xxx.xxx.xxx.xxx:15173 | NULL | Sleep | 12856 | | NULL |
| 106127 | userB | xxx.xxx.xxx.xxx:15180 | databaseA | Sleep | 12849 | | NULL |
| 106766 | userA | xxx.xxx.xxx.xxx:17960 | databaseA | Sleep | 64 | | NULL |
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
11 rows in set (0.00 sec)
从以上的结果来看,没有任何异常。

既然从上述信息中得不到任何对排查问题有帮助的信息,那么我们可以试着分析 MySQL 的 binlog,看 Pos 为 594374863 的点发生了什么操作。

分析日志我们可以使用 mysqlbinlog 命令,指定 start-position 为夯住的那个点,并重定向到文件。
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v --start-position="594374863" \
binlog.000283 > /XXX/binlog.sql
查看输出结果,发现端倪了,以下是摘抄的部分结果:
/[i]!40019 SET @@session.max_insert_delayed_threads=0[/i]/;
/[i]!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0[/i]/;
DELIMITER /[i]![/i]/;
# at 4
#150814 17:43:15 server id 21 end_log_pos 107 Start: binlog v 4, server v x.x.xx-log created 150814 17:43:15
BINLOG '
M7jNVQ8VAAAAZwAAAGsAAAAAAAQANS41LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/[i]![/i]/;
# at 594374863
#150814 18:09:36 server id 21 end_log_pos 594374945 Query thread_id=210702841 exec_time=43 error_code=0
SET TIMESTAMP=1439546976/[i]![/i]/;
SET @@session.pseudo_thread_id=210702841/[i]![/i]/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/[i]![/i]/;
SET @@session.sql_mode=0/[i]![/i]/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/[i]![/i]/;
/[i]!\C utf8 [/i]//[i]![/i]/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/[i]![/i]/;
SET @@session.lc_time_names=0/[i]![/i]/;
SET @@session.collation_database=DEFAULT/[i]![/i]/;
BEGIN
/[i]![/i]/;
# at 594374945
# at 594375036
# at 594376047
# at 594377085
# at 594378123
# at 594379152
# at 594380187
# at 594381165
# at 594382194
# at 594383223
# at 594384252
# at 594385269
# at 594386307
# at 594387282
# at 594388299
# at 594389265
# at 594390270
# at 594391299
# at 594392310
# at 594393327
# at 594394344
# at 594395340
# at 594396336
# at 594397332
从以上输出中,我们可以知道,从夯住的那个点开始,binlog 记录的信息就出现了异常,可以推测在主库有大操作。另外,针对出现问题库,查看主库和从库的表数量,发现从库的表数量多于主库,有几个临时表出现。可以推测的,主库有删表的操作,从库同步夯住,导致同步异常,主库删表的操作还没来得及同步到从库。

经过和研发沟通,确认了两点。第一,确实有大操作,程序有大量的批量插入,而且是用的 LOAD DATA LOCAL INFILE;第二,主库确实有删表的操作,这几张表都是临时表。
 
三、故障解决
既然问题找到了,那解决办法自然就有了。既然从库的表多于主库,而且这几张表是临时数据,我们可以过滤掉对这几张表的同步操作。具体思路如下:在主库备份临时表(虽然研发说数据不重要,但还是以防万一,DBA 还是谨慎为好),然后通知研发临时切走从库的流量,修改配置文件,指定 replicate-ignore-table 参数,重启 MySQL。

接下来就是具体的解决步骤,首先备份数据。备份时不加 –master-data 参数和 –single-transaction。究其原因,–master-data 禁用 –lock-tables 参数,在和 –single-transaction 一起使用时会禁用 –lock-all-tables。在备份开始时,会获取全局 read lock。 –single-transaction 参数设置默认级别为 REPEATABLE READ,并且在开始备份时执行 START TRANSACTION。在备份期间, 其他连接不能执行如下语句:ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE。MySQL 同步夯住,如果加了上述参数,mysqldump 也会夯住。mysqldump 会 FLUSH TABLES、LOCK TABLES,如果有 –master-data 参数,会导致 Waiting for table flush。同样,有 –single-transaction 参数,仍然会导致 Waiting for table flush。另外,还可以看到 Waiting for table metadata lock,此时做了 DROP TABLE 的操作。此时可以停掉 MySQL 同步来避免这个问题。

为了保险起见,我们在主库加大 expire_logs_days 参数,避免 binlog 丢失。
mysql> SHOW VARIABLES LIKE '%expire%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 3 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL expire_logs_days=5;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%expire%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 5 |
+------------------+-------+
1 row in set (0.00 sec)
接着修改从库的配置文件:
vim /xxx/xxxx/xxx/my.cnf
在 mysqld 后,加入如下配置:
replicate-ignore-table=databaseA.tableA
replicate-ignore-table=databaseA.tableB
replicate-ignore-table=databaseA.tableC
replicate-ignore-table=databaseA.tableD
然后重启 MySQL:
/xxx/xxx/xxx/xxx/mysqld restart
登录 MySQL 从库,查看从库状态,并定时刷新状态,我们可以看到的是,Exec_Master_Log_Pos 在递增,Seconds_Behind_Master 在递减,证明问题已经解决了。
mysql> SHOW SLAVE STATUS \G
***********************[i][b][/i] 1. row [/b]*************************
Slave_IO_State: Waiting for master to send event
Master_Host: masterIP
Master_User: replUser
Master_Port: masterPort
Connect_Retry: 60
Master_Log_File: binlog.000319
Read_Master_Log_Pos: 985656691
Relay_Log_File: relaylog.000004
Relay_Log_Pos: 709043542
Relay_Master_Log_File: binlog.000284
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: databaseA.tableA,databaseA.tableB,databaseA.tableC,databaseA.tableD
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 709043399
Relay_Log_Space: 38579192969
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 258490
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: masterID
1 row in set (0.00 sec)
需要注意的是,待同步完成后,需要把从库配置文件中增加的 replicate-ignore-table参数注释,并重启 MySQL。
 
四、原理探讨
在主库运行 LOAD DATA LOCAL INFILE,主库和从库时这样同步的:
在主节点:
    []执行 LOAD DATA LOCAL INFILE;[/][]拷贝使用的整个文本文件内容到二进制日志;[/][]添加 LOAD DATA LOCAL INFILE 到最新的二进制日志。[/]


复制所有主库的二进制日志到从库的中继日志;
在从节点:
    []检查中继日志中的文本文件;[/][]从多个中继日志文件中读取所有的块;[/][]文本文件存放在 /tmp 文件夹中;[/][]从中继日志中读取 LOAD DATA LOCAL INFILE;[/][]在 SQL 线程中执行 LOAD DATA LOCAL INFILE。[/]

 
在从节点执行的 1-4 步骤中,IO 线程会呈现 Reading event from the relay log 状态,持续地为下一个 LOAD DATA LOCAL INFILE 命令提取 CSV 行。此时从库会持续落后,一旦从库落后时间较长,会导致 SQL 线程阻塞,呈现 Queueing master event to the relay log 状态,从而复制夯住。
mysqlabcopy.jpg

 
五、小结
这样的故障,归根结底还是研发写的程序还有优化的余地。大批量的数据插入,这在 MySQL 中是不推荐使用的。我们可以这样:第一,一条 SQL 语句插入多条数据;第二,在事务中进行插入处理;第三,分批插入,在程序中设置 auto_commit 为 0,分批插入完成后,手动 COMMIT;第四,需要使用 LOAD DATA LOCAL INFILE 时,设置 sync_binlog 为 1。
 
分享原文:https://dbarobin.com/2015/08/22/mysql-replication-hanging/
作者:温国兵老师