Mysql高可用 - Fabric安装配置

ikjhgf

Fabric简介

mysql fabric是oracle推出的，它可以简化管理mysql集群，提供两大特性：
1  通过故障检测和故障转移提供高可用
2  通过自动数据分片实现可扩展性

Fabric前提条件

• MySQL server 5.6.10 or later for Fabric MySQL servers.（fabric是基于mysql 5.6的新特性gtid实现的）
• MySQL server 5.6.x or later for the backing store.
• Python 2 (2.6 or later) for the mysqlfabric utility.
• A Fabric-aware connector to use Fabric in applications. Permitted connectors and versions are:
  
  
  • Connector/Python 1.2.1 or later
  • Connector/J 5.1.27 or later
    
    

以上都是mysql fabric官网介绍的

Fabric所需安装包
server节点：
MySQL-server-5.6.21-1.el6.x86_64  # mysql server包，安装这个包需要先卸载mysql-libs这个包
MySQL-client-5.6.21-1.el6.x86_64  # mysql client包，可以不装，没装就没有mysql命令行命令了

MySQL-shared-5.6.21-1.el6.x86_64

MySQL-shared-compat-5.6.21-1.el6.x86_64  # 卸载了mysql-libs包会有一些依赖问题，安装MySQL-shared这两个包可以解决依赖

client节点：
MySQL-client-5.6.21-1.el6.x86_64  # mysql client包，可以不装，没装就没有mysql命令行命令了
mysql-connector-python-2.0.1-1.el6.noarch # app连接fabric的驱动，跟MySQL-python这个包不一样


fabric节点：
mysql-connector-python-2.0.1-1.el6.noarch # app连接fabric的驱动，fabric节点可以不装
mysql-utilities-1.5.2-1.el6.noarch # mysqlfabric包含在这个包里
MySQL-server-5.6.21-1.el6.x86_64  # mysql server包，安装这个包需要先卸载mysql-libs这个包

MySQL-client-5.6.21-1.el6.x86_64  # mysql client包，可以不装，没装就没有mysql命令行命令了

MySQL-shared-5.6.21-1.el6.x86_64

MySQL-shared-compat-5.6.21-1.el6.x86_64  # 卸载了mysql-libs包会有一些依赖问题，安装MySQL-shared这两个包可以解决依赖

Fabric具体配置
我的环境：
linux系统  centos 6.5 x86_64
controller1  192.168.141.110  server节点
controller2  192.168.141.120  server节点
controller3  192.168.141.130  fabric节点

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 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107

