0.开启二进制日志记录功能:
#vim /etc/my.cnf
[mysqld]
log_bin=mysql-bin
binlog_format=MIXED
或在服务启动时指定
#mysqld_safe --log_bin=mysql-bin --binlog_format='MIXED' --user=root &
1.查看所有二进制日志文件:
mysql>SHOW BINARY LOGS;
2.显示当前正在使用的二进制日志文件:
mysql>SHOW MASTER STATUS;
3.查看指定二进制日至文件的具体内容:
mysql>SHOW BINLOG EVENTS in ‘mysql-bin.000005’ FROM POSITION=177;
或
#mysqlbinlog /mydata/mdata/mysql-bin.000005
4.手工滚动二进制文件:
mysql>FLUSH LOGS;
5.删除指定二进制日志文件之前的日志文件
mysql>PURGE BINARY LOGS TO ‘mysql-bin.00005’;
6.导出二进制日志文件为sql脚本:
#mysqlbinlog /mydata/mdata/mysql-bin.000005 > a.sql
[root@CNSZ22PL0088:/var/lib/mysql]#mysqlbinlog mysql-bin.000120 >/home/appdeploy/dev/a.sql mysqlbinlog: [ERROR] unknown variable 'default-character-set=UTF8' [root@CNSZ22PL0088:/var/lib/mysql]#mysqlbinlog --no-defaults mysql-bin.000120 >/home/appdeploy/dev/a.sql [root@CNSZ22PL0088:/var/lib/mysql]#
查看a.sql的信息如下:
BINLOG这部分还是未解码,看不懂的。用
–base64-output=DECODE-ROWS -v
解码:
[root@CNS088:/var/lib/mysql]#mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000123 >/home/appdeploy/dev/f.sql
这次再看日志,如下: