MySQL--binlog--介绍/使用/用法

素颜马尾好姑娘i 2023-05-21 11:46 177阅读 0赞

原文网址：[MySQL--binlog--介绍/使用/用法\_IT利刃出鞘的博客-CSDN博客][MySQL--binlog--_IT_-CSDN]

# 其他网址 #

> [什么是 binlog\_数据库\_茅坤宝骏氹的博客-CSDN博客][binlog_-CSDN]  
> [mysqlbinlog用法详细说明\_数据库\_JBBOY的专栏-CSDN博客][mysqlbinlog_JBBOY_-CSDN]

# 简介 #

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格式

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:75.85pt;"> 格式 </td> 
 <td style="vertical-align:top;width:173.15pt;"> 定义 </td> 
 <td style="vertical-align:top;width:193.1pt;"> 优点 </td> 
 <td style="vertical-align:top;width:222.5pt;"> 缺点 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:75.85pt;"> row </td> 
 <td style="vertical-align:top;width:173.15pt;"> 记录的是行数据的变更。（默认格式） </td> 
 <td style="vertical-align:top;width:193.1pt;"> 非常清楚的记录每条数据详细的变更细节。 </td> 
 <td style="vertical-align:top;width:222.5pt;"> binlog日志会非常大，mysql主从同步时，会产生大量磁盘IO </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:75.85pt;"> statement </td> 
 <td style="vertical-align:top;width:173.15pt;"> 记录的是修改数据的sql </td> 
 <td style="vertical-align:top;width:193.1pt;"> 不需要记录每一行的变化，日志文件小，节约IO，可提高性能 </td> 
 <td style="vertical-align:top;width:222.5pt;"> 准确性差，对一些系统函数不能准确复制或者不能复制，如now(),uuid()等 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:75.85pt;"> mixed </td> 
 <td style="vertical-align:top;width:173.15pt;"> statement与row的结合： 一般的语句修改使用statement格式保存binlog，statement无法完成主从复制的操作（如一些函数），则采用row格式保存binlog。 </td> 
 <td style="vertical-align:top;width:193.1pt;"> row与statement的折中。 </td> 
 <td style="vertical-align:top;width:222.5pt;"> 有可能发生主从不一致问题 </td> 
 </tr> 
 </tbody> 
</table>

> 业内目前**推荐使用的是row模式**，准确性高，虽然说文件大，但是现在有SSD和万兆光纤网络，这些磁盘IO和网络IO都是可以接受的。  
> 不推荐使用mixed模式，因为有可能主从不一致，如下：假设master有两条记录，而slave只有一条记录。
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vYWt1bg_size_16_color_FFFFFF_t_70][]
> 
> 当在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重复

<table style="width:495.4pt;"> binlog相关命令 
 <tbody> 
 <tr> 
 <td style="width:220pt;"> 命令 </td> 
 <td style="width:273.2pt;"> 作用 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> mysqlbinlog [options] log-files </td> 
 <td style="width:273.2pt;"> 查看binlog文件 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> show variables like 'binlog_format'; </td> 
 <td style="width:273.2pt;"> 查看mysql的binlog模式 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> show variables like 'log_bin'; </td> 
 <td style="width:273.2pt;"> 查看mysql是否开启binlog同步功能。默认是关闭的 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> show binary logs </td> 
 <td style="width:273.2pt;"> 获取binlog文件日志列表 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> show master status </td> 
 <td style="width:273.2pt;"> 查看当前正在写入的binlog文件 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> show master logs </td> 
 <td style="width:273.2pt;"> 查看master上的binlog文件 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> show binlog events </td> 
 <td style="width:273.2pt;"> 查看第一个binlog文件内容 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> show binlog events 'mysql-bin.000002' </td> 
 <td style="width:273.2pt;"> 查看指定binlog文件内容， 如：查看mysql-bin.000002文件内容 </td> 
 </tr> 
 </tbody> 
</table>

