Mysql数据库的备份与恢复技术 Mysql数据库的备份与恢复
一、数据备份的重要性
1.1、在生产环境中，数据的安全性至关重要
1.2、任何数据的丢失都可能产生严重的后果
1.3、造成数据丢失的原因
1.3.1、程序错误
1.3.2、人为操作错误
1.3.3、运算错误
1.3.4、磁盘故障
1.3.5、灾难（如火灾、地震等）
举例：携程宕机的损失为每小时106.48万美元
二、数据库备份的分类
2.1、从物理与逻辑的角度，备份可分为
2.1.1、物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份方法：
①冷备份（脱机备份）：是在关闭数据库的时候进行的
②热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
③温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
2.1.2、逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份
2.2、从数据库的备份策略角度，备份可分为
2.2.1、完全备份：每次对数据库进行完整的备份
2.2.2、差异备份：备份自从上次完全备份之后被修改过的文件
2.2.3、增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份
三、常见的备份方法
3.1、物理冷备
3.1.1、备份时将数据库处于关闭状态，直接打包数据库文件
3.1.2、备份速度快，恢复时也是最简单的
3.2、专用备份工具mydump或mysqlhotcopy
3.2.1、mydump常用的逻辑备份工具
3.2.2、mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
3.3、启用二进制日志进行增量备份
3.3.1、进行增量备份，需要刷新二进制日志
3.4、第三方工具备份
3.4.1、免费的MySQL热备份软件Percona XtraBackup
四、MySQL完全备份
4.1、是对整个数据库、数据库结构和文件结构的备份
4.2、保存的是备份完成时刻的数据库
4.3、是差异备份与增量备份的基础
4.4、优点：
4.4.1、备份与恢复操作简单方便
4.5、缺点：
4.5.1、数据存在大量的重复
4.5.2、占用大量的备份空间
4.5.3、备份与恢复时间长
4.6、数据库完全备份分类
4.6.1、物理冷备份与恢复
①关闭MySQL数据库
②使用tar命令直接打包数据库文件夹
③直接替换现有MySQL目录即可
4.6.2、mysqldump备份与恢复
①MySQL自带的备份工具，可方便实现对MySQL的备份
②可以将指定的库、表导出为SQL脚本
③使用命令mysql导入备份的数据
五、MySQL物理冷备份及恢复
1 #数据库创建数据库和表 2 mysql -uroot -p 3 mysql> create datebase test; 4 mysql> use test; 5 mysql> create table aa(id char(24) not null,name varchar(36) not null,score int(3) not null,primary key(id)); 6 mysql> insert into aa(id,name,score) values(1,\'lisi\',88),(2,\'zhangsan\',60),(3,\'wangwu\',78); 7 mysql> select * from aa; 8 #物理冷备份 9 systemctl stop mysqld #冷备份一定要关闭数据库服务 10 mkdir /backup 11 tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data 12 systemctl start mysqld 13 #误删除操作 14 mysql -uroot -p 15 mysql> delete from aa where name=\'zhangsan\'; 16 mysql> select * from aa; 17 #恢复数据库 18 systemctl stop mysqld 19 mkdir /bak 20 mv /usr/local/mysql/data /bak 21 mkdir /restore 22 tar zxf /backup/mysql_all-$(date +%F).tar.gz -C /restore 23 mv /restore/usr/local/mysql/date /usr/local/mysql 24 systemctl start mysqld 25 #查看数据 26 mysql -uroot -p 27 mysql> use test; 28 mysql> select * from aa;
六、mysqldump备份和恢复
1 mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###备份所有数据库 2 mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库 3 mysqldump -u root -p auth > auth-$(data +%F).sql ###备份auth数据库 4 mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表 5 mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构
6.1、方法一
1 mysqldump -u root -p test > test-$(date +%F).sql 2 mysql> drop database test; 3 mysql> create database test2; ###建立空库 4 mysql -u root -p test2 < test-2020-10-23.sql
6.2、方法二
1 mysqldump -u root -p test > test-$(date +%F).sql 2 mysql> drop database test; 3 mysql> create database test2; 4 mysql> use test2; 5 mysql> source /root/test-2020-10-24.sql;
七、MySQL增量备份与恢复
7.1、MySQL增量备份
7.1.1、使用mysqldump进行完全备份存在的问题
①备份数据中有重复数据
②备份时间与恢复时间过长
7.1.2、MySQL增量备份是自上一次备份后增加/变化的文件或者内容
7.1.3、特点
没有重复数据，备份量不大，时间短
恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
7.2、MySQL数据库增量恢复
7.2.1、一般恢复
将所有备份的二进制日志内容全部恢复
7.2.2、断点恢复
基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
基于时间点恢复
跳过某个发生错误的时间点实现数据恢复
7.3、增量恢复的方法
7.3.1、一般恢复
mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p
7.3.2、基于位置的恢复
恢复数据到指定位置（到错误操作前的最后一次正确操作）
mysqlbinlog –stop-position=‘操作id’ 二进制日志 | mysql -u 用户名 -p密码
从指定的位置开始恢复数据（跳过错误操作后的第一次正确操作）
mysqlbinlog –start-position=‘操作id’ 二进制日志 | mysql -u 用户名 -p密码
7.3.3、基于时间点恢复
跳过某个发生错误的时间点实现数据恢复
恢复数据到指定时间（停止错误操作的时间）
mysqlbinlog –stop-datetime=‘错误时间’ 二进制日志 | mysql -u 用户名 -p密码
从指定的位置开始恢复数据（跳过错误操作后的第一次正确操作）
mysqlbinlog –start-datetime=‘正确操作时间’ 二进制日志 | mysql -u 用户名 -p密码
时间点恢复：
1 [root@server1 ~]# mkdir -p /opt/bak_sql 2 [root@server1 ~]# mysqldump -uroot -p test2 > /opt/bak_sql/test2-$(date +%F).sql; ###完整备份 3 4 [root@server1 ~]# vi /etc/my.cnf 5 [mysqld] 6 log_bin=/usr/local/mysql/data/mysql_bin ###开启增量备份 7 8 [root@server1 ~]# systemctl restart mysqld 9 10 [root@server1 ~]# mysqladmin -uroot -p flush-logs ###将二进制日志更新，产生新的日志文件 11 [root@server1 ~]# cd /usr/local/mysql/data/ 12 [root@server1 data]# ll ###查询增量备份结果 13 14 [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查询该二进制日志内容是否正确 15 16 mysql> insert into aa values(4,\'sisi\',90); ###正确操作 17 mysql> delete from aa where name=\'zhangsan\'; ###错误操作 18 mysql> insert into aa values(5,\'haha\',89); ###正确操作 19 mysql> select * from aa; 20 +----+--------+-------+ 21 | id | name | score | 22 +----+--------+-------+ 23 | 1 | lisi | 88 | 24 | 3 | wangwu | 78 | 25 | 4 | sisi | 90 | 26 | 5 | haha | 89 | 27 +----+--------+-------+ 28 4 rows in set (0.01 sec) 29 30 [root@server1 ~]# mysqladmin -u root -p flush-logs; ###将二进制日志更新，产生新的日志文件 31 [root@server1 ~]# cd /usr/local/mysql/data/ 32 [root@server1 data]# ll ###查询增量备份结果 33 [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查询该二进制日志内容是否正确 34 35 ###还原时间点的步骤 36 mysql> use test2; 37 mysql> drop table aa; ###先删掉坏的那张表 38 [root@server1 ~]# mysql -u root -p test2 < /opt/bak_sql/test2-2020-10-24.sql ###还原完全备份的数据库 39 40 [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查询该二进制日志内容 41 # at 613 42 #201024 0:55:25 server id 1 end_log_pos 665 CRC32 0xa674c8df Delete_rows: table id 219 flags: STMT_END_F 43 ### DELETE FROM `test2`.`aa` 44 ### WHERE 45 ### @1=2 46 ### @2=\'zhangsan\' 47 ### @3=\'60\' 48 # at 665 49 #201024 0:55:25 server id 1 end_log_pos 696 CRC32 0x297a966a Xid = 14 50 COMMIT/*!*/; 51 # at 696 52 #201024 0:55:48 server id 1 end_log_pos 761 CRC32 0x11834792 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes 53 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; 54 SET @@SESSION.GTID_NEXT= \'ANONYMOUS\'/*!*/; 55 # at 761 56 #201024 0:55:48 server id 1 end_log_pos 834 CRC32 0x28de3d38 Query thread_id=4 exec_time=0 error_code=0 57 SET TIMESTAMP=1603526148/*!*/; 58 BEGIN 59 /*!*/; 60 # at 834 61 #201024 0:55:48 server id 1 end_log_pos 886 CRC32 0xa67e20b9 Table_map: `test2`.`aa` mapped to number 219 62 # at 886 63 #201024 0:55:48 server id 1 end_log_pos 934 CRC32 0x8a2c0765 Write_rows: table id 219 flags: STMT_END_F 64 ### INSERT INTO `test2`.`aa` 65 ### SET 66 ### @1=5 67 ### @2=\'haha\' 68 ### @3=\'89\' 69 70 [root@server1 ~]# mysqlbinlog --no-defaults --stop-datetime=\'2020-10-24 0:55:25\' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###停止错误的时间 71 72 [root@server1 ~]# mysqlbinlog --no-defaults --start-datetime=\'2020-10-24 0:55:48\' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###开始正确的时间 73 74 mysql> select * from aa; ###查看是否复原 75 +----+----------+-------+ 76 | id | name | score | 77 +----+----------+-------+ 78 | 1 | lisi | 88 | 79 | 2 | zhangsan | 60 | 80 | 3 | wangwu | 78 | 81 | 4 | sisi | 90 | 82 | 5 | haha | 89 | 83 +----+----------+-------+ 84 5 rows in set (0.00 sec)
位置点恢复：
1 mysql> use test2; 2 mysql> delete from aa where name=\'lisi\'; ###误操作 3 mysql> delete from aa where name=\'haha\'; ###误操作 4 5 [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查询该二进制日志内容 6 ...省略内容 7 # at 2168 8 #201024 1:26:20 server id 1 end_log_pos 2220 CRC32 0x5a192cfd Table_map: `test2`.`aa` mapped to number 221 9 # at 2220 10 #201024 1:26:20 server id 1 end_log_pos 2268 CRC32 0x30f35bdf Delete_rows: table id 221 flags: STMT_END_F 11 ### DELETE FROM `test2`.`aa` 12 ### WHERE 13 ### @1=1 14 ### @2=\'lisi\' 15 ### @3=\'88\' 16 # at 2268 17 #201024 1:26:20 server id 1 end_log_pos 2299 CRC32 0xe8fa9bd8 Xid = 123 18 COMMIT/*!*/; 19 # at 2299 20 #201024 1:26:26 server id 1 end_log_pos 2364 CRC32 0xfa901848 Anonymous_GTID last_committed=9 sequence_number=10 rbr_only=yes 21 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; 22 SET @@SESSION.GTID_NEXT= \'ANONYMOUS\'/*!*/; 23 # at 2364 24 #201024 1:26:26 server id 1 end_log_pos 2437 CRC32 0x04379db1 Query thread_id=12 exec_time=0 error_code=0 25 SET TIMESTAMP=1603527986/*!*/; 26 BEGIN 27 /*!*/; 28 # at 2437 29 #201024 1:26:26 server id 1 end_log_pos 2489 CRC32 0xa0c3b6c1 Table_map: `test2`.`aa` mapped to number 221 30 # at 2489 31 #201024 1:26:26 server id 1 end_log_pos 2537 CRC32 0xd7509926 Delete_rows: table id 221 flags: STMT_END_F 32 ### DELETE FROM `test2`.`aa` 33 ### WHERE 34 ### @1=5 35 ### @2=\'haha\' 36 ### @3=\'89\' 37 # at 2537 38 #201024 1:26:26 server id 1 end_log_pos 2568 CRC32 0xb7b17eee Xid = 124 39 ...省略内容 40 41 [root@server1 ~]# mysqlbinlog --no-defaults --stop-position=\'2168\' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ###上一次操作正确的位置点停止 42 [root@server1 ~]# mysqlbinlog --no-defaults --start-position=\'2537\' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ###下一次操作正确的位置点开始 43 44 mysql> select * from aa; 45 +----+----------+-------+ 46 | id | name | score | 47 +----+----------+-------+ 48 | 1 | lisi | 88 | 49 | 2 | zhangsan | 60 | 50 | 3 | wangwu | 78 | 51 | 4 | sisi | 90 | 52 | 5 | haha | 89 | 53 +----+----------+-------+ 54 5 rows in set (0.00 sec)
版权声明：本文为wqhao原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。