facebook twitter youtube
Replication Query Optimization DB Tuning Server Load and Query Locking Linux Download Interview Questions Basic DB Commands Mysql
in Replication - 17 Mar, 2016
by Zafar Malik - no comments
Easy steps to setup MySQL Fabric (HA with Master-Slave even with innodb engine)

Here we will setup Mysql Fabric with auto failover at DB level with 5 servers (1 Fabric Manager or Arbiter + 3 SQL Nodes + 1 Application server):

So Below are required server details:

192.168.18.1 : fabric manager : mysql 5.6.29 + fabric + connector required.
192.168.18.2 : Mysql Master : mysql 5.6.29
192.168.18.3 : Mysql slave1 : mysql 5.6.29
192.168.18.4 : Mysql Slave2 : mysql 5.6.29
192.168.18.5 : app : connector required

Note: Mysql Fabric worked on Mysql 5.6.10 and above.

 

First we will prepare Fabric Manager Server:

# Install mysql 5.6.29 and start mysql.

# Install Mysql Fabric (which will monitor SQL Servers and will be responsible for HA) by below command:

$ yum install mysql-utilities*

# Install fabric connector (used to connect SQL Servers) by below commands:

$ yum install mysql-connector-python*

# create fabric user, which will be used by fabric manager.

$ mysql -uroot -proot123 -e “grant all privileges on fabric.* to fabric@localhost identified by ‘fabric’;”

# Do the required changes in fabric configuration file like fabric/admin users password etc.

$ vi /etc/mysql/fabric.cfg

———————-

[storage]
user = fabric
password = fabric

[servers]
user = fabric #It will manage to sql nodes.
password = fabric
backup_user = fabric #will be used for backup
backup_password = fabric
restore_user = fabric #will be used for restoration
restore_password = fabric

[protocol.xmlrpc]
address = 192.168.18.1:32274 #Here change localhost to IP_Address if want to connect application remotely.

———————-

Note: Other entries are normal, need to check and update as per requirement. Here we are keeping single user as fabric for all tasks but on production user we should keep different users with required rights only. By default app will connect to fabric on 32274 port.

# Setup fabric which will auto create management database called fabric and store required tables.
$ mysqlfabric manage setup –param=storage.user=fabric
# Now start mysql fabric manager-
$ mysqlfabric manage start –daemonize
# To check mysql fabric manager if running-
$ mysqlfabric manage ping

 

Now We will parepare mysqld nodes:

#Install mysql 5.6.29 all mysql nodes (Master + slaves).
# start mysql with below lines in my.cnf on all 3 mysqld nodes.
$ vi /etc/my.cnf
—————-
server_id=1 #will be different on each server
log_bin
gtid-mode=ON
enforce-gtid-consistency
log_slave_updates
—————-
Note: Other configuration will be as per normal requirement. Same on all 3 machines. Mysql fabric works only with GTID based replication.
# Start mysql on all 3 mysqld nodes.

Note: No need to start replication by change master to command as it will be by mysql fabric at the time of node addition.

# provide rights to fabric user on all 3 mysqld machines either globally or to all other fabric and mysqld ips.
$ mysql -uroot -proot123 -e “grant all privileges on *.* to ‘fabric’@'%’ identified by ‘fabric’;”

 

Now we will create a cluster group and add SQL nodes to it:

# Now create a cluster group named “mycluster”
$ mysqlfabric group create mycluster
# Add all 3 mysqld nodes to cluster.

$ mysqlfabric group add mycluster 192.168.18.2  #If port is different then 192.168.18.2:3307

$ mysqlfabric group add mycluster 192.168.18.3

$ mysqlfabric group add mycluster 192.168.18.4

# To check cluster group health
$ mysqlfabric group lookup_groups

# To check cluster nodes health
$ mysqlfabric group lookup_servers mycluster

Note: Till now all mysqld nodes will show as read only means secondary.

# To make one SQL Node as master
$ mysqlfabric group promote mycluster –slave_id bb0c7e34-e5f8-11e5-a921-000c299bf386

Note: Where bb0c7e34-e5f8-11e5-a921-000c299bf386 is the server_uuid getting from above nodes health check command.

# Check if master is promoted
$ mysqlfabric group lookup_servers mycluster

Note: Now one server should be read/write and others only read only, if we check by nodes health check command.

# We can remotely check all servers replication status from manager server.
$ mysql -h 192.168.18.3 -uroot -p -e “show slave status\G”

# At this stage, the MySQL replication relationship is configured and running but there isn’t yet High Availability as MySQL Fabric is not monitoring the state of the servers – the final configuration step fixes that:
$ mysqlfabric group activate mycluster