数据库主从复制

目录

一.主从复制架构

二.主从复制原理

三.实现主从复制配置

1.新建主从复制

2.实战遇到问题

3.复制错误解决方法

4.级联 主从复制

5.半同步复制

---

MySQL数据库的主从复制（Master-Slave Replication）是一种常见的数据库复制架构，用于提高数据库的可用性、可靠性和性能。

一.主从复制架构

在MySQL主从复制中，通常涉及以下几个角色：

1. 主服务器（Master）：

   • 主服务器是数据库的主要写入节点。
   • 所有的写操作（INSERT、UPDATE、DELETE）以及数据定义语言（DDL）语句（如CREATE TABLE、ALTER TABLE）都在主服务器上执行。
   • 主服务器将其执行的所有变更记录到二进制日志（Binary Log）中。

2. 从服务器（Slave）：

   • 从服务器是主服务器的备份节点，用于读操作和备份。
   • 从服务器从主服务器上的二进制日志复制数据变更，并将这些变更应用到自己的数据副本中。
   • 从服务器可以用于读取查询操作，从而减轻主服务器的负载。

3. 二进制日志（Binary Log）：

   • 主服务器记录所有变更操作的详细信息到二进制日志中。
   • 从服务器通过读取主服务器的二进制日志来获取主服务器上发生的数据变更。

二.主从复制原理

1主节点负责用户的写操作，用户发起写操作后，会修改数据库
2.数据库修改后，会更新主节点上的二进制日志

4.从服务器会开启io线程，主动请求与主服务器同步
3.主服务器会产生一个  dump线程， 一边读取二进制日志一边将二进制日志通过 网络传给从服务器
5 io线程会将主服务器的二进制日志写入从服务器的中继日志，这时只是生成了一个文件，并没有同步
6.从服务器再开启  sql线程将 中继日志中  操作写入数据库完成更新

主从复制相关线程

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#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志

中继日志 （relay log）只在主从服务器架构的从服务器上存在。从服务器（slave）为了与主服务器（Master）保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

主从复制（Master-Slave Replication）是数据库中常见的一种复制方式，其主要特点包括：

1. 数据复制: 主从复制是将一个数据库（主数据库）的数据复制到一个或多个其他数据库（从数据库）的过程。主数据库的更新操作（写操作）会被记录并同步到所有从数据库上，从而保持数据的一致性。

2. 异步复制: 典型的主从复制是异步进行的，即主数据库执行写操作后，并不等待所有从数据库都接收并应用该操作，而是立即返回给客户端。从数据库在稍后的时间点异步地从主数据库获取更新，并应用到自己的数据集上。

3. 读写分离: 主从复制可以实现读写分离，即主数据库负责处理写操作（写入数据），而从数据库负责处理读操作（读取数据）。这种架构可以有效分担主数据库的读写压力，提高整体性能。

4. 容灾和备份: 通过主从复制可以实现数据的容灾备份。如果主数据库发生故障，可以快速切换到某个从数据库继续提供服务，从而保证系统的可用性和持久性。

5. 分布式数据: 主从复制可以用于在不同地理位置的数据库之间同步数据，从而实现分布式数据管理和协作。

6. 延迟复制: 由于主从复制是异步的，从数据库上的数据更新可能会有一定的延迟，这取决于网络延迟、主从数据库的负载等因素。因此，在某些情况下需要考虑到延迟带来的数据一致性问题。

复制需要考虑二进制日志事件记录格式

• STATEMENT（5.0之前）, Mariadb5.5 默认使用此格式

• ROW（5.1之后，推荐）,MySQL 8.0 默认使用此格式

• MIXED: Mariadb10.3 默认使用此格式

三.实现主从复制配置

官网参考

https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
https://mariadb.com/kb/en/library/setting-up-replication/

1.新建主从复制

主服务器：192.168.240.11

从服务器：192.168.240.14

（1）主从服务器关闭防火墙和selinux

systemctl stop firewalld
setenforce  0 

（2）主从复制主服务器配置

#开启二进制日志
vim  /etc/my.cnf
[mysqld]
server-id=11
log-bin=/data/mysql/mysql-binmkdir  -p /data/mysql
chown  -R mysql.mysql  /data 
systemctl restart  mysqld

（3）从服务器配置

#开启二进制日志
vim  /etc/my.cnf
[mysqld]
server-id=14
log-bin=/data/mysql/mysql-bin
#read only  #只读可加
mkdir  -p /data/mysql
chown  -R mysql.mysql  /data 
systemctl restart  mysqld

