HEBIN博客


一个专注于IT原创技术教程分享的博客

MySQL 主从复制

2020年7月9日 1条评论 4.44k次阅读 4人点赞 HEBIN
文章目录
一、主从复制原理
1.主从复制原理
2.主从复制过程
二、主从复制搭建
1.主库配置
2.从库配置
三、主从故障处理
1.IO线程故障
2.SQL线程故障
四、延时从库
1.对已经为从库的数据库做延时从库
2.对新数据配置延迟从库
五、过滤复制
1.过滤复制的方式
2.在主库配置过滤复制
3.在从库配置过滤复制
4.过滤复制总结

一、主从复制原理

mysql_master-slave_ replication.jpg

主库有一个线程：binlog dump线程。

从库有两个线程：IO线程、SQL线程。

1.主从复制原理

  • 用户对数据的修改进行提交，然后Master（主库）把所有数据库的变更写进binlog中，主库线程binlog dump把binlog内容推送给Slave（从库）。

    注意：启动复制通道后，第一次是从库告诉主库要从哪个位置点或者gtid位执行，然后主库启动一个dump线程，向从库推数据。

  • 从库I/O线程读取主库上的binlog信息，并把binlog写到本地中继日志 （relay log）中。

  • 从库SQL线程读取并解析ralay log内容，按照主库中的提交顺序进行事务回放，写 入本地数据文件中，这样就实现了数据在主从实例之间的同步。

注意：主库在写入binlog并落盘之后，通知dump线程有新的 binlog产生，并发送到从库中。然后主库并不理会从库是否接收到binlog，而是自顾自地照常进行事务的提交。

2.主从复制过程

  • 从库通过执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； 然后start slave启动从库线程。
  • 从库的IO线程和主库的dump线程建立连接。
  • 从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。
  • 主库dump线程根据从库的请求，将本地binlog以events的方式推送给从库IO线程。
  • 从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中
  • 从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge
master.info：用于保存从库的I/O线程连接主库的连接状态、账号、IP地址、端口、密码，以及I/O线程当前读取主库binlog的文件和位置 信息（称为I/O线程信息日志）。

relay-log.info：当从库的I/O线程从主库获取 到最新的binlog事件信息后会先写入从库本地的relay log中，然后SQL线程再去读取relay log解析并重放。relay-log.info就是用于记录最新的 relay log的文件和位置，以及SQL线程当前重放的事件对应的主库binlog的文件和位置信息 的（SQL线程位置被称为SQL线程信息日志）。

二、主从复制搭建

1.主库配置

1.1 修改主库配置文件

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
skip_name_resolve = ON
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log_bin = mysql-bin     #开启binlog
server_id = 1       #该参数在同一个复制架构中需要保持唯一

[client]
socket = /tmp/mysql.sock

1.2 在主库授权一个用户，用与从库连接主库‘

mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';

1.3 查看主库的binlog信息，从库连接主库时需要用到。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      326 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.从库配置

2.1 修改配置文件

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
skip_name_resolve = ON
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
server_id = 2   #该参数在同一个复制架构中需要保持唯一

[client]
socket = /tmp/mysql.sock

2.2 从主库的备份同步主库数据（为了保证主库、从库数据一致，可省略）

[root@db02 ~]# mysql -uroot -p123 < master.sql

2.3 登录从库，执行change master to 语句

mysql > change master to 
master_host='172.16.1.51',
master_port=3306,
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000002',
master_log_pos=326;

2.4 启动从库线程

mysql> start slave;

2.4 查看从库IO线程和SQL线程是否正常

[root@db02 ~]# mysql -uroot -p123 -e "show slave status\G" | grep -Ew "Slave_(IO|SQL)_Running"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

三、主从故障处理

1.IO线程故障

  • IO线程为Connecting状态
[root@db02 ~]# mysql -uroot -p123 -e "show slave status\G" | grep -Ew "Slave_IO_Running"
             Slave_IO_Running: Connecting
  • IO线程为No状态
[root@db02 ~]# mysql -uroot -p123 -e "show slave status\G" | grep -Ew "Slave_IO_Running"
             Slave_IO_Running: No

出现这2种状态的故障一般有以下几种原因：

1.从库与主库的网络不通

2.主库的mysql服务没有启动

3.从库在执行change master to语句时，配置有问题

4.从库在连接主库的数据库时，IP被反向解析成了主机名

5.开启了防火墙，并未对mysql服务、端口放行

2.SQL线程故障

  • SQL线程为No状态
[root@db02 ~]# mysql -uroot -p123 -e "show slave status\G" | grep -Ew "Slave_SQL_Running"
            Slave_SQL_Running: No

SQL线程出现故障的原因一般都是主库和从库数据不一致：

1.主库有数据，从库没有
2.从库有数据，主库没有
3.主库与从库数据库结构不一致

SQL线程故障解决方法：

方法一：让从库跳过下一个event记录

1.停止从库SQL线程

mysql> stop slave sql_thread;

2.跳过下一个event记录（错误的event）

若有N个错的的events，可以跳过N个events

mysql> set global sql_slave_skip_counter=1;

3.启动SQL线程

mysql> start slave sql_thread;

