1. 主从复制

1.1 介绍

依赖于二进制日志的，“实时”备份的多节点架构

1.2 主从复制的前提（如何搭建主从复制）

（1）至少2个实例
（2）不同的server_id
（3）主库要开启二进制日志
（4）主库需要授权一个副库专用的复制用户
（5）主库数据备份
（6）开启专用复制线程

2. 搭建主从复制

2.1 搭建多实例

（1） 准备多个目录
mkdir -p /data/330{7,8,9}/data

（2）准备配置文件
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

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

（3）初始化三套数据
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/app/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/app/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/app/mysql

（4） systemd管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

vim mysqld3307.service
ExecStart=/app/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/app/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/app/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

（5）授权
chown -R mysql.mysql /data/*

（6）启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

（7）验证多实例
netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"

2.2 检测server_id

mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"

2.3 检查3307（主库）的二进制日志情况

mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"

2.4 主库创建复制用户（3307）（用户=repl）

mysql [(none)]>grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

2.5 进行主库数据备份

mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/3307full.sql

2.6 恢复数据到从库（3308）

[root@db01 /data]# mysql -S /data/3308/mysql.sock
mysql [none]>set sql_log_bin=0;
mysql [none]>source /tmp/3307full.sql;

2.7 通知从库一些复制的信息

CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='master2-bin.002',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;

mysql [(none)]>start slave;

3. 主从复制工作过程

3.1 名词认识

文件：
   主库：binlog
   从库：relay-log（中继日志）、master.info（主库信息文件）、relay-log.info（中继日志应用信息）
线程：
    主库：binlog_dump_thread （二进制投递线程）查看方法：mysql [(none)]>show processlist;
    从库：IO_Thread：从库的I/O线程：请求和接受binlog
          SQL_Thread：从库的SQL线程：回放日志

3.2 工作原理

image.png

（1）从库执行change master to 语句，会立即将主库信息记录到从库的master.info中
（2）从不高库执行 stat slave ，会立即生成TO_T和SQL_T
（3）IO_T读取master.info文件，获取到主库信息
（4） 主库会生成一个准备binlog DUMP线程，来响应从库
（5）IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
（6）主库 DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
（7）IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成
（8）IO_T会更新master.info文件重置binlog位置点信息
（9）从库IO_T会将binl，写入到relay-log中
（10）SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
（11）SQL_T按照位置点往下执行relaylog日志。
（12）SQL_T执行完成后重新更新relay-log.info
（13）relay-log会有自动清理的功能。

细节：
主库发生了信息的修改，更新二进制日志完成后，会发送一个“信号”给Dump_T,Dump_T通知给IO_T线程

4. 主从复制监控及故障处理分析

4.1 主从监控

主库：show processlist;
      正常状态：
 mysql [(none)]>show processlist;
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host            | db   | Command     | Time | State                                                         | Info             |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 19 | repl | 10.0.0.51:28599 | NULL | Binlog Dump | 1920 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 20 | root | localhost       | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

从库：show slave status\G

*************************** 1. row ***************************
主库的信息：
        Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51                 --->主库的IP
                  Master_User: repl                      --->复制用户名
                  Master_Port: 3307                      --->主库的端口
                  Connect_Retry: 10                      ---> 断练之后重试次数
                  Master_Log_File: mysql-bin.000002      --->主库获取到的binlog文件名
                  Read_Master_Log_Pos: 444               --->已经获取到的binlog的位置号

从库的relaylog的信息：（relay-log.info）
                  Relay_Log_File: db01-relay-bin.000002  --->从库已经运行过的relaylog的文件名
                  Relay_Log_Pos: 320                     --->从库已经运行过的relaylog的位置点
从库复制线程工作状态：
                  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:
从库延时主库的时间：
                  Seconds_Behind_Master: 0
从库线程报错详细信息：
                  Last_IO_Errno: 0                        --->IO报错的号码
                  Last_IO_Error:                          --->IO报错的具体信息
                  Last_SQL_Errno: 0                       --->SQL报错的号码
                  Last_SQL_Error:                         --->SQL线程报错的具体原因
延时从库：
                  SQL_Delay:  0                           --->延时从库设定的时间
                  SQL_Remaining_Delay: NULL               --->延时操作的剩余时间
GTID复制信息：
                  Retrieved_Gtid_Set:                     --->接收到的GTID的格式  
                  Executed_Gtid_Set:                      --->执行的GTID的个数

4.2 主从故障的分析及处理

从库复制线程的状态
                  Slave_IO_Running: Yes
                  Slave_SQL_Running: Yes
从库线程报错详细信息：
                  Last_IO_Errno: 0
                  Last_IO_Error: 
                  Last_SQL_Errno: 0
                  Last_SQL_Error: 

4.2.1 IO线程故障

（1）连接主库连接不上
出现
                  Slave_IO_Running: Connecting
原因：
       网络不通、防火墙、
       IP不对、port不对、用户名，密码不对、skip_name_reslove、连接数上限
判断于以下错误对比：
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P3308
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host '10.0.0.51' is not allowed to connect to this MySQL server

[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.52 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (110)'

[root@db01 ~]# mysql -urepl -p111 -h 10.0.0.51 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'10.0.0.51' (using password: YES)

[root@db01 ~]# mysql -urepll -p123 -h 10.0.0.51 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repll'@'10.0.0.51' (using password: YES）

找到原因后，如何处理？
stop slave;
reset slave all ;
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
start slave;
（2）请求新的binlog
出现
                  Slave_IO_Running: No
分析：
错误1：
日志名不对
        从库信息：
                  Master_Log_File: mysql-bin.000002
                  Read_Master_Log_Pos: 444
于全库备份文件对比
 20 --
 21 
 22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444;
 23                                                                                                                           
 24 --
错误2：主库使用了reset master;语句
主库出现mysql [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
要想把最近的数据也能传到从库上就要从154开头的时候开始
处理方法：

处理：
mysql -S /data/3308/mysql.sock 
stop slave;
reset slave all ;
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
start slave;
（3）写relaylog
（4）更新master.info

4.2.2 SQL线程故障

（1）读relay-log.info
     读relay-log.，并执行日志
     更新relay-log.info
以上文件损坏，最好是重新构建主从
（2）为什么一条SQL语句执行不成功
     1.主从数据库版本差异较大
     2.主从数据库配置参数不一致（例如：sql_mode等）
     3.想要创建的对象已经存在
     4.想要删除或修改的对象不存在
     5.主键冲突  
     6.DML语句不符合表定义及约束时
归根揭底的原因都是由于从库发生了写入操作.

方法一：
stop slave; 
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.

5. 主从延时原因分析

从库延时主库的时间
Seconds_Bxehind_Master：0 从库延时主库的时间（秒为单位）

5.1 主库方面

（1）日志写入不及时
    sync_binlog=1
（2）主库并发业务较高
    “分布式”架构
（3）从库太多
     级联主从
对于Classic Replication：
主库是有能力并发运行事务的，但是在Dumo_T在传输日志的时候，是以事件为单元传输日志的，所以导致事务的传输工作是串行方式的，这时在主库TPS很高时，会产生比较大的主从延时。

怎么处理：从5.6开始加入GTID，在复制时，可以将串行的传输模式变成并行的
除了GTID支持，还需要双一保证。

5.2 从库方面

Classic Replication：

SQL线程只有一个，所以说只能串行执行relay的事务。
怎么解决？
多加几个SQL线程>>>在5.6中出现了多线程SQL
只能针对不同库下的事务，才能并发
到5.7版本加入了MTS，真正实现了事务级别的并发SQL

6. 延时从库

6.1 数据损坏

物理损坏
逻辑损坏
对于传统的主从复制，比较擅长处理物理损坏。

6.2 设置理念

对SQL线程进行延时设置

6.3 延时多久合适？

一般企业，3-6小时

6.4 如何设置？

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL

6.5 如何使用延时从库

6.5.1 思路

mysql [(none)]>
mysql [(none)]>create database delay charset utf8mb4;
mysql [(none)]>use delay;
mysql [delay]>create table t1(id int);
mysql [delay]>insert into t1 values(1),(2),(3);
mysql [delay]>commit;

drop database delay;
发现问题啦：
（1）停止SQL线程，停止主库业务
（2）模拟SQL手工恢复relaylog到drop之前的位置点
（3）截取relaylog日志，找到起点（relay-log.info）和终点（drop操作）
（4）恢复截取的日志，验证

开始处理：
1. 停从库的SQL线程 
mysql -S /data/3308/mysql.sock 
mysql> stop slave sql_thread;
2. 找relaylog的起点和终点
起点：
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 476

终点： 
mysql> show relaylog events in 'db01-relay-bin.000002'
| db01-relay-bin.000002 | 1149 | Query          |         7 |        2036 | drop database delay  

3. 截取日志 
[root@db01 ~]# mysqlbinlog --start-position=476 --stop-position=1149 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql

4. 恢复 
mysql -S /data/3308/mysql.sock 
set sql_log_bin=0;
source /tmp/relay.sql

7. 过滤复制

主库：（了解）
binlog_do_db
binlog_ignore_db
从库：在SQL线程回放日志时，进行控制过滤
              Replicate_Do_DB: 
              Replicate_Ignore_DB: 
              Replicate_Do_Table: 
              Replicate_Ignore_Table: 
              Replicate_Wild_Do_Table: 
              Replicate_Wild_Ignore_Table: 

例子： 
只需要复制xyz库的数据到从库
[root@db01 ~]# vim  /data/3308/my.cnf
replicate_do_db=xyz

systemctl restart mysqld3308
注意：如果有多个库的话，写多行即可。

8. 半同步复制

加载插件
主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
从:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加载成功:
show plugins;
启动:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
从:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在运行
主:
show status like 'Rpl_semi_sync_master_status';
从:
show status like 'Rpl_semi_sync_slave_status';

和传统复制区别：
是一个插件形式提供的功能
主库： ack_receiver
从库:  ACK_send
主库的 ack_re 线程 只有接收到 从库发来的ACK确认，主库事务才能commit成功
从库的 Acksend只有等relaylog落地才能发送ACK 
主库只会等10s  如果ACK还没收到的，会自动替换为异步复制。

9.GTID复制（高可用环境）---为下节做准备

9.1 重点

gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1               --->强制刷新从库二进制日志：1. 高可用（MHA）2. 级联复制的中间库

9.2 清理环境

pkill mysqld
\rm -rf /data/*
\rm -rf /data/*
mkdir -p /data/mysql/data 
mkdir -p /data/binlog/
chown -R mysql.mysql /data

9.3 准备配置文件

主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF

slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF

slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF

9.4 初始化数据(所有节点)

mysqld --initialize-insecure --user=mysql --basedir=/application/mysql  --datadir=/data/mysql/data

9.5 启动数据库并创建复制用户

/etc/init.d/mysqld.server start
db01 [(none)]>grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

9.6 两个从库开启主从

mysql -e "change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;start slave; "

 mysql -e "show slave status \G"|grep Yes

配置完成