详解MySQL双主集群搭建：高可用性与数据同步的完美结合

目录

• 前提条件
• 搭建过程
• 测试双主复制
• 故障转移和监控
• 注意

搭建 MySQL 双主集群涉及多个配置步骤，以及对于可能出现的问题的理解和解决。下面将详细说明搭建过程的每个步骤。

回到顶部

前提条件

1. 环境准备：准备两台服务器（物理或虚拟），并确保它们可以互相通信（例如，通过内部网络）。
2. 软件准备：在两台服务器上安装相同版本的 MySQL。建议使用官方稳定版。
3. 备份：在生产环境中操作时，请务必备份所有重要数据。

回到顶部

搭建过程

1. 修改配置文件

服务器A（master1）配置：

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = your_database_name
sync_binlog = 1
binlog_format = mixed
relay_log = relay-bin
relay_log_index = relay-bin.index
auto_increment_increment = 2
auto_increment_offset = 1

# 为复制设置监听IP和端口（如果非默认3306端口）
# bind-address = your_server_ip
# port = your_port_number

服务器B（master2）配置：

[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-do-db = your_database_name
sync_binlog = 1
binlog_format = mixed
relay_log = relay-bin
relay_log_index = relay-bin.index
auto_increment_increment = 2
auto_increment_offset = 2

# 为复制设置监听IP和端口（如果非默认3306端口）
# bind-address = your_server_ip
# port = your_port_number

请替换 your_database_name 为你需要复制的数据库名称，如果需要复制多个数据库，可以使用多个 binlog-do-db 配置项，或者使用 replicate-do-db 在 SLAVE 端进行过滤。

注意：在实际部署时，根据网络安全和性能需要，可以调整监听IP和端口。

2. 创建复制用户

在两个服务器上的 MySQL 命令行执行以下 SQL 命令来创建一个专门用于复制的用户。

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'strong_password';
FLUSH PRIVILEGES;

确保替换 'replication_user' 为你希望使用的复制用户名，并设置 'strong_password' 为强密码。

3. 锁定表并获取二进制日志位置

在服务器A上执行：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

记录下返回的 File 和 Position 值。

在服务器B上也执行同样的步骤，但记录服务器B的值时不要忘记先在服务器A上执行 UNLOCK TABLES; 命令解锁表。

4. 配置复制

在服务器B上设置复制服务器A的数据：

CHANGE MASTER TO
MASTER_HOST='master1_ip_address',
MASTER_USER='replication_user',
MASTER_PASSWORD='strong_password',
MASTER_LOG_FILE='master1_binlog_file',
MASTER_LOG_POS=master1_binlog_position;

将 'master1_ip_address' 替换为服务器A的IP地址，'strong_password' 替换为之前设置的密码，以及 'master1_binlog_file' 和 master1_binlog_position 分别替换为在服务器A上执行 SHOW MASTER STATUS; 命令后获取的文件名和位置。

在服务器A上执行相同操作来设置服务器B的数据。

5. 启动复制

在两台服务器上分别执行：

sql复制代码

START SLAVE;

6. 检查复制状态

在两台服务器上执行以下命令检查复制状态：

sql复制代码

SHOW SLAVE STATUS \G;

确认以下两个字段的值为 Yes：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果不是 Yes，则查看 Last_Error 字段的错误信息，根据提示解决相应的问题。

7. 安装并配置 keepalived

在每台服务器上安装 keepalived，并编辑其配置文件（通常是 /etc/keepalived/keepalived.conf），设置虚拟 IP 地址、优先级和检查脚本等。

例如：

! Configuration File for keepalived

global_defs {
notification_email {
admin@yourdomain.com
}
notification_email_from keepalived@yourdomain.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}

vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.124.151.23
}
}

在另一台服务器上，你需要将 state 设置为 BACKUP，并相应地调整 priority。

确保 interface 指定了正确的网络接口，virtual_ipaddress 指定了你要使用的虚拟 IP 地址。

8. 启动 keepalived

在两台服务器上分别启动 keepalived 服务。

9. 测试故障转移

模拟一台服务器故障，检查 keepalived 是否能够成功将虚拟 IP 地址漂移到另一台服务器上，并且 MySQL 服务仍然可用。

回到顶部

测试双主复制

在两个 MySQL 服务器上创建或更新数据库中的数据，并验证这些更改是否成功复制到了另一台服务器上。

回到顶部

故障转移和监控

