mysql多主模式配置、慢查询、路由器、MHA高可用、主从切换

一、多主模式配置

文档：MySQL :: MySQL 5.7 Reference Manual :: 17.2.1.2 Configuring an Instance for Group Replication

先清理数据库

[root@server1 ~]# cd /data/mysql/
[root@server1 mysql]# rm -fr *

 server1中配置

[root@server1 mysql]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid

disabled_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=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= "server1:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.6.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

初始化数据库，重新启动

[root@server1 mysql]# mysqld --initialize --user=mysql
[root@server1 mysql]# /etc/init.d/mysqld start
[root@server1 mysql]# cat mysqld.log 
[root@server1 mysql]# mysql -p
mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.00 sec)

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

设置主从复制

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

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
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='westos' FOR CHANNEL'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 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 (2.06 sec)

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

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 0eb25ad3-5e14-11ec-b60d-52540069c4fc | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

 server2中，清理数据库，重新启动

[root@server2 ~]# cd /data/mysql/
[root@server2 mysql]# rm -fr *
[root@server2 mysql]# vim /etc/my.cnf
[root@server2 mysql]# mysqld --initialize --user=mysql
[root@server2 mysql]# /etc/init.d/mysqld start 
Starting MySQL. SUCCESS! 
[root@server2 mysql]# cat mysqld.log 
[root@server2 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-log

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.00 sec)

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

设置数据库

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

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

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%&