mysql数据库通过binlog日志进行数据恢复
mysql数据库通过binlog日志进行数据恢复
1.需要开启binlog日志
# vim my.cnf 配置如下选项，重启数据库，让数据库生成我们需要的binlog日志
server-id = 206
log-bin = mysql-bin
expire_logs_days = 10
binlog_format = row
2.创建测试数据
mysql> create database itpart;
mysql> use itpart;
mysql> create table users(id int auto_increment primary key, name varchar(200));
# 插入数据
mysql> insert into users(name) values('jack'),('tom'),('lily'),('lucy');
mysql> select * from users;
+----+------+
| id | name |
+----+------+
| 1 | jack |
| 2 | tom |
| 3 | lily |
| 4 | lucy |
+----+------+
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 835 | | | |
+------------------+----------+--------------+------------------+-------------------+
# 查看binlog日志的文件
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 835 |
3.删除表，然后通过binlog进行数据恢复
> drop table users;
# 查看binlog日志中记录的内容
# cd /var/lib/mysql
# gtid模式查看需要加参数 --base64-output=DECODE-ROWS -v
# mysqlbinlog --base64-output=DECODE-ROWS -v --start-position=0 --stop-position=835 mysql-bin.000001
[root@server01 mysql]# mysqlbinlog --base64-output=DECODE-ROWS -v --start-position=0 --stop-position=835 mysql-bin.000001
mysqlbinlog: [Warning] option 'start-position': unsigned value 0 adjusted to 4
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201103 16:50:14 server id 206 end_log_pos 123 CRC32 0xaa4b8247 Start: binlog v 4, server v 5.7.31-log created 201103 16:50:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#201103 16:50:14 server id 206 end_log_pos 154 CRC32 0xb3c39630 Previous-GTIDs
# [empty]
# at 154
#201103 16:51:07 server id 206 end_log_pos 219 CRC32 0x617069ad Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#201103 16:51:07 server id 206 end_log_pos 319 CRC32 0x1bef97c7 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1604393467/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database itpart
/*!*/;
# 从此处开始是我们插入表的操作
# at 319
#201103 16:51:41 server id 206 end_log_pos 384 CRC32 0x9236a8ba Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 384
#201103 16:51:41 server id 206 end_log_pos 534 CRC32 0xd068dcb2 Query thread_id=12 exec_time=0 error_code=0
use `itpart`/*!*/;
SET TIMESTAMP=1604393501/*!*/;
create table users(id int auto_increment primary key, name varchar(200))
/*!*/;
# at 534
#201103 16:52:54 server id 206 end_log_pos 599 CRC32 0x7ac60d6f Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 599
#201103 16:52:54 server id 206 end_log_pos 673 CRC32 0x529cfe02 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1604393574/*!*/;
BEGIN
/*!*/;
# at 673
#201103 16:52:54 server id 206 end_log_pos 726 CRC32 0xd98193ed Table_map: `itpart`.`users` mapped to number 109
# at 726
#201103 16:52:54 server id 206 end_log_pos 804 CRC32 0x8b2d79ca Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `itpart`.`users`
### SET
### @1=1
### @2='jack'
### INSERT INTO `itpart`.`users`
### SET
### @1=2
### @2='tom'
### INSERT INTO `itpart`.`users`
### SET
### @1=3
### @2='lily'
### INSERT INTO `itpart`.`users`
### SET
### @1=4
### @2='lucy'
# at 804
#201103 16:52:54 server id 206 end_log_pos 835 CRC32 0xc09cc5e4 Xid = 108
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
4.使用binlog日志对mysql的数据库进行恢复操作
mysqlbinlog --start-position=319 --stop-position=835 --database=itpart /var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -uroot -p密码 -v itpart
