如何利用HAProxy 代理 MySQL Master-Slave Replication

2023-11-13 来源：哗拓教育

背景

我有一个MySQL Master-Slave Replication, 想要通过HAProxy代理, 做读写分离.

写流量给到Master 节点, 读流量给到Slave 节点.

技术图片

首先我应该由一个HAProxy的配置, 我想应该这样写(伪配置):

listen mysql bind *:3316 mode tcp ... server 192.168.1.111 192.168.1.111:3306 check server 192.168.1.112 192.168.1.112:3306 check server 192.168.1.113 192.168.1.113:3306 check

很明显, 这么写仅仅只实现了流量的分发, 但并不能实现我的需求: 把写流量给Master, 读流量给Slave 节点.

那么问题来了,首先我要能够识别Master Slave 节点, 才能有接下来的流量分离.

HAProxy有个配置项叫做 mysql-check. 但是这里并不能用它去做Master-Slave的检测,因为这个option只提供存活检测.只要能够连上,就代表这个mysql后端是Health OK的.

这还是不符合我的需求.

MySQL 如果能够通过tcp-check 检测是Master还是Slave就好了.

既然这样, 写一个TCP-check Wrapper 不就可以了?

TCP-Check-Wrapper

这个Wrapper应该监听在TCP端口上,并以守护进程的方式运行.当HAproxy连接至其监听的端口时, 会执行相应的脚本, 并将脚本执行结果通过 TCP 连接发送至Haproxy.

代码如下:

TCP-Check-Wrapper

mysqlchk

放置好mysqlchk 脚本, 然后在每个MySQL节点上把tcp-check-wrapper服务跑起来.

在Master节点上测试一下脚本

[root@control-01 ~]# mysqlchk.mysqlHTTP/1.1 200 OKContent-Type: text/htmlContent-Length: 43<html><body>MySQL master is running.</body></html>

在Slave节点上测试一下

[root@control-02 ~]# mysqlchk.mysqlHTTP/1.1 200 OKContent-Type: text/htmlContent-Length: 43<html><body>MySQL slave is running. (Slave lag: 0)</body></html>

OK, 通过 mysql check 脚本已经可以帮助我们区分Master还是Slave了.

然后配置好tcp-check-wrapper.

# /etc/haproxy-tcp-check-wrapperhost = "0.0.0.0"port = 9090script = "/usr/bin/mysqlchk.mysql"

在每个MySQL节点上, 运行 tcp-check-wrapper.

haproxy_tcp_check &

HAProxy的配置

基于我们前面所做的, 现在可以写出HAProxy的配置文件了.

listen mysql_write bind *:3316 mode tcp timeout client 10800s timeout server 10800s balance leastconn option tcp-check tcp-check expect string MySQL master is running. option allbackups default-server port 9090 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100 server 192.168.1.111 192.168.1.111:3306 check server 192.168.1.112 192.168.1.112:3306 check server 192.168.1.113 192.168.1.113:3306 checklisten mysql_read bind *:3317 mode tcp timeout client 10800s timeout server 10800s balance leastconn option tcp-check tcp-check expect string MySQL slave is running. option allbackups default-server port 9090 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100 server 192.168.1.111 192.168.1.111:3306 check server 192.168.1.112 192.168.1.112:3306 check server 192.168.1.113 192.168.1.113:3306 check

总结

至此, 我们已经通过HAProxy, 借助了 "TCP-Check-Wrapper" + "mysqlchk", 实现了MySQL Master-Slave Replication 的读写分离,

TCP-Check-Wrapper 帮助我们对一些不能使用tcp-check option 的应用执行检查.mysqlchk 提供了具体检测的方法.

其实再多想一点, 可以再进一步利用MHA + HAPRoxy, 可以实现 MySQL Master-Slave Replication 高可用.即: 在Master发生故障时, MHA可以将其中一台 MySQL Slave提升为Master, 并且配置其他的Slave指向新的Master.

如何利用HAProxy 代理 MySQL Master-Slave Replication

标签：高可用 ica 配置文件 inf efault default slave pre 基于

小编还为您整理了以下内容，可能对您也有帮助：

如何配制MySql的Replication

