Mysql主从复制案例

各种复制架构

主从复制原理

主从复制相关线程

• 主节点：

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

二进制日志功能

【安装好mysql再来查询】

这里讲一下二进制日志是干啥的，你只要开启了二进制日志的功能

sql_log_bin=ON：开启二进制日志记录的功能，你执行一个mysql语句他就会记录一下，他在mysql默认是开启的就不用管了

log-bin=路径/文件前缀：开启二进制日志功能,这个可以直接写在/etc/my.cnf里,一主一从的步骤里有介绍

一个是记录，一个是开启，如果你只有记录，没有开启是没有二进制日志的，所以二者缺一不可

先看看怎么查

[root@master ~]# mysql -uroot -p123456 -e "select @@log_bin;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
[root@master ~]# mysql -uroot -p123456 -e "select @@sql_log_bin;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
[root@master ~]#

如果你看到两个都是1或者ON那就是全部开启了

一主一从

master：192.168.48.128

slave：192.168.48.11

安装mysql

自行关闭firewalld，selinux

操作节点：【所有节点】

二进制安装（适合新机）

#你可以选择用我的脚本进行安装二进制的mysql
#二进制安装是基本所有linux都可以使用的，如果有哪个系统运行不了请留言
wget https://blog.qianyios.top/file/mysql_install.sh
bash mysql_install.sh
#密码我设置了123456（建议不要太简单）
#建议选清理一遍（输入3），再进行安装mysql8.0（输入2）

其他安装

其他方式自行安装，我就不解释了，只要确保同个版本差不多就行了8.0.x这些。

主节点配置

操作节点：【master】

修改/etc/my.cnf

#其他安装mysql的方法，你只需要确保有如下选项就行了
[mysqld]
sever-id=128
log-bin=/data/logbin/qylog

sever-id ：必须是整个集群里面唯一的，不能重复

log-bin:后续会在/data/logbin生成qylog.000001的二进制文件，你可以自定义路径和文件名不用加后缀

如果是用了我的脚本安装的mysql可以直接执行下面的语句，不是的话就看上面那个步骤手动修改

cat > /etc/my.cnf << "EOF"
[mysqld]
server-id=128
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
#开启二进制选项
log-bin=/data/logbin/qylog
#二进制日志记录的格式，mariadb5.5默认STATEMENT
#binlog_format=STATEMENT|ROW|MIXED
[client]
socket=/data/mysql/mysql.sock
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
EOF

由于前面不是开启了二进制文件的功能吗

我的路径是：/data/logbin/qylog，其中/data/logbin并不存在，你要手动创建，不然开启mysql会报错

mkdir -p /data/logbin
chown -R mysql:mysql /data/logbin
systemctl restart mysqld

此时二进制文件已经创建成功

[root@master ~]# ll /data/logbin/
total 8
-rw-r----- 1 mysql mysql 157 Jul 31 18:53 qylog.000001
-rw-r----- 1 mysql mysql  26 Jul 31 18:53 qylog.index

创建用于主从复制的账号

[root@master ~]# mysql -uroot -p123456 -e "show master status"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| qylog.000001 |      157 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
#记住这个qylog.000001和起始位置157，后面会用到

#创建repluser用户，密码是123456，允许从192.168.48.0来连接master的mysql
mysql -uroot -p123456 -e "create user repluser@'192.168.48.%' identified by '123456';"
mysql -uroot -p123456 -e "grant replication slave on *.* to repluser@'192.168.48.%';"

从节点配置

操作节点：【slave1】

修改/etc/my.cnf

#其他安装mysql的方法，你只需要确保有如下选项就行了
[mysqld]
server-id=11
log-bin=/data/logbin/qylog
read_only=ON #设置数据库只读，针对supper user无效
#启动中继日志
relay_log=/data/relaylog/relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=/data/relaylog/relay-log.index  #默认值hostname-relay-bin.index

sever-id ：必须是整个集群里面唯一的，不能重复

log-bin:后续会在/data/logbin生成qylog.000001的二进制文件，你可以自定义路径和文件名不用加后缀

如果是用了我的脚本安装的mysql可以直接执行下面的语句，不是的话就看上面那个步骤手动修改

cat > /etc/my.cnf << "EOF"
[mysqld]
server-id=11
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
#开启二进制选项
log-bin=/data/logbin/qylog
#二进制日志记录的格式，mariadb5.5默认STATEMENT
#binlog_format=STATEMENT|ROW|MIXED
read_only=ON #设置数据库只读，针对supper user无效
relay_log=/data/relaylog/relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=/data/relaylog/relay-log.index  #默认值hostname-relay-bin.index
[client]
socket=/data/mysql/mysql.sock
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
EOF

由于前面不是开启了二进制文件的功能，还有开启中继日志的功能吗

我的路径是：

log-bin=/data/logbin/qylog，其中/data/logbin并不存在

relay_log=/data/relaylog/relay-log，其中/data/relaylog并不存在

你要手动创建，不然开启mysql会报错

mkdir -p /data/logbin /data/relaylog
chown -R mysql:mysql /data/logbin /data/relaylog
systemctl restart mysqld

启动复制线程

操作节点：[slave]

使用有复制权限的用户账号连接至主服务器

mysql -u root -p123456
CHANGE MASTER TO 
MASTER_HOST='192.168.48.128', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='qylog.000001', 
MASTER_LOG_POS=157,
get_master_public_key=1,
MASTER_DELAY=10; 

CHANGE MASTER TO
MASTER_HOST=‘192.168.48.128’, master的ip
MASTER_USER=‘repluser’, master创建的主从复制的账号
MASTER_PASSWORD=‘123456’, master创建的主从复制的账号的密码
MASTER_LOG_FILE=‘qylog.000001’, master查看的二进制文件
MASTER_LOG_POS=157, master查看的二进制文件的起始位置
MASTER_DELAY=10; 延迟更新10s

启动复制线程

