linux 学习笔记-061-Mysql-Mysql 主从相关知识(2)

发布于 2018-04-04  480 次阅读


查看 Mysql 主从复制延迟和数据中断

01:查看 Seconds_Behind_Master 参数的值

Seconds_Behind_Master 参数的值代表着从库和主库的延迟时间,数值越高意味着延迟越大,但是当 Seconds_Behind_Master 参数的值为 0 的时候,并不真正意味着从库已经追上主库

我们都知道从库 relay-log 和主库的 bin-log 里的内容一样,真正和主库有关联的是 io_thread,当主库 I/O 负载很大或网络阻塞时,io_thread 不能及时复制 binlog,而 sql_thread 一直能跟上 io_thread 的脚步,这时 seconds_behind_master 的值是 0,这明显看出,实际上同步是发生了延迟的,所以 Seconds_Behind_Master 参数的值只是一个参考值

MySQL 的复制环境中,slave 先从 master 上将 binlog 拉取到本地(通过 IO thread),然后通过 SQL thread 将 binlog 重放,而 Seconds_Behind_Master 表示本地 relaylog 中未被执行完的那部分的差值

Seconds_Behind_Master 参数的值为以下几种情况时

NULL:表示 io_thread 或 sql_thread 有一个发生故障,就是说该线程的 Running 状态时 No,而非 Yes

0:表示主从复制良好,没有 lag 存在

正值:表示主从已出现延时,数字越大表示从库落后主库越多

负值:很罕见,是一个 BUG,按理说不应该出现

