阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL双主（主主）架构方案

204次阅读
没有评论

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

在企业中，数据库高可用一直是企业的重中之重，中小企业很多都是使用 MySQL 主从方案，一主多从，读写分离等，但是单主存在单点故障，从库切换成主库需要作改动。因此，如果是双主或者多主，就会增加 mysql 入口，增加高可用。不过多主需要考虑自增长 ID 问题，这个需要特别设置配置文件，比如双主，可以使用奇偶，总之，主之间设置自增长 ID 相互不冲突就能完美解决自增长 ID 冲突问题。

主从同步复制原理

在开始之前，我们先来了解主从同步复制原理。

复制分成三步：

1. master 将改变记录到二进制日志 (binary log) 中（这些记录叫做二进制日志事件，binary log events）；

2. slave 将 master 的 binary log events 拷贝到它的中继日志(relay log)；

3. slave 重做中继日志中的事件，将改变反映它自己的数据。

下图描述了这一过程：

MySQL 双主（主主）架构方案

该过程的第一部分就是 master 记录二进制日志。在每个事务更新数据完成之前，master 在二日志记录这些改变。MySQL 将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master 通知存储引擎提交事务。

下一步就是 slave 将 master 的 binary log 拷贝到它自己的中继日志。首先，slave 开始一个工作线程——I/ O 线程。I/ O 线程在 master 上打开一个普通的连接，然后开始 binlog dump process。Binlog dump process 从 master 的二进制日志中读取事件，如果已经跟上 master，它会睡眠并等待 master 产生新的事件。I/ O 线程将这些事件写入中继日志。

SQL slave thread 处理该过程的最后一步。SQL 线程从中继日志读取事件，更新 slave 的数据，使其与 master 中的数据一致。只要该线程与 I / O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小。

此外，在 master 中也有一个工作线程：和其它 MySQL 的连接一样，slave 在 master 中打开一个连接也会使得 master 开始一个线程。

MySQL5.6 以前的版本复制过程有一个很重要的限制——复制在 slave 上是串行化的，也就是说 master 上的并行更新操作不能在 slave 上并行操作。MySQL5.6 版本参数 slave-parallel-workers=1 表示启用多线程功能。

MySQL5.6 开始，增加了一个新特性，是加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。

官方文档：http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html

MySQL 双主（主主）架构方案思路是:

1. 两台 mysql 都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用；

2.masterA 是 masterB 的主库，masterB 又是 masterA 的主库，它们互为主从；

3. 两台主库之间做高可用, 可以采用 keepalived 等方案（使用 VIP 对外提供服务）；

4. 所有提供服务的从服务器与 masterB 进行主从同步（双主多从）;

5. 建议采用高可用策略的时候，masterA 或 masterB 均不因宕机恢复后而抢占 VIP（非抢占模式）；

这样做可以在一定程度上保证主库的高可用, 在一台主库 down 掉之后, 可以在极短的时间内切换到另一台主库上（尽可能减少主库宕机对业务造成的影响），减少了主从同步给线上主库带来的压力；

但是也有几个不足的地方:

1.masterB 可能会一直处于空闲状态（可以用它当从库，负责部分查询）；

2. 主库后面提供服务的从库要等 masterB 先同步完了数据后才能去 masterB 上去同步数据，这样可能会造成一定程度的同步延时；

架构的简易图如下:

MySQL 双主（主主）架构方案

主主环境（这里只介绍 2 台主的配置方案）:

1.CentOS 6.8 64 位 2 台：masterA（192.168.10.11），masterB（192.168.10.12）

2. 官方 Mysql5.6 版本

搭建过程：

1. 安装 MySQL 服务（建议源码安装）

1.1 yum 安装依赖包

yum -y install make gcc gcc-c++  ncurses-devel bison openssl-devel

1.2 添加 MySQL 所需要的用户和组

groupadd -g 27 mysql
adduser -u 27 -g mysql -s /sbin/nologin mysql

1.3 下载 MySQL 源码包

mkdir -p /data/packages/src
cd /data/packages/
wget http://distfiles.macports.org/cmake/cmake-3.2.3.tar.gz
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.34.tar.gz

