Mysql之binlog日志恢复操作记录

最新推荐文章于 2024-01-31 18:40:14 发布

涵宇菲子

最新推荐文章于 2024-01-31 18:40:14 发布

阅读量1.4k

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/xianglingchuan/article/details/77154200

版权

Mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

开启binlog日志

[root@xlc ~]# vim /etc/my.cnf
在[mysqld] 区块添加
log-bin=mysql-bin //如果已经存在去掉#号

查询是否支持binlog

如下OFF代表不支持，ON代表支持

    mysql> show variables like 'log_bin';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)

查看当前日志文件名称

    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000005 |      120 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

    #关闭当前的二进制日志文件并创建一个新文件，新的二进制日志文件的名字在当前的二进制文件的编号上加1。
    mysql> flush logs;
    Query OK, 0 rows affected (0.02 sec)

恢复测试

创建测试表数据

mysql>  select * from t1;
+----+-------+-----+---------+
| id | name  | sex | address |
+----+-------+-----+---------+
|  7 | daiiy | m   | aaa     |
|  8 | tom   | f   | bbb     |
|  9 | liany | m   | ccc     |
| 10 | lilu  | m   | ddd     |
+----+-------+-----+---------+
4 rows in set (0.00 sec)

误删除测试表数据

mysql> delete from t1;
Query OK, 4 rows affected (0.00 sec)

mysql>  select * from t1;
Empty set (0.00 sec)

重新生成log文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |     1609 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

查看指定时间段内的日志数据

[root@iZwz97cbcapiyem3o09u09Z bin]# ./mysqlbinlog --no-defaults --database=test --start-datetime='2017-08-13 23:10:00' --stop-datetime='2017-08-13 23:12:13' ../data/mysql-bin.000005 | more
/*!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
#170813 23:07:06 server id 1  end_log_pos 120 CRC32 0x02ab0045  Start: binlog v 4, server v 5.6.33-log created 170813 23:07:06
BINLOG '
GmuQWQ8BAAAAdAAAAHgAAAAAAAQANS42LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUUA
qwI=
'/*!*/;
# at 120
#170813 23:10:57 server id 1  end_log_pos 199 CRC32 0xb6a73a78  Query   thread_id=486   exec_time=0     error_code=0
SET TIMESTAMP=1502637057/*!*/;
SET @@session.pseudo_thread_id=486/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 199
#170813 23:10:57 server id 1  end_log_pos 287 CRC32 0x0ac79c04  Query   thread_id=486   exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1502637057/*!*/;
delete from t1
/*!*/;
# at 287
#170813 23:10:57 server id 1  end_log_pos 318 CRC32 0xb0b4da63  Xid = 102852
COMMIT/*!*/;
# at 318
#170813 23:11:20 server id 1  end_log_pos 397 CRC32 0xfeca9fb6  Query   thread_id=486   exec_time=0     error_code=0
SET TIMESTAMP=1502637080/*!*/;
BEGIN
/*!*/;
# at 397
# at 429
#170813 23:11:20 server id 1  end_log_pos 429 CRC32 0x6f803988  Intvar
SET INSERT_ID=7/*!*/;
#170813 23:11:20 server id 1  end_log_pos 561 CRC32 0x2208be50  Query   thread_id=486   exec_time=0     error_code=0
SET TIMESTAMP=1502637080/*!*/;
insert into t1 (name,sex,address)values('daiiy','m','aaa')
/*!*/;
# at 561
#170813 23:11:20 server id 1  end_log_pos 592 CRC32 0x74e80a04  Xid = 102853
COMMIT/*!*/;
# at 592
#170813 23:11:20 server id 1  end_log_pos 671 CRC32 0x75baa899  Query   thread_id=486   exec_time=0     error_code=0
SET TIMESTAMP=1502637080/*!*/;
BEGIN
/*!*/;
# at 671
# at 703
#170813 23:11:20 server id 1  end_log_pos 703 CRC32 0x26252021  Intvar
SET INSERT_ID=8/*!*/;
#170813 23:11:20 server id 1  end_log_pos 833 CRC32 0x82810296  Query   thread_id=486   exec_time=0     error_code=0
SET TIMESTAMP=1502637080/*!*/;
insert into t1 (name,sex,address)values('tom','f','bbb')
/*!*/;
# at 833
#170813 23:11:20 server id 1  end_log_pos 864 CRC32 0x8c2790d6  Xid = 102854
COMMIT/*!*/;
# at 864
#170813 23:11:20 server id 1  end_log_pos 943 CRC32 0x1da588b6  Query   thread_id=486   exec_time=0     error_code=0
SET TIMESTAMP=1502637080/*!*/;
BEGIN
/*!*/;
# at 943
# at 975
#170813 23:11:20 server id 1  end_log_pos 975 CRC32 0x71dc1b65  Intvar
SET INSERT_ID=9/*!*/;
#170813 23:11:20 server id 1  end_log_pos 1107 CRC32 0x870f4947         Query   thread_id=486   exec_time=0     error_code=0
SET TIMESTAMP=1502637080/*!*/;
insert into t1 (name,sex,address)values('liany','m','ccc')
/*!*/;
# at 1107
#170813 23:11:20 server id 1  end_log_pos 1138 CRC32 0xba226ed1         Xid = 102855
COMMIT/*!*/;
# at 1138
#170813 23:11:22 server id 1  end_log_pos 1217 CRC32 0x0dec63f4         Query   thread_id=486   exec_time=0     error_code=0
SET TIMESTAMP=1502637082/*!*/;
BEGIN
/*!*/;
# at 1217
# at 1249
#170813 23:11:22 server id 1  end_log_pos 1249 CRC32 0xfc436a97         Intvar
SET INSERT_ID=10/*!*/;
#170813 23:11:22 server id 1  end_log_pos 1380 CRC32 0x6a004a4e         Query   thread_id=486   exec_time=0     error_code=0
SET TIMESTAMP=1502637082/*!*/;
insert into t1 (name,sex,address)values('lilu','m','ddd')
/*!*/;
# at 1380
#170813 23:11:22 server id 1  end_log_pos 1411 CRC32 0x979f2eab         Xid = 102856
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