对于双主复制集群，需要考虑故障转移策略以及使用工具进行监控。你可以使用 MHA (Master High Availability Manager) 或其他类似工具来实现自动故障转移。同时，定期监控两台服务器的复制状态和健康状况非常重要，可以使用 Percona Monitoring and Management (PMM) 或自定义的监控脚本来完成这个任务。

回到顶部

注意

• 数据一致性：务必确保在开始搭建之前两个节点的数据是一致的。
• 自增长ID：设置 auto_increment_increment 和 auto_increment_offset 可以避免 ID 冲突。
• 冲突解决：在实际操作中可能会遇到复制冲突，例如，在两个主节点上同时修改同一行数据。你需要为这种情况设置解决策略。
• 安全性：复制用户需要足够的安全保护，包括限制访问主机和使用强密码。
• 网络延迟：网络延迟可能会导致数据复制延迟，监控并确保网络连接质量是良好的实践。
• 备份策略：双主复制不应该替代数据备份。你应该实施一个稳健的备份和恢复策略来保护数据。

链接：https://www.cnblogs.com/ydswin/p/18060100

                                                              （版权归原作者所有，侵删）

5 万字 160道MySQL经典面试题总结（2024修订版）

需要完整资料的看这里

扫码回复666 根据提示 添加小助理领取：160道MySQL面试题

什么是MySQL？

MySQL是⼀个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流⾏的关系型数据库管理系统之⼀，在 WEB 应⽤⽅⾯，MySQL是最好的RDBMS (Relational Database Management System，关系数据库管理系统) 应⽤软件之⼀。在Java企业级开发中⾮常常⽤，因为 MySQL 是开源免费的，并且⽅便扩展。

MySql, Oracle,Sql Service的区别？

1. Sql Service只能在Windows上使⽤，⽽MySql和Oracle可以在其他系统上使⽤， ⽽且可以⽀持数据库不同系统之间的移植

2. MySql开源免费的，Sql Service和Oracle要钱。

3. 我从⼩到⼤排序哈，MySql很⼩，Sql Service居中，Oracle最⼤

4. Oracle⽀持⼤并发量，⼤访问量，Sql Service还⾏，⽽MySql的话压⼒没这么⼤，因此现在的MySql的话最好是要使⽤集群或者缓存来搭配使⽤

5. Oracle⽀持多⽤户不同权限来进⾏操作，⽽MySql只要有登录权限就可操作全部数据库

6. 安装所⽤的空间差别也是很⼤的，Mysql安装完后才⼏百M⽽Oracle有⼏G左右，且使⽤的时候Oracle占⽤特别⼤的内存空间和其他机器性能。

7. 做分⻚的话，MySql使⽤Limit，Sql Service使⽤top，Oracle使⽤row

8. Oracle没有⾃动增⻓类型，Mysql和Sql Service⼀般使⽤⾃动增⻓类型

什么是索引?

索引是一种特殊的文件(innoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录索引是一个文件，它是要占据物理空间的。

索引的使用场景？

1.当数据多且字段值有相同的值得时候用普通索引。

2.当字段多且字段值没有重复的时候用唯一索引。

3.当有多个字段名都经常被查询的话用复合索引。

4.普通索引不支持空值，唯一索引支持空值。

5.但是，若是这张表增删改多而查询较少的话，就不要创建索引了，因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引，

6.若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集

7.若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集,

8.若是删，则会把索引中以这个字段为名的索引的子集删掉。

9.所以，会对增删改的执行减缓速度，

10.所以，若是这张表增删改多而查询较少的话，就不要创建索引了，

11.更新太频繁地字段不适合创建索引。

12.不会出现在where条件中的字段不该建立索引。

B树和B+树的区别?

在B树中，你可以将键和值存放在内部节点和叶子节点;但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

非聚簇索引一定会回表查询吗?

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。就像酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他使用完毕才可以再次使用。

什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法：

1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率
3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率

为什么要使用视图？什么是视图？

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

SQL语句主要分为哪几类?

• 数据定义语言DDL(Data Ddefinition Language)CREATE，DROP，ALTER主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。
• 数据查询语言DQL(Data Query Language)SELECT这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。
• 数据操纵语言DML(Data Manipulation Language)INSERT，UPDATE，DELETE主要为以上操作 即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。
• 数据控制功能DCL(DataControlLanguage)GRANT，REVOKE，COMMIT，ROLLBACK主要为以上操作 即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

资料整理自网络，仅作免费交流分享，侵权删！

需要完整资料的看这里

扫码回复666 根据提示 添加小助理领取：160道MySQL面试题

本文链接：https://www.yunweipai.com/45961.html