摘要:本文主要向大家介绍了MySQL数据库之MaxScale Binlog Server实践 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
本文主要向大家介绍了MySQL数据库之MaxScale Binlog Server实践 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
简介
Part1:写在最前
在之前的博文中有说到MaxScale,作为中间件,配合MHA使用或者主从使用可实现读写分离和负载均衡,今天简单介绍下MaxScale作为Binlog Server来减少主从延迟的问题;MySQL的主从架构中,链式拓扑的架构比较容易出现主从延迟的问题。本文着重介绍MaxScale作为Binlog Server是如何降低主从延迟的。
MaxScale配合MHA请移步至:
//suifu.blog.51cto.com/9167728/1869520
Part2:本文环境
HE1:192.168.1.248 slave
HE3:192.168.1.250 master
HE4:192.168.1.251 maxscale
架构演示
效果对比
实战
Part1:安装maxscale
[root@HE4 ~]# yum -y install maxscale-2.0.1-2.centos.6.x86_64.rpm
[root@HE4 ~]# mkdir -p /data/binlog
[root@HE4 ~]# useradd maxscale
[root@HE4 ~]# chown -R maxscale. /data/binlog
[root@HE4 ~]# cat /etc/maxscale.cnf
[maxscale]threads=1##根据CPU核数设置[Replication]type=service
router=binlogrouter
user=mysync
passwd=MANAGER# 使用主库上的repl复制账号# 权限:# GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';router_options=server_id=1251,heartbeat=30,binlogdir=/data/binlog,transaction_safety=1,mariadb10-compatibility=1,send_slave_heartbeat=1# server_id设置maxscale的,记得不能与主和从库重复,要唯一# heartbeat=30秒,意思为当maxscale在30秒内没有接收到主库推送的binlog日志,发送心跳检查# binlogdir设置接收binlog的存放路径,目录属性chown -R maxscale.maxscale /data/binlog# transaction_safety=1此参数用于启用binlog日志中的不完整事务检测。 当MariaDB MaxScale启动时,如果当前binlog文件已损坏或找到不完整的事务,则可能会出现错误消息。 在正常工作期间,binlog事件不会分配到从库,直到事务已经提交。 默认值为off,设置transaction_safety = on以启用不完全事务检测。# send_slave_heartbeat=1开启心跳检查[Replication Listener]type=listener
service=Replication
protocol=MySQLClient
port=5308# 后端的从库CHANGE MASTER TO这个端口,默认5308[CLI]type=service
router=cli[CLI Listener]type=listener
service=CLI
protocol=maxscaled
port=6603
Part2:启动Maxscale
[root@HE4 ~]# /etc/init.d/maxscale start
Starting MaxScale: maxscale (pid 16680) is running... [ OK ]
[root@HE4 ~]# /etc/init.d/maxscale status
Checking MaxScale status: MaxScale (pid 16680) is running.[ OK ]
Part3:从库配置
[root@HE1 ~]# mysql -umysync -pMANAGER -h192.168.1.251 -P5308Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3196
Server version: 10.0.0 2.0.1-maxscale
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.250',MASTER_USER='mysync',MASTER_PASSWORD='MANAGER',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=20;ERROR 1234 (42000): Can not set MASTER_LOG_POS to 20: Permitted binlog pos is 4. Specified master_log_file=mysql-bin.000005
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.250',MASTER_USER='mysync',MASTER_PASSWORD='MANAGER',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=4;MySQL [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)
这里可以看出,Maxscale binlog server只能从位置4开始配置
配置好后,在/data/binlog下生成的binlog文件
[root@HE4 ~]# cd /data/binlog/
[root@HE4 binlog]# ls
cache master.ini mysql-bin.000003
Part4:主库配置
[root@HE3 ~]# mysql -uroot -pEnter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.16-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000005 | 652 | | |+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)MariaDB [(none)]> grant replication client,replication slave on *.* to 'mysync'@'192.168.1.%' identified by 'MANAGER';MariaDB [(none)]>flush privileges;
Part5:主从配置
从库指向binlogserver[root@HE1 ~]# mysql -uroot -pMANAGERWelcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.16-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.251',MASTER_USER='mysync',MASTER_PASSWORD='MANAGER',MASTER_PORT=5308,MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=652;Query OK, 0 rows affected (0.02 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.251
Master_User: mysync
Master_Port: 5308
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 652
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 537
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 652
Relay_Log_Space: 835
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1250
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)
——总结——
生产环境中,大多采用的是一主多从架构,例如星状拓扑和链式拓扑,星状拓扑在从库过多的情况下,会增加主库的io压力,而链式拓扑虽然缓解了主库的网络IO压力,但其缺点是:二级Slave得到最新的数据,需要再经过一层的复制才到达,期间的延迟比一主多从架构要大。而采用maxscale binlog server则避免了这类问题。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号