MySQL Fabric - Installing / Instalar | MySQL by Marcelo Altmann

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.

Liked? Help and Share!

MySQL Fabric – Part 1 – Installing