Mysql集群技术

一、mysql在服务器中的部署方法

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

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

官网：MySQL

Mysql编译安装

在Linux下部署mysql

#解压包
[root@mysql-node1 ~]# tar zxf mysql-boost-5.7.44.tar.gz
[root@mysql-node1 ~]# cd mysql-5.7.44/
​
#安装依赖性
[root@mysql-node1 mysql-5.7.44]#  yum install cmake gcc-c++ openssl-devel ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64 -y
​
#编译
[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]# rm -fr /usr/local/mysql/
​
#安装
[root@mysql-node1 mysql-5.7.44]# make -j2
[root@mysql-node1 mysql-5.7.44]# make install
​
#重新安装
[root@mysql-node1]# rm -fr mysql-5.7.44/

部署mysql

[root@mysql-node2 ~]# ls
anaconda-ks.cfg       mysql-5.7.44               公共  视频  文档  音乐
initial-setup-ks.cfg  mysql-boost-5.7.44.tar.gz  模板  图片  下载  桌面
[root@mysql-node2 ~]# cd mysql-5.7.44/
[root@mysql-node2 mysql-5.7.44]# ls
​
#建立用户
[root@mysql-node2 mysql-5.7.44]# useradd -s /sbin/nologin -M mysql
[root@mysql-node2 mysql-5.7.44]# mkdir -p /data/mysql
[root@mysql-node2 mysql-5.7.44]# chown mysql.mysql -R /data/mysql/
[root@mysql-node2 mysql-5.7.44]# cd support-files/
[root@mysql-node2 support-files]# cp mysql.server  /etc/init.d/mysqld
​
#生成配置文件
[root@mysql-node2 support-files]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql                #指定数据目录
socket=/data/mysql/mysql.sock      #指定套接字
symbolic-links=0                   #禁止链接到数据目录
​
​
#修改环境变量
[root@mysql-node1 support-files]# vim ~/.bash_profile
[root@mysql-node1 ~]# 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 support-files]# source ~/.bash_profile
​
[root@mysql-node1 support-files]# cd
[root@mysql-node1 ~]# ll /data/mysql/ -d
drwxr-xr-x 2 mysql mysql 6 8月  22 10:07 /data/mysql/
​
#数据库初始化建立mysql基本数据
[root@mysql-node1 ~]# mysqld --user=mysql --initialize
2024-08-22T02:14:47.459237Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is dep     recated. Please use --explicit_defaults_for_timestamp server option (see documentati     on for more details).
2024-08-22T02:14:48.042202Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-22T02:14:48.116667Z 0 [Warning] InnoDB: Creating foreign key constraint syst     em tables.
2024-08-22T02:14:48.177587Z 0 [Warning] No existing UUID has been found, so we assum     e that this is the first time that this server has been started. Generating a new UU     ID: 4e63f4a5-602c-11ef-8075-000c29a3fb4f.
2024-08-22T02:14:48.178433Z 0 [Warning] Gtid table is not ready to be used. Table 'm     ysql.gtid_executed' cannot be opened.
2024-08-22T02:14:48.329875Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. P     lease use TLSv1.2 or higher.
2024-08-22T02:14:48.329891Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled.      Please use TLSv1.2 or higher.
2024-08-22T02:14:48.330403Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-22T02:14:48.357650Z 1 [Note] A temporary password is generated for root@localhost: &kuiw)hO/1gJ
[root@mysql-node1 ~]# vim passwd
​
#如果想重新建立，删除重新做
[root@mysql-node1 ~]# rm -fr /data/mysql/ *
[root@mysql-node1 ~]# mysqld --user=mysql --initialize
[root@mysql-node1 ~]# /etc/init.d/mysqld start
-bash: /etc/init.d/mysqld: 权限不够
[root@mysql-node1 ~]# chmod +x /etc/init.d/mysqld
[root@mysql-node1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node1.wang.org.err'.
 SUCCESS!
​
[root@mysql-node1 ~]# yum install chkconfig -y
[root@mysql-node1 ~]# chkconfig --list

[root@mysql-node1 ~]# cat passwd
 &kuiw)hO/1gJ
 
 #数据库安全初始化
