一.MySQL数据库管理之全量与增量详解

1.1全量备份与增量备份

1.全量备份

全量备份
全量数据就是数据库中所有的数据，全量备份就是把数据库中所有的数据进行备份
例：
备份所有库
~]# mysqldump -uroot -p12345678 -S /data/3306/mysql.sock -A -B -F --events|gzip>/tmp/all.backup.$(date +%F).sql.gz
备份一个库
~]# mysqldump -uroot -p12345678 -S /data/3306/mysql.sock -F --events -B john|gzip>/tmp/john.backup.$(date +%F).sql.gz

2. 增量备份

增量数据是从上次全量备份之后，更新的新数据，对于mysql来说，binlog日志就是mysql的增量数据
例：
按天全备情况
优点：恢复时间：短，维护成本：低
缺点：占用空间：多，占用系统资源多，经常锁表影响用户体验

周一00点全量备份                          周二00点全量备份                     .....
01.sql.gz                                02.sql.gz           
周一增量备份                              周二增量数据
mysql-bin.000024                         mysql-bin.000037 
mysql-bin.000025                         mysql-bin.000038
mysql-bin.000026                         mysql-bin.000039
.............................            .............................
mysql-bin.index (binlog的索引文件)        mysql-bin.index (binlog的索引文件)

按周全备情况
缺点：维护成本高，恢复麻烦，时间长
优点：占用空间：少，占用系统资源少，无需锁表，用户体验好一些

周六00点全量备份
01. sql.gz
周六增量备份
mysql-bin.000024
mysql-bin.000025
mysql-bin.000026
.............................
mysql-bin.index (binlog的索引文件)

3.企业场景和增量的频率

1）中小公司，全量一般是每天一次，业务流量低谷执行全备，备份时会锁表
增量备：
定时推binlog增量，例如每分钟推一次增量
rsync –avz /data/3306/mysql-bin000* rsync_backup@118.190.201.68 –password-file=/etc/rsync.password
2）大公司周备，每周六00点一次全备，周日-下周六00点前都是增量
优点：节省备份时间，减少备份压力，缺点：增量的binlog文件副本太多，还原会很麻烦
3）一主多从环境，主从复制的意义就是实时远程备份，可以解决服务器物理故障
4）一主多从环境，可采取一个从库服务器上专门用mysqldump，cp，tar，xtrabackup做备份，延迟同步

4.mysql增量恢复必备条件

1、开启mysql log-bin日志功能
mysql数据库开启了log-bin参数记录binlog日志功能如下：
[root@mysql ~]# grep 'log-bin' /data/3306/my.cnf 
log-bin = /data/3306/mysql-bin
提示：主库和备份的从库都要开启binlog记录功能
小结：
增量恢复的条件：
存在一份全备加上全备之后的时刻到出问题时刻的所有增量binlog文件备份

5.MySQL备份常用方法

MySQL备份的常用方式有逻辑备份和物理备份（oracl也是如此）

6.实现全备与增备的问题案例

实战模拟凌晨00点对john库做个全备，早上10点左右删除了john库，下面是其恢复过程

备份过程
~]# mkdir -p /backup/
~]# date -s '2018/6/17 00:00'
~]# mysqldump -u root -p12345678 -S /data/3306/mysql.sock -F -R -x -B john --master-data=2 |gzip >/backup/john_$(date +%F).sql.gz
~]# ll /backup/john_$(date +%F).sql.gz
-rw-r--r-- 1 root root 1547 Jun 17 00:03 /backup/john_2018-06-17.sql.gz

mysql> use john
mysql> insert into test(id,name) values(7,'john02');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert into test(id,name) values(8,'john03');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id,name) values(9,'john04');
Query OK, 1 row affected (0.00 sec)

此刻假如到达10:00,john库被删除
~]# date -s  '2018/6/17 10:00'
数据库john库被删除
mysql> drop database john;
Query OK, 4 rows affected (0.02 sec)

假如不知道binlog数据文件是那一份，到备份目录下面
~]# cd /backup/
[root@node backup]# gzip -d john_2018-06-17.sql.gz 
[root@node backup]# egrep CHANGE john_2018-06-17.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=107;
[root@node backup]# cp /data/3306/mysql-bin.000023 ./  ##先做一个备份
[root@node backup]# ll
total 12
-rw-r--r-- 1 root root 5216 Jun 17 00:00 john_2018-06-17.sql  ##全备
-rw-r----- 1 root root 797 Jun 17 10:02 mysql-bin.000023      ##增备

接下来进行恢复
[root@node backup]# mysqlbinlog -d john mysql-bin.00002* >23bin.sql  ##binlog一定要按照顺序进行恢复
[root@node backup]# grep drop 23bin.sql 
drop database john
清除drop的语句
[root@node backup]# sed -i 's#drop database john##g' 23bin.sql 
[root@node backup]# grep drop 23bin.sql
[root@node backup]# mysql -u root -p12345678 -S /data/3306/mysql.sock <john_2018-06-17.sql
[root@node backup]# mysql -u root -p12345678 -S /data/3306/mysql.sock john <23bin.sql
mysql> select * from test;
+------+------+----------+
| id   | age  | name     |
+------+------+----------+
| 1    | NULL | john01   |
| 2    | NULL | 卓恩     |
| 3    | NULL | kaka     |
| 5    | 23   | zhangsan |
| 6    | 23   | wanger   |
| 7    | NULL | john02   |
| 8    | NULL | john03   |
| 9    | NULL | john04   |
+------+------+----------+
8 rows in set (0.00 sec)

7.问题小结

1. 数据库里如果有多个库
-d,--database=name list entries for just this database
从binlog文件里，过滤出指定库的binlog语句：mysqlbinlog -d +binlog文件
2. 如果某个表被删，恢复单表，使用grep命令过滤表，或导入测试库重新导出
3. 如果重要的库出了问题，最后进行锁库进行操作，或使用iptables命令
4. 多个binlog文件要按顺序进行恢复

 

 

AD：《熊孩子博客》熊孩子QQ:443060965