【MySQL】主从复制

文章目录

• 一、前言
• 二、MySQL主从复制
• • 1. 主从复制原理
  • 2. 主从复制同步方式
  • • 2.1 MySQL基于语句复制
    • • 2.1.1 在主服务器上开启二进制日志功能，记录更新操作到二进制日志中
      • 2.1.2 在从服务器上连接主服务器，开始同步数据
      • 2.1.3 在主服务器上更新数据
      • 2.1.4 在从服务器上检查更新是否同步过来：
    • 2.2 基于行复制
    • • 2.2.1 在主服务器上创建一个新的数据库和表
      • 2.2.2 向主服务器的数据库中插入一些行数据
      • 2.2.3 启用 MySQL 的二进制日志(binlog)
      • 2.2.4 在从服务器上启用基于行的复制
      • 2.2.5 在从服务器上添加主服务器的信息
      • 2.2.6 开始基于行的复制
      • 2.2.7 在主服务器上更新一些行数据
      • 2.2.8 在从服务器上检查数据同步情况
    • 2.3 混合型复制
    • • 2.3.1 在主服务器上创建一个新的数据库和表：
      • 2.3.2 向主服务器的数据库中插入一些行数据：
      • 2.3.3 启用 MySQL 的二进制日志(binlog)：
      • 2.3.4 在从服务器上启用混合型复制：
      • 2.3.5 在从服务器上添加主服务器的信息：
      • 2.3.6 开始混合型复制：
      • 2.3.7 在主服务器上更新一些行数据和插入数据：
      • 2.3.8 在从服务器上检查数据同步情况：
  • 3. 主从复制应用场景
  • • 3. 1 数据备份
    • 3. 2 负载均衡
    • 3. 3 读写分离
• 三、MySQL高可用
• • 1. 高可用概念
  • 2. MySQL高可用方案
  • • 1. 基于主从复制的高可用方案
    • 2. 基于主主复制的高可用方案
    • 3. 基于MHA的MySQL高可用方案
    • 4. 基于Galera Cluster的高可用方案
• 四、MySQL主从复制与高可用的整合
• • 1. 前置条件
  • 2. MHA+MySQL主从复制整合部署
  • 3. MHA+MySQL主主复制整合部署
  • 4. MHA+Galera Cluster整合部署
• 五、总结和展望

一、前言

MySQL是一款关系型数据库，被广泛应用于各种互联网应用中。随着业务的不断扩大，数据量不断增加，如何让MySQL具有更好的可用性和容灾能力成为了越来越重要的问题。本文将重点介绍MySQL主从复制和高可用性的实现，旨在为MySQL的使用者提供指导和帮助。

二、MySQL主从复制

1. 主从复制原理

MySQL主从复制是指将一个MySQL服务器的数据复制到另一个MySQL服务器上的功能。其中，一个MySQL服务器被称为“主服务器”，另一个MySQL服务器被称为“从服务器”。主服务器将数据更新记录到二进制日志(binlog)中，然后从服务器通过读取主服务器上的binlog日志文件，获得需要更新的数据并进行相应的操作。这样，从服务器上的数据得以与主服务器保持同步，达到数据备份、负载均衡、读写分离等目的。

2. 主从复制同步方式

数据库的数据同步可以通过以下方式实现：

2.1 MySQL基于语句复制

MySQL基于语句复制是MySQL数据库的一种主从同步方式，主服务器将更新操作转化成SQL语句并记录在binlog中，从服务器连接主服务器轮询binlog，解析binlog中需要同步的SQL语句并在从服务器上执行这些SQL语句，从而实现主从同步。
以下是基于语句复制的操作示例：

2.1.1 在主服务器上开启二进制日志功能，记录更新操作到二进制日志中

在主服务器的my.cnf配置文件中添加以下内容：

[mysqld] log-bin = mysql-bin #开启二进制日志功能，并指定保存日志的文件名 server-id = 1 #设置服务器ID 

重启MySQL使配置文件生效。

2.1.2 在从服务器上连接主服务器，开始同步数据

在从服务器命令行下，输入以下命令：

mysql> CHANGE MASTER TO     -> MASTER_HOST = 'master_host_ip',     -> MASTER_USER = 'replication',     -> MASTER_PASSWORD = 'password',     -> MASTER_LOG_FILE = 'mysql-bin.000001',     -> MASTER_LOG_POS = 113; 

其中，MASTER_HOST为主服务器IP地址，MASTER_USER和MASTER_PASSWORD为主服务器的同步用户和密码，MASTER_LOG_FILE和MASTER_LOG_POS为从服务器上次同步数据后，binlog中已经同步到的位置。

