第1章 MySQL主从同步介绍

1.1 数据库损坏介绍

数据库损坏可以理解为业务不能使用数据库的情况，主要有外在原因和数据库本身的原因两方面。

外在原因：

网络原因
业务应用存在问题导致的客户端损坏

数据库本身的原因：

物理损坏：服务器、硬盘、存储、数据文件的损坏
逻辑损坏：误 drop 、delete 、truncate ……update等的操作命令

解决方法：

备份
主从同步

1.2 MySQL同步简介

复制是 MySQL 的一项功能，允许服务器将更改从一个实例复制到另一个实例。主服务器将所有数据和结构更改记录到二进制日志中。从属服务器从主服务器请求该二进制日志并在本地应用其内容。

1.3 主从同步的作用

高可用
辅助备份
负载分担

1.4 MySQL同步应用场景

从服务器作为主服务器的实时数据备份
主从服务器实现读写分离，从服务器实现负载均衡
把多个从服务器根据业务重要性进行拆分访问

第2章 MySQL同步的结构原理

2.1 MySQL同步原理总览

至少两台服务器（一主一从或多主多从）通过二进制日志文件进行主从同步
主服务器的二进制日志自动同步到从服务器上再运行一遍

提示：通过网络连接两台服务器，一般都会出现延迟的状态，所以也可以说是异步的

2.2 MySQL同步原理详解（第一次开启主从同步）

从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user、password、port、ip），并且让从库知道，二进制日志的起点位置（file名、position号）
从库的IO和主库的dump线程建立连接
从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求
主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程
从库IO线程接收binlog evnets，并放到本地relay-log中，传送过来的信息会记录到info中。
从库应用relay-log，并且把应用过的记录存放到relay-log.info，默认情况下已经应用过的relay会自动被清理purge

至此第一次主从同步就已经完成，一旦主从运行起来就不需要再手工执行change master to了，因为信息都会被存放到master.info中，之后的每次同步其他的过程都是一样的。

图片[2]|MySQL主从同步（十一）|leon的博客

2.3 主从同步的必要条件

主服务期一定要打开二进制日志
必须两台以上服务器（或者是多个实例）
从服务器需要一次数据初始化

如果主从服务器都是新搭建的话，可以不做初始化
如果主服务器已经运行了很长时间，可以通过备份将主库数据恢复到从库。

主库必须要有对从库复制请求的用户。
从库需要有relay-log设置，存放从主库传送过来的二进制日志
在第一次的时候，从库需要change master to 去连接主库。
change master信息需要存放到info中
从库是通过relay-log.info记录的已经应用过的relay-log信息感知主库发生了新的变化。
在复制过程中涉及到的线程：

从库会开启一个IO thread，负责连接主库，请求binlog，接收binlog并写入relay-log。
从库会开启一个SQL thread，负责执行relay-log中的事件。
主库会开启一个dump thrad，负责响应从IO thread的请求。

2.3.1 复制中的线程

主库：Dump thread
从库：IO thread、SQL thread

2.3.2 复制中的日志及文件

主库：binlog
从库：Relay-log、Relay-index、info、Relay-log.info

第3章基本主从同步配置实践

3.1 配置思路

两个节点
主库binlog开启，从库开启relay-log
server-id不同
主库开启复制账户
主库备份并记录二进制文件和position
从库change master to连接主库
启动从库复制
验证主从

3.2 配置实践

3.2.1 修改配置文件

3.2.1.1 主库配置文件

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 41

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

3.2.1.2 从库配置文件

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server-id = 42

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

3.2.2 主库中创建从库用户

[root@db01 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.17 sec)

3.2.3 全备主库并复制给从库

[root@db01 ~]# mysqldump -uroot -p123456 -A -B -F --master-data=2 > /tmp/full.sql
[root@db01 ~]# scp /tmp/full.sql 10.0.0.42:/tmp/

3.2.4 查看备份文件使用的log-bin文件和position号

[root@db02 ~]# vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;

3.2.5 从库还原主库备份

[root@db02 ~]# mysql -uroot -p123456
mysql> source /tmp/full.sql;

3.2.6 从库连接主库

