Centos7实现MySQL基于日志还原数据的示例代码
<p><span><strong>简介</strong></span></p>
<p>
Binlog日志，即二进制日志文件，用于记录用户对数据库操作的SQL语句信息，当发生数据误删除的时候我们可以通过binlog日志来还原已经删除的数据，还原数据的方法分为传统二进制文件还原数据和基于GTID的二进制文件还原数据</p>
<p>
<span><strong>前期准备</strong></span></p>
<p>
准备一台Centos7虚拟机，关闭防火墙和selinux，配置IP地址,同步系统时间,安装MySQL数据库</p>
<p>
<span><strong>传统二进制日志还原数据</strong></span></p>
<p>
修改配置文件</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_820659">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># vi /etc/my.cnf</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain">server-</code><code class="bash functions">id</code><code class="bash plain">=1</code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">log-bin=binlog</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="bash comments">#重启数据库服务</code>
</div>
<div class="line number6 index5 alt1">
<code class="bash plain"></code><code class="bash comments"># systemctl restart mysqld</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
操作数据库</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_679600">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">create</code> <code class="sql keyword">database</code> <code class="sql plain">mydb charset utf8mb4;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">mysql> use mydb;</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">create</code> <code class="sql keyword">table</code> <code class="sql plain">test(id </code><code class="sql keyword">int</code><code class="sql plain">)engine=innodb charset=utf8mb4;</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">test </code><code class="sql keyword">values</code><code class="sql plain">(1);</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">test </code><code class="sql keyword">values</code><code class="sql plain">(2);</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">test </code><code class="sql keyword">values</code><code class="sql plain">(3);</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">test </code><code class="sql keyword">values</code><code class="sql plain">(4);</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">commit</code><code class="sql plain">;</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">update</code> <code class="sql plain">test </code><code class="sql keyword">set</code> <code class="sql plain">id=10 </code><code class="sql keyword">where</code> <code class="sql plain">id=4;</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">commit</code><code class="sql plain">;</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">test;</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">| id |</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">| 1 |</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql plain">| 2 |</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">| 3 |</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql plain">| 10 |</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql plain">4 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">drop</code> <code class="sql keyword">database</code> <code class="sql plain">mydb;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查看二进制日志信息</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_245768">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">mysql> show master status\G;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">*************************** 1. row ***************************</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">File: binlog.000001</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">Position: 1960</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">Binlog_Do_DB: </code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">Binlog_Ignore_DB: </code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">Executed_Gtid_Set: </code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">1 row </code><code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
<div class="line number9 index8 alt2">
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">#查找创库和删库的点,为219和1868</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql plain">mysql> show binlog events </code><code class="sql color1">in</code> <code class="sql string">'binlog.000001'</code><code class="sql plain">;</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">+</code><code class="sql comments">---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql plain">| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">+</code><code class="sql comments">---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql plain">| binlog.000001 | 219 | Query | 1 | 329 | </code><code class="sql keyword">create</code> <code class="sql keyword">database</code> <code class="sql plain">mydb charset utf8mb4 |</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">| binlog.000001 | 1868 | Query | 1 | 1960 | </code><code class="sql keyword">drop</code> <code class="sql keyword">database</code> <code class="sql plain">mydb |</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql plain">+</code><code class="sql comments">---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
另存为二进制日志信息</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_898578">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># mysqlbinlog --start-position=219 --stop-position=1868 /var/lib/mysql/binlog.000001 > /tmp/binlog.sql</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
恢复数据</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_270508">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">#临时关闭二进制日志记录以免重复记录</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">set</code> <code class="sql plain">sql_log_bin=0;</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">#恢复数据</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">mysql> source /tmp/binlog.sql</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">#重启二进制日志记录</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">set</code> <code class="sql plain">sql_log_bin=1;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查看数据恢复情况</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_461105">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">mysql> show databases;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">+</code><code class="sql comments">--------------------+</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">| </code><code class="sql keyword">Database</code> <code class="sql plain">|</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">+</code><code class="sql comments">--------------------+</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">| information_schema |</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">| mydb |</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">| mysql |</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">| performance_schema |</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">| sys |</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">+</code><code class="sql comments">--------------------+</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">5 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
<div class="line number12 index11 alt1">
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">mysql> use mydb;</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql keyword">Database</code> <code class="sql plain">changed</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">test;</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">| id |</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql plain">| 1 |</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql plain">| 2 |</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql plain">| 3 |</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql plain">| 10 |</code>
</div>
<div class="line number23 index22 alt2">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number24 index23 alt1">
<code class="sql plain">4 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)、</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<span><strong>基于GTID二进制日志还原数据</strong></span></p>
<p>
修改配置文件</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_387489">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># vi /etc/my.cnf</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain">server-</code><code class="bash functions">id</code><code class="bash plain">=1</code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">log-bin=binlog</code>
</div>
<div class="line number4 index3 alt1">
<code class="bash plain">gtid_mode=ON</code>
</div>
<div class="line number5 index4 alt2">
<code class="bash plain">enforce_gtid_consistency=</code><code class="bash functions">true</code>
</div>
<div class="line number6 index5 alt1">
<code class="bash plain">log_slave_updates=1</code>
</div>
<div class="line number7 index6 alt2">
</div>
<div class="line number8 index7 alt1">
<code class="bash comments">#重启数据库服务</code>
</div>
<div class="line number9 index8 alt2">
<code class="bash plain"></code><code class="bash comments"># systemctl restart mysqld</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
操作数据库</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_310659">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">create</code> <code class="sql keyword">database</code> <code class="sql plain">mydb1;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">mysql> use mydb1;</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">Database</code> <code class="sql plain">changed</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">create</code> <code class="sql keyword">table</code> <code class="sql plain">t1(id </code><code class="sql keyword">int</code><code class="sql plain">)engine=innodb charset=utf8mb4;</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">t1 </code><code class="sql keyword">values</code><code class="sql plain">(1);</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">t1 </code><code class="sql keyword">values</code><code class="sql plain">(2);</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">t1 </code><code class="sql keyword">values</code><code class="sql plain">(3);</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">t1 </code><code class="sql keyword">values</code><code class="sql plain">(11);</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">t1 </code><code class="sql keyword">values</code><code class="sql plain">(12);</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">commit</code><code class="sql plain">;</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">t1;</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">| id |</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">| 1 |</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql plain">| 2 |</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">| 3 |</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql plain">| 11 |</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql plain">| 12 |</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql plain">5 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">drop</code> <code class="sql keyword">database</code> <code class="sql plain">mydb1;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查看二进制日志信息</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_838083">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">mysql> show master status\G;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">*************************** 1. row ***************************</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">File: binlog.000003</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">Position: 1944</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">Binlog_Do_DB: </code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">Binlog_Ignore_DB: </code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">Executed_Gtid_Set: 51d3db57-bf69-11ea-976c-000c2911a022:1-8</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">1 row </code><code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
<div class="line number9 index8 alt2">
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">mysql> show binlog events </code><code class="sql color1">in</code> <code class="sql string">'binlog.000003'</code><code class="sql plain">;</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">+</code><code class="sql comments">---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql plain">| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">+</code><code class="sql comments">---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql plain">| binlog.000003 | 154 | Gtid | 1 | 219 | </code><code class="sql keyword">SET</code> <code class="sql plain">@@SESSION.GTID_NEXT= </code><code class="sql string">'51d3db57-bf69-11ea-976c-000c2911a022:1'</code> <code class="sql plain">|</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">| binlog.000003 | 219 | Query | 1 | 316 | </code><code class="sql keyword">create</code> <code class="sql keyword">database</code> <code class="sql plain">mydb1 |</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql plain">| binlog.000003 | 1784 | Gtid | 1 | 1849 | </code><code class="sql keyword">SET</code> <code class="sql plain">@@SESSION.GTID_NEXT= </code><code class="sql string">'51d3db57-bf69-11ea-976c-000c2911a022:8'</code> <code class="sql plain">|</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">| binlog.000003 | 1849 | Query | 1 | 1944 | </code><code class="sql keyword">drop</code> <code class="sql keyword">database</code> <code class="sql plain">mydb1 |</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql plain">+</code><code class="sql comments">---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
另存为二进制日志信息</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_264364">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash comments">#8号事务记录为删除数据库，因此只需恢复1-7号事务记录即可</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain"></code><code class="bash comments"># mysqlbinlog --skip-gtids --include-gtids='51d3db57-bf69-11ea-976c-000c2911a022:1-7' /var/lib/mysql/binlog.000003 > /tmp/gtid.sql</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
参数说明:<br>
--include-gtids:包含事务<br>
--exclude-gtids:排除事务<br>
--skip-gtids:跳过事务</p>
<p>
恢复数据</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_119028">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">set</code> <code class="sql plain">sql_log_bin=0;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">mysql> source /tmp/gtid.sql</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">set</code> <code class="sql plain">sql_log_bin=1;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查看数据恢复情况</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_527168">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">mysql> show databases;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">+</code><code class="sql comments">--------------------+</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">| </code><code class="sql keyword">Database</code> <code class="sql plain">|</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">+</code><code class="sql comments">--------------------+</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">| information_schema |</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">| mydb |</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">| mydb1 |</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">| mysql |</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">| performance_schema |</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">| sys |</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">+</code><code class="sql comments">--------------------+</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql plain">6 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
<div class="line number13 index12 alt2">
</div>
<div class="line number14 index13 alt1">
<code class="sql plain">mysql> use mydb1;</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql keyword">Database</code> <code class="sql plain">changed</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">t1;</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql plain">| id |</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql plain">| 1 |</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql plain">| 2 |</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql plain">| 3 |</code>
</div>
<div class="line number23 index22 alt2">
<code class="sql plain">| 11 |</code>
</div>
<div class="line number24 index23 alt1">
<code class="sql plain">| 12 |</code>
</div>
<div class="line number25 index24 alt2">
<code class="sql plain">+</code><code class="sql comments">------+</code>
</div>
<div class="line number26 index25 alt1">
<code class="sql plain">5 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
到此这篇关于Centos7实现MySQL基于日志还原数据的示例代码的文章就介绍到这了,更多相关Centos7 MySQL日志还原数据内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！</p>
<p>
原文链接：https://blog.51cto.com/14832653/2509121</p>
頁:
[1]