为 MySQL/MariaDB 开启 Binlog 功能|云服务器 ECS - 开发者论坛

你不知道的忧伤

<div class="f14 mb10" id="read_tpc">
<span style="display:none"> r]x;JBy </span>&nbsp;<br /> <span style="display:none"> O&lt;3i6 </span>&nbsp;<br /><font size="6"><b>介绍</b></font><span style="display:none"> %G%##wv: </span>&nbsp;<br /> <span style="display:none"> ,"U8Fgf[r </span>&nbsp;<br /> <span style="display:none"> FzOr#(^ </span>&nbsp;<br />说到 Binlog 就不得不提一下 MySQL Server 的四种类型的日志：Error Log、General Query Log、Slow Query Log 和 Binary Log 。<span style="display:none"> ]Gw?DD|Gn </span>&nbsp;<br /><ol type="1" style="margin:0 0 0 25px"><li><b>Error Log</b> 即 <span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=错误',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_8">错误</span>日志，记录 mysqld 发生的一些错误。</li><li><b>General Query Log</b> 即 一般查询日志，记录 mysqld 正在做的事情，如客户端的连接和断开、来<span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=自',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_3">自</span>客户端每条 Sql Statement 记录<span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=信息',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_4">信息</span>；如果你想准确知道客户端究竟传了什么内容给<span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=服务',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_2">服务</span>端，这个日志就非常管用了，当然了这货非常影响性能。</li><li><b>Slow Query Log</b> 即 慢查询日志，记录一些查询执行较慢的 SQL 语句，这个日志非常常用，主要是给开发者调优用的。</li><li><b>Binary Log</b> 简称 Binlog 即 二进制日志文件，这个文件记录了mysql所有的 DML <span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=操作',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_9">操作</span>。通过 Binlog 日志我们可以做<span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=数据',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_5">数据</span>恢复，做主主复制和主从复制等等。对于开发者可能对 Binlog 并不怎么关注，但是对于运维或者架构人员来讲是非常重要的。</li></ol><span style="display:none"> zE{@
' </span>&nbsp;<br /><span style="display:none"> P#o/S4 </span>&nbsp;<br /><span style="display:none"> v1hrRf2&lt; </span>&nbsp;<br />简而言之，Binlog 两个重要的用途——复制和恢复，很多十分好用的 MySQL 体验比如说增量备份，回滚至指定时间以及上面提到的主主和主从等等都依赖于 Binlog。<span style="display:none"> c65_E&lt;5Z </span>&nbsp;<br /><b>Binlog 有三种模式：</b><ol type="1" style="margin:0 0 0 25px"><li>STATEMENT：顾名思义，STATEMENT 格式的 Binlog 记录的是<span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=数据库',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_7">数据库</span>上执行的原生SQL语句</li><li>ROW：这种格式的 Binlog 记录的是数据表的行是怎样被修改的。</li><li>MIXED：混合模式，如果设置了这种格式，MariaDB / MySQL 会在一些特定的情况下自动从 STATEMENT 格式切换到 ROW 格式。例如，包含 UUID 等不确定性函数的语句，引用了<span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=系统',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_6">系统</span>变量的语句等等。</li></ol><span style="display:none"> xO2S|DH{ </span>&nbsp;<br /> <span style="display:none"> &lt;B]\& </span>&nbsp;<br /><font size="6"><b>开启 Binlog</b></font><span style="display:none"> >uN`q1?l' </span>&nbsp;<br /> <span style="display:none"> /o=V
( </span>&nbsp;<br /> <span style="display:none"> tuA
,t </span>&nbsp;<br />像一些公有云上的云数据库一般都是默认开始 Binlog 的，不需要人为干涉，如果是自建的 MySQL 一般是不开启的。<span style="display:none"> "ei*iUBN: </span>&nbsp;<br /> <span style="display:none"> "7R"(.~> </span>&nbsp;<br /><font size="5"><b>检查是否开启</b></font><span style="display:none"> RU!j"T
5 </span>&nbsp;<br /> <span style="display:none"> 2[&3$-] </span>&nbsp;<br /> <span style="display:none"> z/*nY? </span>&nbsp;<br />登录 MySQL ，执行 SQL ：<span style="display:none"> { 7y.0_Y
 </span>&nbsp;<br /><div class="f12">复制代码</div><div class="blockquote2" id="code1"><ol><li>show global variables like &#39;log_bin&#39;;</li></ol></div><span style="display:none"> GyV3]Qqj </span>&nbsp;<br /><span style="display:none"> fP`g#t)4Tu </span>&nbsp;<br />会返回一个如下的结果：<span style="display:none"> zr9o </span>&nbsp;<br /><div class="f12">复制代码</div><div class="blockquote2" id="code2"><ol><li>+---------------+-------+</li><li>| Variable_name | Value |</li><li>+---------------+-------+</li><li>| log_bin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | OFF&nbsp;&nbsp; |</li><li>+---------------+-------+</li><li>1 row in set (0.001 sec)</li></ol></div><span style="display:none"> |]a=He; </span>&nbsp;<br /><span style="display:none"> 9Vx2VjK2' </span>&nbsp;<br /><span style="display:none"> ;        Xy\7tx </span>&nbsp;<br />如果返回的是 log_bin | OFF 那么就是没有开启，ON 则是已经开启<span style="display:none"> E3gR%t </span>&nbsp;<br /> <span style="display:none"> qvz2u]IOw </span>&nbsp;<br /><font size="5"><b>　设置开启</b></font><span style="display:none"> W        G2 E3y </span>&nbsp;<br /> <span style="display:none"> 2Mc/ah </span>&nbsp;<br /> <span style="display:none"> so|5HR| </span>&nbsp;<br />修改 my.cnf 文件，一般来说都位于 /etc/my.cnf 这边，部分像 MariaDB 可能是修改/etc/my.cnf.d/server.cnf 文件。<span style="display:none"> N=K|Nw </span>&nbsp;<br />在 [mysqld] 下写入如下内容：<span style="display:none"> qu[ ~# </span>&nbsp;<br /><div class="f12">复制代码</div><div class="blockquote2" id="code3"><ol><li>log_bin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = /var/lib/mysql/bin-log</li><li>log_bin_index&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = /var/lib/mysql/mysql-bin.index</li><li>expire_logs_days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= 7</li><li>server_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 0002</li><li>binlog_format&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = ROW</li></ol></div><span style="display:none"> hkw;W[ZWa </span>&nbsp;<br /><span style="display:none">         OKfJ </span>&nbsp;<br /><span style="display:none"> &lt;@FOqi{o{ </span>&nbsp;<br /><b>解释一下：</b><span style="display:none"> =>Y        b~r71 </span>&nbsp;<br />log_bin = /var/lib/mysql/bin-log ， 开启 Binlog 并写明存放日志的位置<span style="display:none"> &
