Linux MySQL 学习笔记-，主从复制
1 AB复制原理
-
AB复制是一种数据复制技术，是myslq数据库提供的一种高可用、高性能的解决方案。
-
AB复制的模式：
一主一从 一主多从 双主 多主多从
复制的工作原理：
要想实现ab复制，那么前提是 master上必须开启二进制日志
- 首先master将数据更新记录到二进制日志文件中
- 从slave start开始，slave通过I/O线程向master请求二进制日志文件，slave要知道向谁请求从哪个位置点请求
- master接收到slave的I/O请求之后，就会从相应的位置点开始，给slave传日志
- slave接收到日志后，会写入本地的中继日志中
- slave通过sql线程读取中继日志中的内容，在数据库中执行相应的操作，到此为止，master和slave上的数据一致，之后slave服务器进入等待状态，等待master的后续更新
主从服务器上与复制相关的线程 - 从服务器上的线程
- I/O线程：负责从主服务器上索要二进制日志，并将其存入从服务器额中继日志中
- SQL线程：负责读取从服务器上的中继日志，并对数据库执行相应的操作
- 主服务器上的线程
- binlog dump线程：负责发送二进制日志
2 AB复制配置
2.1 传统方式
2.1.1 准备工作
1.关闭防火墙
2.关闭selinux
3.配置固定ip地址
4.配置好yum源
5.修改主机名
6.配置好ssh双机互信
2.1.2 实验环境
master server：192.168.1.3 master.qf.com
slave server: 192.168.1.4 slave.qf.com
2.1.3 配置过程
master配置
- 安装软件
略 - 修改配置文件
[root@master ~]# vim /etc/my.cnf
[mysqld]
log-bin=binlog #开启二进制日志
server-id=1 #指定服务id
- 重新启动服务
[root@master ~]# /usr/local/mysql/bin/mysqladmin -uroot -p shutdown
[root@master ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql &
- 查看服务是否被监听
[root@master ~]# netstat -tulnp | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3811/mysql
- 授权一个实现复制数据的用户
[root@master ~]# markup
\> grant replication slave on *.* to 'repluser'@'192.168.1.4' identified by '123456';
2.1.4 slave配置
-
安装软件
-
修改配置文件
[root@slave ~]# vim /etc/my.cnf
[mysqld]
server-id=2 #保证和主服务器不一样
- 重启服务
[root@slave ~]# /usr/local/mysql/bin/mysqladmin -uroot -p shutdown
[root@slave ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql &
- 查看服务是否被监听
[root@slave ~]# netstat -tulnp | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3457/mysqld
- 在master上查看日志文件及位置
MariaDB [test]> show master status\G
*************************** 1. row ***************************
File: binlog.000003
Position: 1196
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
- 在slave上配置，需要告诉slave从master的哪个位置开始同步
MariaDB [(none)]> change master to
master_host='192.168.1.3',
master_port=3306,master_user='repluser',
master_password='123456',
master_log_file='binlog.000003',
master_log_pos=1196;
- 启动从服务器
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
- 查看从服务器的状态
MariaDB [(none)]> show slave status\G;
Slave_IO_Running: Yes #确保io和sql线程是yes
Slave_SQL_Running: Yes
- 测试：主从同步是否成功
在master上创建表插入数据
在slave上查看是否有该表
有表示主从同步完成
2.1.5 问题及解决
- SQL重放失败会导致SQL线程挂掉，如果需要跳过这个失败：
1). 停掉slave
mysql> stop slave;
2). 配置要忽略的 event 数量
mysql> set global sql_slave_skip_counter=N; # 将N修改为要忽略的具体数量
3). 启动slave
mysql> start slave;
二、从服务器从主服务器恢复数据过程中主服务器数据发生变化
主服务器配置选项：
sync_binlog = 1 如果开启该选项, My SQL每次在提交事务前会将二进制日志同步到磁盘上,保证在服
务器崩溃时不会丢失事件。如果禁止该选项,服务器会少做一些工作,但二进制日志文
件可能在服务器崩溃时损坏或丢失信息。在一个不需要作为主库的备库上,该选项带来
了不必要的开销。它只适用于二进制日志,而非中继日志。
从服务器配置选项：
read_only = 1 # 只有主从复制用户或有super权限的用户可写（尽量避免给普通用户超级权限）
log_slave_updates # 将SQL重放操作记录到binlog日志，当然这会给从服务器带来额外的开销。
2.2 GFTID方式
GTID即全局事务ID（global transaction identifier）,是MySQL5.6在5.5的基础上的一个新特性, GTID由UUID+TID组成的。
其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增,所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务,并且会补全没有执行的事务）。下面是一个GTID的具体形式：
4e659069-3cd8-11e5-9a49-001c4270714e:1-77
- GTID意义：
引入GTID的意义是什么？
1. 因为清楚了GTID的格式,所以通过UUID可以知道这个事务在哪个实例上提交的。
2. 通过GTID可以极方便的进行复制结构上的故障转移,新主设置。很好的解决了下面这个图（图来自高性能MySQL第10章-10.6.5）的问题:
上图的意思是：
Server1(Master)崩溃,Server2已经跟上了主, Server3没有跟上。
这时要是把Server2提升为主,Server3变成Server2的从,需要在Server3上执行change的时候做一些计算,相当麻烦。
这个问题在5.6的GTID出现后,就非常的简单了。由于同一事务的GTID在所有节点上的值一致,那么根据Server3当前停止点的GTID就能定位到Server2上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现,我们都不需要知道GTID的具体值,直接使用CHANGE MASTER TO MASTER_HOST=‘xxx’, MASTER_AUTO_POSITION命令就可以直接完成failover的工作。
-
原理：
从服务器连接到主服务器之后,把自己执行过的GTID(Executed_Gtid_Set)<SQL线程> 、获取到的GTID(Retrieved_Gtid_Set）<IO线程>发给主服务器,主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候,找出同步最成功的那台从服务器,直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主, 先change到同步最成功的那台从服务器, 等把GTID全部补全了,就可以把它提升为主了。 -
GTID主从环境的搭建与传统方式没有太大区别,需要注意的是：
- 开启GTID需要启用这三个参数(主从都要)：
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
- 在执行change master to的时候也有一点区别:
> change master to master_host='192.168.10.11',master_user='user',master_password='password',master_auto_position=1;
- GTID的优点：
1.一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次
2.GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
3.减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机
3 MySQL主从复制的问题与解决方案
3.1.1 主从复制介绍
Master负责写操作的负载，也就是说一切写的操作都在Master上进行，而读的操作则分摊到Slave上进行。这样一来的可以大大提高读取的效率。
在一般的互联网应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右 ，也就是说大量的数据操作是集中在读的操作，这也就是为什么我们会有多个Slave的原因。
但是为什么要分离读和写呢？熟悉DB的研发人员都知道，写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，从另一个方面有效的提高了读的效率，保证了系统的高可用性。
3.1.2 主从复制形式
● 一主一从
● 主主复制
● 一主多从 #扩展系统读取的性能，因为读是在从库读取的
● 多主一从 #5.7开始支持
● 联级复制
3.1.3 主从同步的延迟问题、原因及解决方案
MySQL的主从同步在实际使用过程中会有从库延迟的问题,那么为什么会有这种问题呢? 如何避免这种问题呢?
情况一: 从服务器配置过低导致延迟
这类延迟场景的出现往往是主节点拥有较大规格的配置，而只读节点却购买了一个最小规格的配置
只读节点的数据为了和主节点保持同步，采用了MySQL binlog复制技术，由一个IO线程和一个SQL线程来完成，IO线程负责将主库的binlog拉取到只读节点，SQL线程负责消费这些binlog日志，这两个线程会消耗掉只读节点的IO资源，所以当只读节点IOPS配置不够的时候，则会导致只读节点的数据出现延迟
- 解决办法: 升级从服务器的配置,让只读节点的配置大于或者等于主节点的配置即可
情况二: 主库的QPS过高导致只读节点延迟
由于只读节点与主库的同步采用的是单线程同步，而主库的压力是并发多线程写入，这样势必会导致只读节点的数据延迟
- 解决办法: 开启只读节点的并行复制 (mysql5.6.3以后支持多线程复制)
拓展：
在MySQL5.6中，引入了并发复制，这个并发复制是数据库级别的，这意味着一个SQL线程可以处理一个数据库的连续事务，而不用等待其它数据库完成。
这个版本的并发复制，可以理解成一个数据库一个SQL线程。
其与并发有关的参数如下：
slave_parallel_workers #worker 线程个数
slave-checkpoint-group #隔多少个事务做一次
checkpointslave-checkpoint-period #隔多长时间做一次
checkpointslave-pending-jobs-size-max #分发给worker的、处于等待状态的event的大小上限
MySQL5.6基于DATABASE级别的并发复制可以解决业务表放在不同的database下同步延迟的问题
但是在实际生产中大部分表还是放在同一个库中的，这种情况即使设置slave_parallel_workers大于0，也无法进行并发。在高并发的情况下，依然会造成主从复制延迟.
MySQL 5.7版本才真正支持“真正”的并行复制功能.在MySQL5.7中，引入了新的并发复制方法，基于LOGICAL_CLOCK的并发复制，可以支持在一个database中，并发执行relaylog中的事务。
相同的二进制日志组在master上提交并行应用到slave节点上，没有跨数据库的限制，并且不需要把数据分割到多个数据库。
要实现这个功能，需要在master节点标记binlog中提交的事务哪些是可以并发执行，虽然的MySQL5.6中已经引入binarylog group commit,但是没有将可并发的事务标记出来。
在MySQL5.7中，已经解决了主从复制延迟的问题，具体配置参数如下：
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
情况三: 主库的DDL语句导致只读节点延迟
可能1:只读节点与主库的DDL同步是串行进行的，如果DDL操作在主库执行时间很长，那么同样在备库也会消耗同样的时间.
比如在主库对一张500W的表添加一个字段耗费了10分钟，那么在只读节点上也同样会耗费10分钟，所以只读节点会延迟600S
可能2：只读节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，读节点表被锁，直到查询结束为止，进而导致了只读节点的数据延迟。
在只读节点上可以通过执行show processlist命令查看连接的状态处于: Waiting for table metadata lock
- 解决办法: 对于可能1，只能说执行操作之前对可能带来的影响要有考量; 对于情况2，可以kill掉只读节点上的大查询进行，就可以恢复只读节点与主节点的数据同步
情况四: 主库执行大事务导致延迟
主库执行了一条insert … select非常大的插入操作，该操作产生了近几百G的binlog文件传输到只读节点，进而导致了只读节点出现应用binlog延迟。
- 解决办法: 将大事务拆分成为小事务进行排量提交，这样只读节点就可以迅速的完成事务的执行，不会造成数据的延迟。
情况五：无主键的表进行DML操作导致延迟
如:mysql> update test set kk=‘fafa01’;
由于表中没有主键，所以导致了每一个事务条目的更新都是全表扫描，如果表中很很多的数据，则备库执行该更新的事务条目的时候，就会出现很多的全表扫描更新；
进一步说明就是，由于表中没有主键，在ROW模式下，每删一条数据都会做全表扫，也就是说一条delete，如果删了10条，会做10次全表扫，所以slave会一直卡住；
- 解决办法: 每张表在设计的时候都加上一个主键
拓展：
主键对于innodb来说，是非常重要的，每张表的设计的时候，都应该把主键默认的加上，不管你需不需要他
主键的设计最好选择自增型的主键
自增主键的好处：
a.自增型主键以利于插入性能的提高；
b.自增型主键设计(int,bigint)可以降低二级索引的空间，提升二级索引的内存命中率；
c.自增型的主键可以减小page的碎片，提升空间和内存的使用
3.1.4 总结
为了避免MySQL主从复制延迟,我们可以从以下几方面入手:
-
数据库设置: 主从同步加速
1).sync_binlog在slave端设置为0
2).log-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志.
3).直接禁用slave端的binlog
4).slave端,如果使用的存储引擎是innodb,innodb_flush_log_at_trx_commit =2 -
架构方面:在架构上做优化,尽量让主库的DDL快速执行,尽量减轻数据库的压力
1).业务的持久化层的实现采用分库架构,mysql服务可平行扩展,分散压力.
2).单个库读写分离,一主多从,主写从读,分散压力.这样从库压力比主库高,保护主库.
3).服务的基础架构在业务和mysql之间加入memcache或者redis的cache层.降低mysql的读压力.
4).不同业务的mysql物理上放在不同机器,分散压力. -
硬件方面:使用比主库更好的硬件设备作为slave
1).采用好服务器,比如4u比2u性能明显好,2u比1u性能明显好.
2).存储用ssd或者盘阵或者san,提升随机写的性能.
3).主从间保证处在同一个交换机下面,并且是万兆环境.
PS:
sync_binlog: 默认值 0
这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性.
sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步.
sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘.
0 - 性能最好,风险最大: 一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失.
1 - 性能最差,安全性最好: 即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据影响最小.
从以往经验和相关测试来看,对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多.
innodb_flush_log_at_trx_commit: 默认值 1
1 - 每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,很费时.特别是使用电池供电缓存(Battery backed up cache)时.
2 - 不写入硬盘而是写入系统缓存.日志仍然会每秒flush到硬盘,所以你一般不会丢失超过1-2秒的更新.
0 - 更快,但安全方面比较差,即使仅MySQL挂了也可能会丢失事务的数据.而值2只会在整个操作系统挂了时才可能丢数据.
3.2 主从复制监控
- 在从服务器上执行show slave status\G, 与主从同步状态相关的参数:
Master_Log_File # SLAVE中的I/O线程当前正在请求的主服务器二进制日志文件的名称
Read_Master_Log_Pos # 在当前的主服务器二进制日志中，SLAVE中的I/O线程已经读取的位置
Relay_Log_File # SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos # 在当前的中继日志中，SQL线程已读取和执行的位置
Relay_Master_Log_File # 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
Slave_IO_Running # I/O线程是否启动并成功地连接到主服务器上
Slave_SQL_Running # SQL线程是否启动
Seconds_Behind_Master # 从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。从库同步延迟情况出现的
- 通常需要监控下面三个参数:
- Slave_IO_Running:Yes表示和主库连接正常并能实施复制工作，No则说明与主库通信异常；
- Slave_SQL_Running:YES表示正常，NO表示执行失败，具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。
- Seconds_Behind_Master:是通过比较sql_thread执行的event的ts和io_thread复制好的event的ts进行比较得到的值:
NULL — 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No，而非Yes。
0 — 该值为零，是我们极为渴望看到的情况，表示主从复制良好。
正值 — 表示主从已经出现延时，数字越大表示从库落后主库越多。
负值 — 几乎很少见，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。
简单来说,就是监控slave同步状态中的:
1. Slave_IO_Running、Slave_SQL_Running状态值,如果都为YES,则表示主从同步;反之,主从不同步.
2. Seconds_Behind_Master的值,如果为0,则表示主从同步不延时,反之同步延时.
上面根据Seconds_Behind_Master的值来判断slave的延迟状态,这么做在大部分情况下尚可接受,但有时候是不够准确的.
Seconds_Behind_Master
是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp进行比较,而得到的这么一个差值.
我们都知道的relay-log和主库的bin-log里面的内容完全一样,在记录sql语句的同时会被记录上当时的ts,所以比较参考的值来自于binlog,
其实主从没有必要与NTP进行同步,也就是说无需保证主从时钟的一致.你也会发现,其实比较真正是发生在io_thread与sql_thread之间,
而io_thread才真正与主库有关联,于是,问题就出来了, 当主库I/O负载很大或是网络阻塞,io_thread不能及时复制binlog(没有中断,也在复制),
而sql_thread一直都能跟上 io_thread的脚步,这时Seconds_Behind_Master的值是0,也就是我们认为的无延时,但是,实际上不是.
这也就是为什么大家要批判用这个参数来监控数据库是否发生延时不准的原因
这个值并不是总是不准,如果当io_thread与master网络很好的情况下,那么该值也是很有价值的.
3.2.1 较为准确的判断主从同步状态:
1)首先看 Relay_Master_Log_File 和 Master_Log_File 是否有差异;
2)如果Relay_Master_Log_File 和 Master_Log_File不一样,那说明存在延迟,需要从MASTER上取得binlog status,判断当前的binlog和MASTER上的差距;
3)如果Relay_Master_Log_File 和 Master_Log_File一样,再看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异,对比SQL线程比IO线程慢了多少个binlog事件
故相对严谨的做法是:
对MASTER和slave同时发起SHOW BINARY LOGS和SHOW slave STATUS\G的请求
判断二者binlog的差异,以及Exec_Master_Log_Pos 和Read_Master_Log_Pos 的差异.
3.2.2 主从延迟监控的其他方法:
mk-heartbeat,Maatkit万能工具包中的一个工具,被认为可以准确判断复制延时的方法.
mk-heartbeat的实现也是借助timestmp的比较实现的,它首先需要保证主从服务器必须要保持一致,通过与相同的一个NTP server同步时钟.
它需要在主库上创建一个heartbeat的表,里面至少有id与ts两个字段,id为server_id,ts就是当前的时间戳 now(),该结构也会被复制到从库上
表建好以后,会在主库上以后台进程的模式去执行一行更新操作的命令,定期去向表中的插入数据,这个周期默认为1秒
同时从库也会在后台执行一个监控命令,与主库保持一致的周期去比较,复制过来记录的ts值与主库上的同一条ts值,差值为0表示无延时,差值越大表示延时的秒数越多.
我们都知道复制是异步的ts不可能完全一致,所以该工具允许半秒的差距,在这之内的差异都可认为无延时.
这个工具就是通过实打实的复制,巧妙的借用timestamp来检查延时.
Reference
- 课堂笔记
- 高性能MySQL(第3版)
482
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
