注意:本案例中很多地方为了方便展示直接输出了明文密码,但是实际操作不要这样!
增量备份前提:mysql log-bin日志功能开启(一般是默认开启的)
mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)
log_bin=on表示开启
log_bin_basename=/var/lib/mysql/binlog表示二进制文件保存路径
假设此时xingyun库中的student表是这样的
mysql> select * from student;
+-----+--------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+--------+------+------+-----------------+
| 1 | 路飞 | 男 | 24 | 计算机系 |
| 2 | 刘洋 | 女 | 25 | 电子商务系 |
| 3 | tom | 男 | 25 | 英语系 |
+-----+--------+------+------+-----------------+
3 rows in set (0.00 sec)
到零点手工全备份模拟半夜零点全备
[root@localhost ~]# date -s "20210119"
2021年 01月 19日 星期二 00:00:00 CST
[root@localhost ~]# mysqldump -uroot -pMa=123456 --default-character-set=utf8 --single-transaction -F -B xingyun -e | gzip > /data/mysql/mysql_backup_`date +%F`.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份后继续插入数据
mysql> insert into student values (4, '大雄', '男', 26, '美术系'), (5, '草莓', '女', 24, '美食系');
Query OK, 2 rows affected (0.01 sec)
模拟用户破坏数据
mysql> drop database xingyun;
Query OK, 1 row affected (0.04 sec)
增量备份-恢复过程
1、检查凌晨备份
2、检查全备后的所有binlog
[root@localhost ~]# ls -lrt /var/lib/mysql/binlog.*
-rw-r----- 1 mysql mysql 1505 1月 19 00:00 /var/lib/mysql/binlog.000002
-rw-r----- 1 mysql mysql 48 1月 19 00:00 /var/lib/mysql/binlog.index
-rw-r----- 1 mysql mysql 691 1月 19 00:04 /var/lib/mysql/binlog.000003
-rw-r----- 1 mysql mysql 693 1月 19 2021 /var/lib/mysql/binlog.000001
3、立即刷新并备份出binlog
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost ~]# cp /var/lib/mysql/binlog.* /data/mysql/backup/
4、恢复凌晨备份
[root@localhost mysql]# gunzip mysql_backup_2021-01-19.sql.gz
[root@localhost mysql]# mysql -uroot -pMa=123456 -e 'create database xingyun'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# mysql -uroot -pMa=123456 xingyun < mysql_backup_2021-01-19.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
此时查表发现,凌晨备份前的数据已恢复,但之后新加的并没有恢复
mysql> select * from student;
+-----+--------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+--------+------+------+-----------------+
| 1 | 路飞 | 男 | 24 | 计算机系 |
| 2 | 刘洋 | 女 | 25 | 电子商务系 |
| 3 | tom | 男 | 25 | 英语系 |
+-----+--------+------+------+-----------------+
3 rows in set (0.00 sec)
5、恢复增量备份
从bakeup下的二进制文件中找需要恢复的文件,可以将二进制文件转化成.sql语句
[root@localhost backup]# mysqlbinlog binlog.000003 > test03.sql
[root@localhost backup]# vim test03.sql
找到drop,恢复此文件,恢复前一定要注释掉此文件中的drop
通过时间戳可以看出此文件就是从零点开始一直到被drop(此处数据较少,时间相隔也较小,如若数据量大,时间间隔也大,需要找到并恢复不止一个文件)
此处报错不用管,是由于注释掉了drop,不影响恢复
[root@localhost backup]# mysql -uroot -pMa=123456 < test03.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1790 (HY000) at line 66: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
mysql> select * from student
-> ;
+-----+--------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+--------+------+------+-----------------+
| 1 | 路飞 | 男 | 24 | 计算机系 |
| 2 | 刘洋 | 女 | 25 | 电子商务系 |
| 3 | tom | 男 | 25 | 英语系 |
| 4 | 大雄 | 男 | 26 | 美术系 |
| 5 | 草莓 | 女 | 24 | 美食系 |
+-----+--------+------+------+-----------------+
5 rows in set (0.00 sec)