一、先来描述下线上环境产生的主从同步延迟，宕机问题的现象
3月的某一天，突然接到客户反馈说是大部分页面的新增更新操作都失败了。
二、运维查看定位问题的步骤
系统中一些数据量相对较少的业务数据都放在mysql了。接着运维紧急查看业务日志，然后定位到数据库挂了的问题，再查看binlog发现 目录下的binlog日志大小到了TB级别。现场是一主一从的服务搭配。同时主节点的磁盘IO到了99%。
再刷新下binlog日志，语句: flush logs ,会出来一个新的binlog日志，这样就不会因为之前的太大而看起来不方便。刷新出来一个新的日志后，再等2分钟，通过mysqlbinlog -v mysql-bin.000001 可以看到具体的语句。 同时再执行show processlist ，看下当前哪些sql语句在执行。
经过定位发现当前配置的mysql模式是ROW，同时binlog日志中发现了大量的update语句(因为分库分表后，update语句时候没有加库表位，导致的全表路由)，从而引起了大事务更新 导致主从同步延迟，服务宕机。
更改步骤:等待运行完当前的事务，然后转移走部分binlog日志，由Row模式改成mixed模式。再更改应用服务的代码，更改那个引起错误的update代码，确定路由到的具体库表，减少大范围请求。
然后问题解决~ 主因还是那条被发了130多次的重复update 语句。
下面我们来看下主从，复制模式等相关知识点:
三、Mysql单点存在的问题
小公司用单体服务，单体数据库，大公司考虑性能、考虑安全性、容灾性
Mysql复制拓扑结构使用场景： update、delete、insert(主库) select(从库)
横向扩展(Scale-Out)：指在多个从库之间进行读负载均衡，以提高读性能。所有数据变更在主库上执行，把之前在主库上的读负载剥离出来，以承载更多的写请求，另外，如果读负载越来越大，可以通过扩展从库来提高读性能
数据安全性：形成多个备份库，减少在极端场景丢失造成损失，对于主库来说，从库有多个，所以如果在从库上执行备份，对只读应用的可用性影响就要小很多（从库的复制机制本身也支持断点续传）。也就是说，在执行备份操作时，选择使用从库而不使用主库是一个更好的替代方案，这样可以尽量减少对主库性能以及数据安全性的影响
四、mysql主从数据同步搭建
在内网内的两台机器装上Mysql 172.16.244.168(主),172.16.244.169(从)
启动两台服务器、关闭系统防火墙、保证两台服务器之间可以相互ping 通
第一步:主节点授权
GRANT REPLICATION SLAVE ON *.* to 'root'@'172.16.244.168' identified by 'wnn';
//刷新系统权限表的配置
FLUSH PRIVILEGES;
第二步:找到主节点mysql的配置文件/etc/my.cnf，增加以下配置，在最后一行下面追加
# 开启binlog
log-bin=mysql-bin
server-id=100max_binlog_size = 200M #日志的最大大小
# 需要同步的数据库，如果不配置则同步全部数据库
binlog-do-db=xxxx_replicationbinlog_ignore_db = mysql #忽略同步的数据库 没有可以不填
# binlog日志保留的天数，清除超过20天的日志
# 防止日志文件过大，导致磁盘空间不足
expire-logs-days=20
第三步:重启master节点
service mysql restart。失败的话试试systemctl restart mysqld
第四步：用刚授权的账户进入到slave mysql后，再输入以下命令
CHANGE MASTER TO
MASTER_HOST='172.16.244.168',//主机IP
MASTER_USER='root',//之前创建的用户账号
MASTER_PASSWORD='wxxxxx',//之前创建的用户密码
MASTER_LOG_FILE='mysql-bin.000001',//master主机的binlog日志名称
MASTER_LOG_POS=100,//binlog日志偏移量(show master status; 主节点下执行 然后看file和position列)
master_port=3306;//端口
第五步：
slave配置：在/etc/my.cnf配置文件，增加以下配置
server-id=101
第六步：验证
连接master主机节点，往主机节点创建表检验、插入数据，检查slave节点是否有master插入的数据
五、mysql主从同步原理讲解
主从同步的步骤原理：
1.Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events)
2.Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)
3.Slave重做中继日志中的事件，通过SQL线程把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)
master在dump的过程需要关注的信息：
是push还是pull方式：
用户提交对数据的修改，然后Master（主库）把所有数据库变更写进Binary Log（二进制日志），主库通过Binlog Dump线程把二进制日志内容推送给Slave（从库），从库被动接收数据，不是主动去获取，除非是新建连接
中继日志:
从服务器 I/O 线程将主服务器的 Binlog 日志读取过来，解析到各类 Events 之后记录到从服务器本地文件，这个文件就被称为 relay log。
然后 SQL 线程会读取 relay log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。中继日志充当缓冲区，这样 master 就不必等待 slave 执行完成才发送下一个事件
六、常常说Binlog，你知道本质上是什么吗？
Mysql实际上是在复制什么？复制的单位怎么定义
复制功能之所以能够正常工作，是因为写入二进制日志的事件是从主库获取，然后在从库上回放的。根据事件的类型，事件以不同的格式被记录在二进制日志中，一般有三种复制模式，基于row复制模式，基于statement的复制模式以及mixed复制模式
什么是基于row的复制模式
使用row格式的二进制日志时，主库会将产生的事件（一组事件）写入二进制日志，以事件来表示数据的变更。将这些表示数据变更的事件复制到从库，然后在从库中应用这些事件，把主库数据同步到从库，这称为基于row（行）的复制，简称为RBR
关键行为：将ROW变更转成Binlog二进制日志在从库重放什么是基于statement的复制模式
使用statement格式的二进制日志时,主库会将SQL语句文本写入二进制日志。在主库上执行的SQL语句，然后将主库的SQL变更在从库重放，这称为基于statement（语句）的复制，简称为SBR
关键行为：SQL转成Binlog二进制日志在从库重放什么是mixed复制模式
默认采用基于SQL的复制，一旦发现基于SQL的无法精确的复制时，就会采用基于行的复制
show master logs;查看当前的日志 以及日志文件大小
show VARIABLES like 'binlog_format';查看当前的复制模式 默认ROW
set GLOBAL binlog_format='statement';
更改复制模式为statement。然后用上述命令查看，需要重新连接一个会话进来，当前会话更改后查看不生效。
分别查看insert 基于单行模式语句在ROW模式和Statement模式下的log存放
find / -name mysql-bin.000001 不记得存放目录可以在系统中搜索。
搜索到后进入该目录下，使用 mysqlbinlog -v mysql-bin.000001 进行binlog日志的查看。
ROW的insert日志内容存放
Statement的insert日志内容存放
再看下update 基于多行模式语句在ROW模式和Statement模式下的log存放大小。
基于ROW模式 ，单行情况下，insert语句 478条log信息。多行update语句(30条数据更新)，产生了1721条log数据
基于Statement模式 ，单行情况下，insert语句 560条log信息。多行update语句(30条数据更新)，产生了503条log数据
分析三种复制模式的区别
基于row的优缺点在哪里？
优势：
可以正确复制所有数据的变更，这是最安全的复制格式
劣势：
生成更多的二进制日志数据，因为基于row的复制会将每行数据的变更都写入二进制日志。利用二进制日志进行备份和恢复的时间也会更长。此外，二进制日志的文件锁也会因为需要更长的时间来写入数据而被持有更久的时间，这可能会影响数据库的并发性能
无法直接看到从库中执行的语句，但是可以使用mysqlbinlog工具
基于statement的优劣势在哪里？
优势：
写入日志文件的数据较少。当更新或删除操作涉及多行时，可以大大减少存储空间，在利用二进制日志备份与恢复数据时也可以快速完成。
日志文件中包含所有的数据变更的原始语句，可用于数据库审计。
劣势：
一些执行结果不确定的DML语句，不能使用基于statement的复制，否则可能会造成主从库的数据不一致
DML语句中，使用不带ORDER BY的LIMIT子句时，由于在主从库之间执行的排序结果可能不同，所以执行结果是不确定的
使用statement格式的日志时，一些内置的函数无法正确复制，如下：
LOAD_FILE()
UUID()
UUID_SHORT()
USER()
FOUND_ROWS()
SYSDATE()（主库和从库都使用--sysdate-is-now选项启动时适用）
GET_LOCK()
IS_FREE_LOCK()
IS_USED_LOCK()
MASTER_POS_WAIT()
RAND()
RELEASE_LOCK()
SLEEP()
VERSION()
关于安全与不安全数据复制
什么情况下是不安全复制：“安全”是指是否可以使用基于statement的格式（这里指的是在二进制日志文件中实际记录的内容为statement格式，不是指设置系统变量binlog_format = statement）正确复制语句，如果能正确复制，则认为语句是安全的，否则就认为是不安全的
Mysql对不安全执行的处理
使用mixed格式的日志时，被视为不安全的语句在记录到二进制日志时会自动转换为row格式，被视为安全的语句在记录到二进制日志时会使用statement格式
使用statement格式的日志时，对标记为不安全的语句会生成警告，甚至拒绝执行，被标记安全的语句则被正常记录
七、传统复制与基于GTID复制
传统复制
复制拓扑的初始化配置(比如说新增加一个slave库出来)和变更、复制的高可用切换(比如说master挂了，需要重新从slave中选举一个master)等操作都需要找到正确的二进制日志文件和位置，否则就无法正确复制
每个从库都会记录当前对应主库的二进制日志文件位置、二进制日志文件名，以及在此文件中它已从主库读取和处理的位置（即SQL线程和I/O线程的位置）。每个从库独立地应用主库的二进制日志，相互之间不产生影响并各自记录自身应用到的位置，而且就算有从库与主库的连接发生断开或重连，也不会影响主库的操作
基于GTID的复制方式
定义：GTID（Global Transaction Identifier，全局事务标识符），即基于GTID实现的复制，指的是基于事务的复制
mysql在insert update delete操作的时候，都是原子性操作 会分配一个ID，这个ID就是GTID。
使用GTID复制在搭建新从库或者因故障转移到新主库时，会自动根据GTID来定位对应的二进制日志文件和位置，更准确地说，是自动寻找从库缺失的GTID SET对应的二进制日志记录，极大地降低了这些任务的复杂度
GTID SET信息在主库与从库中都会保存。
这意味着可以通过GTID SET信息来追踪二进制日志的来源。此外，一旦在给定Server中提交过某个GTID的事务，则该Server将忽略后续提交的相同GTID的事务。
因此，主库上提交的事务在从库上只能应用一次，之后碰到重复的GTID时会自动跳过整个事务，这有助于保证主从库数据一致
通过GTID可以区分事务的来源（通过GTID组成中的UUID可以区分事务是由哪个Server提交的
八、GTID生成工作作用阶段以及GTID优缺点
开启GTID复制模式，在my.cnf配置文件中添加 5.7+版本
gtid_mode=on (必选)
enforce-gtid-consistency=1 （必选）
log_bin=mysql-bin （可选） #高可用切换，最好开启该功能
log-slave-updates=1 （可选） #高可用切换，最好打开该功能
GTID作用方式:
最开始的时候，MySQL只支持一种binlog dump方式，也就是指定binlog filename + position，向master发送COM_BINLOG_DUMP命令。在发送dump命令的时候，我们可以指定flag为BINLOG_DUMP_NON_BLOCK，这样master在没有可发送的binlog event之后，就会返回一个EOF package。不过通常对于slave来说，一直把连接挂着可能更好，这样能更及时收到新产生的binlog event
在MySQL 5.6之后，支持了另一种dump方式，也就是GTID dump，通过发送COM_BINLOG_DUMP_GTID命令实现，需要带上的是相应的GTID信息.
GTID工作原理:
master更新数据时，会在事务前产生GTID，一同记录到binlog日志中
slave端的i/o 线程将变更的binlog，写入到本地的relay log中
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录 2 3 4 5
如果有记录，说明该GTID的事务已经执行，slave会忽略
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog
在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描
GTID优势:
一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次;
GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置;
减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机;
GTID的劣势:
不支持非事务引擎;
不支持create table ... select 语句复制(主库直接报错);(原理: 会生成两个sql, 一个是DDL创建表SQL, 一个是insert into 插入数据的 sql; 由于DDL会导致自动提交, 所以这个sql至少需要两个GTID, 但是GTID模式下, 只能给这个sql生成一个GTID)
不允许一个SQL同时更新一个事务引擎表和非事务引擎表
在一个复制组中，必须要求统一开启GTID或者是关闭GTID
九、Mysql异步复制与半同步复制
传统异步复制模式介绍
自动提交，主库发起事务提交，在execute（执行）阶段执行完对数据的修改操作，然后在binlog（二进制日志）阶段将修改数据所产生的二进制日志记录写入二进制日志文件中，在commit（提交）阶段完成存储引擎层的事务提交（事务的状态修改为“提交”）。与此同时，主库会通过Dump线程将二进制日志记录发送给两个从库，两个从库收到后会写入relay log（中继日志）文件中。之后，两个从库各自读取relay log文件中的内容进行apply（应用），即模拟事务在主库中的提交方式来回放relay log
由于主库执行提交与发送二进制日志是异步的，也就是说，从库是否成功接收二进制日志不影响主库中的事务执行提交，因此可能会出现“主库发生宕机，但主库中已提交事务的二进制日志并没有被任何从库成功接收”的情况，即发生了数据丢失
半同步复制模式介绍 5.7插件模式（微内核架构）
我们可以看到，在binlog阶段后commit阶段前，主库必须等待从库在relay log阶段之后回复的ACK消息。而从库给主库回复ACK消息之前，必须确保已经成功接收主库的二进制日志记录，并写入中继日志
当主库发生故障时，主库已提交的事务如果丢失，可以通过从库的中继日志恢复，避免在主库发生故障时已提交的数据丢失
半复制的特点
如果主库在超时时间内没有收到任何从库的ACK消息，则主库将切换为异步复制，直到至少有一个从库恢复与主库的半同步复制连接，主库才重新切换为半同步复制
要正确使用半同步复制，需要主从库两端都启用半同步复制，如果在主库上禁用了半同步复制，或者在从库上禁用了半同步复制，则主库将使用异步复制中的方式传输二进制日志
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
