1.day11-MySQL-VIP班-MySQL.txt
1.day12-MySQL-VIP班-MySQL .txt
mha基础环境.txt
MGR ： https://www.jianshu.com/p/8c66e0f65324

• 取消主从： stop slave； reset slave all；

9.1 介绍

9.1概述：

1.主从复制原理：两台或以上数据库实例，通过二进制日志，实现数据同步关系。

9.2 搭建的过程： （前提）

• 准备两个以上数据库实例(主\从).主库打开二进制日志
• 不同节点的server_id和server_uuid不同.主库中需要创建专用复制用户I
• 备份主库已有数据到从库恢复
• 从库：主库链接信息，确认复制起点，主库，端口，起点。（change master to …）

• 从库：启动专用复制线程

  9.2 搭建主从复制

  9.2.1 多实例准备

• 见 3-3.11.1

  启动多实例 [root@db01 ~]# systemctl start mysqld3307 [root@db01 ~]# systemctl start mysqld3308 [root@db01 ~]# systemctl start mysqld3309 [root@db01 ~]# netstat -lntp | grep 330 tcp6 0 0 :::3306 :::* LISTEN 976/mysqld tcp6 0 0 :::3307 :::* LISTEN 1374/mysqld tcp6 0 0 :::3308 :::* LISTEN 1408/mysqld tcp6 0 0 :::3309 :::* LISTEN 1442/mysqld 检查关键信息 1. 主库开启binlog [root@db01 ~]# mysql -uroot -p123 -e "select @@log_bin;" [root@db01 ~]# mysql -uroot -p123 -e "select @@log_bin_basename;" 2. 检查server_id 和 server_uuid [root@db01 ~]# mysql -uroot -p123 -e "select @@server_id;" [root@db01 ~]# mysql -uroot -p123 -e "select @@server_uuid;" 如果一样: [root@db03 data]# vim /etc/my.cnf #如何修改server id server_id=53 [root@db03 data]# rm -rf /data/3306/data/auto.cnf #如何删除uuid [root@db03 data]# /etc/init.d/mysqld restart [root@db03 data]# mysql -uroot -p123 -e "reset master;" Ps: 初始化设置： - mysql> reset master; #清空二进制日志binglog

9.2.2 主库建立复制用户

1. 建立的主库用户，用于从库远程连接 [root@db01 ~]# mysql -S /tmp/mysql3307.socket -e “grant replication slave on . to repl@’10.0.0.%’ identified by ‘123’”; [root@db01 ~]# mysql -S /tmp/mysql3307.socket -e “select user,host from mysql.user” +———————-+—————-+ | user | host | +———————-+—————-+ | repl | 10.0.0.% | +———————-+—————-+ Ps : 8.0 操作： create user repl@’10.0.0.%’ identified with mysql_native_password by ‘123’; Grant replication slave on . to repl@’10.0.0.%’;

9.2.3 (多种)主库备份恢复到从库👉

一： mysqldump方式备份 - [root@db01 ~]# mysqldump -uremote -p123 -h 10.0.0.51 -A —master-data=2 —single-transaction -R -E —triggers —max_allowed_packet=64M >/tmp/full.sql - 从库同步数据 [root@db01 ~]# mysql -S /tmp/mysql3308.socket 二： PXB 方式备份
Ps: 8.0 使用clone pluging插插件方式

9.2.4 主库配置连接信息，确认复制起点

2.4.1 告知从库的信息 mysql -S /tmp/mysql3307.socket #进入主库 mysql> help change master to —获得SQl语句的小技巧 2.4.2 进入从节点 2.4.3 从库分别执行SQL语句 myaql> CHANGE MASTER TO MASTER_HOST=’10.0.0.51’, MASTER_USER=’repl’, MASTER_PASSWORD=’123’, MASTER_PORT=3307, MASTER_LOG_FILE=’mysql-bin.002’, MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10; PS：检查从库位置信息 1. musqldump: - [root@db01 ~]# grep “— CHANGE MASTER TO” /tmp/all.sql - — CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001’, MASTER_LOG_POS=444; 2. xbk: 无 3. 8.0 clone plugin: select * from performance_schema.clone_status\G
如何清空二进制日志: mysql > reset master ;

9.2.5 从库：开启专用复制线程

进入从库 [root@db01 ~]# mysql -S /tmp/mysql3308.socket mysql> start slave;

9.2.6 验证从库复制