start slave;
show slave status\G;

确保有两个yes就行了

如果你想重置线程可以用以下命令

stop slave;
reset slave;

有一个重要的点，因为二进制日志是记录你的操作的嘛，我们在开启二进制日志之后，不是在主节点创建了一个repluser用户吗，那这个操作肯定也被记录，然后这不是主从复制了吗，这里肯定，也会同步，也会运行，二进制日志本身就是一个sql文件，普通cat是看不了，你得用这个命令

[root@master ~]# mysqlbinlog -uroot -p123456 /data/logbin/qylog.000001 -v

所有既然主从复制了，那在从节点，应该也运行了这个二进制文件，就说明从节点也有这个账号

select host,user from mysql.user;

测试

接下来在主节点导入测试数据

全选复制粘贴退出mysql运行

cat > hellodb.sql <<"EOF"

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `hellodb`;

DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
  `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `Class` varchar(100) DEFAULT NULL,
  `NumOfStu` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

LOCK TABLES `classes` WRITE;
INSERT INTO `classes` VALUES
(1,'Shaolin Pai',10),(2,'Emei Pai',7),
(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),
(6,'Lianshan Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15);
UNLOCK TABLES;

DROP TABLE IF EXISTS `coc`;
CREATE TABLE `coc` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ClassID` tinyint(3) unsigned NOT NULL,
  `CourseID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

LOCK TABLES `coc` WRITE;
INSERT INTO `coc` VALUES (1,1,2),(2,1,5),(3,2,2),(4,2,6),(5,3,1),(6,3,7),(7,4,5),(8,4,2),(9,5,1),(10,5,9),(11,6,3),(12,6,4),(13,7,4),(14,7,3);
UNLOCK TABLES;

DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
  `CourseID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Course` varchar(100) NOT NULL,
  PRIMARY KEY (`CourseID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

LOCK TABLES `courses` WRITE;
/*!40000 ALTER TABLE `courses` DISABLE KEYS */;
INSERT INTO `courses` VALUES (1,'Hamo Gong'),(2,'Kuihua Baodian'),(3,'Jinshe Jianfa'),(4,'Taiji Quan'),(5,'Daiyu Zanghua'),(6,'Weituo Zhang'),(7,'Dagou Bangfa');
/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
UNLOCK TABLES;

DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `StuID` int(10) unsigned NOT NULL,
  `CourseID` smallint(5) unsigned NOT NULL,
  `Score` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

