主流 MySQL 热备份方案

按照备份时数据库的运行状态，可以分为三种:

冷备:停库、停服来备份。即关mysql服务，读写请求均不允许状态

温备:不停库、不停服务备份，会(锁表)阻止用户的写入.

热备:不停库、不停服务备份，也不会(锁表)阻止用户的写入。

注:MyISAM不支持热备，InnoDB都支持。

 

1.冷备，先停止服务，使用cp 命令把mysql的数据文件，复制到备份目录；恢复是把备份目录的文件cp到mysql的数据目录，再启动mysql服务。

2.温备，

#备份
mysqldump -u [username] -p[password] [database_name] > backup_database_name.sql
mysqldump -u root -p mydatabase > mydatabase_backup.sql

mysqldump -u [username] -p[password] [database_name] [table_name] > backup_table_name.sql
mysqldump -u root -p mydatabase mytable > mytable_backup.sql

#恢复
mysql -u [username] -p[password] [database_name] < backup_database_name.sql
mysql -u root -p mydatabase < mydatabase_backup.sql

mysql -u [username] -p[password] [database_name] < backup_table_name.sql
mysql -u root -p newdatabase < mytable_backup.sql

3.热备

 方案一：Percona XtraBackup（最推荐！生产首选）

开源、免费、专为 InnoDB 设计，真正实现 无锁热备

✔️ 优势：

• 备份时 不锁表，业务读写照常
• 支持 全量 + 增量 备份
• TB 级数据恢复只要几分钟（比 mysqldump 快10倍+）
• 支持压缩、加密、流式传输

🛠️ 快速上手（CentOS）：

#安装

#‌1.确认 Percona 仓库已正确安装
sudo rpm -ivh https://repo.percona.com/yum/percona-release-latest.noarch.rpm

#2.如果你使用的是 CentOS Stream 或类似系统，可能需要启用 Percona 的特定仓库。
sudo percona-release enable-only tools release

#3.然后清理 YUM 缓存并更新
sudo yum clean all
sudo yum makecache

#4.查找可用的 Percona XtraBackup 包‌
yum list available | grep -i xtrabackup

#MySQL 版本命名，例如：
#percona-xtrabackup-24：通常用于 MySQL 5.6 和 5.7。
#percona-xtrabackup-80：用于 MySQL 8.0。

 

# 安装（以 MySQL 8.0 为例）
sudo yum install percona-xtrabackup-80   # CentOS
# 或
sudo apt install percona-xtrabackup-80   # Ubuntu

# 创建备份用户（安全起见）
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'StrongPass123!';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';

# 全量热备份（数据库正在跑也没关系！）
xtrabackup --user=bkpuser --password=StrongPass123! \
           --backup --target-dir=/backup/full_$(date +%F)

# “准备”备份（应用 redo log，让数据一致）
xtrabackup --prepare --target-dir=/backup/full_2026-01-21

# 恢复？只需 copy-back（先停 MySQL）
systemctl stop mysqld
xtrabackup --copy-back --target-dir=/backup/full_2026-01-21
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

💡 增量备份也超简单：基于上次全量或增量目录再备份，省空间又快！

注意：如果mysql版本低于8.0.21，不支持上面的BACKUP_ADMIN权限，需要手动授予备份所需的其他权限。通常：

• 需要手动授予备份所需的其他权限。通常，备份用户需要以下权限：
  • SELECT：用于读取数据。
  • RELOAD：用于执行 FLUSH TABLES WITH READ LOCK。
  • PROCESS：用于执行 SHOW PROCESSLIST。
  • REPLICATION CLIENT：用于执行 SHOW MASTER STATUS 和 SHOW SLAVE STATUS。
  • LOCK TABLES：用于执行 FLUSH TABLES WITH READ LOCK。
  • CREATE、INSERT、DROP、UPDATE：用于在 mysql 数据库中创建和操作 backup_process 表（如果需要）。
  • SUPER：用于控制复制线程（在某些场景下）。
• 授予这些权限的示例语句：

  GRANT SELECT, RELOAD, PROCESS, REPLICATION CLIENT, LOCK TABLES ON *.* TO 'bkpuser'@'localhost';
  -- 如果需要操作 mysql.backup_process 表
  GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_process TO 'bkpuser'@'localhost';
  -- 如果需要创建表空间或进行表级恢复
  GRANT CREATE TABLESPACE ON *.* TO 'bkpuser'@'localhost';
  FLUSH PRIVILEGES;

   

 

✅ 方案二：主从复制（Master-Slave）—— 把从库当热备节点

本质：从库 = 实时热备份 + 可读扩展

✔️ 优势：

• 从库数据几乎实时同步（秒级延迟）
• 备份直接在 从库 上做，零影响主库性能
• 主库挂了？秒切从库，服务不中断！

🛠️ 配置关键步骤：

1. 修改 my.cnf（Linux 路径通常为 /etc/my.cnf）

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
# 推荐开启 GTID（更健壮，自动处理位置）
gtid_mode = ON
enforce_gtid_consistency = ON

# 可选：只同步特定库（安全）
# binlog-do-db = myapp

2. 重启 MySQL

sudo systemctl restart mysqld

3. 创建复制专用账号（关键！不能用 root）

-- 创建用户（注意：MySQL 8 默认认证插件是 caching_sha2_password）
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'StrongReplPass123!';

-- 授权复制权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%';

-- 刷新权限
FLUSH PRIVILEGES;

💡 为什么不用 root？

• 安全：最小权限原则
• 稳定：避免因 root 密码变更导致复制中断

🔧 获取主库当前 binlog 位置（两种方式）

方式 A：使用 Position 模式（传统，兼容老版本）

-- 锁表（短暂只读，几秒）
FLUSH TABLES WITH READ LOCK;

-- 查看位置
SHOW MASTER STATUS;

输出示例：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     154  |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

记下 File = mysql-bin.000003，Position = 154

⚠️ 别忘了解锁！

UNLOCK TABLES;

方式 B：使用 GTID 模式（推荐！自动定位，不怕断电）

• 不需要手动记 position！
• 只要主从都开了 gtid_mode=ON，从库会自动找最新 GTID

要判断 MySQL 使用的是 ‌Position 模式‌ 还是 ‌GTID 模式‌ 进行主从复制

SHOW VARIABLES LIKE 'gtid_mode';

 

从库配置（Slave）

1. 修改 my.cnf

[mysqld]
server-id = 2          # 必须唯一！不能和主库一样
relay-log = mysql-relay-bin
read_only = ON         # 防止误写（应用用户仍可写，除非 SUPER 权限）
gtid_mode = ON
enforce_gtid_consistency = ON

2. 重启 MySQL

sudo systemctl restart mysqld

3. 配置主从关系

如果用 Position 模式：

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongReplPass123!',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;  -- 连不上时每10秒重试

如果用 GTID 模式（更简单！）：

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongReplPass123!',
  MASTER_AUTO_POSITION=1;   -- 关键！自动用 GTID 同步

4. 启动复制

START SLAVE;

5. 检查状态

SHOW SLAVE STATUS\G

✅ 成功标志：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0（或很小）

❌ 常见失败原因：

• 网络不通（防火墙没开 3306）
• 复制账号密码错
• server-id 重复
• 主库没开 binlog

 

 

 总结两点：

1. 主库要不要建账号？ → 必须建！ 且只给 REPLICATION SLAVE 权限
2. 从库复制位置怎么来？ →
   • Position 模式：SHOW MASTER STATUS 手动记录
   • GTID 模式：自动同步，无需记位置（强烈推荐！）