MySQL基础系列--binlog
其他网址
什么是 binlog_数据库_茅坤宝骏氹的博客-****博客
mysqlbinlog用法详细说明_数据库_JBBOY的专栏-****博客
简介
binlog定义
- binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。
- binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。
binlog用途
三个用途，分别为恢复、复制、审计（出自《MySQL技术内幕 InnoDB存储引擎》）。
- 恢复（recovery):某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-iii-time的恢复。
- 复制（replication):其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或standby)与一台MySQL数据库（一般称为master或primary)进行实时同步。
- 审计（audit):用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。
binlog格式
|
格式 |
定义 |
优点 |
缺点 |
|
row |
记录的是行数据的变更。（默认格式） |
非常清楚的记录每条数据详细的变更细节。 |
binlog日志会非常大，mysql主从同步时，会产生大量磁盘IO |
|
statement |
记录的是修改数据的sql |
不需要记录每一行的变化，日志文件小，节约IO，可提高性能 |
准确性差，对一些系统函数不能准确复制或者不能复制，如now(),uuid()等 |
|
mixed |
statement与row的结合： 一般的语句修改使用statement格式保存binlog，statement无法完成主从复制的操作（如一些函数），则采用row格式保存binlog。 |
row与statement的折中。 |
有可能发生主从不一致问题 |
业内目前推荐使用的是row模式，准确性高，虽然说文件大，但是现在有SSD和万兆光纤网络，这些磁盘IO和网络IO都是可以接受的。
不推荐使用mixed模式，因为有可能主从不一致，如下：假设master有两条记录，而slave只有一条记录。
当在master上更新一条从库不存在的记录时，也就是id=2的记录，你会发现master是可以执行成功的。而slave拿到这个SQL后，也会照常执行，不报任何异常，只是更新操作不影响行数而已。并且你执行命令show slave status，查看输出，你会发现没有异常。但是，如果你是row模式，由于这行根本不存在，是会报1062错误的。
binlog命令
开启binlog的方法
法1：set logbin='on';
法2：修改配置文件
[mysqld] # 这一行必须有，否则会出错
#log_bin
log-bin = mysql-bin #开启binlog
binlog-format = ROW #选择row模式
server_id = 12345 #配置mysql replication需要定义，不能和canal的slaveId重复
|
命令 |
作用 |
|
mysqlbinlog [options] log-files |
查看binlog文件 |
|
show variables like 'binlog_format'; |
查看mysql的binlog模式 |
|
show variables like 'log_bin'; |
查看mysql是否开启binlog同步功能。默认是关闭的 |
|
show binary logs |
获取binlog文件日志列表 |
|
show master status |
查看当前正在写入的binlog文件 |
|
show master logs |
查看master上的binlog文件 |
|
show binlog events |
查看第一个binlog文件内容 |
|
show binlog events 'mysql-bin.000002' |
查看指定binlog文件内容， 如：查看mysql-bin.000002文件内容 |
|
选项 |
含义 |
|
—help，-？ |
显示帮助消息并退出。 |
|
—database=db_name，-d db_name |
只列出该数据库的条目(只用本地日志)。 |
|
-force-read，-f |
使用该选项，如果mysqlbinlog读它不能识别的二进制日志事件，它会打印警告，忽略该事件并继续。没有该选项，如果mysqlbinlog读到此类事件则停止。 |
|
-hexdump，-H |
在注释中显示日志的十六进制转储。该输出可以帮助复制过程中的调试。在MySQL 5.1.2中添加了该选项。 |
|
-host=host_name，-h host_name |
获取给定主机上的MySQL服务器的二进制日志。 |
|
-local-load=path，-l path |
为指定目录中的LOAD DATA INFILE预处理本地临时文件。 |
|
-offset=N，-o N |
跳过前N个条目。 |
|
-password[=password]，-p[password] |
当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和 密码之间不能有空格。如果在命令行中-password或-p选项后面没有 密码值，则提示输入一个密码。 |
|
-port=port_num，-P port_num |
用于连接远程服务器的TCP/IP端口号。 |
|
-position=N，-j N |
不赞成使用，应使用-start-position。 |
|
-protocol={TCP | SOCKET | PIPE | -position |
使用的连接协议。 |
|
-read-from-remote-server，-R |
从MySQL服务器读二进制日志。如果未给出该选项，任何连接参数选项将被忽略。这些选项是-host、-password、-port、-protocol、-socket和-user。 |
|
-result-file=name, -r name |
将输出指向给定的文件。 |
|
-short-form，-s |
只显示日志中包含的语句，不显示其它信息。 |
|
-socket=path，-S path |
用于连接的套接字文件。 |
|
-start-datetime=datetime |
从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。例如：shell> mysqlbinlog -start-datetime=”2004-12-25 11:25:56″ binlog.000003该选项可以帮助点对点恢复。 |
|
-stop-datetime=datetime |
从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见-start-datetime选项。该选项可以帮助及时恢复。 |
|
-start-position=N |
从二进制日志中第1个位置等于N参量时的事件开始读。 |
|
-stop-position=N |
从二进制日志中第1个位置等于和大于N参量时的事件起停止读。 |
|
-to-last-logs，-t |
在MySQL服务器中请求的二进制日志的结尾处不停止，而是继续打印直到最后一个二进制日志的结尾。如果将输出发送给同一台MySQL服务器，会导致无限循环。该选项要求-read-from-remote-server。 |
|
-disable-logs-bin，-D |
禁用二进制日志。如果使用-to-last-logs选项将输出发送给同一台MySQL服务器，可以避免无限循环。该选项在崩溃恢复时也很有用，可以避免复制已经记录的语句。注释：该选项要求有SUPER权限。 |
|
-user=user_name，-u user_name |
连接远程服务器时使用的MySQL用户名。 |
|
-version，-V |
显示版本信息并退出。还可以使用-var_name=value选项设置下面的变量：open_files_limit指定要保留的打开的文件描述符的数量。 |