MySQL备份与恢复

it2026-01-18 6

这里写目录标题

前言一、数据库备份的分类1.1、从物理与逻辑的角度，备份可分为1.2、从数据库的备份策略角度，备份可分为二、MySQL完全备份与恢复2.1、常见的备份方法2.2、MySQL完全备份简介2.3、数据库完全备份分类2.4、物理冷备份与恢复步骤2.5、mysqldump备份和恢复三、MySQL增量备份与恢复3.1、MySQL增量备份3.2、MySQL数据库增量恢复3.3、增量恢复的方法时间点恢复：位置点恢复：

前言

数据备份的重要性 1、在生产环境中，数据的安全性至关重要 2、任何数据的丢失都可能产生严重的后果 3、造成数据丢失的原因 4、程序错误 5、人为操作错误 6、运算错误 7、磁盘故障 8、灾难（如火灾，地震）和盗窃

一、数据库备份的分类

1.1、从物理与逻辑的角度，备份可分为

1、物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份物理备份方法冷备份（脱机备份）：是在关闭数据库的时候进行的热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作 2、逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

1.2、从数据库的备份策略角度，备份可分为

1、完全备份：每次对数据库进行完整的备份 2、差异备份：备份自从上次完全备份之后被修改过的文件 3、增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

二、MySQL完全备份与恢复

2.1、常见的备份方法

物理冷备备份时数据库处于关闭状态，直接打包数据库文件备份速度快，恢复时也是最简单的专业备份工具mysqldump或mysqlhotcopy mysqldump常用的逻辑备份工具 mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表启用二进制日志进行增量备份进行增量备份，需要刷新二进制日志第三方工具备份免费的MySQL热备份软件Percona XtraBackup

2.2、MySQL完全备份简介

1、是对整个数据库、数据库结构和文件结构的备份 2、保存的是备份完成时刻的数据库 3、是差异备份与增量备份的基础 4、每次对数据进行完整的备份，完全备份是增量备份的基础，完全备份保存的是备份完成时刻的数据库 5、优点：备份与恢复操作简单方便 6、缺点数据存在大量的重复占用大量的备份空间备份与恢复时间长

2.3、数据库完全备份分类

1、物理冷备份与恢复关闭MySQL数据库使用tar命令直接打包数据库文件夹直接替换现有MySQL目录即可 2、mysqldump备份与恢复 MySQL自带的备份工具，可方便实现对MySQL的备份可以将指定的库、表导出为SQL脚本（.sql结尾）使用命令MySQL导入备份的数据

2.4、物理冷备份与恢复步骤

1、在MySQL中先创建test库，再创建aa表，写入一行记录（用于验证） 2、先关闭数据库服务，再打包备份

[root@server1 ~]# systemctl stop mysqld [root@server1 ~]# mkdir /backup [root@server1 ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data [root@server1 ~]# cd /backup [root@server1 backup]# ll 总用量 1376 -rw-r--r--. 1 root root 1405224 10月 23 20:30 mysql_all-2020-10-23.tar.gz

3、将原来的数据移走到备份文件夹中，解压刚才备份的tar包到/restore目录下，再移动到mysql服务的文件夹中

[root@server1 ~]# mkdir bak [root@server1 ~]# mv /usr/local/mysql/data/ /bak ###将数据库的文件移动至/bak文件夹中 [root@server1 ~]# mkdir restore [root@server1 ~]# tar zxf /backup/mysql_all-2020-10-23.tar.gz -C restore [root@server1 ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/ ###将之前备份的文件移动至mysql服务的文件夹中

4、重启mysql服务，登录mysql，查看数据是否恢复

[root@server1 ~]# systemctl restart mysqld

2.5、mysqldump备份和恢复

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###备份所有数据库 mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库 mysqldump -u root -p auth > auth-$(data +%F).sql ###备份auth数据库 mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表 mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构

1、方法一