| %&lt;=\ </span>&nbsp;<br />log_bin_index = /var/lib/mysql/mysql-bin.index ， 指定索引文件的位置。<span style="display:none"> W)jO        4,eO </span>&nbsp;<br />expire_logs_days = 7 ，删除超出这个变量保留期之前的全部日志被删除<span style="display:none"> f3e#.jan </span>&nbsp;<br />server_id = 0002 ， 指定一个集群内的 MySQL <span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=服务器',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_1">服务器</span> ID，如果做数据库集群那么必须全局唯一，一般来说不推荐 指定 server_id 等于 1。<span style="display:none"> D3V5GQ\=
</span>&nbsp;<br />binlog_format = ROW，设置方面提到过的三种 Binlog 的日志模式。<span style="display:none"> e&f9/r
fx </span>&nbsp;<br /><b>更多选项：</b><span style="display:none"> *6~ODiB
 </span>&nbsp;<br />max_binlog_size ，binary log 最大的大小<span style="display:none"> TEl:;4 </span>&nbsp;<br />binlog_cache_size ，当前的多少事务cache在内存中<span style="display:none">  ~dfc </span>&nbsp;<br />binlog_cache_disk_use ，当前有多少事务暂存在磁盘上的，如果这个值有数值的话，就应该要注意调优了。<span style="display:none"> i"%X[(U7 </span>&nbsp;<br />max_binlog_cache_size ，最大能有多少事务cache在内存中<span style="display:none"> fk)ts,p? </span>&nbsp;<br />binlog_do_db和binlog_ingore_db ，是一对控制对哪些数据库进行收集的选项。<span style="display:none"> t4pc2b </span>&nbsp;<br />sync_binlog ，这个值控制cache的数据commit多少次才刷到磁盘上。默认是0，也就是让数据库自己决定同步的频率。如设置成1的话，则每commit一次就会将cache的数据同步到磁盘上，这样做最<span onclick="sendmsg('pw_ajax.php','action=relatetag&tagname=安全',this.id)" style="cursor:pointer;border-bottom: 1px solid #FA891B;" id="rlt_10">安全</span>，但是性能最差。<span style="display:none"> T9]|*~ ,T </span>&nbsp;<br />最后就是重启一下数据库：<span style="display:none"> kM5N#|! </span>&nbsp;<br /><div class="f12">复制代码</div><div class="blockquote2" id="code4"><ol><li>service mysql restart</li></ol></div><span style="display:none"> 3o%,8l, </span>&nbsp;<br /><span style="display:none"> oK&LYlU </span>&nbsp;<br />或者<span style="display:none"> 9ctvy?53H </span>&nbsp;<br /><div class="f12">复制代码</div><div class="blockquote2" id="code5"><ol><li>service mysqld restart</li></ol></div><span style="display:none"> *]~ug%a </span>&nbsp;<br /><span style="display:none"> B"TZ8(&lt; </span>&nbsp;<br /><span style="display:none"> (Da/$S