1.建立专门用于Replication的账户首先Replication操作会涉及到的两个重要权限，这里先做一下说明： The REPLICATION CLIENT privilege enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS. REPLICATION CLIENT 使得用户可以使用SHOW MASTER STATUS和SHOW SLAVE STATUS命令，也就是说这个权限是用于授予账户监视Replication状况的权力。 The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server. REPLICATION SLAVE则是一个必须而基本的权限，它直接授予slave服务器以该账户连接master后可以执行replicate操作的权利。一般来说，我们会单独在主服务器创建一个专门用于Replication的账户。这个账户必须具有REPLICATION SLAVE权限，除此之外没有必要添加不必要的权限，保证该用户的职责单一。假定我们要建立的这个账户为repl,密码为repl,那么这一操作的命令如下： mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'repl'; 其中要特别说明一下192.168.0.%，这个配置是指明repl用户所在服务器，这里%是通配符，表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。如果没有使用通配符，而访问的服务器又不在上述配制里，那么你将无法使用该账户从你的服务器replicate主服务器. 另外在《Hight Performance MySql》一书中对用户权限的设置有所不同，作者建议在主机和从机上都配置repl账户，并同时赋予REPLICATION SLAVE和REPLICATION CLIENT权限，命令如下： mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'repl'; 作者解释了这样做的好处：一方面使用同一账户对Replication进行监视管理会很方便，不必区分slave,master，另一方面，repl账户在slave和master上的配制是一样的，这样如果我们切换slave和master，账户不需要做任何改动。 2.配置主从服务器主从服务器的配置都是通过改写my.cnf/my.ini文件来完成的。下面是主从服务器的必须的配置项：主机必须的配置项： log-bin //自定义，比如 log-bin=mysql-bin server_id //为server起一个唯一的id，默认是1，推荐使用IP的最后一节。从机必须的配置项： server_id //为server起一个唯一的id，默认是1，推荐使用IP的最后一节. 注意：一般，我们也会为从机设定log-bin,这是因为默认的log-bin文件是根据主机名命名的，一旦机器更改主机名就会出问题，再者保持主从机的配制一致也方便做主从机切换！主机可选的配置项：（用于配置主机哪些库会做二进制日志用以Replicate） binlog-do-db binlog-ignore-db 从机可选的配置项：（用于配置从机会Replicate哪些库和表） replicate-do-db, replicate-ignore-db replicate-do-table, replicate-ignore-table replicate-wild-do-table replicate-wild-ignore-table 注意：一条建议是不要在my.cnf/my.ini中配制master_host等选项，而应该使用CHANGE MASTER TO命令来动态设置！对于Master端，我只需简单地设置server_id和log_bin两项即可，对于Slave端其实只需要设置server_id，但是还有一些推荐的设置项。以下是《Hight Performance MySql》一书中给出的Slave端的推荐设置 # SLAVE-END replication-related configuration. # The only required option for slave-end is server_id. # The other options are recommanded on P 349 of《Hight Performance MySql》 server_id=234 log_bin=mysql_bin_log relay_log = mysql_relay_bin_log log_slave_updates = 1 read_only = 1 3.连接从服务器至主服务器进行Replicate 通过在从服务器上输入CHANGE MASTER TO命令可以使从服务连接到某个主服务器上进行replication. mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.246', -> MASTER_USER='repl', -> MASTER_PASSWORD='repl', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=0; 输入上述命令后即完成了全部配置工作，通过： start slave; 启动从服务的replication工作，这样主从服务器就开始同步了。你可以通过： SHOW SLAVE STATUS/G; 命令来查看从服务器的状态，如果是Slave_IO_State一项显示：Waiting for master to send event，表示所有工作已经就绪。转载

如何配制MySql的Replication

如何配置两个MySQL数据库之间的主从同步功能

一、概述

MySQL从3.23.15版本以后提供数据库复制（replication）功能，利用该功能可以实现两个数据库同步、主从模式、互相备份模式的功能。本文档主要阐述了如何在linux系统中利用mysql的replication进行双机热备的配置。

二、环境

操作系统：Linux 2.6.23.1-42.fc8 # SMP（不安装XEN）

Mysql版本：5.0.45-4.fc8

设备环境：PC（或者虚拟机）两台

三、配置

数据库同步复制功能的设置都在MySQL的配置文件中体现，MySQL的配置文件（一般是my.cnf）：在本环境下为/etc/my.cnf。

3.1 设置环境：

IP的设置：

A主机 IP：10.10.0.119

Mask：255.255.0.0

B主机 IP：10.10.8.112

