MariaDB主从配置与MaxScale实现MySQL读写分离

maxscale是mariadb公司开发的一套数据库中间件,可以很方便的实现读写分离方案;并且提供了读写分离的负载均衡和高可用性保障。另外maxscale对于前端应用而言是透明的,我们可以很方便的将应用迁移到maxscale中实现读写分离方案,来分担主库的压力。maxscale也提供了sql语句的解析过滤功能。

2 环境说明 主机IP 角色 Server-id 操作系统版本 192.168.1.67 Master 77 RedHat7.3 192.168.1.68 Slave 78 Redhat7.3 192.168..1.69 Slave 79 Redhat7.3 3 MaxScale版本

https://downloads.mariadb.com/MaxScale/
https://downloads.mariadb.com/MaxScale/2.2.0/rhel/7server/x86_64/maxscale-2.2.0-1.rhel.7.x86_64.rpm

3.1 安装 将maxscale安装在主节点 rpm -ivh maxscale-2.2.0-1.rhel.7.x86_64.rpm 3.2 创建所需目录 mkdir -p /maxscale/cache mkdir -p /maxscale/data mkdir -p /maxscale/log mkdir -p /maxscale/pid mkdir -p /maxscale/tmp 3.3 创建用户 create user 'jiankongdb'@'%' identified by 'jiankong123'; grant SELECT on MySQL.user to 'jiankongdb'@'%'; GRANT SELECT ON mysql.db TO 'jiankongdb'@'%'; GRANT all ON *.* TO 'jiankongdb'@'%'; GRANT SHOW DATABASES ON *.* TO 'jiankongdb'@'%'; grant REPLICATION CLIENT on *.* to 'jiankongdb'@'%'; GRANT replication slave, replication client,SELECT ON *.* TO jiankongdb@'%'; 3.4 配置MaxScale

详细参数说明参考官方文档:
https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-21-mariadb-maxscale-configuration-usage-scenarios/
编辑配置文件/etc/maxscale.cnf

