你的位置:首页 > 信息动态 > 新闻中心
信息动态
联系我们

MySQL 主从复制

2021-11-20 5:05:39

文章目录

  • 一、 主从复制介绍
    • 1.1 什么是主从复制
    • 1.2 为何要做主从
    • 1.3 主从复制架构
    • 1.4 主从复制原理
  • 二、主从复制实现
    • 2.1 主节点配置
    • 2.2 从节点配置
    • 2.3 当主节点业务运行一段时间后,后期加载从节点的实现思路
    • 2.3 同步时出现错误解决方案
    • 2.4 如果在从节点写错了与主服务器的同步信息,需要先清除后重新写
    • 2.5 主从复制时候可以指定 START SLAVE 语句,指定执到特定的点
  • 三、级联复制实现
  • 四、主主复制
  • 六、复制过滤器

一、 主从复制介绍

1.1 什么是主从复制

将主服务器的binlog日志复制到从服务器上执行一遍,达到主从数据的一致状态,称之为主从复制。一句话表示就是,主数据库做什么,从数据库就跟着做什么。

1.2 为何要做主从

  • 为实现服务器负载均衡/读写分离做铺垫,提升访问速度

    #1、什么是读写分离
    有了主从保持数据一致作为大前提,我们便可以分离读写操作,其中Master负责写操作的负载,
    也就是说一切写的操作都在Master上进行,而读的操作则分摊到Slave上进行。
    
    #2、读写分离的作用
    先说答案:读写分离可以大大提高读取的效率。
    
    在一般的互联网应用中,经过一些数据调查得出结论,读/写的比例大概在 101左右 ,
    也就是说写操作非常少,大量的数据操作是集中在读的操作(如某些应用,像基金净值预测的网站。
    其数据的更新都是有管理员更新的,即更新的用户比较少。而查询的用户数 量会非常的多。)
    此时我们可以制作一主多从,因为写操作很少,所以由一个主库负责即可,而大量的读操作则分配给
    多个从库,这样占据比例最大的读操作的压力就被负载均衡了,因此读效率会得到了极大的提升,
    另外,还有一个原因就是:写操作涉及到锁的问题,不管是行锁还是表锁还是块锁,
    都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上,
    而读操作其其他的N个节点上进行,这从另一个方面有效的提高了读的效率,保证了系统的性能及高可用性。
    
    #3、具体做法
    方案一:
    就是主库写,从库读
    
    方案二:
    主库负责写,还有部分读,从库只负责读,而且是读操作的主力
    即当主服务器比较忙时,部分查询请求会自动发送到从服务器中,以降低主服务器的工作负荷。
    
  • 通过复制实现数据的异地备份,保障数据安全

  • 提高数据库系统的可用性

1.3 主从复制架构

一主一从复制架构
在这里插入图片描述
一主多从复制架构
在这里插入图片描述
级联复制、一主一从、一主多从、双主模式。
在这里插入图片描述

1.4 主从复制原理

在这里插入图片描述
主从复制相关线程

  • 主节点:
    • dump Thread:为每个Slave的I/O Thread启动一个dump线程,用于向其发送binary log events
  • 从节点:
    • I/O Thread:向Master请求二进制日志事件,并保存于中继日志中
    • SQL Thread:从中继日志中读取日志事件,在本地完成重放

跟复制功能相关的文件:

  • master.info:用于保存slave连接至master时的相关信息,例如账号、密码、服务器地址等
  • relay-log.info:保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
  • mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志

MySQL8.0 取消 master.info 和 relay-log.info文件

终极日志如下:

[root@rocky8 ~]# file /var/lib/mysql/rocky8-relay-bin.000001
/var/lib/mysql/rocky8-relay-bin.000001: MySQL replication log, server id 1 MySQL V5+, server version 8.0.26

二、主从复制实现

2.1 主节点配置

1.在主节点开启二进制日志

vim /etc/my.cnf
[mysqld]
log_bin=1

2.为当前节点设置一个全局惟一的ID号

vim /etc/my.cnf
[mysqld]
log_bin=1
server-id=1
log-basename=master  #可选项,设置datadir中日志名称,确保不依赖主机名
server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2),默认值为1
0 to 4294967295 (<= MariaDB 10.2.1),默认值为0,如果从节点为0,所有master都将拒绝此
slave的连接