[root@db01 ~]# mysql -S /tmp/mysql3308.socket -e “show slave status\G” | grep Running; Slave_IO_Running: Yes Slave_SQL_Running: Yes

9.2.7 报错处理

mysql主从同步出现Slave_IO_Running:NO的解决办法 (主库起点配置问题) 1. 查看主库 (发现Position与主库不同) mysql> show master status; 获得—————> mysql-bin.000003 Pos： 313 从库执行： mysql> stop slave; mysql> CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000003’,Master_Log_Pos=313; mysql> start slave; 2. 执行start slave提示GTID相关： 可能由于主库开启GTID，从库未开启GTID造成的。 从库配置gtid设置 3. server_id server_uuid重复 从库删除 /data/3306/data/auto.cnf ，重启
Ps: 快速清空配置：mysql -S /tmp/mysql3308.socket -e “stop slave; reset slave all;”

9.3 主从复制的原理

• 有助于处理故障

  9.3.1 主从复制中涉及到的资源

  主库： binlog文件 从库： 1.rela-log文件： 中继日志 #作用—-存储接受到的binlog 默认存储在从库 | 数据 |目录下 -rw-r——- 1 mysql mysql 206 Oct 20 01:44 /data/3308/data/db01-relay-bin.000001 #手动定义rela-log的方法： mysql> show variables like ‘%relay%’; relay_log_basename=/data/3308/data/db01-relay-bin 2.master.info: #作用—-记录连接主库的信息（IP port user passwprd) 及已经接受到主库binlog位置点信息 默认存储在从库的数据下路径—-master.info 手工定义：master_info_repository=FILE/TABLE 表: mysql.slave_master_info 3.relay.info: 作用—-记录从库回放到的relay.log的位置点（上次运行到的位置点；类似断点续传） 默认存储在从库数据目录下—-relay-log.info 手工定义：master_info_repository=FILE/TABLE #早期只能存文件里，放table里性能更好 表： mysql.slave_relay_log_info

9.3.2 线程

从库——主库沟通：依靠线程
#主库 binlog dump Thread： - 作用： 用来接收从库的请求，并且投递binglog给从库 - 查询: mysql> show processlist; #从库 IO线程: 请求binglog日志，接收日志binglog SQL线程：回放relay-log日志

9.3.3 主从复制原理图

9.3.4 主从复制原理文字说明

2.2.3 复制原理文字说明 a. 从库: change master to ….,主库相关信息记录到master_info(MI)中. b. 从库: start slave , 启动 IO SQL线程 c. 从库: 根据MI的信息,连接主库. d. 主库: 生成一个DUMP线程和IO通信 e. 从库: 根据MI的信息(binlog pos),向主库请求新的binlog. f. 主库: DUMP截取新的binlog,发送给从库IO g. 从库: IO接收binlog,日志存放TCP/IP，此时网络层层面返回ACK给主库， 主库工作完成 h. 从库： IO将binglog最终写入到relaylog中， 并跟新M.info。 IO线程工作结束。 i. 从库: SQL 读取Relay-info(RI)信息,获取上次会放到的位置点,回放最新的relaylog. j. 从库: 从库SQL回放完成后,更新RI信息. 补充: 主库DUMP实时监控binlog的变化,通知给从库IO. 从库relaylog,定期会自动被清理

9.4 主从监控

9.4.1 主库监控方法

主库 : mysql> show master status ; mysql> show processlist; mysql> show slave hosts; 注： 如果需要show slave hosts看到从库的地址信息,需要在从库做以下配置. vim /etc/my.cnf [mysqld] report_host=10.0.0.52 report_port=3306

9.4.2从库监控方法

mysql> show slave status\G
* 1. row * 1.主库相关信息：来自于 mster.info: 表—> Master_Host: 10.0.0.51 Master_User: repl Master_Port: 3307 Connect_Retry: 10 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 154 2.从库的relay-log的执行情况，来自于relay.info： 表—> mysql.slave.rely_log_info （一般用来做主从延时：） Relay_Log_File: db01-relay-bin.000011 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000006 Exec_Master_Log_Pos: 154 —执行到的位置点 Seconds_Behind_Master: 0 —落后主库多少 3. 从库线程状态，具体报错信息： Slave_IO_Running: Yes —IO线程状态 yes|NO|conneting Slave_SQL_Running: Yes —SQL线程状态 Last_IO_Errno: 0 Last_SQL_Errno: 0 4.过滤复制相关信息（复制/不复制的库）： Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: 5.主从延时的看时间 —- 0不一定没延时，应为他是基于 Seconds_Behind Master: 0 6.延迟从库的配置信息（人为配置的延迟-防止逻辑损坏-恢复较快） SQL_Delay: 0 SQL_Remaining_Delay: NULL 7.GTID个相关信息： Retrieved_Gtid_Set: Executed_Gtid_Set: 其他： mysql> use information_schema; mysql> show tables;

