服务器技术

当前位置: 主页 > 新闻资讯 > 服务器技术 >

mysqlbinlog的增量恢复实战

  • 来源:未知
  • 编辑:IDC香港
  • 时间:2019-08-30 09:09
  • 阅读:

企业生产环境下,突发事件,某个脑残的leader,执行了drop database blog,如何恢复数据?

mysql增量恢复必备条件:

1.1开启MySQL log-bin日志功能

[root@teddy ~]# grep mysql-bin /etc/my.cnf

log-bin=mysql-bin

1.2有mysql的数据库全备份

+++++++++++++++++++++++++++++++++++++++++++++++

A.基于时间点的增量备份
1)指定开始时间和结束时间
mysqlbinlog mysql-bin.000008 –start-datetime=’2014-03-19 02:58:54’ –stop-datetime=’2014-03-19 03:58:54’ -r -d teddylu time.sql
将2014-03-19 02:58:54到2014-03-19 03:58:54之间的关于数据库teddylu的binlog,输出到time.sql

提示:当你的MySQL中有多个数据库的时候,而你只要恢复某个数据库的时候,需要指定 -d database_name,否则是无法恢复的,因为其他的数据库是正常的,这时你又要插入数据到那些数据库中,如果设定了主键,是会出错的!!!

[root@bacula_server backup]# mysqlbinlog mysql-bin.000002 –stop-position=860|mysql -uroot -p123456
ERROR 1062 (23000) at line 30: Duplicate entry ‘5709’ for key ‘PRIMARY’

 

2)指定开始时间到文件结束
mysqlbinlog mysql-bin.000008 –start-datetime=’2014-03-19 02:58:54’ -d oldboy -r time.sql
即从2014-03-19 02:58:54时刻到日志结尾,oldboy数据库的bin-log输出到time.sql

3)从文件开头到指定结束时间
mysqlbinlog mysql-bin.000008 –stop-datetime=’2014-03-19 03:58:54’ -d oldboy -s
从日志开始到2014-03-19 03:58:54截止,oldboy数据库的bin-log输出
B.基于位置点的增量备份
1)指定开始位置和结束问题

2)指定开始位置到文件结束

3)从文件开始位置到指定结束位置
mysqlbinlog mysqlbin.000021 –stop-position=954 -r start-954.sql
从文件开始位置,到954位置截止所有的bin-log输出到start-954.sql

++++++++++++++实战演练+++++++++
实例1,基于时间点的增量备份,现将数据库还原到下面的样子:
[root@teddy backup]# mysql -uroot -p’123456′ <mysql_backup_2015-01-14.sql
[root@teddy backup]# mysql -uroot -p’123456′ -e “set names utf8;select*from teddylu.students;”
+—-+——–+—–+—–+——————–+
| id | name | sex | age | dpte |
+—-+——–+—–+—–+——————–+
| 1 | 小北 | 男 | 24 | 计算机网络 |
| 2 | 小南 | 男 | 44 | 计算机编程 |
| 3 | 小西 | 男 | 28 | 人力资源管理 |
| 4 | 小东 | 女 | 25 | 财务 |
| 5 | 小中 | 男 | 24 | 英语 |
+—-+——–+—–+—–+——————–+
#查看bin-log文件
mysqlbinlog mysql-bin.000003
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150114 0:00:45 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 150114 0:00:45
BINLOG ‘
LUG1VA8BAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
‘/*!*/;
# at 106
#150114 0:02:04 server id 1 end_log_pos 177 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1421164924/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@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=28/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 177
#150114 0:02:04 server id 1 end_log_pos 311 Query thread_id=3 exec_time=0 error_code=0
use `teddylu`/*!*/;
SET TIMESTAMP=1421164924/*!*/;
INSERT INTO students values(0006,’小李’,’女’,’25’,’汽车维修’)
/*!*/;
# at 311
#150114 0:02:04 server id 1 end_log_pos 338 Xid = 76
COMMIT/*!*/;
# at 338
#150114 0:02:04 server id 1 end_log_pos 409 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1421164924/*!*/;
BEGIN
/*!*/;
# at 409
#150114 0:02:04 server id 1 end_log_pos 543 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1421164924/*!*/;
INSERT INTO students values(0007,’小王’,’男’,’29’,’机械工程’)
/*!*/;
# at 543
#150114 0:02:04 server id 1 end_log_pos 570 Xid = 77
COMMIT/*!*/;
# at 570
#150114 0:02:37 server id 1 end_log_pos 657 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1421164957/*!*/;
drop database teddylu
/*!*/;
# at 657
#150114 0:03:32 server id 1 end_log_pos 700 Rotate to mysql-bin.000004 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
根据上面bin-log的日志分析,2个绿色部分的时间点是正常的数据,要将这个2条数据还原回去,红色部分是出问题的数据,不能在要了,所以可以采用基于时间点的增量备份即从文件开头到指定结束时间,
[root@teddy backup]# mysqlbinlog mysql-bin.000003 –stop-datetime=’2015-01-14 0:02:04’|mysql -uroot -p’123456′
[root@teddy backup]# mysql -uroot -p’123456′ -e “set names utf8;select*from teddylu.students;”
+—-+——–+—–+—–+——————–+
| id | name | sex | age | dpte |
+—-+——–+—–+—–+——————–+
| 1 | 小北 | 男 | 24 | 计算机网络 |
| 2 | 小南 | 男 | 44 | 计算机编程 |
| 3 | 小西 | 男 | 28 | 人力资源管理 |
| 4 | 小东 | 女 | 25 | 财务 |
| 5 | 小中 | 男 | 24 | 英语 |
+—-+——–+—–+—–+——————–+
[root@teddy backup]# mysqlbinlog mysql-bin.000003 –stop-datetime=’2015-01-14 0:02:15’|mysql -uroot -p’123456′
[root@teddy backup]# mysql -uroot -p’123456′ -e “set names utf8;select*from teddylu.students;”
+—-+——–+—–+—–+——————–+
| id | name | sex | age | dpte |
+—-+——–+—–+—–+——————–+
| 1 | 小北 | 男 | 24 | 计算机网络 |
| 2 | 小南 | 男 | 44 | 计算机编程 |
| 3 | 小西 | 男 | 28 | 人力资源管理 |
| 4 | 小东 | 女 | 25 | 财务 |
| 5 | 小中 | 男 | 24 | 英语 |
| 6 | 小李 | 女 | 25 | 汽车维修 |
| 7 | 小王 | 男 | 29 | 机械工程 |
+—-+——–+—–+—–+——————–+
提示:
1)根据经验,结束时间要比bin-log日志的指定时间要大一点,这样才能将数据最大限度完整找回来
2)结束时间不一定非要和bin-log日志上指定的时间一致,在上例中,只要在出问题的时间点和正常数据点之间,就可以,即在150114 0:02:04之后150114 0:02:37之前,就Ok!!!