3.查看主节点二进制日志的文件和位置开始进行复制

SHOW MASTER STATUS;

4.创建有复制权限的用户账号

GRANT REPLICATION SLAVE  ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

#MySQL8.0 分成两步实现
mysql> create user tom@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to tom@'10.0.0.%';

具体步骤

1.修改配置文件
vim /etc/my.cnf

[mysqld]
log_bin=/data/mysql/logs/mysql-bin # 二进制日志存放目录及前缀
server-id=1 # 指定唯一id号

2.创建目录并且授权     
[root@rocky8 ~]# mkdir -p /data/mysql/logs
[root@rocky8 ~]# chown -R mysql.mysql /data/mysql 

3.重启mysql

4.登录数据库并查看当前二进制日志位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
5.创建账号
mysql> create user tom@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.*  to tom@'10.0.0.%';

2.2 从节点配置

1.启动中继日志

[mysqld]
server_id=# #为当前节点设置一个全局惟的ID号
log-bin # 建议从节点也开启二进制日志
read_only=ON #设置数据库只读,针对supper user无效
relay_log=relay-log #relay log的文件路径,默认值hostname-relay-bin
relay_log_index=relay-log.index  #默认值hostname-relay-bin.index

2.使用有复制权限的用户账号连接至主服务器,并启动复制线程

CHANGE MASTER TO MASTER_HOST='10.0.0.201', 
MASTER_USER='tom', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='mysql-bin.000003', 
MASTER_LOG_POS= 156;


START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;

具体操作
1.安装和主节点一样版本的数据库
2.修改从节点配置
3.启动从节点
4.执行change master to 命令