9.5 主从故障分析及处理🔥

9.5.1监控方法

mysql> show slave status\G

9.5.2 IO线程故障

IO线程负责 连接主库，请求 接收 存储日志
1. 正常状态： Slave_IO_Running: Yes 2. 非正常状态： Slave_IO_Running: NO | Connetting 连接故障

Slave_IO_Running: Connetting 连接故障 外部因素： (1) 网路，端口，防火墙问题 内部因素： (2) 用户，密码，IP，port, 授权问题 (3) 主库连接数上限 默认连接数150个并发会话 mysql> select @@max_connections; 查看151 预留1个给本地管理员—>152。 (4) 版本加密插件不统一， 5.7默认使用 native 加密算法验证 8.0sha2 验证 (5) server_id server_uuid 重复 1.1.2 故障模拟： (1) 宕掉主机 Slave_IO_Running: Connecting Last_IO_Errno: 2003 Last_IO_Error: error connecting to master ‘repl@10.0.0.51:3306’ - retry-time: 10 retries: 1 message: Can’t connect to MySQL server on ‘10.0.0.51’ (111)’ (2) 用户 密码 IP PORT 加密插件 Slave_IO_Running: Connecting Last_IO_Errno: 1045 Last_IO_Error: error connecting to master ‘repl@10.0.0.51:3306’ - retry-time: 10 retries: 1 message: Access denied for user ‘repl’@’10.0.0.52’ (using password: YES) (3) 最大连接数上限 mysql> set global max_connections=2; Slave_IO_Running: Connecting Last_IO_Errno: 1040 Last_IO_Error: error connecting to master ‘repl@10.0.0.51:3306’ - retry-time: 10 retries: 1 message: Too many connections 通用故障处理方法： 通过复制用户，手动连接主库排错 (connecting 通用故障排错) 1.1.3 故障恢复 stop slave; reset slave all; change master to … start slave; 其他： mysql> start slave sql_thread; #单独启动SQL线程 mysql> start slave io_thread; #单独启动SQL线程 ———————————————————————————- # Slave_IO_Running: NO 请求日志，接受日志故障 1.2.2故障模拟-报错信息： (1) Server_id 或者Server_uuid重复 Slave_IO_Running: No Last_IO_Errno: 13117 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work… (2) binlog 位置点写错了 Slave_IO_Running: No Last_IO_Errno: 13114 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file (3) 3. 日志损坏 Last_IO_Errno: 13114 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘could not find next log; the first event ‘mysql-bin.000003’ at …

• Ps: 生产中如果要 reset master；

     ----正确姿势----               1.找业务不繁忙期间，业务员停止5分钟     2.等待从库所有主库日志     3.主库reset master；     4.从重新同步主库      myaql> stop slave;      myaql> restart slave all;      myaql> CHANGE MASTER TO         MASTER_HOST='10.0.0.51',                       MASTER_USER='repl',                            MASTER_PASSWORD='123',                        MASTER_PORT=3307,                              MASTER_LOG_FILE='mysql-bin.001',             MASTER_LOG_POS=444,                              MASTER_CONNECT_RETRY=10;      myaql> start  all;  如果业务繁忙期间，有可能会导致数据库hang --->如果要恢复主从，需要重新搭建

9.5.3 SQL线程故障（常见）

• SQl线程 主要工作:回放relay log 中的 SQL语句

