MySQL 集群技术全攻略：从搭建到优化（上）

目录

一.Mysql在服务器中的部署方法

1.在Linux下部署mysql

二.mysql的组从复制

1.当有数据时添加slave2

2.延迟复制

3.慢查询日志

4.mysql的并行复制

5.原理架构图

5.1三个线程

5.2复制三步骤

6.架构缺陷

三.半同步模式

1.半同步模式原理

2.gtid模式

3.启用半同步模式

一.Mysql在服务器中的部署方法

1.在企业中90%的服务器操作系统均为Linux，在企业中对于Mysql的安装通常用源码编译的方式来进行 官网：MySQL

2.mysql源码安装包地址：https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.44.tar.gz

3.要求：两台主机

• mysql-node1；rhel7.9；2核40G；172.25.254.10；

• mysql-nide2；rhel7.9；2核40G；172.25.254.20；

1.在Linux下部署mysql

#两台主机都需要同样的操作。
du -sh mysql-5.7.44/   #查看安装包多大
#有个包需要额外拖进去libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm，先拖入mysql的源码安装包和这个插件安装包。
#安装依赖性
yum install cmake gcc-c++ openssl-devel \
ncurses-devel.x86_64 libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm rpcgen.x86_64
#下载并解压源码包
 tar zxf mysql-boost-5.7.44.tar.gz
 cd /root/mysql-5.7.44
 cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #指定安装路径
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \ #指定套接字文件
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #指定启用INNODB存储引擎，默认
用myisam
-DWITH_EXTRA_CHARSETS=all \ #扩展字符集
-DDEFAULT_CHARSET=utf8mb4 \ #指定默认字符集
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \ #指定默认校验字符集
-DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/ #指定c++库依赖
#当cmake出错后如果想重新检测，删除 mysql-5.7.44 中 CMakeCache.txt即可
make -j2 && make install #-j2 表示有几个
核心就跑几个进程

#基础配置
[root@node1 ~]# cd /usr//local/mysql/
[root@node1 mysql]# useradd -s /sbin/nologin -M mysql
[root@node1 mysql]# mkdir -p /data/mysql
[root@node1 mysql]# chown mysql.mysql -R /data/mysql

