MySQL Fabric高可用配置

1、架构说明

注：node10\node09\node08的主从已经配置过程略过

2、下载工具包

mkdir soft
cd soft
wget http://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.3-1.el6.x86_64.rpm
wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.6-1.el6.noarch.rpm
wget http://downloads.mysql.com/archives/get/file/mysql-5.7.9-1.el6.x86_64.rpm-bundle.tar
tar -xvf mysql-5.7.9-1.el6.x86_64.rpm-bundle.tar

3、安装Fabric工具包

[root@node06 soft]# rpm -ivh mysql-connector-python-2.1.3-1.el6.x86_64.rpm 
warning: mysql-connector-python-2.1.3-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-connector-python ########################################### [100%]

[root@node06 soft]# rpm -ivh mysql-utilities-1.5.6-1.el6.noarch.rpm 
warning: mysql-utilities-1.5.6-1.el6.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-utilities        ########################################### [100%]

4、安装MySQL

[root@node06 soft]# rpm -qa|grep mysql |xargs -i rpm -e --nodeps {};
[root@node06 soft]# rpm -ivh mysql-community-*
warning: mysql-community-client-5.7.9-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-common ########################################### [ 11%]
   2:mysql-community-libs   ########################################### [ 22%]
   3:mysql-community-client ########################################### [ 33%]
   4:mysql-community-server ########################################### [ 44%]
   5:mysql-community-devel  ########################################### [ 56%]
   6:mysql-community-embedde########################################### [ 67%]
   7:mysql-community-embedde########################################### [ 78%]
   8:mysql-community-test   ########################################### [ 89%]
   9:mysql-community-libs-co########################################### [100%]

5、创建本地库

创建数据目录
mkdir -p  /u01/mysql
修改配置文件
vi /etc/my.cnf
[mysqld]
datadir=/u01/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

初始化库
 mysqld --initialize --datadir=/u01/mysql 

启动数据库
service mysqld restart
Stopping mysqld:                                           [  OK  ]
Initializing MySQL database:                               [  OK  ]
Starting mysqld:                                           [  OK  ]

查看密码
[root@node06 soft]# cat /var/log/mysqld.log |grep root
2016-03-15T14:54:17.104028Z 1 [Note] A temporary password is generated for root@localhost: %FoohqpBe5UK

登陆数据库修改密码
mysql -uroot -p‘%FoohqpBe5UK‘
mysql>  set password=password(‘Fabric123*‘);


创建帐号及数据库
[root@node06 soft]# mysql -uroot -p‘Fabric123*‘
mysql> create database fabric;
mysql> grant all on fabric.* to fabric@‘%‘ identified by ‘Fabricdb123*‘;
mysql> grant all on fabric.* to fabric@‘localhost‘ identified by ‘Fabricdb123*‘;
mysql> drop database fabric;

6、修改Fabric配置文件

vi /etc/mysql/fabric.cfg
.....
[storage]
auth_plugin = mysql_native_password
database = fabric
user = fabric
address = localhost:3306
connection_delay = 1
connection_timeout = 6
password = Fabricdb123*
.....

7、初始化库

[root@node06 soft]# mysqlfabric manage setup --param=storage.user=fabric 
[INFO] 1458056916.001109 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc: 
Repeat Password: 
Password set.
Password set.

8、查询数据库

[root@node06 soft]# mysql -uroot -p‘Fabric123*‘
mysql> use fabric;show tables;
Database changed
+-------------------+
| Tables_in_fabric  |
+-------------------+
| checkpoints       |
| error_log         |
| group_replication |
| group_view        |
| groups            |
| log               |
| machines          |
| permissions       |
| proc_view         |
| providers         |
| role_permissions  |
| roles             |
| servers           |
| shard_maps        |
| shard_ranges      |
| shard_tables      |
| shards            |
| user_roles        |
| users             |
+-------------------+
19 rows in set (0.00 sec)