（4）主服务器登录数据库，新建同步用户

grant replication slave on *.* to cxk@'%' identified by 'Admin@123';
#新建用户，同时授权用户，mysql 8.0 以上需要分开写
show master status;   #查看同步文件和 同步的位置

（5）从服务器登录数据库，添加主的信息

help change master to;
#查看帮助信息，复制信息并修改
CHANGE MASTER TOMASTER_HOST='192.168.240.11',MASTER_USER='cxk',MASTER_PASSWORD='Admin@123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=436;

（6）开启io线程和sql线程，查看服务器状态

start slave;#开启io线程 以及sq| 线程
show processlist;#看到进程列表
show slave status\G; # 查看从服务器状态

（7）测试主从复制

在主服务器上新建zxy库，查看从服务器有没有同步
create  database  zxy;
show databases;从服务器
show databases;

2.实战遇到问题

假设主节点的数据库已经运行了一段时间，产生了一定量的数据，主从复制只能复制开启后数据，那之前的数据如何处理？

主服务器：192.168.240.11

从服务器：192.168.240.14

 （1）主从服务器关闭防火墙和selinux

systemctl stop firewalld
setenforce  0 

（2）主从复制主节点配置

导入hellodb数据库

mysql  -uroot -p123123  <  /opt/hellodb_innodb.sql

#开启二进制日志
[root@localhost ~]#vim  /etc/my.cnf
server-id=11
log-bin=/data/mysql/mysql-bin
[root@localhost ~]# mkdir  -p  /data/mysql/binlog
[root@localhost ~]# chown mysql.mysql /data/  -R
[root@localhost ~]# systemctl  restart  mysqldmysqldump -uroot -p123123 -A -F --master-data=1  --single-transaction > /data/all.sql

（3）从服务器上配置

[root@localhost ~]#vim  /etc/my.cnf
server-id=14
log-bin=/data/mysql/mysql-bin
#read only  #只读可加
mkdir  -p  /data/mysql/binlog
chown mysql.mysql /data/  -R
systemctl  restart  mysqld

 （4）主服务器登录数据库，新建同步用户

grant replication slave on *.* to cxk@'%' identified by 'Admin@123';
#新建用户，同时授权用户，mysql 8.0 以上需要分开写
scp -r /data/all.sql   192.168.240.14:/opt
#将完全备份文件拷贝到从服务器上

（5）从服务器上编辑从主服务器拷贝的完全备份文件

[root@localhost ~]# vim  /opt/all.sql
#  找到    CHANGE MASTER TO  的行修改如下
CHANGE MASTER TOMASTER_HOST='192.168.240.11',MASTER_USER='cxk',MASTER_PASSWORD='Admin@123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;#由于之前再备份的时候加入了    --master-data=1 选项    就添加了主从复制的选项000002文件的154位置往后开始主从复制
#000002文件154位置之前的  配置由  备份文件自行实现 

（6）进入数据库关闭二进制日志导入完全备份数据库

mysql  -uroot -p123123
set sql_log_bin=0;
source /opt/all.sql ;
start  slave;
show slave status\G;  

（7）查看数据库

3.复制错误解决方法

可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID

#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL  

复制冲突的解决

create table info (id int,name char(10),age char(10));
#先在从上建表#再在主上建表  插入信息
create table info (id int,name char(10),age char(10));
insert info values(1,'a',10);
#此时报错 从主机会报错
show  slave  status\G
#可以跳过错误
stop slave;
set global sql_slave_skip_counter=1;
start slave;
#方法2
vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL 
#systemctl restart mysqld

 更换主从

reset slave  all;
#清空主从配置

4.级联 主从复制

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

主服务器：192.168.240.11

中间节点：192.168.240.12

从节点1：192.168.240.13

从节点2：192.168.240.14

 （1）主从服务器关闭防火墙和selinux

systemctl stop firewalld
setenforce  0 

（2）主服务器配置

#开启二进制日志
[root@localhost ~]#vim  /etc/my.cnf
server-id=11
log-bin=/data/mysql/mysql-bin
[root@localhost ~]# mkdir  -p  /data/mysql/binlog
[root@localhost ~]# chown mysql.mysql /data/  -R
[root@localhost ~]# systemctl  restart  mysqld
select @@server_id;
#可以查看serverid  默认都是1
show master status;
#查看二进制日志位置
grant replication slave on *.* to cxk@'%' identified by 'Admin@123';