[root@db02 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.41',               # 主库IP地址
    ->   MASTER_USER='repl',                    # 主库中连接从库的用户
    ->   MASTER_PASSWORD='123456',              # 主库中连接从库的密码
    ->   MASTER_PORT=3306,                      # 主库数据库端口号
    ->   MASTER_LOG_FILE='mysql-bin.000002',    # 备份文件中的log-bin文件
    ->   MASTER_LOG_POS=120;                    # 备份文件中的position号
Query OK, 0 rows affected, 2 warnings (0.21 sec)

相关命令：

CHANGE MASTER TO
  MASTER_HOST='10.0.0.41',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=120;

3.2.7 开启从库进程

[root@db02 ~]# mysql -uroot -p123456
mysql> start slave;
Query OK, 0 rows affected (0.13 sec)

3.2.8 验证从库状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.41
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: db02-relay-bin.000003
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes                  # 显示为Yes表示从库线程状态正常
            Slave_SQL_Running: Yes                  # 显示为Yes表示从库线程状态正常
                ...省略部分输出内容...
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
                ...省略部分输出内容...

3.2.9 查看同步情况

3.2.9.1 主库更新数据库

[root@db01 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.13 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> create table test (              
    -> id int(4) not null auto_increment,
    -> name varchar(20),                
    -> age int(3),                      
    -> primary key (id)                  
    -> );                               
Query OK, 0 rows affected (0.61 sec)

mysql> insert into test values(1,'leon',18),(2,'shadow',20),(3,'shuaige',26),(4,'meinv',19),(5,'wangzi',28);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test.test;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | leon    |   18 |
|  2 | shadow  |   20 |
|  3 | shuaige |   26 |
|  4 | meinv   |   19 |
|  5 | wangzi  |   28 |
+----+---------+------+
5 rows in set (0.00 sec)

3.2.9.2 从库同步数据库

[root@db02 ~]# mysql -uroot -p123456
mysql> select * from test.test;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | leon    |   18 |
|  2 | shadow  |   20 |
|  3 | shuaige |   26 |
|  4 | meinv   |   19 |
|  5 | wangzi  |   28 |
+----+---------+------+
5 rows in set (0.00 sec)

提示：注意同步时注意需要关闭防火墙和SELinux。

第4章主从同步问题汇总

4.1 忘记root密码

【问题现象】：

[root@db01 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

【解决方法】：

[root@db01 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &
[root@db01 ~]# mysql -S /tmp/mysql.sock
mysql> update user set password=password('123456') where user='root' and host='localhost';
mysql> flush privileges;

4.2 Slave_IO_Running: Connecting

【问题现象】：

mysql> show slave status\G;
Last_IO_Error: error reconnecting to master '[email protected]:3306' - retry-time: 60  retries: 1

【原因分析】：

1、主机没启动，或者宕机

2、网络通信问题

3、防火墙未关闭

4、repl用户名或密码发生了变更

4.3 主从库UUID相同

【问题现象】：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

【解决办法】：

更改任意主库或从库的UUID信息即可：

[root@db02 ~]# uuidgen
0e728e35-2a8a-43a1-ba9a-c1d2b680cf51
[root@db02 ~]# vim /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=0e728e35-2a8a-43a1-ba9a-c1d2b680cf51
[root@db02 ~]# /etc/init.d/mysqld restart

4.4 Master_Log_File中的二进制日志文件落后主库

【问题现象】：

从库中记录的，主库已经给从库传送的binlog事件的坐标，一般在繁忙的生产环境下，会落后于主库（show master status）

Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 729

【原因分析】：

若落后的很多（20以上）的原因可能为：

网络问题
主库DUMP太繁忙
从库IO线程太忙

4.5 Last_SQL_Error

【原因分析】：

主库做操作的对象，在从库不存在
主库做操作的对象属性不一致
主库做操作的对象，从库已经存在
……等等

第5章监控主从同步状态

5.1 主从同步关键信息

5.1.1 Slave_*_Running

Slave_IO_Running I/O 线程正在运行、未运行还是正在运行但尚未连接到主服务器。可能值分别为Yes、No 或 Connecting。
Slave_SQL_Running SQL 线程当前正在运行、未运行，可能值分别为 Yes、No

5.1.2 主服务器日志坐标

Master_Log_File 和 Read_Master_Log_Pos 标识主服务器二进制日志中 I/O 线程已经传输的最近事件的坐标。
如果Master_Log_File和Read_Master_Log_Pos 的值远远落后于主服务器上的那些值，这表示主服务器与从属服务器之间事件的网络传输可能存在延迟。

5.1.3 中继日志坐标

Relay_Log_File 和 Relay_Log_Pos 列标识从属服务器中继日志中 SQL 线程已经执行的最近事件的坐标。这些坐标对应于 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列标识的主服务器二进制日志中的坐标。
如果 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列的输出远远落后于 Master_Log_File 和Read_Master_Log_Pos 列（表示 I/O 线程的坐标），这表示 SQL 线程（而不是 I/O 线程）中存在延迟。即，它表示复制日志事件快于执行这些事件。

5.1.4 Last_IO_Error、Last_SQL_Error

分别导致 I/O 线程或 SQL 线程停止的最新错误的错误消息。在正常复制过程中，这些字段是空的。如果发生错误并导致消息显示在以上任一字段中，则错误值也显示在错误日志中。

5.1.5 Last_IO_Errno、Last_SQL_Errno

与分别导致 I/O 线程或 SQL 线程停止的最新错误关联的错误编号。在正常复制过程中，这些字段包含编号 0。

5.1.6 Last_IO_Error_Timestamp、Last_SQL_Error_Timestamp

分别导致 I/O 线程或 SQL 线程停止的最新错误的时间戳，格式为 YYMMDD HH:MM:SS。在正常复制过程中，这些字段是空的。

5.2 查看整体状态

[root@db02 ~]# mysql -uroot -p123456 -e "show slave status\G;"
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.41
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 748
               Relay_Log_File: db02-relay-bin.000003
                Relay_Log_Pos: 911
        Relay_Master_Log_File: mysql-bin.000002
             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: 748
              Relay_Log_Space: 1083
              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: 41
                  Master_UUID: 0b1a934f-a67f-11e7-95e2-000c2982d598
             Master_Info_File: /usr/local/mysql-5.6.34/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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

5.3 查看关键线程状态

[root@db02 ~]# mysql -uroot -p123456 -e "show slave status\G;" | egrep "Running|Behind_Master" | head -3
             Slave_IO_Running: Yes              # 获取bin-log并存储到relay-log的线程
            Slave_SQL_Running: Yes              # 运行relay-log中的events到从库的线程
        Seconds_Behind_Master: 0                # 落后主库的时间

第6章主从同步故障企业案例实战

6.1 企业环境说明

背景：标准主从复制结构，在业务逻辑中有leon数据库，leon数据库下有t1表为生产表。
故障原因：开发人员在从库创建了一个leon1库，觉得不对，后又在主库中做了相同的操作。导致了从库复制失效。

6.2 解决方案

在mysql中可以跳过某些错误。

6.2.1 解决方法一：MySQL语句

mysql> stop slave;                          # 临时停止同步开关
mysql> set global sql_slave_skip_counter=1; # 将同步指针向下移动一个，如果多次不同步可以重复操作
mysql>start slave;                          # 重新启动服务

6.2.2 解决方法二：配置文件

[root@db01 ~]# vim /etc/my.cnf
slave-skip-errors = 1032,1062,1007      # 跳过错误的代码

6.2.3 解决办法三：重新搭建主从复制（推荐）

过程略。

第7章主从复制架构演变

7.1 基本结构

一主一从：使用从库实时备份。如果从库只做备份服务器用，那么主库的压力会不减反增，因为所有的读写业务都在主库实现，dump线程读取并投递binlog会增加。
一主多从：应对读多的业务需求。一旦发展成这个架构，会出现dump线程读取并投递binlog，导致主库的压力更大了。
多级主从：采用中间库缓解主库dump的压力。但会出现中间库瓶颈的问题：权衡性能和安全后中间库可以选择blackhole引擎适当的提高MySQL的效率。
双主：双主未必能提升写入性能，因为每一个主的写入都会实时同步到另外的上，因此一般生产用的不多。
循环复制

7.2 高级应用架构演变

读写分离：MySQL proxy、amoeba、xx-dbproxy等。
分库分表：cobar、自主研发等。
MMM架构：mysql-mmm（google，已经淘汰了）
MHA架构：mysql-master-ha（日本DeNa，有人说已经过时，但还可以使用）
MGR：7新特性 MySQL Group replication
PXC、MySQL Cluster架构

7.3 读写分离简介

7.3.1 实现思路

判断语句类型，根据语句类型进行分发
分发的负载均衡
会话持续性
判断语句是否执行过，如果执行过，就不再进行负载均衡。

7.3.2 读写分离软件

atlas：配置简单，比较实用
自主开发：依赖于业务
cober：依赖与业务

7.4 防止从库误操作

7.4.1 设置从库为只读库

[root@db03 ~]# vim /etc/my.cnf
[mysqld]
read_only = 1   # 重启数据库即可

7.4.2 给从库创建只读用户

跟开发人员明确说明读的时候只给 ruser，写的时候使用wuser（开发制度），添加完成后只要使用新建的ruser和wuser登录数据库进行测试即可：

[root@db01 ~]# mysql -uroot -p123456
# 创建读用户
mysql> grant select on leon.* to ruser@'localhost' identified by '123456';
# 创建写用户
mysql> grant select,insert,update,delete,alter,drop on leon.* to wuser@'localhost' identified by '123456';

7.4.3 选择方式

推荐使用第二种方式：
配置时不用重启
将来如果主库宕机，故障切换时也不需要重启数据库。

7.5 多级主从复制实践

7.5.1 编辑MySQL配置文件

7.5.1.1 主库配置文件

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 41

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

7.5.1.2 中间库配置文件

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server-id = 42
log-slave-updates

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

7.5.1.3 从库配置文件

[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server-id = 43
read_only = 1

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

7.5.2 重启服务器

[root@db01 ~]# /etc/init.d/mysqld restart
[root@db02 ~]# /etc/init.d/mysqld restart
[root@db03 ~]# /etc/init.d/mysqld restart

7.5.3 备份当前主库数据

[root@db01 ~]# mysqldump -uroot -p123456 -A -B -F --master-data=2 > /tmp/full.sql

7.5.4 查看备份文件中二进制文件信息

[root@db01 ~]# vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=120;

7.5.5 备份文件拷贝到中间库和从库

[root@db01 ~]# scp /tmp/full.sql 10.0.0.42:/tmp
[root@db01 ~]# scp /tmp/full.sql 10.0.0.43:/tmp

7.5.6 还原备份文件并连接主库

7.5.6.1 中间库

[root@db02 ~]# mysql
mysql> source /tmp/full.sql;

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.41',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000011',
    ->   MASTER_LOG_POS=120;

mysql> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.41
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 444
               Relay_Log_File: db02-relay-bin.000030
                Relay_Log_Pos: 607
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
             ...省略部分输出内容...

7.5.6.2 从库

[root@db03 ~]# mysql
mysql> source /tmp/full.sql;

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.42',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000011',
    ->   MASTER_LOG_POS=120;

mysql> start slave;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.42
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 120
               Relay_Log_File: db03-relay-bin.000015
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
             ...省略部分输出内容...

7.5.7 进行测试

7.5.7.1 主库中操作

[root@db01 ~]# mysql -uroot -p123456
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> create table t1(name varchar(20));
Query OK, 0 rows affected (0.36 sec)

mysql> insert into t1 values('xiaoming');
Query OK, 1 row affected (0.21 sec)

mysql> select * from t1;
+----------+
| name     |
+----------+
| xiaoming |
+----------+
1 row in set (0.00 sec)

7.5.7.2 从库中查看

[root@db03 ~]# mysql -uroot -p123456
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| test           |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+----------+
| name     |
+----------+
| xiaoming |
+----------+
1 row in set (0.00 sec)

第8章主从复制高级功能

8.1 延时复制

8.1.1 延时复制介绍

对于误删除、误修改（delete drop update）类似的逻辑性错误，普通从库可能很快就会应用此错误操作，对于业务是灾难性的。常规处理方法是找备份恢复并应用binlog到错误操作之前。

由此产生了延时从库。正常主从复制的过程是：误操作binlog—>dump THREAD—>IO THREAD—>relay—>SQL THREAD—>从数据库；而延时从库是控制SQL THREAD不实时的进行应用relay-log操作，从而进行延时同步。

通俗的理解延时同步就是人为设置从库延迟一段时间同步主库，以保证防止主库中的误操作对数据的影响。bin-log文件是实时同步到从库，但是延时一段时间再执行，而不是延时一段时间再同步bin-log文件。

延时同步主要应用在备份库中，一般生产中延时3-6小时。

8.1.2 延时复制配置

[root@db02 ~]# mysql -uroot -p123456
mysql> stop slave;
Query OK, 0 rows affected (0.22 sec)

mysql> change master to master_delay = 30;
Query OK, 0 rows affected (0.20 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

8.1.3 查看测试结果

8.1.3.1 主库操作

[root@db01 ~]# mysql -uroot -p123456
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| test           |
+----------------+
2 rows in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.11 sec)

8.1.3.2 从库查看

[root@db02 ~]# mysql -uroot -p123456

mysql> show slave status\G;
*************************** 1. row ***************************
        Seconds_Behind_Master: 22       # 提示还有22秒进行更新

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.01 sec)

8.2 半同步复制

8.2.1 半同步复制介绍

普通主从是异步的，意味着从库同步到什么程度是不可控的，对于安全性要求比较高的应用场景，如金融、运营商等要求数据安全性极高的企业，为了让mysql更加能够适用于高安全性的场景，产生了半同步复制，半同步复制是为了安全，不是为了性能。

8.2.2 配置半同步复制

8.2.2.1 主库配置

[root@db01 ~]# mysql -uroot -p123456
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.29 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.01 sec)
# 查看是否已经开启延时
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

8.2.2.2 从库配置

[root@db02 ~]# mysql -uroot -p123456
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.26 sec)

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.10 sec)

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.11 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

# 查看是否已经开启延时
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.11 sec)