Slave_SQL_Running: No 故障 原因: 1. 需要创建的对象已经存在 2. 删除和修改的对象不存在 3. 约束冲突 4. 从库被误写入了. 5. 双主设计问题 6. 高可用脑裂 7. 主从数据不一致. #故障重现-报错信息： 1. 从库误写入：(模拟由于错误连接到从库操作，创建表后，再在主库创建相同的表。 此时，打破了主从复制关系，导致SQL语句不能执行成功，主从复制失败) Slave_SQL_Running: No Last_Errno: 1007 Last_Error: Error ‘Can’t’ create database ‘new’; database exists’ on query. Default database: ‘new’. Query: ‘create database new’ #1.以主库为准的处理方法: mysql> set sql_log_bin=0; #binglog日志不记录drop操作 mysql> drop database new; mysql> set sql_log_bin=1; #2. 以从库为准的方法(跳过此次错误): 开了GTID: stop slave; set gtid_next=’00bf718b-491c-11eb-81a2-000c2905f029:6’; begin;commit; set gtid_next=’AUTOMATIC’; 没开GTID的模式: mysql> stop slave; mysql> set global sql_slave_skip_counter=1; mysql> start slave; ###从库被写入,源头解决问题: —-设置从库为只读库. mysql> set global read_only=1; mysql> set global super_read_only=1; —-通过中间件隔离操作

• 自动： 遇到自动跳过，非常暴力，除非库不重要

/etc/my.conf slave-skip-errors = 1032,1062,1007 - 将同步指针线向下移动一个，如果多次不同步，可重复操作 - 限于比较简易的操作， 有一定风险，如果非要使用，一定要保证此时故障数据域完全一致。 - 第三方工具： pt工具，可以帮助我们检查数据的一致性： https://www.jianshu.com/p/c97228b6f60c

问题处理：
一: 主从数据不一致处理方法: 1. 增强半同步复制 (较强) 2. 组复制(很强) 3. PXC(很强) mysql> select @@server_id; +——————-+ | @@server_id | +——————-+ | 9 | +——————-+ mysql> create databases oldguo; 恢复思路3： 重新搭建主从： 备份恢复 + 重新构建 二： 主库出现问题怎么办？？？ #物理 1. 看主库时候能ssh上 2. 检查binglog是否完整 3. 手工追加日志到新位置 4. 从库替代主库工作 #逻辑 drop 只能通过备份去回复 三： 双主情况下，一个宕机了，但是不知道买过了很久，另一个主也宕机，怎么恢复？ 备份+日志恢复。　重新构建主从解决大部分问题(主从position相差不多，利用binglog恢复) SQL线程较多，处理麻烦，双主使用较少了。 四： 从库怎么充当主库： 修复最新状态， 取消从库身份 清空binglog日志信息 五：自愈功能，还不是特别成熟：mysql8.0.17 +　MGR+Mysql +K8 了解连接—-> https://myslide.cn/slides/16102

• 2PC (commit事务的2个阶段)

9.6 主从延时问题的原因分析处理🌈

9.6.1 什么是（异步复制）主从复制延时：

主库发生了操作，从库”很久”才能跟上来。

9.6.2主从延迟如何监控？

show slave status \G；
如何计算,主从日志差异的日志量(字节)? 粗略： Second_behind_Master: 0 #通过日志的时间戳间隔: ——> 只能得出传输过程中有没有延迟 （等于0 不代表没有延时） 精确： 日志量： 主库binglog位置点 -从relay执行的位置点 (主库show master status—> Position) - (从库 show slave status—-> Exec_Master_Log_Pos) = 日志量

9.6.3 如何计算延时的量？

面试题：

• 如何计算,主从日志差异的日志量(字节)?
• 差异可以准确预估主从的延时—-> 基于主执行的pso 和从执行的pos号，更加确切。

① 非GTID日志量差异 ② 使用GTID号码判定
(主库show master status—> Position) - (从库 show slave status—-> Exec_Master_Log_Pos) = 日志量
可以使用shell脚本处理
myql>　show master status； #主库 cat /data/3308/relay-log.info #从库

9.6.4 造成主从复制延时的原因

1.3.0 外部 : - 网络慢 - 主从硬件配置差异大. - 参数. - 版本(只支持从低到高.不支持从高到低—> 主从升级，请先升级从库) 1.3.1 主库 : - dump 是串行工作的.如果主库并发事务量高,或者大事务时.传输时就会有较高延时. - 解决方案: 5.6+版本,加入了Group Commit(GC)技术. 两个指标时间延时+个数控制进行组提交. - 但是依然怕大事务,不成熟！ binlog_group_commit_sync_delay=1 #1s提交一次 binlog_group_commit_sync_no_delay_count=1000 #或者满足1000事务提交一次 1.3.2 从库 : #SQL默认是串行工作的. 主库的并发事务量大或者大事务.都会导致 SQL线程回放慢. 解决方案: —-> 5.6版本: 加入了SQL线程并发回放机制. 以database级别进行并发回放. - 意思是,只要主库中的事务是来自于不同库的操作,可以并发回放.但对于同一库的并发/大事务比较鸡肋。 —-> 5.7+版本: 加入了Logical_clock模式 使得在#主库能够group commit的事务，并且根据sequence_number排序，在从库并发回放 Ps: 打开主库binglog—> 发现last_committed=8 8组：代表同一时间提交的事务 slave_parallel_type = DATABASE / logical_clock #模式 slave_parallel_workers = 0 #cpu/2 —-> 8.0+ writesets 写集合方式. MGR. #说明：即使有以上的自带的优化机制，我们还是要注意对于 / 大事务的处理问题 / 锁的问题！！！/性能较差的sql（优化人的操作） #大事务： 5000行的操作就是大事务

