MySQL 搭建双主复制服务并通过 HAProxy 负载均衡的过程详解
（福利推荐：你还在原价购买阿里云服务器？现在阿里云0.8折限时抢购活动来啦！4核8G企业云服务器仅2998元/3年，立即抢购>>>：9i0i.cn/aliyun）
一、MySQL 搭建双主复制高可用服务
在数据库管理中，数据的备份和同步是至关重要的环节，而双主复制（Dual Master Replication）作为一种高可用性和数据同步的解决方案，通过让两个数据库实例同时充当主服务器和从服务器，MySQL双主复制可以实现数据的双向同步，为数据库系统提供了更灵活和可靠的解决方案。即使其中一个主服务器发生故障，另一个主服务器仍然可以继续提供服务，确保系统的稳定性和可用性。这种数据同步方式不仅可以加强数据的备份与恢复能力，还可以提高系统的扩展性，适用于需要高度数据一致性和容错性的场景。
本文将介绍MySQL双主复制的配置过程，整体实现架构如下：
主机规划：
| ip | 规划 |
|---|---|
| 172.19.222.20 | MySQL1 |
| 172.19.222.82 | MySQL2 |
| 172.19.222.84 | Haproxy |
二、MySQL1 部署
mysql 的部署这里采用 docker + docker-compose 的方式快速实现，主要关注点在于配置文件：
首先编写 my.cnf 文件，写入如下内容：
[client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake skip-name-resolve pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # 主从同步 server-id = 1 log-bin = mysql-bin sync_binlog = 1 binlog_checksum = none binlog_format = mixed auto-increment-increment = 2 auto-increment-offset = 1 slave-skip-errors = all event_scheduler = 1 max_allowed_packet = 64M # Custom config should go here !includedir /etc/mysql/conf.d/
其中主从复制的参数解释如下：
server-id = 1：指定MySQL实例的唯一标识ID，用于在主从复制中区分不同的MySQL实例。
log-bin = mysql-bin：启用binlog日志，并指定binlog文件的前缀名为mysql-bin。binlog用于记录所有的数据更改操作，以便主从服务器之间进行数据同步。
sync_binlog = 1：表示每次事务提交时都将强制把binlog缓冲区的内容写入磁盘，确保binlog日志及时持久化，提高数据安全性。
binlog_checksum = none：设置binlog文件的校验方式为none，表示不对binlog文件进行校验和验证。
binlog_format = mixed：指定binlog日志的格式为mixed，即混合模式，根据具体情况自动选择使用statement或row格式记录数据变更。
auto-increment-increment = 2：设置自增长字段的增量值为2，用于在主从复制中避免自增字段冲突。
auto-increment-offset = 1：设置自增长字段的偏移量为1，用于在主从复制中避免自增字段冲突。
slave-skip-errors = all：当从服务器在执行SQL线程时发生错误时，跳过所有错误继续执行，这可能会导致数据不一致，谨慎使用。
event_scheduler = 1：启用Event Scheduler，用于执行预定的事件任务。
max_allowed_packet = 64M：设置最大允许的数据包大小为64MB，用于控制单个数据库请求或查询的数据包大小限制。
编写 docker-compose.yml 文件：
version: '2.0'
services:
mysql:
restart: always
image: mysql:8.0.20
container_name: mysql
volumes:
- ./data:/var/lib/mysql
- ./my.cnf:/etc/mysql/my.cnf
command:
--lower_case_table_names=1
--character-set-server=utf8
--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ports:
- "3306:3306"
environment:
- MYSQL_ROOT_PASSWORD=root123
- TZ=Asia/Shanghai 启动服务：
docker-compose up -d
下面使用客户端工具连接该数据库：
查看当前 master 状态：
show master status;
注意这里查询出来的 File 和 Position 下面主从复制时会用到。
创建用于主从复制的用户：
CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'replica123'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%'; FLUSH PRIVILEGES;
三、MySQL2 部署
同样编写 my.cnf 文件，写入如下内容：
[client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake skip-name-resolve pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # 主从同步 server-id = 2 log-bin = mysql-bin sync_binlog = 1 binlog_checksum = none binlog_format = mixed auto-increment-increment = 2 auto-increment-offset = 2 slave-skip-errors = all event_scheduler = 1 max_allowed_packet = 64M # Custom config should go here !includedir /etc/mysql/conf.d/
配置和MySQL1差不多，主要注意 server-id 和 auto-increment-offset 的区别。
编写 docker-compose.yml 文件：
version: '2.0'
services:
mysql:
restart: always
image: mysql:8.0.20
container_name: mysql
volumes:
- ./data:/var/lib/mysql
- ./my.cnf:/etc/mysql/my.cnf
command:
--lower_case_table_names=1
--character-set-server=utf8
--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ports:
- "3306:3306"
environment:
- MYSQL_ROOT_PASSWORD=root123
- TZ=Asia/Shanghai 启动服务：
docker-compose up -d
下面使用客户端工具连接该数据库：
查看当前 master 状态：
show master status;
同样这里查询出来的 File 和 Position 下面主从复制时会用到。
创建用于主从复制的用户：
CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'replica123'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%'; FLUSH PRIVILEGES;
四、配置主主复制
MySQL 1 同步 MySQL 2
在 MySQL1 中执行 ：
change master to master_host='172.19.222.82',master_user='replica',master_password='replica123',master_log_file='mysql-bin.000003',master_log_pos=152;
注意：其中 master_log_file 和 master_log_pos 是上面部署 MySQL 2 时 show master status; 查询的结果。
启动同步进程：
start slave;
查看同步状态：
show slave status\G
看到 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 则表示启动成功。
MySQL 2 同步 MySQL 1
在 MySQL 2 中执行 ：
change master to master_host='172.19.222.20',master_user='replica',master_password='replica123',master_log_file='mysql-bin.000003',master_log_pos=811;
其中 master_log_file 和 master_log_pos 是上面部署 MySQL 1 时 show master status; 查询的结果。
启动同步进程：
start slave;
查看同步状态：
show slave status\G
同样观察 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 则表示启动成功。
五、测试主主复制
首先在 MySQL1 中创建数据库 testdb:
create database testdb;
然后在 MySQL 2 中查看数据库：
show databases;
可以正常查到 MySQL 1 中创建的数据库。
然后在 MySQL 2 中创建测试表：
use testdb; create table `test` ( `id` int not null auto_increment, `name` varchar(255) default null, primary key (`id`) ) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;
接着在 MySQL 1 中查看表：
use testdb; show tables;
可以正常看到 MySQL 2 创建的表。
然后在 MySQL 1 中写入一条测试数据：
insert into test(name) values('小明');
然后到 MySQL 2 中查看表数据：
select * from test;
可以正常查到 MySQL 1 写入的数据。
六、配置 HAProxy 负载 MySQL 服务
经过上面测试已经证明主主复制功能正常，数据无论写在哪个数据库上都能同步给另外一台，下面通过 HAProxy 负载均衡代理 MySQL 1 和 MySQL 2 ，对外提供统一的入口。
下载 HAProxy：
yum install -y haproxy
覆盖修改 /etc/haproxy/haproxy.cfg 配制文件，注意修改 backend mysql-apiserver 下的两个 MySQL 的 ip ：
cat > /etc/haproxy/haproxy.cfg << EOF
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
# to have these messages end up in /var/log/haproxy.log you will
# need to:
# 1) configure syslog to accept network log events. This is done
# by adding the '-r' option to the SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like the following can be added to
# /etc/sysconfig/syslog
#
# local2.* /var/log/haproxy.log
#
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
# turn on stats unix socket
stats socket /var/lib/haproxy/stats
#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
mode http
log global
option httplog
option dontlognull
option http-server-close
option forwardfor except 127.0.0.0/8
option redispatch
retries 3
timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout http-keep-alive 10s
timeout check 10s
maxconn 3000
#---------------------------------------------------------------------
# mysql apiserver frontend which proxys to the backends
#---------------------------------------------------------------------
frontend mysql-apiserver
mode tcp
bind *:3306
option tcplog
default_backend mysql-apiserver
#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
backend mysql-apiserver
mode tcp
balance roundrobin
server master1 172.19.222.20:3306 check
server master2 172.19.222.82:3306 check
#---------------------------------------------------------------------
# collection haproxy statistics message
#---------------------------------------------------------------------
listen stats
bind *:1080
stats auth admin:awesomePassword
stats refresh 5s
stats realm HAProxy\ Statistics
stats uri /admin?stats
EOF启动haproxy
systemctl start haproxy
设置开机自启：
systemctl enable haproxy
查看启动状态：
systemctl status haproxy
测试
通过 84 服务器连接 MySQL：
查看前面创建的数据库、表、数据：
写入一条数据：
insert into test(name) values("小红");分别到 MySQL 1和 MySQL 2查询数据：
都可以查询到数据，到此 MySQL 搭建双主复制服务 并 通过 HAProxy 负载均衡就结束了。
到此这篇关于MySQL 搭建双主复制服务 并 通过 HAProxy 负载均衡的文章就介绍到这了,更多相关MySQL 双主复制服务内容请搜索程序员之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持程序员之家！
相关文章
在做大型网站或者系统的时候，经常会遇到个问题就是批量插入或者修改数据库;今天这边不讲SqlBulkCopy，只简单讲sql自定义表类型,感兴趣的朋友可以了解下哦，希望本文对你有所帮助2013-01-01
下面通过图文并茂的方式给大家介绍通用设备管理信息系统数据库的创建过程，感兴趣的朋友一起学习2016-04-04
ERROR 1406 : Data too long for column 解决办法
导入数据的时候，mysql报错 ERROR 1406 : Data too long for column Data too long for column2011-04-04
这篇文章主要介绍了聊聊MySQL的COUNT(*)的性能,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧2020-11-11
这篇文章主要介绍了Mysql大表添加字段失败问题及解决,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教2024-04-04
这篇文章主要介绍了Mysql字符串字段判断是否包含某个字符串的2种方法,本文使用Like和find_in_set两种方法实现,需要的朋友可以参考下2015-01-01
mysql数据库没有增量备份的机制，当数据量太大的时候备份是一个很大的问题。还好mysql数据库提供了一种主从备份的机制，其实就是把主数据库的所有的数据同时写到备份数据库中。2010-06-06
我们在使用mysql的时候要注意这方面的问题,一个因为数据库字段命名不当造成的问题检查起来还是相当麻烦的,今天遇到这种问题,花费了很长时间才找到问题所在，这是无语2015-04-04
MySQL 5.7并行复制的思想简单易懂，本文就详细的介绍了MySQL5.7并行复制原理及实现，需要的朋友们下面随着小编来一起学习学习吧2021-06-06
对线上运行的mysql数据库来说,周期性做数据库备份具有重要的意义,一方面可以防止数据丢失,另一方面,备份的数据可以快速在不同的环境中使用、迁移,本文将给大家详细介绍mysql增量备份与恢复使用,需要的朋友可以参考下2023-09-09
最新评论