实例2,基于位置点的增量备份,现将数据库还原到下面的样子:
[root@teddy backup]# mysql -uroot -p’123456′ -e “set names utf8;select*from teddylu.students;”
+—-+——–+—–+—–+——————–+
| id | name | sex | age | dpte |
+—-+——–+—–+—–+——————–+
| 1 | 小北 | 男 | 24 | 计算机网络 |
| 2 | 小南 | 男 | 44 | 计算机编程 |
| 3 | 小西 | 男 | 28 | 人力资源管理 |
| 4 | 小东 | 女 | 25 | 财务 |
| 5 | 小中 | 男 | 24 | 英语 |
+—-+——–+—–+—–+——————–+

#查看bin-log文件并分析
[root@teddy backup]# mysqlbinlog mysql-bin.000003
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150114 0:00:45 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 150114 0:00:45
BINLOG ‘
LUG1VA8BAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
‘/*!*/;
# at 106
#150114 0:02:04 server id 1 end_log_pos 177 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1421164924/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@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=28/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
at 177
#150114 0:02:04 server id 1 end_log_pos 311 Query thread_id=3 exec_time=0 error_code=0
use `teddylu`/*!*/;
SET TIMESTAMP=1421164924/*!*/;
INSERT INTO students values(0006,’小李’,’女’,’25’,’汽车维修’)
/*!*/;
# at 311
#150114 0:02:04 server id 1 end_log_pos 338 Xid = 76
COMMIT/*!*/;
# at 338
#150114 0:02:04 server id 1 end_log_pos 409 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1421164924/*!*/;
BEGIN
/*!*/;
at 409
#150114 0:02:04 server id 1 end_log_pos 543 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1421164924/*!*/;
INSERT INTO students values(0007,’小王’,’男’,’29’,’机械工程’)
/*!*/;
# at 543
#150114 0:02:04 server id 1 end_log_pos 570 Xid = 77
COMMIT/*!*/;
at 570
#150114 0:02:37 server id 1 end_log_pos 657 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1421164957/*!*/;
drop database teddylu
/*!*/;
# at 657
#150114 0:03:32 server id 1 end_log_pos 700 Rotate to mysql-bin.000004 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
根据上面bin-log的日志分析,2个绿色部分的时间点是正常的数据,要将这个2条数据还原回去,红色部分是出问题的数据,不能在要了,即#at 570之前,就可以,比如569
[root@teddy backup]# mysql -uroot -p’123456′ -e “set names utf8;select*from teddylu.students;”
+—-+——–+—–+—–+——————–+
| id | name | sex | age | dpte |
+—-+——–+—–+—–+——————–+
| 1 | 小北 | 男 | 24 | 计算机网络 |
| 2 | 小南 | 男 | 44 | 计算机编程 |
| 3 | 小西 | 男 | 28 | 人力资源管理 |
| 4 | 小东 | 女 | 25 | 财务 |
| 5 | 小中 | 男 | 24 | 英语 |
+—-+——–+—–+—–+——————–+
[root@teddy backup]# mysqlbinlog mysql-bin.000003 –stop-position=569|mysql -uroot -p’123456′
[root@teddy backup]# mysql -uroot -p’123456′ -e “set names utf8;select*from teddylu.students;”
+—-+——–+—–+—–+——————–+
| id | name | sex | age | dpte |
+—-+——–+—–+—–+——————–+
| 1 | 小北 | 男 | 24 | 计算机网络 |
| 2 | 小南 | 男 | 44 | 计算机编程 |
| 3 | 小西 | 男 | 28 | 人力资源管理 |
| 4 | 小东 | 女 | 25 | 财务 |
| 5 | 小中 | 男 | 24 | 英语 |
| 6 | 小李 | 女 | 25 | 汽车维修 |
| 7 | 小王 | 男 | 29 | 机械工程 |

这样,数据库teddylu的表students中的数据就全部找回来了!!!


服务热线
4000-070-040
在线咨询
常见问题