Mask：255.255.0.0

在IP设置完成以后，需要确定两主机的防火墙确实已经关闭。可以使用命令service iptables status查看防火墙状态。如果防火墙状态

为仍在运行。使用service iptables stop来停用防火墙。如果想启动关闭防火墙，可以使用setup命令来禁用或定制。

最终以两台主机可以相互ping通为佳。

3.2 配置A主(master) B从(slave)模式

3.2.1 配置A 为master

、增加一个用户同步使用的帐号：

GRANT FILE ON *.* TO ‘backup’@'10.10.8.112' IDENTIFIED BY ‘1234’;

GRANTREPLICATION SLAVE ON *.* TO ‘backup’@'10.10.8.112' IDENTIFIED BY ‘1234’;

赋予10.10.8.112也就是Slave机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权限才可以。

、增加一个数据库作为同步数据库：

create database test;

、创建一个表结构：

create table mytest (username varchar(20),password varchar(20));

、修改配置文件：

修改A的/etc/my.cnf文件，在my.cnf配置项中加入下面配置：

server-id = 1 #Server标识

log-bin

binlog-do-db=test #指定需要日志的数据库

、重起数据库服务：

service mysqld restart

查看server-id：

show variable like ‘server_id’；

实例：

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 1 |

+---------------+-------+

1 row in set (0.00 sec)

、用show master status/G命令看日志情况。

正常为：

mysql> show master status/G

*************************** 1. row ***************************

File: mysqld-bin.000002

Position: 198

Binlog_Do_DB: test,test

Binlog_Ignore_DB:

1 row in set (0.08 sec)

3.2.2 配置B 为slave

、增加一个数据库作为同步数据库：

create database test;

、创建一个表结构：

create table mytest (username varchar(20),password varchar(20));

、修改配置文件：

修改B的/etc/my.cnf文件，在my.cnf配置项中加入下面配置：

server-id=2

master-host=10.10. 0.119

master-user=backup #同步用户帐号

master-password=1234

master-port=3306

master-connect-retry=60 #预设重试间隔秒

replicate-do-db=test #告诉slave只做backup数据库的更新

、重起数据库服务：

service mysqld restart

查看server-id：

show variables like ‘server_id’；

实例：

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 2 |

+---------------+-------+

1 row in set (0.00 sec)

、用show slave status/G命令看日志情况。

正常为：

mysql> show slave status/G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.0.119

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000001

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000003

Relay_Log_Pos: 236

Relay_Master_Log_File: mysqld-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test,test

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: 98

Relay_Log_Space: 236

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

1 row in set (0.01 sec)

3.2.3 验证配置

分别使用insert, delete , update在A主机进行增删改查数据库；查看B主机的数据库是否与A主机一致；若一致，则配置成功。

3.3双机互备模式

如果在A主机加入slave设置，在B主机加入master设置，则可以做B->A的同步。

、在A主机的配置文件中 mysqld配置项加入以下设置：

master-host=10.10.8.112

master-user=backup

master-password=1234

replicate-do-db=test

master-connect-retry=10

、在B的配置文件中 mysqld配置项加入以下设置：

log-bin

binlog-do-db=test

注意：当有错误产生时，*.err日志文件同步的线程退出，当纠正错误后，要让同步机制进行工作，运行slave start。

重起A、B机器，则可以实现双向的热备份。

四、常见问题及解决

、Slave机器的权限问题，不但要给slave机器File权限，还要给它REPLICATION SLAVE的权限。

、在修改完Slave机器/etc/my.cnf之后，slave机器的mysql服务启动之前，记得要删除掉master.info

、在show master status或着show slave status不正常时，看看.err是怎样说的。

、Slave上Mysql的Replication工作有两个线程, I/O thread和SQL thread。I/O的作用是从

master 3306端口上把它的binlog取过来(master在被修改了任何内容之后,就会把修改了什么写到自己的binlog等待slave更

新),然后写到本地的relay-log,而SQL thread则是去读本地的relay-log,再把它转换成本Mysql所能理解的语句，于是同步

就这样一步一步的完成.决定I/O thread的是/var/lib/mysql/master.info,而决定SQL thread的是/var

/lib/mysql/relay-log.info.

、启动slave，命令用start slave；重新启动用restart slave

显示全文

全部栏目

如何利用HAProxy 代理 MySQL Master-Slave Replication