MySQL 备份恢复与主从同步操作指南

MySQL 备份与恢复方案

数据库备份是防止数据丢失的最后防线。针对不同的业务场景，可选择逻辑备份（mysqldump）或物理备份（XtraBackup），并结合二进制日志实现精确的时间点恢复。

第1步：创建专用备份管理员账号

目的：遵循最小权限原则，确保备份过程安全且不影响生产环境其他操作。
入口：MySQL CLI
动作：创建用户并授予特定权限（SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, PROCESS）。

# 创建管理员
CREATE USER 'backup'@'localhost' IDENTIFIED BY '你的密码';

# 给管理员授权
GRANT SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,PROCESS ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

第2步：执行全量备份（mysqldump 逻辑备份）

入口：系统终端
动作：使用 mysqldump 工具备份指定数据库。为保证 InnoDB 事务一致性且尽量减少锁表时间，推荐组合使用 --single-transaction（开启快照读，仅限InnoDB）与 --master-data=2（记录 Binlog 位置信息作为注释）。

重要参数说明：

• --single-transaction：对 InnoDB 表非常有效，无需锁定表即可保证一致性。
• -l/--lock-tables：仅支持 MyISAM 或在混合引擎时作为兜底，会锁定单库所有表。
• --routines：包含存储过程；--triggers：包含触发器；--events：包含定时任务。
• --hex-blob：将 BLOB 和 BIT 列以十六进制导出，防止特殊字符乱码。

# 标准全备命令示例
mysqldump -ubackup -p --master-data=2 --single-transaction \
--routines --triggers --events --database 数据库名 > backup_full.sql

注意：--single-transaction 与 --lock-tables 互斥。若数据库同时存在 InnoDB 和 MyISAM 表，只能使用 --lock-tables，但此时无法保证全局一致性，需权衡业务可用性。

第3步：SQL 文件恢复与时间点还原

当发生误删除等操作时，需要结合全量备份与二进制日志进行恢复。

A. 基础全量恢复： 直接将备份的 .sql 文件导入数据库即可。

mysql -uroot -p 数据库名 < backup_full.sql

B. 基于时间点（PITR）的深度恢复： 适用于需要恢复到具体某一秒的场景。假设全量备份后的 binlog 文件中包含了误操作前的正确数据。

1. 恢复全量基线：先执行上述全量恢复命令。
定位 Binlog 位置：在 .sql 备份文件中查找 CHANGE MASTER TO 注释，记录 MASTER_LOG_FILE 和 MASTER_LOG_POS。
2. 分析 Binlog 日志：找到误操作发生的时间点范围。

cd /var/lib/mysql
# 查看特定时间段内的 DELETE 操作，确定截断位置
mysqlbinlog --base64-output=decode-rows -vv --start-datetime='2023-01-01 10:00:00' --stop-datetime='2023-01-01 11:00:00' binlog.000008 | grep -B3 -A3 'DELETE'

4. 提取有效变更：根据上一步确定的起始和结束 Position（或时间戳），将正确的操作导出为临时 SQL。

# 导出指定区间的 binlog 到临时 sql 文件
mysqlbinlog --start-position=<起始Position> --stop-position=<结束Position> \
--database=数据库名 binlog文件名 > recover_incremental.sql

5. 应用增量恢复：将临时 SQL 重新导入数据库。

mysql -uroot -p 数据库名 < recover_incremental.sql

第4步：物理备份恢复（Percona XtraBackup）

准备条件：需安装 Percona 源及 percona-xtrabackup-24 包。适合超大型数据库的热备份。

全量备份：

innobackupex --user=backup --password=密码 --parallel=2 /home/db_backup/

全量恢复：
风险提醒：执行前务必停止 MySQL 服务并清空原有数据目录，否则可能导致数据覆盖混乱。

1. 准备阶段（Prepare）：使数据文件处于可一致恢复状态。

innobackupex --apply-log /home/db_backup/日期文件夹/

2. 恢复阶段（Copy-back）：将文件拷贝回 MySQL 数据目录。

# 执行恢复
innobackupex --datadir=/var/lib/mysql --copy-back /home/db_backup/日期文件夹/

# 严格修复权限（必须执行，否则 MySQL 无法启动）
chown -R mysql:mysql /var/lib/mysql
chmod -R 700 /var/lib/mysql

MySQL 分区分表策略

对于海量数据表，分区可以将数据分散在多个物理文件中，提升查询效率并简化数据生命周期管理。

第1步：评估与创建分区表

入口：MySQL Console
动作：根据数据增长特性选择合适的分区类型。

• RANGE 分区：最常用，按数值或日期范围切分。例如：PARTITION BY RANGE (YEAR(create_time))。
• HASH 分区：按哈希值均匀分布，解决“热点”问题。例如：PARTITION BY HASH(UNIX_TIMESTAMP(create_time)) PARTITIONS 4。

# 时间范围分区示例
CREATE TABLE `records` (
    id INT NOT NULL,
    create_time DATE
) ENGINE=INNODB
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

验证方式：

SELECT table_name, partition_name, table_rows 
FROM information_schema.PARTITIONS 
WHERE table_name = 'records';

第2步：数据归档（Exchange Partition）

目的：快速清理过期历史数据（如 3 年前的数据），且速度远快于逐行 Delete。
前提：MySQL 5.7+，归档表与原分区表结构完全一致，且非 Archive 引擎。

1. 创建空归档表：结构与原表相同。

CREATE TABLE `arch_records_2017` LIKE records;

2. 交换分区：该操作是元数据级别的交换，瞬间完成。

ALTER TABLE records 
EXCHANGE PARTITION p2017 WITH TABLE arch_records_2017;

3. 删除旧分区：原表中该分区数据消失（实际已移至归档表）。

ALTER TABLE records DROP PARTITION p2017;

4. 转换引擎：将归档表转换为 ARCHIVE 引擎，大幅节省存储空间。

ALTER TABLE arch_records_2017 ENGINE=ARCHIVE;

主从复制与读写分离架构搭建

主从复制是实现读写分离的基础。Master 负责写，Slave 负责读，通过 Binlog 异步同步数据。

第1步：网络连通性与账号授权

入口：主库与从库
动作：确保两台机器 IP 互通，并从库具备连接主库复制数据的权限。

-- 在主库上创建复制专用账号
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'StrongPassword';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

第2步：配置主服务器（Master）

入口：/etc/my.cnf
动作：启用 Binary Log 并设置唯一 Server ID。

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
# 如需仅同步特定库
# binlog_do_db = your_database_name

验证：重启 MySQL 后执行 SHOW MASTER STATUS;，确保 File 和 Position 不为空。

第3步：配置从服务器（Slave）

入口：/etc/my.cnf
动作：设置唯一 Server ID，指向主库地址。

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
# 如需只读某库
# replicate-do-db = your_database_name

注意：新版 MySQL (5.7+/8.0+) 推荐使用 CHANGE MASTER TO 命令而非在 my.cnf 中配置 master-host，这里演示传统 my.cnf 方式或配合初始化脚本。

第4步：建立复制连接与状态检查

操作：在从库端绑定主库信息并开始线程。

CHANGE MASTER TO
  MASTER_HOST='192.168.x.x',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongPassword',
  MASTER_LOG_FILE='mysql-bin.000001', -- 根据主库show status获取
  MASTER_LOG_POS=154;                -- 根据主库show status获取

START SLAVE;

完成判断：
执行 SHOW SLAVE STATUS\G，观察以下两个关键指标：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Last_Error 应为空。

至此，主从复制链路打通。应用层可通过中间件（如 ProxySQL）或代码路由实现读写分离。