MYSQL数据同步、复制、分发

来源：互联网发布：网络行为管理服务器编辑：程序博客网时间：2024/05/19 01:10

ySQL主从复制：
1，主从服务器都启用二进制日志，设置唯一ID，并相互建立连接的账号并授权；
2，从服务器主动向主服务器请求查询当前同步状态，并拉回数据；

主要相关命令：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">查看主服务器状态：<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">show</span> master status\G;</span>查看从服务器状态：<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">show</span> slave status\G;</span>查看mysql I/O线程：<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">show</span> processlist\G;</span>配置从服务器复制：change master to {***}详见后面说明启动从服务器复制：<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">start</span> slave;</span>授权用户：<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">grant</span> 权限 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">on</span> 数据库.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">to</span> 用户名@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'登录主机'</span> identified <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"密码"</span>;</span>刷新系统权限表：flush privileges;锁定数据库：flush tables with read <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">lock</span>;</span>解除锁定库：unlock tables;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li></ul>

相关设置

主服务器：

<code class="hljs oxygene has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">#vi /etc/my.cnf[mysqld]log-bin=mysql-bin                     <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//[必须]启用二进制日志</span>server-id=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">222</span>                             <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//[必须]服务器唯一ID，默认是1，一般取IP最后一段</span>binlog-<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">do</span>-db=wordpress         <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//表示只备份wordpress</span>binlog_ignore_db=mysql         <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//表示忽略备份mysql</span>不加binlog-<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">do</span>-db和binlog_ignore_db，那就表示备份全部数据库。mysql>GRANT REPLICATION SLAVE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> *.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">to</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'copy_user'</span>@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span> identified <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'copy_password'</span>;    --这里要注意：REPLICATION SLAVE，指主服务器复制权限mysql>show processlist;         --查看已连接的从服务器</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li></ul>

从服务器：

<code class="hljs oxygene has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">#vi /etc/my.cnf[mysqld]log-bin=mysql-bin     <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//[不是必须]    启用二进制日志 ，当从服务器是其他服务器的主服务器时，必须要设置</span>server-id=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">111</span>             <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//[必须]        服务器唯一ID，默认是1，一般取IP最后一段</span>slave-<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">skip</span>-errors = <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1062</span>    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//[不是必须]忽略重复键值错误</span>replicate-<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">do</span>-db=testreplicate-ignore-db=mysqlmysql>GRANT REPLICATION CLIENT <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> *.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">to</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'copy_user'</span>@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span> identified <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'copy_password'</span>;    --这里要注意：REPLICATION  CLIENT指从服务器复制权限mysql>change master <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">to</span>                                            --配置连接到哪个主服务器         ->master_host=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.2.12'</span>,                        --主服务器IP地址        ->master_user=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'copy_user'</span>,                            --主服务器中配置的用户名        ->master_password=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'copy_password'</span>,            --主服务器中的密码        ->master_log_file=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'mysql-bin.000039'</span>,        --就是当前主服务器日志文件名，就是从这个文件开始复制，不大于前边的File值。        ->master_log_pos=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">32176</span>;                              --从上面日志文件中的相应位置开始，前边的Position值 ，mysql>start slave;                                                        --启动从服务器复制功能]mysql>stop slave;mysql><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SET</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GLOBAL</span> SQL_SLAVE_SKIP_COUNTER=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>;    --忽略<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>步错误</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li></ul>

<code class="hljs ocaml has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">mysql>stop slave;mysql>change master <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">to</span> master_host=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.1.21'</span>,master_user=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'copy_user'</span>,  master_password=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'copy_password'</span>,master_log_file=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'mysql-bin.000031'</span>, master_log_pos=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">108506</span>;mysql>start slave;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>

查看主服务器状态，在主服务器上运行：

