MaxScale Binlog Server实践

简介Part1:写在最前在之前的博文中有说到MaxScale，作为中间件，配合MHA使用或者主从使用可实现读写分离和负载均衡，今天简单介绍下MaxScale作为Binlog Server来减少主从延迟的问题；MySQL的主从架构中，链式拓扑的架构比较容易出现主从延迟的问题。本文着重介绍MaxScale作为Binlog Server是如何降低主从延迟的。MaxScale配合MHA请移步至：http://www.linuxidc.com/Linux/2016-11/136969.htmPart2:本文环境HE1:192.168.1.248 slaveHE3:192.168.1.250 masterHE4: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=servicerouter=binlogrouteruser=mysyncpasswd=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=listenerservice=Replicationprotocol=MySQLClientport=5308# 后端的从库CHANGE MASTER TO这个端口，默认5308[CLI]type=servicerouter=cli[CLI Listener]type=listenerservice=CLIprotocol=maxscaledport=6603Part2:启动Maxscale[root@HE4 ~]# /etc/init.d/maxscale startStarting MaxScale: maxscale （pid 16680） is running... [ OK ][root@HE4 ~]# /etc/init.d/maxscale statusChecking MaxScale status: MaxScale （pid 16680） is running.[ OK ]
Part2:从库配置[root@HE1 ~]# mysql -umysync -pMANAGER -h192.168.1.251 -P5308Welcome to the MariaDB monitor. Commands end with ; or g.Your MySQL connection id is 3196Server version: 10.0.0 2.0.1-maxscaleCopyright （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.000005MySQL [（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]# lscache master.ini mysql-bin.000003Part2:主库配置[root@HE3 ~]# mysql -uroot -pEnter password:Welcome to the MariaDB monitor. Commands end with ; or g.Your MariaDB connection id is 7Server version: 10.1.16-MariaDB MariaDB ServerCopyright （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;Part3:主从配置从库指向binlogserver[root@HE1 ~]# mysql -uroot -pMANAGERWelcome to the MariaDB monitor. Commands end with ; or g.Your MariaDB connection id is 5Server version: 10.1.16-MariaDB MariaDB ServerCopyright （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 statusG*************************** 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: 0Master_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: conservative1 row in set （0.00 sec）——总结——生产环境中，大多采用的是一主多从架构，例如星状拓扑和链式拓扑，星状拓扑在从库过多的情况下，会增加主库的io压力，而链式拓扑虽然缓解了主库的网络IO压力，但其缺点是：二级Slave得到最新的数据，需要再经过一层的复制才到达，期间的延迟比一主多从架构要大。而采用maxscale binlog server则避免了这类问题。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。本文永久更新链接地址