安装配置Haproxy代理MySQL Galera集群

koyo 发表了文章 • 0 个评论 • 1221 次浏览 • 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 个评论 • 688 次浏览 • 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 个评论 • 725 次浏览 • 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 个评论 • 991 次浏览 • 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 个评论 • 734 次浏览 • 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 个评论 • 652 次浏览 • 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/
作者:温国兵老师

删除清空Mysql表中数据记录

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

删除清空表数据有两种方法分别是delete和truncate,语法如下:
delete from Table_Name;
truncate table Table_Name;不带where参数的delete语句可以删除mysql表中所有内容,使用truncate table也可以清空mysql表中所有内容。
 
效率上truncate比delete快,但truncate删除后不记录mysql日志,不可以恢复数据。
 
delete的效果有点像将mysql表中所有记录一条一条删除到删完,而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表。 查看全部
删除清空表数据有两种方法分别是delete和truncate,语法如下:
delete from Table_Name;
truncate table Table_Name;
不带where参数的delete语句可以删除mysql表中所有内容,使用truncate table也可以清空mysql表中所有内容。
 
效率上truncate比delete快,但truncate删除后不记录mysql日志,不可以恢复数据。
 
delete的效果有点像将mysql表中所有记录一条一条删除到删完,而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表。

Mysql通过binlog恢复数据

koyo 发表了文章 • 0 个评论 • 755 次浏览 • 2016-06-24 23:49 • 来自相关话题

如果有时候你的数据库被误删除了,可以通过binlog来恢复。
 Mysql开启binlog记录如下:
[mysqld]
log-bin=/data/mysqldir/binlog/mysql-binlog //定义binlog路径及名称用mysqlbinlog可以查看binlog日志里的信息
1、根据位置恢复
# mysqlbinlog --start-position=144 --stop-position=240 mysql-binglog.000002 > /data/sql/pos.sql然后导入就好
mysql> source /data/sql/pos.sql
2、根据日期
# ./mysqlbinlog --start-date="2016-06-23 19:30:07" --stop-date="2016-06-24 17:21:28" mysql-binlog.000003 > /root/sql/time.sql导入数据
mysql> source /root/sql/time.sqlOR
#./mysqlbinlog --start-date=
"2016-06-23 19:30:07" --stop-date="2016-06-24 17:21:28" mysql-binlog.000003 |mysql -u root -p'admin@login'如果有多个binlog文件,中间用空格隔开,打上完全路径。 查看全部
如果有时候你的数据库被误删除了,可以通过binlog来恢复。
 Mysql开启binlog记录如下:
[mysqld]
log-bin=/data/mysqldir/binlog/mysql-binlog //定义binlog路径及名称
用mysqlbinlog可以查看binlog日志里的信息
1、根据位置恢复
# mysqlbinlog --start-position=144 --stop-position=240 mysql-binglog.000002 > /data/sql/pos.sql
然后导入就好
mysql> source /data/sql/pos.sql

2、根据日期
# ./mysqlbinlog --start-date="2016-06-23 19:30:07" --stop-date="2016-06-24 17:21:28" mysql-binlog.000003  > /root/sql/time.sql
导入数据
mysql> source /root/sql/time.sql
OR
#./mysqlbinlog --start-date=
"2016-06-23 19:30:07" --stop-date="2016-06-24 17:21:28" mysql-binlog.000003 |mysql -u root -p'admin@login'
如果有多个binlog文件,中间用空格隔开,打上完全路径。

Mysql的log_slave_updates参数说明

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

架构说明:最近部署了MySQL的集群环境,详细情况如上图所示MMA和MMB为主主复制,MMA和MSA为主从复制;在测试的过程中发现了以下问题:
[]MMA和MMB的主主复制没有问题(从MMA写入数据能同步到MMB,从MMB写入数据也能够同步到MMA);[/][]MMA和MSA主从复制也没有问题,当从MMA写入的时候,数据可以写入到MSA;[/][]但是当从MMB写入的时候,数据就不能写入到MSA;[/]
 
问题原因是:MMA Mysql参数log_slave_updates参数的状态为NO。
 
