MySQL Fabric高可用配置

23ewrw

1、架构说明

主机	IP	作用
node10	192.168.1.209	MySQL主机
node09	192.168.1.208	MySQL从机
node08	192.168.1.207	MySQL从机
node06	192.168.1.205	Fabric主机，MySQL 5.7

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

2、下载工具包

1 2 3 4 5 6

mkdir soft cd soft wget http://dev.mysql.com/get/Downloa ... .3-1.el6.x86_64.rpm wget http://dev.mysql.com/get/Downloa ... .6-1.el6.noarch.rpm wget http://downloads.mysql.com/archi ... 6_64.rpm-bundle.tar tar -xvf mysql-5.7.9-1.el6.x86_64.rpm-bundle.tar

3、安装Fabric工具包

1 2 3 4 5 6 7 8 9

[iyunv@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%] [iyunv@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

1 2 3 4 5 6 7 8 9 10 11 12 13

[iyunv@node06 soft]# rpm -qa|grep mysql |xargs -i rpm -e --nodeps {}; [iyunv@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、创建本地库

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

创建数据目录 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  ] 查看密码 [iyunv@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*'); 创建帐号及数据库 [iyunv@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配置文件

1 2 3 4 5 6 7 8 9 10 11

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、初始化库

1 2 3 4 5 6 7 8 9

[iyunv@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、查询数据库

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

[iyunv@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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

[iyunv@node06 soft]# mysqlfabric manage start & [1] 2711 [iyunv@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、创建成员组

1 2 3 4 5 6 7 8 9 10 11 12 13

[iyunv@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管理帐号到各数据库上

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

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

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

12、添加成员

1 2 3

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、查看成员组信息

1 2 3 4 5 6 7 8 9

[iyunv@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、自动选主

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

[iyunv@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、查看信息

1 2 3 4 5 6 7 8 9

[iyunv@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组

1	mysqlfabric group activate  mydb_group

17、测试HA

1 2	[iyunv@node10 ~]# service mysqld stop Stopping mysqld:                                           [  OK  ]

1 2 3 4 5 6 7 8 9

[iyunv@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、恢复节点

1 2	[iyunv@node10 ~]# service mysqld start Starting mysqld:                                           [  OK  ]

1 2 3 4 5 6 7 8 9

[iyunv@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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

[iyunv@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). [iyunv@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).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[iyunv@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、更新主机

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

[iyunv@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). [iyunv@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还是不错的，但做为中间件性能就......