MySQL数据库备份还原（基于binlog的增量备份）

Song • 1780 次浏览 • 0个评论 • 2018年12月25日

1、简介

1.1、增量备份简介

增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。优点是不做重复的操作，减少服务器压力；缺点是数据恢复是比较麻烦的，您必须具有上一次全备份和所有增量备份磁带(一旦丢失或损坏其中的一个增量，就会造成恢复的失败)，并且它们必须沿着从全备份到依次增量备份的时间顺序逐个反推恢复，因此这就极大地延长了恢复时间。

1.2、binlog简介

binlog日志由配置文件的log-bin选项负责启用，Mysql会把用户对所有数据库的内容和结构的修改记入XXX-bin.n文件，而不会记录SELECT和没有实际更新的UPDATE语句。

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

2、binlog日志操作

2.1、开启binlog日志

在my.cnf或my.ini中的mysqld下增加：

[mysqld]
log-bin=mysql-bin
binlog_format=row

log-bin若不显示指定存储目录，则默认存储在mysql的data目录下
binlog_format的几种格式：(STATEMENT，ROW和MIXED)
- STATEMENT：基于SQL语句的复制(statement-based replication, SBR)
- ROW：基于行的复制(row-based replication, RBR)
- MIXED：混合模式复制(mixed-based replication, MBR)

启动后会产生mysql-bin.*这样的文件，每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到max_binlog_size，mysql自动创建新的二进制日志。如果你正使用大的事务，二进制日志还会超过max_binlog_size：事务全写入一个二进制日志中，绝对不要写入不同的二进制日志中。

my.ini中有两个设置：

# expire_logs_days = 10
# max_binlog_size = 100M

Expire_logs_days: 定义了mysql清除过期日志的时间,二进制日志自动删除的天数。默认值为0,表示没有自动删除。启动时和二进制日志循环时可能删除。
max_binlog_size: 如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。

此外mysqladmin flush-logs也可以刷新binary log。

查看binlog开启情况

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
mysql> show variables like 'binlog%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_cache_size                       | 32768 |
| binlog_direct_non_transactional_updates | OFF   |
| binlog_format                           | ROW |
| binlog_stmt_cache_size                  | 32768 |
+-----------------------------------------+-------+

2.2、查看binlog日志内容

[root@localhost data]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001;
mysqlbinlog: unknown variable 'default-character-set=utf8'

这里我们碰到了mysqlbinlog的一个bug，解决方法有两个：

2.2.1：使用--no-defaults选项

