mysql增量备份二进制日志_mysqldump 全量备份 + mysqlbinlog 二进制日志增量备份

第一节：mysqldump 全量备份 + mysqlbinlog 二进制日志增量备份

1.1 mysqldump 数据库增量备份前提

(1)要开启 mysql log-bin 日志功能，若没有开启则，修改配置文件 /etc/my.cnf，添加如下内容：

[mysqld]

datadir=/var/lib/mysql/data

log-bin=mysql-bin

server-id=1

重启 mysql

(2)查看数据库是否开启了二进制日志打印功能：

mysql> show variables like 'log_%';

+---------------------------------+---------------------+

| Variable_name | Value |

+---------------------------------+---------------------+

| log_bin | ON |

......

log_bin 为 ON 则表示该功能已经开启

(3)存在一个完全备份，生产环境一般凌晨某个时刻进行全备

#例如:

mysqldump -uroot -p --default-character-set=gbk --single-transaction -F -B school |gzip > /backup/school_$(date +%F).sql.gz

InnoDB 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性

1.2 mysqldump 全量备份 + mysqlbinlog 二进制日志增量备份过程

模拟 00:00 进行全量备份，误删数据库，恢复：

(1)准备数据库和表并向表中插入数据

mysql> create database school default character set utf8; #创建school数据库

Query OK, 1 row affected (0.00sec)

mysql> use school #切换至school数据库

Database changed

mysql>create table student(-> id int(10) not null comment '学号',-> name varchar(16) not null comment '姓名',-> sex varchar(16) not null comment '性别',-> age tinyint(2) not null default '0' comment '学生年龄',-> dept varchar(16) default 'null' comment '学生所在系别',-> primary key (id))-> ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8-> COLLATE=utf8_general_ci; #创建student表

Query OK, 0 rows affected (0.03sec)

mysql> insert into student values #向表中插入数据

-> (1,'张三','男',24,'计算机'),(2,'李四','女',27,'英语'),-> (3,'王五','男',22,'电子商务'),(4,'赵六','女',20,'物流管理');

Query OK,4 rows affected, 4 warnings (0.02sec)

Records:4 Duplicates: 0 Warnings: 4mysql> select * from student; #查看student表中的所有字段的值

+----+--------+-----+-----+--------------+

| id | name | sex | age | dept |

+----+--------+-----+-----+--------------+

| 1 | 张三 | 男 | 24 | 计算机 |

| 2 | 李四 | 女 | 27 | 英语 |

| 3 | 王五 | 男 | 22 | 电子商务 |

| 4 | 赵六 | 女 | 20 | 物流管理 |

+----+--------+-----+-----+--------------+

4 rows in set (0.00 sec)

(2)模拟凌晨 00:00 全备

[root@centos6 ~]# mkdir /backup #创建备份文件夹

[root@centos6 ~]# date -s "20200225"[root@centos6~]# mysqldump -uroot -p111111 --default-character-set=utf8 --single-transaction -F -B school -e | gzip > /backup/mysql_backup_`date +%F`.sql.gz

(3)全备之后再向表中写入数据

[root@centos6 ~]# mysql -uroot -p111111

......

mysql>use school

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql>insert into student values-> (5,'tom','男',29,'化学'),(6,'jack','女',19,'法语'),-> (7,'mimi','女',21,'化妆'),(8,'kaka','女',20,'酒店管理');

Query OK,4 rows affected (0.02sec)

Records:4 Duplicates: 0 Warnings: 0mysql> select *from student;+----+--------+-----+-----+--------------+

| id | name | sex | age | dept |

+----+--------+-----+-----+--------------+

| 1 | 张三 | 男 | 24 | 计算机 |

| 2 | 李四 | 女 | 27 | 英语 |

| 3 | 王五 | 男 | 22 | 电子商务 |

| 4 | 赵六 | 女 | 20 | 物流管理 |

| 5 | tom | 男 | 29 | 化学 |

| 6 | jack | 女 | 19 | 法语 |