8.3 复制故障切换（failover）

8.3.1 故障切换的原理

选择新的主库：尽量找一个数据相对新的节点
数据补偿：判断新主库和其他从节点数据的新旧，将自己没有拿过来，尽量恢复到比较新或者去数据补偿服务器，获取到自己没有的那段binlog。binlog server
启动新主库：将其他从库change master to 到新主库上
告诉应用服务器原来的主库已经不适用了，需要连接新的主库

8.4 5.6新特性：GTID复制

8.4.1 GTID复制简介

全局事务ID，对于一个事务组里的events 打以一个唯一标签，是为了更好地做failover而出现的复制方式，从5.6版本开始支持，5.7版本进行完善。GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。

8.4.2 GTID复制配置

8.4.2.1 配置主节点

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 41                          # 同一个复制拓扑中的所有服务器的id号必须惟一

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin
binlog-format=ROW                       # 二进制日志格式，强烈建议为ROW

gtid-mode=on                            # 启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true           # 强制GTID的一致性
log-slave-updates=1                     # slave更新是否记入日志

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

8.4.2.2 配置从节点

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server-id = 42

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin
binlog-format=ROW

gtid-mode=on
enforce-gtid-consistency=true
log_slave_updates = 1

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

8.4.2.3 在主节点创建用户

[root@db01 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.17 sec)

8.4.2.4 从库连接主库

[root@db02 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.41',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

8.4.2.5 开启从库线程

[root@db02 ~]# mysql -uroot -p123456
mysql>start slave;
Query OK, 0 rows affected (0.12 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Auto_Position: 1

温馨提示：本文最后更新于2022-12-20 20:57:51，已超过492天没有更新。某些文章具有时效性，若文章内容或图片资源有错误或已失效，请联系站长。谢谢！
转载请注明本文链接：https://blog.leonshadow.cn/763482/758.html

1. 本网站名称：Leon的博客
2. 本站永久网址：https://blog.leonshadow.cn
3. 本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ632113590进行删除处理。
4. 本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5. 本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6. 本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END