Loading... <h2>MySQL Fabric </h2> <h3><span style="font-weight: normal">是一个用于管理 MySQL 服务器群的可扩展框架。该框架实现了两个特性 — 高可用性 (HA) 以及使用数据分片的横向扩展。这两个特性既可以单独使用,也可以结合使用。</span></h3> <h2></h2> <ul class="toc-indentation list-paddingleft-2"> <li> <p><span style="color:#3b73af">环境:</span></p> </li> <li> <p><span style="color:#3b73af">安装MySQL Fabric</span></p> </li> <li> <p><span style="color:#3b73af">配置MySQL Fabric</span></p> </li> <li> <p><span style="color:#3b73af">MySQL Fabric 安装管理数据库fabric</span></p> </li> <li> <p><span style="color:#3b73af">启动MySQL Fabric</span></p> </li> <li> <p><span style="color:#3b73af">创建Fabric高可用组mysql_ha</span></p> </li> <li> <p><span style="color:#3b73af">添加mysql_ha组添加节点</span></p> </li> <li> <p><span style="color:#3b73af">自动推举一个节点为主库</span></p> </li> <li> <p><span style="color:#3b73af">查看mysql_ha组节点服务器</span></p> </li> <li> <p><span style="color:#3b73af">测试mysql_ha组高可用</span></p> </li> <li> <p><span style="color:#3b73af">恢复192.168.1.101并设置为从库</span></p> </li> <li> <p><span style="color:#3b73af">查看mysql_ha组状态</span></p> </li> <li> <p><span style="color:#3b73af">使用python连接MySQL Fabric</span></p> </li> <li> <p><span style="color:#3b73af">运行python-fabric.py</span></p> </li> <li> <p><span style="color:#3b73af">使用PHP连接MySQL Fabric</span></p> </li> <li> <p><span style="color:#3b73af">MySQL Fabric 常用操作</span></p> </li> </ul> <p></p> <h2>环境:</h2> <p></p> <p></p> <table class="confluenceTable"> <tbody> <tr class="firstRow"> <td class="highlight-blue confluenceTd">名称</td> <td class="highlight-blue confluenceTd">IP</td> <td class="highlight-blue confluenceTd">端口</td> <td class="highlight-blue confluenceTd">软件</td> <td class="highlight-blue confluenceTd">备注</td> </tr> <tr> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">MySQL Fabric</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">192.168.1.100</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">3306</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">MySQL5.6.24、mysql-utilities-1.5.4</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">Fabric管理</td> </tr> <tr> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">MySQL主服务器</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">192.168.1.101</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">3306</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">MySQL5.6.24</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">节点</td> </tr> <tr> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">MySQL从服务器</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">192.168.1.102</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">3306</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">MySQL5.6.24</td> <td class="confluenceTd" style="padding-top: 7px;padding-bottom: 7px;vertical-align: top">节点</td> </tr> </tbody> </table> <p> </p> <p>注:Fabric基于MySQL GTID主从复制,需要安装MySQL 5.6以上版本。</p> <p>GTID特点是很方便在1主多从环境下,根据GITD号方便切换主从关系。</p> <p></p> <p><img src="//cto.wang/usr/uploads/2016/07/20160703163945-86.png" title="1464838481863798.png" alt="mysql_f.png" /></p> <p>详细请参考官方文档</p> <p>http://dev.mysql.com/doc/mysql-utilities/1.6/en/fabric.html</p> <p>主从复制安装部署忽略</p> <h2 id="MySQLFabric安装部署-安装MySQLFabric"></h2> <h2>安装MySQL Fabric</h2> <p>#wget http://mirrors.sohu.com/mysql/MySQLGUITools/mysql-utilities-1.5.4.tar.gz</p> <p>#tar zxvf mysql-utilities-1.5.4.tar.gz</p> <p>#cd mysql-utilities-1.5.4</p> <p>#python ./setup.py build</p> <p>#python ./setup.py install</p> <p>注:Fabric只需安装在192.168.1.100服务器上</p> <h1><strong>配置MySQL Fabric</strong></h1> <p><strong>建立管理账号</strong></p> <p>#mysql>grant all on *.* to ‘fabric’@’192.168.1.100’identified by ‘pass’;</p> <p>注:此账号需要在192.168.1.100、192.168.1.101、192.168.1.102服务器创建。</p> <p> </p> <p><strong>修改MySQL配置文件(my.cnf)</strong></p> <p>gtid-mode = on</p> <p>enforce-gtid-consistency = true</p> <p>log_slave_updates = true</p> <p>注:需要修改192.168.1.101、192.168.1.102服务器</p> <p> </p> <p><strong>修改MySQL Fabric 配置文件</strong></p> <p>#vi /etc/mysql/fabric.cfg</p> <p>[storage]</p> <p>address = 192.168.1.100:3306</p> <p>user = fabric</p> <p>password = pass</p> <p>database = fabric</p> <p>auth_plugin = mysql_native_password</p> <p>connection_timeout = 6</p> <p>connection_attempts = 6</p> <p>connection_delay = 1</p> <p> </p> <p>[servers]</p> <p>user = fabric</p> <p>password = pass</p> <p>unreachable_timeout = 5</p> <p> </p> <h2 id="MySQLFabric安装部署-MySQLFabric安装管理数据库fabric"><strong>MySQL Fabric 安装管理数据库fabric</strong></h2> <p>#mysqlfabric manage setup</p> <p>[INFO] 1408115689.486792 – MainThread -Initializing persister: user (fabric), server (192.168.1.100:3306), database(fabric).</p> <p>Finishing initial setup</p> <p>=======================</p> <p>Password for admin user is not yet set.</p> <p>Password for admin/xmlrpc:</p> <p>Repeat Password:</p> <p>Password set.</p> <p>安装期间需要设置fabric管理密码。</p> <p>安装完成后。会生成一个fabric管理数据库</p> <h2 id="MySQLFabric安装部署-启动MySQLFabric"><strong>启动MySQL Fabric</strong></h2> <p>#mysqlfabric manage start –daemonize</p> <p>注:参数—daemonize 为后台启动</p> <h2 id="MySQLFabric安装部署-创建Fabric高可用组mysql_ha"><strong>创建Fabric高可用组mysql_ha</strong></h2> <p>#mysqlfabric group create mysql_ha</p> <h2 id="MySQLFabric安装部署-添加mysql_ha组添加节点"><strong>添加mysql_ha组添加节点</strong></h2> <p>#mysqlfabric group add my_group192.168.1.101:3306</p> <p>#mysqlfabric group add my_group192.168.1.102:3306</p> <h2 id="MySQLFabric安装部署-自动推举一个节点为主库"><strong>自动推举一个节点为主库</strong></h2> <p>#mysqlfabric group promote mysql_ha</p> <h2 id="MySQLFabric安装部署-查看mysql_ha组节点服务器"><strong>查看mysql_ha组节点服务器</strong></h2> <p>#mysqlfabric group lookup_servers mysql_ha</p> <p>Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e</p> <p>Time-To-Live: 1</p> <p> </p> <p> server_uuid address status mode weight</p> <p>——————————————————– ——— ———- ——</p> <p>1135f441-f867-11e4-9bfc-000c296cbb9a192.168.1.101:3306 PRIMARY READ_WRITE S 1.0</p> <p>13f32739-f867-11e4-9bfc-000c2957c804192.168.1.102:3306 SECONDARY READ_ONLY 1.0</p> <h2 id="MySQLFabric安装部署-测试mysql_ha组高可用"><strong>测试mysql_ha组高可用</strong></h2> <p>将192.168.1.101 主库关闭。在查看mysql_ha组节点服务器状态</p> <p>#mysqlfabric group lookup_servers mysql_ha</p> <p>Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e</p> <p>Time-To-Live: 1</p> <p> server_uuid address status mode weight</p> <p>——————————————————– ——— ———- ——</p> <p>1135f441-f867-11e4-9bfc-000c296cbb9a192.168.1.101:3306 FAULTY READ_WRITE 1.0</p> <p>13f32739-f867-11e4-9bfc-000c2957c804192.168.1.102:3306 PRIMARY READ_WRITE S 1.0</p> <p> </p> <p>注:Fabric自动将从库192.168.1.102设置为主库,主库192.168.1.101状态为FAULTY</p> <h2 id="MySQLFabric安装部署-恢复192.168.1.101并设置为从库"><strong>恢复192.168.1.101并设置为从库</strong></h2> <p>#mysqlfabric server set_status 1135f441-f867-11e4-9bfc-000c296cbb9a spare</p> <p>#mysqlfabric server set_status 1135f441-f867-11e4-9bfc-000c296cbb9asecondary</p> <h2 id="MySQLFabric安装部署-查看mysql_ha组状态"><strong>查看mysql_ha组状态</strong></h2> <p>#mysqlfabric group lookup_servers mysql_ha</p> <p>Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e</p> <p>Time-To-Live: 1</p> <p> server_uuid address status mode weight</p> <p>——————————————————– ——— ———- ——</p> <p>1135f441-f867-11e4-9bfc-000c296cbb9a192.168.1.101:3306 SECONDARY READ_ONLY 1.0</p> <p>13f32739-f867-11e4-9bfc-000c2957c804192.168.1.102:3306 PRIMARY READ_WRITE S 1.0</p> <p>注:192.168.1.101状态已从FAULTY变为 SECONDARY</p> <h2 id="MySQLFabric安装部署-使用python连接MySQLFabric"><strong>使用python连接MySQL Fabric</strong></h2> <p>#vi python-fabric.py</p> <p>#!/usr/bin/env python </p> <p>import sys</p> <p>import mysql.connector </p> <p>from mysql.connector import fabric </p> <p> </p> <p>def main(): </p> <p> try: </p> <p> conn = mysql.connector.connect(</p> <p> fabric ={"host":"localhost","port":32274,"username":"admin","password":"pass"},</p> <p> user = 'fabric',database = 'test',password = 'fabric',autocommit = True</p> <p> ) </p> <p> exceptException,e: </p> <p> print e </p> <p> sys.exit(1) </p> <p> else: </p> <p> conn.set_property(mode=fabric.MODE_READWRITE,group="mysql_ha")</p> <p> cur = conn.cursor() </p> <p> cur.execute( </p> <p> "CREATE TABLE IF NOT EXISTS fabrictest ("</p> <p> " sub_no INT," </p> <p> " first_name CHAR(40),"</p> <p> " last_name CHAR(4)" </p> <p> ")" </p> <p> ) </p> <p> finally: </p> <p> conn.close() </p> <p>if __name__ == '__main__': </p> <p>main() </p> <p>注:注意修改脚本里面参数。</p> <h2 id="MySQLFabric安装部署-运行python-fabric.py"><strong>运行python-fabric.py</strong></h2> <p>#python python-fabric.py</p> <p>运行成功会在mysql主服务器(192.168.1.101)test数据库里创建fabrictest表。</p> <p>mysql> show tables from test;</p> <p>+——————–+</p> <p>| Tables_in_test |</p> <p>+——————–+</p> <p>| fabrictest |</p> <p>+——————–+</p> <h2 id="MySQLFabric安装部署-使用PHP连接MySQLFabric"><strong>使用PHP连接MySQL Fabric</strong></h2> <p>请参考PHP官方说明</p> <p>http://php.net/manual/zh/mysqlnd-ms.quickstart.mysql_fabric.php</p> <p>注:PHP目前对MySQLFabric 支持不太好。MySQL官方文档也没介绍PHP如何连接MySQL Fabric。</p> <h2 id="MySQLFabric安装部署-MySQLFabric常用操作"><strong>MySQL Fabric 常用操作</strong></h2> <p><strong>创建组</strong></p> <p>mysqlfabric group create my_group</p> <p> </p> <p><strong>添加节点</strong></p> <p>mysqlfabric group add my_group192.168.223.128:3306</p> <p>mysqlfabric group add my_group192.168.223.135:3306</p> <p> </p> <p><strong>删除</strong></p> <p>注:不能删除主数据库节点,要先降级</p> <p>mysqlfabric group demote my_group</p> <p>mysqlfabric group remove my_group1135f441-f867-11e4-9bfc-000c296cbb9a</p> <p> </p> <p><strong>自动推举一个主</strong></p> <p>mysqlfabric group promote my_group</p> <p> </p> <p><strong>手动设置一个主</strong></p> <p>mysqlfabric group promote my_group–slave_id=1135f441-f867-11e4-9bfc-000c296cbb9a –update_only</p> <p> </p> <p><strong>自动切换主从</strong></p> <p>mysqlfabric group activate my_group</p> <p> </p> <p><strong>禁用自动切换主从</strong></p> <p>mysqlfabric group deactivate my_group</p> <p> </p> <p>设置节点状态primary,secondary,spare,faulty</p> <p>mysqlfabric server set_status 13f32739-f867-11e4-9bfc-000c2957c804 spare</p> <p> </p> <p>MySQL服务器恢复,如果MySQL从服务器处于faulty(故障)状态。需要先设置为spare,在设置为secondary (从)</p> <p>mysqlfabric group demote my_group #将my_group组降级</p> <p>mysqlfabric server set_status 1135f441-f867-11e4-9bfc-000c296cbb9a spare</p> <p>mysqlfabric server set_status 13f32739-f867-11e4-9bfc-000c2957c804secondary</p> <p> </p> <p>MySQL服务器恢复,如果MySQL主服务器处于faulty(故障)状态。需要先设置为spare,在手动设置主</p> <p>mysqlfabric group demote my_group #将my_group组降级</p> <p>mysqlfabric server set_status 1135f441-f867-11e4-9bfc-000c296cbb9a spare,</p> <p>mysqlfabric group promote my_group–slave_id=1135f441-f867-11e4-9bfc-000c296cbb9a –update_only</p> <p> </p> <p><strong>查看</strong></p> <p>mysqlfabric group lookup_servers my_group</p> <p>mysqlfabric group health my_group</p> <p> </p> <p><strong>启动mysqlfabric</strong></p> <p>mysqlfabric manage start</p> <p> </p> <p><strong>后台启动mysqlfabric</strong></p> <p>mysqlfabric manage start –daemonize</p> <p> </p> <p><strong>停止mysqlfabric</strong></p> <p>mysqlfabric manage stop</p> <p> </p> <p><strong>查看mysqlfabric 状态</strong></p> <p>mysqlfabric manage ping</p> <p></p> <p>原文:https://www.chinasa.net/archives/155.html</p> 最后修改:2021 年 12 月 10 日 10 : 53 AM © 允许规范转载 赞赏 如果觉得我的文章对你有用,请随意赞赏 赞赏作者 支付宝微信