Linux之mysql数据备份案例

注意：本案例中很多地方为了方便展示直接输出了明文密码，但是实际操作不要这样！
增量备份前提：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)