方法二：跳过错误代码

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
slave_skip_errors=1107,1032,1062

方法三：重新同步主库数据、重做从库（同步数据前，从库环境要纯净）

四、延时从库

1.对已经为从库的数据库做延时从库

  • 停止从库的IO、SQL线程
mysql> stop slave;
  • 执行change master to 语句，配置延迟从库
mysql> change master to master_delay=3600;
  • 启动从库线程
mysql> start slave;
  • 查看从库的状态
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                    SQL_Delay: 3600     #延时从库延时的时间
          SQL_Remaining_Delay: NULL     #执行语句倒计时，如果主库没有操作，为NULL

2.对新数据配置延迟从库

  • 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
  • 从主库的备份同步主库数据（为了保证主库、从库数据一致，可省略）
[root@db02 ~]# mysql -uroot -p123 < master.sql
  • 在从库执行change master to 语句，配置延迟从库
mysql > change master to 
master_host='172.16.1.51',
master_port=3306,
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000002',
master_log_pos=326,
master_delay=3600;
  • 启动从库线程
mysql> start slave;
  • 查看从库的状态
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                    SQL_Delay: 3600     #延时从库延时的时间
          SQL_Remaining_Delay: NULL     #执行语句倒计时，如果主库没有操作，为NULL

五、过滤复制

过滤复制其实就是在从库或者主库，配置白名单、黑名单，只针对某些库、表进行复制。

1.过滤复制的方式

  • 白名单：只执行白名单中列出的库或者表的binlog

配置方法：

#只同步test1库
replicate_do_db=test1
#只同步test1库下面的tb1表
replicate_do_table=test1.tb1
#同步test1库下面的t开头的所有表（支持通配符）
replicate_wild_do_table=test.t*

#如果需要同步多个库，则可以用逗号隔开，如：test1,test2,test3
#也可以写多条配置。
  • 黑名单：不执行黑名单中列出的库或者表的binlog

配置方法：

#不同步test2库
replicate_ignore_db=test2
#不同步test2库下面的tb2表
replicate_ignore_table=test2.tb2
#不同步test2库下面的t开头的所有表（支持通配符）
replicate_wild_ignore_table=test2.t*

#如果需要指定多个库，则可以用逗号隔开，如：test1,test2,test3
#也可以写多条配置。

2.在主库配置过滤复制

在主库配置过滤复制后，从库的IO线程只会读取主库的白名单内或黑名单外的库或表的binlog（即：主库的dump线程只会推送白名单内或黑名单外的表库的binlog给从库）。

  • 给主库配置白名单
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
replicate_do_db=yxlm

保存并重启数据库

  • 查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      120 | yxlm         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在Binlog_Do_DB看到只同步yxlm库.

在数据中新建一些库、表

mysql> create table yxlm.hsmg(id int);
mysql> create database db_test1;
mysql> create database db_test2;
  • 去从库查看数据库信息

看到在主库执行的三条语句当中，只有第一条同步了过来。

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

mysql> use yxlm
mysql> show tables;
+----------------+
| Tables_in_yxlm |
+----------------+
| hsmg           |
+----------------+
1 row in set (0.00 sec)

查看从库中继日志：

mysql_master_slave-1.png
从中继日志中，确实发现只记录了create table yxlm.hsmg(id int)语句。证明了从库IO线程只读取了主库的有关yxlm库的binlog。

3.在从库配置过滤复制

在从库配置过滤复制后，从库的IO线程会读取主库的白名单内或黑名单外的所有库或表的binlog，但是从库的SQL线程只执行白名单内或黑名单外的所有库或表的binlog。

  • 在主库创建几个新库
mysql> create database jdqs;
mysql> create database lol;
  • 给从库配置白名单
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
replicate_do_db=jdqs,lol

查看从库的主从状态：

mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: jdqs,lol
  • 再次在主库创建一些库和表
mysql> create table jdqs.yafu(id int);
mysql> create table lol.cjzd(id int);
mysql> create database new_test1;
mysql> create database new_test2;

查看从库数据库信息

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

mysql> use lol
mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| hsmg          |
+---------------+
1 row in set (0.00 sec)

mysql> use jdqs
mysql> show tables;
+----------------+
| Tables_in_jdqs |
+----------------+
| yafu           |
+----------------+
1 row in set (0.00 sec)

从结果来看，new_test1库和new_test2库并没有创建。

在查看下从库的binlog日志：

mysql_master_slave-2.png

发现在主库执行的4条SQL，从库中继日志中都记录了下来。

但因为在从库设置了白名单，所以SQL线程只重写了前2行SQL命令。

4.过滤复制总结

  • 在主库配置时：

1.配置白名单：主库只将白名单配置的库相关语句记录到binlog
2.配置黑名单：主库只不记录黑名单配置的库相关语句到binlog

  • 在从库配置时：

1.配置白名单：IO线程将主库数据拿到relay-log，但是SQL线程只执行白名单设置的库相关语句
2.配置黑名单：IO线程将主库数据拿到relay-log，但是SQL线程不执行黑名单设置的库相关语句

本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可
点赞
最后编辑：2020年7月9日
  1. Pingback： MySQL 半同步复制 - HEBIN博客

发表回复