MySQL 主从复制

一、主从复制原理

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线程不执行黑名单设置的库相关语句

点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注