(window.webpackJsonp=window.webpackJsonp||[]).push([[104],{405:function(s,t,a){"use strict";a.r(t);var e=a(10),r=Object(e.a)({},(function(){var s=this,t=s._self._c;return t("ContentSlotsDistributor",{attrs:{"slot-key":s.$parent.slotKey}},[t("h1",{attrs:{id:"日志"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#日志"}},[s._v("#")]),s._v(" 日志")]),s._v(" "),t("p",[s._v("mysqld 可以产生多种日志，不过默认只启用了错误日志。")]),s._v(" "),t("h2",{attrs:{id:"error-log"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#error-log"}},[s._v("#")]),s._v(" Error Log")]),s._v(" "),t("p",[s._v("：错误日志。用于记录 mysqld 启动、停止、报错的日志。")]),s._v(" "),t("ul",[t("li",[s._v("相关配置："),t("div",{staticClass:"language-ini extra-class"},[t("pre",{pre:!0,attrs:{class:"language-ini"}},[t("code",[t("span",{pre:!0,attrs:{class:"token section"}},[t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("[")]),t("span",{pre:!0,attrs:{class:"token section-name selector"}},[s._v("mysqld")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("]")])]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("log_error")]),s._v("    "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("/var/lib/mysql/mysql-error.log   # 错误日志的保存路径。默认为 log_error=stderr ，即输出到终端")]),s._v("\n")])])])]),s._v(" "),t("li",[s._v("日志内容示例："),t("div",{staticClass:"language-sh extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sh"}},[t("code",[t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2020")]),s._v("-01-12T09:09:17.376824Z "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("0")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("[")]),s._v("Note"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("]")]),s._v(" Server socket created on IP: "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'0.0.0.0'")]),t("span",{pre:!0,attrs:{class:"token builtin class-name"}},[s._v(".")]),s._v("                  "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 创建 Socket")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2020")]),s._v("-01-12T09:09:17.385746Z "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("0")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("[")]),s._v("Note"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("]")]),s._v(" Event Scheduler: Loaded "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("0")]),s._v(" events\n"),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2020")]),s._v("-01-12T09:09:17.385880Z "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("0")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("[")]),s._v("Note"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("]")]),s._v(" mysqld: ready "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("for")]),s._v(" connections.                           "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# mysqld 启动成功，允许被连接")]),s._v("\nVersion: "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'5.7.26-29-log'")]),s._v("  socket: "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'/var/lib/mysql/mysql.sock'")]),s._v("  port: "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("3306")]),s._v("  Percona Server "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),s._v("GPL"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(", Release "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("29")]),s._v(", Revision 11ad961\n"),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2020")]),s._v("-01-12T09:34:51.166819Z "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("5")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("[")]),s._v("Note"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("]")]),s._v(" Access denied "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("for")]),s._v(" user root@"),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'localhost'")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),s._v("using password: YES"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v("    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 客户端连接出错（连接成功则不会在该日志中记录）")]),s._v("\n")])])])]),s._v(" "),t("li",[s._v("相关命令："),t("div",{staticClass:"language-sql extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sql"}},[t("code",[s._v("flush logs"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("           "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 关闭并重新打开所有类型的日志文件，这会将内存中的日志缓冲立即写入磁盘")]),s._v("\nflush "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("binary")]),s._v("  logs"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("   "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 关闭当前打开的 binlog 文件，并创建一个新的 binlog 文件供写入")]),s._v("\nflush "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("engine")]),s._v("  logs"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\nflush error   logs"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\nflush general logs"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\nflush slow    logs"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\nflush replay  logs"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n")])])])])]),s._v(" "),t("h2",{attrs:{id:"general-query-log"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#general-query-log"}},[s._v("#")]),s._v(" General Query Log")]),s._v(" "),t("p",[s._v("：通用查询日志。用于记录 mysqld 执行过的所有 SQL 命令。")]),s._v(" "),t("ul",[t("li",[s._v("相关配置："),t("div",{staticClass:"language-ini extra-class"},[t("pre",{pre:!0,attrs:{class:"language-ini"}},[t("code",[t("span",{pre:!0,attrs:{class:"token section"}},[t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("[")]),t("span",{pre:!0,attrs:{class:"token section-name selector"}},[s._v("mysqld")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("]")])]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("general_log")]),s._v("       "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("on                          # 是否启用。默认禁用，因为会拖慢 MySQL 的处理速度")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("general_log_file")]),s._v("  "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("/var/lib/mysql/mysql.log")]),s._v("\n")])])])]),s._v(" "),t("li",[s._v("日志内容示例："),t("div",{staticClass:"language-sh extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sh"}},[t("code",[s._v("Time                            Id  Command   Argument\n"),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2020")]),s._v("-01-12T09:10:00.371447Z     "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2")]),s._v("   Connect   root@localhost on  using Socket     "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 客户端建立连接")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2020")]),s._v("-01-12T09:10:00.371642Z     "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2")]),s._v("   Query     SELECT @@version_comment LIMIT "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2020")]),s._v("-01-12T09:10:24.049055Z     "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2")]),s._v("   Query     SHOW variables LIKE "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'%time_zone%'")]),s._v("   "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 执行一条 SQL 命令")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2020")]),s._v("-01-12T09:34:34.644737Z     "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2")]),s._v("   Query     SHOW tables\n"),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2020")]),s._v("-01-12T09:34:43.278005Z     "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2")]),s._v("   Quit                                          "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 客户端断开连接")]),s._v("\n")])])]),t("ul",[t("li",[s._v("Id 表示这是 mysql 启动之后，第几次建立的客户端连接。")])])])]),s._v(" "),t("h2",{attrs:{id:"slow-query-log"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#slow-query-log"}},[s._v("#")]),s._v(" Slow Query Log")]),s._v(" "),t("p",[s._v("：慢查询日志，简称为慢日志。用于记录耗时较久的查询操作，方便用户排查问题、进行优化。")]),s._v(" "),t("ul",[t("li",[t("p",[s._v("相关配置：")]),s._v(" "),t("div",{staticClass:"language-ini extra-class"},[t("pre",{pre:!0,attrs:{class:"language-ini"}},[t("code",[t("span",{pre:!0,attrs:{class:"token section"}},[t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("[")]),t("span",{pre:!0,attrs:{class:"token section-name selector"}},[s._v("mysqld")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("]")])]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("slow_query_log")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("on                     # 是否启用慢日志。默认禁用")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("long_query_time")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("10                    # 慢查询的阈值，默认为 10 秒，超过该时长的查询操作才会被记录")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("log_queries_not_using_indexes")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("off     # 是否记录未使用索引的查询操作")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("slow_query_log_file")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("'/var/lib/mysql/mysql-slow.log'   # 日志文件的保存路径，默认为 <hostname>-slow.log")]),s._v("\n")])])]),t("ul",[t("li",[s._v("long_query_time 的最小值为 0 ，表示记录所有查询操作，但记录大量慢日志会降低 MySQL 的性能。")])])]),s._v(" "),t("li",[t("p",[s._v("也可以在 MySQL 终端中配置，当 MySQL 重启时就会失效：")]),s._v(" "),t("div",{staticClass:"language-sh extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sh"}},[t("code",[s._v("mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" SET global "),t("span",{pre:!0,attrs:{class:"token assign-left variable"}},[s._v("slow_query_log")]),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v("on"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\nmysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" SET global "),t("span",{pre:!0,attrs:{class:"token assign-left variable"}},[s._v("long_query_time")]),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("3")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n")])])])]),s._v(" "),t("li",[t("p",[s._v("直接查看慢日志文件比较麻烦，可以用 mysqldumpslow 命令进行筛选：")]),s._v(" "),t("div",{staticClass:"language-sh extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sh"}},[t("code",[s._v("mysqldumpslow "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("<")]),s._v("slow.log"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v("\n              "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("-s")]),s._v("            "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 指定排序方式，默认为 at")]),s._v("\n                  c         "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# count ，访问次数")]),s._v("\n                  l         "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# lock time ，锁定时间")]),s._v("\n                  r         "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# rows sent ，返回的记录数")]),s._v("\n                  t         "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# query time ，查询时间")]),s._v("\n                  al        "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# average rows sent")]),s._v("\n                  ar        "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# average query time")]),s._v("\n                  at        "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# average lock time")]),s._v("\n              "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("-r")]),s._v("            "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 倒序排列")]),s._v("\n              "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("-t")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("10")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 只显示前 10 条")]),s._v("\n              "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("-g")]),s._v(" PATTERN    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 通过正则匹配进行筛选")]),s._v("\n")])])]),t("p",[s._v("例：")]),s._v(" "),t("div",{staticClass:"language-sh extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sh"}},[t("code",[s._v("mysqldumpslow "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("-s")]),s._v(" r "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("-t")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("10")]),s._v(" slow.log\n")])])])])]),s._v(" "),t("h2",{attrs:{id:"binary-log"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#binary-log"}},[s._v("#")]),s._v(" Binary Log")]),s._v(" "),t("p",[s._v("：二进制日志。用于记录 MySQL 执行过的所有 DDL、DML 语句。默认禁用。")]),s._v(" "),t("ul",[t("li",[s._v("优点：可用于拷贝数据、备份数据。")]),s._v(" "),t("li",[s._v("缺点：会让 MySQL 执行事务时稍微变慢。")]),s._v(" "),t("li",[s._v("一个事务可能包含一组事件（event），比如 INSERT、UPDATE 。因此 binlog 记录日志的基础单位是 event 。")]),s._v(" "),t("li",[s._v("mysqld 会将 binlog 保存为磁盘上的多个文件。\n"),t("ul",[t("li",[s._v("binlog 文件中，每个 event 有确定的字节偏移量 start-position、stop-position ，而事务也是根据 position 来定位。")])])])]),s._v(" "),t("h3",{attrs:{id:"相关配置"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#相关配置"}},[s._v("#")]),s._v(" 相关配置")]),s._v(" "),t("div",{staticClass:"language-ini extra-class"},[t("pre",{pre:!0,attrs:{class:"language-ini"}},[t("code",[t("span",{pre:!0,attrs:{class:"token section"}},[t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("[")]),t("span",{pre:!0,attrs:{class:"token section-name selector"}},[s._v("mysqld")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("]")])]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("server_id")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("1                 # 启用 binlog 时，必须指定服务器的唯一 ID ，以支持主从同步")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("log_bin")]),s._v("           "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("mysql-bin         # 启用 binlog ，并指定其保存路径，默认在 datadir 目录下。实际保存时会加上时间字符串作为后缀")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# log_bin_index   = mysql-bin.index   # binlog 的索引文件的保存路径，默认为 ${log_bin}.index")]),s._v("\n\n"),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# binlog_format     = row       # binlog 的记录格式")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# binlog_row_image  = full      # binlog 采用 row 记录格式时，每修改一行数据，记录哪些列。默认为 full ，表示记录所有列。改为 minimal ，则只记录被修改的部分列")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# binlog_cache_size = 32k       # binlog 的内存缓冲区大小，单位为 bytes 。超出缓冲区的事务会存储到磁盘的临时文件中")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("sync_binlog")]),s._v("       "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("0           # 每提交几次事务就写入 binlog 文件。默认为 1 ，安全性最高，但性能最差。取值为 0 则由文件系统自动写入，性能最好")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("expire_logs_days")]),s._v("  "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("7           # binlog 文件的保存天数，过期后会自动删除。默认为 0 ，即不自动删除")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# max_binlog_size = 1G          # 每个 binlog 文件的最大大小，超过则创建一个新的文件，文件编号递增。不过一个事务总是会整个写入一个 binlog 文件中，因此一个 binlog 文件可能超过最大大小")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("gtid_mode")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("on          # 是否启用 gtid ，默认为 off")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("enforce-gtid-consistency")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("on   # 是否让 master 只执行支持 gtid 的事务，默认为 off")]),s._v("\n\n"),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# binlog-do-db    = db1         # 记录该数据库（其它的默认不记录）")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# binlog-do-db    = db2         # 可以多次配置该参数")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# binlog-ignore-db= db1         # 不记录该数据库（其它的默认记录）")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# binlog-ignore-db= db2")]),s._v("\n")])])]),t("ul",[t("li",[s._v("binlog 有三种记录格式：\n"),t("ul",[t("li",[s._v("statement\n"),t("ul",[t("li",[s._v("：记录 MySQL 执行的每条写操作的 SQL 命令.")]),s._v(" "),t("li",[s._v("优点：日志量较少。")]),s._v(" "),t("li",[s._v("缺点：还原数据时，SQL 命令的执行结果不一定与原来一致。比如重复执行 "),t("code",[s._v("UPDATE tb1 SET datetime=now() WHERE id=1;")]),s._v(" 时，now() 函数的返回值不同。")])])]),s._v(" "),t("li",[s._v("row\n"),t("ul",[t("li",[s._v("：记录 MySQL 对每行数据做出的实际修改，记录成 Write_rows、Update_rows、Delete_rows 事件。")]),s._v(" "),t("li",[s._v("假设一条 UPDATE 语句修改了 5 条数据，则 statement 格式只会记录一条 SQL 命令，而 row 格式会记录 5 条被修改的数据内容。")]),s._v(" "),t("li",[s._v("优点：还原数据时，能保证与原来的数据一致。")]),s._v(" "),t("li",[s._v("缺点：日志量较多。且只能记录 INSERT、UPDATE、DELETE 三种 DML 语句修改的数据，而 DDL 语句依然以 statement 格式记录，记录成 create table、alter table、drop table 等日志。")])])]),s._v(" "),t("li",[s._v("mixed\n"),t("ul",[t("li",[s._v("：一般的操作用 statement 格式记录，状态可能变化的操作用 row 格式记录。")])])])])]),s._v(" "),t("li",[s._v("binlog 根据 position 定位每个事务，因此主从复制时，各个事务必须按顺序传输。\n"),t("ul",[t("li",[s._v("启用 gtid 时，会给每个已提交事务分配一个全局事务 ID（Global Transaction ID ，GTID），格式为 "),t("code",[s._v("server_uuid:transaction_id")]),s._v(" 。\n"),t("ul",[t("li",[s._v("此时不需要根据 position 定位事务，因此不需要按顺序传输事务，还可以避免 slave 重复执行同一事务。")])])])])])]),s._v(" "),t("h3",{attrs:{id:"相关命令"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#相关命令"}},[s._v("#")]),s._v(" 相关命令")]),s._v(" "),t("ul",[t("li",[t("p",[s._v("显示 binlog 的配置：")]),s._v(" "),t("div",{staticClass:"language-sql extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sql"}},[t("code",[s._v("mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SHOW")]),s._v(" variables "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("LIKE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'%log_bin%'")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("---------------------------------+--------------------------------+")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Variable_name                   "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("Value")]),s._v("                          "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("---------------------------------+--------------------------------+")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" log_bin                         "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ON")]),s._v("                             "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 说明是否启用了 binlog")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" log_bin_basename                "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("/")]),s._v("var"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("/")]),s._v("lib"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("/")]),s._v("mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("/")]),s._v("mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("bin       "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" log_bin_index                   "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("/")]),s._v("var"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("/")]),s._v("lib"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("/")]),s._v("mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("/")]),s._v("mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("bin"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("index")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" log_bin_trust_function_creators "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("OFF")]),s._v("                            "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" log_bin_use_v1_row_events       "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("OFF")]),s._v("                            "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" sql_log_bin                     "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ON")]),s._v("                             "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 是否为当前 session 启用 binlog")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("---------------------------------+--------------------------------+")]),s._v("\n")])])])]),s._v(" "),t("li",[t("p",[s._v("显示 binlog 的内容：")]),s._v(" "),t("div",{staticClass:"language-sql extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sql"}},[t("code",[s._v("mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SHOW")]),s._v(" binlog events "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("in")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'mysql-bin.000060'")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("LIMIT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("7")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Log_name         "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Pos "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Event_type     "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Server_id "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" End_log_pos "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Info                                                                 "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("bin"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("000060")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("   "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("4")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Format_desc    "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("125")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Server ver: "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("8.0")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v(".25")]),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("15")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" Binlog ver: "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("4")]),s._v("                                 "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("  "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- binlog 固定格式的开头")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("bin"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("000060")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("125")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Previous_gtids "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("196")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" f75d3723"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),s._v("c3e"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("11")]),s._v("ec"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("a666"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("0242")]),s._v("c0a8f002:"),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("7754")]),s._v("                          "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("bin"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("000060")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("196")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Gtid           "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("275")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SET")]),s._v(" @"),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@SESSION.GTID_NEXT")]),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'f7343723-8di7-11ec-aj66-0r36c0a8f002:7455'")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("bin"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("000060")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("275")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Query          "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("370")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("BEGIN")]),s._v("                                                                "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("  "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 开始一个事务")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("bin"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("000060")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("370")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Table_map      "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("449")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" table_id: "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("85")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),s._v("db1"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("tb1"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v("                                               "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("bin"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("000060")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("449")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Update_rows    "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("619")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" table_id: "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("85")]),s._v(" flags: STMT_END_F                                       "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" mysql"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("-")]),s._v("bin"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("000060")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("619")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" Xid            "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("1")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("650")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("COMMIT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("/* xid=351 */")]),s._v("                                                 "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("|")]),s._v("  "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 结束一个事务")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+")]),s._v("\n")])])])]),s._v(" "),t("li",[t("p",[s._v("其它命令：")]),s._v(" "),t("div",{staticClass:"language-sql extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sql"}},[t("code",[t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SHOW")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("binary")]),s._v(" logs"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("                           "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 显示当前所有 binlog 文件")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SHOW")]),s._v(" binlog events "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("in")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'mysql-bin.000001'")]),s._v("    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 显示一个 binlog 的内容")]),s._v("\n                      "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("0")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("LIMIT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("2")]),s._v("        "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 显示从 position=0 开始的最多 2 个 event")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SHOW")]),s._v(" master "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("status")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("                         "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 显示 master 的状态")]),s._v("\n\nreset master"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("                               "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 删除所有 binlog 及其索引，重新开始记录")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("purge")]),s._v(" master logs "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("to")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'mysql-bin.000001'")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 删除指定的 binlog")]),s._v("\n")])])])]),s._v(" "),t("li",[t("p",[s._v("在 shell 中，可用官方提供的 mysqlbinlog 命令解析 binlog 的内容。如下：")]),s._v(" "),t("div",{staticClass:"language-sh extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sh"}},[t("code",[s._v("mysqlbinlog mysql-bin.000001\n            "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("--database")]),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v("db1                  "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 只显示指定数据库的记录")]),s._v("\n            --start-position"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("0")]),s._v("\n            --stop-position"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("177")]),s._v("\n            --start-datetime"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'2019-12-01 12:00:00'")]),s._v("\n            --stop-datetime"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'2019-12-02 12:00:00'")]),s._v("\n            "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("-vv")]),s._v(" --base64-output"),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v("decode-rows "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# row 格式的 binlog 只记录了修改的数据内容，可加上该选项，解析成 DML 语句（位于注释中）")]),s._v("\n")])])])]),s._v(" "),t("li",[t("p",[s._v("将 binlog 转换成.sql 文件之后，便可以导入数据库，还原数据。如下：")]),s._v(" "),t("div",{staticClass:"language-sh extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sh"}},[t("code",[s._v("mysqlbinlog mysql-bin.000001 "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" tmp.sql\nmysql "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("-u")]),s._v(" root "),t("span",{pre:!0,attrs:{class:"token parameter variable"}},[s._v("-p")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("<")]),s._v(" tmp.sql\n")])])])])]),s._v(" "),t("h2",{attrs:{id:"transaction-log"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#transaction-log"}},[s._v("#")]),s._v(" Transaction Log")]),s._v(" "),t("ul",[t("li",[s._v("InnoDB 引擎默认启用了事务日志（Transaction Log），分为两种：\n"),t("ul",[t("li",[s._v("重做日志（redo log）")]),s._v(" "),t("li",[s._v("回滚日志（undo log）")])])])]),s._v(" "),t("h3",{attrs:{id:"redo-log"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#redo-log"}},[s._v("#")]),s._v(" redo log")]),s._v(" "),t("p",[s._v("：属于预写日志（WAL），用于在数据库崩溃之后重启时，重新提交最近一些尚未保存到磁盘的事务。")]),s._v(" "),t("ul",[t("li",[t("p",[s._v("InnoDB 引擎每次提交事务时，会用 redo log 记录相应数据页的变化内容。")]),s._v(" "),t("ul",[t("li",[s._v("新增的事务先记录到内存的 redo log buffer ，缓冲了一定数量才写入磁盘的 redo log file ，并记录此时的 LSN 。\n"),t("ul",[t("li",[s._v("redo log file 缓冲了一定数量，才 flush 到磁盘，实际完成事务。")])])]),s._v(" "),t("li",[s._v("redo log file 的大小固定，会从头到尾循环写入。")]),s._v(" "),t("li",[s._v("MySQL 重启时，会恢复 redo log file 中尚未 flush 到磁盘的数据页。")])])]),s._v(" "),t("li",[t("p",[s._v("新增一个事务时，InnoDB 引擎通过二阶段提交确保将它同时记录到 redo log 和 binary log 。")]),s._v(" "),t("ol",[t("li",[s._v("先将该事务记录到 redo log buffer ，标记为 prepare 状态，表示预提交。")]),s._v(" "),t("li",[s._v("然后将该事务记录到 binary log 。")]),s._v(" "),t("li",[s._v("最后在 redo log buffer 中将该事务标记为 commit 状态，表示已提交。实际上不一定写入了磁盘。")]),s._v(" "),t("li",[s._v("返回响应给客户端。")])])]),s._v(" "),t("li",[t("p",[s._v("相关配置：")]),s._v(" "),t("div",{staticClass:"language-ini extra-class"},[t("pre",{pre:!0,attrs:{class:"language-ini"}},[t("code",[t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("innodb_log_buffer_size")]),s._v("         "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("16777216   # redo log buffer 的大小，默认为 16M")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("innodb_log_file_size")]),s._v("           "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("50331648   # redo log file 的大小，默认为 48M")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("innodb_log_files_in_group")]),s._v("      "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("2          # redo log file 的数量，默认为 2 个，名为 ib_logfile0、ib_logfile1")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("innodb_log_group_home_dir")]),s._v("      "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("./         # redo log file 的保存目录，默认在数据目录下")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token key attr-name"}},[s._v("innodb_flush_log_at_trx_commit")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token value attr-value"}},[s._v("1          # redo log 的 flush 策略，取值如下：")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 0 ：每隔一秒，就将 redo log buffer 新增的事务写入 redo log file ，并 flush 到磁盘")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 1 ：默认值。redo log buffer 每次新增事务时，都写入 redo log file ，并 flush 到磁盘，实现严格的事务")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("# 2 ：redo log buffer 每次新增事务时，都写入 redo log file ，但每隔一秒才 flush 到磁盘")]),s._v("\n")])])])]),s._v(" "),t("li",[t("p",[s._v("关闭 redo log ：")]),s._v(" "),t("div",{staticClass:"language-sql extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sql"}},[t("code",[t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ALTER")]),s._v(" INSTANCE "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DISABLE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("INNODB")]),s._v(" REDO_LOG"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("             "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 关闭 redo log")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SHOW")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("GLOBAL")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("STATUS")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("LIKE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'Innodb_redo_log_enabled'")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("  "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 查看是否启用")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ALTER")]),s._v(" INSTANCE "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ENABLE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("INNODB")]),s._v(" REDO_LOG"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("              "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- 启用 redo log")]),s._v("\n")])])])])])])}),[],!1,null,null,null);t.default=r.exports}}]);