[iyunv@controller3 ~]# mysql -uroot -p   # fabric节点需要在backing store mysql server上创建一个fabric database，这里fabric节点和backing store mysql server在同一台机器上 mysql> create user 'fabric'@'localhost' identified by 'fabric'  # 连接backing store mysql server认证的用户 mysql> grant all on fabric.* to 'fabric'@'localhost' [iyunv@controller3 ~]# mysqlfabric help commands  # 显示所有的mysqlfabric命令 [iyunv@controller3 ~]# cd /etc/mysql/   # 编辑fabric.cfg配置文件 [iyunv@controller3 mysql]# cp fabric.cfg fabric.cfg.bak  # 备份fabric.cfg配置文件 [iyunv@controller3 mysql]# vim fabric.cfg [DEFAULT] prefix = sysconfdir = /etc  # 配置文件目录 logdir = /var/log  #日志目录 [statistics]  # How often the internal event log is pruned, in seconds and also the age of events in the event log that is used to present statistics. prune_time = 3600 [logging] url = file:///var/log/fabric.log level = INFO  # 日志级别 [storage] auth_plugin = mysql_native_password database = fabric #连接backing store mysql server的数据库名 user = fabric  # 连接backing store mysql server fabric数据库的用户名 address = localhost:3306  # backing store mysql server的地址和端口，localhost说明backing store mysql server和fabric节点在同一个机器上 connection_delay = 1   connection_timeout = 6 password = fabric # 连接backing store mysql server fabric数据库的密码 connection_attempts = 6   [failure_tracking] notification_interval = 60 notification_clients = 50 detection_timeout = 1 detection_interval = 6 notifications = 300 detections = 3 failover_interval = 0 prune_time = 3600 [servers] password = oracle   # fabric节点连接mysql HAgroup中的server认证的密码 user = oracle # fabric节点连接mysql HAgroup中的server认证的用户名 unreachable_timeout = 5 [connector] ttl = 1 [client]  # This section is used by the mysql client when called from MySQL Fabric and is not used MySQL Fabric. password = oracle [protocol.xmlrpc] # This section contains information about how the client connects to a MySQL Fabric node and configuration parameters for the XML-RPC protocol on the server disable_authentication = no ssl_cert = realm = MySQL Fabric ssl_key = ssl_ca = threads = 5 user = admin address = controller3:32274 password = admin [executor]  # The executor executes procedures in a serial order, which guarantees that requests do not conflict. executors = 5 [sharding]  # To perform operations such as moving and splitting shards, MySQL Fabric relies on the mysqldump and mysqlclient programs. prune_limit = 10000 mysqldump_program = /usr/bin/mysqldump   mysqlclient_program = /usr/bin/mysql [protocol.mysql]  # 官方文档上我没找到这个的解释，我想跟protocol.xmlrpc应该是一样是类似的吧 disable_authentication = no ssl_cert = ssl_key = ssl_ca = user = admin address = localhost:32275 password = admin # fabric节点的my.cnf配置文件 [iyunv@controller3 mysql]# cat /etc/my.cnf [mysqld] bind_address = localhost datadir=/var/lib/mysql collation-server = utf8_general_ci init-connect = 'SET NAMES utf8' character-set-server = utf8 default-storage-engine = innodb innodb_file_per_table = 1 innodb_buffer_pool_size = 512M log_bin                        gtid_mode=on   enforce_gtid_consistency=on   log_slave_updates=1 # 初始化fabric数据库 [iyunv@controller3 mysql]# mysqlfabric manage setup --param=storage.user=fabric --param=storage.password=fabric  #如果报错见下面的Trouble Shooting # 成功执行，会让你输入admin用户的密码 [iyunv@controller3] mysqlfabric manage start --daemonize # 启动fabric管理系统 [iyunv@controller3] mysqlfabric group create mysql_group # 创建管理组mysql_group [iyunv@controller3] mysqlfabric group lookup_groups  # 查看管理组信息 # 在每个mysql server添加到管理组mysql_group之前，需要在每个mysql server上创建授权用户 [iyunv@controller1 ~]# mysql -uroot -e "grant all on *.* to oracle@'%' identified by 'oracle';" [iyunv@controller2 ~]# mysql -uroot -e "grant all on *.* to oracle@'%' identified by 'oracle';" [iyunv@controller3] mysqlfabric group add mysql_group controller1:3306 [iyunv@controller3] mysqlfabric group add mysql_group controller2:3306  # 添加完节点后，查看组信息，两个都为secondary，记得在/etc/hosts文件写controller1、从controller2的ip映射 [iyunv@controller3] mysqlfabric group lookup_servers mysql_group [iyunv@controller3] mysqlfabric group health mysql_group  # 这两种方式都可以查看节点状态信息 [iyunv@controller3] mysqlfabric group promote mysql_group # promote之后，fabric会选举出一台作为primary , demote是取消primary选举 [iyunv@controller3] mysqlfabric group activate mysql_group  #  激活失败检测 [iyunv@controller3 ~]# mysqlfabric group lookup_servers mysql_group Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1                          server_uuid          address    status       mode weight ------------------------------------ ---------------- --------- ---------- ------ 086193ff-4f7f-11e4-8e93-525400788967 controller2:3306 SECONDARY  READ_ONLY    1.0 0c826bd0-4f8c-11e4-8ee8-5254003d38c9 controller1:3306   PRIMARY READ_WRITE    2.0

客户端通过Fabric connector连接操作就可，可是在openstack中客户端这部分连接操作的代码还没实现，还有fabric节点也要做高可用，希望oracle能尽快改进。

Trouble Shooting
1   初始化fabric数据库mysqlfabric manage setup --param=storage.user=fabric --param=storage.password=fabric执行时可能报错如下：
Error: Command (CREATE TABLE machines (machine_uuid VARCHAR(40) NOT NULL, provider_id VARCHAR(256) NOT NULL, av_zone VARCHAR(256), addresses TEXT, INDEX idx_machine_provider_id (provider_id)), ()) failed accessing (localhost:3306). 1071 (42000): Specified key was too long; max key length is 767 bytes.
原因：MySQL的varchar主键只支持不超过768个字节 或者 768/2=384个双字节 或者 768/3=256个三字节的字段 而 GBK是双字节的，UTF-8是三字节的。


解决：
[iyunv@controller3 mysql]# vim /etc/my.cnf  # 编辑my.cnf文件，先注释掉下面这些utf-8的配置选项,重启mysql服务，然后再去初始化fabric数据库，初始化完毕后，再去掉注释。

1 2 3 4

[mysqld] #collation-server = utf8_general_ci #init-connect = 'SET NAMES utf8' #character-set-server = utf8

2  当数据库某个节点故障后重新恢复，需要先将节点变成spare，再切换为seconary，这个真让人蛋疼，没有自动恢复的机制

1 2 3 4

[iyunv@controller3] mysqlfabric server set_status 086193ff-4f7f-11e4-8e93-525400788967 SPARE [iyunv@controller3] mysqlfabric server set_status 086193ff-4f7f-11e4-8e93-525400788967 SECONDARY

这是fabric管理系统的server状态切换图(http://mysqlmusings.blogspot.fr/ ... ability-groups.html)，看这个应该都懂了吧！

参考链接
http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html