1.4 创建 mysql 数据目录

mkdir -p /usr/local/mysql/data

1.5 解压编译安装 cmake、MySQL

cd /data/packages/src
tar -zxvf ../cmake-3.2.3.tar.gz
cd cmake-3.2.3/
./bootstrap
gmake
make install
cd ../
tar xf  mysql-5.6.34.tar.gz
cd mysql-5.6.34
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc \
-DWITH_SSL=bundled -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \
-DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_DATADIR=/usr/local/mysql/data
make && make install

1.6 添加开机启动脚本

cp support-files/mysql.server /etc/rc.d/init.d/mysqld

1.7 添加 masterA 配置文件 /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
basedir = /usr/local/mysql
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysql.pid
log-error = /usr/local/mysql/data/mysql.err

server-id = 1
auto_increment_offset = 1
auto_increment_increment = 2                                            #奇数 ID

log-bin = mysql-bin                                                     #打开二进制功能,MASTER 主服务器必须打开此项
binlog-format=ROW
binlog-row-image=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=1024M                                                   #binlog 单文件最大值

replicate-ignore-db = mysql                                             #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = zabbix

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8'                                           #连接时执行的 SQL
character-set-server=utf8                                               #服务端默认字符集
wait_timeout=1800                                                       #请求的最大连接时间
interactive_timeout=1800                                                #和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql 模式
max_allowed_packet = 10M
bulk_insert_buffer_size = 8M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K

skip-name-resolve
slow_query_log=1
long_query_time = 6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
max_allowed_packet = 16M

[mysqld_safe]

1.8 特别参数说明

log-slave-updates = true     #将复制事件写入 binlog, 一台服务器既做主库又做从库此选项必须要开启
#masterA 自增长 ID
auto_increment_offset = 1
auto_increment_increment = 2                                            #奇数 ID
#masterB 自增加 ID
auto_increment_offset = 2
auto_increment_increment = 2                                            #偶数 ID

1.9 添加 masterB 配置文件 /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
basedir = /usr/local/mysql
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysql.pid
log-error = /usr/local/mysql/data/mysql.err

server-id = 2
auto_increment_offset = 2
auto_increment_increment = 2                                            #偶数 ID

log-bin = mysql-bin                                                     #打开二进制功能,MASTER 主服务器必须打开此项
binlog-format=ROW
binlog-row-image=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=1024M                                                   #binlog 单文件最大值

replicate-ignore-db = mysql                                             #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = zabbix

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8'                                           #连接时执行的 SQL
character-set-server=utf8                                               #服务端默认字符集
wait_timeout=1800                                                       #请求的最大连接时间
interactive_timeout=1800                                                #和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql 模式
max_allowed_packet = 10M
bulk_insert_buffer_size = 8M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K

skip-name-resolve
slow_query_log=1
long_query_time = 6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
max_allowed_packet = 16M

[mysqld_safe]

1.10 初始化 MySQL

cd /usr/local/mysql
scripts/mysql_install_db --user=mysql

1.11 为启动脚本赋予可执行权限并启动 MySQL

chmod +x /etc/rc.d/init.d/mysqld
/etc/init.d/mysqld start

2. 配置主从同步

2.1 添加主从同步账户

masterA 上：

mysql> grant replication slave on *.* to 'repl'@'192.168.10.12' identified by '123456';
mysql> flush privileges;

masterB 上：

mysql> grant replication slave on *.* to 'repl'@'192.168.10.11' identified by '123456';
mysql> flush privileges;

2.2 查看主库的状态

masterA 上：

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000003 |      120 |              |                  |                   |

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

1 row in set (0.00 sec)

masterB 上

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000003 |      437 |              |                  |                   |

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

1 row in set (0.00 sec)

2.3 配置同步信息：

masterA 上：

mysql> change master to master_host='192.168.10.12',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=437;

mysql> start slave;

mysql> show slave status\G;

 

显示有如下状态则正常：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

masterB 上：

# 本人是测试环境，可以保证没数据写入，否则需要的步骤是：先 masterA 锁表 –>masterA 备份数据 –>masterA 解锁表 –>masterB 导入数据 –>masterB 设置主从 –> 查看主从

