官方网站改版建议,广阳网站制作,wordpress魔改,android开发者官网一、MaxScale基本介绍
MaxScale是maridb开发的一个mysql数据中间件#xff0c;其配置简单#xff0c;能够实现读写分离#xff0c;并且可以根据主从状态实现写库的自动切换#xff0c;对多个从服务器能实现负载均衡。 二、MaxScale实验环境
中间件192.168.121.51MaxScale…一、MaxScale基本介绍
MaxScale是maridb开发的一个mysql数据中间件其配置简单能够实现读写分离并且可以根据主从状态实现写库的自动切换对多个从服务器能实现负载均衡。 二、MaxScale实验环境
中间件192.168.121.51MaxScale 22.08.4主服务器192.168.121.11mysql 8.0.30从服务器192.168.121.12mysql 8.0.30从服务器192.168.121.13mysql 8.0.30
三、实现数据库主从复制
1主库配置
[rootopenEuler-1 ~]# tail -n4 /etc/my.cnf
server_id11
gtid_modeON
enforce-gtid-consistencyON
read_only0
[rootopenEuler-1 ~]# systemctl restart mysqld# 创建用户
create user rep192.168.121.% identified with mysql_native_password by 123456;
# 用户授权
grant replication slave on *.* to rep192.168.121.%;
2从库配置
[rootopenEuler-2 ~]# tail -n4 /etc/my.cnf
server_id12
gtid_modeON
enforce-gtid-consistencyON
read_only1
[rootopenEuler-2 ~]# systemctl restart mysqld# 配置主从同步
CHANGE MASTER TO
MASTER_HOST 192.168.121.11,
MASTER_USER rep,
MASTER_PASSWORD 123456,
MASTER_AUTO_POSITION 1;# 启动主从同步
start slave
四、创建用户
在开始配置之前需要在master中为 MaxScale 创建两个用户用于监控模块和路由模块已经实现主从复制的前提下主库创建的用户能同步到从库上
1创建监控用户
# 创建用户
mysql create user maxscale_monitor192.168.121.% identified with mysql_native_password by Admin123456;# 授权用户
mysql grant replication slave, replication client on *.* to maxscale_monitor192.168.121.%;
2创建路由用户
# 创建用户
mysql create user maxscale_route192.168.121.% identified with mysql_native_password by Admin123456;# 授权用户
mysql GRANT SHOW DATABASES ON *.* TO maxscale_route192.168.121.%;
mysql GRANT SELECT ON mysql.user TO maxscale_route192.168.121.%;
mysql GRANT SELECT ON mysql.db TO maxscale_route192.168.121.%;
mysql GRANT SELECT ON mysql.tables_priv TO maxscale_route192.168.121.%;
mysql GRANT SELECT ON mysql.columns_priv TO maxscale_route192.168.121.%;
mysql GRANT SELECT ON mysql.proxies_priv TO maxscale_route192.168.121.%;五、安装MaxScale
在 Download MariaDB Products Tools | MariaDB 选择对应系统合适的版本下载安装可供参考。
# 配置存储库源
[rootlocalhost ~]# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash# 安装maxscale
[rootlocalhost ~]# dnf install maxscale -y六、配置MaxScale
在/etc/maxscale.cnf.d新建一个配置my.cnf或者直接修改/etc/maxscale.cnf文件
[maxscale]
# 开启线程个数默认为1.设置为auto会同cpu核数相同
threadsauto# 定义三台主机
[dbserv1]
typeserver
address192.168.121.11
port3306
protocolMariaDBBackend[dbserv2]
typeserver
address192.168.121.12
port3306
protocolMariaDBBackend[dbserv3]
typeserver
address192.168.121.13
port3306
protocolMariaDBBackend# 监视进程
[MySQL-Monitor]
typemonitor
modulemariadbmon
serversdbserv1, dbserv2, dbserv3
usermaxscale_monitor
passwordAdmin123456
monitor_interval2s# 读写分离
[Read-Write-Service]
typeservice
routerreadwritesplit
serversdbserv1,dbserv2,dbserv3
usermaxscale_route
passwordAdmin123456
enable_root_usertrue# 监听读写服务端口
[Read-Write-Listener]
typelistener
serviceRead-Write-Service
protocolMariaDBClient
address0.0.0.0
port3306七、重启MaxScale服务
# 启动服务
[rootlocalhost ~]# maxscale -f /etc/maxscale.cnf.d/my.cnf -U maxscale# 查看运行状态
[rootlocalhost ~]# maxctrl list servers
┌─────────┬────────────────┬──────┬─────────────┬─────────────────┬──────┬───────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤
│ dbserv1 │ 192.168.121.11 │ 3306 │ 0 │ Master, Running │ │ MySQL-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤
│ dbserv2 │ 192.168.121.12 │ 3306 │ 0 │ Slave, Running │ │ MySQL-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤
│ dbserv3 │ 192.168.121.13 │ 3306 │ 0 │ Slave, Running │ │ MySQL-Monitor │
└─────────┴────────────────┴──────┴─────────────┴─────────────────┴──────┴───────────────┘# 查看注册服务
[rootlocalhost ~]# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 0 │ dbserv1, dbserv2, dbserv3 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘# 查看服务监听状态信息
[rootlocalhost ~]# maxctrl list listeners Read-Write-Service
┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐
│ Name │ Port │ Host │ State │ Service │
├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 3306 │ 0.0.0.0 │ Running │ Read-Write-Service │
└─────────────────────┴──────┴─────────┴─────────┴────────────────────┘八、测试连接
1在master中创建一个访问用户已经实现主从复制的前提下主库创建的用户能同步到从库上
mysql create user admin_user% identified with mysql_native_password by Admin123456;
mysql grant all privileges on *.* to admin_user% with grant option;2测试读写分离
[rootopenEuler-2 ~]# mysql -uadmin_user -pAdmin123456 -h 192.168.121.51 -e select server_id
mysql: [Warning] Using a password on the command line interface can be insecure.
-------------
| server_id |
-------------
| 13 |
-------------
[rootopenEuler-2 ~]# mysql -uadmin_user -pAdmin123456 -h 192.168.121.51 -e select server_id
mysql: [Warning] Using a password on the command line interface can be insecure.
-------------
| server_id |
-------------
| 12 |
-------------
[rootopenEuler-2 ~]# mysql -uadmin_user -pAdmin123456 -h 192.168.121.51 -e begin;select server_id commit;
mysql: [Warning] Using a password on the command line interface can be insecure.
--------
| commit |
--------
| 11 |
--------3也可以在从库中 slave(192.168.121.12) 新增一条数据登录主库 master(192.168.121.11) 进行查询如果查询不到在中间件连接库中 maxscale(192.168.121.51) 可以查询到则成功 但是不建议这样测试会破坏主从同步。
九、配置脚本启动maxscale
[rootlocalhost ~]# ps -ef | grep maxscale
maxscale 10918 1 0 11:59 ? 00:00:09 maxscale -f /etc/maxscale.cnf.d/my.cnf -U maxscale
root 10964 1681 0 12:42 pts/0 00:00:00 grep --colorauto maxscale# 关闭进程停止服务
[rootlocalhost ~]# killall -9 maxscale# 拷贝配置文件为主配置文件
[rootlocalhost ~]# cp /etc/maxscale.cnf.d/my.cnf /etc/maxscale.cnf
# 原来的重命名防止出现报错
[rootlocalhost ~]# mv /etc/maxscale.cnf.d/my.cnf{,.bak}# 启动服务
[rootlocalhost ~]# systemctl enable --now maxscale十、开启maxscale GUI
1在maxscale配置文件中[maxscale]下添加两行配置
[rootlocalhost ~]# vim /etc/maxscale.cnf
[rootlocalhost ~]# head -n4 /etc/maxscale.cnf
[maxscale]
threadsauto
admin_host0.0.0.0
admin_secure_guifalse
[rootlocalhost ~]# systemctl restart maxscale
2访问 http://192.168.121.51:8989默认账户密码如下