然后，启动从服务器复制进程：

mysql> START SLAVE; 

2.1.3 在主服务器上更新数据

在主服务器命令行下，输入以下命令：

mysql> INSERT INTO test_db.test_table(col1, col2) VALUES(1, 'one'); 

这条语句在主服务器上执行后，会记录在主服务器的binlog中。

2.1.4 在从服务器上检查更新是否同步过来：

在从服务器命令行下，输入以下命令：

mysql> SELECT * FROM test_db.test_table; 

如果能查询到刚才在主服务器上插入的数据，则表明成功进行了主从同步。

需要注意的是，基于语句复制方式同步数据时，主从服务器上的数据结构必须一致，否则同步会出现问题。同时，在进行主从同步时，也需要注意数据一致性和安全性等问题。

2.2 基于行复制

基于行复制：主服务器将更新的行数据记录到binlog中，从服务器定期轮询主服务器的binlog，获取需要同步的行数据，并在从服务器上更新这些行数据。
以下是基于行复制的操作示例：

2.2.1 在主服务器上创建一个新的数据库和表

CREATE DATABASE mydatabase; USE mydatabase; CREATE TABLE mytable ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); 

2.2.2 向主服务器的数据库中插入一些行数据

INSERT INTO mytable (id, name, age) VALUES (1, 'John', 30); INSERT INTO mytable (id, name, age) VALUES (2, 'Mary', 25); 

2.2.3 启用 MySQL 的二进制日志(binlog)

在 MySQL 配置文件 my.cnf 中添加以下信息：

[mysqld] log-bin=mysql-bin server-id=1 

2.2.4 在从服务器上启用基于行的复制

在 MySQL 配置文件 my.cnf 中添加以下信息：

[mysqld] server-id=2 binlog-format=ROW log-slave-updates=TRUE relay-log=relay-bin 

2.2.5 在从服务器上添加主服务器的信息

CHANGE MASTER TO MASTER_HOST='主服务器的IP地址', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='主服务器的binlog文件名', MASTER_LOG_POS=主服务器的binlog偏移量; 

其中，replication_user 和 replication_password 是主服务器上的授权访问从服务器的账号和密码；主服务器的binlog文件名和偏移量可以通过 SHOW MASTER STATUS 命令获得。

2.2.6 开始基于行的复制

START SLAVE; 

2.2.7 在主服务器上更新一些行数据

UPDATE mytable SET age=35 WHERE id=1; 

2.2.8 在从服务器上检查数据同步情况

SELECT * FROM mydatabase.mytable; 

可以发现，从服务器上的 mytable 表的第一条数据的 age 字段已经更新为 35。

需要注意的是，MySQL 的基于行的复制模式不仅仅可以用于主从复制，也可以用于环形复制和分层复制。在使用的时候，需要根据具体的需求选择合适的复制模式。

2.3 混合型复制

混合型复制：此方法是基于语句复制和行复制的一个混合。主服务器根据SQL语句的不同，选择将语句或者行数据记录到binlog中。从服务器同样会根据SQL语句的不同，选择方式进行同步。

以下是基于混合型复制的操作示例：

2.3.1 在主服务器上创建一个新的数据库和表：

CREATE DATABASE mydatabase; USE mydatabase; CREATE TABLE mytable ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); 

2.3.2 向主服务器的数据库中插入一些行数据：

INSERT INTO mytable (id, name, age) VALUES (1, 'John', 30); INSERT INTO mytable (id, name, age) VALUES (2, 'Mary', 25); 

2.3.3 启用 MySQL 的二进制日志(binlog)：

在 MySQL 配置文件 my.cnf 中添加以下信息：

[mysqld] log-bin=mysql-bin server-id=1 

2.3.4 在从服务器上启用混合型复制：

在 MySQL 配置文件 my.cnf 中添加以下信息：

[mysqld] server-id=2 log-slave-updates=TRUE binlog-format=MIXED relay-log=relay-bin 

2.3.5 在从服务器上添加主服务器的信息：

CHANGE MASTER TO MASTER_HOST='主服务器的IP地址', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='主服务器的binlog文件名', MASTER_LOG_POS=主服务器的binlog偏移量; 

其中，replication_user 和 replication_password 是主服务器上的授权访问从服务器的账号和密码；主服务器的binlog文件名和偏移量可以通过 SHOW MASTER STATUS 命令获得。

2.3.6 开始混合型复制：

START SLAVE; 

2.3.7 在主服务器上更新一些行数据和插入数据：