[root@server1 ~]# mysqldump -u root -p test > test-$(date +%F).sql mysql> drop database test; mysql> create database test2; ###建立空库 [root@server1 ~]# mysql -u root -p test2 < test-2020-10-23.sql

2、方法二

[root@server1 ~]# mysqldump -u root -p test > test-$(date +%F).sql mysql> drop database test; mysql> create database test2; mysql> use test2; mysql> source /root/test-2020-10-24.sql;

三、MySQL增量备份与恢复

###MySQL的日志文件记录了详细的操作过程，千万不能随便删###

3.1、MySQL增量备份

1、使用mysqldump进行完全备份存在的问题备份数据中有重复数据备份时间与恢复时间过长 2、MySQL增量备份是自上一次备份后增加/变化的文件或者内容 3、特点没有重复数据，备份量不大，时间短恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

4、MySQL没有提供直接的增量备份方法可通过MySQL提供的二进制日志间接实现增量备份 5、MySQL二进制日志对备份的意义二进制日志保存了所有更新或者可能更新数据库的操作二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

3.2、MySQL数据库增量恢复

1、一般恢复将所有备份的二进制日志内容全部恢复 2、断点恢复基于位置恢复数据库在某一时间点可能既有错误的操作也有正确的操作可以基于精准的位置跳过错误的操作基于时间点恢复跳过某个发生错误的时间点实现数据恢复

3.3、增量恢复的方法

1、一般恢复 mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p 2、基于位置的恢复恢复数据到指定位置（到错误操作前的最后一次正确操作） mysqlbinlog --stop-position=‘操作id’ 二进制日志 | mysql -u 用户名 -p密码从指定的位置开始恢复数据（跳过错误操作后的第一次正确操作） mysqlbinlog --start-position=‘操作id’ 二进制日志 | mysql -u 用户名 -p密码 3、基于时间点恢复跳过某个发生错误的时间点实现数据恢复恢复数据到指定时间（停止错误操作的时间） mysqlbinlog --stop-datetime=‘错误时间’ 二进制日志 | mysql -u 用户名 -p密码从指定的位置开始恢复数据（跳过错误操作后的第一次正确操作） mysqlbinlog --start-datetime=‘正确操作时间’ 二进制日志 | mysql -u 用户名 -p密码

时间点恢复：

[root@server1 ~]# mkdir -p /opt/bak_sql [root@server1 ~]# mysqldump -uroot -p test2 > /opt/bak_sql/test2-$(date +%F).sql; ###完整备份 [root@server1 ~]# vi /etc/my.cnf [mysqld] log_bin=/usr/local/mysql/data/mysql_bin ###开启增量备份 [root@server1 ~]# systemctl restart mysqld [root@server1 ~]# mysqladmin -uroot -p flush-logs ###将二进制日志更新，产生新的日志文件 [root@server1 ~]# cd /usr/local/mysql/data/ [root@server1 data]# ll ###查询增量备份结果 [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查询该二进制日志内容是否正确 mysql> insert into aa values(4,'sisi',90); ###正确操作 mysql> delete from aa where name='zhangsan'; ###错误操作 mysql> insert into aa values(5,'haha',89); ###正确操作 mysql> select * from aa; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 1 | lisi | 88 | | 3 | wangwu | 78 | | 4 | sisi | 90 | | 5 | haha | 89 | +----+--------+-------+ 4 rows in set (0.01 sec) [root@server1 ~]# mysqladmin -u root -p flush-logs; ###将二进制日志更新，产生新的日志文件 [root@server1 ~]# cd /usr/local/mysql/data/ [root@server1 data]# ll ###查询增量备份结果 [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查询该二进制日志内容是否正确 ###还原时间点的步骤 mysql> use test2; mysql> drop table aa; ###先删掉坏的那张表 [root@server1 ~]# mysql -u root -p test2 < /opt/bak_sql/test2-2020-10-24.sql ###还原完全备份的数据库 [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查询该二进制日志内容 # at 613 #201024 0:55:25 server id 1 end_log_pos 665 CRC32 0xa674c8df Delete_rows: table id 219 flags: STMT_END_F ### DELETE FROM `test2`.`aa` ### WHERE ### @1=2 ### @2='zhangsan' ### @3='60' # at 665 #201024 0:55:25 server id 1 end_log_pos 696 CRC32 0x297a966a Xid = 14 COMMIT/*!*/; # at 696 #201024 0:55:48 server id 1 end_log_pos 761 CRC32 0x11834792 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 761 #201024 0:55:48 server id 1 end_log_pos 834 CRC32 0x28de3d38 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1603526148/*!*/; BEGIN /*!*/; # at 834 #201024 0:55:48 server id 1 end_log_pos 886 CRC32 0xa67e20b9 Table_map: `test2`.`aa` mapped to number 219 # at 886 #201024 0:55:48 server id 1 end_log_pos 934 CRC32 0x8a2c0765 Write_rows: table id 219 flags: STMT_END_F ### INSERT INTO `test2`.`aa` ### SET ### @1=5 ### @2='haha' ### @3='89' [root@server1 ~]# mysqlbinlog --no-defaults --stop-datetime='2020-10-24 0:55:25' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###停止错误的时间 [root@server1 ~]# mysqlbinlog --no-defaults --start-datetime='2020-10-24 0:55:48' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###开始正确的时间 mysql> select * from aa; ###查看是否复原 +----+----------+-------+ | id | name | score | +----+----------+-------+ | 1 | lisi | 88 | | 2 | zhangsan | 60 | | 3 | wangwu | 78 | | 4 | sisi | 90 | | 5 | haha | 89 | +----+----------+-------+ 5 rows in set (0.00 sec)

