美文网首页
mysql备份与恢复策略

mysql备份与恢复策略

作者: 词穷又词贫 | 来源:发表于2017-05-17 11:35 被阅读742次

事故：

早几天开发要求查看一下4月24号的数据库，其中有一张表的数据，在4.25号被删除了，现在想了解下那张表的数据。

由于数据库完整备份是7天以内，现在都是5月15号，其他机器上恢复4月24号的数据已经不可能，另外我又想到，完完全全利用二进制日志的备份来做恢复。

在检查二进制日志时，通过mysqlbinlog发现，最原始的binlog已经删除，没有办法从0开始恢复。

类似这样的情况，还真没有好的办法策略，我们的备份策略真的是太烂了，虽然业务量不大，但真是太烂了。

为了应付以后备份数据的可恢复性与灵活性，决定更新备份策略。

新的备份策略思路

检查所有自建数据库数据库备份与二进制日志备份，确认是否依靠数据库备份+二进制日志备份来进行时间点，或者pos点位还原。
(RDS依照RDS的备份策略进行备份，另外可以添加备份到ECS系统)

1、清理mysql二进制日志文件：reset master;
2、设置mysql自动清理7天以前的二进制日志 : expire_logs_days;
3、全库完整备份，数据量比较小，采用mysqldump,
4、定时任务每天凌晨1点开始备份，接着传输到本地
4、二进制日志备份，定时任务每天凌晨2点，将除了当前正在使用的二进制日志备份压缩，

恢复策略

利用最原始的数据库备份先恢复数据库，接着按照备份的二进制日志，按照次序依次恢复至自己想要的数据节点时间的数据。
数据库完整备份
二进制日志选择恢复，可以依据当前要恢复的二进制日志文件pos位置，或者时间。


备份时间图

实验

现有一数据库信息如下：

MariaDB [testdb]> show binary logs ;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       312 |
| mysql-bin.000002 |       464 |
| mysql-bin.000003 |       517 |
| mysql-bin.000004 |      1143 |
| mysql-bin.000005 |    530910 |
+------------------+-----------+
5 rows in set (0.00 sec)
# binlog从mysql-bin.000001开始，并不代表这个二进制日志文件是最原始的，可能清理过binlog

# 备份策略
# 执行reset master;
MariaDB [testdb]> reset master;
Query OK, 0 rows affected (0.01 sec)
MariaDB [testdb]> show binary logs ;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       312 |
+------------------+-----------+
1 row in set (0.00 sec)
# 二进制日志被删除，索引文件内容被初始化。
# 此时数据库正在正常工作，二进制日志文件一直在刷新内容。

MariaDB [testdb]> show binary logs ;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1268 |
+------------------+-----------+
1 row in set (0.00 sec)

记下来备份数据库，数据库简单备份脚本：

#!/bin/bash
#
BACKDIR=/data/backup/mysql
MYSQLDUMP=/usr/local/mysql/bin/mysqldump
DATEFORMAT=$(date +%Y%m%d%H%M)
LOG=/data/backup/mysql/backup.log
TODAY=`date '+%Y-%m-%d'`

[ -d $BACKDIR/"$TODAY" ] ||  mkdir -p $BACKDIR/"$TODAY"
[ -f $LOG ] && touch $LOG

find "$BACKDIR" -name "20*" -type d -mtime +7|xargs rm -rf
[ $? -eq 0 ]  && echo -e "${DATEFORMAT} Delete 7 days ago backup file success" >> ${LOG} || echo -e "${DATEFORMAT} Delete Old backup file failure" >> ${LOG}

$MYSQLDUMP -uroot -p123456 --routines --triggers --single-transaction --flush-logs --all-database | gzip - | openssl des3 -salt -k 123456 -out ${BACKDIR}/"$TODAY"/mysql-${DATEFORMAT}.sql.gz.des3
[ $? -eq 0 ] && echo -e "${DATEFORMAT} mysqldump database backup success" >> ${LOG} || echo -e "${DATEFORMAT} mysqldump database backup failure" >> ${LOG}

备份数据库文件：

mysql-201705161815.sql.gz.des3

