SQL数据备份方法咨询：请说明不使用dump的MySQL备份方案

当然存在多种可行的SQL数据备份方案，以下是几种不使用mysqldump等dump工具的MySQL备份实现方式，覆盖不同场景需求：

1. 物理文件冷备份 #

这是最直接的全量备份方式，通过复制MySQL核心数据文件完成备份。

• 操作步骤：
  1. 停止MySQL服务，确保内存中所有脏数据刷写到磁盘：

     # Linux系统
     systemctl stop mysql
     # Windows系统
     net stop MySQL
     

  2. 复制MySQL数据目录到备份存储位置（默认路径：Linux为/var/lib/mysql，Windows为C:\ProgramData\MySQL\MySQL Server X.X\Data）：

     cp -r /var/lib/mysql /path/to/backup/mysql_full_$(date +%Y%m%d)
     

  3. 重启MySQL服务：

     systemctl start mysql
     

• 关键注意事项：
  • 必须停止服务，否则会出现数据文件不一致问题。
  • 需完整备份所有关联文件：包括表结构文件（.frm）、InnoDB表空间文件（.ibd）、MyISAM数据/索引文件（.MYD/.MYI），以及my.cnf配置文件。
  • 恢复时需将备份文件放回原目录，确保文件权限与原目录一致（Linux下设置mysql:mysql权限）。
• 适用场景：小型数据库，允许短时间停机的场景。

2. SELECT ... INTO OUTFILE 文本导出 #

通过SQL语句将单表数据导出为自定义格式的文本文件，仅导出数据（需单独备份表结构）。

• 操作示例：

  -- 导出users表为CSV格式
  SELECT * INTO OUTFILE '/var/lib/mysql-files/users_backup.csv'
  FIELDS TERMINATED BY ',' 
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM users;
  

  • 导出表结构需单独执行：

    SHOW CREATE TABLE users;
    

• 关键注意事项：
  • MySQL进程需拥有目标路径的写入权限，建议使用secure_file_priv指定的目录（可通过SHOW VARIABLES LIKE 'secure_file_priv'查看）。
  • 无法直接导出整个数据库，需逐个表执行语句。
  • 恢复时使用LOAD DATA INFILE命令导入：

    LOAD DATA INFILE '/var/lib/mysql-files/users_backup.csv'
    INTO TABLE users
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
    

• 适用场景：导出特定表数据、需要自定义导出格式的场景。

3. 基于二进制日志（Binlog）的增量备份 #

利用MySQL的二进制日志记录所有数据修改操作，结合全量备份实现增量备份与时间点恢复。

• 前置准备：在my.cnf中开启Binlog：

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

• 操作步骤：
  1. 先完成一次全量物理备份（如冷备份），同时记录当前Binlog位置：

     SHOW MASTER STATUS;
     

     记录返回的File（如mysql-bin.000001）和Position（如1234）。
  2. 定期复制新生成的Binlog文件到备份位置，或导出为SQL语句：

     # 导出指定位置区间的Binlog为SQL
     mysqlbinlog --start-position=1234 --stop-position=5678 /var/lib/mysql/mysql-bin.000001 > incremental_backup_20240520.sql
     

• 恢复方式：
  1. 先恢复全量备份。
  2. 执行增量Binlog SQL文件：

     mysql -u root -p < incremental_backup_20240520.sql
     

• 关键注意事项：
  • 定期清理旧Binlog，避免磁盘占用过大（可通过expire_logs_days配置自动清理）。
  • 恢复时需按Binlog文件顺序依次应用，不能跳号。
• 适用场景：需要增量备份、时间点恢复的生产环境。

4. InnoDB热备份（Percona XtraBackup） #

使用Percona XtraBackup工具实现InnoDB数据库的无停机物理备份，属于热备份范畴，不影响业务运行。

• 操作步骤：
  1. 安装Percona XtraBackup工具。
  2. 执行全量热备份：

     xtrabackup --user=root --password=your_pass --backup --target-dir=/path/to/full_backup
     

  3. 执行增量备份（基于上次全量/增量备份）：

     xtrabackup --user=root --password=your_pass --backup --target-dir=/path/to/incremental_backup --incremental-basedir=/path/to/full_backup
     

  4. 备份完成后需执行prepare操作，确保数据一致性（恢复前必须做）：

     # 准备全量备份
     xtrabackup --prepare --target-dir=/path/to/full_backup
     # 合并增量备份到全量备份
     xtrabackup --prepare --target-dir=/path/to/full_backup --incremental-dir=/path/to/incremental_backup
     

• 关键注意事项：
  • 无需停止MySQL服务，备份过程中不锁表（InnoDB引擎）。
  • 支持压缩备份、流式备份等高级特性。
• 适用场景：大型InnoDB数据库，需要无停机备份的生产环境。

5. 主从复制作为备份方案 #

搭建MySQL主从复制架构，将从库作为备份节点，实现备份与业务分离。

• 操作步骤：
  1. 主库创建复制用户：

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

  2. 主库锁表并记录Binlog状态：

     FLUSH TABLES WITH READ LOCK;
     SHOW MASTER STATUS;
     

  3. 复制主库数据目录到从库（或用物理全量备份），然后在从库my.cnf中配置：

     [mysqld]
     server-id=2
     relay_log=mysql-relay-bin
     read_only=1
     

  4. 从库启动复制：

     CHANGE MASTER TO 
     MASTER_HOST='主库IP', 
     MASTER_USER='repl', 
     MASTER_PASSWORD='repl_pass', 
     MASTER_LOG_FILE='mysql-bin.000001', 
     MASTER_LOG_POS=1234;
     START SLAVE;
     

• 关键注意事项：
  • 定期检查复制状态（SHOW SLAVE STATUS\G），确保Slave_IO_Running和Slave_SQL_Running均为Yes。
  • 从库设置read_only防止误操作，可在从库执行备份操作（如物理备份）不影响主库。
• 适用场景：高可用需求与备份需求结合的生产环境。

内容的提问来源于stack exchange，提问作者Gulshan Kumar