当前位置：首页 > news >正文

Mysql 集群技术

news 来源：原创 2024/9/21 16:24:51

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

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

在本次示例过程中需要用到三台主机风别是mysql-node1、mysql-node2和mysql-nnode3 主机IP分别为172.25.254.30\40\50

由于两主机环境一样所以配置过程都是一样的所以下面的示例就只用了node1主机做了示例，同时在node2上也要做和node1上的一样操作，我们也可以在其中一台上面源码安装编译完成后直接将编译文件拷贝到其余两台主机上

主机名	IP地址	左右
root@mysql-node1	172.25.254.30	master
root@mysql-node2	172.25.254.40	slave
root@mysql-node3	172.25.254.50	slave

1.1.1 安装依赖性：

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

1.1.2 下载并解压源码包

[root@mysql-node1 ~]# tar zxf mysql-boost-5.7.44.tar.gz
[root@mysql-node1 ~]# cd /root/mysql-5.7.44/

1.1.3 源码编译安装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/ #指定c++库依赖

[root@mysql-node1 mysql-5.7.44]# make -j2 && make install

1.1.4部署mysql

#生成启动脚本
[root@mysql-node1 ~]# cd /usr/local/mysql/
[root@mysql-node1 mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql-node1 mysql]# mkdir /data/mysql -p
[root@mysql-node1 mysql]# chown mysql.mysql -R /data/mysql
[root@mysql-node1 mysql]# pwd
/usr/local/mysql
[root@mysql-node1 mysql]# cd support-files/
[root@mysql-node1 support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
[root@mysql-node1 support-files]# cp mysql.server /etc/init.d/mysqld
#修改环境变量
[root@mysql-node1 support-files]# vim ~/.bash_profile
export PATH=$PATH:/usr/local/mysql/bin
[root@mysql-node1 support-files]# source ~/.bash_profile

#生成配置文件
[root@mysql-node1 support-files]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql                #指定数据目录
socket=/data/mysql/mysql.sock        #指定套接字
symbolic-links=0                    #数据只能存放到数据目录中，禁止链接到数据目录

#数据库初始化建立mysql基本数据
[root@mysql-node1 ~]# mysqld --user=mysql --initialize
[root@mysql-node1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node1.timinglee.org.err'.
SUCCESS!
[root@mysql-node1 ~]# chkconfig --list

#数据库安全初始化
[root@mysql-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:                   #重复密码

登录数据库

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

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

MySQL主从复制

[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
进入数据库配置用户权限
[root@mysql-node1 ~]# mysql -uroot -p
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'wang'; #生成专门用来做复制的用户，此用户是用于slave端做认证用
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; #查看master的状态
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 595 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

node2配置salve

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

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

[root@mysql-node2 ~]# mysql -p

mysql>CHANGE MASTER TO MASTER_HOST='172.25.254.30',MASTER_USER='repl',MASTER_PASSWORD='wang',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=595;

Query OK, 0 rows affected, 2 warnings (0.00 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.30
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 595
Relay_Log_File: mysql-node2-relay-bin.000003
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: 595
Relay_Log_Space: 699
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: af937912-602f-11ef-953c-000c298b234d
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> select * from wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| wang | 123 |
+----------+----------+
1 row in set (0.00 sec)

mysql> quit
Bye

延迟复制

#在slave端，随便那个slave端都来可以
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;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.30
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 420
Relay_Log_File: mysql-node3-relay-bin.000002
Relay_Log_Pos: 586
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: 420
Relay_Log_Space: 799
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: af937912-602f-11ef-953c-000c298b234d
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_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)

慢查询日志

慢查询，顾名思义，执行很慢的查询
当执行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-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)

[root@mysql-node3 ~]# cat /data/mysql/mysql-node3-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44 (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-node3 ~]# cat /data/mysql/mysql-node3-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44 (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
# Time: 2024-08-23T06:35:26.657120Z
# User@Host: root[root] @ localhost [] Id: 13
# Query_time: 10.000508 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1724394926;
select sleep (10);

mysql的并行复制

#在slaves中设定
[root@mysql-node2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK                                   #基于组提交
slave-parallel-workers=16                                           #开启线程数量
master_info_repository=TABLE                       #master信息在表中记录，默认记录在/data/mysql//master.info
relay_log_info_repository=TABLE                       #回放日志信息在表中记录，默认记录在/data/mysql/relay-log.info
relay_log_recovery=ON                                               #日志回放恢复功能开启
rpl_semi_sync_master_enabled=1
gtid_mode=ON

[root@mysql-node2 ~]# /etc/init.d/mysqld restart

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

半同步模式

设置gtid

#在master端和slave端开启gtid模式
[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
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1

[root@mysql-node1 ~]# /etc/init.d/mysqld restart

[root@mysql-node2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2
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
rpl_semi_sync_master_enabled=1
gtid_mode=ON

[root@mysql-node2 ~]# /etc/init.d/mysqld restart

[root@mysql-node3 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=3
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON

[root@mysql-node3 ~]# /etc/init.d/mysqld restart

#停止slave端
[root@mysql-node2 ~]# mysql -p
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

[root@mysql-node3 ~]# mysql -p
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

#开启slave端的gtid
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.30', MASTER_USER='repl',MASTER_PASSWORD='wang', 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.30
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-node2-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 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: 1
Master_UUID: af937912-602f-11ef-953c-000c298b234d
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端配置启用半同步模式

[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
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1 #开启半同步功能

[root@mysql-node1 ~]# mysql -p
#安装半同步插件
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%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)
#打开半同步功能
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.01 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)

mysql> show plugins
+----------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows in set (0.01 sec)

在slave端开启半同步功能

[root@mysql-node2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2
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
rpl_semi_sync_master_enabled=1 #开启半同步功能
gtid_mode=ON

[root@mysql-node2 ~]# mysql -p

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;
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)

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
mysql> insert into wang.userlist values('user3','123');
Query OK, 1 row affected (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 | 1 |
| 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 | 1046 |
| Rpl_semi_sync_master_tx_wait_time | 1046 |
| 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.00 sec)

模拟故障：

#在slave端
[root@mysql-node2 ~]# mysql -p
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

[root@mysql-node3 ~]# mysql -p
mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#在master端插入数据
mysql> insert into wang.userlist values ('user4','444');
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 | 1 |
| 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 | 1046 |
| Rpl_semi_sync_master_tx_wait_time | 1046 |
| 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)

mysql高可用之组复制 (MGR)

MySQL Group Replication(简称 MGR )是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩
展的解决方案
组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务
MySQL 组复制分单主模式和多主模式，传统的mysql复制技术仅解决了数据同步的问题，
MGR 对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务
的顺序达成一致
提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定
如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，
这是一种内置的自动裂脑保护机制
MGR由组通信系统( Group Communication System ，GCS ) 协议支持
该系统提供故障检测机制、组成员服务以及安全且有序的消息传递

组复制流程

组复制单主和多主模式

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

Mysql route的部署方式

[root@mysql-node1 ~]# yum install mysql-router-community-8.4.0-1.el7.x86_64.rpm -y

#配置mysql-router
[root@mysql-node1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.30:3306,172.25.254.40:3306,172.25.254.50:3306
routing_strategy = round-robin

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

[root@mysql-node1 ~]# systemctl start mysqlrouter.service

测试:

#建立测试用户
mysql> create user wang@'%' identified by 'wang';
Query OK, 0 rows affected (10.01 sec)

mysql> grant all on wang.* to wang@'%';
Query OK, 0 rows affected (0.01 sec)

[root@mysql-node1 ~]# mysql -ulee -plee -h 172.25.254.30 -P 7001

高可用

[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
gtid_mode=ON
enforce-gtid-consistency=ON
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
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.30:33061"
group_replication_group_seeds="172.25.254.30:33061,172.25.254.40:33061,172.25.254.50: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

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| group_replication_applier | 0168b09c-61e9-11ef-8fee-000c293dbbbb | mysql-node2.timinglee.org | 3306 | RECOVERING |
| group_replication_applier | 40505320-61eb-11ef-b0b0-000c295d9a2b | mysql-node3.timinglee.org | 3306 | RECOVERING |
| group_replication_applier | 7df24cc0-6229-11ef-9910-000c298b234d | mysql-node1.timinglee.org | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
3 rows in set (0.00 sec)

测试：在每个节点都可以完成读写

#在mysql-node1中
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.02 sec)

mysql> SELECT * FROM wang.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
+----------+----------+
1 row in set (0.00 sec)

#在mysql-node2中

MHA部署实施

#在master节点中
[root@mysql-node10 ~]# /etc/init.d/mysqld stop
[root@mysql-node10 ~]# rm -fr /data/mysql/*
[root@mysql-node10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@mysql-node10 ~]# mysqld --user mysql --initialize
[root@mysql-node10 ~]# /etc/init.d/mysqld start
[root@mysql-node10 ~]# mysql_secure_installation
[root@mysql-node10 ~]# mysql -p
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'mysql';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

#在slave1和slave2中
[root@mysql-node2 & node3 ~]# /etc/init.d/mysqld stop
[root@mysql-node2 & node3 ~]# rm -fr /data/mysql/*
[root@mysql-node2 & node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2&3
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0

[root@mysql-node2 & node3 ~]# mysqld --user=mysql --initialize
[root@mysql-node2 & node3 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node2.timinglee.org.err'.
SUCCESS!
[root@mysql-node2 & node3 ~]# mysql_secure_installation

[root@mysql-node2 & node3 ~]# mysql -pmysql

mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.30', MASTER_USER='repl',MASTER_PASSWORD='wang', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
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.01 sec)

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

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

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

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