配置MySQL读写分离架构

采用maxscale

下载maxscale

https://mariadb.com/downloads/community/maxscale/
https://dlm.mariadb.com/2539572/MaxScale/22.08.2/yum/centos/7/x86_64/maxscale-22.08.2-1.rhel.7.x86_64.rpm

配置MySQL主从架构

配置MySQL主服务器

tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
yum -y install *.rpm

vim /etc/my.cnf
server_id=001
log_bin=master

systemctl enable mysqld --now
grep password /var/log/mysqld.log | tail -1
mysql -uroot -p'r53xlCoRRr*f'
mysql>  alter user root@localhost identified by 'zzz-123-ZZZ';
mysql> grant replication slave on *.* to repluser@"%" identified by "zzz-123-ZZZ";
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| master.000001 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


mysql> create database DB1;
mysql> use DB1;
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> select * from t1;
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 rows in set (0.00 sec)

配置MySQL从服务器

tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
yum -y install *.rpm

vim /etc/my.cnf
server_id=002

systemctl enable mysqld --now
grep password /var/log/mysqld.log | tail -1
mysql -uroot -p'k8<dXW2(7WCP'
mysql>  alter user root@localhost identified by 'zzz-123-ZZZ';
change master to master_host="192.168.0.20",master_user="repluser",master_password="zzz-123-ZZZ",master_log_file="master.000001",master_log_pos=154;
mysql> start slave;
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
mysql> select * from DB1.t1;
+------+
| id   |
+------+
|    1 |
+------+
1 rows in set (0.00 sec)

配置maxscale读写分离服务器

安装服务

yum -y install maxscale-6.4.3-1.rhel.7.x86_64.rpm

编辑配置文件

vim /etc/maxscale.cnf

# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6/

# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-6-mariadb-maxscale-configuration-guide/

[maxscale]
threads=auto	#服务启动后线程的数量auto 自动

# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#

[server1]		#指定第1台数据库服务器信息
type=server
address=192.168.0.20	#服务器地址
port=3306
protocol=MariaDBBackend

#复制[server1] 新增[server2] 
[server2]	#指定第2台数据库服务器信息
type=server
address=192.168.0.21
port=3306
protocol=MariaDBBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/maxscale-6-monitors/

[MariaDB-Monitor]	#定义监视数据库服务器
type=monitor
module=mariadbmon
servers=server1,server2		#监视server1和server2
user=myusera	# 监控用户，自定义 需要在mysql服务里授权 #replication client  监视数据库服务的运行状态 #replication slave  数据库服务器的主从角色
password=zzz-123-ZZZ	#连接密码，自定义 需满足mysql密码策略
monitor_interval=2000

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readconnroute/

#注释只读服务
#[Read-Only-Service]
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#password=mypwd
#router_options=slave

# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readwritesplit/

[Read-Write-Service]	#启用读写分离服务https://mariadb.com/kb/en/mariadb-maxscale-6-read-write-splitting-with-mariadb-maxscale/
type=service
router=readwritesplit
servers=server1,server2	#读写分离服务在server1和server2服务器之间进行
user=myuserb	#路由用户	自定义 需要在mysql服务里授权 #对授权库下的表有查询权限
password=zzz-123-ZZZ	#连接密码	自定义 需满足mysql密码策略

# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

#因为只读服务没有启用所有也不需要定义服务使用的端口号 全部注释
#[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#protocol=MariaDBClient
#port=4008

[Read-Write-Listener]	#定义读写分离服务使用端口号
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

MySQL服务授权maxscale配置的用户

在主服务器配置即可

添加监控用户 myusera用户

#权限说明：
#replication client  监视数据库服务的运行状态 
#replication slave  数据库服务器的主从角色
mysql> grant replication  slave , replication client on *.* to myusera@"%" identified by "zzz-123-ZZZ";

添加路由用户myuserb用户

#对授权库下的表有查询权限
mysql> grant select on mysql.* to myuserb@"%" identified by "zzz-123-ZZZ"; 

如需要，可在从服务器刷新权限

mysql> flush privileges;

验证MySQL服务授权maxscale配置的用户的权限

查看本机是否有mysql命令，没有命令安装mariadb

which mysql
yum -y install mariadb

验证授权账户登录MySQL服务

#验证授权账户登录MySQL服务
mysql -umyusera -pzzz-123-ZZZ -h192.168.0.20
mysql -umyusera -pzzz-123-ZZZ -h192.168.0.21
mysql -umyuserb -pzzz-123-ZZZ -h192.168.0.20
mysql -umyuserb -pzzz-123-ZZZ -h192.168.0.21

