主从复制基础

企业高可用性标准全年无故障率(非计划内故障停机)

99.9%                 ----> 0.001*365*24*60=525.6  min
99.99%                ----> 0.0001*365*24*60=52.56 min
99.999%               ----> 0.0001*365*24*60=5.256 min

企业级高可用架构方案

负载均衡:有一定的高可用性 
LVS  Nginx

主备系统:有高可用性,但是需要切换,是单活的架构
KeeppAlive ,MHA***** ,MMM ,TMHA

真正高可用(多活系统): 
MySQL NDB Cluster 
Oracle RAC  
Sysbase cluster 
PXC,    ***
MGC,    ***
InnoDB Cluster（MGR 5.7.17） ****

主从复制简介 ☆☆☆☆☆

说一说什么是主从复制 ？

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

主从复制前提(主从复制的规划,实施过程) ***

1. 至少两个以上数据库实例
2. 主库开启二进制日志(binlog), server_id,server_uuid不同
3. 主库要有一个专用作复制的用户(replication slave)
4. 通过备份将源数据补偿到从库 (补课过程)
5. 告知从库,user,passwd,IP port,自动复制的起点(change master to)
6. 需要专门的复制线程(三个):Dump thread IO thread SQL thread 开启(start slave)

手撕主从复制(Classic replication) ***

准备多实例环境

image

启动多实例

systemctl restart mysqld3307.service
systemctl restart mysqld3308.service

mysql -S /data/3307/mysql.sock
mysql -S /data/3308/mysql.sock

检查 主库binlog,不同server_id,server_uuid

[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select @@log_bin;select @@server_id"
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "select @@log_bin;select @@server_id"

主库创建复制用户

[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select user,host from mysql.user where user='repl';"

通过备份将源库数据补偿到从库

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

告知从库,用户名,密码,ip,port,自动复制的起点

# change master to 
[root@db01 ~]# mysql -S /data/3307/mysql.sock 
...
mysql [(none)]>help change master to

CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000009',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;

vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=444;

image

启动主从线程

[root@db01 ~]# mysql -S /data/3308/mysql.sock
mysql [(none)]>start slave;

检测主从状态

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|egrep "Running:" 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

简单排错过程

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G;"|grep "Last" 

如果还有问题:

[root@db01 ~]# mysql -S /data/3308/mysql.sock
mysql [(none)]>stop slave ;
mysql [(none)]>reset slave all;
mysql [(none)]>CHANGE MASTER TO xxxx
mysql [(none)]>start slave;

主从复制原理

image

image

1.change master to 时，ip pot user password binlog position写入到master.info进行记录
2. start slave 时，从库会启动IO线程和SQL线程
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将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
10.SQL_T回放完成之后，会更新relay-log.info文件。
11. relay-log会有自动清理的功能。
细节：
1.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求

主从复制过程中涉及到的文件

主库：
  binlog 日志
    mysql-bin.000001
    mysql-bin.000002

从库：
  relaylog 中继日志
      db01-relay-bin.000001
      db01-relay-bin.000002

  master.info 信息文件
    主库信息文件

  relay-log.info 信息文件
    中继日志信息文件

主从复制中涉及到的线程

主库：
Binlog_dump_Thread  二进制日志投递线程

[root@db01 /data/3307/data]# mysql -S /data/3307/mysql.sock -e "show processlist;"
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host       | db   | Command     | Time | State                                                         | Info             |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 48 | repl | db01:34452 | NULL | Binlog Dump |  748 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 51 | root | localhost  | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+

从库: 
Slave_IO_Thread
Slave_SQL_Thread

阶段回顾:

主从复制前提

(1) 两台节点
(2) 二进制日志
(3) server_id不同,uuid
(4) 复制用户
(5) 备份恢复数据到从
(6) 复制信息提供(change master to)
(7) 开线程(start slave)

主从复制的原理

(1) 从库,change master to(ip port user password binlog pos),存master.info
(2) 从库,start slave,开启 IO和SQL
(3) 从库IO,读master.info,连接主库,主库开启binlog_dump_T
(4) 从库IO,读master.info,根据记录的位置号,请求最新的.
(5) 主库dump进行TP,最新的binlog,给从库IO
(6) 从库IO接收,存储到TCP/IP缓存,在TCP/IP层面返回ACK给主库
(7) 从库IO,将缓存数据,落地到relay-log中,并更新master.info 二进制日志信息
(8) 从库SQL,读取relay.info ,获取上次已经执行 到的relay-log的信息,继续回放最新的relay
(9) SQL更新relay.info为最新状态
(10) 补充1: relay-log会自动被定时清理
(11) 补充2: 主库一旦有新的binlog更新,dump会有信号通知,告知IO线程来取新的,来保证复制实时性.

主从复制的监控

从库
show slave status \G
oldguo[(none)]>
oldguo[(none)]>show slave status \G
*************************** 1\. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 154
               Relay_Log_File: db01-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000012
             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: 154
              Relay_Log_Space: 739
              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: c4af0dce-b834-11e9-9e5f-000c2980e248
             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)

oldguo[(none)]>

线程状态

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

线程报错具体信息

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Last"
Last_Errno: 0
Last_Error: 
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 
[root@db01 ~]# 

查看主库连接信息有关

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Master"
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 154
Relay_Master_Log_File: mysql-bin.000012
Exec_Master_Log_Pos: 154
.......
[root@db01 ~]# 

从库和主库延时的时间

 mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Seconds_Behind_Master"

过滤复制相关状态

[root@db01 ~]#  mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep " Replicate_"
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
  Replicate_Ignore_Server_Ids: 
         Replicate_Rewrite_DB: 
[root@db01 ~]# 

延时从库的状态信息

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Delay:"
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL

监控Gtid复制状态信息

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Gtid"
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
[root@db01 ~]# 

中继日志监控

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Relay"
               Relay_Log_File: db01-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000012
              Relay_Log_Space: 739
[root@db01 ~]# 

主从复制故障

IO线程故障

1.读取master.info
    损坏
    信息错误 change master to 信息错误

2.连接主库
    网络
    防火墙
    主库没启
    连接数上限了(默认151个)

以上问题:
  Slave_IO_Running: Connecting
  Last_IO_Error:  xxxxxx
排查方法: 
  通过复制用户,手工连接主库,看报错信息.
修复方法：
  stop slave 
  reset slave all
  change master to
  start slave

3.请求日志 ☆☆☆
    master.info 复制起点
    主库: 损坏,误删除等操作

4.接收日志
relaylog损坏
  stop slave 
  reset slave all
  change master to
  start slave

5.更新master.info

SQL线程故障 ☆☆☆☆☆

1. relay.info

2.回放relaylog中的日志  *****

SQL语句为什么会失败？
  (1) 版本，SQL_Mode不一致
  (2) DDL DML 为什么会失败
    create database table 创建的对象已经存在了
    从库被提前写入了，删除掉异常库，重启主从
    drop database errdb;   要删除和修改的对象不存在.
    alter 
    insert
    update 
    delete 

处理方法(以从库为核心的处理方案)：
方法一：
stop slave; 
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;

方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

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

防止从库写入

(1) 可以设置从库只读.
mysql [(none)]>show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)