在此备份脚本中，备份所有数据库，备份的同时刷新二进制日志文件。
第一次备份之后的二进制日志情况：

MariaDB [testdb]> show binary logs ;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1420 |
| mysql-bin.000002 |       365 |
+------------------+-----------+
2 rows in set (0.00 sec)

接着执行二进制日志备份：

[root@node2 backup]# ll binlog_backup/
总用量 12
-rw-r--r-- 1 root root  37 5月  16 18:27 backup.file
-rw-r--r-- 1 root root 131 5月  16 18:27 binlog_backup.log
-rw-r----- 1 root root 486 5月  16 18:27 mysql-bin.000001.gz

接着在次执行多次SQL语句：

MariaDB [testdb]> insert into tb values (12,'a1');
MariaDB [testdb]> insert into tb values (13,'a1');
MariaDB [testdb]> insert into tb values (14,'a3');
MariaDB [testdb]> insert into tb values (15,'a4');

在次备份数据库和二进制日志，备份数据库文件与二进制日志文件：

# 数据库完整备份
mysql-201705161815.sql.gz.des3
mysql-201705161904.sql.gz.des3
# 二进制日志备份
mysql-bin.000001.gz
mysql-bin.000002.gz

此时数据库正在运行，测试错误操作删除数据库：

MariaDB [testdb]> drop database testdb;

现在需要恢复数据，先看看binlog的文件内容：

[root@node2 binlogs]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 
/*!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 /*!*/;
# at 4
#170516 17:51:40 server id 2  end_log_pos 248   Start: binlog v 4, server v 10.0.10-MariaDB-log created 170516 17:51:40 at startup
ROLLBACK/*!*/;
BINLOG '
rMsaWQ8CAAAA9AAAAPgAAAAAAAQAMTAuMC4xMC1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACsyxpZEzgNAAgAEgAEBAQEEgAA3AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQACnhVnw==
'/*!*/;
# at 248
#170516 17:51:40 server id 2  end_log_pos 273   Gtid list []
# at 273
#170516 17:51:40 server id 2  end_log_pos 312   Binlog checkpoint mysql-bin.000001
# at 312
#170516 17:57:02 server id 2  end_log_pos 350   GTID 0-2-1
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=2*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
BEGIN
/*!*/;
# at 350
#170516 17:57:02 server id 2  end_log_pos 444   Query   thread_id=13    exec_time=0 error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1494928622/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
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/*!*/;
insert into tb values (6,'f')
/*!*/;
# at 444
#170516 17:57:02 server id 2  end_log_pos 471   Xid = 1594
COMMIT/*!*/;
# at 471
#170516 17:57:10 server id 2  end_log_pos 509   GTID 0-2-2
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
BEGIN
/*!*/;
# at 509
#170516 17:57:10 server id 2  end_log_pos 603   Query   thread_id=13    exec_time=0 error_code=0
SET TIMESTAMP=1494928630/*!*/;
insert into tb values (7,'g')
/*!*/;
# at 603
#170516 17:57:10 server id 2  end_log_pos 630   Xid = 1595
COMMIT/*!*/;
# at 630
#170516 17:57:14 server id 2  end_log_pos 668   GTID 0-2-3
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
BEGIN
/*!*/;
# at 668
#170516 17:57:14 server id 2  end_log_pos 762   Query   thread_id=13    exec_time=0 error_code=0
SET TIMESTAMP=1494928634/*!*/;
insert into tb values (8,'h')
/*!*/;

截取此次事物查看：

# at 509
#170516 17:57:10 server id 2  end_log_pos 603   Query   thread_id=13    exec_time=0 error_code=0
SET TIMESTAMP=1494928630/*!*/;
insert into tb values (7,'g')
/*!*/;

at:表示一次事物开始pos点，
170516 17:57:10 :表示事物开始时间
server id
end_log_pos 603 :表示此次事物终止于此pos点,下次事物就从此点开始记录
Query ：事物类型
thread_id :执行事物的线程ID
exec_time=0 :执行事物花费的时间
error_code=0 :执行事物是否有错误
SET TIMESTAMP=1494928630 ：事物开始执行的时间戳
insert into tb values (7,'g') :事物要执行的DDL语句

