mysql数据库通过binlog还原数据_mysql数据库通过binlog日志进行数据恢复

短命女 2022-10-27 13:58 21阅读 0赞

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;

\+------------------+----------+--------------+------------------+-------------------+

\+------------------+----------+--------------+------------------+-------------------+

| 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

mysql数据库通过binlog还原数据_mysql数据库通过binlog日志进行数据恢复

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Mysql-BinLog数据恢复

相关 MySQL——通过binlog恢复数据

相关 mysql数据库通过binlog还原数据_mysql数据库通过binlog日志进行数据恢复

相关 Mysql通过binlog日志文件恢复数据

相关 mysql binlog恢复数据

相关 mysql恢复数据库方法binlog日志来恢复数据库

相关通过Mysql数据文件进行数据库还原

相关基于binlog恢复mysql数据库

相关烂泥：通过binlog恢复mysql数据库

相关使用binlog还原mysql数据库