八，主从复制

1，主从复制简介

1.基于二进制日志复制的
2.主库的修改操作会记录二进制日志
3.从库会请求新的二进制日志并回放,最终达到主从数据同步
4.主从复制核心功能
    辅助备份,处理物理损坏                   
    扩展新型的架构:高可用,高性能,分布式架构等

2，高可用架构方案

负载均衡:有一定的高可用性 
LVS  Nginx
主备系统:有高可用性,但是需要切换,是单活的架构
KA ,   MHA, MMM
真正高可用(多活系统): 
NDB Cluster  Oracle RAC  Sysbase cluster   , InnoDB Cluster（MGR）,PXC , MGC

3，主从复制前提

(1) 2个或以上的数据库实例
(2) 主库需要开启二进制日志 
(3) server_id要不同,区分不同的节点
(4) 主库需要建立专用的复制用户 (replication slave)
(5) 从库应该通过备份主库,恢复的方法进行"补课"
(6) 人为告诉从库一些复制信息(ip port user pass,二进制日志起点)
(7) 从库应该开启专门的复制线程

4，主从复制搭建

准备多实例

创建目录：
mkdir -p /data/330{7,8}/data

准备配置文件
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

初始化数据;
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/app/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/app/mysql

systemd管理mysql：
cat >/etc/systemd/system/mysqld3307.service << EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service << EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

