MySQL主备数据库搭建及数据同步方法分析

在现代的数据库架构中，高可用性和数据一致性是两个非常重要的指标。主备数据库架构是一种常见的解决方案，它通过在主数据库（Master）上进行写操作，在备数据库（Slave）上进行读操作来实现这两个目标。

一、主备数据库搭建

1.1 环境准备

在搭建主备数据库之前，需要准备至少两台服务器，一台作为主数据库，另一台作为备数据库。这两台服务器可以是物理机，也可以是虚拟机。以下是搭建的基本步骤：

1. 安装MySQL：在两台服务器上安装相同版本的MySQL。
2. 配置MySQL：确保两台服务器的MySQL配置文件（通常是my.cnf或my.ini）中的server-id是唯一的，这是MySQL识别不同服务器的关键配置。
3. 创建用户和权限：在主数据库上创建用于数据同步的用户，并赋予相应的权限。

1.2 配置主数据库

在主数据库上，需要开启二进制日志（Binary Log），这是MySQL复制的基础。编辑my.cnf文件，添加以下配置：

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

这里的server-id是该服务器的唯一标识，log-bin指定了二进制日志文件的存放位置和前缀，binlog_do_db指定了需要复制的数据库。

重启MySQL服务以应用配置：

sudo service mysql restart

1.3 配置备数据库

在备数据库上，需要配置为复制主数据库的数据。编辑my.cnf文件，添加以下配置：

[mysqld]
server-id=2
relay-log=relay-log
read_only=1

这里的server-id也需要是唯一的，relay-log指定了中继日志的存放位置和前缀，read_only=1确保备数据库不接受直接的写操作。

重启MySQL服务以应用配置：

sudo service mysql restart

1.4 启动数据复制

在备数据库上，使用以下命令启动数据复制：

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='replication_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=4;

这里的MASTER_HOST是主数据库的IP地址，MASTER_USER和MASTER_PASSWORD是用于复制的用户和密码，MASTER_LOG_FILE和MASTER_LOG_POS是主数据库上二进制日志的文件名和位置。

启动复制：

START SLAVE;

检查复制状态：

SHOW SLAVE STATUS\G

如果Slave_IO_Running和Slave_SQL_Running都是Yes，则表示复制正常运行。

二、数据同步方法分析

2.1 异步复制

异步复制是MySQL默认的复制类型，主数据库不需要等待备数据库的确认，这可以提高主数据库的写入性能，但可能会在主数据库故障时丢失数据。

2.2 半同步复制

半同步复制（semi-synchronous replication）确保至少有一个备数据库确认了事务的写入。这提高了数据的安全性，但可能会降低写入性能。

2.3 强同步复制

强同步复制（fully synchronous replication）确保所有备数据库都确认了事务的写入。这是最安全的数据复制方式，但会严重影响写入性能。

2.4 多线程复制

MySQL 5.6及以上版本支持多线程复制，可以提高复制的效率，尤其是在备数据库需要处理大量数据时。

三、代码示例

以下是一些关键的SQL命令和配置示例：

3.1 创建复制用户

在主数据库上创建复制用户：

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

3.2 配置主数据库

在主数据库的my.cnf中配置二进制日志：

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

3.3 配置备数据库

在备数据库的my.cnf中配置复制：

[mysqld]
server-id=2
relay-log=relay-log
read_only=1

3.4 启动复制

在备数据库上启动复制：

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='replication_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=4;
START SLAVE;

3.5 检查复制状态

检查复制状态：

SHOW SLAVE STATUS\G