<code class="hljs markdown has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">mysql> show master status\G;<span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">** 1. row **</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-code" style="box-sizing: border-box;">             File: mysql-bin.000008</span><span class="hljs-code" style="box-sizing: border-box;">         Position: 154</span><span class="hljs-code" style="box-sizing: border-box;">     Binlog_Do_DB: </span> Binlog<span class="hljs-emphasis" style="box-sizing: border-box;">_Ignore_</span>DB: mysql,sys,information<span class="hljs-emphasis" style="box-sizing: border-box;">_schema,performance_</span>schemaExecuted<span class="hljs-emphasis" style="box-sizing: border-box;">_Gtid_</span>Set: 1 row in set (0.00 sec)ERROR: No query specifiedmysql> </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li></ul>

查看从服务器当前复制状态，在从服务器上运行：

<code class="hljs avrasm has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">mysql> show slave status\G<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>*************************** <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1.</span> row ***************************<span class="hljs-label" style="box-sizing: border-box;">Slave_IO_State:</span>                   Waiting for master to send event<span class="hljs-label" style="box-sizing: border-box;">Master_Host:</span>                     <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.2</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.12</span>                           //主服务器地址<span class="hljs-label" style="box-sizing: border-box;">Master_User:</span>                      copy_king                             //授权帐户名<span class="hljs-label" style="box-sizing: border-box;">Master_Port:</span>                      <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3306</span>                                      //数据库端口<span class="hljs-label" style="box-sizing: border-box;">Connect_Retry:</span>                  <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">60</span>                                          //当连接错误，间隔多长时间重试<span class="hljs-label" style="box-sizing: border-box;">Master_Log_File:</span>               mysql-bin<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.000038</span>                 //当前主服务器的最大日志ID<span class="hljs-label" style="box-sizing: border-box;">Read_Master_Log_Pos:</span>     <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">95990</span>                                    //当前主服务器进行到的日志位置，这与主服务器中的值相同<span class="hljs-label" style="box-sizing: border-box;">Relay_Log_File:</span>                 kq126-relay-bin<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.000053</span>        //自己的日志位置<span class="hljs-label" style="box-sizing: border-box;">Relay_Log_Pos:</span>                 <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">96153</span>                                    //同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos<span class="hljs-label" style="box-sizing: border-box;">Relay_Master_Log_File:</span>    mysql-bin<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.000038</span>                 //已同步到主服务器的位置<span class="hljs-label" style="box-sizing: border-box;">Slave_IO_Running:</span>           Yes                                         //此状态必须YES，连接到主库是否正在运行<span class="hljs-label" style="box-sizing: border-box;">Slave_SQL_Running:</span>        Yes                                         //此状态必须YES， 本库内写入状态是否运行，这两个都必须是YES，只要有一个是NO，就是出问题了<span class="hljs-label" style="box-sizing: border-box;">Replicate_Do_DB:</span> <span class="hljs-label" style="box-sizing: border-box;">Replicate_Ignore_DB:</span> <span class="hljs-label" style="box-sizing: border-box;">Replicate_Do_Table:</span> <span class="hljs-label" style="box-sizing: border-box;">Replicate_Ignore_Table:</span> <span class="hljs-label" style="box-sizing: border-box;">Replicate_Wild_Do_Table:</span> <span class="hljs-label" style="box-sizing: border-box;">Replicate_Wild_Ignore_Table:</span> <span class="hljs-label" style="box-sizing: border-box;">Last_Errno:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span><span class="hljs-label" style="box-sizing: border-box;">Last_Error:</span> <span class="hljs-label" style="box-sizing: border-box;">Skip_Counter:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span><span class="hljs-label" style="box-sizing: border-box;">Exec_Master_Log_Pos:</span>                  <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">95990</span>            //<span class="hljs-label" style="box-sizing: border-box;">Relay_Log_Space:</span>                         <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">96489</span><span class="hljs-label" style="box-sizing: border-box;">Until_Condition:</span> None<span class="hljs-label" style="box-sizing: border-box;">Until_Log_File:</span> <span class="hljs-label" style="box-sizing: border-box;">Until_Log_Pos:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span><span class="hljs-label" style="box-sizing: border-box;">Master_SSL_Allowed:</span> No<span class="hljs-label" style="box-sizing: border-box;">Master_SSL_CA_File:</span> <span class="hljs-label" style="box-sizing: border-box;">Master_SSL_CA_Path:</span> <span class="hljs-label" style="box-sizing: border-box;">Master_SSL_Cert:</span> <span class="hljs-label" style="box-sizing: border-box;">Master_SSL_Cipher:</span> <span class="hljs-label" style="box-sizing: border-box;">Master_SSL_Key:</span> <span class="hljs-label" style="box-sizing: border-box;">Seconds_Behind_Master:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>                                            从服务器数据比主服务器延迟多少秒，一般是<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span><span class="hljs-label" style="box-sizing: border-box;">Master_SSL_Verify_Server_Cert:</span> No<span class="hljs-label" style="box-sizing: border-box;">Last_IO_Errno:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span><span class="hljs-label" style="box-sizing: border-box;">Last_IO_Error:</span> <span class="hljs-label" style="box-sizing: border-box;">Last_SQL_Errno:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span><span class="hljs-label" style="box-sizing: border-box;">Last_SQL_Error:</span> <span class="hljs-label" style="box-sizing: border-box;">Replicate_Ignore_Server_Ids:</span> <span class="hljs-label" style="box-sizing: border-box;">Master_Server_Id:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">9</span><span class="hljs-label" style="box-sizing: border-box;">Master_UUID:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7</span>d911e39-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>a98-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">11e5</span>-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>b9e-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">000</span>c29168ea6<span class="hljs-label" style="box-sizing: border-box;">Master_Info_File:</span> /usr/local/mysql/var/master<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.info</span>            --当前配置主库的文件<span class="hljs-label" style="box-sizing: border-box;">SQL_Delay:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span><span class="hljs-label" style="box-sizing: border-box;">SQL_Remaining_Delay:</span> NULL<span class="hljs-label" style="box-sizing: border-box;">Slave_SQL_Running_State:</span> Slave has read all relay log<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">; waiting for the slave I/O thread to update it</span><span class="hljs-label" style="box-sizing: border-box;">Master_Retry_Count:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">86400</span><span class="hljs-label" style="box-sizing: border-box;">Master_Bind:</span> <span class="hljs-label" style="box-sizing: border-box;">Last_IO_Error_Timestamp:</span> <span class="hljs-label" style="box-sizing: border-box;">Last_SQL_Error_Timestamp:</span> <span class="hljs-label" style="box-sizing: border-box;">Master_SSL_Crl:</span> <span class="hljs-label" style="box-sizing: border-box;">Master_SSL_Crlpath:</span> <span class="hljs-label" style="box-sizing: border-box;">Retrieved_Gtid_Set:</span> <span class="hljs-label" style="box-sizing: border-box;">Executed_Gtid_Set:</span> <span class="hljs-label" style="box-sizing: border-box;">Auto_Position:</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> row <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">in</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">sec</span>)<span class="hljs-label" style="box-sizing: border-box;">ERROR:</span> No query specified</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li><li style="box-sizing: border-box; padding: 0px 5px;">40</li><li style="box-sizing: border-box; padding: 0px 5px;">41</li><li style="box-sizing: border-box; padding: 0px 5px;">42</li><li style="box-sizing: border-box; padding: 0px 5px;">43</li><li style="box-sizing: border-box; padding: 0px 5px;">44</li><li style="box-sizing: border-box; padding: 0px 5px;">45</li><li style="box-sizing: border-box; padding: 0px 5px;">46</li><li style="box-sizing: border-box; padding: 0px 5px;">47</li><li style="box-sizing: border-box; padding: 0px 5px;">48</li><li style="box-sizing: border-box; padding: 0px 5px;">49</li><li style="box-sizing: border-box; padding: 0px 5px;">50</li><li style="box-sizing: border-box; padding: 0px 5px;">51</li><li style="box-sizing: border-box; padding: 0px 5px;">52</li><li style="box-sizing: border-box; padding: 0px 5px;">53</li><li style="box-sizing: border-box; padding: 0px 5px;">54</li><li style="box-sizing: border-box; padding: 0px 5px;">55</li><li style="box-sizing: border-box; padding: 0px 5px;">56</li><li style="box-sizing: border-box; padding: 0px 5px;">57</li><li style="box-sizing: border-box; padding: 0px 5px;">58</li><li style="box-sizing: border-box; padding: 0px 5px;">59</li><li style="box-sizing: border-box; padding: 0px 5px;">60</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li><li style="box-sizing: border-box; padding: 0px 5px;">40</li><li style="box-sizing: border-box; padding: 0px 5px;">41</li><li style="box-sizing: border-box; padding: 0px 5px;">42</li><li style="box-sizing: border-box; padding: 0px 5px;">43</li><li style="box-sizing: border-box; padding: 0px 5px;">44</li><li style="box-sizing: border-box; padding: 0px 5px;">45</li><li style="box-sizing: border-box; padding: 0px 5px;">46</li><li style="box-sizing: border-box; padding: 0px 5px;">47</li><li style="box-sizing: border-box; padding: 0px 5px;">48</li><li style="box-sizing: border-box; padding: 0px 5px;">49</li><li style="box-sizing: border-box; padding: 0px 5px;">50</li><li style="box-sizing: border-box; padding: 0px 5px;">51</li><li style="box-sizing: border-box; padding: 0px 5px;">52</li><li style="box-sizing: border-box; padding: 0px 5px;">53</li><li style="box-sizing: border-box; padding: 0px 5px;">54</li><li style="box-sizing: border-box; padding: 0px 5px;">55</li><li style="box-sizing: border-box; padding: 0px 5px;">56</li><li style="box-sizing: border-box; padding: 0px 5px;">57</li><li style="box-sizing: border-box; padding: 0px 5px;">58</li><li style="box-sizing: border-box; padding: 0px 5px;">59</li><li style="box-sizing: border-box; padding: 0px 5px;">60</li></ul>

