mysql 备份还原实战案例
实战案例：特定数据库的备份脚本
vim mysql_backup.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hallodb
PASS=ayaka
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p "$PASS" -A -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip >${DIR}/${DB}_${TIME}.sql.gz
#如果是Mysql要加上--flush-privileges
实战案例：分库备份并压缩
[root@centos8 ~]#for db in `mysql -uroot -e 'show databases'|grep - Ewv '^(Database|information_schema|performance_schema|sys)$'`;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv '^(Database|information_schema|performance_schema|sys)$'|while read db;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv '^(Database|information_schema|performance_schema|sys)$' | sed -rn 's#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
[root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn'/^(Database|information_schema|performance_schema|sys)$/!s#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
实战案例：分库备份的实战脚本
vim backup_db.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=ayaka
[ -d "$DIR" ] || mkdir $DIR
for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ewv "^Database|.*schema$"`;do
mysqldump -F --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
done
实战案例：完全备份和还原
#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
#备份
[root@centos8 ~]#mysqldump -uroot -payaka -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz
#还原
[root@centos8 backup]#dnf install mariadb-server
[root@centos8 backup]#gzip -d all-2026-02-27.sql.gz
[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2019-11-27.sql
MariaDB [(none)]> set sql_log_bin=on;
实战案例：利用二进制日志，还原数据库最新状态
#二进制日志独立存放
[mysqld]
log-bin=/data/mysql/mysql-bin
#完全备份，并记录备份的二进制位置
mysqldump -uroot -payaka -A -F --default-character-set=utf8 --single-transaction --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz
#修改数据库
insert students (name,age,gender)value('aka',20,'M');
insert students (name,age,gender)value('wang',22,'M');
#损坏数据库
rm -rf /var/lib/mysql/*
#还原
cd /backup
gzip -d all_2019-11-25.sql.gz
#CentOS 8 需要事先生成数据库相关文件，CentOS7 不需要执行此步
mysql_install_db --user=mysql
systemctl restart mariadb
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 998 |
| mysql-bin.000002 | 28090 |
| mysql-bin.000003 | 342 |
+------------------+-----------+
3 rows in set (0.000 sec)
MariaDB [(none)]>set sql_log_bin=0;
MariaDB [(none)]>source /data/all_2019-11-25.sql
[root@centos8 ~]#grep '^-- CHANGE MASTER TO' /data/all_2019-11-25.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;
#二进制日志的备份
[root@centos8 mysql]#mysqlbinlog mysql-bin.000001 --start-position=328 >/backup/inc.sql
[root@centos8 mysql]#mysqlbinlog mysql-bin.000002 >> /backup/inc.sql
MariaDB [(none)]>set sql_log_bin=0;
MariaDB [(none)]>source /backup/inc.sql
MariaDB [(none)]>set sql_log_bin=1;
实战案例：mysqldump 和二进制日志结合实现差异(增量)备份
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2|gzip > /backup/all-`date +%F`.sql.gz
#观察上面备份文件中记录的二进制文件和位置，定期将其之后生成的所有二进制日志进行复制备份
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup #假设mariadb-bin.000003是后续生成的二进制日志
[root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql
实战案例：恢复误删除的表
案例说明：每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将
数据库还原到10：10的状态，且恢复被删除的students表
#完全备份,要求必须开启二进制日志
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /backup/allbackup_`date +%F_%T`.sql
[root@centos8 ~]#ll /backup/
total 2992
-rw-r--r-- 1 root root 3060921 Nov 27 10:20 allbackup_2019-11-27_10:20:08.sql
#完全备份后数据更新
MariaDB [testdb]> insert students (name,age,gender) values('rose',20,'f');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> insert students (name,age,gender) values('jack',22,'M');
Query OK, 1 row affected (0.001 sec)
#10：00误删除了一个重要的表
MariaDB [testdb]> drop table students;
Query OK, 0 rows affected (0.021 sec)
#后续其它表继续更新
MariaDB [testdb]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> insert teachers (name,age,gender)values('wang',30,'M');
Query OK, 1 row affected (0.002 sec)
MariaDB [hellodb]> insert teachers (name,age,gender)values('aka',28,'M');
Query OK, 1 row affected (0.002 sec）
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | wang | 30 | M |
| 6 | mage | 28 | M |
+-----+---------------+-----+--------+
6 rows in set (0.001 sec)
#10：10发现表删除，进行还原
#停止数据库访问
#从完全备份中，找到二进制位置
[root@centos8 ~]#grep '\-\- CHANGE MASTER TO' /backup/allbackup_2026-02-27_10\:20\:08.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
#备份从完全备份后的二进制日志
[root@centos8 ~]#mysqlbinlog --start-position=389 /var/lib/mysql/mariadb-bin.000003 > /backup/inc.sql1
[root@centos8 ~]#mysqlbinlog /var/lib/mysql/mariadb-bin.000004 >> /backup/inc.sql
[root@centos8 ~]#mysqlbinlog /var/lib/mysql/mariadb-bin.000005 >> /backup/inc.sql
#找到误删除的语句，从备份中删除此语句
[root@centos8 ~]#vim /backup/inc.sql
#DROP TABLE `students` /* generated by server */
#如果文件过大，可以使用sed实现
[root@centos8 ~]#sed -i '/DROP TABLE `students`/d' /backup/inc.sql
#利用完全备份和修改过的二进制日志进行还原
[root@centos8 ~]#mysql -uroot -p
MariaDB [hellodb]> set sql_log_bin=0;
MariaDB [hellodb]> source /backup/allbackup_2026-02-27_10:20:08.sql;
MariaDB [hellodb]> source /backup/inc.sql
MariaDB [hellodb]> set sql_log_bin=1;
实战案例：利用 xtrabackup 实现完全备份及还原
注意：目前percona-xtrabackup-24-2.4.18-1.el8.x86_64.rpm不支持CentOS 8上的mariadb-10.3版本
1 安装xtrabackup包
[root@centos8 ~]#yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
2 在原主机做完全备份到/backup
[root@centos8 ~]#mkdir /backup
[root@centos8 ~]#xtrabackup -uroot -payaka --backup --target-dir=/backup/base
#目标主机无需创建/backup目录,直接复制目录本身
[root@centos8 ~]#scp -r /backup/ 目标主机:/
3 在目标主机上还原
注意：恢复主机MySQL服务停止，并且数据目录为空
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@centos8 ~]#yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base
2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
3）还原属性
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
4）启动服务
[root@centos8 ~]#service mysqld start
案例：新版 xtrabackup完全备份及还原
本案例基于CentOS 8 的 MySQL5.7 实现,也支持MySQL5.5和Mariadb5.5,和上面案例步骤相同
1 安装xtrabackup包
#先安装MySQL5.7和xtrabackup包
[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
2 在原主机做完全备份到/backup
[root@centos8 ~]#mkdir /backup
[root@centos8 ~]#xtrabackup -uroot -payaka --backup --target-dir=/backup/base
#目标主机无需创建/backup目录,直接复制目录本身
[root@centos8 ~]#scp -r /backup/ 目标主机:/
3 在目标主机上还原
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base
2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
3）还原属性
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
4）启动服务
[root@centos8 ~]#service mysqld start
案例：旧版xtrabackup完全备份及还原
1 在源主机备份
innobackupex --user=root /backup
scp -r /backup/2018-02-23_11-55-57/ 目标主机:/data/
2 在目标主机预准备并还原
#预准备
innobackupex --apply-log /data/2018-02-23_11-55-57/
#还原过程
systemctl stop mariadb
rm -rf /var/lib/mysql/*
innobackupex --copy-back /data/2018-02-23_11-55-57/
chown -R mysql.mysql /var/lib/mysql/
systemctl start mariadb
实战案例：利用xtrabackup完全，增量备份及还原
案例: 利用xtrabackup8.0 完全，增量备份及还原MySQL8.0
1 备份过程
1）完全备份：
[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
[root@centos8 ~]#mkdir /backup/
[root@centos8 ~]#xtrabackup -uroot -payaka --backup --target-dir=/backup/base
2）第一次修改数据
mysql>insert students (name,age,gender) values('rose',20,'f');
3）第一次增量备份
[root@centos8 ~]#xtrabackup -uroot -payaka --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
4）第二次修改数据
mysql>insert students (name,age,gender) values('jack',22,'m');
5）第二次增量
[root@centos8 ~]#xtrabackup -uroot -payaka--backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
#观察目录文件的大小
[root@centos8 ~]#du -sh /backup/*
62M /backup/base
2.0M /backup/inc1
2.0M /backup/inc2
6）[root@centos8 ~]#scp -r /backup/* 目标主机:/backup/
#备份过程生成三个备份目录
/backup/{base，inc1，inc2}
2还原过程
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
[root@centos8 ~]#du -sh /backup/*
2）合并第1次增量备份到完全备份
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base--incremental-dir=/backup/inc1
[root@centos8 ~]#du -sh /backup/*
82M /backup/base
9.9M /backup/inc1
2.0M /backup/inc2
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
[root@centos8 ~]#du -sh /backup/*
178M /backup/base
9.9M /backup/inc1
9.9M /backup/inc2
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
#或者执行下面操作也可以
[root@centos8 ~]#cp -r /backup/base/* /var/lib/mysql
5）还原属性：
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
6）启动服务：
[root@centos8 ~]#service mysqld start
案例：新版xtrabackup完全，增量备份及还原
和上面案例步骤相同
1 备份过程
1）完全备份：
[root@centos8 ~]#mkdir /backup/
[root@centos8 ~]#xtrabackup -uroot -payaka --backup --target-dir=/backup/base
2）第一次修改数据
3）第一次增量备份
[root@centos8 ~]#xtrabackup -uroot -payaka --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
#查看xtrabackup相关文件
[root@centos8 ~]#cat /backup/inc1/xtrabackup_info
uuid = 90be5bf4-0c32-11eb-b285-000c29e10e53
name =
tool_name = xtrabackup
tool_command = -uroot -payaka --backup --target-dir=/backup/inc1 --incremental-
basedir=/backup/base
tool_version = 2.4.20
ibbackup_version = 2.4.20
server_version = 5.7.29-log
start_time = 2020-10-12 10:28:06
end_time = 2020-10-12 10:28:08
lock_time = 1
binlog_pos = filename 'centos8-bin.000002', position '10735'
innodb_from_lsn = 2686016
innodb_to_lsn = 2691308
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N
[root@centos8 ~]#cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2686016
to_lsn = 2691308
last_lsn = 2691317
compact = 0
recover_binlog_info = 0
flushed_lsn = 2691317
[root@centos8 ~]#cat /backup/inc1/xtrabackup_binlog_info
centos8-bin.000002 10735
4）第二次修改数据
5）第二次增量
[root@centos8 ~]#xtrabackup -uroot -payaka --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
#查看xtrabackup相关文件
root@centos8 ~]#cat /backup/inc2/xtrabackup_info
uuid = 21b215ee-0c33-11eb-b285-000c29e10e53
name =
tool_name = xtrabackup
tool_command = -uroot -payaka --backup --target-dir=/backup/inc2 --incremental-
basedir=/backup/inc1
tool_version = 2.4.20
ibbackup_version = 2.4.20
server_version = 5.7.29-log
start_time = 2020-10-12 10:32:09
end_time = 2020-10-12 10:32:11
lock_time = 1
binlog_pos = filename 'centos8-bin.000002', position '11285'
innodb_from_lsn = 2691308
innodb_to_lsn = 2696589
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N
[root@centos8 ~]#cat /backup/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2691308
to_lsn = 2696589
last_lsn = 2696598
compact = 0
recover_binlog_info = 0
flushed_lsn = 2696598
[root@centos8 ~]#cat /backup/inc2/xtrabackup_binlog_info
centos8-bin.000002 11285
6）[root@centos8 ~]#scp -r /backup/* 目标主机:/backup/
#备份过程生成三个备份目录
/backup/{base，inc1，inc2}
还原过程
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2）合并第1次增量备份到完全备份
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base--incremental-dir=/backup/inc1
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
5）还原属性：
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
6）启动服务：
[root@centos8 ~]#service mysqld start
案例：旧版xtrabackup完全，增量备份及还原
1 在源主机备份
innobackupex /backup
mkdir /backup/inc{1,2}
#修改数据库内容
innobackupex --incremental /backup/inc1 --incremental-basedir=/backup/2018-02-23_14-21-42（完全备份生成的路径）
#再次修改数据库内容
innobackupex --incremental /backup/inc2 --incremental-
basedir=/backup/inc1/2018-02-23_14-26-17 （上次增量备份生成的路径）
scp -r /backup/* 目标主机:/data/
2 在目标主机还原
#预准备过程
innobackupex --apply-log --redo-only /data/2018-02-23_14-21-42/
innobackupex --apply-log --redo-only /data/2018-02-23_14-21-42/ --incremental-dir=/data/inc1/2018-02-23_14-26-17
innobackupex --apply-log /data/2018-02-23_14-21-42/ --incremental-dir=/data/inc2/2018-02-23_14-28-29/
#还原过程
不启动mariadb
systemctl stop mariadb
rm -rf /var/lib/mysql/*
innobackupex --copy-back /data/2018-02-23_14-21-42/
chown -R mysql.mysql /var/lib/mysql/
systemctl start mariadb
实战案例：xtrabackup单表导出和导入
#导出
1 单表备份
innobackupex -uroot -payaka --include='hellodb.students' /backup
2备份表结构
mysql -e 'show create table hellodb.students' > student.sql
3删除表
mysql -e 'drop table hellodb.students'
#导出
4 innobackupex --apply-log --export /backups/2018-02-23_15-03-23/
5 创建表
mysql>CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint(3) unsigned DEFAULT NULL,
`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
6 删除表空间
alter table students discard tablespace;
7 cp /backups/2018-02-23_15-03-23/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/
8 chown -R mysql.mysql /var/lib/mysql/hellodb/
9 mysql>alter table students import tablespace;