MySQL Fabric is a tool included on MySQL Utilities that helps you to manage your MySQL instances.
It works by basically adding a new layer between your application and MySQL instances, which can provide an easy way to use sharding and build a highly available system.
To install our Fabric environment, we will have to configure 4 servers, I will use the follow names and IP on this tutorial:
fabric1 (192.168.0.200) - fabric mysql1 (192.168.0.201) - mysql master mysql2 (192.168.0.202) - mysql slave mysql3 (192.168.0.203) - mysql slave
Note: I’m running CentOS 6.5 on all servers.
1. Add mysql repo on all 4 machines, please read Installing latest version of MySQL via yum for more info:
rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum update
2. Install mysql mysql-server mysql-utilities:
yum install mysql mysql-server mysql-utilities
chkconfig mysqld on
/etc/init.d/mysqld start
3. On mysql1,mysql2,mysql3 add the follow to my.cnf:
[mysqld] ... binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 report-host=192.168.0.201 report-port=3306 server-id=1 log-bin=mysql1-bin.log
4. On mysql1 add a replication user for each mysql ip:
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd';
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd'; " [root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd'; " [root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd'; "
5. On mysql1 add the privileges to fabric user from fabric node ip:
GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';
[root@mysql1 ~]# mysql -u root -e "GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';"
6. On mysql2 and mysql3, setup replication:
CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;
7. On fabric1, add the fabric MySQL user:
GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';
[root@fabric1 ~]# mysql -u root -e "GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';"
8. On fabric1,, configure user and password on [storage] and [servers] group on /etc/mysql/fabric.cfg :
[storage] ... password = fabricpwd ... [servers] password = reppwd user = replication
9. On fabric1, start the fabric db, it will ask to create a password, this password will be used on all next mysqlfabric commands:
mysqlfabric manage setup
[root@fabric1 ~]# mysqlfabric manage setup [INFO] 1406131468.176740 - 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.
10. On fabric1, start fabric:
mysqlfabric manage start &
11. On fabric1,, add a group:
mysqlfabric group create GLOBAL1
[root@fabric1 ~]# mysqlfabric group create GLOBAL1 Password for admin: Procedure : { uuid = 5e4a6bdb-60f0-4e34-87ba-4c56b7616b35, finished = True, success = True, return = True, activities = }
12. On fabric1,, add mysql1, mysql2 and mysql3 to GLOBAL1 group:
mysqlfabric group add GLOBAL1 192.168.0.201
mysqlfabric group add GLOBAL1 192.168.0.202
mysqlfabric group add GLOBAL1 192.168.0.203
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.201 Password for admin: Procedure : { uuid = 39efb9c4-6195-4c41-aa02-0bfdc228bfe2, finished = True, success = True, return = True, activities = } [root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.202 Password for admin: Procedure : { uuid = c8babfb9-d836-44c0-b4fd-015cd1df8298, finished = True, success = True, return = True, activities = } [root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.203 Password for admin: Procedure : { uuid = c86bba70-69ac-4923-9c54-1a8aaab6d97e, finished = True, success = True, return = True, activities = }
13. On fabric1, get the uuid of your master:
mysqlfabric group lookup_servers GLOBAL1
[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1 Password for admin: Command : { success = True return = [{'status': 'SECONDARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}] activities = }
14. On fabric1, add your master as master on GLOBAL1 group:
mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'
[root@fabric1 ~]# mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242' Password for admin: [WARNING] 1406131951.712366 - Executor-2 - Error () trying to process transactions in the relay log for candidate (('Command (START SLAVE SQL_THREAD, ()) failed: 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO', 1200)). [INFO] 1406131951.824763 - Executor-2 - Master has changed from None to 2e157d1e-1281-11e4-80dc-080027aa0242. Procedure : { uuid = 733ae69d-fb12-447b-b86b-041703491315, finished = True, success = True, return = True, activities = } [root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1 Password for admin: Command : { success = True return = [{'status': 'PRIMARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}] activities = } [root@fabric1 ~]#
That is it, we now have our MySQL Fabric environment working and ready.
Watch out for my next few posts to learn more about MySQL Fabric.
You may find my blog post interesting.
http://www.mysqlfanboy.com/2012/02/the-full-monty-version-2-3/
It’s a little old but it may include some things you haven’t considered.
I have created fabric node and a group with 3 nodes 1 master and 2 slave. Replication started working and are in sync.
I am able to create the Fabric and added the 3 nodes to fabric group.
I am able to see the servers in lookup_servers list. But I am unable to promote the Master server.
C:Program FilesMySQLMySQL Utilities>mysqlfabric group promote group1 –slave_id=’0f5f05f7-5460-11e4-ae63-c03fd535fd47′
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
ServerError: Server (‘0f5f05f7-5460-11e4-ae63-c03fd535fd47’) does not exist.
C:Program FilesMySQLMySQL Utilities>mysqlfabric group health group1
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
uuid is_alive status is_not_running is_not_configured io_not_running sql_not_running io_error sql_error
———————————— ——– ——— ————– —————– ————– ————— ——– ———
0f5f05f7-5460-11e4-ae63-c03fd535fd47 1 SECONDARY 0 1 0 0 False False
26f249d5-5453-11e4-ae0f-c03fd535fd47 1 SECONDARY 0 0 0 0 False False
28ca6364-5453-11e4-ae0f-c03fd535fd47 1 SECONDARY 0 0 0 0 False False
issue
————————————————————————————————-
Group has master (None) but server is connected to master (0f5f05f7-5460-11e4-ae63-c03fd535fd47).
Group has master (None) but server is connected to master (0f5f05f7-5460-11e4-ae63-c03fd535fd47).
Any inputs/suggestions on this.
my master node and slave node not working
master node does not have a port using ‘3306’
i changed my my.cnf port= 3306.
i don’t know why this happen.
mysql server defualt port = 3306
slave node same problem ….
plz help me ….