[root@ data]# cat /etc/maxscale.cnf # MaxScale documentation on GitHub: # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md # Global parameters # # Complete list of configuration options: # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md [maxscale] threads=auto ms_timestamp=1 #timestamp精度 syslog=1 #将日志写入到syslog中 maxlog=1 #将日志写入到maxscale的日志文件中 log_to_shm=0 #不将日志写入到共享缓存中,开启debug模式时可打开加快速度 log_warning=1 #记录告警信息 log_notice=1 #记录notice log_info=1 #记录info log_debug=0 #不打开debug模式 log_augmentation=1 #日志递增 #相关目录设置 logdir=/maxscale/log/ datadir=/maxscale/data/ libdir=/usr/lib64/maxscale/ cachedir=/maxscale/cache/ piddir=/maxscale/pid/ execdir=/usr/bin/ # Server definitions # # Set the address of the server to the network # address of a MySQL server. # [server1] type=server address=192.168.1.67 port=3306 protocol=MySQLBackend #serv_weight=1 [server2] type=server address=192.168.1.68 port=3306 protocol=MySQLBackend #serv_weight=3 [server3] type=server address=192.168.1.69 port=3306 protocol=MySQLBackend #serv_weight=4 # Monitor for the servers # # This will keep MaxScale aware of the state of the servers. # MySQL Monitor documentation: # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md [MySQL Monitor] type=monitor module=mysqlmon servers=server1,server2,server3 user=jiankongdb passwd=jiankong123 monitor_interval=10000 detect_stale_master=true # Service definitions # # Service Definition for a read-only service and # a read/write splitting service. # # ReadConnRoute documentation: # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md [Read-Only Service] type=service router=readconnroute servers=server1,server2,server3 user=jiankongdb passwd=jiankong123 router_options=slave enable_root_user=1 weightby=serv_weight # ReadWriteSplit documentation: # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md [Read-Write Service] type=service router=readwritesplit servers=server1,server2,server3 user=jiankongdb passwd=jiankong123 max_slave_connections=100% use_sql_variables_in=master enable_root_user=1 max_slave_replication_lag=3600 # This service enables the use of the MaxAdmin interface # MaxScale administration guide: # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md [MaxAdmin Service] type=service router=cli # 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=MySQLClient port=4008 [Read-Write Listener] type=listener service=Read-Write Service protocol=MySQLClient port=4006 [MaxAdmin Listener] type=listener service=MaxAdmin Service protocol=maxscaled socket=default 3.5 加密密码 配置文件中的密码都是经过maxscale进行加密后的,可以防止密码泄露,具体的操作步骤为 在刚才配置文件中的datadir目录下创建加密文件 [root@ data]#maxkeys /maxscale/data 生成加密后的密码 [root@ data]# maxpasswd /maxscale/data/ jiankong123 D88ED80AE158722A2A3FD725E906C601 1787C217C118C9B0F9A44DCB8B178414就是123加密后的密码。我们可以添加到配置文件中。 3.6 启动MaxScale maxscale -f /etc/maxscale.cnf 3.7 查看MaxScale 状态 [root@ software]# maxadmin list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 192.168.1.67 | 3306 | 0 | Master, Running server2 | 192.168.1.68 | 3306 | 0 | Slave, Running server3 | 192.168.1.69 | 3306 | 0 | Slave, Running -------------------+-----------------+-------+-------------+-------------------- 或者 [root@ log]# maxadmin -uadmin -pmariadb MaxScale> list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 192.168.1.67 | 3306 | 0 | Master, Running server2 | 192.168.1.68 | 3306 | 0 | Slave, Running server3 | 192.168.1.69 | 3306 | 0 | Slave, Running -------------------+-----------------+-------+-------------+-------------------- 4 验证读写分离 4.1 创建测试表 在主节点server1 192.168.1.67上建立测试表 MariaDB [cndba2]> create table test_maxscale(id int); Query OK, 0 rows affected (0.02 sec) MariaDB [cndba2]> insert into test_maxscale values(87); Query OK, 1 row affected (0.02 sec) MariaDB [cndba2]> select * from test_maxscale; +------+ | id | +------+ | 87 | +------+ 1 row in set (0.00 sec) 在节点server2 192.168.1.68上额外加入测试信息 MariaDB [cndba2]> insert into test_maxscale values(86); MariaDB [cndba2]> select * from test_maxscale; +------+ | id | +------+ s | 87 | | 86 | +------+ 2 rows in set (0.00 sec) 在节点server3 192.168.1.69上额外加入测试信息 MariaDB [cndba2]> insert into test_maxscale values(88); Query OK, 1 row affected (0.00 sec) MariaDB [cndba2]> select * from test_maxscale; +------+ | id | +------+ | 87 | | 88 | +------+ 2 rows in set (0.00 sec) 4.2 只读访问maxscale 通过mysql命令行访问maxscale所在节点192.168.1.78的读写分离listener 4006端口 [root@ data]# mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select * from cndba2.test_maxscale;" +------+ | id | +------+ | 87 | | 86 | +------+发现分到了server2上面 4.3 读写分离 加入包含insert的sql语句 [root@ data]# mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "insert into cndba2.test_maxscale values(90);select * from cndba2.test_maxscale;" +------+ | id | +------+ | 87 | | 86 | | 90 | +------+ 发现转发到server2中,但是也包含90的值,我们需要到主节点server3和另外一个slave进行验证 在server1主节点中 MariaDB [(none)]> select * from cndba2.test_maxscale; +------+ | id | +------+ | 87 | | 90 | +------+ 2 rows in set (0.00 sec) 在server3另一个slave节点中 MariaDB [cndba2]> select * from test_maxscale; +------+ | id | +------+ | 87 | | 88 | | 90 | +------+ 3 rows in set (0.00 sec) maxscale实现了读写分离。 5 验证负载均衡 5.1 查看Read-Write Service [root@ mysql]# maxadmin -uadmin -pmariadb MaxScale> show service "Read-Write-Service" Service: Read-Write-Service Router: readwritesplit State: Started use_sql_variables_in: master slave_selection_criteria: LEAST_CURRENT_OPERATIONS master_failure_mode: fail_instantly max_slave_replication_lag: 3600 retry_failed_reads: true strict_multi_stmt: true strict_sp_calls: false disable_sescmd_history: true max_sescmd_history: 0 master_accept_reads: false Number of router sessions: 121 Current no. of router sessions: 1 Number of queries forwarded: 272 Number of queries forwarded to master: 0 (0.00%) Number of queries forwarded to slave: 272 (100.00%) Number of queries forwarded to all: 121 (44.49%) Started: Mon Nov 27 23:53:13 2017 Root user access: Enabled Backend databases: [192.168.1.67]:3306 Protocol: MySQLBackend Name: server1 [192.168.1.68]:3306 Protocol: MySQLBackend Name: server2 [192.168.1.69]:3306 Protocol: MySQLBackend Name: server3 Total connections: 122 Currently connected: 1 5.2 测试结果 [root@ mysql]# for i in `seq 1 10`; do mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2>/dev/null & done [1] 3963 [2] 3964 [3] 3965 [4] 3966 [5] 3967 [6] 3968 [7] 3969 [8] 3970 [9] 3971 [10] 3972 [root@ mysql]# +------------+ | @@hostname | +------------+ | mariadb2 | +------------+ +------------+ | @@hostname | +------------+ | mariadb3 | +------------+ +------------+ | @@hostname | +------------+ | mariadb3 | +------------+ +------------+ | @@hostname | +------------+ | mariadb2 | +------------+ +------------+ | @@hostname | +------------+ | mariadb2 | +------------+ +------------+ | @@hostname | +------------+ | mariadb3 | +------------+ +------------+ | @@hostname | +------------+ | mariadb2 | +------------+ +------------+ | @@hostname | +------------+ | mariadb3 | +------------+ +------------+ | @@hostname | +------------+ | mariadb2 | +------------+ +------------+ | @@hostname | +------------+ | mariadb3 | +------------+ [1] Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null [2] Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null [3] Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null [4] Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null [5] Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null [6] Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null [7] Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null [8] Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null [9]- Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null [10]+ Done mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null 可以看到会话被平均分配到两个节点上

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/5f813453127b1dfa7e93bffbf632f0b5.html