#生成启动脚本
[root@node1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

#修改环境变量
[root@node1 ~]# vim ~/.bash_profile
...
export PATH=$PATH:/usr/local/mysql/bin
[root@node1 ~]# source ~/.bash_profile

#生成配置文件
[root@node10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql #指定数据目录
socket=/data/mysql/mysql.sock #指定套接字
symbolic-links=0 #数据只能存放到数据目录中，禁止链接到数据目录
#数据库初始化建立mysql基本数据
[root@node1 ~]# mysqld --initialize --user=mysql
2024-08-22T02:37:30.518651Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-22T02:37:31.150582Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-22T02:37:31.271281Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-22T02:37:31.336837Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 7ae58cb2-602f-11ef-b66d-000c29253f97.
2024-08-22T02:37:31.341154Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-22T02:37:31.654853Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T02:37:31.654875Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T02:37:31.655904Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-22T02:37:31.689067Z 1 [Note] A temporary password is generated for root@localhost: dVFIu4IVE+Sy   #mysql初始密码
[root@node1 ~]# echo dVFIu4IVE+Sy >> passwd   #做个备份。防止忘记。
[root@node1 ~]# /etc/init.d/mysqld start
[root@node1 ~]# yum install chkconfig -y
[root@node1 ~]# chkconfig --list
[root@node1 ~]# chkconfig mysqld on
[root@node3 ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off
netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
rhnsd          	0:off	1:off	2:on	3:on	4:on	5:on	6:off

#数据库安全初始化
[root@node1 ~]# mysql_secure_installation 
Securing the MySQL server deployment.
Enter password for user root:  #输入当前初始密码
The existing password for the user account root has expired. Please set a new password.
New password:   #输入新密码
Re-enter new password:    #重复密码
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: n  #是否启用密码插件
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n   #是否要重置密码
 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.
 - Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!

测试；
[root@node1 ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.44 Source distribution
Copyright (c) 2000, 2023, 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                |
+--------------------+
mysql> quit
Bye

二.mysql的组从复制

-----------------------------------#配置mastesr-----------------------------------
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=0
[root@node1 ~]# /etc/init.d/mysqld restart
#进入数据库配置用户权限
[root@mysql-node10 ~]# mysql -p123
mysql> SHOW GLOBAL VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 20    |
+---------------+-------+
1 row in set (0.06 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          20 |
+-------------+
1 row in set (0.01 sec)
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123'; ##生成专门用来做复制的用
户，此用户是用于slave端做认证用
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; ##对这个用户进行授权
mysql> SHOW MASTER STATUS; ##查看master的状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[root@node1 ~]# cd /data/mysql/
[root@node1 mysql]# mysqlbinlog mysql-bin.000001 -vv ##查看二进制日志

------------------------------------#配置salve------------------------------------
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
[root@node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-node2 ~]# mysql -p123
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=595;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 692
               Relay_Log_File: node2-relay-bin.000004
                Relay_Log_Pos: 905
        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: 692
              Relay_Log_Space: 1278
              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: 10
                  Master_UUID: 7ae58cb2-602f-11ef-b66d-000c29253f97
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave 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: 
1 row in set (0.01 sec)

测试：
[root@node1 ~]# mysql -p123
mysql> CREATE DATABASE lee;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE lee.userlist (
-> username varchar(20) not null,
-> password varchar(50) not null
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO lee.userlist VALUE ('lee','123');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee | 123 |
+----------+----------+
1 row in set (0.00 sec)
在slave中查看数据是否有同步过来
[root@mysql-node2 ~]# mysql -p123
mysql> SELECT * FROM lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee | 123 |b
+----------+----------+
1 row in set (0.00 sec)

 结果图片：

1.当有数据时添加slave2

----------------------------------#完成基础配置--------------------------------
#尽量还是不要直接克隆，他们的uuid是一样，会出问题。
-----------------------------------------------------------------
[root@node1 ~]# yum install rsync -y 
[root@node1 ~]# rsync -al /usr/local/mysql root@172.25.254.30:/usr/local
------------------------------------------------------------------
[root@node3 ~]# yum install rsync -y
[root@node3 ~]# useradd -s /sbin/nologin -M mysql
[root@node3 ~]# mkdir -p /data/mysql
[root@node3 ~]# chown mysql.mysql /data/mysql/
[root@node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
#生成启动脚本
[root@node3 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
#修改环境变量
[root@node3 ~]# vim ~/.bash_profile
...
export PATH=$PATH:/usr/local/mysql/bin
[root@node3 ~]# source ~/.bash_profile
[root@node3 ~]# mysqld --initialize --user=mysql
[root@node3 ~]# ll /data/mysql               #看里面有没有数据，有就对的。
total 110660
-rw-r----- 1 mysql mysql       56 Aug 23 15:39 auto.cnf
-rw------- 1 mysql mysql     1680 Aug 23 15:39 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Aug 23 15:39 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Aug 23 15:39 client-cert.pem
-rw------- 1 mysql mysql     1676 Aug 23 15:39 client-key.pem
-rw-r----- 1 mysql mysql      436 Aug 23 15:39 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Aug 23 15:39 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 23 15:39 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 23 15:39 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Aug 23 15:39 mysql
drwxr-x--- 2 mysql mysql     8192 Aug 23 15:39 performance_schema
-rw------- 1 mysql mysql     1676 Aug 23 15:39 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Aug 23 15:39 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Aug 23 15:39 server-cert.pem
-rw------- 1 mysql mysql     1680 Aug 23 15:39 server-key.pem
drwxr-x--- 2 mysql mysql     8192 Aug 23 15:39 sys
[root@node3 ~]# /etc/init.d/mysqld start
[root@node3 ~]# mysql_secure_installation 
#依次输入：n;n;y;y;y;y.

---------------------------#从master节点备份数据-----------------------------------
[root@node1 ~]# mysqldump -uroot -p123 lee > lee.sql
scp lee.sql root@172.25.254.30:/mnt/

-------#生产环境中备份时需要锁表，保证备份前后的数据一致,目前咱们做的先不锁。-----------
mysql> FLUSH TABLES WITH READ LOCK;
备份后再解锁
mysql> UNLOCK TABLES;
mysqldump命令备份的数据文件，在还原时先DROP TABLE，需要合并数据时需要删除此语句
---- 
Table structure for table `userlist`
--
DROP TABLE IF EXISTS `userlist`; #需要合并数据时需要删除此语句
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

---------------#利用master节点中备份出来的lee.sql在slave2中拉平数据--------------------
[root@node3 ~]# ll /mnt/lee.sql
[root@node3 ~]# mysql -uroot -p123 -e "create database lee;"
[root@node3 ~]# mysql -uroot -p123 lee < /mnt/lee.sql
[root@node3 ~]# mysql -uroot -p123 -e "select * from lee.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+----------+
| username   | password |
+------------+----------+
| lee        | 123      |
| xiaozhuzhu | 456      |
| xiaofeifei | 789      |
+------------+----------+
---------------------------#配置slave2的slave功能------------------------------
#在master中查询日志pos
[root@node1 ~]# mysql -uroot -p123 -e "SHOW MASTER STATUS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1778 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
[root@node3 ~]# mysql -uroot -p123
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',
MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1778;
mysql> start slave;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1778
               Relay_Log_File: node3-relay-bin.000002
                Relay_Log_Pos: 320
        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: 1778
              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: 10
                  Master_UUID: 7fb532cc-6055-11ef-bee3-000c29253f97
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave 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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

#如果出现以下报错：
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
这个错误提示表明在当前有运行的从库 I/O 线程的情况下无法执行这个操作。你需要先停止从库的 I/O 线程才能进行这个更改。可以按照错误提示中的建议，执行以下命令停止从库的 I/O 线程：
STOP SLAVE IO_THREAD FOR CHANNEL '';
然后再执行你之前的 CHANGE MASTER TO 命令进行主从配置的更改。

测试：
[root@node1 ~]# mysql -uroot -p123 -e "INSERT INTO lee.userlist VALUES('cycloneJoker','9999999');"
[root@node2 ~]# mysql -uroot -p123 -e 'select * from lee.userlist;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+----------+
| username     | password |
+--------------+----------+
| lee          | 123      |
| xiaopzhuzhu  | 456      |
| xiaofeifei   | 789      |
| zhuhzuxia    | 456      |
| 刘大帅       | 173      |
| cycloneJoker | 9999999  |
+--------------+----------+

[root@node3 ~]# mysql -uroot -p123 -e 'select * from lee.userlist;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+----------+
| username     | password |
+--------------+----------+
| lee          | 123      |
| xiaozhuzhu   | 456      |
| xiaofeifei   | 789      |
| cycloneJoker | 9999999  |
+--------------+----------+

2.延迟复制

• 延迟复制时用来控制sql线程的，和i/o线程无关

• 这个延迟复制不是i/o线程过段时间来复制，i/o是正常工作的

• 是日志已经保存在slave端了，那个sql要等多久进行回放

-------------------------------#在slave端node3-------------------------------
[root@node3 ~]# mysql -uroot -p123
mysql> STOP SLAVE SQL_THREAD;
mysql> CHANGE MASTER TO MASTER_DELAY=60;
mysql> START SLAVE SQL_THREAD;
mysql> SHOW SLAVE STATUS\G;   
......
             Master_Server_Id: 10
                  Master_UUID: 7fb532cc-6055-11ef-bee3-000c29253f97
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 60    #延迟效果
          SQL_Remaining_Delay: NULL
.......

测试：
#master-[root@node1 ~]# mysql -p123
mysql> delete from lee.userlist where username='lee'
mysql> SELECT * FROM lee.userlist;
#slave[root@node3 ~]# mysql -p123  
mysql> SELECT * FROM lee.userlist;    #自己在隔60s后再看看就会不在了。

3.慢查询日志

• 慢查询，顾名思义，执行很慢的查询

• 当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个SQL语句就是需要优化的

• 慢查询被记录在慢查询日志里

• 慢查询日志默认是不开启的

• 如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

[root@node1 ~]# mysql -p123
mysql> SHOW variables like "slow%";
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| slow_launch_time    | 2                          |
| slow_query_log      | OFF                        |
| slow_query_log_file | /data/mysql/node1-slow.log |
+---------------------+----------------------------+
3 rows in set (0.04 sec)

#开启慢查询日志
mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.02 sec)
mysql> SET long_query_time=4;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES like "long%";   #慢查询日志开启
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

测试:
mysql> select sleep (10);
+------------+
| sleep (10) |
+------------+
|          0 |
+------------+
1 row in set (10.01 sec)

[root@node1 ~]# cat /data/mysql/node1-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2024-08-23T08:11:52.935694Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 10.004439  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1724400712;
select sleep (10);

4.mysql的并行复制

• 默认情况下slave中使用的是sql单线程回放

• 在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重

• 开启MySQL的多线程回放可以解决上述问题

• MySQL 组提交（Group commit）是一个性能优化特性，它允许在一个事务日志同步操作中将多个事务的日志记录一起写入。这样做可以减少磁盘I/O的次数，从而提高数据库的整体性能。

---------------------------在slaves中设定-------------------------------
mysql> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time   | State                                                  | Info             |
+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+
|  2 | root        | localhost | NULL | Sleep   | 107858 |                                                        | NULL             |
| 11 | system user |           | NULL | Connect |   5839 | Waiting for master to send event                       | NULL             |
| 12 | system user |           | NULL | Connect |   5357 | Slave has read all relay log; waiting for more updates | NULL             |
| 14 | root        | localhost | NULL | Query   |      0 | starting                                               | SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

[root@node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK   #基于组提交，
slave-parallel-workers=6        #开启线程数量
master_info_repository=TABLE    #master信息在表中记录，默认记录在/data/mysql//master.info
relay_log_info_repository=TABLE  #回放日志信息在表中记录，默认记录在/data/mysql/relay-log.info
relay_log_recovery=ON #日志回放恢复功能开启
[root@mysql-node2 ~]# /etc/init.d/mysqld restart

测试：在[root@node2 ~]#里面输入SHOW PROCESSLIST;
mysql> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  2 | system user |           | NULL | Connect |    5 | Slave has read all relay log; waiting for more updates | NULL             |
|  3 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                  | NULL             |
|  4 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                  | NULL             |
|  5 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                  | NULL             |
|  9 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                  | NULL             |
| 10 | root        | localhost | NULL | Query   |    0 | starting                                               | SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
8 rows in set (0.00 sec)

5.原理架构图

5.1三个线程

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作，一个主库线程，两个从库线程。

• 二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。

• 从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。

• 从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

5.2复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）。 步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）； 步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

6.架构缺陷

• 主从架构采用的是异步机制

• master更新完成后直接发送二进制日志到slave，但是slaves是否真正保存了数据master端不会检测master端直接保存二进制日志到磁盘；当master端到slave端的网络出现问题时或者master端直接挂掉，二进制日志可能根本没有到达slave

• master出现问题slave端接管master，这个过程中数据就丢失了；这样的问题出现就无法达到数据的强一致性，零数据丢失。

三.半同步模式

1.半同步模式原理

• 用户线程写入完成后master中的dump会把日志推送到slave端

• slave中的io线程接收后保存到relaylog中继日志

• 保存完成后slave向master端返回ack

• 在未接受到slave的ack时master端时不做提交的，一直处于等待当收到ack后提交到存储引擎

• 在5.6版本中用到的时after_commit模式，after_commit模式时先提交在等待ack返回后输出ok

2.gtid模式

• 当为启用gtid时我们要考虑的问题：在master端的写入时多用户读写，在slave端的复制时单线程日志回放，所以slave端一定会延迟与master端

• 这种延迟在slave端的延迟可能会不一致，当master挂掉后slave接管，一般会挑选一个master延迟日志最接近的充当新的master

• 那么为接管master的主机继续充当slave角色并会指向到新的master上，作为其slave；这时候按照之前的配置我们需要知道新的master上的pos的id，但是我们无法确定新的master和slave之间差多少.

• 当激活GITD之后：当master出现问题后，slave2和master的数据最接近，会被作为新的master。

• slave1指向新的master，但是他不会去检测新的master的pos id，只需要继续读取自己gtid_next即可.

-------------------------------------#设置gtid-----------------------------------
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=10
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@node1 ~]# /etc/init.d/mysqld restart

[root@node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=20
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@node2 ~]# /etc/init.d/mysqld restart

[root@node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=30
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@node3 ~]# /etc/init.d/mysqld restart
 
#在master端查看日志是否开启gtid模式
[root@node1 ~]# mysql -p123
mysql> SHOW MASTER STATUS ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@node1 ~]# mysqlbinlog -vv /data/mysql/mysql-bin.000002    #看日志
...
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
...
-------------------------------#停止slave端--------------------------------
[root@node2 ~]# mysql -p123
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
[root@node3 ~]# mysql -p123
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

----------------------------#开启slave端的gtid------------------------------
#slave端两个机子一样的配置
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',
MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154    #自动识别
               Relay_Log_File: node2-relay-bin.000002
                Relay_Log_Pos: 367
        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: 154
              Relay_Log_Space: 574
              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: 10
                  Master_UUID: 7fb532cc-6055-11ef-bee3-000c29253f97
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave 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: 1    #功能开启
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
ERROR: 
No query specified

3.启用半同步模式

#在master端添加开启半同步功能指令
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1   #开启半同步功能
symbolic-links=0
[root@node1 ~]# /etc/init.d/mysqld start
[root@mysql-node1 ~]# mysql -p123  
#安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#查看插件情况
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_NAME LIKE '%semi%'\G
*************************** 1. row ***************************
  PLUGIN_NAME: rpl_semi_sync_master
PLUGIN_STATUS: ACTIVE
1 row in set (0.02 sec)
#打开半同步功能
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#查看半同步功能状态
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| 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.01 sec)


------------------------------#在slave端开启半同步功能-----------------------------
[root@node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=20
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1   #开启半同步功能
symbolic-links=0
[root@node2 ~]# /etc/init.d/mysqld start
[root@node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=30
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1   #开启半同步功能
symbolic-links=0
[root@node3 ~]# /etc/init.d/mysqld start
[root@node2 ~]# mysql -p123
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD; #重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD; ##重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.02 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

测试:
#在master端写入数据
[root@mysql-node1 ~]# mysql -p lee
mysql> insert into lee.userlist values ('joker','123');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM lee.userlist; 
+--------------+----------+
| username     | password |
+--------------+----------+
| xiaozhuzhu   | 456      |
| xiaofeifei   | 789      |
| cycloneJoker | 9999999  |
| joker        | 123      |
+--------------+----------+
4 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |   #未同步数据0笔
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 2126  |
| Rpl_semi_sync_master_tx_wait_time          | 2126  |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |    #已同步数据1笔
+--------------------------------------------+-------+
14 rows in set (0.01 sec)

-----------------------------------#模拟故障-----------------------------------
#在slave端
[root@mysql-node2 ~]# mysql -plee
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
[root@mysql-node3 ~]# mysql -plee
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
#在slave端
[root@mysql-node2 ~]# mysql -plee
mysql>  START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
[root@mysql-node3 ~]# mysql -plee
mysql>  START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
#在master端插入数据
mysql> insert into lee.userlist values ('cyclone','555');
Query OK, 1 row affected (10.01 sec)   #10秒超时

mysql> SHOW STATUS LIKE 'Rpl_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             | 2     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |    #一笔数据为同步
| Rpl_semi_sync_master_status                | OFF   |#自动转为异步模式，当slave恢复
| Rpl_semi_sync_master_timefunc_failures     | 0     | #会自动恢复
| Rpl_semi_sync_master_tx_avg_wait_time      | 2126  |
| Rpl_semi_sync_master_tx_wait_time          | 2126  |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
#故障恢复之后
#master-node1
mysql> insert into lee.userlist values ('cyclone','555');
Query OK, 1 row affected (0.00 sec)
mysql> SHOW STATUS LIKE 'Rpl_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             | 5     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1405  |
| Rpl_semi_sync_master_tx_wait_time          | 2810  |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 2     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql> SELECT * FROM lee.userlist;
+--------------+----------+
| username     | password |
+--------------+----------+
| xiaozhuzhu   | 456      |
| xiaofeifei   | 789      |
| cycloneJoker | 9999999  |
| joker        | 123      |
| cyclone      | 555      |
| cyclone      | 555      |
+--------------+----------+
6 rows in set (0.00 sec)
#slave-node2
mysql> SELECT * FROM lee.userlist;
+--------------+----------+
| username     | password |
+--------------+----------+
| xiaopzhuzhu  | 456      |
| xiaofeifei   | 789      |
| zhuhzuxia    | 456      |
| 刘大帅       | 173      |
| cycloneJoker | 9999999  |
| joker        | 123      |
| cyclone      | 555      |
| cyclone      | 555      |
+--------------+----------+
8 rows in set (0.00 sec)
##slave-node3
mysql> SELECT * FROM lee.userlist;
+--------------+----------+
| username     | password |
+--------------+----------+
| xiaozhuzhu   | 456      |
| xiaofeifei   | 789      |
| cycloneJoker | 9999999  |
| joker        | 123      |
| cyclone      | 555      |
+--------------+----------+
5 rows in set (0.00 sec)     #有延迟复制所以第一时间没有出来。

mysql> SELECT * FROM lee.userlist;
+--------------+----------+
| username     | password |
+--------------+----------+
| xiaozhuzhu   | 456      |
| xiaofeifei   | 789      |
| cycloneJoker | 9999999  |
| joker        | 123      |
| cyclone      | 555      |
| cyclone      | 555      |
+--------------+----------+
6 rows in set (0.00 sec)