mysql全量与增量备份

binlog日志： 从上一次全量备份到下一次全量备份直接产生的数据。

一、全备和增量备份介绍

1、全量备份：

备份所有数据库或只备份一个数据库，全量备份之后，全量备份之前的binlog日志就没用了，一般生产环境会保留3-7天的binlog日志

2、增量备份：

从上一次全量备份之后，再次产生的新的数据。对于mysql来说，binlog日志就是mysql的增量备份数据
增量备份条件：
(1) 开启 log-bin=mysql-bin
(2) 先执行mysql全备，全量备份的时候刷新binlog日志，保证生成新的binlog日志，这个新的binlog日志就是增量备份。 -F   [--flush-logs]

二、增量备份恢复必备条件

1、开启mysql log-bin 日志功能

[root@Oldboy ~]# grep log-bin /etc/my.cnf 
log-bin=mysql-bin

2、存在一份全量备份，加上全备之后的binlog增量备份

三、生产环境下模拟数据库增量备份与恢复
顺序：
开启binlog——全量备份——刷新binlog日志（增量备份）——恢复增量备份

1、数据库开启bin-log功能并重启mysql

[root@Oldboy ~]# cat /etc/my.cnf |grep log-bin
log-bin=mysql-bin
[root@Oldboy ~]# vi /etc/init.d/mysqld restart

2、创建数据库和表

[root@Oldboy ~]# mysql -uroot -p123456mysql> create database oldboy;mysql> use oldboy;mysql> CREATE TABLE `student` (   `id` int(4) NOT NULL AUTO_INCREMENT,   `name` char(20) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;mysql> insert into student(id,name) values(1,'oldboy');mysql> select * from student;
+----+--------+
| id | name      |
+----+--------+
|  1 | oldboy    |
+----+--------+

3、mysqldump全量备份并刷新binlog日志

#某男孩(如果不做主从的话 --master-data可以不用加)
[root@Oldboy ~]# mysqldump  -uroot  -p'123456' --default-character-set=utf8  -A -B  -F  -x  --events --master-data=2   |gzip >/server/backup/$(date +%F)_all.sql.gz#网上查的
[root@Oldboy ~]# mysqldump -uroot -p'rocenmysql!niubi' --quick --events --all-databases  -B --flush-logs --delete-master-logs --single-transaction |gzip >/Data/mysql/$(date +%F)_all.sql.gz-A ： --all-databases 备份所有数据库
-B ：  还原的时候不用创建数据库了
-F  ：  --flush-logs

4、在数据库oldboy中增加两条数据

[root@Oldboy ~]# mysql -uroot -p123456mysql> use oldboy;mysql> insert into student(id,name) values(100,'oldboy100');
Query OK, 1 row affected (0.01 sec)mysql> insert into student(id,name) values(101,'oldboy101');
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | oldboy    |
| 100 | oldboy100 |
| 101 | oldboy101 |
+-----+-----------+

5、由于某领导脑残，执行了drop database oldboy

[root@Oldboy ~]# mysql -uroot -p123456
mysql> drop database oldboy;
Query OK, 1 row affected (0.06 sec)

6、恢复之前删除的数据（最好让数据库停止写入）

(0) 拒绝任何人访问mysql
[root@Oldboy backup]# iptables -A INPUT -p tcp  --dport 3306 -j DROP (1) 查看全量备份之后产生的新的binlog日志
[root@Oldboy ~]# cd /server/backup/
[root@Oldboy backup]#  gzip -d 2016-05-24_all.sql.gz
通过vim，找到这一行 "CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=107;"
mysql-bin.000005：是全量备份之后产生的增量备份日志(2) 查看当前binlog
[root@Oldboy data]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql     150 5月  24 03:38 mysql-bin.000001
-rw-rw---- 1 mysql mysql     663 5月  24 04:11 mysql-bin.000002
-rw-rw---- 1 mysql mysql     150 5月  24 04:13 mysql-bin.000003
-rw-rw---- 1 mysql mysql     599 5月  24 05:11 mysql-bin.000004
-rw-rw---- 1 mysql mysql     578 5月  24 05:14 mysql-bin.000005
-rw-rw---- 1 mysql mysql     114 5月  24 05:14 mysql-bin.index(3) 刷新当前binlog
[root@Oldboy ~]# mysql -uroot -p123456 -e "flush logs;"(4) 再次查看，确定恢复的目标范围是mysql-bin.000005
[root@Oldboy data]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql     150 5月  24 03:38 mysql-bin.000001
-rw-rw---- 1 mysql mysql     663 5月  24 04:11 mysql-bin.000002
-rw-rw---- 1 mysql mysql     150 5月  24 04:13 mysql-bin.000003
-rw-rw---- 1 mysql mysql     599 5月  24 05:11 mysql-bin.000004
-rw-rw---- 1 mysql mysql     578 5月  24 05:14 mysql-bin.000005
-rw-rw---- 1 mysql mysql     324 5月  24 05:24 mysql-bin.000006
-rw-rw---- 1 mysql mysql     133 5月  24 05:24 mysql-bin.index(5) 将mysql-bin.000005   复制到 /server/backup
[root@Oldboy data]# cp -a mysql-bin.000005    /server/backup/(6) 利用 mysqlbinlog  将 mysql-bin.000005   导出为  .sql文件
[root@Oldboy backup]# mysqlbinlog mysql-bin.000005 > mysql-bin_000005.sql(7) 进入 binlog中删除  drop database  oldboy
[root@Oldboy backup]# cat mysql-bin_000005.sql 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
drop database oldboy
/*!*/;
# at 192
#160524  5:15:19 server id 1  end_log_pos 281   Query   thread_id=11exec_time=0      error_code=0
SET TIMESTAMP=1464038119/*!*/;
create database oldgirl              删除这行，并保存
/*!*/;
# at 281
#160524  5:24:30 server id 1  end_log_pos 324   Rotate to mysql-bin.000007  pos: 4
DELIMITER ;(8) 恢复最近一次的全量备份
[root@Oldboy ~]# cd /server/backup/
[root@Oldboy backup]# mysql -uroot -p123456 < 2016-05-24_all.sql(9) 恢复增量备份（将全备之后的增量备份到出问题之间的binlog全部恢复，有几个恢复几个）
[root@Oldboy backup]# mysql -uroot -p123456 < mysql-bin_000005.sql (10) 也可以按时间恢复，vim进入binlog.sql中，只保留某个时间段的数据即可
主从情况下，只恢复主就可以了，从会自动同步主的binlog的