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 high available system.
For More information about what is MySQL Fabric, please follow the documentation.
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.