注意：
只会影响到普通用户，对管理员用户无效。

(2)加中间件
读写分离。

扩展项

pt-xxx  关于主从方面的工具
检查主从数据一致性
实现主从数据同步

主从延时

什么是主从延时

主库做的事，从库很久才执行

主从延时的现象

(1) 最直观: 主库做变更,从库看数据状态
(2) show slave status \G;
Seconds_Behind_Master: 0 (只能证明，有或者没有)
(3) 计算日志的差异

主从延时的原因

外部因素

1. 网络

2. 硬件
3. 版本差异
4. 参数差异

内部因素

主库：

(1) 二进制日志方面
二进制日志落地不及时
解决方案:
sync_binlog=1
可以将binlog单独存放高性能存储中

(2) Dump_T(默认是串行工作模式)
主库的事务量大
主库发生大事务
解决方案:
1.GTID 模式
2. 双一的保证

如何监控:

主库: show master status;
从库: show slave status \G
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 484

从库：

(1) IO 线程方面
relaylog写入
解决方案：
可以将relaylog单独存放高性能存储中
| relay_log_basename | /data/3308/data/db01-relay-bin |
| relay_log_index | /data/3308/data/db01-relay-bin.index |

(2) SQL线程方面(只有一个，串行回放)
默认SQL线程，只能逐条的回放SQL
事务并发高
大事务

5.6 版本 加入了多SQL复制
按照库(database)级别,进行并发回放SQL
slave_parallel_workers=16
slave_parallel_type=DATABASE

5.7 版本 进行了多SQL复制加强(MTS)
真正按照事务级别，实现了多SQL线程回放
slave_parallel_workers=16
slave_parallel_type=logical_clock

注意：必须依赖于 GTID复制

如何监控 :
(1) 监控取了多少日志
show slave status \G
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1084

