Loading... <h1 id="mysql主从复制半同步异步" style="font-size: 2.6em;margin: 1.2em 0px 0.6em;font-family: inherit;line-height: 1.1;color: inherit"> <span style="font-family: 楷体, 楷体_GB2312, SimKai">MySQL主从复制:半同步、异步</span></h1> <hr /> <ul class=" list-paddingleft-2"> <li> <p>前言</p> </li> <li> <p>如何对MySQL进行扩展?</p> </li> <li> <p>MySQL Replication WorkFlow</p> </li> <li> <p>MySQL主从复制模式</p> </li> <li> <p>实战演练</p> </li> <ul class=" list-paddingleft-2"> <li> <p>MySQL异步复制实现</p> </li> <li> <p>MySQL半同步复制实现</p> </li> </ul> <li> <p>实验中的思考</p> </li> <li> <p>总结</p> </li> </ul> <p class="note-tags "><code>mysql</code></p> <ul class=" list-paddingleft-2"> <ul class=" list-paddingleft-2"></ul> </ul> <h2 id="前言" style="font-family: inherit;line-height: 1.1;color: inherit;margin: 1.2em 0px 0.6em;font-size: 2.15em">前言</h2> <blockquote><p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-family: 黑体, SimHei;font-size: 16px">本篇我们介绍</span><code><span style="font-family: 黑体, SimHei;font-size: 16px">MySQL Replication</span></code><span style="font-family: 黑体, SimHei;font-size: 16px">的相关内容, 我们首先介绍</span><code><span style="font-family: 黑体, SimHei;font-size: 16px">MySQL CLuster</span></code><span style="font-family: 黑体, SimHei;font-size: 16px">的实现原理和如何一步步构建一个</span><code><span style="font-family: 黑体, SimHei;font-size: 16px">MySQL Replication Cluster</span></code></p> <p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-family: 黑体, SimHei;font-size: 16px">看懂本文需要了解: </span><code><span style="font-family: 黑体, SimHei;font-size: 16px">MySQL基本操作</span></code><span style="font-family: 黑体, SimHei;font-size: 16px">,</span><code><span style="font-family: 黑体, SimHei;font-size: 16px">MySQL日志类型及其作用</span></code></p> </blockquote> <h2 id="如何对mysql进行扩展" style="font-family: inherit;line-height: 1.1;color: inherit;margin: 1.2em 0px 0.6em;font-size: 2.15em">如何对MySQL进行扩展?</h2> <blockquote><p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">大家之前应该了解; 在单台服务器性能不足时, 有两种方式进行扩展</span></p> <ul style="margin-bottom: 1.1em;line-height: 1.6" class=" list-paddingleft-2"> <li> <p><span style="font-size: 16px;font-family: 黑体, SimHei">Scale Up ; 垂直扩展</span></p> </li> <li> <p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">Scale Out ; 水平扩展</span></p> <p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei"><strong style="line-height: 1.6">垂直扩展</strong>: 指的是提升单台服务器的性能(硬件)来提升服务的性能 <br /><strong style="line-height: 1.6">水平扩展</strong>: 指的是添加服务器通过负载均衡的方式来分担单台服务器的负载, 从而提升服务的性能</span></p> </li> </ul> <p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">我们可以通过LVS, HAProxy, Nginx等软件实现一些服务的负载均衡, 但是如果要用到</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">上就会有一些问题, 如下</span></p> <ul style="margin-bottom: 1.1em;line-height: 1.6" class=" list-paddingleft-2"> <li> <p><span style="font-size: 16px;font-family: 黑体, SimHei">如何保证多台</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">服务器数据的一致性</span></p> </li> <li> <p><span style="font-size: 16px;font-family: 黑体, SimHei">如何保证多台服务器同时提交事务导致数据的完整性</span></p> </li> <li> <p><span style="font-size: 16px;font-family: 黑体, SimHei">如何保证一台服务器宕机时, 其的事务能够正常提交或ROLLBACK</span></p> </li> <li> <p><span style="font-size: 16px;font-family: 黑体, SimHei">…</span></p> </li> </ul> <p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">我们需要考虑的问题比扩展</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">WEB</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">服务多太多了, 毕竟大家都知道, <strong style="line-height: 1.6"><code>数据无价</code></strong>, 在很多重要场景中, <strong style="line-height: 1.6">数据不能有半点闪失</strong>, 但是</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">对这方面做得并不是很好, 而</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">Oracle</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">数据库在这方面特别的厉害, 所以众多的银行都采用</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">Oracle</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">数据库存储客户的账户信息等, 这么说, 难道我们就不用</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">了么? 这显然不可能, 我们首先来介绍一下</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL Replication Cluster</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">的几种同步数据方式</span></p> <ul style="line-height: 1.6" class=" list-paddingleft-2"> <li> <p><span style="font-size: 16px;font-family: 黑体, SimHei">Synchronous Replication <strong style="line-height: 1.6">同步复制</strong></span></p> </li> <li> <p><span style="font-size: 16px;font-family: 黑体, SimHei">Asynchronous Replication <strong style="line-height: 1.6">异步复制</strong></span></p> </li> <li> <p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">Semisynchronous Replication <strong style="line-height: 1.6">半同步复制</strong></span></p> <p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei"><strong style="line-height: 1.6">同步复制</strong>: 指的是客户端连接到</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL主服务器</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">写入一段数据, </span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL主服务器</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">同步给</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL从服务器</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">需要等待从服务器发出同步完成的响应才返回客户端OK, 这其中等待同步的过程是阻塞的, 如果有N台从服务器, 效率极低 <br /><strong style="line-height: 1.6">异步复制</strong>: 指的是客户端连接到</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL主服务器</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">写入一段数据, </span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL主服务器</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">将写入的数据发送给</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL从服务器</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">, 然后直接返回客户端OK, 可能从服务器的数据会和主服务不一致 <br /><strong style="line-height: 1.6">半同步复制</strong>:指的是客户端连接到</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL主服务器</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">写入一段数据, </span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL主服务器</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">只将数据同步复制给其中一台从服务器, 半同步复制给其他的从服务器, 来达到其中一台从服务器完全同步的效果</span></p> </li> </ul> </blockquote> <h2 id="mysql-replication-workflow" style="font-family: inherit;line-height: 1.1;color: inherit;margin: 1.2em 0px 0.6em;font-size: 2.15em">MySQL Replication WorkFlow</h2> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">Master/Slave工作流程</strong></p> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><img src="//cto.wang/usr/uploads/2016/07/20160703151258-57.png" title="1461826429539116.png" alt="blob.png" /></p> <blockquote><p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">建立主从关系后, 主服务器如果有数据修改之后, </span><code><span style="font-size: 16px;font-family: 黑体, SimHei">BINLOG</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">会更新, 从服务通过</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">IO-Thread</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">读取主服务器的</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">BINLOG</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">到本地的</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">Relay Log</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">, 再通过</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">SQL-Thread</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">对其进行重放(replay), 从而同步到本地</span></p> </blockquote> <h2 id="mysql主从复制模式" style="font-family: inherit;line-height: 1.1;color: inherit;margin: 1.2em 0px 0.6em;font-size: 2.15em">MySQL主从复制模式</h2> <blockquote><p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">在不同的业务模型中我们可以采用不同的</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">主从复制架构, 一般分为以下两种</span></p> <ul style="line-height: 1.6" class=" list-paddingleft-2"> <li> <p><span style="font-size: 16px;font-family: 黑体, SimHei">Master/Slave</span></p> </li> <li> <p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">Master/Master</span></p> <p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei"><strong style="line-height: 1.6">Master/Slave</strong>: 指的是一主多从模式, 这种模式下可以有效的分担读请求, 但是写请求并不能完成负载分担、从节点可能数据不一致, 并且如果</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">Master</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">宕机了客户端就无法进行写操作了, 还是有很多问题的 <br /><strong style="line-height: 1.6">Master/Master</strong>: 指的是多主模式, 这种模式中的多台</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">服务器都是</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">Master</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">, 也就意味着都可以进行读写操作, 但是有着更为严重的问题, 多个客户端同时写入数据时由于复制延迟可能到导致数据冲突等严重问题</span></p> </li> </ul> </blockquote> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">常用的架构图</strong></p> <blockquote><p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">我们可以使用keepalived实现Master高可用, 并且使用半同步模式实现数据的完全同步</span></p> </blockquote> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><img src="//cto.wang/usr/uploads/2016/07/20160703151258-98.png" title="1461826433829512.png" alt="blob.png" /></p> <blockquote><p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">上面那种方式太占用Master的带宽, 我们可以让一台Slave扮演为Master, 为其他Slave同步数据</span></p> </blockquote> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><img src="//cto.wang/usr/uploads/2016/07/20160703151259-89.png" title="1461826437995532.png" alt="blob.png" /></p> <h2 id="实战演练" style="font-family: inherit;line-height: 1.1;color: inherit;margin: 1.2em 0px 0.6em;font-size: 2.15em">实战演练</h2> <h3 id="mysql异步复制实现" style="font-family: inherit;line-height: 1.6;color: inherit;margin: 1.2em 0px 0.6em;font-size: 1.7em">MySQL异步复制实现</h3> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">实验拓扑</strong></p> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><img src="//cto.wang/usr/uploads/2016/07/20160703151259-97.png" title="1461826460636096.png" alt="blob.png" /></p> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">环境部署</strong></p> <blockquote><p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">我们需要在各台服务器上安装</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">, 这里使用的是rpm包安装, 版本为5.1</span></p> </blockquote> <pre class="prettyprint hljs-light"><code class="hljs autoit">[root<span class="hljs-constant">@node1</span> ~]<span class="hljs-preprocessor"># yum install mysql-server -y</span><br />[root<span class="hljs-constant">@node2</span> ~]<span class="hljs-preprocessor"># yum install mysql-server -y</span><br />[root<span class="hljs-constant">@node3</span> ~]<span class="hljs-preprocessor"># yum install mysql-server -y</span><br /></code></pre> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">配置文件</strong></p> <pre class="prettyprint hljs-light"><code class="hljs stata">node1配置文件(master)<br /><br /> [mysqld]<br /> datadir=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/lib/mysql<br /> socket=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/lib/mysql/mysql.sock<br /> user=mysql<br /> # Disabling symbolic-links is recommended to prevent assorted security risks<br /> symbolic-links=0<br /> innodb_file_per_table = 1<br /> log_bin=master-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span> #开启二进制日志<br /> log_bin_index=1<br /> server_id=1 #设置serverid<br /><br /> [mysqld_safe]<br /> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span>-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">error</span>=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span>/mysqld.<span class="hljs-literal">log</span><br /> pid-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">file</span>=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">run</span>/mysqld/mysqld.pid<br /><br />node2配置文件(slave1)<br /><br /> [mysqld]<br /> datadir=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/lib/mysql<br /> socket=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/lib/mysql/mysql.sock<br /> user=mysql<br /> # Disabling symbolic-links is recommended to prevent assorted security risks<br /> symbolic-links=0<br /> relay-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span>=relay-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span> #开启relay日志<br /> innodb_file_per_table = 1<br /> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">read</span>-only = 1 #设置只读<br /> server_id = 2 #设置serverid<br /><br /> [mysqld_safe]<br /> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span>-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">error</span>=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span>/mysqld.<span class="hljs-literal">log</span><br /> pid-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">file</span>=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">run</span>/mysqld/mysqld.pid<br /><br />node3配置文件(slave2)<br /><br /> [mysqld]<br /> datadir=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/lib/mysql<br /> socket=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/lib/mysql/mysql.sock<br /> user=mysql<br /> # Disabling symbolic-links is recommended to prevent assorted security risks<br /> symbolic-links=0<br /> relay-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span>=relay-<span class="hljs-literal">log</span><br /> innodb_file_per_table = 1<br /> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">read</span>-only = 1<br /> server_id = 3<br /><br /> [mysqld_safe]<br /> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span>-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">error</span>=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">log</span>/mysqld.<span class="hljs-literal">log</span><br /> pid-<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">file</span>=/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">var</span>/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">run</span>/mysqld/mysqld.pid<br /><br /><br />##启动mysql<br />##注意: serverid一定不能相同<br /></code></pre> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">配置Master</strong></p> <blockquote><p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">Slave节点进行同步需要通过一个特定的用户进行, 所以我们需要创建一个用户并赋予</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">REPLICATION SLAVE, REPLICATION CLIENT</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">权限</span></p> </blockquote> <pre class="prettyprint hljs-light"><code class="hljs asciidoc">mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON <span class="hljs-strong" style="line-height: 1.6;font-weight: bold">*.*</span> TO <span class="hljs-emphasis" style="line-height: 1.6;font-style: italic">'rpuser'</span>@<span class="hljs-emphasis" style="line-height: 1.6;font-style: italic">'%'</span> IDENTIFIED BY <span class="hljs-emphasis" style="line-height: 1.6;font-style: italic">'passwd'</span>;<br />Query OK, 0 rows affected (0.00 sec)<br /><br />mysql> FLUSH PRIVILEGES;<br />Query OK, 0 rows affected (0.00 sec)<br /><br /><span class="hljs-header">mysql> SHOW MASTER STATUS; 我们要记录下pos的数值<br />+-------------------+----------+--------------+------------------+</span><br /><span class="hljs-header">| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |<br />+-------------------+----------+--------------+------------------+</span><br /><span class="hljs-header">| master-log.000005 | 523 | | |<br />+-------------------+----------+--------------+------------------+</span><br />1 row in set (0.00 sec)<br /><br /></code></pre> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">配置Slave(1)</strong></p> <pre class="prettyprint hljs-light"><code class="hljs coffeescript">mysql> CHANGE MASTER TO<br /> -> MASTER_HOST=<span class="hljs-string">'172.16.1.2'</span>,<br /> <span class="hljs-function" style="line-height: 1.6">-></span> MASTER_USER=<span class="hljs-string">'rpuser'</span>,<br /> <span class="hljs-function" style="line-height: 1.6">-></span> MASTER_PASSWORD=<span class="hljs-string">'passwd'</span>,<br /> <span class="hljs-function" style="line-height: 1.6">-></span> MASTER_LOG_FILE=<span class="hljs-string">'master-log.000005'</span>,<br /> <span class="hljs-function" style="line-height: 1.6">-></span> MASTER_LOG_POS=<span class="hljs-number">523</span>;<br />Query OK, <span class="hljs-number">0</span> rows affected (<span class="hljs-number">0.01</span> sec<br /><br />mysql> SHOW SLAVE STATUS\G; <span class="hljs-comment">#查看相应信息</span><br /><span class="hljs-comment">######</span><span class="hljs-comment">###省略###</span><span class="hljs-comment">######</span><span class="hljs-comment">######</span><span class="hljs-comment">###<br />Master_Log_File: master-log.000005<br />Read_Master_Log_Pos: 523<br />Relay_Log_File: relay-log.000001<br />Relay_Log_Pos: 4<br />Relay_Master_Log_File: master-log.000005<br />Slave_IO_Running: No #IO-thread没有启动<br />Slave_SQL_Running: No #SQL-thread没有启动<br />###</span><span class="hljs-comment">######</span>省略<span class="hljs-comment">######</span><span class="hljs-comment">######</span><span class="hljs-comment">######</span><br /><br />mysql> START SLAVE; <span class="hljs-comment">#启动sql-thread和io-thred</span><br /><br />mysql> SHOW SLAVE STATUS\G; <span class="hljs-comment">#查看相应信息 </span><br />Query OK, <span class="hljs-number">0</span> rows affected (<span class="hljs-number">0.00</span> sec)<br /><span class="hljs-comment">######</span><span class="hljs-comment">###省略###</span><span class="hljs-comment">######</span><span class="hljs-comment">######</span><span class="hljs-comment">###<br />Master_Log_File: master-log.000005<br />Read_Master_Log_Pos: 523<br />Relay_Log_File: relay-log.000002<br />Relay_Log_Pos: 252<br />Relay_Master_Log_File: master-log.000005<br />Slave_IO_Running: Yes #IO-thread启动<br />Slave_SQL_Running: Yes #SQL-thread启动<br />###</span><span class="hljs-comment">######</span>省略<span class="hljs-comment">######</span><span class="hljs-comment">######</span><span class="hljs-comment">######</span><br /></code></pre> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">配置slave(2)</strong></p> <blockquote><p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">过程和配置slave(1)相同, 不做演示</span></p> </blockquote> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">测试主从复制</strong></p> <pre class="prettyprint hljs-light"><code class="language-sql hljs"><br />##在主服务器上创建数据库和表<br /><br />[root@node1 ~]# mysql<br /><br />mysql> <span class="hljs-operator" style="line-height: 1.6"><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">SHOW</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">DATABASES</span>;</span><br />+<span class="hljs-comment">--------------------+</span><br />| Database |<br />+<span class="hljs-comment">--------------------+</span><br />| information_schema |<br />| mysql |<br />| test |<br />+<span class="hljs-comment">--------------------+</span><br />3 rows in <span class="hljs-operator" style="line-height: 1.6"><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">set</span> (<span class="hljs-number">0.00</span> sec)<br /><br />mysql> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">CREATE</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">DATABASE</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">replication</span>;</span><br />Query OK, 1 row affected (0.00 sec)<br /><br />mysql> <span class="hljs-operator" style="line-height: 1.6"><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">USE</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">replication</span>;</span><br />Database changed<br />mysql> <span class="hljs-operator" style="line-height: 1.6"><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">CREATE</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">TABLE</span> t1 (<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">id</span> <span class="hljs-built_in" style="line-height: 1.6;font-weight: bold">int</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">unsigned</span> auto_increment primary <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">key</span>, <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">name</span> <span class="hljs-built_in" style="line-height: 1.6;font-weight: bold">char</span>(<span class="hljs-number">30</span>));</span><br />Query OK, 0 rows affected (0.01 sec)<br /><br />mysql> DESC t1;<br />+<span class="hljs-comment">-------+------------------+------+-----+---------+----------------+</span><br />| Field | Type | Null | Key | Default | Extra |<br />+<span class="hljs-comment">-------+------------------+------+-----+---------+----------------+</span><br />| id | int(10) unsigned | NO | PRI | NULL | auto_increment |<br />| name | char(30) | YES | | NULL | |<br />+<span class="hljs-comment">-------+------------------+------+-----+---------+----------------+</span><br />2 rows in <span class="hljs-operator" style="line-height: 1.6"><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">set</span> (<span class="hljs-number">0.00</span> sec)<br /><br />mysql> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">SHOW</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">MASTER</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">STATUS</span>;</span><br />+<span class="hljs-comment">-------------------+----------+--------------+------------------+</span><br />| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |<br />+<span class="hljs-comment">-------------------+----------+--------------+------------------+</span><br />| master-log.000005 | 765 | | |<br />+<span class="hljs-comment">-------------------+----------+--------------+------------------+</span><br />1 row in <span class="hljs-operator" style="line-height: 1.6"><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">set</span> (<span class="hljs-number">0.00</span> sec)</span><br /></code></pre> <pre class="prettyprint hljs-light"><code class="language-sql hljs">##在slave服务器测试<br /><br />[root@node2 ~]# mysql<br /><br />mysql> <span class="hljs-operator" style="line-height: 1.6"><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">SHOW</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">DATABASES</span>;</span><br />+<span class="hljs-comment">--------------------+</span><br />| Database |<br />+<span class="hljs-comment">--------------------+</span><br />| information_schema |<br />| mysql |<br />| replication |<br />| test |<br />+<span class="hljs-comment">--------------------+</span><br />4 rows in <span class="hljs-operator" style="line-height: 1.6"><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">set</span> (<span class="hljs-number">0.00</span> sec)<br /><br />mysql> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">use</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">replication</span>;</span><br />Reading table information for completion of table and column names<br />You can turn off this feature to get a quicker startup with -A<br /><br />Database changed<br />mysql> DESC t1;<br />+<span class="hljs-comment">-------+------------------+------+-----+---------+----------------+</span><br />| Field | Type | Null | Key | Default | Extra |<br />+<span class="hljs-comment">-------+------------------+------+-----+---------+----------------+</span><br />| id | int(10) unsigned | NO | PRI | NULL | auto_increment |<br />| name | char(30) | YES | | NULL | |<br />+<span class="hljs-comment">-------+------------------+------+-----+---------+----------------+</span><br />2 rows in <span class="hljs-operator" style="line-height: 1.6"><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">set</span> (<span class="hljs-number">0.00</span> sec)<br /><br />mysql> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">SHOW</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">SLAVE</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">STATUS</span>\<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">G</span>;</span><br /><br />#########省略##################<br />Master_Log_File: master-log.000005<br />Read_Master_Log_Pos: 765 #已经同步<br />Relay_Log_File: relay-log.000002<br />Relay_Log_Pos: 494<br />Relay_Master_Log_File: master-log.000005<br />Slave_IO_Running: Yes<br />Slave_SQL_Running: Yes<br />#########省略##################<br /><br /></code></pre> <h3 id="mysql半同步复制实现" style="font-family: inherit;line-height: 1.6;color: inherit;margin: 1.2em 0px 0.6em;font-size: 1.7em">MySQL半同步复制实现</h3> <blockquote><p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">由于</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">半同步复制在</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">5.5以后才以插件的形式进行提供, 所以这里我们的</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MySQL</span></code><span style="font-size: 16px;font-family: 黑体, SimHei">要换成5.5版本的</span><code><span style="font-size: 16px;font-family: 黑体, SimHei">MariaDB</span></code></p> <p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">由于很多步骤和上面重复,我就不写出来了,先配置成M/S然后再按照我下面操作</span></p> </blockquote> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">实验拓扑</strong></p> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><img src="//cto.wang/usr/uploads/2016/07/20160703151300-89.png" title="1461826470328787.png" alt="blob.png" /></p> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">配置master</strong></p> <pre class="prettyprint hljs-light"><code class="hljs stata">半同步的插件在/usr/lib64/mysql/<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">plugin</span>下, master用的semisync_master.<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">so</span>,slave用的semisync_slave.<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">so</span>,<br /><br />MariaDB [(none)]> INSTALL <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">PLUGIN</span> rpl_semi_sync_master SONAME 'semisync_master.<span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">so</span>';<br /><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">Query</span> OK, 0 rows affected (0.00 sec)<br /><br />MariaDB [(none)]> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">SET</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">GLOBAL</span> rpl_semi_sync_master_enabled = 1;<br /><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">Query</span> OK, 0 rows affected (0.00 sec)<br /><br />MariaDB [(none)]> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">SET</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">GLOBAL</span> rpl_semi_sync_master_timeout = 2000; #设置超时时间为2S<br /><span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">Query</span> OK, 0 rows affected (0.00 sec)<br /><br /></code></pre> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">配置slave</strong></p> <pre class="prettyprint hljs-light"><code class="hljs nginx"><span class="hljs-title" style="line-height: 1.6;font-weight: bold">MariaDB</span> [(<span class="hljs-built_in">none</span>)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME <span class="hljs-string">'semisync_slave.so'</span>;<br /><span class="hljs-title" style="line-height: 1.6;font-weight: bold">Query</span> OK, <span class="hljs-number">0</span> rows affected (<span class="hljs-number">0</span>.<span class="hljs-number">00</span> sec)<br /><br />MariaDB [(<span class="hljs-built_in">none</span>)]> SET GLOBAL rpl_semi_sync_slave_enabled=<span class="hljs-number">1</span>;<br /><span class="hljs-title" style="line-height: 1.6;font-weight: bold">Query</span> OK, <span class="hljs-number">0</span> rows affected (<span class="hljs-number">0</span>.<span class="hljs-number">00</span> sec)<br /></code></pre> <p style="margin-top: 0px;margin-bottom: 1.1em;line-height: 1.6"><strong style="line-height: 1.6">验证</strong></p> <pre class="prettyprint hljs-light"><code class="hljs avrasm">因为在我的环境中,即使是半同步复制,也是直接就完成,看不出效果,所以我们故意将slave节点关闭<br />[root@node2 ~]<span class="hljs-preprocessor"># service mysql stop</span><br /><br /><br /><br /><span class="hljs-preprocessor">##master创建数据库</span><br />MariaDB [(none)]> CREATE DATABASE TEST3<span class="hljs-comment">;</span><br />Query OK, <span class="hljs-number">1</span> row affected (<span class="hljs-number">2.00</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">sec</span>) <span class="hljs-preprocessor">#等待2s, 超时不再等待,直接创建</span><br /><br />MariaDB [(none)]> CREATE DATABASE TEST4<span class="hljs-comment">;</span><br />Query OK, <span class="hljs-number">1</span> row affected (<span class="hljs-number">0.00</span> <span class="hljs-keyword" style="line-height: 1.6;font-weight: bold">sec</span>)<br /><br /><br /><span class="hljs-preprocessor">##完成,这个可能不是特别直观,但是由于我这边环境实在做不出效果,望大家理解</span><br /></code></pre> <h2 id="实验中的思考" style="font-family: inherit;line-height: 1.1;color: inherit;margin: 1.2em 0px 0.6em;font-size: 2.15em">实验中的思考</h2> <blockquote><ul style="line-height: 1.6" class=" list-paddingleft-2"> <li> <p><span style="font-size: 16px;font-family: 黑体, SimHei">如果主从服务器数据相差较大, 最好先使用主服务器的二进制日志在从服务器上replay一篇, 然后再进行同步</span></p> </li> </ul> </blockquote> <h2 id="总结" style="font-family: inherit;line-height: 1.1;color: inherit;margin: 1.2em 0px 0.6em;font-size: 2.15em">总结</h2> <blockquote><p style="margin-top: 0px;margin-bottom: 1.1em;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">这篇其实还打算写SSL复制的,但是因为时间比较紧,就没有写了,总体来说不是特别的满意,有很多地方没有说明白,还望大家谅解。</span></p> <p style="margin-top: 0px;margin-bottom: 0px;font-size: 1em;line-height: 1.6"><span style="font-size: 16px;font-family: 黑体, SimHei">作者水平很低, 如果有错误及时指出, 如果你觉得本文写的好请点一波赞~(≧▽≦)/~ <br />作者: AnyISaIln QQ: 1449472454 <br />感谢: MageEdu</span></p> </blockquote> <p></p> 最后修改:2021 年 12 月 10 日 10 : 53 AM © 允许规范转载 赞赏 如果觉得我的文章对你有用,请随意赞赏 赞赏作者 支付宝微信