文档章节

MySQL备份工具之mysqlbinlog

IT--小哥
 IT--小哥
发布于 2016/12/19 10:32
字数 1908
阅读 198
收藏 4

前言

增量备份 是指在一次全备份或上一次增量备份后,以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着,第一次增量备份的对象是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件,如此类推。这种备份方式最显著的优点就是:没有重复的备份数据,因此备份的数据量不大,备份所需的时间很短。但增量备份的数据恢复是比较麻烦的。您必须具有上一次全备份和所有增量备份磁带(一旦丢失或损坏其中的一个增量,就会造成恢复的失败),并且它们必须沿着从全备份到依次增量备份的时间顺序逐个反推恢复,因此这就极大地延长了恢复时间。

 

 

一、binlog简介

binlog日志由配置文件的 log-bin 选项负责启用,MySQL服务器将在数据根目录创建两个新文 件XXX-bin.001和xxx-bin.index,若配置选项没有给出文件名,Mysql将使用主机名称命名这两个文件,其中.index文件包含一份全体日志文件的清单。  

 

Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件,而不会记录 SELECT和没有实际更新的UPDATE语句。


当MySQL数据库停止或重启时,服务器会把日志文件记入下一个日志文件,Mysql会在重启时生成一个新的binlog日志文件,文件序号递增,此外,如果日志文件超过max_binlog_size系统变量配置的上限时,也会生成新的日志文件。

 

 

二、binlog日志操作

 

1、修改 MySQL 的配置文件my.cnf 如下:

[mysqld] 

log-bin = mysql-bin 

binlog_format = row

 

其中:log-bin若不显示指定存储目录,则默认存储在mysql的data目录下 

 

2、一些常用操作

mysql> show master logs;  #查看数据库所有日志文件。 

mysql> show binlog events in 'mysql-bin.000007';  #查看当前使用的binlog文件信息。 

mysql> show binlog events in 'mysql-bin.000016';  #查看指定的binlog文件信息。 

mysql> flush logs;  #将内存中log日志写磁盘,保存在当前binlog文件中,并产生一个新的binlog日志文件。 

mysql> flush logs; reset master;  #删除所有二进制日志,并重新(mysql-bin.000001)开始记录。

 

三、MySQL备份实例(全备 + 基于 binlog的增备)

示例采用小数据量进行模拟,包含一份全备及两份增备,主要演示下备份还原过程,工程中可根据数据实际情况进行备份还原策略调整。

1、查看当前数据库binlog文件

·         通过mysql客户端查看:

mysql> show master logs; 

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

| Log_name         | File_size | 

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

| mysql-bin.000001 |       107 | 

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