9、启动FABRIC

[root@node06 soft]# mysqlfabric manage start &
[1] 2711
[root@node06 mysql]# [INFO] 1458060656.261079 - MainThread - Initializing persister: user (fabric), server (192.168.1.205:3306), database (fabric).
[WARNING] 1458060656.272060 - MainThread - Provider error: No module named novaclient.
[INFO] 1458060656.272060 - MainThread - Loading Services.
[INFO] 1458060656.288384 - MainThread - MySQL-RPC protocol server started, listening on localhost:32275
[INFO] 1458060656.304173 - MainThread - Fabric node version (1.5.6) started. 
[INFO] 1458060656.307733 - MainThread - Starting Executor.
[INFO] 1458060656.307843 - MainThread - Setting 5 executor(s).
[INFO] 1458060656.308641 - Executor-0 - Started.
[INFO] 1458060656.311089 - Executor-1 - Started.
[INFO] 1458060656.312136 - Executor-2 - Started.
[INFO] 1458060656.315085 - Executor-3 - Started.
[INFO] 1458060656.318284 - MainThread - Executor started.
[INFO] 1458060656.323093 - MainThread - Starting failure detector.
[INFO] 1458060656.323338 - Executor-4 - Started.
[INFO] 1458060656.328053 - XML-RPC-Server - XML-RPC protocol server (‘0.0.0.0‘, 32274) started.
[INFO] 1458060656.328361 - XML-RPC-Server - Setting 5 XML-RPC session(s).
[INFO] 1458060656.329761 - XML-RPC-Session-0 - Started XML-RPC-Session.
[INFO] 1458060656.330760 - XML-RPC-Session-1 - Started XML-RPC-Session.
[INFO] 1458060656.334350 - XML-RPC-Session-2 - Started XML-RPC-Session.
[INFO] 1458060656.337683 - XML-RPC-Session-3 - Started XML-RPC-Session.
[INFO] 1458060656.342705 - XML-RPC-Session-4 - Started XML-RPC-Session.

10、创建成员组

[root@node06 soft]# mysqlfabric group create mydb_group
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
8a41bc19-dc88-465c-84ad-4ef0c534193f        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.45806e+09 Triggered by <mysql.fabric.events.Event object at 0x195ad90>.
    4       2   1.45806e+09                             Executing action (_create_group).
    5       2   1.45806e+09                              Executed action (_create_group).

11、创建Fabric管理帐号到各数据库上

[root@node10 ~]# mysql -uroot -p123456 -e "grant all on *.* to fabric@‘192.168.1.%‘ identified by ‘Server*01‘"

[root@node09 ~]# mysql -uroot -p123456 -e "grant all on *.* to fabric@‘192.168.1.%‘ identified by ‘Server*01‘"

[root@node08 ~]# mysql -uroot -p123456 -e "grant all on *.* to fabric@‘192.168.1.%‘ identified by ‘Server*01‘"

12、添加成员

 mysqlfabric group add mydb_group 192.168.1.209:13306
 mysqlfabric group add mydb_group 192.168.1.208:13306
 mysqlfabric group add mydb_group 192.168.1.207:13306

13、查看成员组信息

[root@node06 soft]# mysqlfabric group lookup_servers mydb_group
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid             address    status      mode weight
------------------------------------ ------------------- --------- --------- ------
19e625e0-ea04-11e5-b192-0800273bba44 192.168.1.208:13306 SECONDARY READ_ONLY    1.0
5592ca61-ae26-11e5-8586-08002732e007 192.168.1.209:13306 SECONDARY READ_ONLY    1.0
7cb26cac-ea9d-11e5-a9a1-080027d9fe9c 192.168.1.207:13306 SECONDARY READ_ONLY    1.0

14、自动选主