9.7 半同步复制：

9.8 增强半同步复制：

1.3.0 外部 : 通过日志的时间戳间隔: 2. 主从数据最终一致性保证—-(增强)半同步复制 2.1 ########################半同步复制-after_commit 和 after_sync ######################## ——> 5.5开始支持半同步复制,但是没有GC机制,性能极差,几乎没人用 ——> 5.6 版本时 ,加入了GC机制,半同步复制开始被接收.使用的是after_commit机制,但是是在redo commit之后进行等待ACK确认. 这里会有一个痛点,如果主库redo commit阶段宕机宕机了,主库又获取到了binlog,会出现主库比从库数据”多”的问题.导致数据不一致. ——> 5.7版本+以后,加入了after_sync机制,在binlog commit(#binlog sync disk)阶段,等待从库ACK,不管谁宕机,都能保证最终一致性. 另外: 不管哪种方式,还会出现,如果ACK超时,会被切换为异步复制的模式.还是有数据不一致的风险. 如果公司能容忍,可以使用这种架构,建议使用增强半同步+GTID模式. 如果不能容忍,可以使用MGR PXC . 2.2 增强半同步复制配置 2.2.1 加载插件 INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’; INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’; 2.2.2 查看是否加载成功: show plugins; 启动: SET GLOBAL rpl_semi_sync_master_enabled = 1; SET GLOBAL rpl_semi_sync_slave_enabled = 1; 2.2.3 重启从库上的IO线程 STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; 2.2.4 查看是否在运行 show status like ‘Rpl_semi_sync_master_status’; show status like ‘Rpl_semi_sync_slave_status’; 2.2.5 其他的优化参数： show variables like ‘%semi%’; rpl_semi_sync_master_enabled =ON rpl_semi_sync_master_timeout =1000 rpl_semi_sync_master_trace_level =32 rpl_semi_sync_master_wait_for_slave_count =1 rpl_semi_sync_master_wait_no_slave =ON rpl_semi_sync_master_wait_point =AFTER_SYNC rpl_semi_sync_slave_enabled =ON rpl_semi_sync_slave_trace_level =32 mysql> set global binlog_group_commit_sync_delay =1; mysql> set global binlog_group_commit_sync_no_delay_count =1000;

10.1 特殊从库的应用

1.1 延时从库

1.1.1 介绍

普通的主从复制，处理物理故障损坏比较擅长（比如说，主库磁盘上的数据损坏）。
如果主库出现了DROP DADABASE操作，从库也无法恢复。

延时从库： 主库做了某项操作以后，从库延时多长时间回放（SQL）可以处理逻辑损坏的备份

一部由别的服务器高并发读，单独一台做延时从库（一般3-6小时，会接收-存储-但会延迟处理）

1.1.2 配置

1. 停止从库 mysql> stop slave; 2.设置从库延时时间 mysql> CHANGE MASTER TO MASTER_DELAY = 300; 3.启动从库复制 mysql> start slave; mysql> show slave status;

企业建议延迟从库设置3-6小时。具体根据公司的处理速度

1.1.3 故障模拟及恢复

1. 模拟数据 create database ys charset utf8mb4; use ys; create table t1(id int); begin; insert into t1 values(1),(2),(3),(4); commit; begin; insert into t1 values(11),(22),(33),(44); commit; drop database ys; 10:00 2.恢复 思路： 1. 先停业务，挂维护页。 example： 10:05 2. 停从库SQL线程 10:05 mysql> stop slave sql thread； 看relay.info ——> 位置点 mysql> stop slave； 3. 追加后续缺失部分的日志到从库 （手工模拟SQL工作） 日志在哪里存？ ——> relay-log 范围： ys: relay.info ——> DROP操作 4. 恢复业务方案 一，ys 导出—> 恢复到主库 二，推荐方法： 直接将从库承当YS主库业务。#### 1.1.3