UPDATE mytable SET age=35 WHERE id=1; INSERT INTO mytable (id, name, age) VALUES (3, 'Tom', 20); 

2.3.8 在从服务器上检查数据同步情况：

SELECT * FROM mydatabase.mytable; 

可以发现，从服务器上的 mytable 表的前两条数据是通过行复制同步而来的，而第三条数据是通过语句复制同步而来的。

需要注意的是，在混合型复制中，如果 SQL 语句无法被正确解析，就会使用行复制进行数据同步。因此，如果在使用混合型复制的过程中出现了数据不一致的情况，需要检查主从服务器之间的数据同步方式是否一致，以及 SQL 语句是否可以被正确解析。

3. 主从复制应用场景

MySQL主从复制可以应用于以下场景：

3. 1 数据备份

主从复制可以实现主服务器的数据备份。从服务器可以作为备份服务器，当主服务器数据丢失或出现故障时，可以快速启用备份服务器以恢复数据。

以下是数据备份的操作示例：

1. 在主服务器上启用二进制日志(binlog)功能：
   在 MySQL 配置文件 my.cnf 中添加以下信息：

[mysqld] log-bin=mysql-bin server-id=1 

2. 在从服务器上启用基于语句的复制：
   在 MySQL 配置文件 my.cnf 中添加以下信息：

[mysqld] server-id=2 binlog-format=STATEMENT log-slave-updates=TRUE relay-log=relay-bin 

3. 将主服务器的数据复制到从服务器：

在从服务器上添加主服务器的信息：

CHANGE MASTER TO MASTER_HOST='主服务器的IP地址', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='主服务器的binlog文件名', MASTER_LOG_POS=主服务器的binlog偏移量; 

其中，replication_user 和 replication_password 是主服务器上的授权访问从服务器的账号和密码；主服务器的binlog文件名和偏移量可以通过 SHOW MASTER STATUS 命令获得。

4. 然后，在从服务器上启动复制流程：

START SLAVE; 

5. 手动备份数据：
   在从服务器上，使用 mysqldump 命令备份数据库数据到本地文件，例如：

mysqldump -u root -p mydatabase > mydatabase.sql 

6. 自动备份数据：
   对于自动备份，可以通过配置 crontab 定时任务，在特定时间自动备份数据库数据。例如：

0 0 * * * mysqldump -u root -p mydatabase > /backup/mydatabase-`date +"%Y%m%d"`.sql 

以上命令指定在每天 0 点执行备份操作，并将备份文件保存在 /backup 目录下，以日期为后缀进行命名，如 mydatabase-20211010.sql。

需要注意的是，备份文件需要存储在安全的地方，以免数据泄露或丢失。另外，备份操作可能会影响服务器的性能，因此需要根据实际情况选择合适的备份时间。

3. 2 负载均衡

主从复制可以通过将查询请求转发到从服务器上进行读操作，以减轻主服务器的负载，提高整体的并发处理能力。

以下是负载均衡的操作示例：

1. 在主服务器上启用二进制日志(binlog)功能：

在 MySQL 配置文件 my.cnf 中添加以下信息：

[mysqld] log-bin=mysql-bin server-id=1 

2. 在从服务器上启用基于语句的复制：

在 MySQL 配置文件 my.cnf 中添加以下信息：

[mysqld] server-id=2 binlog-format=STATEMENT log-slave-updates=TRUE relay-log=relay-bin 

3. 将主服务器的数据复制到从服务器：

在从服务器上添加主服务器的信息：

CHANGE MASTER TO MASTER_HOST='主服务器的IP地址', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='主服务器的binlog文件名', MASTER_LOG_POS=主服务器的binlog偏移量; 

其中，replication_user 和 replication_password 是主服务器上的授权访问从服务器的账号和密码；主服务器的binlog文件名和偏移量可以通过 SHOW MASTER STATUS 命令获得。

然后，在从服务器上启动复制流程：

START SLAVE; 

4. 配置负载均衡器：

在负载均衡器上，配置将读请求转发到从服务器上。这可以通过负载均衡软件（如 Nginx、HAProxy、LVS 等）来实现。以 Nginx 为例，可以在其配置文件中添加如下配置：

upstream mysql_servers { server 192.168.1.101:3306; server 192.168.1.102:3306; }  server { listen 3306; proxy_pass mysql_servers; } 

以上配置指定将所有进入 3306 端口的请求转发到 192.168.1.101 和 192.168.1.102 上的 MySQL 服务器上，从而实现负载均衡。