[root@mysql-node1 ~]# mysql_secure_installation
​
Securing the MySQL server deployment.
​
Enter password for user root:&kuiw)hO/1gJ #-------必须记住！！！！
​
The existing password for the user account root has expired. Please set a new password.
​
New password:   #输入新密码：wang
​
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: no #是否使用密码插件
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no        #是否重置密码
​
 ... 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@mysql-node1 ~]# mysql -uroot -pwang
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.
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
​
mysql> EXIT
Bye

​
​

二、mysql的主从复制

2.1 master主机

[root@mysql-node1 ~]# vim /etc/my.cnf
#加入
server-id=10
log-bin=mysql-bin
​
​
[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
​
[root@mysql-node1 ~]# mysql -uroot -p -e "SELECT @@server_id"
Enter password:
+-------------+
| @@server_id |
+-------------+
|          10 |
+-------------+
​
[root@mysql-node1 mysql]# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240822 13:13:18 server id 10  end_log_pos 123 CRC32 0xccab43aa         Start: binlog v 4, server v 5.7.44-log created 240822 13:13:18 at startup
ROLLBACK/*!*/;
BINLOG '
7sjGZg8KAAAAdwAAAHsAAAAAAAQANS43LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADuyMZmEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AapDq8w=
'/*!*/;
# at 123
#240822 13:13:18 server id 10  end_log_pos 154 CRC32 0xd83c4480         Previous-GTIDs
# [empty]
# at 154
#240822 13:13:19 server id 10  end_log_pos 177 CRC32 0x963d32f4         Stop
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*/;
​
[root@mysql-node1 mysql]# mysql -uroot -pwang
mysql> create user repl@'%' identified by 'wang';
Query OK, 0 rows affected (0.00 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.000004 |     1151 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
​
mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 1151
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

​

2.2 slave配置

[root@mysql-node2 ~]#  vim /etc/my.cnf
#加入
server-id=20
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
​
#进入数据库
[root@mysql-node2 ~]# mysql -uroot -pwang
mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='wang',master_log_file='mysql-bin.000005',master_log_pos=154;
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.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-node2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              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: 10
                  Master_UUID: 3e2d9ef9-6045-11ef-9548-000c29a3fb4f
             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)
​

node2:只能读，不能写，自己可以查到，主查不到

测试

#master
[root@mysql-node1 mysql]# mysql -uroot -pwang
mysql> create database wang;
Query OK, 1 row affected (0.00 sec)
​
mysql> create table wang.userlist(
    -> username varchar(20) not null,
    -> password varchar(50) not null
    -> );
Query OK, 0 rows affected (0.01 sec)
​
mysql> insert into wang.userlist value('wang','123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| wang     | 123      |
+----------+----------+
1 row in set (0.00 sec)
​

在slave查看数据是否同步

#slave
[root@mysql-node2 ~]# mysql -uroot -p
mysql> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| wang     | 123      |
+----------+----------+
1 row in set (0.00 sec)

​

2.3有数据时添加slave2

#node1将mysql数据复制到node3
[root@mysql-node1 ~]# rsync -al /usr/local/mysql root@172.25.254.30:/usr/local
The authenticity of host '172.25.254.30 (172.25.254.30)' can't be established.
ECDSA key fingerprint is SHA256:EAhUHzRjnAUCunL/9JW4sJEh+ej+W84OQaY48ZLjKtg.
ECDSA key fingerprint is MD5:15:7b:fa:60:bc:50:37:5e:c8:a2:21:6b:a3:57:3d:7a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.254.30' (ECDSA) to the list of known hosts.
root@172.25.254.30's password:
​

node3部署：

[root@mysql-node3 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node3 ~]# mkdir -p /data/mysql
[root@mysql-node3 ~]# chown mysql.mysql /data/mysql/
[root@mysql-node3 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql-node3 ~]# vim ~/.bash_profile
[root@mysql-node3 ~]# source ~/.bash_profile
[root@mysql-node3 mysql]# vim /etc/my.cnf
[root@mysql-node3 mysql]# mysqld --user=mysql --initialize
[root@mysql-node3 mysql]# mysql_secure_installation
[root@mysql-node3 mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node3.wang.org.err'.
 SUCCESS!

​

拉平后才能写

拉平数据：

#master节点备份数据
[root@mysql-node1 ~]# mysqldump -uroot -p wang > wang.sql
Enter password:
[root@mysql-node1 ~]# scp wang.sql root@172.25.254.30:/mnt/
root@172.25.254.30's password:
wang.sql                                                100% 1945     2.9MB/s   00:00
[root@mysql-node1 ~]# mysql -uroot -p
Enter password:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      803 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

​

[root@mysql-node3 mysql]# cd /mnt/
[root@mysql-node3 mnt]# ls
wang.sql
​
##利用master节点中备份出来的wang.sql在slave中拉平数据
[root@mysql-node3 mnt]# mysql -uroot -pwang -e "create database wang;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-node3 mnt]# mysql -uroot -pwang wang < wang.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-node3 mnt]# mysql -uroot -p
Enter password:
mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='wang',master_log_file='mysql-bin.000005',master_log_pos=803;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
​
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
​
mysql> show slave status\G
mysql> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| wang     | 123      |
| user2    | 123      |
+----------+----------+
2 rows in set (0.00 sec)
​

测试：

#master
mysql> insert into wang.userlist values('user2','123');
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| wang     | 123      |
| user2    | 123      |
+----------+----------+
2 rows in set (0.00 sec)
​
​
#slave
mysql> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| wang     | 123      |
| user2    | 123      |
+----------+----------+
2 rows in set (0.00 sec)
​

​

2.4 延迟复制

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

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

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

原先delay=0

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

mysql> change master to master_delay=60;
Query OK, 0 rows affected (0.00 sec)

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

mysql> show slave status\G

修改后

master

mysql> delete from wang.userlist where username='wang';
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 123      |
+----------+----------+
1 row in set (0.00 sec)
​

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

node3 60s后删除

2.5 慢查询日志

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

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

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

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

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

master

mysql> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 123      |
+----------+----------+
1 row in set (0.00 sec)
​
mysql> show variables like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | OFF                              |
| slow_query_log_file | /data/mysql/mysql-node3-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)
​
#开启慢日志查询
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-node3-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)
​
#测试查询结果
#10s后出结果
mysql> select sleep (10);
+------------+
| sleep (10) |
+------------+
|          0 |
+------------+
1 row in set (10.00 sec)
​

2.6 并行复制

查看slave中的线程信息

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

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

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

node2

[root@mysql-node2 ~]# mysql -uroot -p
Enter password:
mysql> show processlist;
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                                                  | Info             |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
|  5 | system user |           | NULL | Connect | 10712 | Waiting for master to send event                       | NULL             |
|  6 | system user |           | NULL | Connect |   332 | Slave has read all relay log; waiting for more updates | NULL             |
|  8 | root        | localhost | NULL | Query   |     0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> quit
Bye
​
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
super_read_only=on
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-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node2 ~]# mysql -uroot -p
Enter password:
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  2 | system user |           | NULL | Connect |   11 | Slave has read all relay log; waiting for more updates | NULL             |
|  3 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
|  4 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
|  5 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
|  9 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 10 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 11 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 15 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 16 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 18 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 19 | system user |           | NULL | Connect |   11 | Waiting for an event from Coordinator                  | NULL             |
| 20 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
18 rows in set (0.00 sec)