[root@localhost data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001

2.2.2：将my.cnf中[client]选项组中default-character-set=utf8选项临时屏蔽掉(该选项即时生效，不用重启数据库),使用完mysqlbinlog命令时在恢复。因为使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf(windows下是my.ini),而不是服务器已经加载进内存的配置文件。

输出格式如下：

# at 188
#140705 11:23:55 server id 1  end_log_pos 271   Query   thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1404573835/*!*/;
create database test
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

包含以下要素：

Position:位于文件中的位置，即第一行的(#at 4)和第二行的(log_pos 4)，说明该事件记录从文件第4字节开始。
Timestamp:事件发生的时间戳，即第二行的(#070813 14:16:36)
Exec_time:事件的执行花费时间
Error_code:错误码
Type:事件类型：
Master ID:创建二进制事件的主机服务器ID
Master Pos:事件在原始二进制文件中的位置
Flags:标志信息

2.3、一些常用操作

mysql> show master logs;  #查看数据库所有日志文件。
mysql> show master status;  #查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
mysql> show binlog events;  #查看当前使用的binlog文件信息。
mysql> show binlog events in 'mysql-bin.000016';  #查看指定的binlog文件信息。
mysql> flush logs;
#将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件。
mysql> flush logs; 
mysql> reset master;  #删除所有二进制日志，并重新(mysql-bin.000001)开始记录。

3、mysql备份实例分析(全备+基于binlog的增备)

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

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

通过mysql客户端查看

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+

查询日志文件地址：

find / -name "mysql-bin.000001"

# /var/lib/mysql/mysql-bin.000001

3.2、使用mysqldump将全量数据进行备份

a、准备全量数据

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');

b、备份前需要将数据库加读锁，防止数据在备份时写入。
```
mysql> flush tables with read lock;
```
c、通过命令flush logs;将log日志刷盘，写入当前binlog(mysql-bin.000001)，在生成一个新的binlog(mysql-bin.000002)为增备做准备。
```
mysql> flush logs;
```

d、进行数据备份。在linux命令行下执行：

mysqldump  -u用户名 -p密码 -hIP地址 -P端口 数据库名 > ~/backup_full.sql
mysqldump  -uroot -pjesse -h127.0.0.1 -P3306 backup_full > ~/backup_full.sql

e、解除表锁。
```
mysql> unlock tables;
```

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

3.3、增备备份测试一

a、准备第一份增量数据

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');

b、备份前需要将数据库加读锁，防止数据在备份时写入。
```
mysql> flush tables with read lock;
```
c、通过命令flush logs;将log日志刷盘，写入当前binlog(mysql-bin.000002)，在生成一个新的binlog(mysql-bin.000003)为下次增备做准备。
```
mysql> flush logs;
```
d、将binlog第一个增备文件mysql-bin.000002直接复制保存即可。也可以将二进制文件导出到文本文件保存，在linux命令行下执行
```
cd mysql-bin文件位置
mysqlbinlog --no-defaults mysql-bin.000002 > ~/increment1.txt
```
e、解除表锁。
```
mysql> unlock tables;
```
至此第一个增量备份全部结束，将增量binlog文件mysql-bin.000002或者有binlog文件导出的文本文件~/increment1.txt保存即可。数据库再有新的数据更新会记录在新的binlog(mysql-bin.000003)里面。

3.5、增备备份测试二

a、准备第二份增量数据

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

b、备份前需要将数据库加读锁，防止数据在备份时写入。
```
mysql> flush tables with read lock;
```
c、通过命令flush logs;将log日志刷盘，写入当前binlog(mysql-bin.000003)，在生成一个新的binlog(mysql-bin.000004)为下次增备做准备。
```
mysql> flush logs;
```
d、将binlog第一个增备文件mysql-bin.000003直接复制保存即可。也可以将二进制文件导出到文本文件保存，在linux命令行下执行
```
mysqlbinlog --no-defaults mysql-bin.000003 > ~/increment2.txt
```
e、解除表锁。
```
mysql> unlock tables;
```
至此第二个增量备份全部结束，将增量binlog文件mysql-bin.000003或者有binlog文件导出的文本文件~/increment2.txt保存即可。数据库再有新的数据更新会记录在新的binlog(mysql-bin.000004)里面。

4、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、还原全备数据

4.1.1、进入数据库，通过source

mysql> use backup_full;
mysql> source ~/backup_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.1.2、直接还原数据文件。

mysql -u用户名 -p密码  -hIP地址    -P端口 数据库名     < ~/buckup_full.sql
mysql -uroot   -pjesse -h127.0.0.1 -P3306 backup_full  < ~/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、还原第一个增备文件

4.2.1、通过文本文件还原

mysql> source ~/increment1.txt;
mysql> select * from backup_increment.increment;
+------+------------+
| c1   | c2         |
+------+------------+
|   11 | increment1 |
|   12 | increment2 |
|   13 | increment3 |
|   14 | increment4 |
|   15 | increment5 |
+------+------------+

4.2.2、通过binlog直接还原

在linux命令行下执行：

mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口
mysqlbinlog  mysql-bin.000002 | mysql -uroot -pjesse -h127.0.0.1 -P3306

查看数据：

mysql> select * from backup_increment.increment;
+------+------------+
| c1   | c2         |
+------+------------+
|   11 | increment1 |
|   12 | increment2 |
|   13 | increment3 |
|   14 | increment4 |
|   15 | increment5 |
+------+------------+

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

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

在linux命令行下执行：

mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口
mysqlbinlog  mysql-bin.000003 | mysql -uroot -pjesse -h127.0.0.1 -P3306

查看数据：

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 |
+------+-------------+

至此数据全部还原成功！

提交评论

请登录后评论

用户评论

当前暂无评价,快来发表您的观点吧...

栏目最新文章

公告提示

Pytorch是Facebook的AI研究团队发布了一个Python工具包，是Python优先的深度学习框架。作为numpy的替代品；使用强大的GPU能力，提供最大的灵活性和速度,实现了机器学习框架Torch在Python语言环境的执行,基于python且具备强大GPU加速的张量和动态神经网络。

其他产品

pytorch中文文档
pytorch官方文档

前往官网

pytorch中文网

MySQL数据库备份还原（基于binlog的增量备份）

1、简介

1.1、增量备份简介

1.2、binlog简介

2、binlog日志操作

2.1、开启binlog日志

2.2、查看binlog日志内容

2.3、一些常用操作

3、mysql备份实例分析(全备+基于binlog的增备)

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

3.2、使用mysqldump将全量数据进行备份

3.3、增备备份测试一

3.5、增备备份测试二

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

4.1、还原全备数据

4.1.1、进入数据库，通过source

4.1.2、直接还原数据文件。

4.2、还原第一个增备文件

4.2.1、通过文本文件还原

4.2.2、通过binlog直接还原

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

提交评论

用户评论

更多相关好文

热门文章

栏目最新文章

公告提示

其他产品

前往官网

合作伙伴

标签云

友情链接