MySQL Fabric - Installing / Instalar | MySQL by Marcelo Altmann

Send to Kindle

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.

Send to Kindle

http://www.mysqlfanboy.com Mark Grennan

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.
Kiran mk

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.

MySQL Fabric – Part 1 – Installing

Recent Posts

Categories