[root@node06 soft]# mysqlfabric group promote mydb_group
[INFO] 1458061339.876909 - Executor-0 - Master has changed from 7cb26cac-ea9d-11e5-a9a1-080027d9fe9c to None.
[INFO] 1458061340.060467 - Executor-0 - Master has changed from None to 5592ca61-ae26-11e5-8586-08002732e007.
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
a9a9486b-0775-431b-80a7-83f8428c1d5c        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.45806e+09 Triggered by <mysql.fabric.events.Event object at 0x1781190>.
    4       2   1.45806e+09                      Executing action (_define_ha_operation).
    5       2   1.45806e+09                       Executed action (_define_ha_operation).
    3       2   1.45806e+09 Triggered by <mysql.fabric.events.Event object at 0x18dd810>.
    4       2   1.45806e+09                    Executing action (_find_candidate_switch).
    5       2   1.45806e+09                     Executed action (_find_candidate_switch).
    3       2   1.45806e+09 Triggered by <mysql.fabric.events.Event object at 0x18dd850>.
    4       2   1.45806e+09                   Executing action (_check_candidate_switch).
    5       2   1.45806e+09                    Executed action (_check_candidate_switch).
    3       2   1.45806e+09 Triggered by <mysql.fabric.events.Event object at 0x18dd890>.
    4       2   1.45806e+09                       Executing action (_block_write_switch).
    5       2   1.45806e+09                        Executed action (_block_write_switch).
    3       2   1.45806e+09 Triggered by <mysql.fabric.events.Event object at 0x18dd8d0>.
    4       2   1.45806e+09                       Executing action (_wait_slaves_switch).
    5       2   1.45806e+09                        Executed action (_wait_slaves_switch).
    3       2   1.45806e+09 Triggered by <mysql.fabric.events.Event object at 0x18dd910>.
    4       2   1.45806e+09                      Executing action (_change_to_candidate).
    5       2   1.45806e+09                       Executed action (_change_to_candidate).

15、查看信息

[root@node06 soft]# mysqlfabric group lookup_servers mydb_group
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid             address    status       mode weight
------------------------------------ ------------------- --------- ---------- ------
19e625e0-ea04-11e5-b192-0800273bba44 192.168.1.208:13306 SECONDARY  READ_ONLY    1.0
5592ca61-ae26-11e5-8586-08002732e007 192.168.1.209:13306   PRIMARY READ_WRITE    1.0
7cb26cac-ea9d-11e5-a9a1-080027d9fe9c 192.168.1.207:13306 SECONDARY  READ_ONLY    1.0

16、激活HA组

mysqlfabric group activate  mydb_group

17、测试HA

[root@node10 ~]# service mysqld stop
Stopping mysqld:                                           [  OK  ]

[root@node06 ~]# mysqlfabric group lookup_servers mydb_group
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid             address    status       mode weight
------------------------------------ ------------------- --------- ---------- ------
19e625e0-ea04-11e5-b192-0800273bba44 192.168.1.208:13306 SECONDARY  READ_ONLY    1.0
5592ca61-ae26-11e5-8586-08002732e007 192.168.1.209:13306    FAULTY READ_WRITE    1.0
7cb26cac-ea9d-11e5-a9a1-080027d9fe9c 192.168.1.207:13306   PRIMARY READ_WRITE    1.0

18、恢复节点

[root@node10 ~]# service mysqld start
Starting mysqld:                                           [  OK  ]

[root@node06 ~]# mysqlfabric group lookup_servers mydb_group
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid             address    status       mode weight
------------------------------------ ------------------- --------- ---------- ------
19e625e0-ea04-11e5-b192-0800273bba44 192.168.1.208:13306 SECONDARY  READ_ONLY    1.0
5592ca61-ae26-11e5-8586-08002732e007 192.168.1.209:13306    FAULTY READ_WRITE    1.0
7cb26cac-ea9d-11e5-a9a1-080027d9fe9c 192.168.1.207:13306   PRIMARY READ_WRITE    1.0

