环境条件
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 distributionCopyright (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 eventMaster_Host: 192.168.247.20Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 156Relay_Log_File: relay-log.000002Relay_Log_Pos: 324Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 156Relay_Log_Space: 527Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 20Master_UUID: bd524c88-2ec6-11ed-955f-000c2969119bMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_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
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 distributionCopyright (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 -ADatabase 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 distributionCopyright (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 -ADatabase 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 distributionCopyright (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 distributionCopyright (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; 。。。。。。。
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 distributionCopyright (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 eventMaster_Host: 192.168.247.20Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 156Relay_Log_File: mysql-slave-02-relay-bin.000002Relay_Log_Pos: 324Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 156Relay_Log_Space: 542Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 20Master_UUID: bd524c88-2ec6-11ed-955f-000c2969119bMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0.01 sec)ERROR: No query specifiedmysql> 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 distributionCopyright (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 distributionCopyright (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 -ADatabase 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 sourceMaster_Host: 192.168.247.21Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 156Relay_Log_File: mysql-slave-02-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000004Slave_IO_Running: Connecting Slave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 156Relay_Log_Space: 156Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 1130Last_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 serverLast_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0Master_UUID: Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: 220908 14:00:22Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0.00 sec)ERROR: No query specifiedmysql>
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 eventMaster_Host: 192.168.247.21Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 701Relay_Log_File: mysql-slave-02-relay-bin.000003Relay_Log_Pos: 916Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 701Relay_Log_Space: 1302Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 21Master_UUID: b9a8d0c6-2ec6-11ed-b216-000c295e1f1fMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0.00 sec)ERROR: No query specifiedmysql>
四、实现级联复制
原理：三台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 distributionCopyright (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 -ADatabase 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 distributionCopyright (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 eventMaster_Host: 192.168.247.20Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 156Relay_Log_File: mysql-slave-01-relay-bin.000002Relay_Log_Pos: 324Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 156Relay_Log_Space: 542Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 20Master_UUID: 6c5025b8-2f3f-11ed-89be-000c2969119bMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0.01 sec)ERROR: No query specifiedmysql>
创建同步复制账号，并授权
[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 distributionCopyright (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 distributionCopyright (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 eventMaster_Host: 192.168.247.21Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 30689404Relay_Log_File: mysql-slave-02-relay-bin.000004Relay_Log_Pos: 6549754Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 6549539Relay_Log_Space: 30690052Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 21Master_UUID: c3387553-2f3f-11ed-a10d-000c295e1f1fMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: waiting for handler commitMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0.01 sec)ERROR: No query specifiedmysql>
检查数据同步
查看数据同步
五、主主复制
主主复制特性：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长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 distributionCopyright (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 eventMaster_Host: 192.168.247.21Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 701Relay_Log_File: mysql-master-01-relay-bin.000002Relay_Log_Pos: 324Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 701Relay_Log_Space: 543Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 21Master_UUID: 9ad8b517-2f57-11ed-8d58-000c295e1f1fMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0,00 sec)ERROR: No query specifiedmysql>
配置第二个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 distributionCopyright (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 eventMaster_Host: 192.168.247.20Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 701Relay_Log_File: mysql-master-02-relay-bin.000002Relay_Log_Pos: 324Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 701Relay_Log_Space: 543Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 20Master_UUID: 8ff20ee5-2f57-11ed-9211-000c2969119bMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0.00 sec)ERROR: No query specifiedmysql>
冲突故障模拟测试
在第一个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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 eventMaster_Host: 192.168.247.20Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 858Relay_Log_File: mysql-slave-01-relay-bin.000002Relay_Log_Pos: 1026Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 858Relay_Log_Space: 1244Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 20Master_UUID: 3a4b1173-2f6f-11ed-8186-000c2969119bMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0.01 sec)ERROR: No query specifiedmysql>
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 distributionCopyright (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 eventMaster_Host: 192.168.247.20Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 858Relay_Log_File: mysql-slave-02-relay-bin.000002Relay_Log_Pos: 1026Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 858Relay_Log_Space: 1244Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 20Master_UUID: 3a4b1173-2f6f-11ed-8186-000c2969119bMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 1 row in set, 1 warning (0.01 sec)ERROR: No query specifiedmysql>
再次看看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 distributionCopyright (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、恢复机器，验证数据是否自动同步