提取备份数据库：

openssl des3 -d -salt -in mysql-201705161815.sql.gz.des3 -out 1815.sql.gz
gunzip 1815.sql.gz
# 获得1815.sql数据库文件
# 恢复数据库
set global sql_log_bin=off;  //关闭写入二进制日志
mysql -uroot -p < 1815.sql   //恢复第一次备份数据库
use testdb;
select * from tb;    //检查数据

MariaDB [testdb]> select * from tb;
+------+------+
| id   | name |
+------+------+
......
|   11 | k    |
+------+------+
# 我们可以看到数据库数据是到了id 为11

提取备份的二进制日志文件：

/*!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 /*!*/;
# at 4
#170516 18:15:30 server id 2  end_log_pos 248   Start: binlog v 4, server v 10.0.10-MariaDB-log created 170516 18:15:30
BINLOG '
QtEaWQ8CAAAA9AAAAPgAAAAAAAQAMTAuMC4xMC1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA3AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQAbOlnrw==
'/*!*/;
# at 248
#170516 18:15:30 server id 2  end_log_pos 287   Gtid list [0-2-7]
# at 287
#170516 18:15:30 server id 2  end_log_pos 326   Binlog checkpoint mysql-bin.000001
# at 326
#170516 18:15:30 server id 2  end_log_pos 365   Binlog checkpoint mysql-bin.000002
# at 365
#170516 19:02:42 server id 2  end_log_pos 403   GTID 0-2-8
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=2*//*!*/;
/*!100001 SET @@session.gtid_seq_no=8*//*!*/;
BEGIN
/*!*/;
# at 403
#170516 19:02:42 server id 2  end_log_pos 499   Query   thread_id=13    exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1494932562/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1,
 @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
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/*!*/;
insert into tb values (12,'a1')
/*!*/;
# at 499
#170516 19:02:42 server id 2  end_log_pos 526   Xid = 1994
COMMIT/*!*/;
# at 526
#170516 19:02:45 server id 2  end_log_pos 564   GTID 0-2-9
/*!100001 SET @@session.gtid_seq_no=9*//*!*/;
BEGIN
/*!*/;
# at 564
#170516 19:02:45 server id 2  end_log_pos 660   Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1494932565/*!*/;
insert into tb values (13,'a1')
/*!*/;
# at 660
#170516 19:02:45 server id 2  end_log_pos 687   Xid = 1995
COMMIT/*!*/;
# at 687
#170516 19:02:50 server id 2  end_log_pos 725   GTID 0-2-10
/*!100001 SET @@session.gtid_seq_no=10*//*!*/;
BEGIN
/*!*/;
# at 725
#170516 19:02:50 server id 2  end_log_pos 821   Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1494932570/*!*/;
insert into tb values (14,'a3')
/*!*/;
# at 821
#170516 19:02:50 server id 2  end_log_pos 848   Xid = 1996
COMMIT/*!*/;
# at 848
#170516 19:02:54 server id 2  end_log_pos 886   GTID 0-2-11
/*!100001 SET @@session.gtid_seq_no=11*//*!*/;
BEGIN
/*!*/;
# at 886
#170516 19:02:54 server id 2  end_log_pos 982   Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1494932574/*!*/;
insert into tb values (15,'a4')
/*!*/;
# at 982
#170516 19:02:54 server id 2  end_log_pos 1009  Xid = 1997
COMMIT/*!*/;
# at 1009
#170516 19:04:53 server id 2  end_log_pos 1047  GTID 0-2-12
/*!100001 SET @@session.gtid_seq_no=12*//*!*/;
# at 1047
#170516 19:04:53 server id 2  end_log_pos 1118  Query   thread_id=15    exec_time=0     error_code=0
SET TIMESTAMP=1494932693/*!*/;
FLUSH TABLES
/*!*/;
# at 1118
#170516 19:04:53 server id 2  end_log_pos 1161  Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

根据此二进制日志文件恢复方式：
1、按照时间恢复：