（2）中间节点

root@localhost ~]#vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=12
log-bin=/data/mysql/mysql-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
log_slave_updates
#read-only  #只读可加
[root@localhost ~]#mkdir /data/mysql/   -p
#建立文件夹
[root@localhost ~]#chown mysql.mysql /data/ -R
#注意修改权限
[root@localhost ~]#systemctl restart mysqld

下面修改配置 命令较长可以使用帮助
help change master to
CHANGE MASTER TOMASTER_HOST='192.168.240.11',MASTER_USER='cxk',MASTER_PASSWORD='Admin@123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=436;start slave;
#开启线程，开启主从复制
show slave status\G;
#查看设置的状态

show master logs;
#查看  从节点的复制位置

grant replication slave on *.* to cxk@'%' identified by 'Admin@123';
创建用户，用于连接数据库

（3）从节点1

[root@localhost ~]#vim  /etc/my.cnf
server-id=13
log-bin=/data/mysql/mysql-bin
#read only  #只读可加
mkdir  -p  /data/mysql/binlog
chown mysql.mysql /data/  -R
systemctl  restart  mysqld

help change master to;
CHANGE MASTER TOMASTER_HOST='192.168.240.12',MASTER_USER='cxk',MASTER_PASSWORD='Admin@123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;show slave status\G;
#查看设置的状态
Seconds_Behind_Master: NULL    #目前数据差start slave;
#开启线程，开启主从复制
show slave status\G;

（4）从节点2

[root@localhost ~]#vim  /etc/my.cnf
server-id=14
log-bin=/data/mysql/mysql-bin
#read only  #只读可加
mkdir  -p  /data/mysql/binlog
chown mysql.mysql /data/  -R
systemctl  restart  mysqld

help change master to;
CHANGE MASTER TOMASTER_HOST='192.168.240.12',MASTER_USER='cxk',MASTER_PASSWORD='Admin@123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;show slave status\G;
#查看设置的状态
Seconds_Behind_Master: NULL    #目前数据差start slave;
#开启线程，开启主从复制
show slave status\G;

 

（5）测试

5.半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

实战测试

主服务器：192.168.240.11

从服务器1：192.168.240.12

从服务器2：192.168.240.14

（1）主服务器

[root@localhost ~]#vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=11
log-bin=/data/mysql/mysql-bin
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
#修改此行,需要先安装semisync_master.so插件后,再重启,否则无法启动 开启半同步
#设置3s内无法同步，也将返回成功信息给客户端[root@localhost ~]#mkdir /data/mysql/   -p
#建立文件夹
[root@localhost ~]#chown mysql.mysql /data/ -R
#注意修改权限
[root@localhost ~]#mysql -uroot -p123123mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插件
mysql>UNINSTALL PLUGIN rpl_semi_sync_master ;#卸载
mysql>SET GLOBAL rpl_semi_sync_master_enabled=1; #临时修改变量
mysql>SET GLOBAL rpl_semi_sync_master_timeout = 3000;  #超时长1s,默认值为10s[root@localhost ~]#systemctl restart mysqld
进入数据库
SHOW GLOBAL VARIABLES LIKE '%semi%';查看半同步状态
grant replication slave on *.* to test@'192.168.91.%' identified by 'Admin@123';

（2）从服务器1

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#安装插件
[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server-id=12
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动

help change master to;
#查看帮助，复制修改
CHANGE MASTER TOMASTER_HOST='192.168.240.11',MASTER_USER='test',MASTER_PASSWORD='Admin@123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=592;start slave;
show global status like '%semi%';查看状态

（3）从服务器2

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#安装插件
[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server-id=14
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动

help change master to;
#查看帮助，复制修改
CHANGE MASTER TOMASTER_HOST='192.168.240.11',MASTER_USER='test',MASTER_PASSWORD='Admin@123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=592;start slave;
show global status like '%semi%';查看状态

（4）测试

当将从服务器2，关闭主从复制时

在主上创建cxk数据库时，主没有延迟，

当两台从服务器都关闭主从复制时

在主上创建cxk1数据库时，主有延迟3秒

当再次开启从服务器时

从服务器上数据同步

可以使用     show slave status\G;   #查看设置的状态

Seconds_Behind_Master: NULL    #目前数据差