电商平台 lnmp 架构之 mysql 优化

编程入门行业动态更新时间:2024-10-09 11:22:25

电商平台 lnmp <a href=https://www.elefans.com/category/jswz/34/1771112.html style= 架构之 mysql 优化"/>

电商平台 lnmp 架构之 mysql 优化

- 1. Mysql 的主从复制
- 2. mysql 一主两从
- 3. 基于GTID(全局事务标识)的主从复制
- 4. 半同步复制
- 5. mysql异步复制
- 6. 延迟复制（SQL线程延迟）
- 7. 并行复制
- 8. mysql的全同步复制(组复制)
- 9. 读写分离
- 10. MHA高可用

1. Mysql 的主从复制

关于复制
在如今互联网环境中，复制一般分为传统的复制和大趋势下的组复制，而传统的复制又分为主从复制(异步复制)和半同步复制；
主从复制有原始slave通过Position复制master的二进制日志文件，也有通过GTID的方式进行全局复制，GTID较前者的好处在于出现某个mysql宕掉后，GTID模式会在全局中自动寻找下一个，而不用手动配置。
主从复制有一个比较致命问题，在生成二进制日志文件后，尤其在一些极端条件下，无法100%确认被slave端IO进程成功复制。
而半同步复制中的AFTER_COMMIT恰好解决了这一问题，在slave端IO复制后会返回ACK，但是还有一个问题，在该模式下，插入一个数据时，不论IO线程复制成功与否，在本地都会查询到该数据，这在金融行业是致命的。
半同步复制在AFTER_SYNC模式下会在IO线程返回ACK后才会把数据写道本地，当然也可以设置这个等待时间为无穷大，强制数据的一致性。
MySQL主从异步复制是最常见的复制场景。数据的完整性依赖于主库BINLOG的不丢失，只要主库的BINLOG不丢失，那么就算主库宕机了，我们还可以通过BINLOG把丢失的部分数据通过手工同步到从库上去。
传统的MySQL复制采用主从的方式进行，可以一主一从也可以一主多从，主库执行一个事务，提交后稍后异步的传送到从库中，如果是基于语句的复制则会重新执行，如果是基于行的负责则会应用日志，同时是shared-nothing的架构，即所有服务器拥有同样的数据复制
传统的数据主从复制均属于异步复制，从库起IO线程连接主库，获取主库二进制日志写到本地中继日志，并更新master-info文件(存放主库相关信息)，从库再利用SQL线程执行中继日志。
补充：为了保证Binlog的安全，MySQL引入sync_binlog参数来控制BINLOG刷新到磁盘的频率。
复制的原理
Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop table, grant 等等）。
主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。
复制过程
主节点必须启用二进制日志，记录任何修改了数据库数据的事件。
从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件。
主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置。
从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。
复制过程中的线程
从节点：
I/O Thread: 从 Master 节点请求二进制日志事件，并保存于中继日志中。
Sql Thread: 从Relay log 中读取日志事件并在本地完成重放。
主节点：
Dump Thread:为每个 Slave 的 I/O Thread 启动一个 dump 线程，用于向从节点发送二进制事件。

思考：从节点需要建立二进制日志文件吗？
看情况，如果从节点需要作为其他节点的主节点时，是需要开启二进制日志文件的。这种情况叫做级联复制。如果只是作为从节点，则不需要创建二进制文件。

配置
此处可以在server2上直接将之前server1编译的数据复制过去，也可以用rpm 包来安装。

此处选择用源码包复制：

[root@server1 mysql]# du -sh .
2.0G	.
[root@server1 mysql]# scp -r /usr/local/mysql/ root@172.25.25.2:/usr/local/
[root@server1 ~]# scp /etc/myf root@172.25.25.2:/etc/  
[root@server1 ~]# scp /etc/init.d/mysqld root@172.25.25.2:/etc/init.d/

将 server2 中的mysql 写入环境变量中，并且新建需要运行mysql 的用户，

[root@server2 bin]# pwd
/usr/local/mysql/bin
[root@server2 bin]# cd
[root@server2 ~]# vim .bash_profile 
[root@server2 ~]# source .bash_profile
[root@server2 ~]# tail -n3 .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/binexport PATH
[root@server2 ~]# useradd -M -d /usr/local/mysql/data/ -s /sbin/nologin mysql
[root@server2 ~]# id mysql
uid=1000(mysql) gid=1000(mysql) groups=1000(mysql)

然后清理之前的数据，并完成初始化；