LOCK TABLES `scores` WRITE;
INSERT INTO `scores` VALUES (1,1,2,77),(2,1,6,93),(3,2,2,47),(4,2,5,97),(5,3,2,88),(6,3,6,75),(7,4,5,71),(8,4,2,89),(9,5,1,39),(10,5,7,63),(11,6,1,96),(12,7,1,86),(13,7,7,83),(14,8,4,57),(15,8,3,93);
UNLOCK TABLES;

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') NOT NULL,
  `ClassID` tinyint(3) unsigned DEFAULT NULL,
  `TeacherID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

LOCK TABLES `students` WRITE;
INSERT INTO `students` VALUES (1,'Shi Zhongyu',22,'M',2,3),(2,'Shi Potian',22,'M',1,7),(3,'Xie Yanke',53,'M',2,16),(4,'Ding Dian',32,'M',4,4),(5,'Yu Yutong',26,'M',3,1),(6,'Shi Qing',46,'M',5,NULL),(7,'Xi Ren',19,'F',3,NULL),(8,'Lin Daiyu',17,'F',7,NULL),(9,'Ren Yingying',20,'F',6,NULL),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL);
UNLOCK TABLES;

DROP TABLE IF EXISTS `teachers`;
CREATE TABLE `teachers` (
  `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') DEFAULT NULL,
  PRIMARY KEY (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

LOCK TABLES `teachers` WRITE;
INSERT INTO `teachers` VALUES (1,'Song Jiang',45,'M'),(2,'Zhang Sanfeng',94,'M'),(3,'Miejue Shitai',77,'F'),(4,'Lin Chaoying',93,'F');
UNLOCK TABLES;


DROP TABLE IF EXISTS `toc`;
CREATE TABLE `toc` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CourseID` smallint(5) unsigned DEFAULT NULL,
  `TID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `toc` WRITE;
UNLOCK TABLES;

EOF

在主节点导入

#导入
[root@master ~]# mysql -uroot -p123456 < hellodb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.#这个警告没关系

[root@master ~]# mysql -uroot -p123456 hellodb -e "show tables;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
#成功导入

#在主节点插入一条数据
mysql -uroot -p123456
use hellodb;
insert into teachers (name,age,gender)values("XIAOHU",18,'M');
select * from teachers;
#成功插入数据
(root@localhost) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | XIAOHU        |  18 | M      |
+-----+---------------+-----+--------+

接下来看看从节点是否有成功实现主从复制

[root@slave ~]# mysql -uroot -p123456
use hellodb;
show tables;
select * from teachers;

#看吧，已经成功从主节点复制数据到从节点
(root@localhost) [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)
#新插入的数据也在
(root@localhost) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | XIAOHU        |  18 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

至此，一主一从的主从复制实验成功，你必须理解，主要是通过二进制日志来依靠传输的

案例

实战案例：将已有的MySQL8.0单机架构变成主从复制架构

这台单机，可能运行很久了，有很多数据了，现在要让他变成主从架构

前提：两者开启二进制日志功能

1.mysqldump备份所有数据库

2.创建传输用户并且授权

3.将备份scp复制到从节点

4.从节点开启read-only等参数

5.从节点设置主从复制连接

6.从节点进行还原备份

7.开启主从复制，start slave；

#已有的master节点,备份所有的数据库
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction \
--source-data=1 --flush-privileges --hex-blob --default-character-set=utf8 > all.sql

#创建传输用户并且授权
mysql -uroot -p123456 -e "create user repluser@'192.168.48.%' identified by '123456';"
mysql -uroot -p123456 -e "grant replication slave on *.* to repluser@'192.168.48.%';"

#将备份scp复制到从节点
scp -p all.sql 192.168.48.11:/root/

#从节点开启read-only等参数
[root@slave ~]# cat /etc/my.cnf
server-id=8
log-bin=/data/logbin/qylog
read_only=ON
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index

#从节点设置主从复制连接
#1.查看all.sql的二进制文件和起始位置
head -n 30 all.sql
CHANGE MASTER TO MASTER_LOG_FILE='qylog.000002', MASTER_LOG_POS=157;
#2.设置复制连接，填入前面的二进制文件和起始位置
mysql -u root -p123456
CHANGE MASTER TO 
MASTER_HOST='192.168.48.128', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='qylog.000002', 
MASTER_LOG_POS=157,
MASTER_DELAY=10; 

#从节点进行还原备份
#先停止记录二进制日志
mysql -u root -p123456
set sql_log_bin=0;
source all.sql
set sql_log_bin=1;

#开启主从复制
start slave;
show slave status\G;
#可能等待60秒才能主从双yes

#主节点插入新数据
mysql -uroot -p123456
use hellodb;
insert into teachers (name,age,gender)values("qianyios",18,'M');
select * from teachers;
#最后看看是否有数据库已经新插入的数据库是否同步成功
(root@localhost) [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

(root@localhost) [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
(root@localhost) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | XIAOHU        |  18 | M      |
|   6 | qianyios      |  18 | M      |
+-----+---------------+-----+--------+

一主多从

master：192.168.48.128

slave1：192.168.48.11

slave2：192.168.48.10

接下来在前面的一主一从复制架构，变成一主多从，一样，如果这个mysql的主节点是已经运行很久了，你就要全部备份，拉到从节点，注意新的从节点要安装和另外一个从节点一样的mysql版本（这里我就略过安装教程了），然后只需要在新从节点设置好/etc/my.cnf和主从连接信息，关闭二进制记录，还原all.sql文件，开启二进制记录，开启主从复制即可
这里就接着那个案例来

#在案例的时间点之后，又运行了一段时间，此时你必须重新备份全部数据库，拉到从节点
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction \
--source-data=1 --flush-privileges --hex-blob --default-character-set=utf8 > all.sql

scp -p all.sql 192.168.48.10:/root/

#新从节点创建传输用户并且授权
mysql -uroot -p123456 -e "create user repluser@'192.168.48.%' identified by '123456';"
mysql -uroot -p123456 -e "grant replication slave on *.* to repluser@'192.168.48.%';"

#vim进去all.sql
会有一段
CHANGE MASTER TO MASTER_LOG_FILE='qylog.000003', MASTER_LOG_POS=157;
#是不是很像连接信息，那你就把它变成下面那样，就其实它二进制文件和起始位置都有了，只需要把其他补齐就行了

CHANGE MASTER TO 
MASTER_HOST='192.168.48.128', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='qylog.000003', 
MASTER_LOG_POS=157; 

#然后保存退出，这和案例里的先设置主从复制连接效果是一样，我就直接在文件里设置也一样
#没关系的，这样进文件，省得我还要看一遍二进制文件是啥了，对不对，我直接进去设置


#接下来进行还原
set sql_log_bin=0;
source all.sql;
set sql_log_bin=1;

#开启slave主从复制
start slave;
show slave status\G;

#接下来你就自行测试，看看是否有数据库已经还原成功和新插入数据是否成功并确认是否同步

至此一主多从成功！

级联复制

master：192.168.48.128

slave1：192.168.48.11（充当级联slave中间节点）

slave2：192.168.48.10

这个实验，我就只用了一个slave2，slave3和slave2的操作一样的

为啥会有级联呢！

就好比前面的一主多从，他要进行多个复制任务给从节点，那主节点的压力是不是大一点，那么就有了级联，把复制任务分担给从节点

这里还有一个问题

首先我们都知道主从复制的原理就是，主节点通过二进制日志bin传输数据到从节点的io线程接收写到中继日志relay,那后面的slave2和slave3应该也是这样，只需要接收slave1的bin的数据就行了是不是

错！大错特错！！！！！！

slave1的bin的数据从哪里来，他自己本身就是生成自己自身的数据，他怎么可能有主节点的数据呢？是不是得从自身的relay来，所以后面会讲到一个参数，就是可以让relay的数据传输过来的时候自动写入到bin，那就后面再说

这里延续前面的一主多从

#首先我得把slave2的一些信息删除，脱离原来一主多从
#/etc/my.cnf的信息不用删，本身就是给从节点用的
#要删就要删除主从复制的连接信息，顺便重置二进制信息
stop slave;
reset slave all;
reset master;

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

[mysqld]
sever-id=11
log-bin=/data/logbin/qylog
read_only=ON
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index
log_slave_updates #级联复制中间节点的必选，,MySQL8.0此为默认值,可以不用人为添加,其它版本默认不开启

重启mysql

systemctl restart mysqld

接着在新的从节点（slave2）进行操作，假设他是新的，你要进行的是，配置my.cnf，创建主从复制账号和授权，还有因为他是新的，没有数据，你还要在从节点备份全部数据库到从节点

#新的从节点
[mysqld]
server-id=10
log-bin=/data/logbin/qylog
read_only=ON 
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index

#从节点导出全部数据库的备份（注意是从节点）
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction \
--source-data=1 --flush-privileges --hex-blob --default-character-set=utf8 > all.sql

#将备份scp复制到从节点
scp -p all.sql 192.168.48.10:/root/

#新的从节点创建传输用户并且授权
mysql -uroot -p123456 -e "create user repluser@'192.168.48.%' identified by '123456';"
mysql -uroot -p123456 -e "grant replication slave on *.* to repluser@'192.168.48.%';"

#新的从节点vim进入all.sql
#编辑那条语句，这时候这里的master就不是主节点而是指向中间节点了
CHANGE MASTER TO 
MASTER_HOST='192.168.48.11', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='qylog.000003', 
MASTER_LOG_POS=157; 
#然后保存退出


#新的从节点接下来进行还原
set sql_log_bin=0;
source all.sql;
set sql_log_bin=1;

#新的从节点开启slave主从复制
start slave;
show slave status\G;

#接着我在主节点插入数据
use hellodb;
insert into teachers (name,age,gender)values("qianyios2",18,'M');
#之后在中间从节点看到了新的数据
#在最后的从节点也看到了数据

主主复制

master：192.168.48.128

slave1：192.168.48.11（让他变成master主节点）

slave2：192.168.48.10

容易产生的问题：数据不一致；因此慎用

配置步骤

(1) 各节点使用一个惟一server_id

(2) 都启动binary log和relay log

(3) 创建拥有复制权限的用户账号

(4) 均把对方指定为主节点，并启动复制线程

keepalived提供vip地址，实现数据库集群高可用，这个点后面再说

双主的意思就是，这两个节点互为主从，实现双向复制，我是主的时候你是从，你是主的时候我是从

#将slave1的/etc/my.cnf里的read only相关参数要删掉，既然都是主节点，那么肯定要可读可写，保留如下
[mysqld]
server-id=11
log-bin=/data/logbin/qylog
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index

#主节点添加relay_log相关参数
[mysqld]
server-id=128
log-bin=/data/logbin/qylog
log_slave_updates
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index
#主节点还要创建/data/relaylog
mkdir -p /data/relaylog
chown -R mysql:mysql /data/relaylog
systemctl restart mysqld

#由于slave1的主从复制连接已经指向了master节点，我就不改了，（这是之前的）
CHANGE MASTER TO 
MASTER_HOST='192.168.48.128', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='qylog.000001', 
MASTER_LOG_POS=157,
MASTER_DELAY=10; 

#现在接着配置主节点的主从复制连接指向第二个主节点，slave1
#你首先要在第二个主节点(slave1)查看二进制文件的位置
(root@localhost) [hellodb]> show master logs;
+--------------+-----------+-----------+
| Log_name     | File_size | Encrypted |
+--------------+-----------+-----------+
| qylog.000001 |      1405 | No        |
| qylog.000002 |       200 | No        |
| qylog.000003 |       180 | No        |
| qylog.000004 |       488 | No        |
| qylog.000005 |       180 | No        |
| qylog.000006 |       465 | No        |
+--------------+-----------+-----------+
#记住qylog.000006 | 465 （看最新的就行了）

#在第一个主节点设置复制连接，指向第二个主节点
CHANGE MASTER TO 
MASTER_HOST='192.168.48.11', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='qylog.000006', 
MASTER_LOG_POS=465,
MASTER_DELAY=10;
start slave;
show slave status\G;
#这时候第一个主节点显示的是双yes

测试两个节点各自插入新数据，看看对方有没有实现双向复制

#第一个主节点
use hellodb;
insert into teachers (name,age,gender)values("qi1",18,'M');
#第二个节点查询
(root@localhost) [hellodb]>  select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | XIAOHU        |  18 | M      |
|   6 | qianyios      |  18 | M      |
|   7 | qianyios1     |  18 | M      |
|   8 | qianyios2     |  18 | M      |
|   9 | qianyios3     |  18 | M      |
|  10 | qi1           |  18 | M      |
+-----+---------------+-----+--------+

#第二个主节点
use hellodb;
insert into teachers (name,age,gender)values("qi2",18,'M');

#好奇怪，我这里就出问题了，第二个节点复制不到主节点不知道什么问题
#后来才知道是设置了时间延迟
#但起始也不是这个原因，你要关闭延迟也没问题，主要原因（可以千万最后一个章节bug集锦中查看）
STOP SLAVE SQL_THREAD;
CHANGE MASTER TO MASTER_DELAY = 0;
START SLAVE SQL_THREAD;
SHOW SLAVE STATUS\G;
#两个节点都执行一下
#查看第一个节点是否有数据
(root@localhost) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   9 | qi1           |  18 | M      |
|  10 | qi2           |  18 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

如果你两个节点都同时插入数据，会出现一个主键的冲突
比如说教师id
你第一个节点这边添加了一个新数据，id自动分配为5
但是又有10秒的延迟，都还没同步过去
你在第二个节点又添加一个新数据，Id自动分配为5，
那这样就会报错，所以两个数据库把插入的数据记得删掉

所以要这样配置

添加auto_increment_offset和auto_increment_increment

#slave1
[mysqld]
server-id=11
log-bin=/data/logbin/qylog
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index
auto_increment_offset=2     #开始点
auto_increment_increment=2   #增长幅度 

#主节点
[mysqld]
server-id=128
log-bin=/data/logbin/qylog
log_slave_updates
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index
auto_increment_offset=1     #开始点
auto_increment_increment=2   #增长幅度

两个节点重启服务

systemctl restart mysqld

再次两个节点同时插入数据

#第一个节点
use hellodb;
insert into teachers (name,age,gender)values("qi111",18,'M');
select * from teachers;
......，#id自动增长为19
|  17 | qi13          |  18 | M      |
|  19 | qi111         |  18 | M      |

#第二个节点
use hellodb;
insert into teachers (name,age,gender)values("qi112",18,'M');
select * from teachers;
......，#id自动增长为17
|  17 | qi13          |  18 | M      |
|  18 | qi112         |  18 | M      |

#就这样就不会导致主键的冲突

#最后等待10秒的延迟，数据就会一致，两个节点都同步成功了
(root@localhost) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
......
|  17 | qi13          |  18 | M      |
|  18 | qi112         |  18 | M      |
|  19 | qi111         |  18 | M      |
+-----+---------------+-----+--------+
13 rows in set (0.00 sec)

看前面的架构图，两主一从，其实现在的状况是，数据从master写入到slave再到slave2的，slave2是没有连接master，所以只能后期写脚本，监测keepalived的vip变化，一旦变化，就去修改那个主从复制连接中的master ip去变化。

半同步复制

官方文档

https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html
https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html
https://mariadb.com/kb/en/library/semisynchronous-replication/

经过前两个图的理解，现在就开始实操

#接着上面一个主主的案例下来，如果不是，你只需要保留/etc/my.cnf有如下选项即可
#master
[mysqld]
server-id=128
log-bin=/data/logbin/qylog
rpl_semi_sync_master_enabled=ON   #添加此行,需要先安装semisync_master.so插件后,再重启配置文件,否则无法启动
rpl_semi_sync_master_timeout=10000  #设置10s内无法同步，也将返回成功信息给客户端

#slave1
server-id=11
log-bin=/data/logbin/qylog
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动

#slave1
server-id=10
log-bin=/data/logbin/qylog
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动

#注意先别那么快重启mysql！！要安装半同步复制插件
#master(永久安装)，如果想要临时的直接设置全局变量即可
mysql -uroot -p123456 -e "INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';"
systemctl restart mysql


#slave1
mysql -uroot -p123456 -e "INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';"
systemctl restart mysql

#slave2
mysql -uroot -p123456 -e "INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';"
systemctl restart mysql

#三个节点的复制关系都要删掉
mysql -uroot -p123456
stop slave;
reset slave all;
#三个节点重置二进制日志
reset master;

master导出全部备份给slave1和slave2，创建复制账号，关闭二进制日志记录
还原数据库，开启二进制日志记录，自行验证是否同步，这里就略过了
（注意是一主二从）相当于重新开始了

半同步复制的一些参数

#master节点

(root@localhost) [hellodb]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled             | ON         |
| rpl_semi_sync_master_timeout             | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+

• rpl_semi_sync_master_enabled：是否启用半同步复制功能。ON 表示启用，OFF 表示禁用。
• rpl_semi_sync_master_timeout：主库等待从库响应的超时时间（单位：毫秒）。超时后主库会切换为异步复制。
• rpl_semi_sync_master_trace_level：日志记录的详细程度，数值越大记录越详细。
• rpl_semi_sync_master_wait_for_slave_count：主库提交事务时需要等待的从库数量。
• rpl_semi_sync_master_wait_no_slave：当没有从库连接时，主库是否继续使用半同步复制。ON 表示继续等待，OFF 表示切换为异步复制。
• rpl_semi_sync_master_wait_point：半同步复制的等待点。AFTER_SYNC 表示在同步完成后等待，AFTER_FLUSH 表示在数据刷新到磁盘后等待。

#master
(root@localhost) [hellodb]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     | #成功的有两个
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 674   |
| Rpl_semi_sync_master_tx_wait_time          | 674   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+

Rpl_semi_sync_master_clients：当前连接到主库并支持半同步复制的从库数量。

Rpl_semi_sync_master_net_avg_wait_time：半同步复制中网络平均等待时间（单位：微秒）。

Rpl_semi_sync_master_net_wait_time：半同步复制中网络总等待时间（单位：微秒）。

Rpl_semi_sync_master_net_waits：半同步复制中网络等待的总次数。

Rpl_semi_sync_master_no_times：半同步复制未成功（切换为异步复制）的次数。

Rpl_semi_sync_master_no_tx：半同步复制未成功时的事务数量。

Rpl_semi_sync_master_status：当前半同步复制的状态，ON 表示启用，OFF 表示禁用。

Rpl_semi_sync_master_timefunc_failures：半同步复制中时间函数失败的次数。

Rpl_semi_sync_master_tx_avg_wait_time：事务平均等待时间（单位：微秒）。

Rpl_semi_sync_master_tx_wait_time：事务总等待时间（单位：微秒）。

Rpl_semi_sync_master_tx_waits：事务等待的总次数。

接下来进行测试

1.测试都启动的情况下，一主二从，是否正常的主从同步

#主节点
use hellodb;
insert into teachers (name,age,gender)values("oooo1",18,'M');
use hellodb;select * from teachers;
#从节点
use hellodb;select * from teachers;

#经测试同步成功
(root@localhost) [hellodb]> use hellodb;select * from teachers;
Database changed
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
......
|  21 | qy2           |  56 | F      |
|  22 | oooo1         |  18 | M      |
+-----+---------------+-----+--------+
15 rows in set (0.00 sec)

2.接下来测试，slave关闭mysql模拟宕机的操作，再次进行插入数据

#slave2
systemctl stop mysqld

#主节点
use hellodb;
insert into teachers (name,age,gender)values("oooo1",18,'M');
use hellodb;select * from teachers;
#slave1
use hellodb;select * from teachers;
#slave1成功同步数据
(root@localhost) [hellodb]> use hellodb;select * from teachers;
Database changed
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   9 | qi1           |  18 | M      |
|  10 | qi2           |  18 | M      |
|  11 | qi3           |  18 | M      |
|  12 | qi4           |  18 | M      |
|  13 | qi19          |  18 | M      |
|  14 | qi20          |  18 | M      |
|  17 | qi13          |  18 | M      |
|  18 | qi112         |  18 | M      |
|  19 | qi111         |  18 | M      |
|  21 | qy2           |  56 | F      |
|  22 | oooo1         |  18 | M      |
|  23 | oooo1         |  18 | M      |
+-----+---------------+-----+--------+
16 rows in set (0.00 sec)

3.接下来，让最后一个slave1也宕机看看是什么情况

#slave1
systemctl stop mysqld

#主节点
use hellodb;
insert into teachers (name,age,gender)values("oooo2",18,'M');
use hellodb;select * from teachers;

#情况如下，默认等待10s
(root@localhost) [hellodb]> insert into teachers (name,age,gender)values("oooo2",18,'M');
Query OK, 1 row affected (10.01 sec)

#此时已经没有slave节点连接数了
(root@localhost) [hellodb]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+----------+
| Variable_name                              | Value    |
+--------------------------------------------+----------+
| Rpl_semi_sync_master_clients               | 0        |
#在插入一次数据，这里就没有等待了
(root@localhost) [hellodb]> insert into teachers (name,age,gender)values("oooo3",18,'M');
Query OK, 1 row affected (0.05 sec)

• 第一次插入操作等待 10 秒，是因为主节点在半同步复制模式下等待从节点确认，但超时了。
• 第二次插入操作没有等待，是因为主节点已经检测到没有从节点连接，直接以异步复制模式执行。

4.恢复所有的slave节点，看看有无同步

#slave1和slave2
systemctl start mysqld

#主节点的数据
(root@localhost) [hellodb]> use hellodb;select * from teachers;
Database changed
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
......
|  22 | oooo1         |  18 | M      |
|  23 | oooo1         |  18 | M      |
|  24 | oooo1         |  18 | M      |
|  32 | oooo2         |  18 | M      |
|  33 | oooo3         |  18 | M      |
+-----+---------------+-----+--------+
19 rows in set (0.00 sec)

#slave1和slave2的数据成功同步
(root@localhost) [hellodb]> use hellodb;select * from teachers;
Database changed
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
......
|  22 | oooo1         |  18 | M      |
|  23 | oooo1         |  18 | M      |
|  24 | oooo1         |  18 | M      |
|  32 | oooo2         |  18 | M      |
|  33 | oooo3         |  18 | M      |
+-----+---------------+-----+--------+
19 rows in set (0.00 sec)

至此半同步复制成功

复制过滤器

让从节点仅复制指定的数据库，或指定数据库的指定表

复制过滤器两种实现方式：

(1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件

缺点：基于二进制还原将无法实现；不建议使用

优点: 只需要在主节点配置一次即可

只需要在主服务器配置，如果我不想同步论坛，只想同步其他的，只需要禁用论坛的二进制日志，就行了，但是这就有一个问题，你论坛的二进制日志都禁用了，那你这个论坛的这个数据库突然出现问题了，数据怎么办？这就是缺点

这个禁用二进制，不用禁用log_bin选项，看下面的来配置黑白名单就行

注意：此项和 binlog_format相关

参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db

#只同步db1和db2，也就是只有这两个会开启二进制日志，其他数据库不会
#（白名单和黑名单必须二选一)
#默认就是白名单)
vim /etc/my.cnf
#白名单
binlog-do-db=db1 #数据库白名单列表，不支持同时指定多个值，如果想实现多个数据库需多行实现
binlog-do-db=db2 
#也可以设置黑名单
binlog-ignore-db=db3 #数据库黑名单列表

注意：

This option will not work with cross-database updates with statement-based  logging. See the Statement-Based Logging section for more information.
This option can not be set dynamically.
When setting it on the command-line or in a server option group in an option  file, the option does not accept a comma-separated list. If you would like to  specify multiple filters, then you need to specify the option multiple times.
#此选项不适用于使用基于语句的日志记录的跨数据库更新。有关更多信息，请参阅基于语句的日志记录部分。
#此选项不能动态设置。
#在命令行或选项文件中的服务器选项组中设置该选项时，该选项不接受逗号分隔的列表。如果希望指定多个过滤器，则需要多次指定该选项。

来测试吧

#master
#配置文件只需要保留如下选项
vim /etc/my.cnf
[mysqld]
server-id=128
log-bin=/data/logbin/qylog
binlog-do-db=db1
binlog-do-db=db2 
binlog-ignore-db=db3

#slave1和slave2记得删除半同步的选项，最后所有服务器重启mysql
systemctl restart mysqld

#主节点创建测试的数据库
mysql -u root -p123456 -e"create database db1;"
mysql -u root -p123456 -e"create database db2;"
mysql -u root -p123456 -e"create database db3;"
#db1和db2插入数据
mysql -u root -p123456 -e"USE db1;CREATE TABLE test ( id INT);INSERT INTO test (id) VALUES (1);SELECT * FROM test;"
mysql -u root -p123456 -e"USE db2;CREATE TABLE test ( id INT);INSERT INTO test (id) VALUES (1);SELECT * FROM test;"
#查看数据库
mysql -u root -p123456 -e"show databases;"
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| db3                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

#这时候其他从节点可以查看是否有这个db1和db2数据库
mysql -u root -p123456 -e"show databases;"
mysql -u root -p123456 -e"USE db1;SELECT * FROM test;"
mysql -u root -p123456 -e"USE db2;SELECT * FROM test;"

#同步成功，slave1和slave2都一样，都成功同步了白名单的数据库，黑名单的数据库就没同步
[root@slave1 ~]# mysql -u root -p123456 -e"show databases;"
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@slave1 ~]# mysql -u root -p123456 -e"USE db1;SELECT * FROM test;"
+------+
| id   |
+------+
|    1 |
+------+
[root@slave1 ~]# mysql -u root -p123456 -e"USE db2;SELECT * FROM test;"
+------+
| id   |
+------+
|    1 |
+------+
[root@slave ~]#

(2) 从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地

缺点：会造成网络及磁盘IO浪费,在所有从节点都要配置

优点: 不影响二进制备份还原

从服务器上的复制过滤器相关变量

白名单和黑名单二选一，复制库和复制表，以及通配符类型三选一（白名单和黑名单二选一），不然会出现数据不一致的情况

replicate_do_db=db1 #指定复制库的白名单，选项不支持多值,只能分别写多行实现
replicate_do_db=db2 #指定复制库的白名单，选项不支持多值,只能分别写多行实现
replicate_ignore_db=db4  #指定复制库黑名单
replicate_do_table=db1.test #指定复制数据库中的表的白名单
replicate_ignore_table=db2.test #指定复制数据库中表的黑名单
replicate_wild_do_table= foo%.bar% #支持通配符 只复制数据库名以 foo 开头，表名以 bar 开头的表
replicate_wild_ignore_table=db1.tbl_% #忽略 db1 数据库中所有以 tbl_ 开头的表的复制

#删除master的第一步的配置，保留如下
[mysqld]
server-id=128
log-bin=/data/logbin/qylog

#然后重启
systemctl restart mysqld

在所有的从节点，注意是所有的从节点

[mysqld]
replicate_do_db=db2 
replicate_do_db=db1

#这次我不加黑名单，我看会不会同步db3
#然后重启
systemctl restart mysqld

master创建测试数据

mysql -u root -p123456
#db1创建tes1表，以及在原来的test表插入新数据
USE db1;CREATE TABLE test1 ( id INT);INSERT INTO test1 (id) VALUES (1);SELECT * FROM test1;
USE db1;INSERT INTO test (id) VALUES (2);SELECT * FROM test;

#在db2原来的test插入新数据，且创建新表
USE db2;CREATE TABLE test1 ( id INT);INSERT INTO test1 (id) VALUES (1);SELECT * FROM test1;
USE db2;INSERT INTO test (id) VALUES (2);SELECT * FROM test;
#在db3创建新表
USE db3;CREATE TABLE test ( id INT);INSERT INTO test (id) VALUES (1);SELECT * FROM test;
USE db3;INSERT INTO test (id) VALUES (2);SELECT * FROM test;

slave查看从节点

USE db1;SELECT * FROM test1;
USE db1;SELECT * FROM test;
USE db2;SELECT * FROM test1;
USE db2;SELECT * FROM test;
use db3;


#都是可以成功同步数据，只有db3是没有同步过来的
(root@localhost) [(none)]> USE db1;SELECT * FROM test1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

(root@localhost) [db1]> USE db1;SELECT * FROM test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

(root@localhost) [db1]> USE db2;SELECT * FROM test1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

(root@localhost) [db2]> USE db2;SELECT * FROM test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
(root@localhost) [db2]> use db3;
ERROR 1049 (42000): Unknown database 'db3'

GTID复制

全局复制Id

背景

💥 传统复制机制的问题（基于 binlog 位置点）

1. 多个客户端（如图中）分别发起事务 1001、1002、1003，主库依次执行，写入 binlog。
2. 主库通过只有一个的 dump 线程把 binlog 发送给从库。
3. 从库通过 IO 线程读取 binlog，通过 SQL 线程 一个一个串行执行事务。

❗问题来了：

• 从库自身也有客户端访问，可能也会产生事务，编号也可能是 1001、1002、1003（编号只是应用内部的业务编号，不是数据库唯一标识）。
• 主库传过来的 binlog，只记录了操作内容、表名、数据改动，但没有全局唯一事务 ID。
• 所以从库根本分不清：这个 1001 是主库的？还是我自己产生的？
• 为了避免数据冲突、保证顺序一致，从库只能串行地照着 binlog 执行事务，而不能乱序执行或并发执行。

正是因为分不清，怕双方的事务乱序执行，所以才会选择串行执行，确保安全

而且如果一个事务特别特别慢，他会拖累整个串行执行的进度，效率慢

GTID（全局事务标识符） 从 MySQL 5.6 开始引入，建议使用 5.7 及以上版本，更稳定可靠。

在 MySQL 5.7 中，即使未开启 GTID，也会生成匿名 GTID，具备一定追踪能力。

GTID 复制的优势包括：

• 支持 master_auto_position=1，无需指定 binlog 文件和 POS 点，主从复制更简化。

就是你那个主从复制连接里，不用记录了是哪个二进制文件和起点了，有了这个参数就可以自动识别，前提是主从的数据库要一致

• 实现了事务级的幂等性，重复执行事务不会出错。
• 配合 5.6 的库级并行复制和 5.7 的事务级并行复制（逻辑时钟机制），显著提升复制并发性能，降低延迟。
• 可安全支持多个 dump 线程 + 多 SQL 线程 并发复制。

GTID 优点:

• 保证事务全局统一
• 截取日志更加方便。跨多文件，判断起点终点更加方便
• 判断主从工作状态更加方便
• 传输日志，可以并发传输。SQL回放可以更高并发
• 主从复制构建更加方便

GTID = server_uuid:transaction_id，在一组复制中，全局唯一

server_uuid 来源于 /var/lib/mysql/auto.cnf

GTID服务器相关选项

gtid_mode=ON #gtid模式
enforce_gtid_consistency=ON #保证GTID安全的参数

测试案例：前提是主从的数据库要一致，并且创建好复制传输的账号

#如果主服务器和从服务器数据不一致,需要先将主库数据备份还原至从库,再执行下面操作

mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction \
--source-data=1 --flush-privileges --hex-blob --default-character-set=utf8 > all.sql
#自行复制的从节点，还原的步骤我就不说了

1.主节点

vim /etc/my.cnf
#保留如下
[mysqld]
server-id=128
log-bin=/data/logbin/qylog
gtid_mode=ON
enforce_gtid_consistency=ON

#然后重启mysql
systemctl restart mysqld

2.从节点（slave1和slave2都一样）

vim /etc/my.cnf
server-id=（从节点的唯一id）

log-bin=/data/logbin/qylog
relay_log=/data/relaylog/relay-log
relay_log_index=/data/relaylog/relay-log.index
gtid_mode=ON
enforce_gtid_consistency=ON

#然后重启mysql
systemctl restart mysqld

3.从节点重置前面实验的slave复制连接

#slave1和slave2都执行
stop slave;
reset slave all;
CHANGE MASTER TO 
MASTER_HOST='192.168.48.128', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
get_master_public_key=1,
MASTER_AUTO_POSITION=1; #使用GTID
start slave;
show slave status\G;

#然后就是双yes

4.查看是否成功

主节点执行一个操作，我这里就执行创建一个db4数据库
(root@localhost) [(none)]> create database db5;
Query OK, 1 row affected (0.05 sec)

(root@localhost) [(none)]> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: qylog.000004
         Position: 339
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 363255e7-6ebc-11f0-80de-000c293bf782:1#这里就可以看到gtid了
1 row in set (0.00 sec)

#然后从节点
(root@localhost) [(none)]> show slave status\G;
......
           Retrieved_Gtid_Set: 363255e7-6ebc-11f0-80de-000c293bf782:1
            Executed_Gtid_Set: 363255e7-6ebc-11f0-80de-000c293bf782:1  #是不是和前面主节点一样
......

至此gtid成功

主从复制bug集锦

1.主从复制在从节点你可能会看见

show slave status\G;

Last_IO_Error: error connecting to master 'repluser@192.168.48.11:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

原因：MySQL 8 默认使用 caching_sha2_password 作为认证插件，这种认证方式需要安全连接（SSL/TLS）。需要生成证书，如果不想生成证书，你自己现在从节点进行一次登入

如果你是级联最后的slave，主节点的地址要指向中间节点

在这种情况下，服务器将RSA公钥发送给客户端，后者使用它来加密密码并将结果返回给服务器。插件使用服务器端的RSA私钥解密密码，并根据密码是否正确来接受或拒绝连接。

mysql -urepluser -p123456 -h192.168.48.128 --get-server-public-key

然后重启slave复制线程

stop slave;start slave;show slave status\G;

以上只是临时的解决方案，还有一个办法就是

加这个参数get_master_public_key=1

如果这个不行就换get_source_public_key=1

CHANGE MASTER TO 
MASTER_HOST='192.168.48.128', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='qylog.000001', 
MASTER_LOG_POS=157,
get_master_public_key=1,
MASTER_DELAY=10; 

2.在主主复制的时候，我知道不是设置了延迟复制为10秒吗，就是那个主从复制的连接最后一项

但是情况是：

第一个主节点插入数据成功复制到从节点

但是在第二个主节点，插入数据之后，就出现l下面的情况

SQL_Remaining_Delay: 3995

Slave_SQL_Running_State: Waiting until SOURCE_DELAY seconds after source executed event Master_Retry_Count: 86400

问了ai

你设置了延迟复制 MASTER_DELAY = 10；
从库显示 SQL_Remaining_Delay: 3995，说明它在“等事件执行时间到了再处理”；
这个是正常现象，不是错误；
延迟时间这么长，可能是主节点的系统时间比从节点慢；

结果你知道吗，真的服了，两个主机的时间不一致

[root@slave ~]# date
Fri Aug  1 03:06:40 CST 2025
[root@master ~]# date
Fri Aug  1 02:00:20 AM CST 2025
#但是是我的从节点时间有问题，我更新了一下时间

复制的监控和维护

清理日志

PURGE { BINARY | MASTER } LOGS  { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER TO # #mysql 不支持
RESET SLAVE [ALL]

复制监控

SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST

从服务器是否落后于主服务

Seconds_Behind_Master：0

如何确定主从节点数据是否一致

percona-toolkit

数据不一致如何修复

删除从数据库，重新复制

复制的问题和解决方案

数据损坏或丢失

• Master：MHA + semisync replication
• Slave： 重新复制

不惟一的 server id

重新复制

复制延迟

• 升级到MySQL5.7以上版本(5.7之前的版本，没有开GTID之前，主库可以并发事务，但是dump传输时是串行)利用GTID(MySQL5.6需要手动开启,MySQL5.7以上默认开启)支持并发传输binlog及并行多个SQL线程
• 减少大事务,将大事务拆分成小事务
• 减少锁
• sync_binlog=1 加快binlog更新时间,从而加快日志复制
• 需要额外的监控工具的辅助
• 一从多主：Mariadb10 版后支持
• 多线程复制：对多个数据库复制

MySQL 主从数据不一致

造成主从不一致的原因

• 主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。
• 主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
• 从节点未设置只读，误操作写入数据
• 主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
• 主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
• 主从sql_mode 不一致
• MySQL自身bug导致

主从不一致修复方法

• 将从库重新实现

虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。

• 使用percona-toolkit工具辅助

PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用

关于使用方法，可以参考下面链接：https://www.cnblogs.com/feiren/p/7777218.html

• 手动重建不一致的表

在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的

范例：A,B,C这三张表主从数据不一致

1、从库停止Slave复制
mysql>stop slave;
2、在主库上dump这三张表，并记录下同步的binlog和POS点
mysqldump -uroot -pmagedu -q --single-transaction --master-data=2 testdb A B C >/backup/A_B_C.sql
3、查看A_B_C.sql文件，找出记录的binlog和POS点
head A_B_C.sql
例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666;
#以下指令是为了保障其他表的数据不丢失，一直同步直到那个点结束，A,B,C表的数据在之前的备份已经生成了一份快照，只需要导入进入，然后开启同步即可
4、把A_B_C.sql拷贝到Slave机器上，并做指向新位置
mysql>start slave until MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666;
5、在Slave机器上导入A_B_C.sql
mysql -uroot -pmagedu testdb 
mysql>set sql_log_bin=0;
mysql>source /backup/A_B_C.sql
mysql>set sql_log_bin=1;
6、导入完毕后，从库开启同步即可。
mysql>start slave;

如何避免主从不一致

• 主库binlog采用ROW格式
• 主从实例数据库版本保持一致
• 主库做好账号权限把控，不可以执行set sql_log_bin=0
• 从库开启只读，不允许人为写入
• 定期进行主从一致性检验

特别声明

千屹博客旗下的所有文章，是通过本人课堂学习和课外自学所精心整理的知识巨著
难免会有出错的地方
如果细心的你发现了小失误，可以在下方评论区告诉我，或者私信我！
非常感谢大家的热烈支持！