mysql 5.7 半同步互主+mysql router搭建

1、环境介绍
操作系统：centos 7.9
cpu：4核心
内存：8GB
磁盘：100GB
mysql版本：5.7
优化脚本：mysqltuner
ip地址规划：
mysql01 10.99.24.21
mysql02 10.99.24.22

2、关闭防火墙和selinux

3、设置主机名和hosts解析

4、下载mysql yum源rpm并安装
https://dev.mysql.com/downloads/repo/yum/

wget https://repo.mysql.com//mysql80-community-release-el7-5.noarch.rpm
rpm -ivh mysql80-community-release-el7-5.noarch.rpm

5、修改yum repo文件

vi /etc/yum.repos.d/mysql-community.repo

注：enable=1为启用，把mysql8.0的关上，打开5.7

6、安装mysql

yum install mysql-community-server -y

7、启动mysql，并添加开机自启动

service mysqld start
systemctl enable mysqld

8、获取初始密码

grep 'temporary password' /var/log/mysqld.log

9、用上一步获取的密码登陆mysql

mysql -uroot -p

10、更改mysql密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Smtgbk_123';
FLUSH PRIVILEGES;

11、下载并使用优化脚本
脚本依赖perl环境，先安装环境

yum install perl* -y

下载脚本

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

元数据统计信息更新会严重影响数据库服务器和MySQLTuner的性能。确保禁用innodb_stats_on_metadata。

mysql> set global innodb_stats_on_metadata = 0;

使用脚本

perl mysqltuner.pl --host targetDNS_IP --user admin_user --pass admin_password

修改参数可能如下：添加到/etc/my.cnf

skip-name-resolve=1
key_buffer_size=1M
innodb_log_file_size=16M

12、安装半同步插件
登陆数据库安装

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

13、/etc/my.cnf文件追加配置

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second

rpl_semi_sync_slave_enabled=1

12、/etc/my.cnf文件添加binlog配置和互主模式主键冲突配置
主：

log-bin=mysql-bin
server-id=1
#主键增长起始点是1，步长是2，1、3、5。。增长
auto_increment_increment=2
auto_increment_offset=1

从：

log-bin=mysql-bin
server-id=2
#主键增长起始点是2，步长是2，2、4、6。。增长
auto_increment_increment=2
auto_increment_offset=2

13、配置数据半同步
mysql01：

mysql> CREATE USER 'slave1'@'10.99.24.%' IDENTIFIED WITH mysql_native_password BY '1qazXSW@';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'10.99.24.%';
mysql> flush privileges;

mysql> show master status;

mysql02：

mysql> CREATE USER 'slave2'@'10.99.24.%' IDENTIFIED WITH mysql_native_password BY '1qazXSW@';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'10.99.24.%';
mysql> flush privileges;

mysql> show master status;

mysql02：

change master to
master_host='10.99.24.21',
master_port=3306,
master_user='slave1',
master_password='1qazXSW@',
master_log_file='mysql-bin.000001',
master_log_pos=154;

mysql> start slave;
mysql> show slave status\G;

mysql01：

change master to
master_host='10.99.24.22',
master_port=3306,
master_user='slave2',
master_password='1qazXSW@',
master_log_file='mysql-bin.000001',
master_log_pos=154;

mysql> start slave;
mysql> show slave status\G;

14、创建远程root用户

CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Smtgbk_123';
grant all on *.* to root@'%';
flush privileges;

15、配置mysql router
在要访问mysql的服务器安装

wget https://repo.mysql.com//mysql80-community-release-el7-5.noarch.rpm
rpm -ivh mysql80-community-release-el7-5.noarch.rpm
yum install mysql-router

修改mysql router配置文件，添加如下
/etc/mysqlrouter/mysqlrouter.conf

[routing:primary]
bind_address = 127.0.0.1
bind_port = 3306
destinations = 10.99.24.21:3306,10.99.24.22:3306
routing_strategy = first-available

启动mysqlrouter

systemctl start mysqlrouter
systemctl enable mysqlrouter

安装mysql client

yum install mysql

通过mysql router 连接

mysql -uroot -p -h 127.0.0.1

或者

mysql -uroot -p --protocol=TCP