mysql> show slave status;
Empty set, 1 warning (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.201', 
    -> MASTER_USER='tom', 
    -> MASTER_PASSWORD='123456', 
    -> MASTER_LOG_FILE='mysql-bin.000003', 
    -> MASTER_LOG_POS= 156;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

5.再次查看状态

mysql> show slave status;
               Slave_IO_State: 
                  Master_Host: 10.0.0.201
                  Master_User: tom
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 156
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No

可以看出 Slave_IO_Running: No , Slave_SQL_Running: No 都未开启
Seconds_Behind_Master: NULL
5.开启线程

mysql> start slave;


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.201
                  Master_User: tom
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 156
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 371
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes # 已经开启
            Slave_SQL_Running: Yes # 已经开启
			。。。
			  Seconds_Behind_Master: 0 # 已经显示出数据

6。查看状态

[root@rocky8 my.cnf.d]# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 10.0.0.201:22           10.0.0.1:3435           ESTABLISHED 4329/sshd: root [pr 
tcp        0     52 10.0.0.201:22           10.0.0.1:7359           ESTABLISHED 4464/sshd: root [pr 
tcp6       0      0 10.0.0.201:3306         10.0.0.211:49566        ESTABLISHED 5261/mysqld  

可以看到从节点与主节点保持持续的连接。

2.3 当主节点业务运行一段时间后,后期加载从节点的实现思路

主节点:需要先把主节点的数据做个完全备份,然后开启新的二进制日志
从节点:需要先恢复完全备份的数据,然后同步主节点上完全备份后新的二进制日志

1.主节点全量备份

[root@rocky8 ~]# mysqldump -uroot -p123 -A -F --single-transaction --master-data=1 > /opt/full_backup.sql

2.将全量备份的sql文件拷贝到从节点上去然后change master to 字段下添加如下:

vim /root/full_backup.sql

CHANGE MASTER TO 
MASTER_HOST='10.0.0.201',
MASTER_USER='tom',
MASTER_PASSWORD='123456', # 手动添加的

MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=156; #自动生成的

3.配置从节点的配置文件

[mysqld]
server-id=2
log-bin

read_only=ON

relay_log=relay-log
relay_log_index=relay-log.index


4.重启从节点数据库,执行sql语句

mysql> source /root/full_backup.sql

6.开启IO和sql线程

mysql> start slave;

2.3 同步时出现错误解决方案

可以在从服务器忽略几个主服务器的复制事件,此为global变量,或指定跳过事件的ID
注意: Centos 8.1以上版本上的MariaDB10.3主从节点同时建同名的库和表不会冲突,建主键记录会产生冲突
方式1.

#系统变量,在从节点上指定跳过复制事件的个数
mysql> SET GLOBAL sql_slave_skip_counter = N;
mysql> stop slave; # 先停止
mysql> start slave; # 在启动

方式2.

#服务器选项,只读系统变量,指定跳过事件的ID,需要在配置文件中指定
[mysqld]
slave_skip_errors=1007|ALL (all表示忽略全部错误ID)

2.4 如果在从节点写错了与主服务器的同步信息,需要先清除后重新写

在从节点上执行

mysql> reset slave all #清除所有从服务器上设置的主服务器同步信息,如HOST,PORT, USER和 PASSWORD 
等

2.5 主从复制时候可以指定 START SLAVE 语句,指定执到特定的点

START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS =
log_pos
START SLAVE [SQL_THREAD] UNTIL   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS =
log_pos
thread_types:
    [thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD

三、级联复制实现

需要在中间的从服务器启用以下配置 ,实现中间slave节点能将master的二进制日志在本机进行数据库更新,并且也同时更新本机的二进制,从而实现级联复制

[mysqld]
server-id=18
log_bin
log_slave_updates      #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加,其它
版本默认不开启
read-onl

在这里插入图片描述
步骤:1.在现有的主从模式下,只需要在中间节点开启log_slave_updates
2.开始事务完全备份从节点的数据到第三节点
3.第三节点配置同步第二节点即可

四、主主复制

主主复制:两个节点,都可以更新数据,并且互为主从
容易产生的问题:数据不一致;因此慎用
考虑要点:自动增长id
配置一个节点使用奇数id

auto_increment_offset=1   #开始点
auto_increment_increment=2 #增长幅度

另一个节点使用偶数id

auto_increment_offset=2
auto_increment_increment=2

主主复制的配置步骤:
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点,并启动复制线程

五、半同步
在这里插入图片描述

默认情况下,MySQL的复制功能是异步的,异步复制可以提供最佳的性能,主库把binlog日志发送给从库即结束,并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时,有可能从服务器没有接收到主服务器发送过来的binlog日志,这就会造成主服务器和从服务器的数据不一致,甚至在恢复时造成数据的丢失
而采取完全同步的消息通知机制,有可能会等待很久,所以采取半同步,只要有一个从节点同步完成立刻返回成功的消息。
CentOS8 在MySQL8.0 实现半同步复制
是由插件支撑的

#查看插件文件
[root@centos8 ~]#rpm -ql mysql-server |grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so

1.安装插件

#主服务器配置:
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插
件

2.修改主配置文件

#master服务器配置
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log-bin
rpl_semi_sync_master_enabled=ON #修改此行,需要先安装semisync_master.so插件后,再重启,否
则无法启动
rpl_semi_sync_master_timeout=3000   #设置3s内无法同步,也将返回成功信息给客户端

3.修改从节点主配置文件

先安装插件
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

#slave服务器配置
[root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则
无法启动

[root@slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则
无法启动


#注意:如果已经实现主从复制,需要stop slave;start slave;
mysql> stop slave;
mysql> start slave;

CentOS 8 在Mariadb-10.3.11上实现 实现半同步复制
不需要安装插件,只需要指定如下

#在master实现,启用半同步功能
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
plugin-load-add = semisync_master # 添加插件
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000   #设置3s内无法同步,也将返回成功信息给客户端

在所有slave节点也不需要安装插件,如下配置:

[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=18
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON  

六、复制过滤器

让从节点仅复制指定的数据库,或指定数据库的指定表
复制过滤器两种实现方式:
(1) 服务器选项:主服务器仅向二进制日志中记录与特定数据库相关的事件
缺点:基于二进制还原将无法实现;不建议使用
优点: 只需要在主节点配置一次即可

vim /etc/my.cnf
binlog-do-db=db1 #数据库白名单列表,不支持同时指定多个值,如果想实现多个数据库需多行实现
binlog-do-db=db2 
binlog-ignore-db= #数据库黑名单列表

(2) 从服务器SQL_THREAD在relay log中的事件时,仅读取与特定数据库(特定表)相关的事件并应用于本地
缺点:会造成网络及磁盘IO浪费,在所有从节点都要配置
优点: 不影响二进制备份还原
从服务器上的复制过滤器相关变量