mysql> change master to master_host='192.168.10.11',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120;

start slave;

mysql> show slave status\G;

显示有如下状态则正常：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3. 测试主从同步

3.1 在 masterA 上创建一个数据库测试同步效果

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

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

4 rows in set (0.00 sec)

mysql> create database test01;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

| test01             |

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

5 rows in set (0.00 sec)

mysql> quit

Bye

[root@masterA data]#

3.2 到 masterB 查看是否已经同步创建数据库

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

| test01             |

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

5 rows in set (0.00 sec)

mysql> quit

Bye

[root@masterB data]#

4. 开启 MySQL5.6 的 GTID 功能，实现多线程主从复制

masterA 和 masterB 分别执行如下命令：

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to MASTER_AUTO_POSITION=1;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

5. 遇到的问题

一种主从报错折腾了我半天：

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not open log file’

后面修改主从同步相关 参数，确认原因是 my.cnf 增加了如下参数：

log-bin = mysql-bin

relay-log = mysql-bin

MySQL 双主（主主）架构方案

从正常主主同步时的二进制日志文件显示，有 2 套二进制日志。因此推断上面 2 个参数导致不能产生 2 套二进制文件，故导致二进制文件错乱和丢失。

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

正文完
星哥玩云-微信公众号
post-qrcode
MongoDB MySQL MySQL主主架构 MySQL双主（主主）架构方案 MySQL双主架构 NoSQL Oracle Oracle教程 Oracle数据库 PostgreSQL 数据库
发表至： 数据库技术
2022-01-22
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-22发表，共计9121字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
Oracle 11gR2中的自动并行度
Oracle创建、删除表空间、用户
Oracle数据库之单行函数详解
Oracle虚拟索引
MySQL的多表联查
使用network_link复制Oracle数据库
GoldenGate对unused字段故障总结
数据库升级后报ORA-06550、PLS-00306问题解决
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3456731
评论数
4
文章搜索
热门文章
基于开源MaxKB构建大语言模型的本地知识库系统

基于开源MaxKB构建大语言模型的本地知识库系统

基于开源 MaxKB 构建大语言模型的本地知识库系统 什么是 MaxKB MaxKB 是一款基于 LLM（La...
获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能 AI 工具通过 API 和 KEY 调用的方法 前言 随着人工智能技术的飞速发展，AI 工具...
安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件 ChatALL（齐叨）来聚合各大人工智能工具 前言 如果你跟我一样无论遇到什么问题都要询问人工智...
给你的NAS无限可能，安装小晓雅全家桶影音库

给你的NAS无限可能，安装小晓雅全家桶影音库

给你的 NAS 无限可能，安装小晓雅全家桶影音库 家庭存储和娱乐的需求日益增长。无论是高清电影、电视剧，还是音...
vmware下的网卡分配问题

vmware下的网卡分配问题

导读 vmware 虚拟机下 linux centos6.6 只有 lo，没有 eth0 网卡、随机分配 ip...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
内网之mobaxterm远程访问

内网之mobaxterm远程访问

导读 MobaXterm 是您的远程计算的终极工具箱。在单个 Windows 应用程序中，它提供了大量为程序员...
防火墙的三大方向演进

防火墙的三大方向演进

导读 当前绝大多数企业都部署有防火墙设备，然而如何利用好这些安全防护设备却并非全部企业都擅长的。因此选择什么样...
一个企业的大数据分析平台的构建方法

一个企业的大数据分析平台的构建方法

导读 在搭建大数据分析平台之前，要先明确业务需求场景以及用户的需求，通过大数据分析平台，想要得到哪些有价值的信...
详解：Shell脚本变量判断参数命令

详解：Shell脚本变量判断参数命令

系统变量 $n 传递给脚本或函数的参数。n 是一个数字，表示第几个参数。例如，第一个参数是 $1，第二个参数是...
如何在 Linux 上永久挂载一个 Windows 共享

如何在 Linux 上永久挂载一个 Windows 共享

导读 如果你已经厌倦了每次重启 Linux 就得重新挂载 Windows 共享，读读这个让共享永久挂载的简单方...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中