mysql二进制文件操作语法(mysql binary log operate statements)

开启 binary logs 功能

在 mysql 配置文件中配置 log-bin，重启 mysql
my.cnf (on Linux/unix) or my.ini (on Windows) 例子:

[client]
...

[mysqld]
...
log-bin=mysql-bin  (log_bin=/var/mydb/bin-log，指定 log 的路径，以及名称前缀)
---

一旦重启，Mysql 会自动创建新的二进制文件。您也不妨看看下面的变量：

server-id        = 1
expire_logs_days = 4 (自动删除 log 的天数，缺省值为 0，即不自动删除)
sync_binlog      = 1

详细信息可以阅读 MySQL documentation，如果你使用主从库（使用二进制文件的主要理由），请查阅Replication configuration checklist

查看 binary logs

登陆 MySQL 之后执行如下语句：

SHOW BINARY LOGS
等价
SHOW MASTER LOGS

返回值：

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |        98 |
+------------------+-----------+
1 row in set (0.00 sec)

手动删除 binary log

PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

例子：

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

datetime_expr 参数格式为 YYYY-MM-DD hh:mm:ss。
上述语法，当从库正在同步时，也可以安全运行。你不必要关闭从库。如果你正在删除一个从库正在同步的 log，上述语句将不会做任何操作。MySQL 5.7.2 以及之后版本将会报错。然而，如果你删除了一个从库没有同步的 log，那么从库将无法与主库保持数据一致。
手动安全删除日志的步骤：

在每一个从库的 MySQL 上运行 SHOW SLAVE STATUS，检验从库没有从主库读取日志
使用命令 SHOW BINARY LOGS，查看主库上的 binary log 文件
找出在从库中时间最早的 log 文件，这是我们要删除的目标文件。如果所有从库都对同一个 log 与主库保持同步，那么那个日志就是我们要删除的目标文件
删除之前，备份 log。（这一步是可选的，但是建议你这么做）
删除目标文件之前的 log

可以通过设置 expire_logs_days 参数，自动删除 log。
在调用上述删除语句之前，log 已经被删除，比如 linux 中使用 rm 命令，那么该语句将会报错。

binary log 格式

binary log 会记录所有与数据修改相关的操作，查询不会被记录哦。
比如我有如下的一些操作：

1. drop table student_information;
2. drop table student;
3. CREATE TABLE `student` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` char(20) NOT NULL,
     `age` tinyint(2) NOT NULL DEFAULT '0',
     PRIMARY KEY (`id`),
     KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
4. insert student values(1,'zhangsan',20);
5. insert student values(1,'zhangsan',20);
6. insert student values(3,'wangwu',22);

通过命令将 binary log 转为 SQL 脚本：

mysqlbinlog mysql-bin.000001 > my.sql

binary log 存储内容为：

SET TIMESTAMP=1473842609/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
drop table student_information
/*!*/;
# at 193
#160914 16:43:35 server id 1  end_log_pos 276 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473842615/*!*/;
drop table student
/*!*/;
# at 276
#160914 16:43:57 server id 1  end_log_pos 578 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473842637/*!*/;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
/*!*/;
# at 578
#160914 16:44:11 server id 1  end_log_pos 648 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473842651/*!*/;
BEGIN
/*!*/;
# at 648
#160914 16:44:11 server id 1  end_log_pos 751 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473842651/*!*/;
insert student values(1,'zhangsan',20)
/*!*/;
# at 751
#160914 16:44:11 server id 1  end_log_pos 778 	Xid = 29
COMMIT/*!*/;
# at 778
#160914 16:44:19 server id 1  end_log_pos 848 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473842659/*!*/;
BEGIN
/*!*/;
# at 848
#160914 16:44:19 server id 1  end_log_pos 947 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473842659/*!*/;
insert student values(2,'lisi',21)
/*!*/;
# at 947
#160914 16:44:19 server id 1  end_log_pos 974 	Xid = 30
COMMIT/*!*/;
# at 974
#160914 16:44:25 server id 1  end_log_pos 1044 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473842665/*!*/;
BEGIN
/*!*/;
# at 1044
#160914 16:44:25 server id 1  end_log_pos 1145 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473842665/*!*/;
insert student values(3,'wangwu',22)
/*!*/;
# at 1145
#160914 16:44:25 server id 1  end_log_pos 1172 	Xid = 31
COMMIT/*!*/;
# at 1172
#160914 17:31:33 server id 1  end_log_pos 1242 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473845493/*!*/;
BEGIN
/*!*/;
# at 1242
#160914 17:31:33 server id 1  end_log_pos 1348 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1473845493/*!*/;
update student set age = 100 where id = 1
/*!*/;
# at 1348
#160914 17:31:33 server id 1  end_log_pos 1375 	Xid = 33
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

从语法上可以看出，如果是 update 操作恢复的概率相对要麻烦很多，需要对比操作。 SET TIMESTAMP 是操作的时间戳，这个相当有用，这样就允许我们通过时间来确定重做的范围。delete 以及 update 都被放置在事务里边了，但是只有当整个 binary log 执行完成才算成功，任何一条语法的异常都会导致事务回滚，重做失败。

使用 binary log 增量恢复数据

直接重做 binary log 中的操作：

mysqlbinlog mysql-bin.000001 | mysql -u root -p

执行过程中发生异常就被被终止，所以在重做之前需要自己处理该文件，使得重做的动作是自己想要的。比如从上述 binary log 中有 drop table student_information 操作，而此时的数据库中已经不存在该表，所以重做该步骤就会抛出异常信息。ERROR 1051 (42S02) at line 16: Unknown table 'student_information'

逐行查看 binary log 中内容：

mysqlbinlog mysql-bin.000001 | more

转换 binary log 为 SQL 脚本：

mysqlbinlog mysql-bin.000001 > my.sql

重做 SQL 脚本：

mysql -u root -p < my.sql

如果你有多个 binary log 文件需要被执行，安全的方式是：将所有的 binary log 一次性执行。不安全方法的示例：

mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

使用两个不同连接处理 binary log 可能导致问题，有可能会发生如下情况：第一个 binary log 包含语法 CREATE TEMPOARY TEBLE 而第二个 binary log 使用到该临时表。当第一个 binary log 执行完成将会删除临时表，那么第二个 binary log 需要使用到该临时表的语句将报错。

在一个连接中完成 binary logs 的处理，例子如下：

mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

另外一种方法，将 binary logs 合并为一个 SQL 脚本：

mysqlbinlog binlog.000001 >  /tmp/statements.sql
mysqlbinlog binlog.000002 >> /tmp/statements.sql
mysql -u root -p -e "source /tmp/statements.sql"

在读取包含GTID的二进制日志时写入转储文件（请参见第18.1.3节“使用全局事务标识符复制”），在mysqlbinlog中使用--skip-gtids选项，如下所示：

mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
mysql -u root -p -e "source /tmp/dump.sql"

欢迎转载，但请注明本文链接，谢谢你。
2016.9.14 19:08

binarylog mysqlbinlog

mysql二进制文件操作语法(mysql binary log operate statements)

开启 binary logs 功能

查看 binary logs

手动删除 binary log

binary log 格式

使用 binary log 增量恢复数据

相关

手动创建binary log files和手动编辑binary log index file会有什么影响

使用mysqlbinlog远程备份binlog

mysqlbinlog输出sql

mysqlbinlog 工具分析binlog日志

标签