<table style="width:495.4pt;"> mysqlbinlog选项及含义 
 <tbody> 
 <tr> 
 <td style="width:220pt;"> 选项 </td> 
 <td style="width:273.2pt;"> 含义 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> —help，-？ </td> 
 <td style="width:273.2pt;"> 显示帮助消息并退出。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> —database=db_name，-d db_name </td> 
 <td style="width:273.2pt;"> 只列出该数据库的条目(只用本地日志)。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -force-read，-f </td> 
 <td style="width:273.2pt;"> 使用该选项，如果mysqlbinlog读它不能识别的二进制日志事件，它会打印警告，忽略该事件并继续。没有该选项，如果mysqlbinlog读到此类事件则停止。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -hexdump，-H </td> 
 <td style="width:273.2pt;"> 在注释中显示日志的十六进制转储。该输出可以帮助复制过程中的调试。在MySQL 5.1.2中添加了该选项。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -host=host_name，-h host_name </td> 
 <td style="width:273.2pt;"> 获取给定主机上的MySQL服务器的二进制日志。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -local-load=path，-l path </td> 
 <td style="width:273.2pt;"> 为指定目录中的LOAD DATA INFILE预处理本地临时文件。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -offset=N，-o N </td> 
 <td style="width:273.2pt;"> 跳过前N个条目。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -password[=password]，-p[password] </td> 
 <td style="width:273.2pt;"> 当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和 密码之间不能有空格。如果在命令行中-password或-p选项后面没有 密码值，则提示输入一个密码。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -port=port_num，-P port_num </td> 
 <td style="width:273.2pt;"> 用于连接远程服务器的TCP/IP端口号。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -position=N，-j N </td> 
 <td style="width:273.2pt;"> 不赞成使用，应使用-start-position。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -protocol={TCP | SOCKET | PIPE | -position </td> 
 <td style="width:273.2pt;"> 使用的连接协议。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -read-from-remote-server，-R </td> 
 <td style="width:273.2pt;"> 从MySQL服务器读二进制日志。如果未给出该选项，任何连接参数选项将被忽略。这些选项是-host、-password、-port、-protocol、-socket和-user。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -result-file=name, -r name </td> 
 <td style="width:273.2pt;"> 将输出指向给定的文件。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -short-form，-s </td> 
 <td style="width:273.2pt;"> 只显示日志中包含的语句，不显示其它信息。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -socket=path，-S path </td> 
 <td style="width:273.2pt;"> 用于连接的套接字文件。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -start-datetime=datetime </td> 
 <td style="width:273.2pt;"> 从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。例如：shell&gt; mysqlbinlog -start-datetime=”2004-12-25 11:25:56″ binlog.000003该选项可以帮助点对点恢复。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -stop-datetime=datetime </td> 
 <td style="width:273.2pt;"> 从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见-start-datetime选项。该选项可以帮助及时恢复。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -start-position=N </td> 
 <td style="width:273.2pt;"> 从二进制日志中第1个位置等于N参量时的事件开始读。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -stop-position=N </td> 
 <td style="width:273.2pt;"> 从二进制日志中第1个位置等于和大于N参量时的事件起停止读。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -to-last-logs，-t </td> 
 <td style="width:273.2pt;"> 在MySQL服务器中请求的二进制日志的结尾处不停止，而是继续打印直到最后一个二进制日志的结尾。如果将输出发送给同一台MySQL服务器，会导致无限循环。该选项要求-read-from-remote-server。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -disable-logs-bin，-D </td> 
 <td style="width:273.2pt;"> 禁用二进制日志。如果使用-to-last-logs选项将输出发送给同一台MySQL服务器，可以避免无限循环。该选项在崩溃恢复时也很有用，可以避免复制已经记录的语句。注释：该选项要求有SUPER权限。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -user=user_name，-u user_name </td> 
 <td style="width:273.2pt;"> 连接远程服务器时使用的MySQL用户名。 </td> 
 </tr> 
 <tr> 
 <td style="width:220pt;"> -version，-V </td> 
 <td style="width:273.2pt;"> 显示版本信息并退出。还可以使用-var_name=value选项设置下面的变量：open_files_limit指定要保留的打开的文件描述符的数量。 </td> 
 </tr> 
 </tbody> 
</table>

[MySQL--binlog--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/105602226
[binlog_-CSDN]: https://blog.csdn.net/moakun/article/details/84194249
[mysqlbinlog_JBBOY_-CSDN]: https://blog.csdn.net/jbboy/article/details/42489419
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vYWt1bg_size_16_color_FFFFFF_t_70]: /images/20230521/f023e8a36a8645bf89224fbd1b3ccc67.png