[root@node06 ~]# mysqlfabric server set_status 5592ca61-ae26-11e5-8586-08002732e007 SPARE
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
49450b84-e382-48f2-9cd9-029cf808490f        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.45815e+09 Triggered by <mysql.fabric.events.Event object at 0x130c510>.
    4       2   1.45815e+09                        Executing action (_set_server_status).
    5       2   1.45815e+09                         Executed action (_set_server_status).


[root@node06 ~]# mysqlfabric server set_status 5592ca61-ae26-11e5-8586-08002732e007 SECONDARY
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
58c4b09f-415f-4735-b95b-967fb4e564a8        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.45815e+09 Triggered by <mysql.fabric.events.Event object at 0x130c510>.
    4       2   1.45815e+09                        Executing action (_set_server_status).
    5       2   1.45815e+09                         Executed action (_set_server_status).

[root@node10 ~]# mysql -uroot -pServer01 -e "show slave status\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.207
                  Master_User: fabric
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mybin.000001
          Read_Master_Log_Pos: 446
               Relay_Log_File: iZ23o48zbfjZ-relay-bin.000002
                Relay_Log_Pos: 402
        Relay_Master_Log_File: mybin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

20、更新主机

[root@node06 ~]# mysqlfabric group promote mydb_group
[INFO] 1458147024.997307 - Executor-3 - Master has changed from 7cb26cac-ea9d-11e5-a9a1-080027d9fe9c to None.
[INFO] 1458147025.166686 - Executor-3 - Master has changed from None to 5592ca61-ae26-11e5-8586-08002732e007.
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
57f98367-a2bd-4531-9c9d-4804a3aaeb7a        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.45815e+09 Triggered by <mysql.fabric.events.Event object at 0x1123150>.
    4       2   1.45815e+09                      Executing action (_define_ha_operation).
    5       2   1.45815e+09                       Executed action (_define_ha_operation).
    3       2   1.45815e+09 Triggered by <mysql.fabric.events.Event object at 0x12807d0>.
    4       2   1.45815e+09                    Executing action (_find_candidate_switch).
    5       2   1.45815e+09                     Executed action (_find_candidate_switch).
    3       2   1.45815e+09 Triggered by <mysql.fabric.events.Event object at 0x1280810>.
    4       2   1.45815e+09                   Executing action (_check_candidate_switch).
    5       2   1.45815e+09                    Executed action (_check_candidate_switch).
    3       2   1.45815e+09 Triggered by <mysql.fabric.events.Event object at 0x1280850>.
    4       2   1.45815e+09                       Executing action (_block_write_switch).
    5       2   1.45815e+09                        Executed action (_block_write_switch).
    3       2   1.45815e+09 Triggered by <mysql.fabric.events.Event object at 0x1280890>.
    4       2   1.45815e+09                       Executing action (_wait_slaves_switch).
    5       2   1.45815e+09                        Executed action (_wait_slaves_switch).
    3       2   1.45815e+09 Triggered by <mysql.fabric.events.Event object at 0x12808d0>.
    4       2   1.45815e+09                      Executing action (_change_to_candidate).
    5       2   1.45815e+09                       Executed action (_change_to_candidate).


[root@node06 ~]# mysqlfabric group lookup_servers mydb_group
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid             address    status       mode weight
------------------------------------ ------------------- --------- ---------- ------
19e625e0-ea04-11e5-b192-0800273bba44 192.168.1.208:13306 SECONDARY  READ_ONLY    1.0
5592ca61-ae26-11e5-8586-08002732e007 192.168.1.209:13306   PRIMARY READ_WRITE    1.0
7cb26cac-ea9d-11e5-a9a1-080027d9fe9c 192.168.1.207:13306 SECONDARY  READ_ONLY    1.0

注：Fabric HA还是不错的，但做为中间件性能就......

本文出自 “” 博客，请务必保留此出处