MySQL的官网说明如下:
Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the --log-slave-updates option without also starting the server with the --log-bin option, and would fail with an error; in MySQL 5.5, only a warning is generated. (Bug #44663) --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:A -> B -> C
Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.
[]MMA从MMB同步数据过来的时候,log_slave_updates参数用来控制MMA是否把所有的操作写入到binary log,默认的情况下mysql是关闭的;[/]
[]MSA数据的更新需要通过读取到MMA的binary log才能进行更新,当从MMB写入数据的时候MMA是没有写binary log的,所以MRA也就没有更新操作。[/]
 
问题的解决方法:




log_slave_updates:默认值为OFF;    Dynamic Variable:NO 
 
处理方法:修改配置文件my.cnf,在mysql数据库MMA的配置文件[mysqld]下增加一行log_slave_updates=1,然后暂停所有数据库的同步> stop slave;  最后重启MMA数据库让参数生效,打开所有数据库的同步> start slave;
 
总结:
设置完该参数后,数据库的架构MMA和MMB为主主同步,MSA通过MMA进行主从同步就完善了!
应用的写操作中MMB上面进行,读操作中MSA上面进行(如果读操作很多的话,可以在MMA上面架设多台只读数据库),当MMB发生故障后,系统的写操作自动迁移到MMA上面。这种架构基本可以保证大部分公司的应用需求。
  查看全部
mysqlab.png

架构说明:最近部署了MySQL的集群环境,详细情况如上图所示MMA和MMB为主主复制,MMA和MSA为主从复制;在测试的过程中发现了以下问题
    []MMA和MMB的主主复制没有问题(从MMA写入数据能同步到MMB,从MMB写入数据也能够同步到MMA);[/][]MMA和MSA主从复制也没有问题,当从MMA写入的时候,数据可以写入到MSA;[/][]但是当从MMB写入的时候,数据就不能写入到MSA;[/]

 
问题原因是:MMA Mysql参数log_slave_updates参数的状态为NO。
 
MySQL的官网说明如下:
Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the --log-slave-updates option without also starting the server with the --log-bin option, and would fail with an error; in MySQL 5.5, only a warning is generated. (Bug #44663) --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:
A -> B -> C
Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

    []MMA从MMB同步数据过来的时候,log_slave_updates参数用来控制MMA是否把所有的操作写入到binary log,默认的情况下mysql是关闭的;[/]

    []MSA数据的更新需要通过读取到MMA的binary log才能进行更新,当从MMB写入数据的时候MMA是没有写binary log的,所以MRA也就没有更新操作。[/]

 
问题的解决方法:
slaveupdate.png

log_slave_updates:默认值为OFF;    Dynamic Variable:NO 
 
处理方法:修改配置文件my.cnf,在mysql数据库MMA的配置文件[mysqld]下增加一行log_slave_updates=1,然后暂停所有数据库的同步> stop slave;  最后重启MMA数据库让参数生效,打开所有数据库的同步> start slave;
 
总结:
设置完该参数后,数据库的架构MMA和MMB为主主同步,MSA通过MMA进行主从同步就完善了!
应用的写操作中MMB上面进行,读操作中MSA上面进行(如果读操作很多的话,可以在MMA上面架设多台只读数据库),当MMB发生故障后,系统的写操作自动迁移到MMA上面。这种架构基本可以保证大部分公司的应用需求。
 

Mysql的binlog介绍和数据查看

koyo 发表了文章 • 0 个评论 • 880 次浏览 • 2016-06-20 09:44 • 来自相关话题

Binlog介绍
1、binlog,即二进制日志,它记录了数据库上的所有改变.

2、改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.

3、binlog格式
[]基于语句,无法保证所有语句都在从库执行成功,比如update ... limit 1;[/][]基于行,将每一次改动记为binlog中的一行.在执行一个特别复杂的update或者delete操作时,基于行的格式会有优势.[/]
 
查看binlog数据内容
方式一:登录到mysql查看binlog
 
1、只查看第一个binlog文件的内容
show binlog events;2、查看指定binlog文件的内容
show binlog events in 'mysql-bin.000013';3、查看当前正在写入的binlog文件
show master status\G4、获取binlog文件列表
show binary logs;
方式二:用mysqlbinlog工具查看
需要注意的事件:
[]不要查看当前正在写入的binlog文件[/][]不要加--force参数强制访问[/][]如果binlog格式是行模式的,请加 -vv参数[/]
 
本地查看
1、基于开始/结束时间
mysqlbinlog --start-datetime='2016-06-19 00:00:00' --stop-datetime='2016-09-19 02:01:01' -d 库名 二进制文件2、基于pos值
mysqlbinlog --start-postion=307 --stop-position=1000 -d 库名 二进制文件
远程查看
指定开始/结束时间,并把结果重定向到本地t.binlog文件中.
mysqlbinlog -u username -p password -hMysql_MasterA -P3306 \
--read-from-remote-server --start-datetime='2016-06-10 23:00:00' --stop-datetime='2016-06-10 23:30:00' mysql-bin.000003 > t.binlog 查看全部
Binlog介绍
1、binlog,即二进制日志,它记录了数据库上的所有改变.

2、改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.

3、binlog格式
    []基于语句,无法保证所有语句都在从库执行成功,比如update ... limit 1;[/][]基于行,将每一次改动记为binlog中的一行.在执行一个特别复杂的update或者delete操作时,基于行的格式会有优势.[/]

 
查看binlog数据内容
方式一:登录到mysql查看binlog
 
1、只查看第一个binlog文件的内容
show binlog events;
2、查看指定binlog文件的内容
show binlog events in 'mysql-bin.000013';
3、查看当前正在写入的binlog文件
show master status\G
4、获取binlog文件列表
show binary logs;

方式二:用mysqlbinlog工具查看
需要注意的事件:
    []不要查看当前正在写入的binlog文件[/][]不要加--force参数强制访问[/][]如果binlog格式是行模式的,请加 -vv参数[/]

 
本地查看
1、基于开始/结束时间
mysqlbinlog --start-datetime='2016-06-19 00:00:00' --stop-datetime='2016-09-19 02:01:01' -d 库名 二进制文件
2、基于pos值
mysqlbinlog --start-postion=307 --stop-position=1000 -d 库名 二进制文件

远程查看
指定开始/结束时间,并把结果重定向到本地t.binlog文件中.
mysqlbinlog -u username -p password -hMysql_MasterA -P3306 \
--read-from-remote-server --start-datetime='2016-06-10 23:00:00' --stop-datetime='2016-06-10 23:30:00' mysql-bin.000003 > t.binlog