操作：https://www.bilibili.com/video/BV157411K7sf?p=136

1.2 过滤复制

1.2.1 业务场景：部分库复制

主库实现过滤的方式（不建议）：

从库实现过滤的方式（sql回放时过滤）

常见架构：级联从库
**

多源复制 （MSR） 支持在线分析，在线汇总
可搭建实现一下：

1.2.2 配置方法

• 主库：

mysql> show master status;
binglog_Do_DB: 白名单 binglog_Ignore_DB: 黑名单

• 从库：
  mysql> show slave status；

       Replicate_Do_DB:          Replicate_Ignore_DB:                  Replicate_Do_Table:       Replicate_Ignore_Table:        Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:

cat /etc/my.cnf #配置详解：
1. 库级别复制过滤：（生产常用） Replicate_Do_DB=word Replicate_Ignore_DB=xxx 2.表级别复制过滤： Replicate_Do_Table=word.t1 Replicate_Ignore_Table=xxx 3.表级别， 模糊匹配 Replicate_Wild_Do_Table=word.t* Replicate_Wild_Ignore_Table=xxx

• cat /etc/my.cnf #配置示例：

[root@mysql01 ~]# cat /data/3308/my.cnf [mysqld] basedir=/application/mysql/ datadir=/data/3308/data socket=/tmp/mysql3308.socket log_error=/data/3308/mysql.log port=3308 server_id=8 log_bin=/binlog/3308/mysql-bin replicate_do_db=oldguo replicate_do_db=oldboy

• 从库过滤验证： mysql> show slave status；
  …
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Replicate_Do_DB: oldguo,oldboy
  …
• 从库过滤复制测试：

首先验证是否能够验证主从 1. 主库： mysql> create database oldboy; mysql> use oldboy; Database changed mysql> create table test(id int); mysql> insert into test values(1); #建库，建表 ，插入数据 mysql> show tables; +—————————+ | Tables_in_oldboy | +—————————+ | test | +—————————+ 1 row in set (0.00 sec 2. 从库： mysql> select @@server_id; #3308从库 mysql> use oldboy; Database changed mysql> select * from test; #查看数据 +———+ | id | +———+ | 1 | +———+ #验证过滤的从库是否管用： 1. mysql> show databases; +——————————+ | Database | +——————————+ | information_schema | | bd | | mysql | | oldboy | | oldguo | | oldhou | … 2. mysql> show databases; +——————————+ | Database | +——————————+ | information_schema | | mysql | | oldboy | | oldguo …

1.3半同步复制

Classic replication ： 传统一异步非GTID复制工作模式下，会导致主从数据不一致的情况。
从库宕机等情况，主从复制，没有任何一个确认的能力。
[虽然tcp/ip中有ack的确认。但它不是数据库的行为] ——>保证数据落盘才是真的保障

5.5 版本为了保证主从数据的一致性问题，加入了半同步复制的组件（插件）
——> 半同步过渡产品。面试更多问原理。
半同步复制在从库IO地方做了手脚 —-> 要求我们的必须要把ｒｅｌａｙ．ｌｏｇ写到磁盘
主库的事务才能提交成功。

半同步复制原则： 必须从库必须写入磁盘，才认为是一次完整的复制。（依赖插件，建立应用层之间的ack确认）
半同步复制会阻塞事务的并发，事务执行需要延时，性能会受到一些影响。—-> 会设置超时时间，超过一段时间后，就会恢复到原始的主从复制状态。

在主从结构中，都加入半同步复制的插件 作用： 控制从库IO线程—> 是否将rela.log一旦落盘通过插件返回ACK_rec. 接受到ACK之后，主库事务才能提交成功，在默认情况下，如何超过10S没有返回ACK，此次复制行为会切换为异步复制。 在5.6 5.7当中，也加入了一些比较好的特性(after commit after sync,无损)。 也不能完全保证5个9的数据一致。 如果生产业务比较关注主从最终的一致性（金融类）。我们推荐可以使用MGR架构。 或者RXC等一致性架构。

1.4 GTID复制

作用： 主要保证主从复制中的高级的特性。
GTID: 5.6 版本出现，并没有默认开启， 5.7中即使不开启也有匿名的GTID记录。
DUMP传输可以并行，SQL线程并发回放， 5.7.17+ 的版本以后几乎都是GTID模式。