启动maxscale服务

#systemctl命令启动
systemctl start maxscale.service
#或者指定maxscale用户，指定配置文件启动
#maxscale -f /etc/maxscale.cnf -U maxscale

查看服务状态

systemctl status maxscale.service
[root@maxscale ~]# ss -utnlp | grep maxscale
tcp    LISTEN     0      128    127.0.0.1:8989                  *:*                   users:(("maxscale",pid=623,fd=24))
tcp    LISTEN     0      1024   [::]:4006               [::]:*                   users:(("maxscale",pid=623,fd=27))

访问管理服务查看数据库服务的监控信息

• MaxCtrl 是 MaxScale 的命令行管理客户端，它使用 用于通信的 MaxScale REST API。它取代了旧的MaxAdmin 不再支持或包含的命令行客户端。
• 默认情况下，MaxScale REST API 侦听本地主机上的端口 8989。这 REST API 的默认凭据是。用户使用的 REST API 与 MaxAdmin 网络接口使用的 API 相同。这 表示为 MaxAdmin 网络接口创建的任何用户都应使用 MaxScale REST API 和 MaxCtrl。admin:mariadb
• 详情见https://mariadb.com/kb/en/mariadb-maxscale-6-maxctrl/

#管理服务默认开启，使用端口号8989
#管理工具为maxctrl，默认账户admin，密码mariadb

[root@maxscale ~]# maxctrl
 maxctrl list servers
┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬──────┐
│ Server  │ Address      │ Port │ Connections │ State           │ GTID │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server1 │ 192.168.0.20 │ 3306 │ 0           │ Master, Running │      │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server2 │ 192.168.0.21 │ 3306 │ 0           │ Slave, Running  │      │
└─────────┴──────────────┴──────┴─────────────┴─────────────────┴──────┘
 maxctrl

验证读写分离

MySQL主服务器授权测试账号

mysql> grant select,insert on DB1.* to testt@"%" identified by "123-zzz-ZZZ";

如有需要，可从服务器刷新权限

mysql> flush privileges;

测试写操作在主服务器

mysql -h192.168.0.22 -utestt -pzzz-123-ZZZ -P4006
MySQL [(none)]> show databases; #只对DB1 有权限
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DB1                |
+--------------------+
2 rows in set (0.00 sec)

MySQL [(none)]> select * from DB1.t1;
+------+
| id   |
+------+
|    1 |
+------+
1 rows in set (0.00 sec)

MySQL [(none)]> insert into DB1.t1 values(2);
MySQL [(none)]> insert into DB1.t1 values(100);
MySQL [(none)]> select * from DB1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|  100 |
+------+

• 主数据库和从数据库查看写入的数据，都存在数据。
• 因为写入主，从同步主数据了，所以都有数据
• 写入从的话，就会只有从有数据，主没有数据

[root@mysql-master ~]# mysql -uroot -pzzz-123-ZZZ -e "select * from  DB1.t1"
mysql> select * from DB1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|  100 |
+------+
3 rows in set (0.00 sec)

[root@mysql-slave ~]# mysql -uroot -pzzz-123-ZZZ -e "select * from  DB1.t1"
mysql> select * from DB1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|  100 |
+------+
3 rows in set (0.00 sec)

测试读操作在从服务器

• 直接在从服务器插入新数据
• 因为主从同步结构，主服务器不同步从服务器的数据
• 所以主服务器查不到新的数据
• 因为读写分离，客户端执行读操作时连接从服务器，所以客户端能看到新数据

#直接在从服务器插入新数据
[root@mysql-slave ~]# mysql -uroot -pzzz-123-ZZZ -e "insert into DB1.t1 values(1006)"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-slave ~]# mysql -uroot -pzzz-123-ZZZ -e "select * from  DB1.t1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|    1 |
|    2 |
|  100 |
| 1006 |
+------+
#主服务器看不到
[root@mysql-master ~]# mysql -uroot -pzzz-123-ZZZ -e "select * from  DB1.t1"
+------+
| id   |
+------+
|    1 |
|    2 |
|  100 |
+------+
#通过读写分离服务器可以看到
mysql -h192.168.0.22 -utestt -pzzz-123-ZZZ -P4006 -e "select * from  DB1.t1"
+------+
| id   |
+------+
|    1 |
|    2 |
|  100 |
| 1006 |
+------+

• 说明：如果主从结构中的从服务器宕机了，就实现不了读写分离了，会把读写请求都给主服务器处理。
  – 如果主从结构中的主服务器宕机了，读写分离服务无法访问
  – 读写分离服务器 只有1台 单点故障问题无法避免。