需要注意的是，当负载均衡器将读请求转发到从服务器上进行处理时，可能会出现数据不一致的问题。因为主服务器在写操作时会同步到从服务器，但在读操作时可能尚未同步，导致从服务器上的数据不是最新的。因此，需要根据实际情况选择合适的负载均衡策略，并在必要时通过主从复制来保证数据一致性。

3. 3 读写分离

通过将写操作转发到主服务器上进行处理，而将读操作转发到从服务器上进行，可以解决因为读写冲突带来的性能问题。

以下是读写分离的操作示例：

1. 配置主服务器

在主服务器上，需要将binlog_format设置为ROW，这允许从服务器可以执行复制操作，并确保数据的完整性。

代码示例：

mysql> SET GLOBAL binlog_format = 'ROW'; 

2. 创建从服务器证书

在主服务器上，需要创建用于从服务器与主服务器进行通信的证书。

代码示例：

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; mysql> FLUSH PRIVILEGES; mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; 

3. 配置从服务器

在从服务器上，需要运行CHANGE MASTER TO操作，将从服务器与主服务器进行连接。

代码示例：

mysql> CHANGE MASTER TO     -> MASTER_HOST='master_host_name',     -> MASTER_USER='repl',     -> MASTER_PASSWORD='password',     -> MASTER_LOG_FILE='recorded_log_file_name',     -> MASTER_LOG_POS=recorded_log_position; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G 

注意，请将MASTER_HOST替换为主服务器的IP地址或主机名，MASTER_USER和MASTER_PASSWORD替换为在主服务器上创建的用户和密码，MASTER_LOG_FILE和MASTER_LOG_POS替换为SHOW MASTER STATUS输出的值。

4. 测试

现在，您可以在主服务器上进行写操作并在从服务器上进行读操作。

代码示例：

mysql> INSERT INTO MyTable VALUES (1,'Hello'); mysql> SELECT * FROM MyTable; 

这将在主服务器上插入数据，并且从服务器上可以读取该数据。

请注意，为了保证同步性能，我们通常会将主服务器和从服务器部署在不同的机器上，并且使用高速网络连接进行通信。

三、MySQL高可用

1. 高可用概念

高可用是指系统能够在发生单点故障或意外中断时保持正常的运行状态，即使整个系统或部分系统组件或节点出现故障，这些部分都无法影响整个系统的正常运转。

2. MySQL高可用方案

1. 基于主从复制的高可用方案

主从复制是通过将写操作发送到主节点并将读操作发送到一个或多个从节点来提高MySQL可用性的一种解决方案。当主节点出现故障时，从节点可以接管主节点的职责。然而，这种方案的弱点在于，主节点的单点故障可能导致从节点的数据不一致问题。因此，必须定期检查主从同步状态。

2. 基于主主复制的高可用方案

主主复制是使用两个节点来提高MySQL可用性的一种解决方案，每个节点都可以作为主节点和从节点。当一个节点出现故障时，其他节点可以继续服务。这种方案需要更多的硬件资源，但其数据一致性和容错性要比主从复制方案更高。

3. 基于MHA的MySQL高可用方案

MHA（Master High Availability）是一种基于主从复制的解决方案，其提供了自动故障检测和资源分配的功能。当主节点出现故障时，MHA会自动切换到一个可用的从节点，以确保MySQL的可用性。MHA能够在多个节点之间实现故障切换和数据库服务的自动提供。

4. 基于Galera Cluster的高可用方案

Galera Cluster是一个集群解决方案，它提供了高可用性和可扩展性。Galera Cluster通过同步多个节点上的数据来确保数据一致性。它通过多个节点共享数据来防止单点故障，并且可以在出现故障时自动执行故障恢复。

四、MySQL主从复制与高可用的整合

1. 前置条件

在进行MySQL主从复制与高可用整合之前，您需要具备以下条件：

• 熟悉Linux操作系统和MySQL数据库的安装和配置
• 至少运行了两个MySQL实例，其中一个作为主数据库(master)，另外一个或多个作为备份数据库(slave)
• 熟悉MHA和Galera Cluster的架构和使用

2. MHA+MySQL主从复制整合部署

在MySQL主从复制中，一个数据库实例（主库）将更改同步到一个或多个备库。在出现主库故障的情况下，备库可以接管主库的功能，从而实现高可用性。MHA是一个广泛使用的管理MySQL集群高可用性的工具，它可以自动切换主从复制中的主从角色。

步骤如下：

1. 安装MHA软件包并将配置文件复制到/etc目录下。

2. 配置MHA，主要包括：