• 1.搭建GTID复制：

1. 准备三台虚拟主机 ip 10.0.0.51-53 hostname： db01 db02 db03 /etc/hosts 解析本机地址 防火墙关闭 能够实现远程xshell连接 1. 清理环境 #数据路径 pkill mysqld \rm -rf /data/mysql/data/ \rm -rf /data/binlog/

/etc/my.cnf 重要配置：
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
gtid-mode=on —启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true —强制GTID的一致性
log-slave-updates=1 —slave更新是否记入日志

1. 准备配置文件 主库db01： cat > /etc/my.cnf < EOF slave1(db02)： cat > /etc/my.cnf < EOF slave2(db03)： cat > /etc/my.cnf < EOF 3. 初始化数据 mysqld —initialize-insecure —user=mysql —basedir=/application/mysql/ —datadir=/data/mysql 4.启动数据库 systemctl start mysqld

5.构建主从 5.1 主 创建复制用户： mysql> grant replication slave on . to repl@’10.0.0.%’ identified by ‘123’; (8.0 需要先创建用户再授权) mysql> create user repl@’10.0.0.%’ identified with mysql_native_password by ‘123’; mysql> grant replication slave on . to repl@’10.0.0.%’; 5.2 从 开启主从复制：（生产中，需要先备份>> 恢复后再搭建） #52/53 GTID主从复制，主从延时判断方便了。也不用在关注position号和起点位置了。 change master to master_host=’10.0.0.51’, master_port=’3307’, master_user=’repl’, master_password=’123’, MASTER_AUTO_POSITION=1; start slave;

6.查看：

db58 [mysql]> show slave status\G;

基于GTID模式下的变化：
MASTER_AUTO_POSITION=1; #自动去找位置点（从位置点1起自行判断复制起点）
一：第一次开启主从的时候 io线程会读取本地 relay-log的最后一个事件—-> 判断从那个一gtid开始复制
二：如果是手动恢复过了——> 查看是否执行gtid_purge —->10001开始复制）


5.7默认

10.2 主从复制架构演变

10.2.1 原生态支持
1主1从 1主多从 3-4 多级住从 双主结构 延时从库 过滤复制 MGR 组复制 （5.7.17+ ）自行扩展

10.2.2 非原生态
安全： 高可用 全年无故障率 99% 一般级别 136524*60 = 5256 mins （宕机） 99.9% 普通几级别 525.6 mins 99.99% 准高可用级别 52.56 mins 代表产品： MySQL MHA 99.999% 金融级别 5.256 mins 99.9999% 超金融级别 0.5256 mins

10.2.3 性能
读多写少：读写分离方案 代表产品： Altas ， proxySQL， Maxscale，Mycat… 读写都多：分布式方案 Mycat (DBLE) … Altas-sharding, sharding-jdbc 高可用： MHA 读写分离： Atlas 分布式： MyCAT

面试题总结：

Ps：从库太多的解决方法： 使用多级主从（一个主最多3-4个直属从库）

GTID作用：一次性发送多个binglog（并行发送），并保证从库有序执行SQL

Ps: 面试案例—> 你是如何判断主库传输不及时的？
我以前的公司使用的的数据库是Mysql5.6的版本，用的是默认的非GTID的版本，通这几个指标析发现，主库是通过串行工作的方式的问题，（从库接受的日志量并不多，但是主库又比较繁忙——> 如何判断主库传输不及时： 通过从库show slave status seconds_behind_master 或通过对比主库show master status与日志文件）
小结：主从故障

扩展： 中从复制高可用问题

这种双主的结构，虽然可以在宕机的时候保证高可用，但是双主同时操作同一个库表的时候容易发生故障（建议升级结构或分离ab库的业务，不要混在一起使用）
小结：主从延时
主库： dump串行操作问题—－＞ ５.６＋ 开启GTID，并发传输多个事件
人的原因：大事务和锁是需要我们额外关注的
从库： SQL并发回放的问题， 5.7+ logical_clock线程
人的原因：大事务和锁是需要我们额外关注的
sql数据库配置一般16-64核
总结： MySQL现有的新版， 5.7+ 在主从延时方面已经解决了很多遗留问题
所以我们着重解决人的问题(大事务和锁)
上一篇

9. MySQL高可用✨架构及读写分离

下一篇

7. MySQL 备份恢复2✨