mysql 从库通过binlog日志恢复数据开源书籍

一、在master上查看当前正在写入的binlog文件

mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000004
        Position: 1073742027
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

二、在master上查看所有的binlog

mysql> show master logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 | 1073742567 |
| mysql-bin.000002 | 1073742130 |
| mysql-bin.000003 | 1073741940 |
| mysql-bin.000004 | 1073742027 |
+------------------+------------+

三、在master上通过show binlog events查看的binlog日志

语法：

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

选项解析：

IN 'log_name'：指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

LIMIT [offset,]：偏移量(不指定就是0)

row_count：查询总条数(不指定就是所有行)

操作记录：

对mysql默认的库操作,支持对update ,delete,alter,create ,revoke,grant,drop操作的sql语句记录的。

A.查看第一个(最早)的binlog日志

mysql> show binlog events\G;
*************************** 7092. row ***************************
    Log_name: mysql-bin.000001
         Pos: 1266944
  Event_type: Write_rows_v1
   Server_id: 21
 End_log_pos: 1267254
        Info: table_id: 81 flags: STMT_END_F
*************************** 7093. row ***************************
    Log_name: mysql-bin.000001
         Pos: 1267254
  Event_type: Xid
   Server_id: 21
 End_log_pos: 1267281
        Info: COMMIT /* xid=3922 */
*************************** 7094. row ***************************
    Log_name: mysql-bin.000001
         Pos: 1267281
  Event_type: Gtid
   Server_id: 21
 End_log_pos: 1267319
        Info: BEGIN GTID 0-21-1659
*************************** 7095. row ***************************
    Log_name: mysql-bin.000001
         Pos: 1267319
  Event_type: Table_map
   Server_id: 21
 End_log_pos: 1267397
        Info: table_id: 83 (test.user)
......

B.查看指定binlog文件的内容

mysql> show binlog events in 'mysql-bin.000004'\G;

C.指定查询 mysql-bin.000004 这个文件，从pos点:8224开始查起：

mysql> show binlog events in 'mysql-bin.000004' from 8224\G;

D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条

mysql> show binlog events in 'mysql-bin.000004' from 8224 limit 10\G;

E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条

mysql> show binlog events in 'mysql-bin.000004' from 8224 limit 2,10\G;

四、恢复方法（在slave上操作）

A.通过change命令，指定pos起始点重新同步数据，必须进入mysql数据库。

1. master_log_pos=1，相当于重新同步mysql-bin.000004里所有数据。（可能会出现错误，如：更改了表结构，或者使用了drop等）

mysql> change master to master_host='192.168.10.30',master_user='root',master_password='root',master_log_file='mysql-bin.000004',master_log_pos=1;
mysql> start slaves;
mysql> show slaves status\G;

2. master_log_pos=8224，相当于从pos点:8224同步mysql-bin.000004里的数据

mysql> change master to master_host='192.168.10.30',master_user='root',master_password='root',master_log_file='mysql-bin.000004',master_log_pos=8224;
mysql> start slaves;
mysql> show slaves status\G;

B. 通过mysqlbinlog命令恢复数据，不需要进入mysql数据库。

语法：

mysqlbinlog [参数] mysql-bin.000004 | mysql -u 用户名 -p 密码 -v 数据库名

常用选项：

--start-position=953：起始pos点

--stop-position=1437：结束pos点

--start-datetime="2019-01-01 00:00:00"：起始时间点

--stop-datetime="2019-01-31 23:59:59"：结束时间点

--database=test：指定只恢复test数据库(一台主机上往往有多个数据库，只限本地log日志)

不常用选项：

-u --user=name：连接到远程主机的用户名

-p --password[=name]：连接到远程主机的密码

-h --host=name：从远程主机上获取binlog日志

-v --verbose：输出mysql执行的语句（后面跟数据库就直接进入数据库）

--read-from-remote-server：从某个MySQL服务器上读取binlog日志

1. 完全恢复（可能会出现错误，如：更改了表结构，或者使用了drop等）

# /usr/bin/mysqlbinlog --database=test /data/mysql/mysql-bin.000004 | /usr/local/mysql/bin/mysql -uroot -proot -v test

2.指定pos结束点恢复(部分恢复)

@ --start-position=850

# /usr/bin/mysqlbinlog --stop-position=850 --database=test /data/mysql/mysql-bin.000004 | /usr/local/mysql/bin/mysql -uroot -proot -v test

3.指定pso点区间恢复(部分恢复)

@ --start-position=450

@ --stop-position=950

# /usr/bin/mysqlbinlog --start-position=450 --stop-position=950 --database=test /data/mysql/mysql-bin.000004 | /usr/local/mysql/bin/mysql -uroot -proot -v test

4.指定时间区间恢复(部分恢复)

@ --start-datetime="2013-11-29 13:18:54" 起始时间点

@ --stop-datetime="2013-11-29 13:21:53" 结束时间点

# /usr/bin/mysqlbinlog --start-datetime="2019-01-01 12:12:12" --stop-datetime="2019-01-01 13:00:00" --database=test /data/mysql/mysql-bin.000004 | /usr/local/mysql/bin/mysql -uroot -proot -v test

5.如果通过binlog恢复数据报错

可通过-f --force在表转储过程中，即使出现SQL错误也继续。

# /usr/bin/mysqlbinlog --force --database=test /data/mysql/mysql-bin.000004 | /usr/local/mysql/bin/mysql -uroot -proot -v test

您可以选择一种方式赞助本站

发表评论取消回复