MySQL复制
2022/9/10 2:23:30
本文主要是介绍MySQL复制，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
环境条件
master：192.168.247.20 rocky8.6 mysql8.0.26 slave: 192.168.247.21 rocky8.6 mysql8.0.26 基本环境准备 hostnamectl set-hostname mysql-master-01 hostnamectl set-hostname mysql-slave-01 主从安装mysql-server服务 yum -y install mysql-server systemctl enable --now mysqld
一、配置主从复制
主节点启动二进制日志
cat /etc/my.cnf
[mysqld]
log-bin=/data/mysql/mysql-bin
为当前节点设置唯一id
[root@mysql-master-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] log-bin=/data/mysql/mysql-bin server-id=20 #建议设置本地IP地址最后一个位 [root@mysql-master-01 ~]# [root@mysql-slave-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] log-bin=/data/mysql/mysql-bin server-id=21 [root@mysql-slave-01 ~]#
查看主节点二进制日志起始位置
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 156 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0,00 sec) mysql>
创建有复制权限的用户账号
mysql> create user repluser@'192.168.247.%' identified by '123456'; Query OK, 0 rows affected (0,01 sec) mysql> grant replication slave on *.* to repluser@'192.168.247.%'; Query OK, 0 rows affected (0,00 sec) mysql> flush privileges; Query OK, 0 rows affected (0,00 sec) mysql>
从节点开启中继日志
cat /etc/my.cnf
[mysqld]
log-bin=/data/mysql/mysql-bin
server-id=21
read_only=ON
relay_log=relay-log
relay_log_index=relay-log.index
systemctl restart mysqld
使用有复制权限的用户账号连接至主服务器，并启动复制线程
CHANGE MASTER TOMASTER_HOST='192.168.247.20',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=156,
MASTER_CONNECT_RETRY=10;
start slave;
[root@mysql-slave-01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> CHANGE MASTER TO MASTER_HOST='192.168.247.20',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=156;
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.247.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 156
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 #复制延迟时间
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
Master_UUID: bd524c88-2ec6-11ed-955f-000c2969119b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
mysql>
测试数据同步
在主节点导入测试数据库 mysql < hellodb_innodb.sql mysql hellodb < testlog.sql 生成十万条数据 [root@mysql-master-01 ~]# mysql mysql> use hellodb mysql> call sp_testlog;
hellodb.sql
-- MySQL dump 10.13 Distrib 5.5.33, for Linux (x86_64)
--
-- Host: localhost Database: hellodb
-- ------------------------------------------------------
-- Server version 5.5.33-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `hellodb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `hellodb`;
--
-- Table structure for table `classes`
--
DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `classes`
--
LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
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);
/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `coc`
--
DROP TABLE IF EXISTS `coc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `coc`
--
LOCK TABLES `coc` WRITE;
/*!40000 ALTER TABLE `coc` DISABLE KEYS */;
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);
/*!40000 ALTER TABLE `coc` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `courses`
--
DROP TABLE IF EXISTS `courses`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `courses`
--
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;
--
-- Table structure for table `scores`
--
DROP TABLE IF EXISTS `scores`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `scores`
--
LOCK TABLES `scores` WRITE;
/*!40000 ALTER TABLE `scores` DISABLE KEYS */;
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);
/*!40000 ALTER TABLE `scores` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `students`
--
DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `students`
--
LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
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);
/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `teachers`
--
DROP TABLE IF EXISTS `teachers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `teachers`
--
LOCK TABLES `teachers` WRITE;
/*!40000 ALTER TABLE `teachers` DISABLE KEYS */;
INSERT INTO `teachers` VALUES (1,'Song Jiang',45,'M'),(2,'Zhang Sanfeng',94,'M'),(3,'Miejue Shitai',77,'F'),(4,'Lin Chaoying',93,'F');
/*!40000 ALTER TABLE `teachers` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `toc`
--
DROP TABLE IF EXISTS `toc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `toc`
--
LOCK TABLES `toc` WRITE;
/*!40000 ALTER TABLE `toc` DISABLE KEYS */;
/*!40000 ALTER TABLE `toc` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2013-09-03 2:51:27
View Code
cat testlog.sql
create table testlog (id int auto_increment primary key,name char(10),salary int default 20);
delimiter $$
create procedure sp_testlog()
begin
declare i int;
set i = 1;
while i <= 100000
do insert into testlog(name,salary) values (concat('wang',FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000));
set i = i +1;
end while;
end$$
delimiter ;
主节点查看数据导入情况，并模拟插入10万条数据
[root@mysql-master-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0,00 sec) mysql> mysql> use hellodb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | testlog | | toc | +-------------------+ 8 rows in set (0,00 sec) mysql> call sp_testlog; #插入10万条数据 Query OK, 1 row affected (2 min 0,22 sec) mysql> select count(*) from testlog; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0,01 sec) mysql>
从节点查看数据同步情况
[root@mysql-slave-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use hellodb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select count(*) from testlog; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.02 sec) mysql>
二、当主服务器运行一段时间后，新增从节点服务器
操作步骤：
- 通过备份恢复数据至从服务器
- 复制起始位置为备份时，二进制日志文件及其POS
新增服务器安装数据库
[root@mysql-slave-02 ~]# yum install mysql-server -y
配置丛节点my.cnf
cat /etc/my.cnf
[mysqld]
server-id=22
read-only
systemctl enable --now mysqld
在主服务器完全备份
[root@mysql-master-01 ~]# mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup.sql [root@mysql-master-01 ~]# ll -h /backup total 3,8M -rw-r--r-- 1 root root 3,8M Sep 8 09:18 fullbackup_2022-09-08_09:18:28.sql [root@mysql-master-01 ~]# scp /backup/fullbackup_2022-09-08_09\:18\:28.sql root@192.168.247.22:/data The authenticity of host '192.168.247.22 (192.168.247.22)' can't be established. ECDSA key fingerprint is SHA256:LFBeGPKGTITMRj1L7ud6ngPr5gQUcfuYxx6VC3N5yi8. Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added '192.168.247.22' (ECDSA) to the list of known hosts. root@192.168.247.22's password: fullbackup_2022-09-08_09:18:28.sql 100% 3812KB 111.7MB/s 00:00 [root@mysql-master-01 ~]#
优化主和从节点服务器的性能
set global innodb_flush_log_at_trx_commit=2
set global sync_binlog=0;
show variables like 'sync_binlog';
[root@mysql-master-01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set global innodb_flush_log_at_trx_commit=2
-> ;
Query OK, 0 rows affected (0,00 sec)
mysql> set global sync_binlog=0;
Query OK, 0 rows affected (0,00 sec)
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 |
+---------------+-------+
1 row in set (0,00 sec)
mysql>
[root@mysql-slave-02 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) mysql> set global innodb_flush_log_at_trx_commit=2; Query OK, 0 rows affected (0.00 sec) mysql> set global sync_binlog=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'sync_binlog'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 0 | +---------------+-------+ 1 row in set (0.01 sec) mysql>
配置从节点，从完全备份的位置之后开始复制
[root@mysql-slave-02 ~]# grep '^CHANGE MASTER' /data/fullbackup_2022-09-08_09\:18\:28.sql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=156; [root@mysql-slave-02 ~]# vim /data/fullbackup_2022-09-08_09\:18\:28.sql WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead. -- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 8.0.26 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */; WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead. -- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 8.0.26 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */; /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- #新增以下内容 CHANGE MASTER TO MASTER_HOST='192.168.247.20', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=156; 。。。。。。。注意：在mysql8.0.26版本中，备份数据时使用master-data与source-data参数区别
mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql #数据导入需要进入mysql数据库，mysql> source /data/fullbackup.sql
mysqldump -A -F --single-transaction --source-data=1 > /backup/fullbackup_`date +%F_%T`.sql #数据导入支持客户端导入，[root@mysql-slave-02 data]# mysql < fullbackup.sql
[root@mysql-slave-02 data]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source /data/fullbackup.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNING: --master-data is deprecated and will be removed in a future version. Us' at line 1
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
.........................
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use hellodb
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| testlog |
| toc |
+-------------------+
8 rows in set (0.00 sec)
mysql> select count(*) from testlog;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.02 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.247.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 156
Relay_Log_File: mysql-slave-02-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 542
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
Master_UUID: bd524c88-2ec6-11ed-955f-000c2969119b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified
mysql>
mysql>
测试同步功能，在新增10万条数据
三、当master服务器宕机，提升一个slave成为新的master
[root@mysql-master-01 ~]# systemctl stop mysqld
修改新master配置文件，关闭read-only配置
[root@mysql-slave-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] log-bin=/data/mysql/mysql-bin server-id=21 read_only=OFF relay_log=relay-log relay_log_index=relay-log.index [root@mysql-slave-01 ~]#
清除旧的master复制信息
[root@mysql-slave-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> set global read_only=off; Query OK, 0 rows affected (0.00 sec) mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> reset slave all; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql>
在新master上完全备份
[root@mysql-slave-01 ~]# mysqldump -A --single-transaction --source-data=1 -F > backup.sql [root@mysql-slave-01 ~]# ll -h total 9.1M -rw-r--r-- 1 root root 9.1M Sep 8 12:00 backup.sql -rw-r--r-- 1 root root 7.7K Aug 22 2020 hellodb_innodb.sql -rw-r--r-- 1 root root 352 Feb 4 2021 testlog.sql [root@mysql-slave-01 ~]# scp backup.sql root@192.168.247.22:/root The authenticity of host '192.168.247.22 (192.168.247.22)' can't be established. ECDSA key fingerprint is SHA256:LFBeGPKGTITMRj1L7ud6ngPr5gQUcfuYxx6VC3N5yi8. Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added '192.168.247.22' (ECDSA) to the list of known hosts. root@192.168.247.22's password: backup.sql 100% 9237KB 122.0MB/s 00:00 [root@mysql-slave-01 ~]#
其它所有slave重新还原数据库，指向新的master
[root@mysql-slave-02 ~]# vim backup.sql -- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 8.0.26 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */; /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_HOST='192.168.247.21', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=156; 。。。。。。。。
[root@mysql-slave-02 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
mysql> source backup.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
...................
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to source
Master_Host: 192.168.247.21
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 156
Relay_Log_File: mysql-slave-02-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 156
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1130
Last_IO_Error: error connecting to master 'repluser@192.168.247.21:3306' - retry-time: 60 retries: 1 message: Host '192.168.247.22' is not allowed to connect to this MySQL server
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 220908 14:00:22
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql>
error connecting to master 'repluser@192.168.247.21:3306' - retry-time: 60 retries: 1 message: Host '192.168.247.22' is not allowed to connect to this MySQL server 解决办法
mysql> select Host,User from user; +-----------+------------------+ | Host | User | +-----------+------------------+ | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 4 rows in set (0.00 sec) mysql> create user repluser@'192.168.247.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to repluser@'192.168.247.%'; Query OK, 0 rows affected (0.01 sec) mysql> mysql> select Host,User from user; +---------------+------------------+ | Host | User | +---------------+------------------+ | 192.168.247.% | repluser | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +---------------+------------------+ 5 rows in set (0.00 sec) mysql>
再次查看
mysql>
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.247.21
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 701
Relay_Log_File: mysql-slave-02-relay-bin.000003
Relay_Log_Pos: 916
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 701
Relay_Log_Space: 1302
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 21
Master_UUID: b9a8d0c6-2ec6-11ed-b216-000c295e1f1f
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql>
四、实现级联复制
原理：三台MySQL，一台master，两台slave，其中一台slave节点将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制
在192.168.247.20充当master
在192.168.247.21充当级联slave
在192.168.247.22充当slave
对master做如下配置
[root@mysql-master-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] log-bin=/data/mysql/mysql-bin server-id=20 [root@mysql-master-01 ~]# 导入测试数据，并写入10万条测试数据做测试 [root@mysql-master-01 ~]# mysql < hellodb_innodb.sql [root@mysql-master-01 ~]# mysql hellodb < testlog.sql [root@mysql-master-01 ~]# [root@mysql-master-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> create user repluser@'192.168.247.%' identified by '123456'; Query OK, 0 rows affected (0,00 sec) mysql> grant replication slave on *.* to repluser@'192.168.247.%'; Query OK, 0 rows affected (0,01 sec) mysql> flush privileges; Query OK, 0 rows affected (0,00 sec) mysql> use hellodb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> call sp_testlog; Query OK, 1 row affected (2 min 49,54 sec) mysql> select count(*) from testlog; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0,02 sec) mysql> mysql> exit Bye [root@mysql-master-01 ~]# [root@mysql-master-01 ~]# mysqldump -A -F --single-transaction --master-data=1 > /backup/all.sql [root@mysql-master-01 ~]# ll -h /backup/ total 3,8M -rw-r--r-- 1 root root 3,8M Sep 8 14:50 all.sql [root@mysql-master-01 ~]# root@mysql-master-01 ~]# scp -r /backup/all.sql root@192.168.247.21:/root root@192.168.247.21's password: all.sql 100% 3812KB 48.5MB/s 00:00 [root@mysql-master-01 ~]# scp -r /backup/all.sql root@192.168.247.22:/root root@192.168.247.22's password: all.sql 100% 3812KB 51.0MB/s 00:00 [root@mysql-master-01 ~]#
在第一台slave，现实中间级联
[root@mysql-slave-01 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=21
log-bin=/data/mysql/mysql-bin
read-only
log_slave_updates
[root@mysql-slave-01 ~]#
[root@mysql-slave-01 ~]# systemctl restart mysqld
[root@mysql-slave-01 ~]# less all.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
all.sql...skipping...
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO
MASTER_HOST='192.168.247.20',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=156;
。。。。。。。。。。。
[root@mysql-slave-01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source all.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNING: --master-data is deprecated and will be removed in a future version. Us' at line 1
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
。。。。。。。。。。。。。。。
Query OK, 0 rows affected (0.00 sec)
ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL'
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 179 | No |
| mysql-bin.000003 | 156 | No |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.247.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 156
Relay_Log_File: mysql-slave-01-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 542
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
Master_UUID: 6c5025b8-2f3f-11ed-89be-000c2969119b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified
mysql>
创建同步复制账号，并授权
[root@mysql-slave-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create user repluser@'192.168.247.%' identified by '123456'; ERROR 1396 (HY000): Operation CREATE USER failed for 'repluser'@'192.168.247.%' #由于slave-01已经跟master配置了主从同步，slave已经将master创建的repluer账号密码同步到本机 mysql> grant replication slave on *.* to repluser@'192.168.247.%'; #因此授权即可 Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> select User,Host from mysql.user; +------------------+---------------+ | User | Host | +------------------+---------------+ | repluser | 192.168.247.% | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+---------------+ 5 rows in set (0.00 sec) mysql>
配置第二台slave
[root@mysql-slave-02 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=22
read-only
[root@mysql-slave-02 ~]#
[root@mysql-slave-02 ~]# systemctl restart mysqld
[root@mysql-slave-02 ~]# vim all.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO
MASTER_HOST='192.168.247.21', #这里填中间级联机器地址
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=156;
.。。。。。。。。。。。。。。。。
[root@mysql-slave-02 ~]#
[root@mysql-slave-02 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source all.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNING: --master-data is deprecated and will be removed in a future version. Us' at line 1
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
。。。。。。。。。。。。
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL'
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.247.21
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 30689404
Relay_Log_File: mysql-slave-02-relay-bin.000004
Relay_Log_Pos: 6549754
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 6549539
Relay_Log_Space: 30690052
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 431
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 21
Master_UUID: c3387553-2f3f-11ed-a10d-000c295e1f1f
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: waiting for handler commit
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified
mysql>
检查数据同步
查看数据同步
五、主主复制
主主复制特性：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_incremet_increment=2 #增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
主主复制的配置步骤：
- 各节点使用一个唯一server_id
- 都用启用binary log 和relay log
- 创建拥有复制权限的用户账号
- 定义自动增长id字段的数值范围各为奇偶
- 均把对方指定为主节点，并启动复制线程
配置第一个master
[root@mysql-master-01 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=20
log-bin=/data/mysql/mysql-bin
auto_increment_offset=1
auto_increment_increment=2
default_authentication_plugin=mysql_native_password
[root@mysql-master-01 ~]# systemctl stop mysqld
[root@mysql-master-01 ~]# rm -fr /var/lib/mysql/*
[root@mysql-master-01 ~]# rm -fr /data/mysql/*
[root@mysql-master-01 ~]# systemctl start mysqld
[root@mysql-master-01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user repluser@'192.168.247.%' identified by '123456';
Query OK, 0 rows affected (0,01 sec)
mysql> grant replication slave on *.* to repluser@'192.168.247.%';
Query OK, 0 rows affected (0,00 sec)
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 701 | No |
+------------------+-----------+-----------+
2 rows in set (0,01 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.247.21',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_CONNECT_RETRY=10,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=701;
Query OK, 0 rows affected, 9 warnings (0,01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0,00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.247.21
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 701
Relay_Log_File: mysql-master-01-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 701
Relay_Log_Space: 543
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 21
Master_UUID: 9ad8b517-2f57-11ed-8d58-000c295e1f1f
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0,00 sec)
ERROR:
No query specified
mysql>
配置第二个master
[root@mysql-master-02 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=21
log-bin=/data/mysql/mysql-bin
auto_increment_offset=2
auto_increment_increment=2
default_authentication_plugin=mysql_native_password
[root@mysql-master-02 ~]#
[root@mysql-master-02 ~]# systemctl stop mysqld
[root@mysql-master-02 ~]# rm -fr /var/lib/mysql/*
[root@mysql-master-02 ~]# rm -fr /data/mysql/*
[root@mysql-master-02 ~]# systemctl start mysqld
[root@mysql-master-02 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user repluser@'192.168.247.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repluser@'192.168.247.%';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 701 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.247.20',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_CONNECT_RETRY=10,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=701;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.247.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 701
Relay_Log_File: mysql-master-02-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 701
Relay_Log_Space: 543
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
Master_UUID: 8ff20ee5-2f57-11ed-9211-000c2969119b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql>
冲突故障模拟测试
在第一个master创建db数据库
#两个节点分别插入数据
#在第一个节点上执行
mysql> create database db1;
mysql> insert t1 (name) values('user1');
#在第二个节点上执行
mysql> insert t1 (name) values('user2');
#两个节点同时插入数据
mysql> insert t1 (name) values('userX');
mysql> select * from t1;
两台节点同时创建db2库时，显示创建ok，实着已经冲突了
报错Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2'
六、半同步复制
缺点1： 幻读
- 当用户提交一个事务，该事务已经写入redo日志和binlog日志，但该事务还没写入从库，此时处在waiting slave dump处，此时另一个用户可以读取到这条数据，而他自己却不能；
缺点2：数据丢失
- 一个提交的事务在waiting slave dump处crash后，主库将比从库多一条数据
配置半同步复制
查看插件文件
[root@mysql-master-01 ~]# rpm -ql mysql-server |grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_replica.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/semisync_source.so
[root@mysql-master-01 ~]#
配置主节点
[root@mysql-master-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=20 log-bin=/data/mysql/mysql-bin default_authentication_plugin=mysql_native_password #rpl_semi_sync_master_enabled=ON #启用semi插件 #rpl_semi_sync_master_timeout=3000 [root@mysql-master-01 ~]# [root@mysql-master-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Query OK, 0 rows affected, 1 warning (0,01 sec) mysql> SHOW PLUGINS; +---------------------------------+----------+--------------------+--------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+--------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | +---------------------------------+----------+--------------------+--------------------+---------+ 46 rows in set (0,00 sec) mysql> SET GLOBAL rpl_semi_sync_master_enabled=1; Query OK, 0 rows affected (0,00 sec) mysql> SET GLOBAL rpl_semi_sync_master_timeout = 3000; Query OK, 0 rows affected (0,00 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 3000 | | 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 | +-------------------------------------------+------------+ 6 rows in set (0,01 sec) mysql> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | 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 | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows in set (0,00 sec) mysql> mysql> select @@rpl_semi_sync_master_enabled; +--------------------------------+ | @@rpl_semi_sync_master_enabled | +--------------------------------+ | 1 | +--------------------------------+ 1 row in set (0,00 sec) mysql> 修改my.cnf [root@mysql-master-01 ~]# vi /etc/my.cnf [root@mysql-master-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=20 log-bin=/data/mysql/mysql-bin default_authentication_plugin=mysql_native_password rpl_semi_sync_master_enabled=ON rpl_semi_sync_master_timeout=3000 [root@mysql-master-01 ~]# [root@mysql-master-01 ~]# systemctl restart mysqld [root@mysql-master-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 3000 | | 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 | +-------------------------------------------+------------+ 6 rows in set (0,01 sec) mysql> select @@rpl_semi_sync_master_enabled; +--------------------------------+ | @@rpl_semi_sync_master_enabled | +--------------------------------+ | 1 | +--------------------------------+ 1 row in set (0,00 sec) mysql>
或者
[root@mysql-master-01 ~]# vim /etc/my.cnf [root@mysql-master-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=20 log-bin=/data/mysql/mysql-bin default_authentication_plugin=mysql_native_password plugin-load-add = "semisync_master.so" rpl_semi_sync_master_enabled=ON rpl_semi_sync_master_timeout=3000 [root@mysql-master-01 ~]# systemctl restart mysqld
slave-01从服务器配置
[root@mysql-slave-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=21 log-bin=/data/mysql/mysql-bin default_authentication_plugin=mysql_native_password #rpl_semi_sync_slave_enabled=ON #需要进入数据库加载启动semi模块才能启动 [root@mysql-slave-01 ~]# #从服务器配置: [root@mysql-slave-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1; #临时开启 Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.02 sec) mysql> 开启my.cnf semi功能 [root@mysql-slave-01 ~]# vi /etc/my.cnf [root@mysql-slave-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=21 log-bin=/data/mysql/mysql-bin default_authentication_plugin=mysql_native_password rpl_semi_sync_slave_enabled=ON [root@mysql-slave-01 ~]# [root@mysql-slave-01 ~]# systemctl restart mysqld [root@mysql-slave-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.01 sec) mysql> mysql> select @@rpl_semi_sync_slave_enabled; +-------------------------------+ | @@rpl_semi_sync_slave_enabled | +-------------------------------+ | 1 | +-------------------------------+ 1 row in set (0.00 sec) mysql>
或者
[root@mysql-slave-01 ~]# vi /etc/my.cnf [root@mysql-slave-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=21 log-bin=/data/mysql/mysql-bin default_authentication_plugin=mysql_native_password plugin-load-add = "semisync_slave.so" rpl_semi_sync_slave_enabled=ON [root@mysql-slave-01 ~]# systemctl restart mysqld
slave-02配置
[root@mysql-slave-02 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=22 log-bin=/data/mysql/mysql-bin default_authentication_plugin=mysql_native_password #rpl_semi_sync_slave_enabled=ON [root@mysql-slave-02 ~]# [root@mysql-slave-02 ~]# systemctl restart mysqld [root@mysql-slave-02 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.10 sec) mysql> exit Bye [root@mysql-slave-02 ~]# vi /etc/my.cnf [root@mysql-slave-02 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=22 log-bin=/data/mysql/mysql-bin default_authentication_plugin=mysql_native_password rpl_semi_sync_slave_enabled=ON [root@mysql-slave-02 ~]# systemctl restart mysqld [root@mysql-slave-02 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.01 sec) mysql> mysql> select @@rpl_semi_sync_slave_enabled; +-------------------------------+ | @@rpl_semi_sync_slave_enabled | +-------------------------------+ | 1 | +-------------------------------+ 1 row in set (0.00 sec) mysql>
或者
[root@mysql-slave-02 ~]# vi /etc/my.cnf [root@mysql-slave-02 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=22 log-bin=/data/mysql/mysql-bin default_authentication_plugin=mysql_native_password plugin-load-add = "semisync_slave.so" rpl_semi_sync_slave_enabled=ON [root@mysql-slave-02 ~]# systemctl restart mysqld
查看master半同步信息
[root@mysql-master-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 3000 | | 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 | +-------------------------------------------+------------+ 6 rows in set (0,01 sec) mysql> mysql> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | 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 | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows in set (0,00 sec) mysql>
配置主从复制
mysql> reset master; #删除多余的binlog日志
主节点配置
[root@mysql-master-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> reset master; #清空所有binlog日志 Query OK, 0 rows affected (0,02 sec) mysql> show master logs; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000001 | 156 | No | +------------------+-----------+-----------+ 1 row in set (0,00 sec) mysql> create user repluser@'192.168.247.%' identified by '123456'; Query OK, 0 rows affected (3,03 sec) mysql> grant replication slave on *.* to repluser@'192.168.247.%'; Query OK, 0 rows affected (0,00 sec) mysql> flush privileges; Query OK, 0 rows affected (0,00 sec) mysql>
slave-01配置
[root@mysql-slave-01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> reset master;
Query OK, 0 rows affected (0.09 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.247.20',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=156,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 8 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.247.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 858
Relay_Log_File: mysql-slave-01-relay-bin.000002
Relay_Log_Pos: 1026
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 858
Relay_Log_Space: 1244
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
Master_UUID: 3a4b1173-2f6f-11ed-8186-000c2969119b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified
mysql>
slave-02配置
[root@mysql-slave-02 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> reset master;
Query OK, 0 rows affected (0.55 sec)
mysql>
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.247.20',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=156,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 8 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.247.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 858
Relay_Log_File: mysql-slave-02-relay-bin.000002
Relay_Log_Pos: 1026
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 858
Relay_Log_Space: 1244
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
Master_UUID: 3a4b1173-2f6f-11ed-8186-000c2969119b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified
mysql>
再次看看master进程状态
[root@mysql-master-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show processlist; +----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 2072 | Waiting on empty queue | NULL | | 11 | repluser | 192.168.247.21:37662 | NULL | Binlog Dump | 305 | Source has sent all binlog to replica; waiting for more updates | NULL | | 12 | repluser | 192.168.247.22:36008 | NULL | Binlog Dump | 294 | Source has sent all binlog to replica; waiting for more updates | NULL | | 13 | root | localhost | NULL | Query | 0 | init | show processlist | +----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+ 4 rows in set (0,00 sec) mysql> mysql> mysql> 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 | 0 | | 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 | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows in set (0,00 sec) mysql>
导入数据测试
半同步模拟
1、停掉一个slave节点线程
启动停掉的线程，观察是否会自动同步
2、停止两个slave的线程，观察主节点写数据是否会卡顿3秒，然后继续自行后面语句
3、恢复线程，观察是否会自动同步
4、关闭一台机器，模拟硬件故障宕机，等待恢复后，是否会进行数据同步
5、恢复机器，验证数据是否自动同步
这篇关于MySQL复制的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-12-25如何部署MySQL集群资料：新手入门教程
- 2024-12-24MySQL集群部署资料：新手入门教程
- 2024-12-24MySQL集群资料详解：新手入门教程
- 2024-12-24MySQL集群部署入门教程
- 2024-12-24部署MySQL集群学习：新手入门教程
- 2024-12-24部署MySQL集群入门：一步一步搭建指南
- 2024-12-07MySQL读写分离入门：轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南