​

​

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

三、半同步模式

3.1 gtid模式

在master端的写入时多用户读写，在slave端的复制时单线程日志回放，所以slave端一定会延迟与 master端

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

那么为接管master的主机继续充当slave角色并会指向到新的master上，作为其slave

这时候按照之前的配置我们需要知道新的master上的pos的id，但是我们无法确定新的master和slave之间差多少

设置gtid

#master，slave端开启gtid模式
[root@mysql-node1 ~]# vim /etc/my.cnf
#加入
enforce-gtid-consistency=ON
​
[root@mysql-node2 ~]# vim /etc/my.cnf
#加入
enforce-gtid-consistency=ON
​
[root@mysql-node3 ~]# vim /etc/my.cnf
#加入
enforce-gtid-consistency=ON
​
#slave端停止slave
[root@mysql-node2 ~]# mysql -uroot -p
Enter password:
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
​
[root@mysql-node3 ~]# mysql -uroot -p
Enter password:
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
​
#开启slave端gtid
mysql>  change master to
    ->  master_host='172.25.254.10',
    ->  master_user='repl',
    ->  master_password='wang',
    ->  master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 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.000006
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-node2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000006
             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: 580
              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: 3e2d9ef9-6045-11ef-9548-000c29a3fb4f
             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)
​

master查看

slave端

3.2 启用半同步模式

在master配置启用半同步模式