| 7 | mimi | 女 | 21 | 化妆 |

| 8 | kaka | 女 | 20 | 酒店管理 |

+----+--------+-----+-----+--------------+

8 rows in set (0.00 sec)

(4)模拟用户破坏数据

mysql>drop database school;

Query OK,1 row affected (0.01 sec)

(5)检查全备后的所有 binlog

[root@centos6 ~]# ll -nat /var/lib/mysql/data/总用量21276

-rw-rw----. 1 27 27 5242880 2月 25 00:00ib_logfile0-rw-rw----. 1 27 27 10485760 2月 25 00:00ibdata1-rw-rw----. 1 27 27 416 2月 25 00:00 mysql-bin.000004drwxr-xr-x. 4 27 27 4096 2月 25 00:00.-rw-rw----. 1 27 27 76 2月 25 00:00 mysql-bin.index-rw-rw----. 1 27 27 1581 2月 25 00:00 mysql-bin.000003drwx------. 2 27 27 4096 1月 12 18:34school

drwxr-xr-x. 3 27 27 4096 1月 12 18:31..-rw-rw----. 1 27 27 5242880 1月 12 18:31ib_logfile1-rw-rw----. 1 27 27 765307 1月 12 18:31 mysql-bin.000002

-rw-rw----. 1 27 27 19734 1月 12 18:31 mysql-bin.000001drwx------. 2 27 27 4096 1月 12 18:31 mysql

(6)立即刷新；再次查看 binlog：根据时间点及前一个 binlog 可以知道发现问题时刻前 binlog 日志

[root@centos6 ~]# mysqladmin -uroot -p111111 flush-logs

[root@centos6~]# ll -nat /var/lib/mysql/data/总用量21276drwxr-xr-x. 3 27 27 4096 2月 25 00:05.-rw-rw----. 1 27 27 95 2月 25 00:05 mysql-bin.index-rw-rw----. 1 27 27 106 2月 25 00:05 mysql-bin.000005 #锁定该binlog

-rw-rw----. 1 27 27 544 2月 25 00:05 mysql-bin.000004

-rw-rw----. 1 27 27 5242880 2月 25 00:03ib_logfile0-rw-rw----. 1 27 27 10485760 2月 25 00:03ibdata1-rw-rw----. 1 27 27 1581 2月 25 00:00 mysql-bin.000003drwxr-xr-x. 3 27 27 4096 1月 12 18:31..-rw-rw----. 1 27 27 5242880 1月 12 18:31ib_logfile1-rw-rw----. 1 27 27 765307 1月 12 18:31 mysql-bin.000002

-rw-rw----. 1 27 27 19734 1月 12 18:31 mysql-bin.000001drwx------. 2 27 27 4096 1月 12 18:31 mysql

(7)备份出 binlog 至 /backup 目录

[root@centos6 ~]# cp /var/lib/mysql/data/mysql-bin.* /backup/

(8)恢复全量备份

[root@centos6 data]# cd /backup/[root@centos6 backup]#zcat mysql_backup_2020-02-25.sql.gz >mysql_backup_2020-02-25.sql

[root@centos6 backup]# mysql-uroot -p111111 -e "create database school;"[root@centos6 backup]# mysql-uroot -p111111 school < /backup/mysql_backup_2020-02-25.sql

[root@centos6 backup]# mysql-uroot -p111111

......

mysql>use school

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> select *from student;+----+--------+-----+-----+--------------+

| id | name | sex | age | dept |

+----+--------+-----+-----+--------------+

| 1 | 张三 | 男 | 24 | 计算机 |

| 2 | 李四 | 女 | 27 | 英语 |

| 3 | 王五 | 男 | 22 | 电子商务 |

| 4 | 赵六 | 女 | 20 | 物流管理 |

+----+--------+-----+-----+--------------+

4 rows in set (0.00 sec) #增量恢复成功

mysql> quit

(9)恢复增量备份

[root@centos6 backup]# cd /var/lib/mysql/data/[root@centos6 data]# mysqlbinlog-d school mysql-bin.000004 > school-bin.sql

