MySQL数据库备份还原(基于binlog的增量备份)
MySQL数据库备份还原(基于binlog的增量备份)
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 |
+------+-------------+
至此数据全部还原成功!
用户评论
更多相关好文
-
微信公众号文章/菜单添加小程序时路径如何获取? 2021-12-22
-
如何轻松获取微信小程序路径path? 2021-12-22
-
cannot import name 'CUDA_HOME' from 'mmcv.utils' 2021-12-05
-
vgg的loss一轮达到ln(1/n)阈值,如何解决 2021-11-21
-
如何下载使用utils库 2021-10-27
热门文章
-
微信公众号文章/菜单添加小程序时路径如何获取? 2021-12-22
-
如何轻松获取微信小程序路径path? 2021-12-22
-
python/MySQL分页查询方法与性能优化 2021-06-23
-
mitmproxy & python 忽略所有的https/ssl请求 2021-04-19
-
如何使用邮件/邮箱推广微信公众号/小程序? 2021-01-28
栏目最新文章
公告提示
- pytorch中文文档
- pytorch官方文档
提交评论