授权：
chown -R mysql.mysql /data/*

启动多实例

systemctl start mysqld3307.service
systemctl start mysqld3308.service

验证多实例：

[root@mister_f data]# mysql -p123 -S /data/3307/mysql.sock -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@mister_f data]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
  

检查配置文件

1，检查是否开启了二进制日志
2，检查server_id是否不同，主库的server_id建议比从库的小

[root@mister_f data]# cat /data/3307/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
[root@mister_f data]# cat /data/3308/my.cnf 
[mysqld]
basedir=/app/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin

创建主从复制用户（主库创建）

mysql -uroot -p123 -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123'"

备份主库数据

主库：
[root@mister_f data]# mysqldump -uroot -p123 -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/3307full.sql
 
从库：
[root@mister_f ~]# mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql

告诉从库信息

mysql -S /data/3308/mysql.sock
help change master to 
找到这段信息
CHANGE MASTER TO
  MASTER_HOST='172.21.0.8',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=437,
  MASTER_CONNECT_RETRY=10;

从备份中找到这两段二进制日志信息：
[root@mister_f data]# vim  /tmp/3307full.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=437;
然后从库执行上面语句：

开启主从复制线程(IO,SQL)

mysql> start slave;

查看复制状态

查看两个线程是否是  YES
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.21.0.8
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 437
               Relay_Log_File: mister_f-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             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: 437
              Relay_Log_Space: 530
              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: 0
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: 7
                  Master_UUID: e5f625b1-b135-11eb-8a72-525400956ca6
             Master_Info_File: /data/3308/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

还阔以在主库创建库看一下是否同步过去：
主库：
[root@mister_f data]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> create database wordpress charset utf8mb4;
Query OK, 1 row affected (0.01 sec)
从：

[root@mister_f ~]# mysql -S /data/3308/mysql.sock -e " show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup             |
| bin                |
| binlog             |
| gtid               |
| mysql              |
| performance_schema |
| sys                |
| test               |
| wordpress          |
| world              |
+--------------------+

5，主从复制原理

1.从库执行change master to 命令(主库的连接信息+复制的起点)
2.从库会将以上信息,记录到master.info文件
3.从库执行 start slave 命令,立即开启IO_T和SQL_T
4. 从库 IO_T,读取master.info文件中的信息,获取到IP,PORT,User,Pass,binlog的位置信息
5. 从库IO_T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互
6. IO_T根据binlog的位置信息(mysql-bin.000004 , 444),请求主库新的binlog
7. 主库通过DUMP_T将最新的binlog,通过网络TP给从库的IO_T
8. IO_T接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更新master.info
9.IO_T将TCP/IP缓存中数据,转储到磁盘relaylog中.
10. SQL_T读取relay.info中的信息,获取到上次已经应用过的relaylog的位置信息
11. SQL_T会按照上次的位置点回放最新的relaylog,再次更新relay.info信息
12. 从库会自动purge应用过relay进行定期清理
补充说明:
一旦主从复制构建成功,主库当中发生了新的变化,都会通过dump_T发送信号给IO_T,增强了主从复制的实时性.

image.png

6，主从复制监控

命令：
mysql> show slave status\G

主库相关的信息（master.info）

Master_Host: 172.21.0.8
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
重点关注这两，看是否和主库的binlog文件相对应
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1086

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1086 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

从库relay应用相关的（relay.info）

Relay_Log_File: mister_f-relay-bin.000002
Relay_Log_Pos: 969
Relay_Master_Log_File: mysql-bin.000004
在从库的数据路径下
-rw-r----- 1 mysql mysql      121 May 22 15:07 master.info
-rw-r----- 1 mysql mysql      210 May 22 11:40 mister_f-relay-bin.000001
-rw-r----- 1 mysql mysql      969 May 22 11:48 mister_f-relay-bin.000002
-rw-r----- 1 mysql mysql       56 May 22 11:40 mister_f-relay-bin.index
-rw-r----- 1 mysql mysql       63 May 22 11:48 relay-log.info

从库线程运行状态（一般用于排错）

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error:

过滤复制相关的

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

从库延时主库的时间

 Seconds_Behind_Master: 0

延时从库

SQL_Delay: 0
SQL_Remaining_Delay: NULL

GTID复制相关的

Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

7，主从复制故障

IO线程故障

1，IO线程连接不上
网络,连接信息错误或变更了,防火墙,连接数上线
一般IO线程会显示：connecting状态
排查方案：
使用手工复制用户登录出现一下错误时：
[root@mister_f ~]# mysql -urepl -p1234 -h172.21.0.8 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Unknown error 1045
[root@mister_f ~]# mysql -urepl -p1234 -h172.21.0.9 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mister_f ~]# mysql -urepl -p1234 -h172.21.0.8 -P3309
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '172.21.0.8' (111)
解决方案：
1. stop slave 
2. reset slave all;
3. change master to 
4. start slave

2，请求binlog请求不到
  binlog没开启
  binlog损坏或不存在
  主库执行了reset master操作

处理方法：（从库执行）
stop slave ;
reset slave all; 
CHANGE MASTER TO 
MASTER_HOST='172.21.0.8',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=1086,
MASTER_CONNECT_RETRY=10;
start slave;

SQL线程故障

relay-log损坏
回放relaylog
研究一条SQL语句为什么执行失败?
insert delete  update     ---> t1 表 不存在
create table  oldboy     ---> oldboy 已存在
约束冲突(主键,唯一键,非空..)

合理处理方法: 
把握一个原则,一切以主库为准进行解决.
如果出现问题,尽量进行反操作
最直接稳妥办法,重新构建

暴力的解决方法
方法一：
stop slave; 
set global sql_slave_skip_counter = 1;
start slave;

#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.
为了很程度的避免SQL线程故障主
(1) 从库只读
mysql> show variables like '%read_only%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| read_only                     | OFF   |
| super_read_only               | OFF   |
+-------------------------------+-------+
6 rows in set (0.01 sec)

read_only
super_read_only
(2) 使用读写分离中间件
atlas 
mycat
ProxySQL 
MaxScale从

8，主从延时监控及原因

主库方面原因

(1) 二进制日志写入不及时
mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             1 |
+---------------+
想要一提交事务就写入binlog需设置sync_binlog=1
(2) CR的主从复制中,binlog_dump线程,事件为单元,串行传送二进制日志(5.6 5.5)

1. 主库并发事务量大,主库可以并行,传送时是串行
2. 主库发生了大事务,由于是串行传送,会产生阻塞后续的事务.

解决方案:
1.  5.6 开始,开启GTID,实现了GC(group commit)机制,可以并行传输日志给从库IO
2.  5.7 开始,不开启GTID,会自动维护匿名的GTID,也能实现GC,我们建议还是认为开启GTID
3. 大事务拆成多个小事务,可以有效的减少主从延时.

从库方面原因

(1) 传统复制(Classic)中 
如果主库并发事务量很大,或者出现大事务
由于从库是单SQL线程,导致,不管传的日志有多少,只能一次执行一个事务.
5.6 版本,有了GTID,可以实现多SQL线程,但是只能基于不同库的事务进行并发回放.(database模式针对库进行并发) 5.6多线程是基于库
5.7 版本中,有了增强的GTID,增加了seq_no,增加了新型的并发SQL线程模式(logical_clock),MTS技术，5.7中多线程是基于事务
(2) 主从硬件差异太大
(3) 主从的参数配置
(4) 从库和主库的索引不一致
(5) 版本有差异

主从延时监控的原因

主库方面监控原因
主库：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1790 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库：
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1790
这两个号码对比可以看出是是主库原因

从库监控方面原因：
从库：
首先可以看从库从主库拿了多少日志过来
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1790

查看从库执行了多少
Relay_Log_File: mister_f-relay-bin.000002
Relay_Log_Pos: 1673

用这两个号码和binlog的号码相比较如果没有相差多少说明主从没有延时，如果差别很大说明有什么大事务阻塞，然后找到阻塞的sql语句进行优化
Exec_Master_Log_Pos: 1790
Relay_Log_Space: 1883