MySQL主备操作以及原理详解

什么是MySQL主备

MySQL主备是一种高可用性的架构，通过在主库和多个备库之间进行数据同步，确保在主库发生故障时备库可以立刻接管服务，从而降低系统出现宕机的可能性，保证系统的稳定性和可靠性。

MySQL主备原理

MySQL主从备份原理实现了主从复制，即主库将数据更新同步到备库。MySQL主备的基本原理如下：

主库将写操作记录在二进制日志（Binary Log）中，并通过网络传输到备库；
备库将收到的二进制日志记录到本地的中继日志（Relay Log）中，并依次执行；

因此，当主库发生故障或数据丢失时，可以使用备库中的数据快速恢复业务。

MySQL主备操作

下面是MySQL主备操作的具体步骤：

1. 准备主库

首先，在主库中配置主从同步的用户名和密码，并开启二进制日志。

配置文件 my.cnf 内容示例：

# 配置主从同步的用户名和密码
server-id = 1
log-bin = /var/lib/mysql/mysql-bin.log
binlog_do_db = dbtest
binlog_ignore_db = mysql
relay-log = /var/lib/mysql/relay-bin
expire_logs_days = 10
max_binlog_size = 100M

2. 准备备库

在备库上配置同步用户名和密码，并指定主库的地址。

配置文件 my.cnf 内容示例：

server-id = 2

设置主库地址：

CHANGE MASTER TO
MASTER_HOST='192.168.0.198',
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000018',
MASTER_LOG_POS=251;

3. 启动主备同步

在备库上使用以下命令启动主备同步：

START SLAVE;

4. 监测主备同步状态

使用以下命令查看主备同步状态：

SHOW SLAVE STATUS\G

在状态结果中，如果 Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes，则代表主备同步正常。

MySQL主备示例

以下示例演示了MySQL主备配置的过程：

示例1：主库

在主库上执行以下SQL命令创建一个名为 dbtest 的数据库，并向表中插入10条数据：

CREATE DATABASE dbtest;
USE dbtest;
CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO users (name) VALUES ('user1'),('user2'),('user3'),('user4'),('user5'),('user6'),('user7'),('user8'),('user9'),('user10');

在主库的 my.cnf 文件中设置如下参数：

[mysqld]
#开启二进制日志
log-bin=mysql-bin
#从库IP
bind-address=192.168.0.198

示例2：备库

在备库上执行以下SQL命令：

CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `dbtest`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

在备库的 my.cnf 文件中设置如下参数：

[mysqld]
#设置server-id
server-id = 2
#设置从库ip
bind-address = 192.168.0.199

#主库数据复制配置
#注意：这里的master_log_file和master_log_pos是从SHOW MASTER STATUS出来的结果，请各位按自己的结果来设置 而不是照抄 ...
replicate-do-db = dbtest
master-host = 192.168.0.198
master-user = root
master-password = root
master-port = 3306
relay-log = dbtest-relay-bin
log-slave-updates = true
slave-skip-errors = all

重启MySQL服务，使用以下命令启动主备同步：