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"

When writing to a dump file while reading back from a binary log containing GTIDs (see Section 18.1.3, “Replication with Global Transaction Identifiers”), use the --skip-gtids option with mysqlbinlog, like this:

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏个人随笔

MySQL 视图

数据库视图是虚拟表或逻辑表,它被定义为具有连接的SQL SELECT查询语句。 因为数据库视图与数据库表类似,它由行和列组成,因此可以根据数据库表查询数据。 大...

35611
来自专栏Java学习123

Linux各类压宿包的解压方法

2899
来自专栏magicsoar

Mysql Innodb 锁机制 select * from table where?insert?delete?update?3个insert的死锁2个update的死锁3个以上delete的死

latch与lock latch 可以认为是应用程序中的锁,可以称为闩锁(轻量级的锁) 因为其要求锁定的时间必须要非常短,若持续时间长,则会导致应用性能非常差,...

3278
来自专栏腾讯云Elasticsearch Service

mysql数据实时同步到Elasticsearch

业务需要把mysql的数据实时同步到ES,实现低延迟的检索到ES中的数据或者进行其它数据分析处理。本文给出以同步mysql binlog的方式实时同步数据到ES...

3.8K7
来自专栏JavaQ

MySQL存储引擎InnoDB和MyISAM对比总结(更新)

本篇内容对MySQL两个主要存储引擎InnoDB和MyISAM进行对比总结。 InnoDB引擎 InnoDB是一个事务型的存储引擎,设计目标是处理大数量数据时提...

3678
来自专栏Django Scrapy

mysql相关命令

安装 在ubuntu 14.04 x64下 1 安装 # 安装服务端 (需要中途设置root密码) sudo apt-get install mysql-ser...

3307
来自专栏十月梦想

php创建数据库和数据表

注意:在php5.5以上取消了mysql_query请使用mysqli_query(mysql $link,string $query[,int $resul...

1082
来自专栏PhpZendo

MySQL创建用户与授权

假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'pig'@'%',则在使用REVOK...

2871
来自专栏IT开发技术与工作效率

MySQL数据库优化总结《高性能MySQL》指导其他指导

3364
来自专栏文渊之博

具体问题解决:分离脚本

有时候需要从一个服务器转移数据库到另一个。如果打算一下子分离所有数据库,那么下面的脚本就是有用了。 生成分离脚本: SELECT DISTINCT 'use m...

2018

扫码关注云+社区

领取腾讯云代金券