还原指定时间段数据

[root@iZwz97cbcapiyem3o09u09Z bin]# ./mysqlbinlog --no-defaults --database=test --start-datetime='2017-08-13 23:10:00' --stop-datetime='2017-08-13 23:12:13' ../data/mysql-bin.000005 | /usr/local/mysql/bin/mysql -uroot -p123456 test
Warning: Using a password on the command line interface can be insecure.

查看恢复后数据

mysql> select * from t1;
+----+-------+-----+---------+
| id | name  | sex | address |
+----+-------+-----+---------+
|  7 | daiiy | m   | aaa     |
|  8 | tom   | f   | bbb     |
|  9 | liany | m   | ccc     |
| 10 | lilu  | m   | ddd     |
+----+-------+-----+---------+
4 rows in set (0.00 sec)

mysqlbinlog常见的选项有：
–start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
–stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间取值和上述一样
–start-position：从二进制日志中读取指定position 事件位置作为开始。
–stop-position：从二进制日志中读取指定position 事件位置作为事件截至

测试以后发现，如果想恢复delete了的数据，必须要找到这条数居insert的语句才成，例如某张表中不太确认是什么时候插入的数据，但在今天被误删除了，好像没有办法在被删除的指定时间区间来恢复数据,日志回滚只是把在某一时间段执行的语句重新执行了一次。

涵宇菲子

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql之binlog日志恢复操作记录

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。开启binlog日志[root@xlc ~]# vim /etc/my.cnf 在[mysqld] 区块添加 log-bin=mysql-bin //如果已经存在去掉#
复制链接

扫一扫