[root@am-02:~#] mysql -uroot -pitsupport.0

mysql> show slave status\G

*************************** 1. row ***************************

        Seconds_Behind_Master: 0

1 row in set (0.00 sec)

02:使用 pt-heartbeat 工具

pt-heartbeat 的思路比较巧妙,它在 master 中插入一条带有当前时间(MySQL 中的 now()函数)的记录到心跳表中,然后,该记录会复制到 slave 中。

slave 根据当前的系统时间戳(Perl 中的 time 函数)减去 heartbeat 表中的记录值来判断主从的延迟情况。

所以,主从服务器的时间要一致!

工作原理:

在主上创建一张 heartbeat 表,按照一定的时间频率更新该表的字段(把时间更新进去)。

连接到从上检查复制的时间记录,和从库的当前系统时间进行比较,得出时间的差异。

首先,当然是安装 pt-heartbeat 工具的包 Percona Toolkit 并把其加入到系统环境变量中了!

[root@am-01:~#] cd /usr/local/src/

[root@am-01:/usr/local/src#] wget https://www.percona.com/downloads/percona-toolkit/3.0.8/binary/tarball/percona-toolkit-3.0.8_x86_64.tar.gz

[root@am-01:/usr/local/src#] tar xvf percona-toolkit-3.0.8_x86_64.tar.gz

[root@am-01:/usr/local/src#] mv percona-toolkit-3.0.8 percona

[root@am-01:/usr/local/src#] cd percona/bin/

[root@am-01:/usr/local/src/percona/bin#] pwd

/usr/local/src/percona/bin

[root@am-01:/usr/local/src/percona/bin#] vim /etc/profile

  export PATH=$PATH:/usr/local/src/percona/bin

[root@am-01:/usr/local/src/percona/bin#] source /etc/profile

主从服务器时间同步

[root@am-01:~#] ntpdate time.pool.aliyun.com

 4 Apr 14:37:35 ntpdate[31339]: step time server 120.25.108.11 offset -28707.542094 sec

[root@am-01:~#] date

2018 年 04 月 04 日 星期三 14:37:43 CST
[root@am-02:~#] ntpdate time.pool.aliyun.com

 4 Apr 14:37:38 ntpdate[6332]: step time server 120.25.108.11 offset -28795.514820 sec

[root@am-02:~#] date

2018 年 04 月 04 日 星期三 14:37:46 CST

在主服务器上创建测试库和 heartbeat 表

[root@am-01:~#] mysql -uroot -pitsupport.0

mysql> create database lai;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| am                 |

| lai                |

| mysql              |

| mysql2             |

| performance_schema |

| test               |

| zrlog              |

+--------------------+

8 rows in set (0.01 sec)

mysql> quit

Bye

#创建 lai 库作为测试库
[root@am-01:~#] pt-heartbeat -h localhost -D lai --user root --password itsupport.0 --create-table --update

Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/src/percona/bin/pt-heartbeat line 3960.

BEGIN failed--compilation aborted at /usr/local/src/percona/bin/pt-heartbeat line 3960.

[root@am-01:~#] yum -y install perl-Digest-MD5.x86_64

#出现错误,这是需要安装 perl-Digest-MD5 包
[root@am-01:~#] pt-heartbeat -h localhost -D lai --user root --password itsupport.0 --create-table --update

Cannot connect to MySQL because the Perl DBI module is not installed or not found.  Run 'perl -MDBI' to see the directories that Perl searches for DBI.  If DBI is not installed, try:

  Debian/Ubuntu  apt-get install libdbi-perl

  RHEL/CentOS    yum install perl-DBI

  OpenSolaris    pkg install pkg:/SUNWpmdbi

[root@am-01:~#] yum -y install perl-DBI

#继续提示错误,继续安装 perl-DBI 包
[root@am-01:~#] pt-heartbeat -h localhost -D lai --user root --password itsupport.0 --create-table --update

Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found.  Run 'perl -MDBD::mysql' to see the directories that Perl searches for DBD::mysql.  If DBD::mysql is not installed, try:

  Debian/Ubuntu  apt-get install libdbd-mysql-perl

  RHEL/CentOS    yum install perl-DBD-MySQL

  OpenSolaris    pgk install pkg:/SUNWapu13dbd-mysql

[root@am-01:~#] yum -y install perl-DBD-MySQL

#继续安装 perl-DBD-MySQL 包
[root@am-01:~#] pt-heartbeat -h localhost -D lai --user root --password itsupport.0 --create-table

DBI connect('lai;host=localhost;mysql_read_default_group=client','',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /usr/local/src/percona/bin/pt-heartbeat line 2888.

[root@am-01:~#] mkdir /var/lib/mysql

[root@am-01:~#] ln -s /tmp/mysql.sock /var/lib/mysql/

#还是提示错误,这次的意思是找不到 sock 文件,那么我就做一个软链接过去
[root@am-01:~#] pt-heartbeat -h localhost -D lai --user root --password itsupport.0 --create-table --update

#这句命令在创建表的同时还在每秒更新表的信息,能看到无输出信息

在从服务器上安装 pt-heartbeat

安装步骤跟主服务器的一样,这里省略

[root@am-02:/usr/local/src#] pt-heartbeat -D lai --monitor -h localhost --master-server-id=240 -u root -p itsupport.0

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

#从服务器上做检测,可以见到结果会持续输出

#0 表示从没有延迟。 [ 0.00s, 0.00s, 0.00s ] 表示 1m,5m,15m 的平均值,我这里可以见到,暂时是没有延迟的

参考资料:

https://www.linuxidc.com/Linux/2016-09/135581.htm

http://f.dataguru.cn/thread-461916-1-1.html

http://www.cnblogs.com/moss_tan_jun/p/6138201.html

深入探讨 Mysql 主从

Mysql 主从常用架构

MySQL 集群,常见方式:主从集群

Master 节点,负责所有的写请求

Slave 节点,负责大部分的读请求

MySQL 主从集群的作用:

MySQL 主从集群,分散访问压力,提升整个系统的可用性,降低大访问量引发的故障率。

常见的主从架构:

一主一从:一个 Master,一个 Slave

一主多从:一个 Master,多个 Slave

图示:

linux 学习笔记-061-Mysql-Mysql 主从相关知识(2)

linux 学习笔记-061-Mysql-Mysql 主从相关知识(2)

Mysql 主从实现方法

linux 学习笔记-061-Mysql-Mysql 主从相关知识(2)

linux 学习笔记-061-Mysql-Mysql 主从相关知识(2)

MySQL 的主从复制:异步单线程。

Master 上 1 个 IO 线程,负责向 Slave 传输 binary log(binlog)

Slave 上 2 个线程:IO 线程和执行 SQL 的线程,其中

IO 线程:将获取的日志信息,追加到 relay log 上

执行 SQL 的线程:检测到 relay log 中内容有更新,则在 Slave 上执行 sql

注:MySQL 5.6.3 开始支持多线程的主从复制,一个数据库一个线程,多个数据库可多个线程

 

MySQL 主从复制是典型的生产者-消费者模型:整体耗时,分为几类

磁盘的读写耗时:步骤 3、步骤 5、步骤 6

网络传输耗时:步骤 4

SQL 执行耗时:步骤 7 (地点:Slave 上 relay log 执行过程)

排队耗时:步骤 3(地点:Master 上 bin log 中排队,生产者-消费者)

对主从架构的定位

提升系统的可用行:Master 宕机后,数据不丢失,可以使用 Slave 临时替换 Master

不要求 Slave 跟 Master 的强一致,而只要求最终一致

通常,对数据一致性要求很高的场景下,并不建议采用:主从结构,分担高并发访问压力

 

要求强一致性可以采取同步复制,但是同步复制对数据库的写性能影响很大

同步复制原理

第一阶段:Master 收到 Client 的写入数据请求,在本地写入数据

第二阶段:Master 收到 Slave 写入成功的消息,再向 Client 返回数据写入成功

主从延迟带来的影响

读写分离失效:读写分离,降低单机压力,提升系统瓶颈上限,如果延迟恶化,则失效

主备容灾失效:主备切换,提升系统可用性,如果延迟恶化(1h 以上),则失效

常见的主从延迟原因及其预防和解决方法

Master 上,大事务,耗时长

Master 上,SQL 执行速度慢

Master 上,批量 DML 操作

Master 上,多线程写入频繁, Slave 单线程速度跟不上

 

如何解决主从延迟问题?

内部解决:减弱主从复制的延迟

外部解决:缓存层,在前端访问和数据库之间,添加缓存,优先从缓存读取,减弱数据库的并发压力,Slave 只作为数据备份,不分担访问流量;

 

减弱主从延迟,采取措施:

细化事务:将大事务拆为小事务,不必要的地方移除事务

提升 SQL 执行速度:优化索引

减少批量操作:批量 DML 的耗时较多,减少不必要的批量 DML

降低多线程大事务并发的概率:优化业务逻辑

提升 Slave 硬件性能、借助中间件,改善主从复制的单线程模式

Mysql 双主

所谓的双主,其实意思就是两台 Mysql 服务器互为主从,关键在于 my.cnf 配置文件的一些选项参数

这里列举一下主要参数:

log-bin = mysql-bin

log-slave-updates = true

replicate-ignore-db = mysql

#主从服务器均需要设置的相同的参数

server-id = 1

auto_increment_offset = 1

auto_increment_increment = 2

#主服务器 A 需要设置的参数

server-id = 2

auto_increment_offset = 2

auto_increment_increment = 2

#主服务器 B 需要设置的参数

 

配置好 my.cnf 配置文件文件后,需要互相添加对方为主服务器

命令:

change master to master_host='对方 IP 地址',master_port=3306,master_user='对方用户名',master_password='对方密码',master_log_file='对方 Filename',master_log_pos=对方 Position 值;

 

最后 A 和 B 均开启 MySQL5.6 的 GTID 功能

change master to MASTER_AUTO_POSITION=1;

 

参考资料:

http://www.cnblogs.com/ygqygq2/p/6045279.html

Mysql 一主多从

Mysql 一主多从的一主多从其实跟一主一从一样的原理,只不过 server-id 不一样而已

在此不作详解

扩展

mysql-proxy 实现读写分离:

http://my.oschina.net/barter/blog/93354

mycat 实现读写分离:

http://www.th7.cn/db/mysql/201708/250280.shtml

atlas 相关:

http://www.oschina.net/p/atlas

mysql 环形主从:

http://ask.apelearn.com/question/11437

cobar 实现分库分表:

http://blog.csdn.net/huoyunshen88/article/details/37927553

mysql 分库分表方案:

http://my.oschina.net/ydsakyclguozi/blog/199498

mysql 架构演变:

http://www.aminglinux.com/bbs/thread-8025-1-1.html

MHA 架构:

http://www.dataguru.cn/thread-457284-1-1.html

比较复杂的 mysql 集群架构:

http://ask.apelearn.com/question/17026