mysql利用二进制日志mysqlbinlog备份数据和恢复数据

什么是二进制日志

二进制日志就是记录着mysql数据库中的一些写入性操作，比如一些增删改，但是，不包括查询！

 

二进制日志有哪些功能

一般情况下，二进制日志有着数据复制和数据恢复的功能

 

查看二进制日志是否开启

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |

开启二进制日志 : vi /etc/my.cnf

[mysqld]
log-bin=/data/mydata/log_bin/mysql-bin
server-id=1

查看所有的binlog日志列表

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     29038 |

刷新二进制日志

flush logs;

重置(清空）二进制日志文件

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1341 |
+------------------+-----------+

使用mysqldump备份数据时，加上-F选项可以重新生成一个新的二进制日志文件

mysqldump -uroot -p vincent user -F  > user_bak.sql

 

查看二进制日志文件的内容报错

[root@vincent-public log_bin]# mysqlbinlog mysql-bin.000001
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

解决

第一种：在mysqlbinlog 后边加上 --no-defaults 
​第二种：注释掉配置文件里边的default-character-set=utf8

把二进制日志文件导出成普通文件

mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001 > mysqlbin.sql

找出要恢复的位置

（1）找出关键字的行数：mysqlbinlog --no-defaults mysql-bin.000001 | cat -n  | grep -iw 'drop'
​
[root@vincent-public log_bin]# mysqlbinlog --no-defaults mysql-bin.000001 | cat -n  | grep -iw 'drop'
  4180  DROP TABLE `user` /* generated by server */
​
​
（2）打印出相关内容：mysqlbinlog --no-defaults mysql-bin.000001 | cat -n | sed -n '4170,4180p'
​
[root@vincent-public log_bin]# mysqlbinlog --no-defaults mysql-bin.000001 | cat -n | sed -n '4170,4180p'
  4170  # at 59578
  4171  #190419  0:41:48 server id 1  end_log_pos 59609 CRC32 0x36cda2b7        Xid = 6380
  4172  COMMIT/*!*/;
  4173  # at 59609
  4174  #190419  0:41:48 server id 1  end_log_pos 59674 CRC32 0x8de2f06a        Anonymous_GTID  last_committed=145      sequence_number=146
  4175  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  4176  # at 59674
  4177  #190419  0:41:48 server id 1  end_log_pos 59787 CRC32 0x6b2edd2b        Query   thread_id=14    exec_time=0     error_code=0
  4178  use `vincent`/*!*/;
  4179  SET TIMESTAMP=1555605708/*!*/;
  4180  DROP TABLE `user` /* generated by server */

恢复数据

• 第一步：把备份的数据表user恢复到数据库中：

mysql -uroot -p vincent < /mysql_data_back/user_bak.sql

• 第二步：利用上面找到的删除的位置进行恢复数据

mysqlbinlog --no-defaults --set-charset=utf8  --stop-position="59674" /data/mydata/log_bin/mysql-bin.000001 | mysql -uroot -p