(2) 回放了多少日志
[root@db01 /data/3308/data]# cat relay-log.info
7
./db01-relay-bin.000003
920
mysql-bin.000001
1084

主从基础小结

主从前提

主从原理

主从监控

    show master status;
    show processlist;
    show slave status\G
    master.info 
    relay.info

主从故障

IO 
    连接 
    binlog
SQL 
    从库写入
    DML,insert ,update ,delete

主从延时

主
    dump 串行 : GTID,双一 并行
    show slave status\G 
    Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 1084       

    show master status \G

从
SQL 串行  : MTS 
已经拿过来的日志: 
    show slave status\G 
    Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 1084
    已经执行过的:
    ./db01-relay-bin.000003
    920
    mysql-bin.000001
    800

主从复制-高级进阶

延时从库

作用

处理逻辑损坏

如何实现

SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间

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

怎么处理逻辑损坏

drop 操作;
(1) 感知到故障
(2) 停相关业务,挂维护页.
(3) 停延时从库SQL线程.
(4)  手工模拟SQL线程恢复后续的relaylog
    起点:  relayloginfo
    终点: drop
(5) 直接解除从库替代主库工作
    或者,导出故障数据库,倒回到生产.

演练

(1) 模拟数据
create database oldboy charset utf8mb4;
use oldboy ;
create table t1 (id int);
insert into t1 values(1);
insert into t1 values(11);
insert into t1 values(111);
insert into t1 values(1111);
insert into t1 values(11111);

(2) 模拟误操作 
drop database oldboy;

(3) 停从库线程
stop slave ;

(4) 截取relaylog日志
起点：
  show slave status \G；
  Relay_Log_File: db01-relay-bin.000002
  Relay_Log_Pos: 320
终点：
  show relaylog events in 'db01-relay-bin.000002';
  | db01-relay-bin.000002 | 2172 | Query          |         7 |        3849 | drop database oldboy 

320—2172
[root@db01 ~]# mysqlbinlog --start-position=320 --stop-position=2172 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql

(5) 恢复数据
mysql [(none)]>source /tmp//relay.sql;
mysql [oldboy]>select * from t1;
+-------+
| id    |
+-------+
|     1 |
|    11 |
|   111 |
|  1111 |
| 11111 |
+-------+

(6) 将备份的数据恢复到主库中
mysql -S /data/3307/mysql.sock </tmp/relay.sql
use oldboy;
mysql [oldboy]>select * from t1;
+-------+
| id    |
+-------+
|     1 |
|    11 |
|   111 |
|  1111 |
| 11111 |
+-------+

(7) 开启从库线程
start slave ;
show slave status \G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

过滤复制

主库方面实现

binlog_do_db      白名单
binlog_ignore_db  黑名单

从库参数

Replicate_Do_DB: 
Replicate_Ignore_DB: 

Replicate_Do_Table: 
Replicate_Ignore_Table: 

Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 

过滤复制实现(从库)

例子: 只复制mysql库的数据

vim /data/3308/my.cnf 
replicate_do_db=oldguo

systemctl restart mysqld3308  重启从库
show slave status \G;

GTID复制

GTID介绍

GTID(Global Transaction ID)是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29

什么是sever_uuid，和Server-id 区别？
核心特性: 全局唯一,具备幂等性

GTID核心参数

重要参数：
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更新是否记入日志

GTID复制配置过程：

image

清理环境

pkill mysqld
 \rm -rf /data/mysql/data/*
 \rm -rf /data/binlog/*

mkdir -p /data/mysql/data 
chown -R mysql.mysql /data/*

准备配置文件

# 主库 db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/data/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=/data/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=/data/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

初始化数据

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

启动数据库

/etc/init.d/mysqld start

构建主从：

master:51
slave:52,53

10.0.0.51主库上创建用户:
mysql -e "grant replication slave  on *.* to repl@'10.0.0.%' identified by '123';"

10.0.0.52\53从库上创建用户:
mysql -e "change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;"
mysql -e  "start slave;"

查看主从状态

10.0.0.52\53 :
[root@db02 ~]# mysql -e  "show slave status \G"|grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

[root@db03 ~]# mysql -e  "show slave status \G"|grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

GTID 复制和普通复制的区别

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=444,
MASTER_CONNECT_RETRY=10;

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

（0）在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover
（1）额外功能参数（3个）
（2）change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
（3）在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的 GTID号
（4） mysqldump备份时，默认会将备份中包含的事务操作，以以下方式
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';
告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。

半同步复制(了解)

主从数据一致性

ACK
ACK_Receiver

自己扩展了解下:
MGR 
PXC