Mysql 集群技术-CSDN博客

本文链接：https://blog.csdn.net/m0_63469489/article/details/141687187

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

在企业中90%的服务器操作系统均为Linux

在企业中对于Mysql的安装通常用源码编译的方式来进行

官网：MySQL

1.1在Linux下部署mysql

安装依赖性:
root@mysql-node1 ~]# dnf install cmake gcc-c++ openssl-devel \ ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64

下载并解压源码包:
[root@mysql-node1 ~]# tar zxf mysql-boost-5.7.44.tar.gz
[root@mysql-node1 ~]# cd /root/mysql-5.7.44

源码编译安装mysql:
[root@mysql-node1 mysql-5.7.44]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #指定安装路径
-DMYSQL_DATADIR=/data/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/ 


[root@mysql-node1 mysql-5.7.44]#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0

[root@mysql-node1 mysql-5.7.44]# make -j2    
#-j2 表示有几个核心就跑几个进程

[root@mysql-node1 mysql-5.7.44# make install

若想要删除: cd /usr/local/myaql 删除这个目录就好

1.2部署mysql

[root@mysql-node2 ~]# cd /usr/local/mysql/
[root@mysql-node2 mysql]# useradd -s /sbin/nologin  -M mysql
[root@mysql-node2 mysql]# mkdir /data/mysql -p
[root@mysql-node2 mysql]# /
[root@mysql-node2 mysql]# pwd
/usr/local/mysql
[root@mysql-node2 mysql]# ls
bin   include  LICENSE  mysql-test  README-test  support-files
docs  lib      man      README      share
[root@mysql-node2 mysql]# cd support-files/
[root@mysql-node2 support-files]# cp mysql.server /etc/init.d/mysqld

[root@mysql-node2 support-files]# vim /etc/my.cnf
[root@mysql-node2 support-files]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.socks
symbolic-links=0

[root@mysql-node2 support-files]# vim ~/.bash_profile
[root@mysql-node2 support-files]# source ~/.bash_profile
[root@mysql-node2 support-files]# cat ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

[root@mysql-node1 ~]# mysqld --user mysql --initialize
2024-08-22T02:19:54.308026Z 1 [Note] A temporary password is generated for root@localhost:       密码记住:  ;c8.Z=8j#taq  


[root@mysql-node1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node1.timinglee.org.err'.
 SUCCESS!


[root@mysql-node1 ~]# chkconfig  mysqld on
[root@mysql-node1 ~]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关
netconsole      0:关    1:关    2:关    3:关    4:关    5:关    6:关
network         0:关    1:关    2:开    3:开    4:开    5:开    6:关
rhnsd           0:关    1:关    2:开    3:开    4:开    5:开    6:关
[root@mysql-node1 ~]# vim passwd
[root@mysql-node1 ~]# cat passwd
;c8.Z=8j#taq   #密码  172.25.254.10的密码
m:sMhSg-S24o   #密码  172.25.254.30的密码
 
[root@mysql-node1 ~]# mysql_secure_installation


#测试:
[root@mysql-node1 ~]# mysql -uroot  -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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                |
+--------------------+
4 rows in set (0.00 sec)

二.mysql的组从复制

2.1配置mastesr

[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1

[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

[root@mysql-node1 ~]# mysql -p;c8.Z=8j#taq
Enter password:

mysql>  CREATE USER 'repl'@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)



测试:
mysql> CREATE TABLE mysql.userlist (
    ->   username varchar(20) not null,password varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO mysql.userlist VALUE ('lee','123');
Query OK, 1 row affected (0.01 sec)

mysql> select * from mysql.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| user3    | 123      |
+----------+----------+
2 rows in set (0.00 sec)

2.2配置salve

[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2

[root@mysql-node3 ~]# /etc/init.d/mysqld restart
[root@mysql-node3 ~]# mysql -m:sMhSg-S24o    #172.25.254.30的密码

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.25.254.10',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='lee',
    -> 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.00 sec)

mysql>  SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  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: 595
               Relay_Log_File: mysql-node3-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 595
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             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


在slave中查看数据是否同步过来:
mysql> SELECT * FROM mysql.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
+----------+----------+
1 row in set (0.00 sec)

2.3当有数据时添加slave2

部署node2:

先从master172.25.254.10拷贝mysql:
[root@mysql-node1 ~]# rsync -al /usr/local/mysql root@172.25.254.20:/usr/local
root@172.25.254.20's password:123456

部署node2:
[root@mysql-node2 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node2 ~]# mkdir -p /data/mysql
[root@mysql-node2 ~]# chown mysql.mysql -R /data/mysql
[root@mysql-node2 ~]# cd /usr/local
[root@mysql-node2 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
[root@mysql-node2 local]# cd
[root@mysql-node2 ~]# vim /etc/my.cnf
[root@mysql-node2 ~]# cp /usr/local/mysql/s
share/         support-files/
[root@mysql-node2 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql-node2 ~]# vim ~/.bash_profile
[root@mysql-node2 ~]# source ~/.bash_profile
[root@mysql-node2 ~]# mysqld --user=mysql --initialize

2024-08-22T07:54:42.692924Z 1 [Note] A temporary password is generated for root@localhost: #+x)k=#ra7B:

[root@mysql-node2 ~]# chkconfig  mysqld on
[root@mysql-node2 ~]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关
netconsole      0:关    1:关    2:关    3:关    4:关    5:关    6:关
network         0:关    1:关    2:开    3:开    4:开    5:开    6:关
rhnsd           0:关    1:关    2:开    3:开    4:开    5:开    6:关
[root@mysql-node2 ~]# ll /data/mysql
总用量 110660
-rw-r----- 1 mysql mysql       56 8月  22 15:54 auto.cnf
-rw------- 1 mysql mysql     1680 8月  22 15:54 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 8月  22 15:54 ca.pem
-rw-r--r-- 1 mysql mysql     1112 8月  22 15:54 client-cert.pem
-rw------- 1 mysql mysql     1676 8月  22 15:54 client-key.pem
-rw-r----- 1 mysql mysql      436 8月  22 15:54 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月  22 15:54 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月  22 15:54 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月  22 15:54 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 8月  22 15:54 mysql
drwxr-x--- 2 mysql mysql     8192 8月  22 15:54 performance_schema
-rw------- 1 mysql mysql     1676 8月  22 15:54 private_key.pem
-rw-r--r-- 1 mysql mysql      452 8月  22 15:54 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 8月  22 15:54 server-cert.pem
-rw------- 1 mysql mysql     1676 8月  22 15:54 server-key.pem
drwxr-x--- 2 mysql mysql     8192 8月  22 15:54 sys
[root@mysql-node2 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
Error: Can't connect to local MySQL server through socket '/data/mysql/mysql.sock' (2)       #若报错就重启

[root@mysql-node2 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node2.timinglee.org.err'.
 SUCCESS!
[root@mysql-node2 ~]# mysql_secure_installation
[root@mysql-node2 ~]# mysql -uroot  -p
Enter password:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
部署完成

node1:

Note:
生产环境中备份时需要锁表，保证备份前后的数据一致
mysql> FLUSH TABLES WITH READ LOCK;

备份后再解锁
mysql> UNLOCK TABLES;
mysqldump命令备份的数据文件，在还原时先DROP TABLE，需要合并数据时需要删除此语句

从master节点备份数据:
[root@mysql-node1 ~]# mysqldump -uroot -p mysql > timinglee.sql
Enter password:
[root@mysql-node1 ~]# scp timinglee.sql root@172.25.254.20:/mnt/
root@172.25.254.20's password:
timinglee.sql    


[root@mysql-node1 ~]# mysql -uroot -p -e "SHOW MASTER STATUS;"
Enter password:
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1088 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

node2:

[root@mysql-node2 ~]# cd /mnt/
[root@mysql-node2 mnt]# ls
timinglee.sql


主从复制:
[root@mysql-node2 mnt]# mysql -uroot -p123456 -e "create database zou;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-node2 mnt]# mysql -uroot -p123456 zou < timinglee.sql
mysql: [Warning] Using a password on the command line interface can be insecure.



mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl', MASTER_PASSWORD='lee', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1088;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>  start slave;
Query OK, 0 rows affected (0.00 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.000001
          Read_Master_Log_Pos: 1088
               Relay_Log_File: mysql-node2-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: 1088
              Relay_Log_Space: 533
              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: 1
                  Master_UUID: 19f41128-6032-11ef-bb3e-000c29130bf4
             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

测试:

mysql> INSERT INTO mysql.userlist VALUES('user3','123');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| user3    | 123      |
+----------+----------+
2 rows in set (0.00 sec)

2.4延迟复制

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

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

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

#在slave端
mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_DELAY=60;
Query OK, 0 rows affected (0.01 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;
Empty set (0.00 sec

mysql> SHOW SLAVE STATUS\G;       
           Master_Server_Id: 1         
           Master_UUID: db2d8c92-4dc2-11ef-b6b0-000c299355ea                    Master_Info_File: /data/mysql/master.info         
           SQL_Delay: 60 ##延迟效果
           SQL_Remaining_Delay: NULL  
           Slave_SQL_Running_State: Slave has read all relay log; waiting for more  updates      
           Master_Retry_Count: 86400

测试：在master中写入数据后过了延迟时间才能被查询到

2.5慢查询日志

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

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

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

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

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

mysql> SHOW variables like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | OFF                              |
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.01 sec)

2.5.1开启慢查询日志

#master上做:
mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.00 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>  SHOW VARIABLES like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | ON                               |
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)

[root@mysql-node1 ~]# cat /data/mysql/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

测试慢查询:

mysql> select sleep (10);
+------------+
| sleep (10) |
+------------+
|          0 |
+------------+
1 row in set (10.00 sec)

[root@mysql-node1 ~]# cat /data/mysql/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-23T06:17:05.728453Z
# User@Host: root[root] @ localhost []  Id:     4
# Query_time: 10.000817  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1724393825;
select sleep (10);

2.6mysql的并行复制

查看slaves上的查看线程信息:

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  786 | Slave has read all relay log; waiting for more updates | NULL             |
|  2 | system user |           | NULL | Connect |  796 | Waiting for master to send event                       | NULL             |
|  4 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

默认情况下slave中使用的是sql单线程回放
在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重
开启MySQL的多线程回放可以解决上述问题

在slaves中设定:

[root@mysql-node3 ~]# vim /etc/my.cnf
[root@mysql-node3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

[root@mysql-node3 mysql-5.7.44]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

[root@mysql-node3 ~]# mysql -uroot -p123456
mysql> show processlist;

此时sql线程转化为协调线程，16个worker负责处理sql协调线程发送过来的处理请求

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

2.7原理刨析

三个线程

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

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

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

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

复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）。

步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；

步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

具体操作

1.slaves端中设置了master端的ip，用户，日志，和日志的Position，通过这些信息取得master的认证及信息

2.master端在设定好binlog启动后会开启binlog dump的线程

3.master端的binlog dump把二进制的更新发送到slave端的

4.slave端开启两个线程，一个是I/O线程，一个是sql线程，

i/o线程用于接收master端的二进制日志，此线程会在本地打开relaylog中继日志，并且保存到本地磁盘

sql线程读取本地relog中继日志进行回放

5.什么时候我们需要多个slave？当读取的而操作远远高与写操作时。我们采用一主多从架构数据库外层接入负载均衡层并搭配高可用机制

2.8 架构缺陷

主从架构采用的是异步机制
master更新完成后直接发送二进制日志到slave，但是slaves是否真正保存了数据master端不会检测
master端直接保存二进制日志到磁盘
当master端到slave端的网络出现问题时或者master端直接挂掉，二进制日志可能根本没有到达slave
master出现问题slave端接管master，这个过程中数据就丢失了这样的问题出现就无法达到数据的强一致性，零数据丢失

三半同步模式

3.1半同步模式原理

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

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

3.保存完成后slave向master端返回ack 4.在未接受到slave的ack时master端时不做提交的，一直处于等待当收到ack后提交到存储引擎

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

3.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即可

master上:

[root@mysql-node1 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]#  /etc/init.d/mysqld restart
Shutting down MySQL........... SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON

slaves2上:

[root@mysql-node2 ~]# vim /etc/my.cnf
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node2 ~]# cat  /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=3
gtid_mode=ON
enforce-gtid-consistency=ON

slaves3上:

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='172.25.254.10'
    -> ,master_user='repl',
    -> master_password=';c8.Z=8j#taq',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 172.25.254.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004      
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-node3-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
#slaves2上同理

3.3mysql的半同步模式

[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sockserver-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1   #开启半同步功能
symbolic-links=0

[root@mysql-node1 ~]# mysql -uroot -p

#安装半同步插件:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
ERROR 1125 (HY000): Function 'rpl_semi_sync_master' already exists

#查看插件情况:
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> select * from information_schema.PLUGINS where PLUGIN_NAME LIKE '%semi%'\G;
*************************** 1. row ***************************
           PLUGIN_NAME: rpl_semi_sync_master
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: REPLICATION
   PLUGIN_TYPE_VERSION: 4.0
        PLUGIN_LIBRARY: semisync_master.so
PLUGIN_LIBRARY_VERSION: 1.7
         PLUGIN_AUTHOR: He Zhenxing
    PLUGIN_DESCRIPTION: Semi-synchronous replication master
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.00 sec)

ERROR:
No query specified

#打开半同步功能:
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

#查看半同步的状态:
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.00 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.00 sec)

在两台slave上也要配置半同步:

[root@mysql-node3 ~]# vim /etc/my.cnf
[root@mysql-node3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1    #开启半同步


登录数据库
#安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

#开启半同步功能：
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)

#重启i/o线程，半同步才可以生效：
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
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.00 sec)

master上测试一下:

#写入数据:
mysql> select * from mysql.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| user3    | 123      |
+----------+----------+
2 rows in set (0.00 sec)

mysql> INSERT INTO mysql.userlist values('user1','123');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| user3    | 123      |
| user1    | 123      |
+----------+----------+
3 rows in set (0.00 sec)


mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| 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)

mysql> INSERT INTO mysql.userlist values('user2','333');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| user3    | 123      |
| user1    | 123      |
| user2    | 333      |
| user2    | 333      |
+----------+----------+
5 rows in set (0.00 sec)


#slave上执行的:
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

注意：

半同步模式无法写入数据,一笔数据为同步时自动转为异步模式(可以写入数据)，当slave恢复后 ,会自动恢复写入数据.(如果把slave的i/o线程关闭,master也可以写入,默认要10s) 面试时最好说不能写入数据。

数据库报错解决

1.启动不了数据库：

查看进程，关闭多余的进程，再重启

2.修改数据库密码：

mysql> alter user root@localhost identified by '123'
    -> ;
Query OK, 0 rows affected (0.01 sec)

四 mysql高可用之组复制 (MGR)

MySQL Group Replication(简称 MGR )是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩展的解决方案组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务

MySQL 组复制分单主模式和多主模式，传统的mysql复制技术仅解决了数据同步的问题，

MGR 对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，这是一种内置的自动裂脑保护机制

MGR由组通信系统( Group Communication System ，GCS ) 协议支持该系统提供故障检测机制、组成员服务以及安全且有序的消息传递

4.1 组复制流程

4.2 组复制单主和多主模式

single-primary mode(单写或单主模式)

单写模式 group 内只有一台节点可写可读，其他节点只可以读。当主服务器失败时，会自动选择新的主服务器

multi-primary mode(多写或多主模式)

组内的所有机器都是 primary 节点，同时可以进行读写操作，并且数据是最终一致的。

4.3.实现mysql组复制

[root@mysql-node1 ~]# rm -rf /data/mysql/*
[root@mysql-node1 ~]# ls /data/mysql
[root@mysql-node1 ~]# ps aux | grep mysql     #只要一个进程
root       2167  0.0  0.0 112824   972 pts/0    S+   14:57   0:00 grep --color=auto mysql
[root@mysql-node1 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!

[root@mysql-node1 ~]# mysqld --user=mysql --initialize

注意:

为了避免出错，在所有节点中从新生成数据库数据

master端:
[root@mysql-node1 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1       #注意不能一样
gtid_mode=ON
enforce-gtid-consistency=ON
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.10:33061"  #ip要修改
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"  
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1


[root@mysql-node1 ~]# rm -fr /data/mysql/*
[root@mysql-node1 ~]# ls /data/mysql    #为空才正确
[root@mysql-node1 ~]# mysqld --user mysql --initialize
#在启动

登录数据库:
mysql> alter user root@localhost identified by '123'
    -> ;    #修改密码
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER root@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT REPLICATION SLAVE ON *.* TO root@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='123' FOR CHANNEL
    -> 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
 #用以指定初始成员，值在第一台主机中执行


mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.03 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST               | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| group_replication_applier | 8084a4c1-61e6-11ef-a7d6-000c29130bf4 | mysql-node1.timinglee.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
1 row in set (0.00 sec)


#将文件拷贝过去:
[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.20:/etc/my.cnf
[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.30:/etc/my.cnf

node2和3同理:

SET GLOBAL group_replication_bootstrap_group=ON; 只在master上指定就可以了

mysql> alter user root@localhost identified by '123'   #修改密码
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER root@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO root@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='123' FOR CHANNEL
    -> 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.03 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST               | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| group_replication_applier | 943dc791-61e8-11ef-af72-000c296f9873 | mysql-node2.timinglee.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST               | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| group_replication_applier | 279ee1bb-61e8-11ef-bb7e-000c29038e97 | mysql-node3.timinglee.org |        3306 | ONLINE       |
| group_replication_applier | 943dc791-61e8-11ef-af72-000c296f9873 | mysql-node2.timinglee.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
2 rows in set (0.00 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database zyl;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zyl                |
+--------------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE zyl.userlist(
    -> username VARCHAR(10) PRIMARY KEY NOT NULL,
    -> password VARCHAR(40) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO zyl.userlist VALUES ('user1','111');
Query OK, 1 row affected (0.02 sec)

mysql> select * from zyl.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

五 mysql-router（mysql路由）

MySQL Router

是一个对应用程序透明的InnoDB Cluster连接路由服务，提供负载均衡、应用连接故障转移和客户端路由。利用路由器的连接路由特性，用户可以编写应用程序来连接到路由器，并令路由器使用相应的路由策略来处理连接，使其连接到正确的MySQL数据库服务器

Mysql route的部署方式

我们需要在所有的数据库主机之外再开一台主机mysql-router

首先关闭master上的mysql服务:
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!

#安装mysql-router:
[root@mysql-node1 ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm

警告：mysql-router-community-8.4.0-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID a8d3785c: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-router-community-8.4.0-1.el################################# [100%]
 
 #配置mysql-router的文件:
[root@mysql-node1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[root@mysql-node1 ~]# cat /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306
routing_strategy = round-robin

[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.254.30:3306,172.25.254.20:3306,172.25.254.10:3306
routing_strategy = first-available

#启动mysql-router:
[root@mysql-node1 ~]# systemctl start mysqlrouter.service

在两台slave上部署:

#建立测试用户:
mysql> CREATE USER root@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO root@'%';
Query OK, 0 rows affected (0.00 sec)

在master上测试:

#根据端口访问,在master上停止mysql服务以后,也能登录mysql:
[root@mysql-node1 ~]# mysql -uroot -p123 -h 172.25.254.10 -P 7001
[root@mysql-node1 ~]# mysql -uroot -p123 -h 172.25.254.10 -P 7002
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 5
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.