• 自动故障转移，即当主库挂掉的时候，自动将备库提升为主库
• 监控MySQL实例的状态
• 在手动故障转移发生时指定新主库

3. 确认所有MySQL实例都可以正常连接，并更新MHA配置文件。

4. 安装ssh，以协调MHA和MySQL的自动化角色转换。

5. 启动MHA Manager，即执行如下命令：

/usr/local/bin/masterha_manager --conf=/etc/app1.cnf 

6. 验证MHA是否可用，并进行主从切换的测试。

3. MHA+MySQL主主复制整合部署

MySQL主主复制是指将一个数据库实例的更改同步到另一个数据库实例，并且两个实例都可以处理客户端请求的过程。MHA可以帮助您管理MySQL主主复制实例的故障转移，从而实现高可用性。

步骤如下：

1. 安装MySQL主主复制，并确保两个实例都可以正常连接。

2. 安装MHA并将配置文件复制到/etc目录下。

3. 配置MHA，主要包括：

• 自动故障转移，即当一个MySQL实例挂掉的时候，自动将请求发送到另一个实例
• 监控MySQL实例的状态
• 在手动故障转移发生时指定新的MySQL实例

4. 确认所有MySQL实例都可以正常连接，并更新MHA配置文件。

5. 安装ssh，以协调MHA和MySQL的自动化角色转换。

6. 启动MHA Manager，即执行如下命令：

/usr/local/bin/masterha_manager --conf=/etc/app1.cnf 

8. 确认MHA是否可用，并进行主从切换的测试。

4. MHA+Galera Cluster整合部署

Galera Cluster是一个多主控制的集群，在Galera Cluster中，每个节点都是主库，并且更改可以在整个集群中传播。使用MHA和Galera Cluster整合，可以实现高可用性和自动化故障转移。

步骤如下：

1. 安装Galera Cluster并创建相关的数据库和用户。

2. 配置Galera Cluster集群节点的IP和端口号。

3. 安装MHA并将配置文件复制到/etc目录下。

4. 配置MHA，主要包括：

• 自动故障转移，即当一个Galera Cluster节点挂掉的时候，自动将请求发送到另一个节点
• 监控Galera Cluster节点的状态
• 在手动故障转移发生时指定新的Galera Cluster节点

5. 确认所有Galera Cluster节点都可以正常连接，并更新MHA配置文件。

6. 安装ssh，以协调MHA和Galera Cluster的自动化角色转换。

7. 启动MHA Manager，即执行如下命令：

/usr/local/bin/masterha_manager --conf=/etc/app1.cnf 

8. 确认MHA是否可用，并进行故障转移的测试。

在本文中，我们提供了三种MHA和MySQL高可用性方案的整合方法。您可以选择最适合您的需求和环境的方案来实现高可用性和自动化故障转移。

五、总结和展望

本篇博客介绍了MySQL高可用性的常见解决方案，包括负载均衡、主从复制、主主复制以及结合MHA和Galera Cluster的整合方案。高可用性对于数据库系统至关重要，它可以确保用户的请求始终得以满足，并且降低系统停机时间的风险。

负载均衡是通过将请求分发到多个数据库节点来实现高可用性和高效性。这种方法可以在系统中增加节点，从而提高容量和性能。但是，它的实现需要一些专业知识，并且在部署中需要考虑多个因素以确保其可靠性。

MySQL主从复制是一种常见的高可用性解决方案，它将一个数据库实例（主库）的更改同步到一个或多个备库（从库）中。当主库出现故障时，从库可以接管其功能，从而保证系统的持续运行。但是，在这种方案中，从库一般都是只读的，我们可以选择结合MHA等工具实现自动故障转移。

MySQL主主复制是另一种常见的高可用性解决方案，它将多个数据库实例相互连接，每个实例都可以处理客户端请求和接收更改。在这种解决方案中，如果一个实例出现故障，请求将自动发送到其他实例，从而保持系统的稳定性。与主从复制不同，主主复制可以保持所有节点的读写权限，并且可靠性较高。

MHA和Galera Cluster都是帮助管理高可用性的工具。它们可以与MySQL主从复制和主主复制方案集成，从而实现故障转移和高可用性。这些工具可以自动化角色转换，并确保在节点出现故障时维护数据的完整性和一致性。

总之，高可用性对于数据库系统至关重要。在选择解决方案时，需要考虑到系统的可靠性，性能，可扩展性和成本等多个因素。虽然现有的解决方案已经很成熟并且应用广泛，但是随着技术的进步和需求的变化，未来可能会出现更好的解决方案。