mysql fabric基础测试

rtre

IP规划

主：192.168.1.2，

从：192.168.1.4，

管理主机：192.168.1.5

管理主机要安装mysql-connector-python和mysql-utilities

Mysql-python连接器下载地址：http://dev.mysql.com/downloads/connector/python/

rpm -ivh mysql-connector-python-2.0.4-1.el6.src.rpm

# wget -c http://dev.mysql.com/get/Downloa ... utilities-1.5.3.zip
# unzip mysql-utilities-1.5.3.zip
# cd mysql-utilities-1.5.3
# python ./setup.py build
# python ./setup.py install

在三台数据库执行

GRANT ALL ON fabric.* TO fabric@'%' IDENTIFIED BY 'fabric@123';

FLUSH PRIVILEGES;

在管理主机执行
vi /etc/mysql/fabric.cfg
[DEFAULT]
prefix =
sysconfdir = /etc
logdir = /var/log

[statistics]
prune_time = 3600

[logging]
url = file:///var/log/fabric.log
level = INFO

[storage]
auth_plugin = mysql_native_password
database = fabric
user = fabric
address = localhost:3306
connection_delay = 1
connection_timeout = 6
password = fabric@123
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 = fabric@123
user = fabric
unreachable_timeout = 5

[connector]
ttl = 1

[client]
password =

[protocol.xmlrpc]
disable_authentication = no
ssl_cert =
realm = MySQL Fabric
ssl_key =
ssl_ca =
threads = 5
user = admin
address = localhost:32274
password =

[executor]
executors = 5

[sharding]
prune_limit = 10000
mysqldump_program = /usr/bin/mysqldump
mysqlclient_program = /usr/bin/mysql

[protocol.mysql]
disable_authentication = no
ssl_cert =
ssl_key =
ssl_ca =
user = admin
address = localhost:32275
password = abc123

填充Backing Store信息

我们通过执行以下命令来生成fabric数据库，具体如下：

mysqlfabric manage setup --param=storage.user=fabric

Finishing initial setup

=======================

Password for admin user is not yet set.

Password for admin/xmlrpc:

Repeat Password:

Password set.

Password set.

No result returned

管理密码设置为admin

填加HA组的成员
mysqlfabric group add mygroup 192.168.1.2:3306

因为binlog已经配置了，怀疑是gtid没有配置的问题，传统的主从复制机制已经不适合这个应用场景
现在配置gtid
在主库配置，注意主从的server-id不能一样
在my.cnf添加
gtid_mode=ON
log-bin
log-slave-updates
enforce-gtid-consistency

执行命令
grant replication slave,replication client on *.* to slave@'%'identified by 'itvgd123';
flush privileges
再从库上配置
gtid_mode=ON
log-bin
log-slave-updates
enforce-gtid-consistency

执行命令
change master to master_host='192.168.1.2', master_user='slave',master_password='itvgd123', master_auto_position=1;
flush privileges；
start slave;
show slave status \G;
配置了gtid主从后该报错消失
再次添加节点


根据报错信息百度不出结果，用天行浏览器谷歌，找到
http://fossies.org/linux/mysql-utilities/mysql/fabric/server.py

找到mysql-utilities的源码了
初步看了下


猜测和mysql 的uuid有关，猛然想起，几个数据库是拷贝过去的，改了uuid后重新提添加，添加成功
mysqlfabric group add mygroup 192.168.1.2:3306




mysqlfabric group lookup_servers mygroup查看组成员信息

选举一个主库
mysqlfabric group promote mygroup

激活故障自动切换

mysqlfabric group activate mygroup

切换测试
现在192.168.1.2是从库，192.168.1.4是主库
现在我们把192.168.1.4停掉
service mysqld stop
查看mysql集群状态
mysqlfabric group lookup_servers mygroup

我们看到192.168.1.4已经被认为处于失败状态，192.168.1.2已经被提升为主库
现在把192.168.1.4开起来

可见即便是192.168.1.4重新启动他的转态依然不会改回原先正常的转态
需要手动修改他的状态
mysqlfabric server set_status aca4f313-59a1-11e5-95ff-000c29397430 spare

再次修改状态
mysqlfabric server set_status aca4f313-59a1-11e5-95ff-000c29397430 secondary

常用相关命令

mysqlfabricgroup create mygroup          #创建HA组

mysqlfabricgroup destroy mygroup        #删除HA组

mysqlfabricgroup add mygroup 192.168.247.132:3306     #添加组成员

mysqlfabricgroup remove mygroup c505ce10-9bc0-11e4-bfd4-000c290e7abe   #移出组成员

mysqlfabricgroup lookup_servers mygroup  #查看组成员

mysqlfabricgroup promote mygroup      #选举master

mysqlfabric groupactivate mygroup        #激活自动故障转移

mysqlfabric group deactivatemygroup           #禁用自动故障转移

mysqlfabric serverset_status server_uuid status #变更服务器状态

mysqlfabrichelp manage   #manage命令帮助

mysqlfabrichelp group     #group命令帮助

mysqlfabrichelp server      #server命令帮助

总结：HA支持的状态值为primary,secondary,spare,faulty。它在HA组中的状态不会自动恢复，需要手动改变服务器状态。