[root@centos6 data]# mysqlbinlog-d school mysql-bin.000005 >> school-bin.sql

[root@centos6 data]# vim school-bin.sql #打开school-bin.sql删除drop sql语句

[root@centos6 data]# mysql -uroot -p111111 school < school-bin.sql

ERROR1007 (HY000) at line 65: Can't create database'school'; database exists #因为school已经恢复至全量备份，所以此处提示school数据库已经存在，不过不要紧，对存在的数据覆盖

[root@centos6 data]# mysql -uroot -p111111

......

mysql>use school

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> select *from student;+----+--------+-----+-----+--------------+

| id | name | sex | age | dept |

+----+--------+-----+-----+--------------+

| 1 | 张三 | 男 | 24 | 计算机 |

| 2 | 李四 | 女 | 27 | 英语 |

| 3 | 王五 | 男 | 22 | 电子商务 |

| 4 | 赵六 | 女 | 20 | 物流管理 |

| 5 | tom | 男 | 29 | 化学 |

| 6 | jack | 女 | 19 | 法语 |

| 7 | mimi | 女 | 21 | 化妆 |

| 8 | kaka | 女 | 20 | 酒店管理 |

+----+--------+-----+-----+--------------+

8 rows in set (0.00 sec) #增量备份恢复

mysql> quit

1.3 mysqlbinlog 增量恢复方式

(1)基于时间点恢复：

#(1)指定开始时间到结束时间

myslbinlog mysqlbin.000005 --start-datetime='2020-02-25 01:10:46' --stop datetime='2020-02-25 03:10:46' -r time.sql#(2)指定开始时间到文件结束

myslbinlog mysqlbin.000005 --start-datetime='2020-02-25 01:10:46' -d esen -r time.sql#(3)从文件开头到指定结束时间

myslbinlog mysqlbin.000005 --stop-datetime='2020-02-25 03:10:46' -d esen -r time.sql

(2)基于位置点的增量恢复：

#(1)指定开始位置到结束位置

myslbinlog mysqlbin.000005 --start-position=510 --stop-position=1312 -r pos.sql#(2)指定开始位置到文件结束

myslbinlog mysqlbin.000005 --start-position=510 -r pos.sql#(3)从文件开始位置到指定结束位置

myslbinlog mysqlbin.000005 --stop-position=1312 -r pos.sql

或者，在增量备份的时候，可以将二进制文件转化成 .sql 语句，然后在删除 “drop” 语句，修改 .sql，再导入即可(上述恢复采用该方法)

1.4 实现自动化备份(数据库小)

(1)备份计划

什么时间：2:00

对那些数据备份

备份的文件放的位置

(2)备份脚本

[root@centos6 ~]# vim /mysql_back.sql

#!/bin/bash

back_dir=/backup

back_file=`date +%F`_all.sql

user=root

pass=111111

if [ ! -d /backup ];then

mkdir -p /backupfi# 备份并截断日志

mysqldump-u${user} -p${pass} --lock-all-tables --routines --events --triggers -

-master-data=2 --flush-logs --all-databases > /$back_dir/$back_file

# 只保留最近一周的备份

cd $back_dirfind . -mtime +7 -exec rm -rf {} \;

(3)测试

[root@centos6 ~]# chmod +x /mysql_back.sql

[root@centos6~]# /mysql_back.sql

[root@centos6~]# ls /backup2020-02-25_all.sql

1.5 复制数据文件

复制数据文件方式，可以使用 cp 或 tar

(1)停止服务

[root@centos6 ~]# service mysqld stop

(2)备份数据文件

[root@centos6 ~]# mkdir /backup

[root@centos6~]# tar cvf /backup/all.`date +%F`.tar.gz /var/lib/mysql/*

(3)将备份文件拷贝到目标服务器

scp /backup/all.`date +%F`.tar.gz 192.168.129.142:/tmp/

(4)启动服务，如有需要则恢复数据库数据