. </span>&nbsp;<br /> <span style="display:none"> H]
;|&lt;G </span>&nbsp;<br /><font size="5"><b>继续检查一下是否开启</b></font><span style="display:none"> 8&lt;M'~G%CEq </span>&nbsp;<br /> <span style="display:none"> MB
:knj </span>&nbsp;<br /> <span style="display:none"> g1VdP[Y# </span>&nbsp;<br />登录 MySQL ，执行 SQL ：<span style="color:#c5c8c6 "> </span><span style="display:none"> cc7*O </span>&nbsp;<br /><span style="color:#c5c8c6 "></span><div class="f12">复制代码</div><div class="blockquote2" id="code6"><ol><li>show global variables like &#39;log_bin&#39;;</li></ol></div><span style="display:none"> wXp
A1,i </span>&nbsp;<br /><span style="display:none"> xRYL{+ </span>&nbsp;<br /><span style="display:none"> )ALPMmlRs </span>&nbsp;<br />这下应该就是返回 ON 了<span style="display:none"> 9K~2!&lt;         </span>&nbsp;<br /><div class="f12">复制代码</div><div class="blockquote2" id="code7"><ol><li>MariaDB [(none)]&gt;&nbsp;&nbsp;show global variables like &#39;log_bin&#39;; </li><li>+---------------+-------+</li><li>| Variable_name | Value |</li><li>+---------------+-------+</li><li>| log_bin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | ON&nbsp;&nbsp;&nbsp;&nbsp;|</li><li>+---------------+-------+</li><li>1 row in set (0.001 sec)</li></ol></div><span style="display:none"> j*>+^g\Q6 </span>&nbsp;<br /><span style="display:none"> ,ii*[{X? </span>&nbsp;<br />
</div>
</div>
<!--content_read-->
</td>
</tr>
<tr>