一.软件安装
# cd /mysql/app # unzip -q /soft/p38863836_840_Linux-x86-64.zip # tar -Jxf mysql-commercial-backup-8.4.8-linux-glibc2.28-x86_64.tar.xz # ln -sf mysql-commercial-backup-8.4.8-linux-glibc2.28-x86_64 mysqlbackup /mysql/app/mysqlbackup/bin 加入环境变量 # mysqlbackup --version MySQL Enterprise Backup Ver 8.4.8-commercial for Linux on x86_64 (MySQL Enterprise - Commercial) Copyright (c) 2003, 2026, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Run mysqlbackup --help for help information.
二.准备备份环境
#备份目录 mkdir -p /mysql/backup/backup-db mkdir -p /mysql/backup/backup-tmp mkdir -p /mysql/backup/restore-tmp chown -R mysql:mysql /mysql/backup chmod -R 775 /mysql/backup mkdir -p /mysql/log/3306/binlog chown -R mysql:mysql /mysql/log/3306/binlog chmod -R 775 /mysql/log/3306/binlog/ #创建备份用户 create user 'backup'@'localhost' identified by 'backup'; --5.7权限 grant reload on *.* to 'backup'@'localhost'; grant create, insert, drop, update on mysql.backup_progress to 'backup'@'localhost'; grant create, insert, select, drop, update on mysql.backup_history to 'backup'@'localhost'; grant replication client on *.* to 'backup'@'localhost'; grant super on *.* to 'backup'@'localhost'; grant process on *.* to 'backup'@'localhost'; grant alter on mysql.backup_history to 'backup'@'localhost'; --8.0权限 -- 一次性授予所有必要权限 GRANT BACKUP_ADMIN, RELOAD, PROCESS, SUPER, REPLICATION CLIENT, CREATE TABLESPACE, SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON *.* TO 'backup'@'localhost'; -- 特定数据库权限 GRANT SELECT ON performance_schema.* TO 'backup'@'localhost'; GRANT ALL ON mysql.backup_history TO 'backup'@'localhost'; GRANT ALL ON mysql.backup_progress TO 'backup'@'localhost'; flush privileges;
三.image备份案例
1.备份一个完整的数据库实例 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup \ --backup-image=/mysql/backup/backup-db/fullbackup`date +%Y%m%d`.mbi --with-timestamp backup-to-image \ --backup-dir=/mysql/backup/backup-tmp 260123 21:59:14 MAIN INFO: MySQL binlog position: filename fgedudb-binlog.000005, position 86992575. ------------------------------------------------------------- Parameters Summary ------------------------------------------------------------- Start LSN : 86779904 Last Checkpoint LSN : 86785443 End LSN : 86786467 ------------------------------------------------------------- 2.校验备份有效性 mysqlbackup --backup-image=/mysql/backup/backup-db/fullbackup20260123.mbi validate mysqlbackup --backup-image=/mysql/backup/backup-db/fullbackup20260123.mbi list-image |more 3.恢复数据库 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/backup-db/fullbackup20260123.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log #破坏数据库 systemctl stop mysqld mv data data_bak mkdir data #执行恢复 copy-back-and-apply-log 恢复前需要清空数据目录 --log-bin 将备份中产生的binlog恢复到其他目录 #修改权限启动 chown -R mysql:mysql /mysql/data/3306/data systemctl start mysqld #备份相关表 mysql.backup_progress desc mysql.backup_history;
4.目录备份1
1.备份 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --with-timestamp backup --backup-dir=/mysql/backup/backup-db --read-threads=2 --write-threads=2 --process-threads=8 2.校验 mysqlbackup --backup-dir=/mysql/backup/backup-db/2026-01-24_16-57-04 validate 3.对备份应用备份期间产生的日志 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf apply-log --backup-dir=/mysql/backup/backup-db/2026-01-24_16-57-04 4.破坏库 5.还原 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --datadir=/mysql/data/3306/data --backup-dir=/mysql/backup/backup-db/2026-01-24_16-57-04 --log-bin=/tmp copy-back --read-threads=2 --write-threads=2 --process-threads=8 6. chown -R mysql:mysql /mysql/data/3306/data systemctl start mysqld
4.目录备份2
mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --with-timestamp backup-and-apply-log --backup-dir=/mysql/backup/backup-db --read-threads=2 --write-threads=2 --process-threads=8
5.压缩备份
mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --with-timestamp backup-and-apply-log --compress-level=1 --backup-dir=/mysql/backup/backup-db --read-threads=2 --write-threads=2 --process-threads=8 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --uncompress --backup-dir=/mysql/backup/backup-db/2018-05-13_01-13-25 apply-log mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --datadir=/mysql/data/3306/data --backup-dir=/mysql/backup/backup-db/2018-05-13_01-13-25 copy-back --read-threads=2 --write-threads=2 --process-threads=8
6.增量备份
1.全备 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup-dir=/mysql/backup/backup-db/ --with-timestamp backup-and-apply-log 2.修改数据 3.增量1 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup backup --incremental --incremental-base=dir:/mysql/backup/backup-db/2026-01-24_20-34-09 --incremental-backup-dir=/mysql/backup/backup-db/inc01 4.修改数据 5.增量2 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup backup --incremental --incremental-base=dir:/mysql/backup/backup-db/inc01 --incremental-backup-dir=/mysql/backup/backup-db/inc02 6.破坏 systemctl stop mysqld rm -rf data_bak mv data data_bak mkdir data 7.合并增量备份 mysqlbackup --backup-dir=/mysql/backup/backup-db/2026-01-24_20-34-09 --incremental-backup-dir=/mysql/backup/backup-db/inc01 apply-incremental-backup mysqlbackup --backup-dir=/mysql/backup/backup-db/2026-01-24_20-34-09 --incremental-backup-dir=/mysql/backup/backup-db/inc02 apply-incremental-backup 8.恢复合并后的全备 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf copy-back --backup-dir=/mysql/backup/backup-db/2026-01-24_20-34-09 --log-bin=/tmp/binlog
三.结合binlog进行完全恢复
1.将image还原回目录 mysqlbackup --backup-image=/mysql/backup/backup-db/fullbackup20260123.mbi image-to-backup-dir --backup-dir=/mysql/backup/backup-db/fullbackup20260123 2.先应用日志 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf apply-log --backup-dir=/mysql/backup/backup-db/2026-01-24_16-57-04 3.进入/mysql/backup/backup-db/fullbackup20260123/meta/backup_variables.txt binlog_position=fgedudb-binlog.000005:86992575 备份开始的时间 4.还原全库 mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --datadir=/mysql/data/3306/data --backup-dir=/mysql/backup/backup-db/fullbackup20260123 --log-bin=/tmp/binlog copy-back 5.应用binlog恢复 mysqlbackup --backup-dir=/tmp/backup_test \ --binlog-index=/tmp/binlog.index \ apply-binlog \ --start-file=fgedudb-binlog.000005 \ --start-position=86992575 # 可视化：备份开始(t0) → 备份期间写入 → 备份结束(t1) │ │ │ ↓ ↓ ↓ 数据快照 记录到binlog binlog位置继续(状态A) (操作X,Y,Z) 增加 │ │ │ ↓ ↓ ↓ apply-log apply-binlog 完整恢复 恢复到A 应用X,Y,Z操作 得到最终状态