查看日志，在linux中：

<code class="hljs lasso has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">/usr/<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">local</span>/mysql/bin/mysqlbinlog /usr/<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">local</span>/mysql/<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">var</span>/mysql<span class="hljs-attribute" style="box-sizing: border-box;">-bin</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.000050</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

通过日志还原：

通过时间还原：
–start-datetime=”还原数据的起始时间”
–stop-datetime=”还原数据的结束时间”
mysqlbinlog –start-datetime=“时间” 日志文件路径 | mysql -u用户 -p密码 –从规定的起始时间还原到现在
mysqlbinlog –stop-datetime=“时间” 日志文件路径 | mysql -u用户 -p密码 –从最开始还原到规定的结束时间
mysqlbinlog –start-datetime=“时间” –stop-datetime=“时间” 日志文件路径 | mysql -u用户 -p密码 –指定时间段

<code class="hljs scala has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">[root<span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@localhost</span> bin]# /usr/local/mysql/bin/mysqlbinlog --stop-datetime=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'2015-10-19 17:30:0'</span> /usr/local/mysql/<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">var</span>/mysql-bin<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.000001</span> | mysql -uroot -proot</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

通过位置还原：
–start-position=”还原数据的起始位置”
–stop-position=”还原数据的结束位置”
mysqlbinlog –start-position=“位置” 日志文件路径 | mysql -u用户 -p密码 –从规定的起始位置还原到现在
mysqlbinlog –stop-position=“位置” 日志文件路径 | mysql -u用户 -p密码 –从最开始还原到规定的结束位置
mysqlbinlog –start-position=“位置” –stop-position=“位置” 日志文件路径 | mysql -u用户 -p密码 –指定位置段

0 0