位置点恢复：

mysql> use test2; mysql> delete from aa where name='lisi'; ###误操作 mysql> delete from aa where name='haha'; ###误操作 [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查询该二进制日志内容 ...省略内容 # at 2168 #201024 1:26:20 server id 1 end_log_pos 2220 CRC32 0x5a192cfd Table_map: `test2`.`aa` mapped to number 221 # at 2220 #201024 1:26:20 server id 1 end_log_pos 2268 CRC32 0x30f35bdf Delete_rows: table id 221 flags: STMT_END_F ### DELETE FROM `test2`.`aa` ### WHERE ### @1=1 ### @2='lisi' ### @3='88' # at 2268 #201024 1:26:20 server id 1 end_log_pos 2299 CRC32 0xe8fa9bd8 Xid = 123 COMMIT/*!*/; # at 2299 #201024 1:26:26 server id 1 end_log_pos 2364 CRC32 0xfa901848 Anonymous_GTID last_committed=9 sequence_number=10 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 2364 #201024 1:26:26 server id 1 end_log_pos 2437 CRC32 0x04379db1 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1603527986/*!*/; BEGIN /*!*/; # at 2437 #201024 1:26:26 server id 1 end_log_pos 2489 CRC32 0xa0c3b6c1 Table_map: `test2`.`aa` mapped to number 221 # at 2489 #201024 1:26:26 server id 1 end_log_pos 2537 CRC32 0xd7509926 Delete_rows: table id 221 flags: STMT_END_F ### DELETE FROM `test2`.`aa` ### WHERE ### @1=5 ### @2='haha' ### @3='89' # at 2537 #201024 1:26:26 server id 1 end_log_pos 2568 CRC32 0xb7b17eee Xid = 124 ...省略内容 [root@server1 ~]# mysqlbinlog --no-defaults --stop-position='2168' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ###上一次操作正确的位置点停止 [root@server1 ~]# mysqlbinlog --no-defaults --start-position='2537' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ###下一次操作正确的位置点开始 mysql> select * from aa; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 1 | lisi | 88 | | 2 | zhangsan | 60 | | 3 | wangwu | 78 | | 4 | sisi | 90 | | 5 | haha | 89 | +----+----------+-------+ 5 rows in set (0.00 sec)

最新回复(0)