文章目录
一、数据备份的重要性
- 在生产环境中,数据的安全性至关重要
- 任何数据的丢失都可能产生严重的后果
- 造成数据丢失的原因
- 程序错误
- 人为操作错误
- 运算错误
- 磁盘故障
- 灾难(如火灾、地震)和盗窃
二、数据库备份的分类
- 从物理与逻辑的角度,备份可以分为
- 物理备份:催数据库操作系统的物理文件(如数据文件、日志文件等)的备份
- 物理备份方法
- 冷备份(脱机备份):是在关闭数据库的时候进行
- 热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
- 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
- 逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
- 物理备份:催数据库操作系统的物理文件(如数据文件、日志文件等)的备份
- 从数据库的备份策略角度,备份可以分为:
- 完全备份:每次对数据库进行完整的备份
- 差异备份:备份自从上次完全备份之后被修改过的文件
- 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会备份
三、常见的备份方法
- 物理冷备
- 备份时数据库处于关闭状态,直接打包数据库文件
- 备份速度快,恢复时也是最简单的
- 专用备份工具mydump或mysqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
- 启用二进制日志进行增量备份
- 进行增量备份,需要刷新二进制日志
- 第三方工具备份
- 免费的MySQL热备份软件Percona XtraBackup
四、MySQL完全备份
4.1 完全备份简介
-
是对整个数据库、数据库结构和文件结构的备份
-
保存的是备份完成时刻的数据库
-
是差异备份与增量备份的基础
-
优点
- 备份与恢复操作简单方便
-
缺点
- 数据存在大量的重复
- 占用大量的备份空间
- 备份与恢复的时间长
4.2 数据库完全备份分类
- 物理冷备份与恢复
- 关闭MySQL数据库
- 使用tar命令直接打包数据库文件夹
- 直接替换现有MySQL目录即可
- mysqldump备份与恢复
- MySQL自带的备份工具,可以方便实现对IMySQL的备份
- 可以将制定的库、表到处为SQL脚本
- 使用命令mysql导入备份的数据
4.3 物理冷备份和恢复
1)先创建一个初始表
mysql> select * from student;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
+----+----------+-------+
1 row in set (0.00 sec)
2)关闭数据库,打包备份
[root@mysql1 ~]# systemctl stop mysqld
[root@mysql1 ~]# mkdir /backup
[root@mysql1 ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
3)备份文件,解压备份的tar包到/restore目录下,再restore目录移动到mysql文件夹中
[root@mysql1 ~]# mkdir back
[root@mysql1 ~]# mv /usr/local/mysql/data/ /back
[root@mysql1 ~]# mkdir restore
[root@mysql1 ~]# tar zxf /backup/mysql_all-2020-12-26.tar.gz -C restore/
[root@mysql1 ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
4)启动mysql服务,登录数据库验证结果
[root@mysql1 ~]# systemctl start mysqld
[root@mysql1 ~]# mysql -uroot -p123456
mysql> use school;
mysql> select * from student;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
+----+----------+-------+
1 row in set (0.00 sec)
4.4 mysqldump备份数据库
- mysqldump命令对单个库进行完全备份
mysqldump -u 用户名 -p [密码] [选项] [数据库名] > /备份路径/备份文件名
- 单库备份的示例
mysqldump -u root -p auth > /backup/auth.sql
mysqldump -u root -p mysql > /backup/mysql.sql
- musqldump命令对多个库进行完全备份
mysqldump -u 用户名 -p [密码] [选项] --database 库名1 [库名2]... > /备份路径/备份文件名
- 多库备份的示例
mysqldump -u root -p --database auth mysql > /backup/databases-auth-mysql.sql
- mysqldump命令对所有库进行完全备份
mysqldump -u 用户名 -p [密码] [选项] --all-database > /备份路径/备份文件名
- 所有库备份的示例
mysqldump -uroot -p --all-databases > /backup/all-data.sql
五、MySQL增量备份
5.1 增量备份简介
- 使用mysqldump进行完全备份存在的问题
- 备份数据中有重复数据
- 备份时间与恢复时间过长
- 是自上一次备份后增加/变化的文件或者内容
- 特点
- 没有重复数据,备份量不大,时间短
- 恢复需要上次完全备份以及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复
- MySQL没有提供直接的增量备份的方法
- 可通过MySQL提供的二进制日志间接实现增量备份
- MySQL二进制日志对备份的意义
- 二进制日志保存了所有更新或者可能更新数据库的操作
- 二进制日志在启动MySQL服务器后开始记录,并在文件达到max_binlog_size所设置的大小或者接收到fush logs命令后重新创建新的日志文件
- 只需定时执行flush logs方法重新创建新的日志,生成二进制文件序列,并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份
5.2 MySQL数据库增量恢复
- 一般恢复
- 将所有备份的二进制日志内容全部恢复
- 基于位置恢复
- 数据库在某一时间点可能既有错误的操作也有正确的操作
- 可以基于精准的未知跳过错误的操作
- 基于时间点恢复
- 跳过某个发生错误的时间点实现数据恢复
5.3 MySQL增量恢复操作
5.3.1 时间点恢复
1)初始表状态
mysql> select * from student;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
+----+----------+-------+
1 row in set (0.00 sec)
2)完整备份
[root@mysql1 ~]# mkdir /backup
[root@mysql1 ~]# mysqldump -uroot -p school > /backup/school-$(date +%F).sql
Enter password:
[root@mysql1 ~]# vi /etc/my.cnf
[mysqld]
log_bin=/usr/local/mysql/data/mysql_bin ###开启增量备份
[root@mysql1 ~]# systemctl restart mysqld ##重启服务
[root@mysql1 ~]# mysqladmin -uroot -p flush-logs ## 更新二进制日志文件
Enter password:
cd /usr/local/mysql/data/
[root@mysql1 data]# ll ##查询增量备份结果
-rw-r-----. 1 mysql mysql 201 12月 26 10:46 mysql_bin.000001
-rw-r-----. 1 mysql mysql 154 12月 26 10:46 mysql_bin.000002
3)模拟错误
mysql> insert into student values(2,'xiaohong',60); ##正确操作
Query OK, 1 row affected (0.00 sec)
mysql> delete from student where name='xiaoming'; ## 误删操作
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(3,'xiaobai',100); ## 正确操作
Query OK, 1 row affected (0.00 sec)
4)还原步骤
[root@mysql1 data]# mysqladmin -uroot -p flush-logs ## 刷新二进制日志
Enter password:
[root@mysql1 data]# ll
-rw-r-----. 1 mysql mysql 201 12月 26 10:46 mysql_bin.000001
-rw-r-----. 1 mysql mysql 1595 12月 26 10:49 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 12月 26 10:49 mysql_bin.000003
mysql> drop table student; ## 删除坏表
Query OK, 0 rows affected (0.00 sec)
[root@mysql1 data]# mysql -uroot -p school < /backup/school-2020-12-26.sql ##还原完全备份的数据库
Enter password:
## 查看日志文件找到误操作时间
#201226 10:49:27 server id 1 end_log_pos 1239 CRC32 0xfcaabdcb Delete_rows: table id 118 flags: STMT_END_F
### DELETE FROM `school`.`student`
### WHERE
### @1=1
### @2='xiaoming'
### @3=100
# at 1239
#201226 10:49:27 server id 1 end_log_pos 1270 CRC32 0x7211246f Xid = 15
COMMIT/*!*/;
# at 1270
#201226 10:49:39 server id 1 end_log_pos 1335 CRC32 0xd3fe0bad Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1335
#201226 10:49:39 server id 1 end_log_pos 1409 CRC32 0xe4827e65 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1608950979/*!*/;
BEGIN
/*!*/;
# at 1409
#201226 10:49:39 server id 1 end_log_pos 1465 CRC32 0x712e89d0 Table_map: `school`.`student` mapped to number 118
# at 1465
#201226 10:49:39 server id 1 end_log_pos 1517 CRC32 0xf8f6b5f9 Write_rows: table id 118 flags: STMT_END_F
### INSERT INTO `school`.`student`
### SET
### @1=3
### @2='xiaobai'
### @3=100
# at 1517
#201226 10:49:39 server id 1 end_log_pos 1548 CRC32 0x2d820930 Xid = 16
COMMIT/*!*/;
# at 1548
#201226 10:49:57 server id 1 end_log_pos 1595 CRC32 0x89372dd6 Rotate to mysql_bin.000003 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
```java
[root@mysql1 ~]# mysqlbinlog --no-defaults --stop-datetime='2020-12-26 10:49:27' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p ##停止错误时间
Enter password:
[root@mysql1 ~]# mysqlbinlog --no-defaults --start-datetime='2020-12-26 10:49:39' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p ## 开始正确时间
Enter password:
5)验证结果
mysql> select * from student;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
+----+----------+-------+
3 rows in set (0.00 sec)
5.3.2 位置节点恢复
1)模拟错误
mysql> delete from student where name='xiaobai';
Query OK, 1 row affected (0.01 sec)
2)恢复过程
[root@mysql1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ## 查看日志文件找到错误位置点
# at 2744
#201226 11:53:52 server id 1 end_log_pos 2800 CRC32 0xf097576f Table_map: `school`.`student` mapped to number 221
# at 2800
#201226 11:53:52 server id 1 end_log_pos 2852 CRC32 0x2c59369c Delete_rows: table id 221 flags: STMT_END_F
### DELETE FROM `school`.`student`
### WHERE
### @1=3
### @2='xiaobai'
### @3=100
# at 2852
#201226 11:53:52 server id 1 end_log_pos 2883 CRC32 0x8f573f0d Xid = 130
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mysql1 ~]# mysqlbinlog --no-defaults --stop-position='2744' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ## 上一次操作正确的位置点停止
Enter password:
[root@mysql1 ~]# mysqlbinlog --no-defaults --start-position='2852' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ## 下一次操作正确的位置点开始
Enter password:
3)验证
mysql> use school;
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
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
mysql> select * from student;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
+----+----------+-------+
3 rows in set (0.00 sec)