mysqlbinlog mysql-bin.000002 --stop-date="2017-05-16 19:02:45" | mysql -uroot -p
# 根据上面的二进制日志，可以查看到170516 19:02:45 此时间之前是有做一笔插入id为12数据的动作，是恢复在此时间之前。
# 检查数据库
MariaDB [testdb]> select * from tb;
+------+------+
| id   | name |
+------+------+
......
|   11 | k    |
|   12 | a1   |
+------+------+
#检查到数据库已经回复至id为12位置。

2、按照位置点恢复

mysqlbinlog mysql-bin.000002 --start-position="564" --stop-position="821" | mysql -uroot -p
# 根据设置pos位置点，比较如上二进制日志文件可以得出，是恢复insert into tb values (13,'a1')此SQL语句
# 检查数据库
MariaDB [testdb]> select * from tb;
+------+------+
| id   | name |
+------+------+
......
|   11 | k    |
|   12 | a1   |
|   13 | a1   |
+------+------+

3、二进制日志导出文件，并修改恢复，
测试前先删除数据库 > drop database testdb;

# 先恢复数据库初始
 mysql -u root -p < 1815.sql
# 根据二进制日志文件，截取自己需要的信息并导出成文件，接着恢复
mysqlbinlog mysql-bin.000002 --stop-position="1009" > binlog.sql
# 截取至1009pos位置的信息，进行恢复。（当然也可以利用vim编辑器进行修改）
mysql -u root -p < binlog.sql  
# 调整过的二进制日志内容附加到之前恢复的数据中
检查数据库
MariaDB [testdb]> select * from tb;
+------+------+
| id   | name |
+------+------+
......
|   11 | k    |
|   12 | a1   |
|   13 | a1   |
|   14 | a3   |
|   15 | a4   |
+------+------+
# 结果如我们预期一模一样。

有类似这样的备份策略，就可以恢复至某时间点的数据，后续要用到某时间点数据时，也可以方便恢复调用。

1、每天备份数据库，二进制日志文件，这样当天的内容没有备份，假如数据量比较大，这样一天丢失的数据也比较达，可以适当调整二进制日志文件备份策略，利用定时任务刷新二进制日志，接着每两小时备份一次。
2、数据量大了还是需要另外的备份工具，mysqldump，时间太长。

相关文章

  • mysql备份与恢复策略

    事故： 早几天开发要求查看一下4月24号的数据库，其中有一张表的数据，在4.25号被删除了，现在想了解下那张表的数...

  • MySQL-lesson07-备份恢复

    MySQL-lesson07-备份恢复 1. 运维在数据库备份恢复方面的职责 1.1 设计备份策略 1.2 日常备...

  • MySQL 数据迁移

    MySQL数据迁移方式 备份 恢复 修改密码 修改加密策略 常用配置

  • mysql数据库备份与恢复

    数据库备份 使用mysql自带备份命令行就可实现数据库备份与恢复 备份全部库 mysql> mysqldump -...

  • mysql mysqldump备份恢复策略

    备份： 1、单库备份：/data/mysql/bin/mysqldump -uxxx -pxxx --single...

  • 七，MySQL备份恢复

    1，备份策略设计 备份周期: 备份工具: 备份方式: 逻辑: 物理备份: 备份的策略 数据恢复 数据迁移 2，备份...

  • Oracle顶级Java技术专家心血之作Effective My

    前言 mysql资深专家ronald bradford撰写的精品书籍《effective mysql之备份与恢复》...

  • mysql备份与恢复

    mysql数据库备份与恢复 一、为什么要备份 二、备份类型 三、备份种类 四、逻辑备份 逻辑备份特点 用法 日常用...

  • RDS MySQL 物理备份文件恢复到自建数据库

    RDS MySQL 物理备份文件恢复到自建数据库 RDS 5.7的物理备份恢复到本地的方法 mysql 备份工具 ...

  • 备份恢复

    备份恢复 在备份恢复需要做哪些工作 设计备份策略 备份周期 天,周,月 备份方式 全备,增量.... 备份什么? ...

网友评论

      本文标题：mysql备份与恢复策略

      本文链接：https://www.haomeiwen.com/subject/xahlxxtx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|mysql备份与恢复策略|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！