MHA高可用架构与Atlas读写分离
1.1 MHA简介
1.1.1 MHA软件介绍
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在10~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
MHA能够在较短的时间内实现自动故障检测和故障转移，通常在10-30秒以内;在复制 框架中，MHA能够很好地解决复制过程中的数据一致性问题，由于不需要在现有的 replication中添加额外的服务器，仅需要一个manager节点，而一个Manager能管理多套复制，所以能大大地节约服务器的数量;另外，安装简单，无性能损耗，以及不需要修改现 有的复制部署也是它的优势之处。
MHA还提供在线主库切换的功能，能够安全地切换当前运行的主库到一个新的主库中 (通过将从库提升为主库)，大概**0.5-2秒**内即可完成。
该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。
在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制，可以大大降低数据丢失的风险。
MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。
目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，因为至少需要三台服务器，出于机器成本的考虑，淘宝也在该基础上进行了改造，目前淘宝_TMHA_已经支持一主一从。
1.1.2 MHA工作原理
<td width="83%">
<p>
<strong>工作原理说明：</strong>
</p>
<div>
<blockquote>
<p class="a">
</p>
<div>
<p class="a">
1、保存master上的所有binlog事件
</p>
<p class="a">
2、找到含有最新binlog位置点的slave
</p>
<p class="a">
3、通过中继日志将数据恢复到其他的slave
</p>
<p class="a">
4、将包含最新binlog位置点的slave提升为master
</p>
<p class="a">
5、将其他从库slave指向新的master原slave01 并开启主从复制
</p>
<p class="a">
6、将保存下来的binlog恢复到新的master上
</p>
</div>
<p>
</p>
</blockquote>
</div>
</td>
1、监控所有node节点MHA功能说明：
<p class="a4">
前提是必须有三个节点存在，并且有两个从库
</p>
<p class="a4">
(1)选主前提，按照配置文件的顺序进行，但是如果此节点后主库100M以上relay-log 就不会选
</p>
<p class="a4">
(2)如果你设置了权重，总会切换带此节点；一般在多地多中心的情况下，一般会把权重设置在本地节点。
</p>
<p class="a4">
(3)选择s1为新主
</p>
<p class="a4">
(4)保存主库binlog日志
</p>
<p class="a4">
3、重新构建主从
</p>
<p class="a4">
(1)将有问题的节点剔除MHA
</p>
<p class="a4">
进行第一阶段数据补偿，S2缺失部分补全90
</p>
<p class="a4">
(2)s1切换角色为新主，将s2指向新主S1
</p>
<p class="a4">
s2 change master to s1
</p>
<p class="a4">
(3) 第二阶段数据补偿
</p>
<p class="a4">
将保存过来的新主和原有主缺失部分的binlog，应用到新主。
</p>
<p class="a4">
(4)虚拟IP漂移到新主，对应用透明无感知
</p>
<p class="a4">
(5)通知管理员故障切换
</p>
1.1.3 MHA高可用架构图
1.1.4 MHA工具介绍
MHA软件由两部分组成，Manager工具包和Node工具包，具体的说明如下:
Manager工具包主要包括以下几个工具：
Node工具包主要包括以下几个工具：
1.1.5 MHA的优点
1、自动故障转移
2、主库崩溃不存在数据不一致的情况
3、不需要对当前的mysql环境做重大修改
4、不需要添加额外的服务器
5、性能优秀，可以工作再半同步和异步复制框架
6、只要replication支持的存储引擎mha都支持
1.2 环境说明
在本次的实验中，共需要用到三台主机，系统、软件说明如下。
1.2.1 系统环境说明
db01主机（master）
db02主机（slave1）
<p>
db03主机（slave1，MHA Manages、Atlas节点）
</p>
<div class="cnblogs_code" onclick="cnblogs_code_show('128d5d81-bb1c-4450-8822-1ca59acbfcb4')">
<img id="code_img_closed_128d5d81-bb1c-4450-8822-1ca59acbfcb4" class="code_img_closed" src="https://clsn.io/wp-content/uploads/2018/03/ContractedBlock-4.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /><img id="code_img_opened_128d5d81-bb1c-4450-8822-1ca59acbfcb4" class="code_img_opened" style="display: none;" onclick="cnblogs_code_hide('128d5d81-bb1c-4450-8822-1ca59acbfcb4',event)" data-original="https://clsn.io/wp-content/uploads/2018/03/ExpandedBlockStart-4.gif" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /></p>
<div id="cnblogs_code_open_128d5d81-bb1c-4450-8822-1ca59acbfcb4" class="cnblogs_code_hide">
<pre><span style="color: #008080;"> 1</span> [root@db03 ~]# <span style="color: #0000ff;">cat</span> /etc/redhat-<span style="color: #000000;">release
2 CentOS release 6.9 (Final) 3 [root@db03 ~]# uname -r 4 2.6.32-696.el6.x86_64 5 [root@db03 ~]# /etc/init.d/iptables status 6 iptables: Firewall is not running. 7 [root@db03 ~]# getenforce 8 Disabled 9 [root@db03 ~]# hostname -I 10 10.0.0.53 172.16.1.53
<p>
<span class="cnblogs_code_collapse">View Code db03主机环境说明 </span></div>
<h3>
<span id="122_mysql">1.2.2 mysql软件说明</span>
</h3>
<p>
三台服务器上都全新安装mysql 5.6.36 ：
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db01 ~]# mysql --<span style="color: #000000;">version
mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper
<p>
关于mysql数据库具体的安装方法参考：<a href="/wp-content/themes/clsn-003/inc/go.php?url=http://www.cnblogs.com/clsn/p/8038964.html#_label3" target="_blank">http://www.cnblogs.com/clsn/p/8038964.html#_label3</a>
</p>
</div>
<h2>
<span id="13_GTID">1.3 基于GTID的主从复制配置</span>
</h2>
<h3>
<span id="131">1.3.1 先决条件</span>
</h3>
<p>
? 主库和从库都要开启binlog
</p>
<p>
? 主库和从库server-id必须不同
</p>
<p>
? 要有主从复制用户
</p>
<h3>
<span id="132">1.3.2 配置主从复制</span>
</h3>
<p>
<strong>db01 my.cnf</strong><strong>文件</strong>
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db01 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> cat /etc/my.cnf</span>
[mysqld] basedir=/application/mysql datadir=/application/mysql/data socket=/tmp/mysql.sock log-error=/var/log/mysql.log log-bin=/data/mysql/mysql-bin binlog_format=row secure-file-priv=/tmp server-id=51 skip-name-resolve # 跳过域名解析 gtid-mode=on # 启用gtid类型，否则就是普通的复制架构 enforce-gtid-consistency=true #强制GTID的一致性 log-slave-updates=1 # slave更新是否记入日志（5.6必须的） relay_log_purge = 0 [mysql] socket=/tmp/mysql.sock
<p>
<strong>db02 my.cnf</strong><strong>文件</strong>
</p>
<div>
<div class="cnblogs_code" onclick="cnblogs_code_show('c5a0cfe9-93a3-44b6-ab18-bdcd7a33e080')">
<img id="code_img_closed_c5a0cfe9-93a3-44b6-ab18-bdcd7a33e080" class="code_img_closed" src="https://clsn.io/wp-content/uploads/2018/03/ContractedBlock-4.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /><img id="code_img_opened_c5a0cfe9-93a3-44b6-ab18-bdcd7a33e080" class="code_img_opened" style="display: none;" onclick="cnblogs_code_hide('c5a0cfe9-93a3-44b6-ab18-bdcd7a33e080',event)" data-original="https://clsn.io/wp-content/uploads/2018/03/ExpandedBlockStart-4.gif" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /></p>
<div id="cnblogs_code_open_c5a0cfe9-93a3-44b6-ab18-bdcd7a33e080" class="cnblogs_code_hide">
<pre><span style="color: #008080;"> 1</span> [root@db02 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> cat /etc/my.cnf</span>
2 [mysqld] 3 basedir=/application/mysql 4 datadir=/application/mysql/data 5 socket=/tmp/mysql.sock 6 log-error=/var/log/mysql.log 7 log-bin=/data/mysql/mysql-bin 8 binlog_format=row 9 secure-file-priv=/tmp 10 server-id=52 11 skip-name-resolve 12 gtid-mode=on 13 enforce-gtid-consistency=true 14 log-slave-updates=1 15 relay_log_purge = 0 16 [mysql] 17 socket=/tmp/mysql.sock
<p>
<span class="cnblogs_code_collapse">View Code <strong>db02 my.cnf</strong><strong>文件</strong> </span></div> </div>
<p>
<strong>db03 my.cnf</strong><strong>文件</strong>
</p>
<div>
<div class="cnblogs_code" onclick="cnblogs_code_show('c4a71fe7-ce23-4df3-8dc3-c688a7def1b6')">
<img id="code_img_closed_c4a71fe7-ce23-4df3-8dc3-c688a7def1b6" class="code_img_closed" src="https://clsn.io/wp-content/uploads/2018/03/ContractedBlock-4.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /><img id="code_img_opened_c4a71fe7-ce23-4df3-8dc3-c688a7def1b6" class="code_img_opened" style="display: none;" onclick="cnblogs_code_hide('c4a71fe7-ce23-4df3-8dc3-c688a7def1b6',event)" data-original="https://clsn.io/wp-content/uploads/2018/03/ExpandedBlockStart-4.gif" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /></p>
<div id="cnblogs_code_open_c4a71fe7-ce23-4df3-8dc3-c688a7def1b6" class="cnblogs_code_hide">
<pre><span style="color: #008080;"> 1</span> [root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> cat /etc/my.cnf</span>
2 [mysqld] 3 basedir=/application/mysql 4 datadir=/application/mysql/data 5 socket=/tmp/mysql.sock 6 log-error=/var/log/mysql.log 7 log-bin=/data/mysql/mysql-bin 8 binlog_format=row 9 secure-file-priv=/tmp 10 server-id=53 11 skip-name-resolve 12 gtid-mode=on 13 enforce-gtid-consistency=true 14 log-slave-updates=1 15 relay_log_purge = 0 16 skip-name-resolve 17 [mysql] 18 socket=/tmp/mysql.sock
<p>
<span class="cnblogs_code_collapse">View Code db03 my.cnf文件</span></div> </div>
<p>
<span style="background-color: #ffff00;">创建复制用户 (51作为主节点，52、53为从)</span>
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">REPLICATION</span> SLAVE <span style="color: #0000ff;">ON</span> <span style="color: #808080;">*</span>.<span style="color: #808080;">*</span> <span style="color: #0000ff;">TO</span> repl@<span style="color: #ff0000;">'</span><span style="color: #ff0000;">10.0.0.%</span><span style="color: #ff0000;">'</span> IDENTIFIED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">'</span><span style="color: #ff0000;">123</span><span style="color: #ff0000;">'</span>;</pre>
</div>
</div>
<p>
从库开启复制
</p>
<div>
<div class="cnblogs_code">
<pre>change master <span style="color: #0000ff;">to</span><span style="color: #000000;">
master_host</span><span style="color: #808080;">=</span><span style="color: #ff0000;">'</span><span style="color: #ff0000;">10.0.0.51</span><span style="color: #ff0000;">'</span><span style="color: #000000;">,
master_user</span><span style="color: #808080;">=</span><span style="color: #ff0000;">'</span><span style="color: #ff0000;">repl</span><span style="color: #ff0000;">'</span><span style="color: #000000;">,
master_password</span><span style="color: #808080;">=</span><span style="color: #ff0000;">'</span><span style="color: #ff0000;">123</span><span style="color: #ff0000;">'</span><span style="color: #000000;">,
MASTER_AUTO_POSITION</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">1</span>;</pre>
</div>
</div>
<p>
启动从库复制
</p>
<div>
<div class="cnblogs_code">
<pre>start slave;</pre>
</div>
</div>
<h3>
<span id="133_GTID">1.3.3 GTID复制技术说明</span>
</h3>
<p>
<span style="background-color: #ffff00;"><strong>MySQL GTID</strong><strong>简介</strong></span>
</p>
<p>
GTID的全称为 global transaction identifier ，可以翻译为全局事务标示符，GTID在原始master上的事务提交时被创建。GTID需要在全局的主-备拓扑结构中保持唯一性，GTID由两部分组成：
</p>
<p>
<span class="cnblogs_code">GTID <span style="color: #808080;">=</span> source_id:transaction_id</span>
</p>
<p>
<span style="background-color: #ffff00;"><strong>source_id</strong></span>用于标示源服务器，用server_uuid来表示，这个值在第一次启动时生成，并写入到配置文件data/auto.cnf中
</p>
<p>
<span style="background-color: #ffff00;"><strong>transaction_id</strong></span>则是根据在源服务器上第几个提交的事务来确定。
</p>
<p>
<em><span style="text-decoration: underline;">GTID</span></em><em><span style="text-decoration: underline;">事件结构</span></em>
</p>
<p align="center">
<img data-original="https://clsn.io/wp-content/uploads/2018/03/1190037-20171230164153273-855151708.png" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" />
</p>
<p>
<em><span style="text-decoration: underline;">GTID</span></em><em><span style="text-decoration: underline;">在二进制日志中的结构</span></em>
</p>
<p align="center">
<img data-original="https://clsn.io/wp-content/uploads/2018/03/1190037-20171230164337492-336799988.png" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" />4
</p>
<p>
<strong><em><span style="text-decoration: underline;">一个GTID</span></em></strong><strong><em><span style="text-decoration: underline;">的生命周期包括：</span></em></strong>
</p>
<div>
<blockquote>
<p class="a4">
1.事务在主库上执行并提交给事务分配一个gtid（由主库的uuid和该服务器上未使用的最小事务序列号），该GTID被写入到binlog中。
</p>
<p class="a4">
2.备库读取relaylog中的gtid，并设置session级别的gtid_next的值，以告诉备库下一个事务必须使用这个值
</p>
<p class="a4">
3.备库检查该gtid是否已经被其使用并记录到他自己的binlog中。slave需要担保之前的事务没有使用这个gtid，也要担保此时已分读取gtid，但未提交的事务也不恩呢过使用这个gtid.
</p>
<p class="a4">
4.由于gtid_next非空，slave不会去生成一个新的gtid，而是使用从主库获得的gtid。这可以保证在一个复制拓扑中的同一个事务gtid不变。由于GTID在全局的唯一性，通过GTID，我们可以在自动切换时对一些复杂的复制拓扑很方便的提升新主库及新备库，例如通过指向特定的GTID来确定新备库复制坐标。
</p>
</blockquote>
</div>
<p>
GTID是用来替代以前classic的复制方法；
</p>
<p>
MySQL5.6.2支持 MySQL5.6.10后完善；
</p>
<p>
<span style="background-color: #ffff00;"><strong>GTID</strong><strong>相比传统复制的优点：</strong></span>
</p>
<blockquote>
<p>
1.一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次
</p>
<p>
2.GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
</p>
<p>
3.减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机
</p>
</blockquote>
<p>
<strong>GTID</strong><strong>的限制：</strong>
</p>
<div>
<blockquote>
<p class="a4">
1.不支持非事务引擎
</p>
<p class="a4">
2.不支持create table ... select 语句复制(主库直接报错)
</p>
<p class="a4">
原理：（ 会生成两个sql，一个是DDL创建表SQL，一个是insert into 插入数据的sql。
</p>
<p class="a4">
由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID ）
</p>
<p class="a4">
3.不允许一个SQL同时更新一个事务引擎表和非事务引擎表
</p>
<p class="a4">
4.在一个复制组中，必须要求统一开启GTID或者是关闭GTID
</p>
<p class="a4">
5.开启GTID需要重启（5.7除外）
</p>
<p class="a4">
6.开启GTID后，就不再使用原来的传统复制方式
</p>
<p class="a4">
7.对于create temporary table 和 drop temporary table语句不支持
</p>
<p class="a4">
8.不支持sql_slave_skip_counter
</p>
</blockquote>
</div>
<h3>
<span id="134_COM_BINLOG_DUMP_GTID">1.3.4 COM_BINLOG_DUMP_GTID</span>
</h3>
<p>
从机发送到主机执行的事务的标识符的主范围
</p>
<p>
<span class="cnblogs_code">Master send all other transactions to slave</span>
</p>
<p>
同样的GTID不能被执行两次，如果有同样的GTID，会自动被skip掉。
</p>
<p align="center">
<img data-original="https://clsn.io/wp-content/uploads/2018/03/1190037-20171230172535945-1927730298.png" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" />
</p>
<p>
slave1：将自己的UUID1：1发送给master，然后接收到了UUID1：2，UUID1：3 event
</p>
<p>
slave2：将自己的UUID1：1，UUID1：2发送给master，然后接收到了UUID1：3事件
</p>
<p>
<span style="background-color: #00ff00;"><strong>GTID</strong><strong>组成</strong></span>
</p>
<p>
GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增
</p>
<div>
<div class="cnblogs_code">
<pre>GTID =<span style="color: #000000;"> source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
<h3>
<span id="135_MySQL_GTID">1.3.5 【示例二】MySQL GTID复制配置</span>
</h3>
<p>
主节点my.cnf文件
</p>
<div>
<div class="cnblogs_code">
<pre># <span style="color: #0000ff;">vi</span> /etc/<span style="color: #000000;">my.cnf
[mysqld] basedir=/usr/local/mysql datadir=/data/mysql server-id=1 log-bin=mysql-bin socket=/tmp/mysql.sock binlog-format=ROW gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1
<p>
从节点my.cnf文件
</p>
<div>
<div class="cnblogs_code">
<pre># <span style="color: #0000ff;">vi</span> /etc/<span style="color: #000000;">my.cnf
[mysqld] basedir=/usr/local/mysql datadir=/data/mysql server-id=2 binlog-format=ROW gtid-mode=on enforce-gtid-consistency=true log-bin=mysql-bin log_slave_updates = 1 socket=/tmp/mysql.sock
<p>
<strong>配置文件注解</strong>
</p>
<div>
<div class="cnblogs_code">
<pre>server-<span style="color: #0000ff;">id</span>=<span style="color: #000000;">x # 同一个复制拓扑中的所有服务器的id号必须惟一
binlog-format=RO # 二进制日志格式，强烈建议为ROW gtid-mode=on # 启用gtid类型，否则就是普通的复制架构 enforce-gtid-consistency=true # 强制GTID的一致性 log-slave-updates=1 # slave更新是否记入日志
<p>
复制用户准备（Master主节点）
</p>
<div>
<div class="cnblogs_code">
<pre>mysql>GRANT REPLICATION SLAVE ON *.* TO rep@<span style="color: #800000;">'</span><span style="color: #800000;">10.0.0.%</span><span style="color: #800000;">'</span> IDENTIFIED BY <span style="color: #800000;">'</span><span style="color: #800000;">123</span><span style="color: #800000;">'</span>;</pre>
</div>
<p>
开启复制（Slave从节点）
</p>
</div>
<div>
<div class="cnblogs_code">
<pre>mysql><span style="color: #000000;">start slave;
mysql>show slave status\G
<p>
现在就可以进行主从复制测试。
</p>
</div>
<h2>
<span id="14_MHA">1.4 部署MHA</span>
</h2>
<p>
本次MHA的部署基于GTID复制成功构建，普通主从复制也可以构建MHA架构。
</p>
<h3>
<span id="141">1.4.1 环境准备（所有节点操作）</span>
</h3>
<p>
<strong>安装依赖包</strong>
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">yum</span> <span style="color: #0000ff;">install</span> <span style="color: #0000ff;">perl</span>-DBD-MySQL -y</pre>
</div>
<p>
下载mha软件，mha官网：<a href="https://code.google.com/archive/p/mysql-master-ha/" target="_blank">https://code.google.com/archive/p/mysql-master-ha/</a>
</p>
</div>
<p>
github下载地址：<a href="https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads" target="_blank">https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads</a>
</p>
<p>
<span style="text-decoration: underline;"><strong><em>下载软件包</em></strong></span>
</p>
<div>
<blockquote>
<p class="ad">
mha4mysql-manager-0.56-0.el6.noarch.rpm
</p>
<p class="ad">
mha4mysql-manager-0.56.tar.gz
</p>
<p class="ad">
mha4mysql-node-0.56-0.el6.noarch.rpm
</p>
<p class="ad">
mha4mysql-node-0.56.tar.gz
</p>
</blockquote>
</div>
<p>
<em><span style="text-decoration: underline;"><strong>在所有节点安装node</strong></span></em>
</p>
<div>
<div class="cnblogs_code">
<pre>rpm -ivh mha4mysql-node-<span style="color: #800080;">0.56</span>-<span style="color: #800080;"></span>.el6.noarch.rpm</pre>
</div>
</div>
<p>
创建mha管理用户
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">grant</span> <span style="color: #808080;">all</span> <span style="color: #0000ff;">privileges</span> <span style="color: #0000ff;">on</span> <span style="color: #808080;">*</span>.<span style="color: #808080;">*</span> <span style="color: #0000ff;">to</span> mha@<span style="color: #ff0000;">'</span><span style="color: #ff0000;">10.0.0.%</span><span style="color: #ff0000;">'</span> identified <span style="color: #0000ff;">by</span> <span style="color: #ff0000;">'</span><span style="color: #ff0000;">mha</span><span style="color: #ff0000;">'</span>;</pre>
</div>
<p class="ad">
# 主库上创建，从库会自动复制（在从库上查看）
</p>
</div>
<p>
创建命令软连接<span style="color: #ff0000;"><strong>（重要）</strong></span>
</p>
<div>
<p class="ad">
如果不创建命令软连接，检测mha复制情况的时候会报错
</p>
<div class="cnblogs_code">
<pre>ln <span style="color: #808080;">-</span>s <span style="color: #808080;">/</span>application<span style="color: #808080;">/</span>mysql<span style="color: #808080;">/</span>bin<span style="color: #808080;">/</span>mysqlbinlog <span style="color: #808080;">/</span>usr<span style="color: #808080;">/</span>bin<span style="color: #808080;">/</span><span style="color: #000000;">mysqlbinlog
ln -s /application/mysql/bin/mysql /usr/bin/mysql
<h3>
<span id="142_mha-manager">1.4.2 部署管理节点（mha-manager）</span>
</h3>
<p>
在mysql-db03上部署管理节点
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #008000;">#</span><span style="color: #008000;"> 安装epel源，软件需要</span>
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo # 安装manager 依赖包 yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes # 安装manager管理软件 rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
<p>
<strong>创建必须目录</strong>
</p>
<div>
<div class="cnblogs_code">
<pre>mkdir -p /etc/<span style="color: #000000;">mha
mkdir -p /var/log/mha/app1 —-》可以管理多套主从复制
<p>
<strong><em>编辑mha-manager</em></strong><strong><em>配置文件</em></strong>
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> cat /etc/mha/app1.cnf</span>
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
[server1] hostname=10.0.0.51 port=3306
[server2] hostname=10.0.0.52 port=3306
[server3] hostname=10.0.0.53 port=3306
<p>
<strong>【配置文件详解】</strong>
</p>
<div>
<div class="cnblogs_code" onclick="cnblogs_code_show('ace8f99a-1a22-46a9-ae00-1f069cc0f0a0')">
<img id="code_img_closed_ace8f99a-1a22-46a9-ae00-1f069cc0f0a0" class="code_img_closed" src="https://clsn.io/wp-content/uploads/2018/03/ContractedBlock-4.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /><img id="code_img_opened_ace8f99a-1a22-46a9-ae00-1f069cc0f0a0" class="code_img_opened" style="display: none;" onclick="cnblogs_code_hide('ace8f99a-1a22-46a9-ae00-1f069cc0f0a0',event)" data-original="https://clsn.io/wp-content/uploads/2018/03/ExpandedBlockStart-4.gif" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /></p>
<div id="cnblogs_code_open_ace8f99a-1a22-46a9-ae00-1f069cc0f0a0" class="cnblogs_code_hide">
<pre><span style="color: #008080;"> 1</span> <span style="color: #000000;">[server default]
2 #设置manager的工作目录 3 manager_workdir=/var/log/masterha/app1 4 #设置manager的日志 5 manager_log=/var/log/masterha/app1/manager.log 6 #设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录 7 master_binlog_dir=/data/mysql 8 #设置自动failover时候的切换脚本 9 master_ip_failover_script= /usr/local/bin/master_ip_failover 10 #设置手动切换时候的切换脚本 11 master_ip_online_change_script= /usr/local/bin/master_ip_online_change 12 #设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码 13 password=123456 14 #设置监控用户root 15 user=root 16 #设置监控主库，发送ping包的时间间隔，尝试三次没有回应的时候自动进行failover 17 ping_interval=1 18 #设置远端mysql在发生切换时binlog的保存位置 19 remote_workdir=/tmp 20 #设置复制用户的密码 21 repl_password=123456 22 #设置复制环境中的复制用户名 23 repl_user=rep 24 #设置发生切换后发送的报警的脚本 25 report_script=/usr/local/send_report 26 #一旦MHA到server02的监控之间出现问题，MHA Manager将会尝试从server03登录到server02 27 secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=10.0.0.51 –master_port=3306 28 #设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用） 29 shutdown_script="" 30 #设置ssh的登录用户名 31 ssh_user=root 32 33 [server1] 34 hostname=10.0.0.51 35 port=3306 36 37 [server2] 38 hostname=10.0.0.52 39 port=3306 40 #设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave 41 candidate_master=1 42 #默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master 43 check_repl_delay=0
<p>
<span class="cnblogs_code_collapse">View Code 配置文件详细说明</span></div> </div>
<p>
<strong>配置ssh信任(密钥分发，在所有节点上执行)</strong>
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #008000;">#</span><span style="color: #008000;"> 生成密钥</span>
ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1 # 分发公钥，包括自己 for i in 1 2 3 ;do ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.5$i ;done
<p>
分发完成后测试分发是否成功
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">for</span> i <span style="color: #0000ff;">in</span> 1 2 3 ;do ssh 10.0.0.5<span style="color: #000000;">$i date ;done
或 [root@db03 ~]# masterha_check_ssh –conf=/etc/mha/app1.cnf 最后一行信息为如下字样即为分发成功： Thu Dec 28 18:44:53 2017 - [info] All SSH connection tests passed successfully.
<h3>
<span id="143_mha">1.4.3 启动mha</span>
</h3>
<p>
经过上面的部署过后，mha架构已经搭建完成
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #008000;">#</span><span style="color: #008000;"> 启动mha</span>
nohup masterha_manager –conf=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
<p>
启动成功后，检查主库状态
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> masterha_check_status --conf=/etc/mha/app1.cnf </span>
app1 (pid:3298) is running(0:PING_OK), master:10.0.0.51
<h3>
<span id="144_master">1.4.4 切换master测试</span>
</h3>
<p>
查看现在的主库是哪个
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> masterha_check_status --conf=/etc/mha/app1.cnf </span>
app1 (pid:11669) is running(0:PING_OK), master:10.0.0.51
<p>
手动停止主库
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db01 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> /etc/init.d/mysqld stop </span>
Shutting down MySQL….. SUCCESS!
<p>
再停止数据的同时查看日志信息的变化
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> tailf /var/log/mha/app1/manager</span>
Fri Dec </span>29 15:51:14 2017 - [info] All other slaves should start replication <span style="color: #0000ff;">from</span><span style="color: #000000;">
here. Statement should be: CHANGE MASTER TO MASTER_HOST</span>=<span style="color: #800000;">'</span><span style="color: #800000;">10.0.0.52</span><span style="color: #800000;">'</span>, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=<span style="color: #800000;">'</span><span style="color: #800000;">repl</span><span style="color: #800000;">'</span>, MASTER_PASSWORD=<span style="color: #800000;">'</span><span style="color: #800000;">xxx</span><span style="color: #800000;">'</span>;</pre>
</div>
</div>
<p>
<span style="background-color: #ffff00;"><strong>修复主从</strong></span>
</p>
<p>
① 启动原主库,添加change master to 信息
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db01 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> /etc/init.d/mysqld start </span>
<span style="color: #000000;">Starting MySQL. SUCCESS!
mysql</span>> CHANGE MASTER TO MASTER_HOST=<span style="color: #800000;">'</span><span style="color: #800000;">10.0.0.52</span><span style="color: #800000;">'</span>, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=<span style="color: #800000;">'</span><span style="color: #800000;">repl</span><span style="color: #800000;">'</span>, MASTER_PASSWORD=<span style="color: #800000;">'</span><span style="color: #800000;">123</span><span style="color: #800000;">'</span><span style="color: #000000;">;
mysql</span>> start slave;</pre>
</div>
</div>
<p>
② 查看主从复制状态
</p>
<div>
<div class="cnblogs_code">
<pre>mysql><span style="color: #000000;"> show slave status\G
Master_Host: </span>10.0.0.52<span style="color: #000000;">
Slave_IO_Running: Yes
Slave_SQL_Running: Yes</span></pre>
</div>
</div>
<p>
<span style="background-color: #ffff00;"><strong>修复mha</strong></span>
</p>
<p>
① 修改app1.cnf配置文件，添加回被剔除主机
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> cat /etc/mha/app1.cnf </span>
<span style="color: #000000;">[binlog1]
hostname</span>=10.0.0.53<span style="color: #000000;">
master_binlog_dir</span>=/data/mysql/binlog/<span style="color: #000000;">
no_master</span>=1<span style="color: #000000;">
[server default]
manager_log</span>=/var/log/mha/app1/<span style="color: #000000;">manager
manager_workdir</span>=/var/log/mha/<span style="color: #000000;">app1
master_binlog_dir</span>=/data/<span style="color: #000000;">mysql
master_ip_failover_script</span>=/usr/local/bin/<span style="color: #000000;">master_ip_failover
password</span>=<span style="color: #000000;">mha
ping_interval</span>=2<span style="color: #000000;">
repl_password</span>=123<span style="color: #000000;">
repl_user</span>=<span style="color: #000000;">repl
ssh_user</span>=<span style="color: #000000;">root
user</span>=<span style="color: #000000;">mha
[server1]
hostname</span>=10.0.0.51<span style="color: #000000;">
port</span>=3306<span style="color: #000000;">
[server2]
hostname</span>=10.0.0.52<span style="color: #000000;">
port</span>=3306<span style="color: #000000;">
[server3]
hostname</span>=10.0.0.53<span style="color: #000000;">
port</span>=3306</pre>
</div>
</div>
<p>
② mha检查复制状态
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> masterha_check_repl --conf=/etc/mha/app1.cnf</span>
MySQL Replication Health <span style="color: #0000ff;">is</span> OK.</pre>
</div>
</div>
<p>
③ 启动mha程序
</p>
<div>
<div class="cnblogs_code">
<pre>nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &</pre>
</div>
</div>
<p>
<span style="background-color: #ffff00;"><strong>到此主库切换成功</strong></span>
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> masterha_check_status --conf=/etc/mha/app1.cnf </span>
app1 (pid:11978) <span style="color: #0000ff;">is</span> running(0:PING_OK), master:10.0.0.52</pre>
</div>
</div>
<p>
<span style="background-color: #00ff00;"><strong>实验结束将主库切换回db01.</strong></span>
</p>
<p>
① 停止mha
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> masterha_stop --conf=/etc/mha/app1.cnf </span>
Stopped app1 successfully.</pre>
</div>
</div>
<p>
② 停止所有从库slave(所有库操作)
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #000000;">stop slave;
reset slave all;</span></pre>
</div>
</div>
<p>
③ 重做主从复制(db02、db03)
</p>
<div class="cnblogs_code">
<pre><span style="color: #000000;">CHANGE MASTER TO
MASTER_HOST</span>=<span style="color: #800000;">'</span><span style="color: #800000;">10.0.0.51</span><span style="color: #800000;">'</span><span style="color: #000000;">,
MASTER_PORT</span>=3306<span style="color: #000000;">,
MASTER_AUTO_POSITION</span>=1<span style="color: #000000;">,
MASTER_USER</span>=<span style="color: #800000;">'</span><span style="color: #800000;">repl</span><span style="color: #800000;">'</span><span style="color: #000000;">,
MASTER_PASSWORD</span>=<span style="color: #800000;">'</span><span style="color: #800000;">123</span><span style="color: #800000;">'</span>;</pre>
</div>
<p>
④ 启动slave
</p>
<div>
<div class="cnblogs_code">
<pre>start slave;</pre>
</div>
<p class="ad">
启动之后检查从库是否为两个yes <span class="cnblogs_code">show slave status\G</span>
</p>
</div>
<p>
⑤ mha检查主从复制
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> masterha_check_repl --conf=/etc/mha/app1.cnf</span>
MySQL Replication Health <span style="color: #0000ff;">is</span> OK.</pre>
</div>
</div>
<p>
⑥ 启动mha
</p>
<div>
<div class="cnblogs_code">
<pre>nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &</pre>
</div>
<p class="ad">
检查切换是否成功
</p>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> masterha_check_status --conf=/etc/mha/app1.cnf</span>
app1 (pid:12127) <span style="color: #0000ff;">is</span> running(0:PING_OK), master:10.0.0.51</pre>
</div>
</div>
<p>
到此主主节点有切回到db01
</p>
<h3>
<span id="145">1.4.5 设置权重</span>
</h3>
<p>
修改[server1]的权重
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #000000;">[server1]
hostname</span>=10.0.0.51<span style="color: #000000;">
port</span>=3306<span style="color: #000000;">
candidate_master</span>=1<span style="color: #000000;">
check_repl_delay</span>=0</pre>
</div>
</div>
<p>
配置说明
</p>
<div class="cnblogs_code">
<pre>candidate_master=1 ----<span style="color: #000000;">》不管怎样都切到优先级高的主机，一般在主机性能差异的时候用
check_repl_delay</span>=0 ----》不管优先级高的备选库，数据延时多久都要往那切</pre>
</div>
<p>
<span style="background-color: #00ff00; color: #ff0000;"><strong>注： </strong></span>
</p>
<blockquote>
<p style="color: #000000;">
1、多地多中心，设置本地节点为高权重
</p>
<p style="color: #000000;">
2、在有半同步复制的环境中，设置半同步复制节点为高权重
</p>
<p style="color: #000000;">
3、你觉着哪个机器适合做主节点，配置较高的 、性能较好的
</p>
</blockquote>
<h2>
<span id="15_VIP">1.5 配置VIP漂移</span>
</h2>
<h3>
<span id="151_IP">1.5.1 IP漂移的两种方式</span>
</h3>
<p>
? 通过keepalived的方式，管理虚拟IP的漂移
</p>
<p>
? 通过MHA自带脚本方式，管理虚拟IP的漂移
</p>
<h3>
<span id="152_MHA">1.5.2 MHA脚本方式</span>
</h3>
<p>
<span style="background-color: #00ff00;"><strong>修改mha</strong><strong>配置文件</strong></span>
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]<span style="color: #008000;">#</span><span style="color: #008000;"> grep "script" /etc/mha/app1.cnf </span>
<span style="color: #000000;">[server default]
master_ip_failover_script</span>=/usr/local/bin/master_ip_failover</pre>
</div>
</div>
<p>
再主配置中添加VIP脚本
</p>
<p>
<span style="background-color: #00ff00;"><strong>脚本内容</strong></span>
</p>
<div>
<div class="cnblogs_code" onclick="cnblogs_code_show('bac1ef9d-2c6b-4489-967d-8dd88e7087da')">
<img id="code_img_closed_bac1ef9d-2c6b-4489-967d-8dd88e7087da" class="code_img_closed" src="https://clsn.io/wp-content/uploads/2018/03/ContractedBlock-4.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /><img id="code_img_opened_bac1ef9d-2c6b-4489-967d-8dd88e7087da" class="code_img_opened" style="display: none;" onclick="cnblogs_code_hide('bac1ef9d-2c6b-4489-967d-8dd88e7087da',event)" data-original="https://clsn.io/wp-content/uploads/2018/03/ExpandedBlockStart-4.gif" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /></p>
<div id="cnblogs_code_open_bac1ef9d-2c6b-4489-967d-8dd88e7087da" class="cnblogs_code_hide">
<pre>[root<span style="color: #800080;">@db03</span> ~]<span style="color: #008000;">#</span><span style="color: #008000;"> cat /usr/local/bin/master_ip_failover
#!/usr/bin/env perl</span>
<span style="color: #0000ff;">use</span><span style="color: #000000;"> strict;
</span><span style="color: #0000ff;">use</span> warnings FATAL => <span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">all</span><span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">use</span> Getopt::<span style="color: #000000;">Long;
</span><span style="color: #0000ff;">my</span><span style="color: #000000;"> (
</span><span style="color: #800080;">$command</span>, <span style="color: #800080;">$ssh_user</span>, <span style="color: #800080;">$orig_master_host</span>, <span style="color: #800080;">$orig_master_ip</span>,
<span style="color: #800080;">$orig_master_port</span>, <span style="color: #800080;">$new_master_host</span>, <span style="color: #800080;">$new_master_ip</span>, <span style="color: #800080;">$new_master_port</span><span style="color: #000000;">
);
</span><span style="color: #0000ff;">my</span> <span style="color: #800080;">$vip</span> = <span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">10.0.0.55/24</span><span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">my</span> <span style="color: #800080;">$key</span> = <span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;"></span><span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">my</span> <span style="color: #800080;">$ssh_start_vip</span> = <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">/sbin/ifconfig eth0:$key $vip</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">my</span> <span style="color: #800080;">$ssh_stop_vip</span> = <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">/sbin/ifconfig eth0:$key down</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;">;
GetOptions(
</span><span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">command=s</span><span style="color: #000000; font-weight: bold;">'</span> => \<span style="color: #800080;">$command</span>,
<span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">ssh_user=s</span><span style="color: #000000; font-weight: bold;">'</span> => \<span style="color: #800080;">$ssh_user</span>,
<span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">orig_master_host=s</span><span style="color: #000000; font-weight: bold;">'</span> => \<span style="color: #800080;">$orig_master_host</span>,
<span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">orig_master_ip=s</span><span style="color: #000000; font-weight: bold;">'</span> => \<span style="color: #800080;">$orig_master_ip</span>,
<span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">orig_master_port=i</span><span style="color: #000000; font-weight: bold;">'</span> => \<span style="color: #800080;">$orig_master_port</span>,
<span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">new_master_host=s</span><span style="color: #000000; font-weight: bold;">'</span> => \<span style="color: #800080;">$new_master_host</span>,
<span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">new_master_ip=s</span><span style="color: #000000; font-weight: bold;">'</span> => \<span style="color: #800080;">$new_master_ip</span>,
<span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">new_master_port=i</span><span style="color: #000000; font-weight: bold;">'</span> => \<span style="color: #800080;">$new_master_port</span>,<span style="color: #000000;">
);
</span><span style="color: #0000ff;">exit</span> &<span style="color: #000000;">main();
</span><span style="color: #0000ff;">sub</span><span style="color: #000000;"> main {
</span><span style="color: #0000ff;">print</span> <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">if</span> ( <span style="color: #800080;">$command</span> eq <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">stop</span><span style="color: #000000; font-weight: bold;">"</span> || <span style="color: #800080;">$command</span> eq <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">stopssh</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;"> ) {
</span><span style="color: #0000ff;">my</span> <span style="color: #800080;">$exit_code</span> = <span style="color: #800000;">1</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">eval</span><span style="color: #000000;"> {
</span><span style="color: #0000ff;">print</span> <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">Disabling the VIP on old master: $orig_master_host \n</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;">;
</span>&<span style="color: #000000;">stop_vip();
</span><span style="color: #800080;">$exit_code</span> = <span style="color: #800000;"></span><span style="color: #000000;">;
};
</span><span style="color: #0000ff;">if</span><span style="color: #000000;"> ($@) {
</span><span style="color: #0000ff;">warn</span> <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">Got Error: $@\n</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">exit</span> <span style="color: #800080;">$exit_code</span><span style="color: #000000;">;
}
</span><span style="color: #0000ff;">exit</span> <span style="color: #800080;">$exit_code</span><span style="color: #000000;">;
}
</span><span style="color: #0000ff;">elsif</span> ( <span style="color: #800080;">$command</span> eq <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">start</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;"> ) {
</span><span style="color: #0000ff;">my</span> <span style="color: #800080;">$exit_code</span> = <span style="color: #800000;">10</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">eval</span><span style="color: #000000;"> {
</span><span style="color: #0000ff;">print</span> <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">Enabling the VIP - $vip on the new master - $new_master_host \n</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;">;
</span>&<span style="color: #000000;">start_vip();
</span><span style="color: #800080;">$exit_code</span> = <span style="color: #800000;"></span><span style="color: #000000;">;
};
</span><span style="color: #0000ff;">if</span><span style="color: #000000;"> ($@) {
</span><span style="color: #0000ff;">warn</span><span style="color: #000000;"> $@;
</span><span style="color: #0000ff;">exit</span> <span style="color: #800080;">$exit_code</span><span style="color: #000000;">;
}
</span><span style="color: #0000ff;">exit</span> <span style="color: #800080;">$exit_code</span><span style="color: #000000;">;
}
</span><span style="color: #0000ff;">elsif</span> ( <span style="color: #800080;">$command</span> eq <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">status</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;"> ) {
</span><span style="color: #0000ff;">print</span> <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">Checking the Status of the script.. OK \n</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">exit</span> <span style="color: #800000;"></span><span style="color: #000000;">;
}
</span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
</span>&<span style="color: #000000;">usage();
</span><span style="color: #0000ff;">exit</span> <span style="color: #800000;">1</span><span style="color: #000000;">;
}
}
</span><span style="color: #0000ff;">sub</span><span style="color: #000000;"> start_vip() {
`ssh </span><span style="color: #800080;">$ssh_user</span>\@<span style="color: #800080;">$new_master_host</span> \<span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;"> $ssh_start_vip \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
</span><span style="color: #000000; font-weight: bold;">"</span>Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n<span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">;
}</span></pre>
</div>
<p>
<span class="cnblogs_code_collapse">View Code VIP管理脚本 </span></div> </div>
<p>
<em> 该脚本为软件自带，脚本获取方法：再mha</em><em>源码包中的samples</em><em>目录下有该脚本的模板，对该模板进行修改即可使用。路径如： <span class="cnblogs_code">mha4mysql-manager-<span style="color: #800000;">0.56</span>/samples/scripts</span> </em>
</p>
<p>
脚本修改内容
</p>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">my</span> <span style="color: #800080;">$vip</span> = <span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;">10.0.0.55/24</span><span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">my</span> <span style="color: #800080;">$key</span> = <span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000; font-weight: bold;"></span><span style="color: #000000; font-weight: bold;">'</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">my</span> <span style="color: #800080;">$ssh_start_vip</span> = <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">/sbin/ifconfig eth0:$key $vip</span><span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">my</span> <span style="color: #800080;">$ssh_stop_vip</span> = <span style="color: #000000; font-weight: bold;">"</span><span style="color: #000000; font-weight: bold;">/sbin/ifconfig eth0:$key down</span><span style="color: #000000; font-weight: bold;">"</span>;</pre>
</div>
<p>
脚本添加执行权限否则mha无法启动
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">chmod</span> +x /usr/local/bin/master_ip_failover</pre>
</div>
</div>
<p>
<strong>手动绑定VIP(</strong><strong>主库)</strong>
</p>
<div>
<div class="cnblogs_code">
<pre>ifconfig eth0:<span style="color: #800000;"></span> <span style="color: #800000;">10.0</span>.<span style="color: #800000;">0.55</span>/<span style="color: #800000;">24</span></pre>
</div>
<p>
检查
</p>
</div>
<div>
<div class="cnblogs_code">
<pre>[root@db01 ~<span style="color: #000000;">]# ip a s eth0
</span><span style="color: #800080;">2</span>: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu <span style="color: #800080;">1500</span> qdisc pfifo_fast state UP qlen <span style="color: #800080;">1000</span><span style="color: #000000;">
link</span>/ether <span style="color: #800080;">00</span>:0c:<span style="color: #800080;">29</span>:6c:7a:<span style="color: #800080;">11</span><span style="color: #000000;"> brd ff:ff:ff:ff:ff:ff
inet </span><span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.51</span>/<span style="color: #800080;">24</span> brd <span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.255</span><span style="color: #000000;"> scope global eth0
inet </span><span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.55</span>/<span style="color: #800080;">24</span> brd <span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.255</span> scope global secondary eth0:<span style="color: #800080;"></span><span style="color: #000000;">
inet6 fe80::20c:29ff:fe6c:7a11</span>/<span style="color: #800080;">64</span><span style="color: #000000;"> scope link
valid_lft forever preferred_lft forever</span></pre>
</div>
</div>
<p>
<span style="color: #ffffff;"> <span style="background-color: #00ff00;"> <strong>至此vip</strong><strong>漂移配置完成 </strong></span></span>
</p>
<h3>
<span id="153_IP">1.5.3 测试虚拟IP漂移</span>
</h3>
<p>
查看db02的slave信息
</p>
<div>
<div class="cnblogs_code" onclick="cnblogs_code_show('9d5b6175-ebdf-4c5d-957f-65c2a0d693a4')">
<img id="code_img_closed_9d5b6175-ebdf-4c5d-957f-65c2a0d693a4" class="code_img_closed" src="https://clsn.io/wp-content/uploads/2018/03/ContractedBlock-4.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /><img id="code_img_opened_9d5b6175-ebdf-4c5d-957f-65c2a0d693a4" class="code_img_opened" style="display: none;" onclick="cnblogs_code_hide('9d5b6175-ebdf-4c5d-957f-65c2a0d693a4',event)" data-original="https://clsn.io/wp-content/uploads/2018/03/ExpandedBlockStart-4.gif" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /></p>
<div id="cnblogs_code_open_9d5b6175-ebdf-4c5d-957f-65c2a0d693a4" class="cnblogs_code_hide">
<pre><span style="color: #008080;"> 1</span> mysql<span style="color: #808080;">></span><span style="color: #000000;"> show slave status\G
</span><span style="color: #008080;"> 2</span> <span style="color: #808080;">***************************</span> <span style="color: #800000; font-weight: bold;">1</span>. row <span style="color: #808080;">***************************</span>
<span style="color: #008080;"> 3</span> Slave_IO_State: Waiting <span style="color: #0000ff;">for</span> master <span style="color: #0000ff;">to</span><span style="color: #000000;"> send event
</span><span style="color: #008080;"> 4</span> Master_Host: <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.51</span>
<span style="color: #008080;"> 5</span> <span style="color: #000000;"> Master_User: repl
</span><span style="color: #008080;"> 6</span> Master_Port: <span style="color: #800000; font-weight: bold;">3306</span>
<span style="color: #008080;"> 7</span> Connect_Retry: <span style="color: #800000; font-weight: bold;">60</span>
<span style="color: #008080;"> 8</span> Master_Log_File: mysql<span style="color: #808080;">-</span>bin.<span style="color: #800000; font-weight: bold;">000007</span>
<span style="color: #008080;"> 9</span> Read_Master_Log_Pos: <span style="color: #800000; font-weight: bold;">191</span>
<span style="color: #008080;">10</span> Relay_Log_File: db02<span style="color: #808080;">-</span>relay<span style="color: #808080;">-</span>bin.<span style="color: #800000; font-weight: bold;">000002</span>
<span style="color: #008080;">11</span> Relay_Log_Pos: <span style="color: #800000; font-weight: bold;">361</span>
<span style="color: #008080;">12</span> Relay_Master_Log_File: mysql<span style="color: #808080;">-</span>bin.<span style="color: #800000; font-weight: bold;">000007</span>
<span style="color: #008080;">13</span> <span style="color: #000000;"> Slave_IO_Running: Yes
</span><span style="color: #008080;">14</span> <span style="color: #000000;"> Slave_SQL_Running: Yes
</span><span style="color: #008080;">15</span> <span style="color: #000000;"> Replicate_Do_DB:
</span><span style="color: #008080;">16</span> <span style="color: #000000;"> Replicate_Ignore_DB:
</span><span style="color: #008080;">17</span> <span style="color: #000000;"> Replicate_Do_Table:
</span><span style="color: #008080;">18</span> <span style="color: #000000;"> Replicate_Ignore_Table:
</span><span style="color: #008080;">19</span> <span style="color: #000000;"> Replicate_Wild_Do_Table:
</span><span style="color: #008080;">20</span> <span style="color: #000000;"> Replicate_Wild_Ignore_Table:
</span><span style="color: #008080;">21</span> Last_Errno: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">22</span> <span style="color: #000000;"> Last_Error:
</span><span style="color: #008080;">23</span> Skip_Counter: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">24</span> Exec_Master_Log_Pos: <span style="color: #800000; font-weight: bold;">191</span>
<span style="color: #008080;">25</span> Relay_Log_Space: <span style="color: #800000; font-weight: bold;">564</span>
<span style="color: #008080;">26</span> <span style="color: #000000;"> Until_Condition: None
</span><span style="color: #008080;">27</span> <span style="color: #000000;"> Until_Log_File:
</span><span style="color: #008080;">28</span> Until_Log_Pos: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">29</span> <span style="color: #000000;"> Master_SSL_Allowed: No
</span><span style="color: #008080;">30</span> <span style="color: #000000;"> Master_SSL_CA_File:
</span><span style="color: #008080;">31</span> <span style="color: #000000;"> Master_SSL_CA_Path:
</span><span style="color: #008080;">32</span> <span style="color: #000000;"> Master_SSL_Cert:
</span><span style="color: #008080;">33</span> <span style="color: #000000;"> Master_SSL_Cipher:
</span><span style="color: #008080;">34</span> <span style="color: #000000;"> Master_SSL_Key:
</span><span style="color: #008080;">35</span> Seconds_Behind_Master: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">36</span> <span style="color: #000000;">Master_SSL_Verify_Server_Cert: No
</span><span style="color: #008080;">37</span> Last_IO_Errno: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">38</span> <span style="color: #000000;"> Last_IO_Error:
</span><span style="color: #008080;">39</span> Last_SQL_Errno: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">40</span> <span style="color: #000000;"> Last_SQL_Error:
</span><span style="color: #008080;">41</span> <span style="color: #000000;"> Replicate_Ignore_Server_Ids:
</span><span style="color: #008080;">42</span> Master_Server_Id: <span style="color: #800000; font-weight: bold;">51</span>
<span style="color: #008080;">43</span> Master_UUID: c8fcd56e<span style="color: #808080;">-</span>eb79<span style="color: #808080;">-</span>11e7<span style="color: #808080;">-</span>97b0<span style="color: #808080;">-</span><span style="color: #000000;">000c296c7a11
</span><span style="color: #008080;">44</span> Master_Info_File: <span style="color: #808080;">/</span>application<span style="color: #808080;">/</span>mysql<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">5.6</span>.<span style="color: #800000; font-weight: bold;">36</span><span style="color: #808080;">/</span>data<span style="color: #808080;">/</span><span style="color: #000000;">master.info
</span><span style="color: #008080;">45</span> SQL_Delay: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">46</span> SQL_Remaining_Delay: <span style="color: #0000ff;">NULL</span>
<span style="color: #008080;">47</span> Slave_SQL_Running_State: Slave has <span style="color: #0000ff;">read</span> <span style="color: #808080;">all</span> relay <span style="color: #ff00ff;">log</span>; waiting <span style="color: #0000ff;">for</span> the slave I<span style="color: #808080;">/</span>O thread <span style="color: #0000ff;">to</span> <span style="color: #0000ff;">update</span><span style="color: #000000;"> it
</span><span style="color: #008080;">48</span> Master_Retry_Count: <span style="color: #800000; font-weight: bold;">86400</span>
<span style="color: #008080;">49</span> <span style="color: #000000;"> Master_Bind:
</span><span style="color: #008080;">50</span> <span style="color: #000000;"> Last_IO_Error_Timestamp:
</span><span style="color: #008080;">51</span> <span style="color: #000000;"> Last_SQL_Error_Timestamp:
</span><span style="color: #008080;">52</span> <span style="color: #000000;"> Master_SSL_Crl:
</span><span style="color: #008080;">53</span> <span style="color: #000000;"> Master_SSL_Crlpath:
</span><span style="color: #008080;">54</span> <span style="color: #000000;"> Retrieved_Gtid_Set:
</span><span style="color: #008080;">55</span> Executed_Gtid_Set: c8fcd56e<span style="color: #808080;">-</span>eb79<span style="color: #808080;">-</span>11e7<span style="color: #808080;">-</span>97b0<span style="color: #808080;">-</span>000c296c7a11:<span style="color: #800000; font-weight: bold;">1</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">3</span>
<span style="color: #008080;">56</span> Auto_Position: <span style="color: #800000; font-weight: bold;">1</span>
<span style="color: #008080;">57</span> <span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</pre>
</div>
<p>
<span class="cnblogs_code_collapse">View Code 现在主从状态</span></div>
<p>
停掉主库
</p></div>
<div>
<div class="cnblogs_code">
<pre>[root@db01 ~]# /etc/init.d/mysqld stop</pre>
</div>
</div>
<p>
在db03上查看从库slave信息
</p>
<div>
<div class="cnblogs_code" onclick="cnblogs_code_show('55b33084-103e-4c89-bf41-2d29eb14a1ac')">
<img id="code_img_closed_55b33084-103e-4c89-bf41-2d29eb14a1ac" class="code_img_closed" src="https://clsn.io/wp-content/uploads/2018/03/ContractedBlock-4.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /><img id="code_img_opened_55b33084-103e-4c89-bf41-2d29eb14a1ac" class="code_img_opened" style="display: none;" onclick="cnblogs_code_hide('55b33084-103e-4c89-bf41-2d29eb14a1ac',event)" data-original="https://clsn.io/wp-content/uploads/2018/03/ExpandedBlockStart-4.gif" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /></p>
<div id="cnblogs_code_open_55b33084-103e-4c89-bf41-2d29eb14a1ac" class="cnblogs_code_hide">
<pre><span style="color: #008080;"> 1</span> mysql<span style="color: #808080;">></span><span style="color: #000000;"> show slave status\G
</span><span style="color: #008080;"> 2</span> <span style="color: #808080;">***************************</span> <span style="color: #800000; font-weight: bold;">1</span>. row <span style="color: #808080;">***************************</span>
<span style="color: #008080;"> 3</span> Slave_IO_State: Waiting <span style="color: #0000ff;">for</span> master <span style="color: #0000ff;">to</span><span style="color: #000000;"> send event
</span><span style="color: #008080;"> 4</span> Master_Host: <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.52</span>
<span style="color: #008080;"> 5</span> <span style="color: #000000;"> Master_User: repl
</span><span style="color: #008080;"> 6</span> Master_Port: <span style="color: #800000; font-weight: bold;">3306</span>
<span style="color: #008080;"> 7</span> Connect_Retry: <span style="color: #800000; font-weight: bold;">60</span>
<span style="color: #008080;"> 8</span> Master_Log_File: mysql<span style="color: #808080;">-</span>bin.<span style="color: #800000; font-weight: bold;">000009</span>
<span style="color: #008080;"> 9</span> Read_Master_Log_Pos: <span style="color: #800000; font-weight: bold;">191</span>
<span style="color: #008080;">10</span> Relay_Log_File: db03<span style="color: #808080;">-</span>relay<span style="color: #808080;">-</span>bin.<span style="color: #800000; font-weight: bold;">000002</span>
<span style="color: #008080;">11</span> Relay_Log_Pos: <span style="color: #800000; font-weight: bold;">361</span>
<span style="color: #008080;">12</span> Relay_Master_Log_File: mysql<span style="color: #808080;">-</span>bin.<span style="color: #800000; font-weight: bold;">000009</span>
<span style="color: #008080;">13</span> <span style="color: #000000;"> Slave_IO_Running: Yes
</span><span style="color: #008080;">14</span> <span style="color: #000000;"> Slave_SQL_Running: Yes
</span><span style="color: #008080;">15</span> <span style="color: #000000;"> Replicate_Do_DB:
</span><span style="color: #008080;">16</span> <span style="color: #000000;"> Replicate_Ignore_DB:
</span><span style="color: #008080;">17</span> <span style="color: #000000;"> Replicate_Do_Table:
</span><span style="color: #008080;">18</span> <span style="color: #000000;"> Replicate_Ignore_Table:
</span><span style="color: #008080;">19</span> <span style="color: #000000;"> Replicate_Wild_Do_Table:
</span><span style="color: #008080;">20</span> <span style="color: #000000;"> Replicate_Wild_Ignore_Table:
</span><span style="color: #008080;">21</span> Last_Errno: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">22</span> <span style="color: #000000;"> Last_Error:
</span><span style="color: #008080;">23</span> Skip_Counter: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">24</span> Exec_Master_Log_Pos: <span style="color: #800000; font-weight: bold;">191</span>
<span style="color: #008080;">25</span> Relay_Log_Space: <span style="color: #800000; font-weight: bold;">564</span>
<span style="color: #008080;">26</span> <span style="color: #000000;"> Until_Condition: None
</span><span style="color: #008080;">27</span> <span style="color: #000000;"> Until_Log_File:
</span><span style="color: #008080;">28</span> Until_Log_Pos: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">29</span> <span style="color: #000000;"> Master_SSL_Allowed: No
</span><span style="color: #008080;">30</span> <span style="color: #000000;"> Master_SSL_CA_File:
</span><span style="color: #008080;">31</span> <span style="color: #000000;"> Master_SSL_CA_Path:
</span><span style="color: #008080;">32</span> <span style="color: #000000;"> Master_SSL_Cert:
</span><span style="color: #008080;">33</span> <span style="color: #000000;"> Master_SSL_Cipher:
</span><span style="color: #008080;">34</span> <span style="color: #000000;"> Master_SSL_Key:
</span><span style="color: #008080;">35</span> Seconds_Behind_Master: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">36</span> <span style="color: #000000;">Master_SSL_Verify_Server_Cert: No
</span><span style="color: #008080;">37</span> Last_IO_Errno: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">38</span> <span style="color: #000000;"> Last_IO_Error:
</span><span style="color: #008080;">39</span> Last_SQL_Errno: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">40</span> <span style="color: #000000;"> Last_SQL_Error:
</span><span style="color: #008080;">41</span> <span style="color: #000000;"> Replicate_Ignore_Server_Ids:
</span><span style="color: #008080;">42</span> Master_Server_Id: <span style="color: #800000; font-weight: bold;">52</span>
<span style="color: #008080;">43</span> Master_UUID: c8fa1d13<span style="color: #808080;">-</span>eb79<span style="color: #808080;">-</span>11e7<span style="color: #808080;">-</span>97b0<span style="color: #808080;">-</span><span style="color: #000000;">000c29d60ab3
</span><span style="color: #008080;">44</span> Master_Info_File: <span style="color: #808080;">/</span>application<span style="color: #808080;">/</span>mysql<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">5.6</span>.<span style="color: #800000; font-weight: bold;">36</span><span style="color: #808080;">/</span>data<span style="color: #808080;">/</span><span style="color: #000000;">master.info
</span><span style="color: #008080;">45</span> SQL_Delay: <span style="color: #800000; font-weight: bold;"></span>
<span style="color: #008080;">46</span> SQL_Remaining_Delay: <span style="color: #0000ff;">NULL</span>
<span style="color: #008080;">47</span> Slave_SQL_Running_State: Slave has <span style="color: #0000ff;">read</span> <span style="color: #808080;">all</span> relay <span style="color: #ff00ff;">log</span>; waiting <span style="color: #0000ff;">for</span> the slave I<span style="color: #808080;">/</span>O thread <span style="color: #0000ff;">to</span> <span style="color: #0000ff;">update</span><span style="color: #000000;"> it
</span><span style="color: #008080;">48</span> Master_Retry_Count: <span style="color: #800000; font-weight: bold;">86400</span>
<span style="color: #008080;">49</span> <span style="color: #000000;"> Master_Bind:
</span><span style="color: #008080;">50</span> <span style="color: #000000;"> Last_IO_Error_Timestamp:
</span><span style="color: #008080;">51</span> <span style="color: #000000;"> Last_SQL_Error_Timestamp:
</span><span style="color: #008080;">52</span> <span style="color: #000000;"> Master_SSL_Crl:
</span><span style="color: #008080;">53</span> <span style="color: #000000;"> Master_SSL_Crlpath:
</span><span style="color: #008080;">54</span> <span style="color: #000000;"> Retrieved_Gtid_Set:
</span><span style="color: #008080;">55</span> Executed_Gtid_Set: c8fcd56e<span style="color: #808080;">-</span>eb79<span style="color: #808080;">-</span>11e7<span style="color: #808080;">-</span>97b0<span style="color: #808080;">-</span>000c296c7a11:<span style="color: #800000; font-weight: bold;">1</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">3</span>
<span style="color: #008080;">56</span> Auto_Position: <span style="color: #800000; font-weight: bold;">1</span>
<span style="color: #008080;">57</span> <span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</pre>
</div>
<p>
<span class="cnblogs_code_collapse">View Code 停掉主库后的主从信息</span></div> </div>
<p>
在db01上查看vip信息
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #800000; font-weight: bold;">2</span>: eth0: <span style="color: #808080;"><</span>BROADCAST,MULTICAST,UP,LOWER_UP<span style="color: #808080;">></span> mtu <span style="color: #800000; font-weight: bold;">1500</span> qdisc pfifo_fast state UP qlen <span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">
link</span><span style="color: #808080;">/</span>ether <span style="color: #800000; font-weight: bold;">00</span>:0c:<span style="color: #800000; font-weight: bold;">29</span>:6c:7a:<span style="color: #800000; font-weight: bold;">11</span><span style="color: #000000;"> brd ff:ff:ff:ff:ff:ff
inet </span><span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.51</span><span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">24</span> brd <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.255</span><span style="color: #000000;"> scope global eth0
inet6 fe80::20c:29ff:fe6c:7a11</span><span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">64</span><span style="color: #000000;"> scope link
valid_lft forever preferred_lft forever</span></pre>
</div>
</div>
<p>
在db02上查看vip信息
</p>
<div class="cnblogs_code">
<pre> [root@db02 ~<span style="color: #000000;">]# ip a s eth0
</span><span style="color: #800080;">2</span>: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu <span style="color: #800080;">1500</span> qdisc pfifo_fast state UP qlen <span style="color: #800080;">1000</span><span style="color: #000000;">
link</span>/ether <span style="color: #800080;">00</span>:0c:<span style="color: #800080;">29</span><span style="color: #000000;">:d6:0a:b3 brd ff:ff:ff:ff:ff:ff
inet </span><span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.52</span>/<span style="color: #800080;">24</span> brd <span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.255</span><span style="color: #000000;"> scope global eth0
inet </span><span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.55</span>/<span style="color: #800080;">24</span> brd <span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.255</span> scope global secondary eth0:<span style="color: #800080;"></span><span style="color: #000000;">
inet6 fe80::20c:29ff:fed6:ab3</span>/<span style="color: #800080;">64</span><span style="color: #000000;"> scope link
valid_lft forever preferred_lft forever</span></pre>
</div>
<p>
至此，VIP漂移就测试成功
</p>
<h2>
<span id="16_binlog-server">1.6 配置binlog-server</span>
</h2>
<h3>
<span id="161_binlog-server">1.6.1 配置binlog-server</span>
</h3>
<p>
1）前期准备：
</p>
<div>
<blockquote>
<p class="ad" style="color: #000000;">
1、准备一台新的mysql实例(db03),GTID必须开启。
</p>
<p class="ad" style="color: #000000;">
2、将来binlog接收目录，不能和主库binlog目录一样
</p>
</blockquote>
</div>
<p>
2）停止mha
</p>
<div>
<div class="cnblogs_code">
<pre>masterha_stop --conf=/etc/mha/app1.cnf</pre>
</div>
</div>
<p>
3）在app1.cnf开启binlogserver功能
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #000000;"> [binlog1]
no_master</span>=<span style="color: #800080;">1</span>
<span style="color: #0000ff;">hostname</span>=<span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.53</span> ----><span style="color: #000000;"> 主机DB03
master_binlog_dir</span>=/data/mysql/binlog/ ----> binlog保存目录</pre>
</div>
</div>
<p>
4）开启binlog接收目录，注意权限
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">mkdir</span> -p /data/mysql/binlog/
<span style="color: #0000ff;">chown</span> -R mysql.mysql /data/<span style="color: #000000;">mysql
# 进入目录启动程序
cd </span>/data/mysql/binlog/ &&<span style="color: #000000;">\
mysqlbinlog </span>-R --host=<span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.51</span> --user=mha --password=mha --raw --stop-never mysql-bin.<span style="color: #800080;">000001</span> &</pre>
</div>
</div>
<p>
参数说明：-R 远程主机
</p>
<p>
5）启动mha
</p>
<div>
<div class="cnblogs_code">
<pre>nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/<span style="color: #0000ff;">null</span> > /var/log/mha/app1/manager.log <span style="color: #800080;">2</span>>&<span style="color: #800080;">1</span> &</pre>
</div>
</div>
<h3>
<span id="162_binlog">1.6.2 测试binlog备份</span>
</h3>
<div>
<p>
#查看binlog目录中的binlog
</p>
<div class="cnblogs_code">
<pre><span style="color: #000000;">[root@db03 binlog]# ll
total </span><span style="color: #800080;">44</span>
-rw-r--r-- <span style="color: #800080;">1</span> root root <span style="color: #800080;">285</span> Mar <span style="color: #800080;">8</span> <span style="color: #800080;">03</span>:<span style="color: #800080;">11</span> mysql-bin.<span style="color: #800080;">000001</span></pre>
</div>
<p>
#登录主库
</p>
<div class="cnblogs_code">
<pre>[root@mysql-db01 ~]# mysql -uroot -p123</pre>
</div>
<p>
#刷新binlog
</p>
<div class="cnblogs_code">
<pre>mysql> flush logs;</pre>
</div>
<p>
#再次查看binlog目录
</p>
<div class="cnblogs_code">
<pre><span style="color: #000000;">[root@db03 binlog]# ll
total </span><span style="color: #800080;">48</span>
-rw-r--r-- <span style="color: #800080;">1</span> root root <span style="color: #800080;">285</span> Mar <span style="color: #800080;">8</span> <span style="color: #800080;">03</span>:<span style="color: #800080;">11</span> mysql-bin.<span style="color: #800080;">000001</span>
-rw-r--r-- <span style="color: #800080;">1</span> root root <span style="color: #800080;">143</span> Mar <span style="color: #800080;">8</span> <span style="color: #800080;">04</span>:<span style="color: #800080;">00</span> mysql-bin.<span style="color: #800080;">000002</span></pre>
</div>
</div>
<h2>
<span id="17_mysqlAtlas">1.7 mysql中间件Atlas</span>
</h2>
<h3>
<span id="171_atlas">1.7.1 atlas简介</span>
</h3>
<p>
Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了大量bug，添加了很多功能特性。目前该项目在360公司内部得到了广泛应用，很多MySQL业务已经接入了Atlas平台，每天承载的读写请求数达几十亿条。
</p>
<p>
同时，有超过50家公司在生产环境中部署了Atlas，超过800人已加入了我们的开发者交流群，并且这些数字还在不断增加。而且安装方便。配置的注释写的蛮详细的，都是中文。
</p>
<div>
<p class="a4">
Atlas官方链接： <a href="https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md" target="_blank">https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md</a>
</p>
<p class="a4">
Atlas下载链接：<a href="%20https://github.com/Qihoo360/Atlas/releases" target="_blank"> https://github.com/Qihoo360/Atlas/releases</a>
</p>
</div>
<h3>
<span id="172">1.7.2 主要功能</span>
</h3>
<p>
读写分离、从库负载均衡、自动分表、IP过滤
</p>
<p>
SQL语句黑白名单、DBA可平滑上下线DB、自动摘除宕机的DB
</p>
<p>
<em><span style="text-decoration: underline;">Atlas</span></em><em><span style="text-decoration: underline;">相对于官方MySQL-Proxy</span></em><em><span style="text-decoration: underline;">的优势</span></em>
</p>
<blockquote>
<p style="color: #000000;">
1.将主流程中所有Lua代码用C重写，Lua仅用于管理接口
</p>
<p style="color: #000000;">
2.重写网络模型、线程模型
</p>
<p style="color: #000000;">
3.实现了真正意义上的连接池
</p>
<p>
</p>
<p style="color: #000000;">
4.优化了锁机制，性能提高数十倍
</p>
</blockquote>
<h3>
<span id="173">1.7.3 使用场景</span>
</h3>
<p>
Atlas是一个位于前端应用与后端MySQL数据库之间的中间件，它使得应用程序员无需再关心读写分离、分表等与MySQL相关的细节，可以专注于编写业务逻辑，同时使得DBA的运维工作对前端应用透明，上下线DB前端应用无感知。
</p>
<p align="center">
<img data-original="https://clsn.io/wp-content/uploads/2018/03/1190037-20171230181901679-91103503.png" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" />
</p>
<p>
Atlas是一个位于应用程序与MySQL之间中间件。在后端DB看来，Atlas相当于连接它的客户端，在前端应用看来，Atlas相当于一个DB。
</p>
<p>
Atlas作为服务端与应用程序通讯，它实现了MySQL的客户端和服务端协议，同时作为客户端与MySQL通讯。它对应用程序屏蔽了DB的细节，同时为了降低MySQL负担，它还维护了连接池.
</p>
<h3>
<span id="174">1.7.4 企业读写分离及分库分表其他方案介绍</span>
</h3>
<div>
<blockquote>
<p class="a4">
Mysql-proxy（oracle）
</p>
<p class="a4">
Mysql-router（oracle）
</p>
<p class="a4">
Atlas (Qihoo 360)
</p>
<p class="a4">
Atlas-sharding (Qihoo 360)
</p>
<p class="a4">
Cobar（是阿里巴巴（B2B）部门开发）
</p>
<p class="a4">
Mycat（基于阿里开源的Cobar产品而研发）
</p>
<p class="a4">
TDDL Smart Client的方式（淘宝）
</p>
<p class="a4">
Oceanus(58同城数据库中间件)
</p>
<p class="a4">
OneProxy（原支付宝首席架构师楼方鑫开发 ）
</p>
<p class="a4">
vitess（谷歌开发的数据库中间件）
</p>
<p class="a4">
Heisenberg(百度)
</p>
<p class="a4">
TSharding(蘑菇街白辉)
</p>
<p class="a4">
Xx-dbproxy（金山的Kingshard、当当网的sharding-jdbc ）
</p>
<p class="a4">
amoeba
</p>
</blockquote>
</div>
<h3>
<span id="175_Atlas">1.7.5 安装Atlas</span>
</h3>
<p>
软件获取地址：<a href="https://github.com/Qihoo360/Atlas/releases" target="_blank">https://github.com/Qihoo360/Atlas/releases</a>
</p>
<p>
<em><span style="text-decoration: underline;">注意：</span></em>
</p>
<div>
<blockquote>
<p class="a4">
1、Atlas只能安装运行在64位的系统上
</p>
<p class="a4">
2、Centos 5.X安装 Atlas-XX.el5.x86_64.rpm，Centos 6.X安装Atlas-XX.el6.x86_64.rpm。
</p>
<p class="a4">
3、后端mysql版本应大于5.1，建议使用Mysql 5.6以上
</p>
<p class="a4">
Atlas (普通) : Atlas-2.2.1.el6.x86_64.rpm
</p>
<p class="a4">
Atlas (分表) : Atlas-sharding_1.0.1-el6.x86_64.rpm
</p>
</blockquote>
</div>
<p>
下载安装atlas
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">wget</span> https:<span style="color: #008000;">//</span><span style="color: #008000;">github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm</span>
rpm -ivh Atlas-<span style="color: #800080;">2.2</span>.<span style="color: #800080;">1</span>.el6.x86_64.rpm</pre>
</div>
</div>
<p>
<span style="background-color: #00ff00; color: #ffffff;">至此安装完成</span>
</p>
<h3>
<span id="176_Atlas">1.7.6 配置Atlas配置文件</span>
</h3>
<p>
atlas配置文件中的密码需要加密，可以使用，软件自带的加密工具进行加密
</p>
<div>
<div class="cnblogs_code">
<pre>cd /usr/local/mysql-proxy/conf/
/usr/local/mysql-proxy/bin/encrypt 密码 ---->制作加密密码</pre>
</div>
</div>
<p>
生产密文密码：
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 bin]# /usr/local/mysql-proxy/bin/encrypt <span style="color: #800080;">123</span><span style="color: #000000;">
3yb5jEku5h4</span>=<span style="color: #000000;">
[root@db03 bin]# </span>/usr/local/mysql-proxy/bin/<span style="color: #000000;">encrypt mha
O2jBXONX098</span>=</pre>
</div>
</div>
<p>
编辑配置文件
</p>
<div>
<div class="cnblogs_code">
<pre>vim /usr/local/mysql-proxy/conf/<span style="color: #000000;">test.cnf
[mysql</span>-<span style="color: #000000;">proxy]
admin</span>-username =<span style="color: #000000;"> user
admin</span>-password = <span style="color: #0000ff;">pwd</span><span style="color: #000000;">
proxy</span>-backend-addresses = <span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.55</span>:<span style="color: #800080;">3306</span><span style="color: #000000;">
proxy</span>-read-only-backend-addresses = <span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.52</span>:<span style="color: #800080;">3306</span>,<span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.53</span>:<span style="color: #800080;">3306</span><span style="color: #000000;">
pwds </span>= repl:3yb5jEku5h4=,mha:O2jBXONX098=<span style="color: #000000;">
daemon </span>= <span style="color: #0000ff;">true</span><span style="color: #000000;">
keepalive </span>= <span style="color: #0000ff;">true</span><span style="color: #000000;">
event</span>-threads = <span style="color: #800080;">8</span><span style="color: #000000;">
log</span>-level =<span style="color: #000000;"> message
log</span>-path = /usr/local/mysql-proxy/<span style="color: #000000;">log
sql</span>-log=<span style="color: #000000;">ON
proxy</span>-address = <span style="color: #800080;">0.0</span>.<span style="color: #800080;">0.0</span>:<span style="color: #800080;">33060</span><span style="color: #000000;">
admin</span>-address = <span style="color: #800080;">0.0</span>.<span style="color: #800080;">0.0</span>:<span style="color: #800080;">2345</span><span style="color: #000000;">
charset</span>=utf8</pre>
</div>
</div>
<p>
配置文件内为全中文注释，这里有一份较为详细的解释：
</p>
<div>
<div class="cnblogs_code" onclick="cnblogs_code_show('cc7f89f5-379e-44a4-a5e9-6798f05addfe')">
<img id="code_img_closed_cc7f89f5-379e-44a4-a5e9-6798f05addfe" class="code_img_closed" src="https://clsn.io/wp-content/uploads/2018/03/ContractedBlock-4.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /><img id="code_img_opened_cc7f89f5-379e-44a4-a5e9-6798f05addfe" class="code_img_opened" style="display: none;" onclick="cnblogs_code_hide('cc7f89f5-379e-44a4-a5e9-6798f05addfe',event)" data-original="https://clsn.io/wp-content/uploads/2018/03/ExpandedBlockStart-4.gif" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /></p>
<div id="cnblogs_code_open_cc7f89f5-379e-44a4-a5e9-6798f05addfe" class="cnblogs_code_hide">
<pre><span style="color: #008080;"> 1</span> [mysql-<span style="color: #000000;">proxy]
</span><span style="color: #008080;"> 2</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，默认值即可)管理接口的用户名</span>
<span style="color: #008080;"> 3</span> admin-username =<span style="color: #000000;"> user
</span><span style="color: #008080;"> 4</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，默认值即可)管理接口的密码</span>
<span style="color: #008080;"> 5</span> admin-password =<span style="color: #000000;"> pwd
</span><span style="color: #008080;"> 6</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，根据实际情况配置)主库的IP和端口</span>
<span style="color: #008080;"> 7</span> proxy-backend-addresses = 192.168.0.12:3306
<span style="color: #008080;"> 8</span> <span style="color: #008000;">#</span><span style="color: #008000;">(非必备，根据实际情况配置)从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔。如果想让主库也能分担读请求的话，只需要将主库信息加入到下面的配置项中</span>
<span style="color: #008080;"> 9</span> proxy-read-only-backend-addresses = 192.168.0.13:3306,192.168.0.14:3306
<span style="color: #008080;">10</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，根据实际情况配置)用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，用户名与密码之间用冒号分隔。主从数据库上需要先创建该用户并设置密码（用户名和密码在主从数据库上要一致）。比如用户名为myuser，密码为mypwd，执行./encrypt mypwd结果为HJBoxfRsjeI=。如果有多个用户用逗号分隔即可。则设置如下行所示：</span>
<span style="color: #008080;">11</span> pwds = myuser: HJBoxfRsjeI=,myuser2:HJBoxfRsjeI=
<span style="color: #008080;">12</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，默认值即可)Atlas的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发调试时设为false，线上运行时设为true</span>
<span style="color: #008080;">13</span> daemon =<span style="color: #000000;"> true
</span><span style="color: #008080;">14</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，默认值即可)设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运行时设为true</span>
<span style="color: #008080;">15</span> keepalive =<span style="color: #000000;"> true
</span><span style="color: #008080;">16</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，根据实际情况配置)工作线程数，推荐设置成系统的CPU核数</span>
<span style="color: #008080;">17</span> <span style="color: #008000;">#</span><span style="color: #008000;"> 对性能和正常运行起到重要作用</span>
<span style="color: #008080;">18</span> event-threads = 4
<span style="color: #008080;">19</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，默认值即可)日志级别，分为message、warning、critical、error、debug五个级别</span>
<span style="color: #008080;">20</span> log-level =<span style="color: #000000;"> message
</span><span style="color: #008080;">21</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，默认值即可)日志存放的路径</span>
<span style="color: #008080;">22</span> log-path = /usr/local/mysql-proxy/<span style="color: #000000;">log
</span><span style="color: #008080;">23</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，根据实际情况配置)SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，该模式下日志刷新是基于缓冲区的，当日志填满缓冲区后，才将日志信息刷到磁盘。REALTIME用于调试，代表记录SQL日志且实时写入磁盘，默认为OFF</span>
<span style="color: #008080;">24</span> sql-log =<span style="color: #000000;"> OFF
</span><span style="color: #008080;">25</span> <span style="color: #008000;">#</span><span style="color: #008000;">(可选项，可不设置）慢日志输出设置。当设置了该参数时，则日志只输出执行时间超过sql-log-slow（单位：ms)的日志记录。不设置该参数则输出全部日志。</span>
<span style="color: #008080;">26</span> sql-log-slow = 10
<span style="color: #008080;">27</span> <span style="color: #008000;">#</span><span style="color: #008000;">(可选项，可不设置）关闭不活跃的客户端连接设置。当设置了该参数时，Atlas会主动关闭经过'wait-timeout'时间后一直未活跃的连接。单位：秒</span>
<span style="color: #008080;">28</span> wait-timeout = 10
<span style="color: #008080;">29</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，默认值即可)Atlas监听的工作接口IP和端口；代表客户端应该使用1234这个端口连接Atlas来发送SQL请求。</span>
<span style="color: #008080;">30</span> proxy-address = 0.0.0.0:1234
<span style="color: #008080;">31</span> <span style="color: #008000;">#</span><span style="color: #008000;">(必备，默认值即可)Atlas监听的管理接口IP和端口 ；代表DBA应该使用2345这个端口连接Atlas来执行运维管理操作。</span>
<span style="color: #008080;">32</span> admin-address = 0.0.0.0:2345
<span style="color: #008080;">33</span> <span style="color: #008000;">#</span><span style="color: #008000;">(可选项，可不设置)分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项，子表需要事先建好，子表名称为表名_数字，数字范围为[0,子表数-1]，如本例里，子表名称为mt_0、mt_1、mt_2</span>
<span style="color: #008080;">34</span> tables = person.mt.id.3
<span style="color: #008080;">35</span> <span style="color: #008000;">#</span><span style="color: #008000;">(可选项，可不设置)默认字符集，若不设置该项，则默认字符集为latin1</span>
<span style="color: #008080;">36</span> charset =<span style="color: #000000;"> utf8
</span><span style="color: #008080;">37</span> <span style="color: #008000;">#</span><span style="color: #008000;">(可选项，可不设置)允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接</span>
<span style="color: #008080;">38</span> client-ips = 127.0.0.1, 192.168.1
<span style="color: #008080;">39</span> <span style="color: #008000;">#</span><span style="color: #008000;">(可选项，极少需要)Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置</span>
<span style="color: #008080;">40</span> lvs-ips = 192.168.1.1</pre>
</div>
<p>
<span class="cnblogs_code_collapse">View Code Atlas配置文件说明</span></div> </div>
<h3>
<span id="177_Atlas">1.7.7 启动Atlas</span>
</h3>
<p>
编写一个atlas的管理脚本，当然也可以写脚本，可以直接手动的管理：
</p>
<div>
<div class="cnblogs_code">
<pre>/usr/local/mysql-proxy/bin/mysql-proxyd test start <span style="color: #008000;">#</span><span style="color: #008000;">启动</span>
/usr/local/mysql-proxy/bin/mysql-proxyd test stop <span style="color: #008000;">#</span><span style="color: #008000;">停止</span>
/usr/local/mysql-proxy/bin/mysql-proxyd test restart <span style="color: #008000;">#</span><span style="color: #008000;">重启</span></pre>
</div>
<p>
注意：test是配置文件的名称
</p>
</div>
<p>
脚本内容：
</p>
<div>
<div class="cnblogs_code" onclick="cnblogs_code_show('d45ef69b-9876-450c-bb26-61fbc042600d')">
<img id="code_img_closed_d45ef69b-9876-450c-bb26-61fbc042600d" class="code_img_closed" src="https://clsn.io/wp-content/uploads/2018/03/ContractedBlock-4.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /><img id="code_img_opened_d45ef69b-9876-450c-bb26-61fbc042600d" class="code_img_opened" style="display: none;" onclick="cnblogs_code_hide('d45ef69b-9876-450c-bb26-61fbc042600d',event)" data-original="https://clsn.io/wp-content/uploads/2018/03/ExpandedBlockStart-4.gif" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" /></p>
<div id="cnblogs_code_open_d45ef69b-9876-450c-bb26-61fbc042600d" class="cnblogs_code_hide">
<pre><span style="color: #008080;"> 1</span> [root@db03 ~]# <span style="color: #0000ff;">cat</span> /etc/init.d/<span style="color: #000000;">atlasd
</span><span style="color: #008080;"> 2</span> #!/bin/<span style="color: #0000ff;">sh</span>
<span style="color: #008080;"> 3</span> <span style="color: #000000;">#
</span><span style="color: #008080;"> 4</span> <span style="color: #000000;"># atlas: Atlas Daemon
</span><span style="color: #008080;"> 5</span> <span style="color: #000000;">#
</span><span style="color: #008080;"> 6</span> # chkconfig: - <span style="color: #800080;">90</span> <span style="color: #800080;">25</span>
<span style="color: #008080;"> 7</span> <span style="color: #000000;"># description: Atlas Daemon
</span><span style="color: #008080;"> 8</span> <span style="color: #000000;"># user:clsn
</span><span style="color: #008080;"> 9</span> # Blog: http:<span style="color: #008000;">//</span><span style="color: #008000;">blog.nmtui.com</span>
<span style="color: #008080;">10</span> # Source <span style="color: #0000ff;">function</span><span style="color: #000000;"> library.
</span><span style="color: #008080;">11</span>
<span style="color: #008080;">12</span> Demo=<span style="color: #000000;">test
</span><span style="color: #008080;">13</span>
<span style="color: #008080;">14</span> <span style="color: #000000;">start()
</span><span style="color: #008080;">15</span> <span style="color: #000000;">{
</span><span style="color: #008080;">16</span> <span style="color: #0000ff;">echo</span> -n $<span style="color: #800000;">"</span><span style="color: #800000;">Starting atlas: </span><span style="color: #800000;">"</span>
<span style="color: #008080;">17</span> /usr/local/mysql-proxy/bin/mysql-<span style="color: #000000;">proxyd $Demo start
</span><span style="color: #008080;">18</span> <span style="color: #000000;">}
</span><span style="color: #008080;">19</span> <span style="color: #000000;">stop()
</span><span style="color: #008080;">20</span> <span style="color: #000000;">{
</span><span style="color: #008080;">21</span> <span style="color: #0000ff;">echo</span> -n $<span style="color: #800000;">"</span><span style="color: #800000;">Shutting down atlas: </span><span style="color: #800000;">"</span>
<span style="color: #008080;">22</span> /usr/local/mysql-proxy/bin/mysql-<span style="color: #000000;">proxyd $Demo stop
</span><span style="color: #008080;">23</span> <span style="color: #000000;">}
</span><span style="color: #008080;">24</span> <span style="color: #000000;">status()
</span><span style="color: #008080;">25</span> <span style="color: #000000;">{
</span><span style="color: #008080;">26</span> <span style="color: #0000ff;">echo</span> $<span style="color: #800000;">"</span><span style="color: #800000;">Atlas status: </span><span style="color: #800000;">"</span>
<span style="color: #008080;">27</span> /usr/local/mysql-proxy/bin/mysql-<span style="color: #000000;">proxyd $Demo status
</span><span style="color: #008080;">28</span> <span style="color: #000000;">}
</span><span style="color: #008080;">29</span> <span style="color: #000000;">restart()
</span><span style="color: #008080;">30</span> <span style="color: #000000;">{
</span><span style="color: #008080;">31</span> <span style="color: #0000ff;">echo</span> $<span style="color: #800000;">"</span><span style="color: #800000;">Atlas Restart Info: </span><span style="color: #800000;">"</span>
<span style="color: #008080;">32</span> /usr/local/mysql-proxy/bin/mysql-<span style="color: #000000;">proxyd $Demo restart
</span><span style="color: #008080;">33</span> <span style="color: #000000;">}
</span><span style="color: #008080;">34</span>
<span style="color: #008080;">35</span>
<span style="color: #008080;">36</span> ATLAS=<span style="color: #800000;">"</span><span style="color: #800000;">/usr/local/mysql-proxy/bin/mysql-proxyd</span><span style="color: #800000;">"</span>
<span style="color: #008080;">37</span> [ -f $ATLAS ] || exit <span style="color: #800080;">1</span>
<span style="color: #008080;">38</span> <span style="color: #000000;"># See how we were called.
</span><span style="color: #008080;">39</span> <span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">$1</span><span style="color: #800000;">"</span> <span style="color: #0000ff;">in</span>
<span style="color: #008080;">40</span> <span style="color: #000000;"> start)
</span><span style="color: #008080;">41</span> <span style="color: #000000;"> start
</span><span style="color: #008080;">42</span> <span style="color: #000000;"> ;;
</span><span style="color: #008080;">43</span> <span style="color: #000000;"> stop)
</span><span style="color: #008080;">44</span> <span style="color: #000000;"> stop
</span><span style="color: #008080;">45</span> <span style="color: #000000;"> ;;
</span><span style="color: #008080;">46</span> <span style="color: #000000;"> restart)
</span><span style="color: #008080;">47</span> <span style="color: #000000;"> restart
</span><span style="color: #008080;">48</span> <span style="color: #000000;"> ;;
</span><span style="color: #008080;">49</span> <span style="color: #000000;"> status)
</span><span style="color: #008080;">50</span> <span style="color: #000000;"> status
</span><span style="color: #008080;">51</span> <span style="color: #000000;"> ;;
</span><span style="color: #008080;">52</span> *<span style="color: #000000;">)
</span><span style="color: #008080;">53</span> <span style="color: #0000ff;">echo</span> $<span style="color: #800000;">"</span><span style="color: #800000;">Usage: $0 {start|stop|restart|status}</span><span style="color: #800000;">"</span>
<span style="color: #008080;">54</span> exit <span style="color: #800080;">1</span>
<span style="color: #008080;">55</span> <span style="color: #0000ff;">esac</span>
<span style="color: #008080;">56</span> exit <span style="color: #800080;"></span></pre>
</div>
<p>
<span class="cnblogs_code_collapse">View Code Atas管理脚本</span></div> </div>
<p>
检查端口是否正常
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]# netstat -lntup|<span style="color: #0000ff;">grep</span> mysql-<span style="color: #000000;">proxy
tcp </span><span style="color: #800080;"></span> <span style="color: #800080;"></span> <span style="color: #800080;">0.0</span>.<span style="color: #800080;">0.0</span>:<span style="color: #800080;">33060</span> <span style="color: #800080;">0.0</span>.<span style="color: #800080;">0.0</span>:* LISTEN <span style="color: #800080;">2125</span>/mysql-<span style="color: #000000;">proxy
tcp </span><span style="color: #800080;"></span> <span style="color: #800080;"></span> <span style="color: #800080;">0.0</span>.<span style="color: #800080;">0.0</span>:<span style="color: #800080;">2345</span> <span style="color: #800080;">0.0</span>.<span style="color: #800080;">0.0</span>:* LISTEN <span style="color: #800080;">2125</span>/mysql-proxy</pre>
</div>
</div>
<h3>
<span id="178_Atlas">1.7.8 Atlas管理操作</span>
</h3>
<p>
登入管理接口
</p>
<div>
<div class="cnblogs_code">
<pre>[root@db03 ~]# mysql -uuser -ppwd -h127.<span style="color: #800080;">0.0</span>.<span style="color: #800080;">1</span> -P2345</pre>
</div>
<p class="ad">
查看帮助信息
</p>
<div class="cnblogs_code">
<pre>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> help;</pre>
</div>
<p class="ad">
查看后端的代理库
</p>
<div class="cnblogs_code">
<pre>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> backends;
</span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------+----------------+-------+------+</span>
<span style="color: #808080;">|</span> backend_ndx <span style="color: #808080;">|</span> address <span style="color: #808080;">|</span> state <span style="color: #808080;">|</span> type <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------+----------------+-------+------+</span>
<span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.55</span>:<span style="color: #800000; font-weight: bold;">3306</span> <span style="color: #808080;">|</span> up <span style="color: #808080;">|</span> rw <span style="color: #808080;">|</span>
<span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.52</span>:<span style="color: #800000; font-weight: bold;">3306</span> <span style="color: #808080;">|</span> up <span style="color: #808080;">|</span> ro <span style="color: #808080;">|</span>
<span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.53</span>:<span style="color: #800000; font-weight: bold;">3306</span> <span style="color: #808080;">|</span> up <span style="color: #808080;">|</span> ro <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------+----------------+-------+------+</span>
<span style="color: #800000; font-weight: bold;">3</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</pre>
</div>
<p>
平滑摘除mysql
</p>
<div class="cnblogs_code">
<pre>mysql<span style="color: #808080;">></span> REMOVE BACKEND <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">;
Empty </span><span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</pre>
</div>
<p class="ad">
检查是否摘除
</p>
<div class="cnblogs_code">
<pre>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> backends;
</span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------+----------------+-------+------+</span>
<span style="color: #808080;">|</span> backend_ndx <span style="color: #808080;">|</span> address <span style="color: #808080;">|</span> state <span style="color: #808080;">|</span> type <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------+----------------+-------+------+</span>
<span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.55</span>:<span style="color: #800000; font-weight: bold;">3306</span> <span style="color: #808080;">|</span> up <span style="color: #808080;">|</span> rw <span style="color: #808080;">|</span>
<span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.53</span>:<span style="color: #800000; font-weight: bold;">3306</span> <span style="color: #808080;">|</span> up <span style="color: #808080;">|</span> ro <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------+----------------+-------+------+</span>
<span style="color: #800000; font-weight: bold;">2</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</pre>
</div>
<p>
保存到配置文件中
</p>
<div class="cnblogs_code">
<pre>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">SAVE</span> CONFIG;</pre>
</div>
<p class="ad">
将节点再添加回来
</p>
<div class="cnblogs_code">
<pre>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">add</span> slave <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.52</span>:<span style="color: #800000; font-weight: bold;">3306</span><span style="color: #000000;">;
Empty </span><span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</pre>
</div>
<p class="ad">
查看是否添加成功
</p>
<div class="cnblogs_code">
<pre>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> backends;
</span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------+----------------+-------+------+</span>
<span style="color: #808080;">|</span> backend_ndx <span style="color: #808080;">|</span> address <span style="color: #808080;">|</span> state <span style="color: #808080;">|</span> type <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------+----------------+-------+------+</span>
<span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.55</span>:<span style="color: #800000; font-weight: bold;">3306</span> <span style="color: #808080;">|</span> up <span style="color: #808080;">|</span> rw <span style="color: #808080;">|</span>
<span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.53</span>:<span style="color: #800000; font-weight: bold;">3306</span> <span style="color: #808080;">|</span> up <span style="color: #808080;">|</span> ro <span style="color: #808080;">|</span>
<span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">10.0</span>.<span style="color: #800000; font-weight: bold;">0.52</span>:<span style="color: #800000; font-weight: bold;">3306</span> <span style="color: #808080;">|</span> up <span style="color: #808080;">|</span> ro <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------+----------------+-------+------+</span>
<span style="color: #800000; font-weight: bold;">3</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</pre>
</div>
<p>
保存到配置文件中
</p>
<div class="cnblogs_code">
<pre>mysql> SAVE CONFIG;</pre>
</div>
</div>
<h3>
<span id="179">1.7.9 连接数据库查看负载</span>
</h3>
<p>
通过atlas登陆数据，注意，使用的是数据库上的用户及密码
</p>
<div>
<div class="cnblogs_code">
<pre>shell<span style="color: #808080;">></span> mysql <span style="color: #808080;">-</span>umha <span style="color: #808080;">-</span>pmha <span style="color: #808080;">-</span>h127.<span style="color: #800000; font-weight: bold;">0.0</span>.<span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">-</span>P33060</pre>
</div>
</div>
<p>
第一次查询server_id
</p>
<div>
<div class="cnblogs_code">
<pre>mysql<span style="color: #808080;">></span> show variables <span style="color: #808080;">like</span><span style="color: #000000;"> "server_id";
</span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+-------+</span>
<span style="color: #808080;">|</span> Variable_name <span style="color: #808080;">|</span> Value <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+-------+</span>
<span style="color: #808080;">|</span> server_id <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">53</span> <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+-------+</span>
<span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</pre>
</div>
</div>
<p>
第二次查询server_id
</p>
<div>
<div class="cnblogs_code">
<pre>mysql<span style="color: #808080;">></span> show variables <span style="color: #808080;">like</span><span style="color: #000000;"> "server_id";
</span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+-------+</span>
<span style="color: #808080;">|</span> Variable_name <span style="color: #808080;">|</span> Value <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+-------+</span>
<span style="color: #808080;">|</span> server_id <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">52</span> <span style="color: #808080;">|</span>
<span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+-------+</span>
<span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</pre>
</div>
</div>
<p>
<strong><em>通过上面可以看到负载成功</em></strong>
</p>
<h3>
<span id="1710">1.7.10 读写分离的说明</span>
</h3>
<p>
Atlas会透明的将事务语句和写语句发送至主库执行，读语句发送至从库执行。具体以下语句会在主库执行：
</p>
<p>
显式事务中的语句
</p>
<blockquote>
<p>
autocommit=0时的所有语句
</p>
<p>
含有select GET_LOCK()的语句
</p>
<p>
除SELECT、SET、USE、SHOW、DESC、EXPLAIN外的。
</p>
</blockquote>
<p>
<strong>从库负载均衡配置</strong>
</p>
<div>
<div class="cnblogs_code">
<pre>proxy<span style="color: #808080;">-</span><span style="color: #0000ff;">read</span><span style="color: #808080;">-</span><span style="color: #0000ff;">only</span><span style="color: #808080;">-</span>backend<span style="color: #808080;">-</span>addresses<span style="color: #808080;">=</span>ip1:port1<span style="color: #008000;">@权重</span>,ip2:port2<span style="color: #008000;">@权重</span></pre>
</div>
</div>
<h3>
<span id="1711_Atlas">1.7.11 Atlas高级功能</span>
</h3>
<p>
<span style="background-color: #00ff00;"><strong>自动分表</strong></span>
</p>
<p>
使用Atlas的分表功能时，首先需要在配置文件test.cnf设置tables参数。
</p>
<p>
tables参数设置格式：数据库名.表名.分表字段.子表数量，比如：
</p>
<div>
<blockquote>
<p class="a4">
你的数据库名叫school，表名叫stu，分表字段叫id，总共分为2张表，那么就写为school.stu.id.2，如果还有其他的分表，以逗号分隔即可。
</p>
<p class="a4">
用户需要手动建立2张子表（stu_0,stu_1，注意子表序号是从0开始的）。
</p>
<p class="a4">
所有的子表必须在DB的同一个database里。
</p>
<p class="a4">
当通过Atlas执行（SELECT、DELETE、UPDATE、INSERT、REPLACE）操作时，Atlas会根据分表结果（id%2=k），定位到相应的子表（stu_k）。
</p>
<p class="a4">
例如，执行select * from stu where id=3;，Atlas会自动从stu_1这张子表返回查询结果。
</p>
<p class="a4">
但如果执行SQL语句（select * from stu;）时不带上id，则会提示执行stu表不存在。
</p>
</blockquote>
</div>
<p>
<span style="background-color: #00ff00;">Atles功能的说明</span>
</p>
<div>
<blockquote>
<p class="a4">
Atlas暂不支持自动建表和跨库分表的功能。
</p>
<p class="a4">
Atlas目前支持分表的语句有SELECT、DELETE、UPDATE、INSERT、REPLACE。
</p>
</blockquote>
</div>
<p>
<span style="background-color: #00ff00;"><strong>IP</strong><strong>过滤：client-ips</strong></span>
</p>
<p>
该参数用来实现IP过滤功能。
</p>
<p>
在传统的开发模式中，应用程序直接连接DB，因此DB会对部署应用的机器(比如web服务器)的IP作访问授权。
</p>
<p>
在引入中间层后，因为连接DB的是Atlas，所以DB改为对部署Atlas的机器的IP作访问授权，如果任意一台客户端都可以连接Atlas，就会带来潜在的风险。
</p>
<p>
client-ips参数用来控制连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔写在一行上即可。
</p>
<p>
如： <span class="cnblogs_code">client<span style="color: #808080;">-</span>ips<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">192.168</span>.<span style="color: #800000; font-weight: bold;">1.2</span>, <span style="color: #800000; font-weight: bold;">192.168</span>.<span style="color: #800000; font-weight: bold;">2</span></span>
</p>
<div>
<p class="a4">
这就代表192.168.1.2这个IP和192.168.2.*这个段的IP可以连接Atlas，其他IP均不能连接。如果该参数不设置，则任意IP均可连接Atlas。如果设置了client-ips参数，且Atlas前面挂有LVS，则必须设置lvs-ips参数，否则可以不设置lvs-ips。
</p>
</div>
<p>
<strong>SQL</strong><strong>语句黑白名单功能：</strong> Atlas会屏蔽不带where条件的delete和update操作，以及sleep函数。
</p>
<h2>
<span id="18_Atlas-Sharding">1.8 Atlas-Sharding版本</span>
</h2>
<h3>
<span id="181">1.8.1 版本介绍</span>
</h3>
<p>
Sharding的基本思想就是把一个数据表中的数据切分成多个部分, 存放到不同的主机上去(切分的策略有多种), 从而缓解单台机器的性能跟容量的问题.
</p>
<p>
sharding是一种水平切分, 适用于单表数据庞大的情景. 目前atlas支持静态的
</p>
<p>
sharding方案, 暂时不支持数据的自动迁移以及数据组的动态加入.
</p>
<p>
Atlas以表为单位sharding, 同一个数据库内可以同时共有sharding的表和不sharding的表, 不sharding的表数据存在未sharding的数据库组中.
</p>
<p>
目前Atlas sharding支持insert, delete, select, update语句, 只支持不跨shard的事务. 所有的写操作如insert, delete, update只能一次命中一个组, 否则会报"ERROR 1105 (HY000):write operation is only allow to one dbgroup!"错误.
</p>
<p>
由于sharding取替了Atlas的分表功能, 所以在Sharding分支里面, Atlas单机分表的功能已经移除, 配置tables将不会再有效.
</p>
<h3>
<span id="182_Atlas-Sharding">1.8.2 Atlas-Sharding架构</span>
</h3>
<p align="center">
<img data-original="https://clsn.io/wp-content/uploads/2018/03/1190037-20171230183135851-1350555351.png" src="/wp-content/themes/clsn-003/img/blank.gif" alt="MHA高可用架构与Atlas读写分离" alt="" />
</p>
<h3>
<span id="183_Sharding">1.8.3 Sharding配置示例</span>
</h3>
<p>
Atlas支持非sharding跟sharding的表共存在同一个Atlas中, 2.2.1之前的配置可以直接运行. 之前的配置如
</p>
<div>
<div class="cnblogs_code">
<pre>proxy<span style="color: #808080;">-</span>backend<span style="color: #808080;">-</span>addresses <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">192.168</span>.<span style="color: #800000; font-weight: bold;">0.12</span>:<span style="color: #800000; font-weight: bold;">3306</span><span style="color: #000000;">
proxy</span><span style="color: #808080;">-</span><span style="color: #0000ff;">read</span><span style="color: #808080;">-</span><span style="color: #0000ff;">only</span><span style="color: #808080;">-</span>backend<span style="color: #808080;">-</span>addresses <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">192.168</span>.<span style="color: #800000; font-weight: bold;">0.13</span>:<span style="color: #800000; font-weight: bold;">3306</span>,<span style="color: #800000; font-weight: bold;">192.168</span>.<span style="color: #800000; font-weight: bold;">0.14</span>:<span style="color: #800000; font-weight: bold;">3306</span> ... </pre>
</div>
<p>
这配置了一个master和两个slave,这属于非sharding的组, 所有非sharding的表跟语句都会发往这个组内.
</p>
<p>
所以之前没有Sharding的Atlas的表可以无缝的在新版上使用,
</p>
</div>
<p>
<span style="background-color: #00ff00;"><strong>注意:</strong> </span>非Sharding的组只能配置一个, 而sharding的组可以配置多个. 下面的配置, 配置了Sharding的组, 注意与上面的配置区分
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #ff0000;">[</span><span style="color: #ff0000;">shardrule-0</span><span style="color: #ff0000;">]</span>
<span style="color: #0000ff;">table</span> <span style="color: #808080;">=</span> test.sharding_test</pre>
</div>
</div>
<p>
分表名，有数据库+表名组成 t
</p>
<div>
<div class="cnblogs_code">
<pre>ype <span style="color: #808080;">=</span> range</pre>
</div>
</div>
<p>
sharding类型：range 或 hash
</p>
<div>
<div class="cnblogs_code">
<pre>shard<span style="color: #808080;">-</span><span style="color: #0000ff;">key</span> <span style="color: #808080;">=</span> id</pre>
</div>
</div>
<p>
sharding 字段
</p>
<div>
<div class="cnblogs_code">
<pre>groups <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;"></span>:<span style="color: #800000; font-weight: bold;"></span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">999</span>,<span style="color: #800000; font-weight: bold;">1</span>:<span style="color: #800000; font-weight: bold;">1000</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1999</span></pre>
</div>
</div>
<p>
分片的group，如果是range类型的sharding，则groups的格式是：group_id:id范围。如果是hash类型的sharding，则groups的格式是：group_id。例如groups = 0, 1
</p>
<div>
<div class="cnblogs_code">
<pre><span style="color: #ff0000;">[</span><span style="color: #ff0000;">group-0</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
proxy</span><span style="color: #808080;">-</span>backend<span style="color: #808080;">-</span>addresses<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">192.168</span>.<span style="color: #800000; font-weight: bold;">0.15</span>:<span style="color: #800000; font-weight: bold;">3306</span><span style="color: #000000;">
proxy</span><span style="color: #808080;">-</span><span style="color: #0000ff;">read</span><span style="color: #808080;">-</span><span style="color: #0000ff;">only</span><span style="color: #808080;">-</span>backend<span style="color: #808080;">-</span>addresses<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">192.168</span>.<span style="color: #800000; font-weight: bold;">0.16</span>:<span style="color: #800000; font-weight: bold;">3306</span>
<span style="color: #ff0000;">[</span><span style="color: #ff0000;">group-1</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
proxy</span><span style="color: #808080;">-</span>backend<span style="color: #808080;">-</span>addresses<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">192.168</span>.<span style="color: #800000; font-weight: bold;">0.17</span>:<span style="color: #800000; font-weight: bold;">3306</span><span style="color: #000000;">
proxy</span><span style="color: #808080;">-</span><span style="color: #0000ff;">read</span><span style="color: #808080;">-</span><span style="color: #0000ff;">only</span><span style="color: #808080;">-</span>backend<span style="color: #808080;">-</span>addresses<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">192.168</span>.<span style="color: #800000; font-weight: bold;">0.18</span>:<span style="color: #800000; font-weight: bold;">3306</span></pre>
</div>
</div>
<h3>
<span id="184_Sharding">1.8.4 Sharding限制</span>
</h3>
<p>
<span style="background-color: #00ff00;"><strong>关于支持的语句</strong></span>
</p>
<p>
Atlas sharding只对sql语句提供有限的支持, 目前支持基本的Select, insert/replace, delete,update语句,支持全部的Where语法(SQL-92标准), 不支持DDL(create drop alter)以及一些管理语句,DDL请直连MYSQL执行, 请只在Atlas上执行Select, insert, delete, update(CRUD)语句.
</p>
<p>
对于以下语句, 如果语句命中了多台dbgroup, Atlas均未做支持(如果语句只命中了一个dbgroup, 如 select count(*) from test where id < 1000, 其中dbgroup0范围是0 - 1000, 那么这些特性都是支持的)Limit Offset(支持Limit)
</p>
<blockquote>
<p>
Order by
</p>
<div>
<p class="ad">
Group by Join
</p>
<p class="ad">
ON
</p>
<p class="ad">
Count, Max, Min等函数
</p>
</div>
</blockquote>
<p>
<span style="background-color: #00ff00;"><strong>增加节点</strong></span>
</p>
<p>
注意: 暂时只支持range方式的节点扩展, hash方式由于需要数据迁移, 暂时未做支持.
</p>
<p>
扩展节点在保证原来节点的范围不改变的情况下, 如已有dbgroup0为范围0 - 999, dbgroup1为范围 1000-1999, 这个时候可以增加范围>2000的节点. 如增加一个节点为2000 - 2999, 修改配置文件, 重启Atlas即可.
</p>
<h2>
<span id="19">1.9 参考文献</span>
</h2>
<div>
<blockquote>
<p class="a4">
[1] http://www.cnblogs.com/cenalulu/p/4309009.html
</p>
<p class="a4">
[2] https://yq.aliyun.com/articles/57731
</p>
<p class="a4">
[3] https://www.jianshu.com/p/b68e429d09c7
</p>
<p class="a4">
[4] https://www.cnblogs.com/yyhh/archive/2015/12/29/5084844.html
</p>
<p class="a4">
[5] http://blog.csdn.net/jhq0113/article/details/44302703
</p>
<p class="a4">
[6] http://blog.csdn.net/jhq0113/article/details/44239823
</p>
<p class="a4">
[7] https://www.guokr.com/blog/475765/
</p>
<p class="a4">
[8] http://blog.csdn.net/bluishglc/article/details/6161475/
</p>
</blockquote>
</div>
<p>
</p>
<div id="toc_container" class="toc_white have_bullets">
<ul class="toc_list">
<li>
<a href="#11_MHA">1.1 MHA简介</a><ul>
<li>
<a href="#111_MHA">1.1.1 MHA软件介绍</a>
</li>
<li>
<a href="#112_MHA">1.1.2 MHA工作原理</a>
</li>
<li>
<a href="#113_MHA">1.1.3 MHA高可用架构图</a>
</li>
<li>
<a href="#114_MHA">1.1.4 MHA工具介绍</a>
</li>
<li>
<a href="#115_MHA">1.1.5 MHA的优点</a>
</li>
</ul>
</li>
<li>
<a href="#12">1.2 环境说明</a><ul>
<li>
<a href="#121">1.2.1 系统环境说明</a>
</li>
<li>
<a href="#122_mysql">1.2.2 mysql软件说明</a>
</li>
</ul>
</li>
<li>
<a href="#13_GTID">1.3 基于GTID的主从复制配置</a><ul>
<li>
<a href="#131">1.3.1 先决条件</a>
</li>
<li>
<a href="#132">1.3.2 配置主从复制</a>
</li>
<li>
<a href="#133_GTID">1.3.3 GTID复制技术说明</a>
</li>
<li>
<a href="#134_COM_BINLOG_DUMP_GTID">1.3.4 COM_BINLOG_DUMP_GTID</a>
</li>
<li>
<a href="#135_MySQL_GTID">1.3.5 【示例二】MySQL GTID复制配置</a>
</li>
</ul>
</li>
<li>
<a href="#14_MHA">1.4 部署MHA</a><ul>
<li>
<a href="#141">1.4.1 环境准备（所有节点操作）</a>
</li>
<li>
<a href="#142_mha-manager">1.4.2 部署管理节点（mha-manager）</a>
</li>
<li>
<a href="#143_mha">1.4.3 启动mha</a>
</li>
<li>
<a href="#144_master">1.4.4 切换master测试</a>
</li>
<li>
<a href="#145">1.4.5 设置权重</a>
</li>
</ul>
</li>
<li>
<a href="#15_VIP">1.5 配置VIP漂移</a><ul>
<li>
<a href="#151_IP">1.5.1 IP漂移的两种方式</a>
</li>
<li>
<a href="#152_MHA">1.5.2 MHA脚本方式</a>
</li>
<li>
<a href="#153_IP">1.5.3 测试虚拟IP漂移</a>
</li>
</ul>
</li>
<li>
<a href="#16_binlog-server">1.6 配置binlog-server</a><ul>
<li>
<a href="#161_binlog-server">1.6.1 配置binlog-server</a>
</li>
<li>
<a href="#162_binlog">1.6.2 测试binlog备份</a>
</li>
</ul>
</li>
<li>
<a href="#17_mysqlAtlas">1.7 mysql中间件Atlas</a><ul>
<li>
<a href="#171_atlas">1.7.1 atlas简介</a>
</li>
<li>
<a href="#172">1.7.2 主要功能</a>
</li>
<li>
<a href="#173">1.7.3 使用场景</a>
</li>
<li>
<a href="#174">1.7.4 企业读写分离及分库分表其他方案介绍</a>
</li>
<li>
<a href="#175_Atlas">1.7.5 安装Atlas</a>
</li>
<li>
<a href="#176_Atlas">1.7.6 配置Atlas配置文件</a>
</li>
<li>
<a href="#177_Atlas">1.7.7 启动Atlas</a>
</li>
<li>
<a href="#178_Atlas">1.7.8 Atlas管理操作</a>
</li>
<li>
<a href="#179">1.7.9 连接数据库查看负载</a>
</li>
<li>
<a href="#1710">1.7.10 读写分离的说明</a>
</li>
<li>
<a href="#1711_Atlas">1.7.11 Atlas高级功能</a>
</li>
</ul>
</li>
<li>
<a href="#18_Atlas-Sharding">1.8 Atlas-Sharding版本</a><ul>
<li>
<a href="#181">1.8.1 版本介绍</a>
</li>
<li>
<a href="#182_Atlas-Sharding">1.8.2 Atlas-Sharding架构</a>
</li>
<li>
<a href="#183_Sharding">1.8.3 Sharding配置示例</a>
</li>
<li>
<a href="#184_Sharding">1.8.4 Sharding限制</a>
</li>
</ul>
</li>
<li>
<a href="#19">1.9 参考文献</a>
</li>
</ul>
</div>- 原文作者：惨绿少年
- 原文链接：https://clsn.io/clsn/lx290.html
- 版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。