MaxScale Binlog Server实践

113次阅读

共计 5578 个字符，预计需要花费 14 分钟才能阅读完成。

简介

Part1: 写在最前

在之前的博文中有说到 MaxScale，作为中间件，配合 MHA 使用或者主从使用可实现读写分离和负载均衡，今天简单介绍下 MaxScale 作为 Binlog Server 来减少主从延迟的问题；MySQL 的主从架构中，链式拓扑的架构比较容易出现主从延迟的问题。本文着重介绍 MaxScale 作为 Binlog Server 是如何降低主从延迟的。

MaxScale 配合 MHA 请移步至：http://www.linuxidc.com/Linux/2016-11/136969.htm

Part2: 本文环境

HE1:192.168.1.248 slave

HE3:192.168.1.250 master

HE4:192.168.1.251 maxscale

架构演示

MaxScale Binlog Server 实践

实战

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]

Part2: 从库配置

[root@HE1 ~]# mysql -umysync -pMANAGER -h192.168.1.251 -P5308

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 3196

Server version: 10.0.0 2.0.1-maxscale

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

Part2: 主库配置

[root@HE3 ~]# mysql -uroot -p

Enter 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

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> show master status;

+——————+———-+————–+——————+

+——————+———-+————–+——————+

| 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 -pMANAGER

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 5

Server version: 10.1.16-MariaDB MariaDB Server

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 则避免了这类问题。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-12/137892.htm

正文完

星哥说事-微信公众号