MySQL二进制日志

常用二进制日志操作命令
1、查看所有二进制日志列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       354 |
| mysql-bin.000002 |      1942 |
| mysql-bin.000003 |      2623 |
+------------------+-----------+

2、查看master状态，即最后(最新)一个二进制日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     2623 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3、刷新log日志，自此刻开始产生一个新编号的二进制日志文件

mysql> flush logs;

注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
4、删除二进制日志

4.1、重置(清空)所有binlog日志
mysql> reset master;
4.2、根据编号删除二进制日志
#删除mysql-bin.000002之前的二进制日志
mysql> PURGE BINARY LOGS TO 'mysql-bin.000002'
4.3、根据创建时间来删除
#删除2017-02-24 16:00:00之前创建的二进制日志
mysql> PURGE BINARY LOGS BEFORE '2017-02-24 16:00:00'

purge前先确认文件已传递到从库；purge会删除文件，并更新index文件；purge命令不会写binlog，不会把purge命令传递到从库
查看二进制日志(log-bin)
1、使用mysqlbinlog命令

shell> mysqlbinlog E:\MySQL\4306\logbin\mysql-bin.000001

如果报错，加上"--no-defaults"选项

shell> mysqlbinlog --no-defaults E:\MySQL\4306\logbin\mysql-bin.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170223 17:31:45 server id 6  end_log_pos 120 CRC32 0x7bda5b75     Start: binlog v 4, server v 5.6.33-log created 170223 17:31:45 at startup
ROLLBACK/*!*/;
BINLOG '
AayuWA8GAAAAdAAAAHgAAAAAAAQANS42LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAABrK5YEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAXVb
2ns=
'/*!*/;
# at 120
#170223 17:38:00 server id 6  end_log_pos 331 CRC32 0x5ec5d087     Query    thread_id=1    exec_time=0    error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1487842680/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost' IDENTIFIED BY PASSWORD '*A424E797037BF97C19A2E88CF7891C5C2038C039'
/*!*/;
# at 331
#170223 17:55:12 server id 6  end_log_pos 354 CRC32 0x4815cf67     Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2、登录数据库查看(推荐)

#查询第一个(最早)的binlog日志
mysql> show binlog events;
#指定查询 mysql-bin.000001 这个文件，从pos点4开始查起，偏移1行，查询1条
mysql> show binlog events in 'mysql-bin.000001' from 4 limit 1,1;

其他

#Version: 5.6.33-log
#使用二进制还原数据
shell> mysqlbinlog --no-defaults  E:\MySQL\4306\logbin\mysql-bin.000002|mysql -uroot -p -P3306
shell> mysqlbinlog --no-defaults  E:\MySQL\4306\logbin\mysql-bin.000003|mysql -uroot -p -P3306
shell> mysqlbinlog --no-defaults  E:\MySQL\4306\logbin\mysql-bin.000004|mysql -uroot -p -P3306
上面这种方式会使用不同的连接到服务器(如果前一个文件使用CREATE TEMPORARY TABLE，第二个文件引用此临时表就会报错)，因此建议所有的二进制日志文件使用同一个连接
类似于
shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -uroot -p
或者
shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"

#暂停二进制日志写入
mysql> SET sql_log_bin=0

#配置文件设定日志存储路径
#The Binary Log
log-bin=E:\MySQL\4306\logbin\mysql-bin
#The Error Log(.err)
log-error=E:\MySQL\4306\logbin\mysql
#The General Query Log(.log)
general_log=1
general_log_file=E:\MySQL\4306\logbin\mysql_general.log
#The Slow Query Log(.log)
slow_query_log=1
slow_query_log_file=E:\MySQL\4306\logbin\mysql_slow.log