·       通过linux命令行直接查看(mysql数据目录data

[root@localhost data]# ll -h 

total 5.1G 

-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ibdata1 

-rw-rw---- 1 mysql mysql 2.0G Jul 18 14:12 ibdata2 

-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile0 

-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile1 

drwxr-xr-x 2 mysql mysql 4.0K Jul 18 13:52 mysql 

-rw-rw---- 1 mysql mysql  107 Jul 23 13:29 mysql-bin.000001 

-rw-rw---- 1 mysql mysql   19 Jul 23 13:29 mysql-bin.index 

srwxrwxrwx 1 mysql mysql    0 Jul 18 14:14 mysql.sock 

drwx------ 2 mysql mysql 4.0K Jul 18 14:01 performance_schema 

-rw-rw---- 1 mysql mysql  483 Jul 23 13:29 R820-08.err 

-rw-rw---- 1 mysql mysql    5 Jul 18 14:14 R820-08.pid 

drwx------ 2 mysql mysql   19 Jul 22 23:15 test 

 目前只有一个binlog文件mysql-bin.000001。

 

3.2 准备全量数据

mysql> create database backup_full; 

mysql> create table full (c1 int(10), c2 varchar(20)) engine=innodb; 

mysql> insert into full values (1, 'full1'),(2, 'full2'),(3, 'full3'),(4, 'full4'),  (5, 'full5'),(6, 'full6'),(7, 'full7'),(8, 'full8'),(9, 'full9'),(10, 'full10');

 

3.3 将全量数据进行备份

步骤如下:

a.备份前需要将数据库加读锁,防止数据在备份时写入。  

mysql> flush tables with read lock; 

b.通过命令flush logs;将log日志刷盘,写入当前binlog(mysql-bin.000001),在生成一个新的binlog(mysql-bin.000002)为增备做准备。 

mysql> flush logs;

c.进行数据备份。在linux命令行下执行:

[root]mysqldump  -u用户名 -p密码 -hIP地址 -P端口 数据库名 > /tmp/backup_full.sql 

[root]mysqldump  -uroot -pjesse -h127.0.0.1 -P3355 buckup_full > /tmp/backup_full.sql

d.解除表锁。 

mysql> unlock tables;

至此全量备份全部结束,将全量数据文件buckup_full.sql保存即可。数据库再有新的数据更新会记录在新的binlog(mysql-bin.000002)里面。

 

3.4 准备第一份增量数据

mysql> create database backup_increment; 

mysql> use backup_increment; 

mysql> create table increment (c1 int(10), c2 varchar(20)) engine=innodb; 

mysql> insert into increment values (11, 'increment1'),(12, 'increment2'),(13, 'increment3'),(14, 'increment4'),(15, 'increment5');

 

3.5 将第一份增量数据进行备份

步骤如下:

a.备份前需要将数据库加读锁,防止数据在备份时写入。

mysql> flush tables with read lock;

b.通过命令flush logs;将log日志刷盘,写入当前binlog(mysql-bin.000002),在生成一个新的binlog(mysql-bin.000003)为下次增备做准备。 

mysql> flush logs;

c.将binlog第一个增备文件mysql-bin.000002直接复制保存即可。        也可以将二进制文件导出到文本文件保存,在linux命令行下执行

[root ]mysqlbinlog  mysql-bin.000002 > /tmp/increment1.txt

d.解除表锁。 

mysql> unlock tables;

至此第一个增量备份全部结束,将增量binlog文件mysql-bin.000002或者有binlog文件导出的文本文件/tmp/increment1.txt保存即可。数据库再有新的数据更新会记录在新的binlog(mysql-bin.000003)里面。

 

3.6 准备第二份增量数据

mysql> use backup_increment; 

mysql> insert into increment values (16, 'increment16'),(17, 'increment17'),(18, 'increment18'),(19, 'increment19'),(20, 'increment20');

 

3.7 将第二份增量数据进行备份

步骤如下:

同上 

四、mysql还原实例分析(全备还原+基于binlog的增备还原)

模拟数据库故障,即删除全备数据及增备数据库。

mysql> drop table backup_full.full; 

mysql> drop database backup_increment;

此时数据库数据被清空。

mysql> show databases; 

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

| Database           | 

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

| information_schema | 

| backup_full        | 

| mysql              | 

| performance_schema | 

| test               | 

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

   5 rows in set (0.00 sec) 



mysql> select * from backup_full.full; 

ERROR 1146 (42S02): Table 'backup_full.full' doesn't exist 

mysql> select * from backup_increment.increment; 

ERROR 1146 (42S02): Table 'backup_increment.increment' doesn't exist

 

4.1 还原全备数据

·          方法:直接还原数据文件。

mysql -u用户名 -p密码  -hIP地址    -P端口 数据库名     < /tmp/buckup_full.sql 

mysql -uroot   -pjesse -h127.0.0.1 -P3355 backup_full  < /tmp/buckup_full.sql 

mysql> select * from backup_full.full; 

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

| c1   | c2     | 

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

|    1 | full1  | 

|    2 | full2  | 

|    3 | full3  | 

|    4 | full4  | 

|    5 | full5  | 

|    6 | full6  | 

|    7 | full7  | 

|    8 | full8  | 

|    9 | full9  | 

|   10 | full10 | 

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

 

4.2 还原第一个增备文件

·         方法:通过binlog直接还原

在linux命令行下执行:

[root]mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口 

[root]mysqlbinlog  mysql-bin.000002 | mysql -uroot -pjesse -h127.0.0.1 -P3355 

查看数据:

mysql> select * from backup_increment.increment; 

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

| c1   | c2         | 

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

|   11 | increment1 | 

|   12 | increment2 | 

|   13 | increment3 | 

|   14 | increment4 | 

|   15 | increment5 | 

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

 第一份增量数据还原成功!

4.3 还原第二个增备文件(方法同上)

在linux命令行下执行:

[root]mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口 

[root]mysqlbinlog  mysql-bin.000003 | mysql -uroot -pjesse -h127.0.0.1 -P3355 

查看数据:

mysql> select * from backup_increment.increment; 

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

| c1   | c2          | 

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

|   11 | increment1  | 

|   12 | increment2  | 

|   13 | increment3  | 

|   14 | increment4  | 

|   15 | increment5  | 

|   16 | increment16 | 

|   17 | increment17 | 

|   18 | increment18 | 

|   19 | increment19 | 

|   20 | increment20 | 

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

 至此数据全部还原成功!

 

4.4 按时间点和位置点恢复

 

按位置点信息恢复

[root@bogon ~]# mysqlbinlog --start-position=259 --stop-position=561 /data/mysql/mysql3306/logs/mysql-bin.000003 |mysql -uroot -p123456

 

按时间点恢复

[root@bogon ~]# mysqlbinlog --start-datetime="2016-11-18 6:10:34" --stop-datetime="2016-11-18 6:11:13" /data/mysql/mysql3306/logs/mysql-bin.000003 |mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

 

 

参考文档:

http://www.cnblogs.com/luoyx/p/5896419.html

http://blog.csdn.net/jesseyoung/article/details/41211841

为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧

© 著作权归作者所有

IT--小哥
粉丝 47
博文 152
码字总数 156670
作品 0
东城
数据库管理员
私信 提问
MySQL 备份与恢复

1.备份与恢复的类型 逻辑备份与物理备份 线上备份与线下备份 本地备份与远程备份:mysqldump,mysqlhotcopy(第三方PERL程序),select ... into outfile 快照备份(mysql自身不支持),可使用第三方...

VictorLi
2010/11/28
888
3
Mysqlbinlog的一些操作和用法

1,查看二进制日志 show master logs; 2,删除二进制日志 mysql> purge master logs to 'mysql-bin.000009'; Query OK, 0 rows affected (0.01 sec) 删除09以前的日志 mysql> purge master ......

技术佬
2018/09/27
0
0
mysql binlog出错问题

最近mysql的binlog经常出现以下问题: ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 131665, event_type: 73 ERROR: Could not read entry at offset 4......

pyruby
2013/01/28
837
2
unknown variable 'default-character-set=utf8'

下午在排查MySQL主从同步问题时,想从主库的binlog中找一些线索,裸的binlog文件是无法直视的,mysqlbinlog这个工具是用来查看binlog文件内容的(使用方式man mysqlbinlog查看),但是使用m...

rootliu
2018/07/05
33
0
mysqlbinlog命令必备知识

mysqlbinlog 命令以用户可视的方式展示出二进制日志中的内容。同时,也可以将其中的内容读取出来,供其他MySQL实用程序使用。 1. 获取当前二进制日志列表 2. 获取特定数据库条目 3. 禁止恢复...

大飞侠大虾
2018/08/24
0
0

没有更多内容

加载失败,请刷新页面

加载更多

聊聊nacos config的deleteConfig

序 本文主要研究一下nacos config的deleteConfig ConfigController nacos-1.1.3/config/src/main/java/com/alibaba/nacos/config/server/controller/ConfigController.java @Controller@Re......

go4it
27分钟前
3
0
面试:原来Redis的五种数据类型底层结构是这样的

关注我,可以获取最新知识、经典面试题以及微服务技术分享   在Redis中会涉及很多数据结构,比如SDS,双向链表、字典、压缩列表、整数集合等等。Redis会基于这些数据结构自定义一个对象系统...

ccww_
32分钟前
4
0
java发送html模板的高逼格邮件

最近做了一个监测k8s服务pod水平伸缩发送邮件的功能(当pod的cpu/内存达到指定阈值后会水平扩展出多个pod、或者指定时间内pod数应扩展到指定数量),一开始写了个格式很low的邮件,像下面这样...

码农实战
46分钟前
10
0
php-fpm配置文件详解/MariaDB密码重置、慢查询日志

来源:https://blog.csdn.net/Powerful_Fy php-fpm主配置文件路径:/usr/local/php-fpm/etc/php-fpm.conf #位于安装php安装目录下的etc/目录中,该文件中最后一行将配置文件指向:include=/...

asnfuy
51分钟前
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部