[root@mysql-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@mysql-node1 ~]# mysql -uroot -p
Enter password:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
​
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';^C
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wang               |
+--------------------+
5 rows in set (0.00 sec)
​
mysql> select * from information_schema.PLUGINS\G
​
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)
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> 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-node2 ~]# 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@mysql-node3 ~]# mysql -uroot -p
Enter password:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
​
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)
​
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.01 sec)
​
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

​

测试

模拟故障

#slave端：
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
​
​
#master端插入数据
mysql>  insert into wang.userlist values ('user5','555');
Query OK, 1 row affected (0.00 sec)
​
mysql>  insert into wang.userlist values ('user3','333');
Query OK, 1 row affected (10.01 sec)      #10秒超时，10秒后写入数据

​

四、mysql高可用之组复制

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

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

组复制流程

首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层 （Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节 点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起 方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接 提交 即可

编辑主配置文件

[root@mysql-node1 ~]# rm -fr /data/mysql/
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=1                          #配置server唯一标识号
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #禁用指定存储引擎
gtid_mode=ON                         #启用全局事件标识
enforce_gtid_consistency=ON          #强制gtid一致
master_info_repository=TABLE         #复制事件数据到表中而不记录在数据目录中
relay_log_info_repository=TABLE
binlog_checksum=NONE                 #禁止对二进制日志校验
log_slave_updates=ON                 #打开数据库中继，
                                     #当slave中sql线程读取日志后也会写入到自己的binlog中

log_bin=binlog                       #重新指定log名称
binlog_format=ROW #使用行日志格式
plugin_load_add='group_replication.so'            #加载组复制插件
transaction_write_set_extraction=XXHASH64          #把每个事件编码为加密散列
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #通知插件正
式加入或创建的组名名称为uuid格式

group_replication_start_on_boot=off #在server启动时不自动启动组复制
group_replication_local_address="172.25.254.100:33061" #指定插件接受其他成员的信息端口
group_replication_group_seeds="172.25.254.100:33061,172.25.254.200:33061，
172.25.254.300:33061" #本地地址允许访问成员列表

group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8" #主机白名单不随系统自启而启动，只在初始成员主机中手动开启，需要在两种情况下做设定：1.初始化建组时 2.关闭并重新启动整个组时

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    #放弃自己信息以
master事件为主
[root@mysql-node1 ~]# mysqld --user=mysql --initialize
[root@mysql-node1 ~]# /etc/init.d/mysqld start
[root@mysql-node1 ~]# mysql -uroot -p初始化后生成的密码 -e "alter user
root@localhost identified by 'wang';"

配置数据库

[root@mysql-node10 ~]# mysql -pwang
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'wang';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
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='rpl_user', MASTER_PASSWORD='wang' FOR CHANNEL
'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 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.19 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;

#在复制配置文件到myql-node2和mysql-node3

[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.200:/etc/my.cnf

[root@mysql-node1  ~]# scp /etc/my.cnf root@172.25.254.300:/etc/my.cnf

修改mysql-node2和mysql-node3的配置

[root@mysql-node2&&3 ~]# vim /etc/my.cnf
server-id=2 #在3上写3
group_replication_local_address="172.25.254.3 0:33061" #在3上要写300

配置数据库

[root@mysql-node2 & 3 ~]# mysql -pwang
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'wang';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
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='rpl_user', MASTER_PASSWORD='wang' FOR CHANNEL
'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;

在mysql-node1中

[root@mysql-node1 ~]# mysql -p
mysql> CREATE DATABASE wang;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE wang.userlist(
-> username VARCHAR(10) PRIMARY KEY NOT NULL,
-> password VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO wang.userlist VALUES ('user1','111');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
+----------+----------+
1 row in set (0.00 sec)

#在mysql-node2中
[root@mysql-node2 ~]# mysql -p
mysql> INSERT INTO lee.userlist values ('user2','222');
Query OK, 1 row affected (0.00 sec)
mysql> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
| user2 | 222 |
+----------+----------+
2 rows in set (0.00 sec)

#mysql—node3中
[root@mysql-node3 ~]# mysql -p
mysql> INSERT INTO wang.userlist values ('user3','333');
Query OK, 1 row affected (0.00 sec)
mysql> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
| user2 | 222 |
| user3 | 333 |
+----------+----------+
3 rows in set (0.00 sec)