[root@server2 ~]# cd /usr/local/mysql/data/
[root@server2 data]# rm -fr *
[root@server2 ~]# mysqld --initialize --user=mysql
#生成随机数密码，记住随机数密码，重复执行会报错
[root@server2 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/server2.err'.SUCCESS! 
# 启动mysql,并使用随机数密码修改mysql密码（切记不要重复启动，否则会报错）
[root@server2 ~]# mysql_secure_installation 
#执行安全初始化脚本,全部回车即可

此时server2 上的mysql 数据库就已经配置完成并且启动；此时启动server1 上的数据库，来保证两个数据库之间数据的同步；

以下的配置可以参考官网： .7/en/replication-howto-masterbaseconfig.html

主从复制(master&slave)

master 节点：
在mysql 8.0以后是默认支持主从复制的，在当前的版本中需要在配置文件中增添信息；

[root@server1 ~]# vim /etc/myf
[root@server1 ~]# cat /etc/myf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0server-id=1			#加入id(server_id值为0时，副本拒绝连接到源。)
log-bin=mysql-bin	##启动二进制日志
[root@server1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@server1 ~]# ls /usr/local/mysql/data/
autof         ibdata1           mysql-bin.index     server1.err
ca-key.pem       ib_logfile0       mysql.sock          server1.pid
ca.pem           ib_logfile1       mysql.sock.lock     server-cert.pem
client-cert.pem  ibtmp1            performance_schema  server-key.pem
client-key.pem   mysql             private_key.pem     sys
ib_buffer_pool   mysql-bin.000001  public_key.pem

mysql -p 创建用于复制的用户

[root@server1 ~]# mysql -pmysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'redhat';
##%代表除本机外的所有用户，方便测试
Query OK, 0 rows affected (0.01 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.01 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)

slave
在server2上添加id 信息并重启mysqld 重载配置文件；

[root@server2 ~]# vim /etc/myf
[root@server2 ~]# tail -n1 /etc/myf
server-id=2
[root@server2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS!

注： server_id：是必须的，而且唯一。

mysql -p 建立副本

[root@server2 ~]# mysql -p
mysql> CHANGE MASTER TO MASTER_HOST='172.25.25.1',
MASTER_USER='repl',
MASTER_PASSWORD='redhat',
MASTER_LOG_FILE='mysql-bin.000001',	#以server1上二进制日志文件为准
MASTER_LOG_POS=595;	#Position
Query OK, 0 rows affected, 2 warnings (0.04 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;	
#查看到Slave_IO_Running: yes Slave_SQL_Running: Yes
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.25.1Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 595Relay_Log_File: server2-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 595Relay_Log_Space: 529Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 18bc9556-c7a4-11eb-a8a3-525400759ebfMaster_Info_File: /usr/local/mysql/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 
1 row in set (0.00 sec)ERROR: 
No query specified

测试：在主节点中插入数据，在从节点中查看。
加入数据：

mysql> create database westos;
Query OK, 1 row affected (0.01 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)

在slave 中查看；

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

2. mysql 一主两从

上面做了mysql 的主从复制，接下来对其更进一步的扩展，实现一主两从。

下来的操作主要是针对mysql ，可以先将nginx和php关闭，节省资源消耗；

[root@server1 ~]# systemctl stop nginx
[root@server1 ~]# systemctl stop php-fpm
[root@server1 ~]# systemctl disable php-fpm
Removed symlink /etc/systemd/system/multi-user.target.wants/php-fpm.service.
[root@server1 ~]# systemctl disable nginx
Removed symlink /etc/systemd/system/multi-user.target.wants/nginx.service.

先在server3上配置数据库，此时可以和上面创建server2时一样，将已经存在的数据库复制一份；

[root@server2 local]# scp -r mysql/ root@172.25.25.3:/usr/local/
[root@server2 local]# scp /etc/myf root@172.25.25.3:/etc/
[root@server2 local]# scp /etc/init.d/mysqld root@172.25.25.3:/etc/init.d/[root@server3 ~]# vim /etc/myf
[root@server3 ~]# tail -n1 /etc/myf
server-id=3
[root@server3 data]# pwd 
/usr/local/mysql/data
[root@server3 data]# rm -fr *
[root@server3 ~]# useradd -M  -d /usr/local/mysql/ -s /sbin/nologin mysql
[root@server3 ~]# id mysql
uid=1000(mysql) gid=1000(mysql) groups=1000(mysql)
[root@server3 ~]# mysqld --initialize --user=mysql
[root@server3 ~]# /etc/init.d/mysqld start
[root@server3 ~]# mysql_secure_installation

此时，新加的数据库和之前的数据库信息并不同步；需要将其同步；

[root@server1 ~]# mysql -pType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.01 sec)mysql> use westos;
Database changed
mysql> show tables;
Empty set (0.00 sec)mysql> create table user_tb (-> username varchar(25) not null,-> password varchar(50) not null);
Query OK, 0 rows affected (0.03 sec)mysql> insert into user_tb values ('user','1111');
Query OK, 1 row affected (0.01 sec)mysql> insert into user_tb values ('user2','222');
Query OK, 1 row affected (0.01 sec)

此时由于server2 是server1的从库，会同步信息；但是server3是新加入的并不是从库，此时并不会同步数据；需要做备份。

[root@server1 ~]# mysqldump -uroot -pwestos westos > dump.db
#将 westos的库信息，导出做备份。
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@server1 ~]# scp dump.db root@172.25.25.3:~[root@server3 ~]# cat dump.db | grep DROP
DROP TABLE IF EXISTS `user_tb`;
[root@server3 ~]# mysqladmin create westos -pwestos
# 创库
[root@server3 ~]# mysql -pwestos westos < dump.db 
#将备份数据导入库中
[root@server3 ~]# mysql -p
Enter password: mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user     | 1111     |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

导入备份数据之后，此时登陆数据库查看其库的信息。

注：在生产环境中一定要注意用mysqldump 的方式是先删除在复制的过程，而且生产环境中备份数据库时可能要锁表。

此时server1是主数据库，server2为从数据库，在此基础上加入server3。
有两种方案：
1）server2和3都作为server1的从库，但是会加大server1的负载；
2）将server3作为server2的从数据库，因此server2既是从库也是主库。以达到宏观上的负载均衡。

此处选择第二种方法，将server3作为 server2的从库；来缓解主库的压力。

先在 server2上修改配置信息；

[root@server2 ~]# vim /etc/myf
[root@server2 ~]# cat /etc/myf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2
lob-slave-updates	#将修改操作写入二进制日志中
log-bin=mysql-bin	#启用二进制日志
[root@server2 ~]# /etc/init.d/mysqld restart
[root@server2 ~]# ls /usr/local/mysql/data/
autof         ib_logfile1       performance_schema        server2-relay-bin.index
ca-key.pem       ibtmp1            private_key.pem           server-cert.pem
ca.pem           master.info       public_key.pem            server-key.pem
client-cert.pem  mysql             relay-log.info            sys
client-key.pem   mysql-bin.000001  server2.err               westos
ib_buffer_pool   mysql-bin.index   server2.pid
ibdata1          mysql.sock        server2-relay-bin.000009
ib_logfile0      mysql.sock.lock   server2-relay-bin.000010

重启之后，此时server2-relay-bin.000009和 server2-relay-bin.0000010是从 IO 读取的server1 的二进制信息，mysql-bin.000001是生成的二进制日志，供server3读取。

对从库进行授权；

[root@server2 ~]# mysql -pmysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'redhat';
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)

在 server3上验证刚刚创建的用户是否可以成功登陆；
验证结束，要退出。

[root@server3 ~]# mysql -h 172.25.25.2 -urepl -predhat

验证完成之后并且退出之后，此时登陆自己的数据库；

[root@server3 ~]# mysql -pmysql> CHANGE MASTER TO MASTER_HOST='172.25.25.2',MASTER_USER='repl',MASTER_PASSWORD='redhat',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=595;
Query OK, 0 rows affected, 2 warnings (0.04 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 eventMaster_Host: 172.25.25.2Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 595Relay_Log_File: server3-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes

注：此时如果Slave_IO_Running 是no 要检查用户是否能登陆；Slave_SQL_Running 是no 要检查数据是否一致。

测试：在server1中写入数据，在server3上查看；

mysql> USE westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> insert into user_tb values ('user3','333');
Query OK, 1 row affected (0.01 sec)mysql> insert into user_tb values ('user4','444');
Query OK, 1 row affected (0.01 sec)

在 server3 中查看到的效果如下所示：

mysql> SELECT * FROM westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user     | 1111     |
| user2    | 222      |
| user3    | 333      |
| user4    | 444      |
+----------+----------+
4 rows in set (0.00 sec)

每次重启，master的号和二进制日志都会变化，但是 slave 不会改变，master 变化并不会对slave有影响。
在生产环境中，不会多次重启，当遇到日志文件太大时，会新建一个；要求日志不要总是在同一个日志里写。

当前架构是多从，当 master 挂了，会将 slave1作为新的主这样；此时就必须知道slave2上的二进制日志和号。
此时就需要手动去做就比较麻烦，可以用gtid 来做。

3. 基于GTID(全局事务标识)的主从复制

每个主从都有个下一跳，全局识别ID，只关心 next 就可以，下一跳；

由于以上主从复制，在其中一个宕机后，要保证数据的一致性难度较大，因此一般使用GTID模式。像这样一主多从的架构下，出现宕机时，不用再手动设置，GTID模式在全局中自动寻找下一跳。

配置 GTID
在所有节点上配置/etc/myf 增加以下内容，开启gtid服务;

[root@server1 ~]# vim /etc/myf
[root@server1 ~]# cat /etc/myf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
[root@server1 ~]# /etc/init.d/mysqld restart[root@server2 ~]# vim /etc/myf
[root@server2 ~]# /etc/init.d/mysqld restart

此时当 slave 端也重启之后，便可以重新读取信息；此时在配置文件中已经家里 gtid的模式，此时设置主的时候便不用指定从那开始读那个文件，会自动读取；

[root@server2 ~]# mysql -pwestosmysql> STOP SLAVE;	##配置从站slave使用基于GTID的自动定位。mysql> CHANGE MASTER TO MASTER_HOST='172.25.25.1',MASTER_USER='repl',MASTER_PASSWORD='redhat',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;Retrieved_Gtid_Set: 18bc9556-c7a4-11eb-a8a3-525400759ebf:1-2Executed_Gtid_Set: 18bc9556-c7a4-11eb-a8a3-525400759ebf:1-2Auto_Position: 1

依次类推将server3 上的文件信息，以及数据库的slave 信息做更改；
测试：在 server1 上的主 master 上新建数据信息；在server3上查看，数据已经同步，并且Gtid 信息也已经更新。

4. 半同步复制

在当前配置的5.7 版本中默认用的是 sync 的模式；
MySQL也提供了一个半同步复制，其要求主库在commit时等待从库接受完事务并返回确认信息后才能提交。半同步复制是建立在基本的主从复制基础上，利用插件完成半同步复制，传统的主从复制，不管从库是否正确获取到二进制日志，主库不断更新，半同步复制则当确认了从库把二进制日志写入中继日志才会允许提交，如果从库迟迟不返回ack,主库会自动将半同步复制状态取消，进入最基本的主从复制模式。
半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的BINLOG日志上，另一份在至少一个从库的中继日志Relay Log上，从而更进一步保证了数据的完整性。
半同步复制模式下，假如在传送BINLOG日志到从库时，从库宕机或者网络延迟，导致BINLOG并没有即使地传送到从库上，此时主库上的事务会等待一段时间(时间长短由参数rpl_semi_sync_master_timeout设置的毫秒数决定)，如果BINLOG在这段时间内都无法成功发送到从库上，则MySQL自动调整复制为异步模式，事务正常返回提交结果给客户端。
半同步复制很大程度上取决于主从库之间的网络情况，往返时延RTT越小决定了从库的实时性越好。通俗地说，主从库之间的网络越快，从库约实时。

安装插件
在 master 所在的主机安装插件rpl_semi_sync_master；在slave 所在主机上安装插件rpl_semi_sync_slave；并且激活插件，开启半同步复制。

master 所在主机中：

[root@server1 ~]# mysql -p
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> set global rpl_semi_sync_master_enabled = 1;mysql> show variables like 'rpl%'; 	#环境变量
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |	#这个时间是10s，金融行业是无穷大。
| 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 |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
7 rows in set (0.00 sec)mysql> show status like 'rpl%';		#状态变量
+--------------------------------------------+-------+
| 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@server2 ~]# mysql -p
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_master_enabled = 1;
mysql>  set global rpl_semi_sync_slave_enabled = 1;
#此时查看到的slave 的状态是ON;需要重启依次IO线程；
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;
mysql> Show status like '%rpl%';
+--------------------------------------------+-------+
| 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     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)在server3上：
```php
[root@server3 ~]# mysql -p
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;
mysql> Show status like '%rpl%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

测试：
在 server1 所在的 master 输入数据；

[root@server1 ~]# mysql -p
mysql> use westos
mysql> insert into user_tb values ('user7','777');[root@server3 ~]# mysql -p
mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user     | 1111     |
| user2    | 222      |
| user3    | 333      |
| user4    | 444      |
| user5    | 555      |
| user6    | 666      |
| user7    | 777      |
+----------+----------+

模拟故障，同步失败，将 server2的IO线程停掉，此时在server1所在master 主机中插入数据，会发现数据不能到达，master端会默认等待10s，返回确认信息，在master端可以查询到数据，但并没有同步到slave端，这个时间延迟可以设置，所以在金融行业就要设置为无穷大。当开启之后，数据就又继续传输。

5. mysql异步复制

更快一些，matser 发送二进制日志给 slave，master只负责发送，不确保slave是否收到。

6. 延迟复制（SQL线程延迟）

在生产环境当中，如果在master端误操作了数据库，在slave端通过设置sql线程延迟复制，其好处在于只是IO线程读取了二进制日志文件，但在延迟时间内并没有回放二进制日志记录文件，因此在一定程度上有了容灾能力。

[root@server2 ~]# mysql -pmysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_DELAY =30;	#sql从IO读取复制的二进制日志延迟30s
Query OK, 0 rows affected (0.00 sec)mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

在测试时，当我们写入数据时，slave端会在30s之后才执行；
此时在 slave 端查看 slave 信息时，可以看到延迟信息，是时时更新的；当时间到了之后，才会复制到 slave 端；

mysql> show slave status\G;Seconds_Behind_Master: 17Retrieved_Gtid_Set: 18bc9556-c7a4-11eb-a8a3-525400759ebf:1-4Executed_Gtid_Set: 18bc9556-c7a4-11eb-a8a3-525400759ebf:1-3

7. 并行复制

开启 sql 线程并行复制，在 slave 端开启16线程进行 sql 回放，可以提高80%的性能。原先是一线程，如果启用并行复制，值不要设置为1 ；启用并行复制，原先的线程会退化为协调线程，后端是一个work，中间做了调度，性能会有损耗，不如之前。

把半同步的参数写到主配置文件里，让它重启后自动生效；

[root@server2 ~]# cat /etc/myf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2
log-slave-updates
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ONrpl_semi_sync_master_enabled=1		#以下缩进为上面的半同步复制写到配置文件中
rpl_semi_sync_slave_enabled=1slave-parallel-type=LOGICAL_CLOCK	#以下缩进为sql并行复制配置
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@server2 ~]# /etc/init.d/mysqld restart

重启之后确保数据库的主从是完整的；

[root@server2 ~]# mysql -p
mysql> show slave status\G;
mysql> show variables like 'slave_%';
+------------------------------+-----------------------+
| Variable_name                | Value                 |
+------------------------------+-----------------------+
| slave_allow_batching         | OFF                   |
| slave_checkpoint_group       | 512                   |
| slave_checkpoint_period      | 300                   |
| slave_compressed_protocol    | OFF                   |
| slave_exec_mode              | STRICT                |
| slave_load_tmpdir            | /tmp                  |
| slave_max_allowed_packet     | 1073741824            |
| slave_net_timeout            | 60                    |
| slave_parallel_type          | LOGICAL_CLOCK         |
| slave_parallel_workers       | 16       #数字变为16   |
| slave_pending_jobs_size_max  | 16777216              |
| slave_preserve_commit_order  | OFF                   |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors            | OFF                   |
| slave_sql_verify_checksum    | ON                    |
| slave_transaction_retries    | 10                    |
| slave_type_conversions       |                       |
+------------------------------+-----------------------+
17 rows in set (0.01 sec)
mysql> show variables like '%info%';	##变为TABLE
+--------------------------------+----------------+
| Variable_name                  | Value          |
+--------------------------------+----------------+
| master_info_repository         | TABLE          |
| relay_log_info_file            | relay-log.info |
| relay_log_info_repository      | TABLE          |
| session_track_transaction_info | OFF            |
| sync_master_info               | 10000          |
| sync_relay_log_info            | 10000          |
+--------------------------------+----------------+
6 rows in set (0.01 sec)

8. mysql的全同步复制(组复制)

组复制是一种可以用来部署容错系统的技术，复制组中的服务器通过massage passing来进行交互，通信层通过atomic message 和 total order message delivery来保证组内成员数据的一致性，所有的读-写(RW)操作需要组内所有成员都通过才可提交，只读(RO)事务不需要这个过程。

复制组是一个通过消息传递相互交互的 server 集群。复制组由多个server 成员组成，如上图的master1，master2，master3，所有成员独立完成各自的事务。当客户端先发起一个更新事务，该事务先在本地执行，执行完成之后就要发起对事务的提交操作。在还没有真正提交之前需要将产生的复制广播出去，复制到其他成员。如果冲突检测成功，组内决定该事务可以提交，其他成员可以应用，否则就回滚。最终，这意味着所有组内成员以相同的顺序接收同一组事务。因此组内成员以相同的顺序应用相同的修改，保证组内数据强一致性。

组复制的过程
当事务在原始服务器上要求提交后，服务器会自动广播写值(row changed)以及相应的写集( unique identifiers of the rows)，然后一个全局的总的顺序(global total order )为该事务建立,这意味着所有的服务器按照顺序接受到了该事务，然后所有服务器按照相同的顺序应用该事务。
MGR提供一种叫做certification的步骤来解决冲突的问题，当不同服务器同时更新一行，此时则会发生冲突，这时MGR会承认第一个提交的事务，剩下的会被回滚，这也叫做distributed first commit wins 规则。
新成员加入组的简单流程
1)当有新的成员加入组中，组内原有的成员会在二进制日志中插入一个视图切换的事件；
2)在组成员内找到一个donor捐赠之前缺失的数据，如果这个donor突然下线了，新成员会从新的donor获取缺失的数据，这时候组还在不断更新，新成员会将新的事件写到内存的一个临时空间；
3)当获取到视图切换事件的时候，新成员将开始执行保存到内存临时空间的事件。
组复制模式
组复制可以在两种模式下运行。
1)在单主模式下，组复制具有自动选主功能，每次只有一个 server成员接受更新。
2)在多主模式下，所有的 server 成员都可以同时接受更新。
组复制使用场景
MGR可以让你在组内不是全部或者大多数服务器失效时都可以保证数据库服务的可用，MGR利用一个依赖分布式失败检测器(distributed failure detector)的组成员关系服务(group membership service)来跟踪组内成员的离开(资源的离开或者是意外的离开)。
MGR有个分布式恢复程序(distributed recovery procedure)来确保每当有服务器加入组后数据库保持最新，从而使得我们不需要做fail-over，多主架构还可以保证主库宕机时不会阻塞更新，MGR保证数据库服务持续可用。
最后需要理解的是虽然MGR可以保证数据库服务器的可用性，但是客户端的连接还是需要重新定向到另外的服务器的。想要达到这个目的，可以考虑MySQL Router。

如下为一些可能需要MGR的场景

Elastic Replication - 一个非常流式复制的架构，服务器需要尽可能没有影响的动态的增长和减少，典型的如云上数据库服务；
Highly Available Shards - 分片是一个写扩展的功能实现，使用MGR来讲每个分片映射到不同的复制组中；
Alternative to Master-Slave replication -MGR可以作为传统主从切换的一个升级以获得更好的可用性；
Autonomic Systems - 最后你可以全新部署MGR来实现复制自动管理。

配置过程：
为保证实验环境纯净，将所有的数据库停掉，然后将 server1-3 的data 目录删除，重新配置初始化，初始化完成之后启动数据库，然后修改配置信息，然后再重启数据库，让新加入的参数生效。

[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# rm -fr *
[root@server1 ~]# cat /etc/myf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
[root@server1 ~]# mysqld --initialize --user=mysql
[root@server1 ~]# /etc/init.d/mysqld start
[root@server1 ~]# cat /etc/myf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0disabled_storage_engines=“MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
server_id=1
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=ROWplugin_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.25.1:33061" 
group_replication_group_seeds="172.25.25.1:33061,172.25.25.2:33061,172.25.25.2:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.25.0/24,127.0.0.1/8" ##ip
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
[root@server1 ~]# /etc/init.d/mysqld restart

根据初始化生成的密码在数据库中更改密码，做配置更改；

[root@server1 ~]# mysql -p	#此时登陆进去不改密码什么都做不了mysql> ALTER USER root@localhost identified by 'westos';
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
mysql>  GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
##以上的引导文件过程只在第一台主机中做。mysql> SELECT * FROM performance_schema.replication_group_members; 
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 9bc168d7-d68a-11eb-ba74-525400759ebf | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

在server2 和server3 上做如下的操作：删除数据，初始化，启动数据库，该配置文件，重新启动数据库，在数据中修改密码，做配置。

[root@server2 ~]# cd /usr/local/mysql/data/
[root@server2 data]# rm -fr *
[root@server2 ~]# cat /etc/myf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
[root@server2 ~]# mysqld --initialize --user=mysql
[root@server2 ~]# cat /etc/myf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0disabled_storage_engines=“MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
server_id=2
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=ROWplugin_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.25.2:33061" 
group_replication_group_seeds="172.25.25.1:33061,172.25.25.2:33061,172.25.25.2:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.25.0/24,127.0.0.1/8" ##ip
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON
[root@server2 ~]# /etc/init.d/mysqld start
[root@server2 ~]# mysql -pmysql> ALTER USER root@localhost identified by 'westos';
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
mysql>  GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;		#加入集群

当第二个启动之后，在server1 上的数据库中可以查看到信息：

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 71130754-d68d-11eb-8f53-525400863db7 | server2     |        3306 | ONLINE       |
| group_replication_applier | 9bc168d7-d68a-11eb-ba74-525400759ebf | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

然后第三个节点依次类推。
当这个过程中有什么问题时需要看日志。

测试:
在server1上，创建数据，

mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY ,c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1,'Luis');
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | NO   | PRI | NULL    |       |
| c2    | text    | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)

然后在集群中的其他节点上可以处看到新建的信息，也可以继续写入数据；如在server2上如下操作：

mysql> USE test;
mysql> INSERT INTO t1 VALUES (2,'zxk');
mysql>  select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | zxk  |
+----+------+
2 rows 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 | /usr/local/mysql/data/server1-slow.log |
+---------------------+----------------------------------------+
3 rows in set (0.00 sec)
mysql> set global slow_query_log=ON;	#直接开启
mysql> show variables like "long%";		#默认是10s
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

9. 读写分离

mysql 路由配置通过路由来控制读写分离。
在额外一台主机上配置路由；安装软件，然后修改配置文件rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm；
配置文件：
如果是一主两从，那么目的地只能写相应的master，如果是组复制，就不需要。当前环境是组环境；

[root@server4 ~]# cd /etc/mysqlrouter/
[root@server4 mysqlrouter]# vim mysqlrouter.conf 
[root@server4 mysqlrouter]# tail -n11 mysqlrouter.conf
[routing:ro]	#只读
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.25.1:3306,172.25.25.2:3306,172.25.25.3:3306
routing_strategy = round-robin	#轮询[routing:rw]	#读写
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.25.1:3306,172.25.25.2:3306,172.25.25.3:3306
routing_strategy = first-available	#第一个可用
[root@server4 mysqlrouter]# systemctl start mysqlrouter
#启动mysqlroute
[root@server4 mysqlrouter]# netstat -antlp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      3192/sshd           
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      3534/mysqlrouter    
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      3364/master         
tcp        0      0 0.0.0.0:7002            0.0.0.0:*               LISTEN      3534/mysqlrouter

由于超级用户在初始化时禁用了远程用户的登陆，此处新建用户来做测试，并对不同的用户授予不同的权限，然后测试；

mysql> GRANT SELECT ON *.* TO user1@'%' IDENTIFIED BY 'westos';
#对user1 用户授予所有库的查看权限
mysql> GRANT ALL ON test.* TO user2@'%' IDENTIFIED BY 'westos';
#对user2用户授予test库的所哟权限
mysql> FLUSH PRIVILEGES;	#刷新授权表

测试：
注：此处如果执行时测试不了，需要安装 mariadb客户端。

访问的是7001端口，会根据配置文件中的信息，只能做查询动作；并且每次连接数据库是轮询的方式；

[root@westos ~]# mysql -h 172.25.25.4 -P 7001 -u user1 -pwestos
MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.001 sec)MySQL [(none)]> SELECT * FROM test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | zxk  |
+----+------+
2 rows in set (0.001 sec)

访问的是7002端口，会根据配置文件中的信息，只能对test库做任何动作；并且每次连接数据库是默认的第一个；但是对后端是有健康检查的，当宕机之后，会默认顺延。

[root@westos ~]# mysql -h 172.25.25.4 -P 7002 -u user2 -pwestos
MySQL [(none)]> use test;
MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.001 sec)MySQL [test]> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | zxk  |
+----+------+
2 rows in set (0.001 sec)
MySQL [test]> insert into t1 values (3,'kkk');
Query OK, 1 row affected (0.009 sec)MySQL [test]> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | zxk  |
|  3 | kkk  |
+----+------+
3 rows in set (0.001 sec)

可以在对应的数据库组群所在主机中通过端口查看是谁连接的，可以下载 lsof用命令 lsof -i ：3306查看数据来源；也可以用端口netstat -antulp来查看。

[root@server1 ~]# yum install -y lsof
[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  3887 mysql   13u  IPv6  34256      0t0  TCP *:mysql (LISTEN)
mysqld  3887 mysql   88u  IPv6  28463      0t0  TCP server1:mysql->server4:39456 (ESTABLISHED)

10. MHA高可用

对于mysql 的高可用，经典的类型是一主两从，性能非常好；但是很耗费资源，所以企业中对于如果要用一主一从，就需要自研。

此处用一主两从的架构来构建高可用平台；在做之前需要将之前的集群关闭，从新初始化为主从结构。
将三台数据库主机上的数据库停掉，删除之前的数据，从新初始化；

[root@server1 ~]# /etc/init.d/mysqld stop
[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# rm -fr *
[root@server1 ~]# cat /etc/myf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
[root@server1 ~]# mysqld --initialize --user=mysql
[root@server1 ~]# /etc/init.d/mysqld start
[root@server1 ~]# mysql_secure_installation
[root@server1 ~]# mysql -p
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |      710 |              |                  | ba0f955a-d70c-11eb-9a4d-525400759ebf:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql>  grant replication slave on *.* to repl@'%' identified by 'westos';
#新建用户并授权

server1 上的主开启之后，然后依次配置server2 和server3 上的从库；

[root@server2 ~]#  cd /usr/local/mysql/data/
[root@server2 data]# rm -fr *
[root@server2 data]# vim /etc/myf
[root@server2 data]# mysqld --initialize --user=mysql
[root@server2 data]# /etc/init.d/mysqld start
[root@server2 data]# mysql_secure_installation
[root@server2 data]# mysql -p
mysql> CHANGE MASTER TO MASTER_HOST='172.25.25.1',MASTER_USER='repl',MASTER_PASSWORD='westos',MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

再将server3上的和server2上的操作一样执行。

然后在 server4 上，安装配置MHA高可以管理节点。
在MHA官方下载rpm格式的程序包，直接安装：

[root@server4 MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz                   perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm     perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm          perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm  perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm       perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@server4 MHA-7]# yum install -y *.rpm

在数据库结点上安装yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm即可；

完成之后，做免密认证：先做管理端到数据库端的免密认证，然后在主从节点间做数据库之间的免密；

[root@server4 ~]# ssh-keygen
[root@server4 ~]# ssh-copy-id server1
[root@server4 ~]# ssh-copy-id server2
[root@server4 ~]# ssh-copy-id server3
[root@server4 ~]# scp -r .ssh/ server1:
[root@server4 ~]# scp -r .ssh/ server2:
[root@server4 ~]# scp -r .ssh/ server3:

无论在1，2，3，4哪台主机上，都应该是免密连接的。

在管理节点需要配置其响应的信息；

[root@server4 MHA-7]# masterha_check_ssh --help
Usage:masterha_check_ssh --global_conf=/etc/masterha_defaultf--conf=/etc/conf/masterha/app1fSee online reference() for details.
[root@server4 MHA-7]# mkdir /etc/masterha
[root@server4 MHA-7]# cd /etc/masterha/[root@server4 MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@server4 conf]# pwd
/root/MHA-7/mha4mysql-manager-0.58/samples/conf
[root@server4 conf]# cat  masterha_defaultf app1f > /etc/masterha/appf
[root@server4 conf]# cd /etc/masterha/
[root@server4 masterha]# vim appf 
[root@server4 masterha]# cat appf
[server default]
user=root		#mysql主从节点的管理员用户密码，确保可以从远程登陆
password=westos
ssh_user=root	#ssh用户名
repl_user=repl	#主从复制用户密码
repl_password=westos
master_binlog_dir= /usr/local/mysql/data	#mysql主服务器的binlog目录
remote_workdir=/tmp		#远端mysql在发生切换时binlog的保存位置
secondary_check_script= masterha_secondary_check -s 172.25.25.1 -s 172.25.25.2
ping_interval=3	
#发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行failover
# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change
manager_workdir=/etc/masterha/app1	#manager工作目录
manager_log=/etc/masterha/app1/manager.log	#manager日志文件[server1]
hostname=172.25.25.1[server2]
hostname=172.25.25.2
candidate_master=1
#指定failover时此slave会接管master，即使数据不是最新的。
[server3]
hostname=172.25.25.3
no_master=1	#始终是slave

完成之后需要用命令在管理节点来测试主从是否都已经完整:masterha_check_repl --conf=/etc/masterha/appf和 masterha_check_repl --conf=/etc/mha/appf ；当没有成功时根据报错信息修改内容，直至成功。
此处的错误在于之前在主节点初始化数据库时没有允许远程登陆：通过在数据库中授权。

mysql> grant all on *.* to root@'%' identified by 'westos';
mysql>  flush privileges;

Manager工具包主要包括以下几个工具：

工具包	含义
masterha_check_ssh	检查MHA的SSH配置状况
masterha_check_repl	检查MySQL复制状况
masterha_manger	启动MHA
masterha_check_status	检测当前MHA运行状态
masterha_master_monitor	检测master是否宕机
masterha_master_switch	控制故障转移（自动或者手动）
masterha_conf_host	添加或删除配置的server信息

Node工具包（由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：

工具包	含义
save_binary_logs	保存和复制master的二进制日志
apply_diff_relay_logs	识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog	去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
purge_relay_logs	清除中继日志（不会阻塞SQL线程）

MHA的故障切换过程，共包括以下的步骤：
1.配置文件检查阶段，这个阶段会检查整个集群配置文件配置；
2.宕机的master处理，这个阶段包括虚拟ip摘除操作，主机关机操作；
3.复制dead maste和最新slave相差的relay log，并保存到MHA Manger具体的目录下；
4.识别含有最新更新的slave；
5.应用从master保存的二进制日志事件（binlog events）；
6.提升一个slave为新的master进行复制；
7.使其他的slave连接新的master进行复制；
MHA在线切换的大概过程：
1.检测复制设置和确定当前主服务器
2.确定新的主服务器
3.阻塞写入到当前主服务器
4.等待所有从服务器赶上复制
5.授予写入到新的主服务器
6.重新设置从服务器
为了保证数据完全一致性，在最快的时间内完成切换，MHA的在线切换必须满足以下条件才会切换成功，否则会切换失败。
1.所有slave的IO线程都在运行
2.所有slave的SQL线程都在运行
3.所有的show slave status的输出中Seconds_Behind_Master参数小于或者等于running_updates_limit秒，如果在切换过程中不指定running_updates_limit,那么默认情况下running_updates_limit为1秒。
4.在master端，通过show processlist输出，没有一个更新花费的时间大于running_updates_limit秒。

手动切换：当前server1时master ，此时手动切换，将master 切换到server2；

[root@server4 ~]# masterha_master_switch --conf=/etc/masterha/appf --master_state=alive --new_master_host=172.25.25.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000From:
172.25.25.1(172.25.25.1:3306) (current master)+--172.25.25.2(172.25.25.2:3306)+--172.25.25.3(172.25.25.3:3306)To:
172.25.25.2(172.25.25.2:3306) (new master)+--172.25.25.3(172.25.25.3:3306)+--172.25.25.1(172.25.25.1:3306)Starting master switch from 172.25.25.1(172.25.25.1:3306) to 172.25.25.2(172.25.25.2:3306)? (yes/NO): yesSun Jun 27 15:23:18 2021 - [info] Switching master to 172.25.25.2(172.25.25.2:3306) completed successfully.

在切换的过程中会有一些提示信息，如当前的 master 是那个，slave 是那个，以及切换完成之后，会提示切换成功。
此时在登陆数据库server1时，便可看到其已经成为 slave 。

此时server2已经是 master，如果此时server2宕机，数据库关闭；然后手动在切换到 server1；

[root@server4 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/appf --dead_master_host=172.25.25.2 --dead_master_port=3306 --new_master_host=172.25.25.1 --new_master_port=3306 --ignore_last_failoverSelected 172.25.25.1(172.25.25.1:3306) as a new master.
172.25.25.1(172.25.25.1:3306): OK: Applying all logs succeeded.
172.25.25.3(172.25.25.3:3306): OK: Slave started, replicating from 172.25.25.1(172.25.25.1:3306)
172.25.25.1(172.25.25.1:3306): Resetting slave info succeeded.
Master failover to 172.25.25.1(172.25.25.1:3306) completed successfully.

切换之后会在 /etc/masterha/app1生成文件app.failoverplete,当有这个文件存在时，在做切换时如果不加参数--ignore_last_failover会失败。
因为在生产环境中会限制数据库频繁切换；当需要切换时就需要加参数，或者将此文件删除，然后在做切换。

此时即使server2 重新启动，但是也不会自动加入集群中去，需要手动设定。CHANGE MASTER TO MASTER_HOST='172.25.25.1',MASTER_USER='repl',MASTER_PASSWORD='westos',MASTER_AUTO_POSITION=1;.

自动切换
此时还是server1 是 master ；server2和3 是slave；
在做之前，先检测一下，文件和数据库是否完整。masterha_check_repl --conf=/etc/masterha/appf masterha_check_ssh --conf=/etc/masterha/appf.

将其运行在后台，当检测到有退出时，会自动切换；

[root@server4 ~]# masterha_manager --conf=/etc/masterha/appf &
[root@server4 ~]# ps ax4342 pts/0    S      0:00 perl /usr/bin/masterha_manager --conf=/etc/masterha/appf
[root@server4 app1]# pwd
/etc/masterha/app1
[root@server4 app1]# rm -fr app.failoverplete

然后让server1 宕机/etc/init.d/mysqld stop ，由于文件在后台运行，每隔3s检测一下，当检测不到时，会根据文件的信息，让备选的 server2 上线。上线之后，此时会再次生成文件app.failoverplete 。

在让 server1 上线，手工恢复 slave 状态。

利用脚本切换
从解压的里面复制模板，然后做修改。让 vip 随着更改。

[root@server4 scripts]# pwd
/root/MHA-7/mha4mysql-manager-0.58/samples/scripts
[root@server4 scripts]# ls
master_ip_failover  master_ip_online_change  power_manager  send_report
[root@server4 bin]# chmod +x master_ip_failover master_ip_online_change
[root@server4 bin]# ll
total 8
-rwxr-xr-x 1 root root 2156 Jun 27 16:12 master_ip_failover
-rwxr-xr-x 1 root root 3813 Jun 27 16:12 master_ip_online_change
[root@server4 bin]# vim master_ip_online_change#!/usr/bin/env perl
use strict;
use warnings FATAL =>'all';use Getopt::Long;my $vip = '172.25.25.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
my $exit_code = 0;
...
[root@server4 bin]# vim master_ip_failover#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;my ($command,          $ssh_user,        $orig_master_host, $orig_master_ip,$orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
);my $vip = '172.25.25.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";[root@server4 masterha]# vim appf [server default]
user=root
password=westos
ssh_user=root
repl_user=repl
repl_password=westos
master_binlog_dir= /usr/local/mysql/data
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.25.1 -s 172.25.25.2
ping_interval=3
master_ip_failover_script= /usr/local/bin/master_ip_failover	#确保路径一定要正确
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /usr/local/bin/master_ip_online_change	
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log

改完之后检测一下；masterha_check_repl --conf=/etc/masterha/appf
给当前master 所在主机加上vip

[root@server2 ~]# ip addr add 172.25.25.100/24 dev eth0
[root@server2 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 52:54:00:86:3d:b7 brd ff:ff:ff:ff:ff:ffinet 172.25.25.2/24 brd 172.25.25.255 scope global eth0valid_lft forever preferred_lft foreverinet 172.25.25.100/24 scope global secondary eth0valid_lft forever preferred_lft foreverinet6 fe80::5054:ff:fe86:3db7/64 scope link valid_lft forever preferred_lft forever

测试：

[root@westos ~]# mysql -h 172.25.25.100 -u root -pwestos
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.001 sec)

手工切换，来将master 切换到 server1上；切换之后，此时vip 就在server1上。

[root@server4 masterha]# masterha_master_switch --conf=/etc/masterha/appf --master_state=alive --new_master_host=172.25.25.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

自动切换来将 master 再切换到 server2 上；将文件打入后台，然后让master 所在主机宕机，让其自动切换。此时vip 会随着 master 漂移。

[root@server4 masterha]# masterha_manager --conf=/etc/masterha/appf &[root@server1 ~]# /etc/init.d/mysqld stop

手动让server再次上线。
以上便是mysql的高可用，一主两从，一个从为备选master，一个始终不做master ；有两种切换的方式，手工切换和自动切换。

更多推荐

电商平台 lnmp 架构之 mysql 优化

本文发布于:2024-03-12 23:53:49，感谢您对本站的认可！

本文链接:https://www.elefans.com/category/jswz/34/1732715.html

架构平台 lnmp mysql

上一篇：知识图谱里的知识存储：neo4j的介绍和使用
下一篇：在竞争白热化的电商行业，链动2+1为什么还有企业在用

发布评论取消回复

评论列表（有 0 条评论）

电商平台 lnmp 架构之 mysql 优化