Tuesday, November 11, 2014

Tutorial Introduction to working with Groups in MySQL Fabric

Tutorial Introduction to working with Groups in MySQL Fabric

Tutorial Introduction to working with Groups in MySQL Fabric

1 Working with Groups in MySQL Fabric

This blog deals with working with high availability in Fabric. It focuses on Creating MySQL Fabric groups, adding servers and working with the servers in the Fabric groups. The blog also includes an example that can be run to insert data and fetch from a Fabric group. The blog retains focus on the commands used to create the topology and does not focus on the security aspects.

The blog is more intended for users who want to get started quickly with MySQL Fabric, create a group and write a python program that works with this setup.

1.1 Prerequisites

In order to start working with MySQL Fabric groups, we need a set of MySQL Servers. We assume that the set of servers are started on the same machine. We use mysqlserverclone for starting multiple mysql servers.

Note that we are starting the servers on the same machine just for the sake of simplicity.

1.1.1 Downloading mysqlutilities

Download MySQL Utilities for linux from utils. MySQL utilities contains "mysqlfabric" and "mysqlserverclone". The steps to install from the downloaded package and verify the installation are given below.

1.1.2 Unzip the utilities

>> gunzip mysql-utilities-1.5.2.tar.gz

>> tar -xvf mysql-utilities-1.5.2.tar

>> cd mysql-utilities-1.5.2

1.1.3 Build and install the utilities

mysql-utilities-1.5.2>> python setup.py build

mysql-utilities-1.5.2>> sudo python setup.py install

1.1.4 Verify that server clone is installed

>> mysqlserverclone --version
MySQL Utilities mysqlserverclone version 1.5.2
License type: GPLv2

1.1.5 Verify that MySQL Fabric is installed

>> mysqlfabric --version
mysqlfabric 1.5.2

1.1.6 Start the first server

  • Setup the configuration tables
    Setup the system tables for the MySQL Server. The mysql_install_db script can be used to setup the system tables. See below how to setup the system tables.

    The basic syntax of the command is the following

    >> ./scripts/mysql_install_db --datadir=<MYSQL_DATA_DIR>
    

    The following shows a running example of the above command in my system

    >> ./scripts/mysql_install_db
       --datadir=/home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-1/
    
    Installing MySQL system tables...OK
    
    Filling help tables...OK
    
    A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
    You will find that password in '/home/narayanan/.mysql_secret'.
    
    You must change that password on your first connect,
    no other statement but 'SET PASSWORD' will be accepted.
    See the manual for the semantics of the 'password expired' flag.
    
    
    You can start the MySQL daemon with:
    
      cd . ; ./bin/mysqld_safe &
    
    You can test the MySQL daemon with mysql-test-run.pl
    
      cd mysql-test ; perl mysql-test-run.pl
    
    Please report any problems at http://bugs.mysql.com/
    
    The latest information about MySQL is available on the web at
    
      http://www.mysql.com
    
    Support MySQL by buying support/licenses at http://shop.mysql.com
    
    WARNING: Found existing config file ./my.cnf on the system.
    Because this file might be in use, it was not replaced,
    but was used in bootstrap (unless you used --defaults-file)
    and when you later start the server.
    The new default config file was created as ./my-new.cnf,
    please compare it with your file and take the changes you need.
    
  • Start the MySQL Daemon

    The syntax of the command is the following

    >> ./bin/mysqld_safe --skip-grant-tables --server-id=1 --log-bin
    --log-slave-updates --gtid-mode=ON --enforce-gtid-consistency
    --datadir=<MYSQL_DATA_DIR>
    
    OptionExpln
    –skip-grant-tablesRead Here
    –server-idRead Here
    –log-binRead Here
    –gtid-modeRead Here
    –log-slave-updatesRead Here
    –enforce-gtid-consistencyRead Here
    Start the MySQL Server that will be cloned into multiple mysql servers.

    The following represents a running example of the above command in my system

    >> ./bin/mysqld_safe --skip-grant-tables --server-id=1 --log-bin
    --log-slave-updates --gtid-mode=ON --enforce-gtid-consistency
    --datadir=/home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-1/
    
    141008 16:32:44 mysqld_safe Logging to
    '/home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-1//awesomeness.err'.
    141008 16:32:44 mysqld_safe Starting mysqld daemon with databases from
    /home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-1/
    

1.1.7 Use mysqlserverclone to start multiple servers

mysqlserverclone permits an administrator to clone an existing MySQL server instance to start a new server instance on the same host. The utility creates a new datadir, and starts the server with a socket file. You can optionally add a password for the login user account on the new instance.

Clone the mysqld daemon into four servers. The following shows an detailed example of running the command and the obtained output for one server, in my system.

NOTE: The below commands contain the directory configuration and the setup of my local machine, please ensure that you make the necessary changes while running it in your local machines.

>> mysqlserverclone --server=root@localhost:3306:/tmp/mysql.sock
--new-data=/home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-2/
--mysqld="--server-id=2 --log-bin --log-slave-updates --gtid-mode=ON
--enforce-gtid-consistency" --new-port=13000 --root-password=""

WARNING: Using a password on the command line interface can be insecure.
# WARNING: Root password for new instance has not been set.
# Cloning the MySQL server running on localhost.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# WARNING: The socket file path
# '/home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-2/mysql.sock'
# is too long (>107), using '/tmp/tmpmmyBeX/mysql.sock' instead

# Testing connection to new instance...
# Success!
# Connection Information:
#  -uroot --socket=/tmp/tmpmmyBeX/mysql.sock
#...done.

NOTE: Use the option --delete-data for a clean start

The above options are explained in detail here

Repeat the above command for creating the rest of the servers.

>> mysqlserverclone --server=root@localhost:3306:/tmp/mysql.sock
--new-data=/home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-3/
--mysqld="--server-id=2 --log-bin --log-slave-updates --gtid-mode=ON
--enforce-gtid-consistency" --new-port=13001 --root-password=""

>> mysqlserverclone --server=root@localhost:3306:/tmp/mysql.sock
--new-data=/home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-4/
--mysqld="--server-id=3 --log-bin --log-slave-updates --gtid-mode=ON
--enforce-gtid-consistency" --new-port=13002 --root-password=""

>> mysqlserverclone --server=root@localhost:3306:/tmp/mysql.sock
--new-data=/home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-5/
--mysqld="--server-id=4 --log-bin --log-slave-updates --gtid-mode=ON
--enforce-gtid-consistency" --new-port=13003 --root-password=""

>> mysqlserverclone --server=root@localhost:3306:/tmp/mysql.sock
--new-data=/home/narayanan/WORKSPACES/MYSQL-SERVER/FRESHWORKSPACE/mysql-trunk/install-local/datadir/datadir-6/
--mysqld="--server-id=5 --log-bin --log-slave-updates --gtid-mode=ON
--enforce-gtid-consistency" --new-port=13004 --root-password=""

Once the above command are successfully run you have a set of five running servers in the ports 13000-4

1.1.8 Create the Fabric user on the state store (localhost:13000)

This user will be used by Fabric when connecting to the MySQL Server that acts as the state store (localhost:13000).

For the sake of simplicity we shall create an user named "fabric" who will use the password "fabric" to authenticate himself.

Connect to the server

>> ./bin/mysql -u root --port=13000 --host=localhost --protocol=tcp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.5-m15-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Create the user

mysql> CREATE USER 'fabric'@'localhost' IDENTIFIED BY 'fabric';
Query OK, 0 rows affected (0.00 sec)

Grant the permissions for the user. For now the user needs to be a super user.

mysql> GRANT ALL ON *.* TO 'fabric'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Flush the privileges Reloads the privileges from the grant tables in the mysql database.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

1.2 Setup the Fabric Server

Once the above servers have started running, we need to register the servers with Fabric and allow Fabric to manage the servers. The following changes detail the things that need to be done to get Mysql Fabric to manage the servers.

1.2.1 Create the user for authenticating into the groups

This user shall be used for connecting to the servers that are running in the groups. For simplicity we shall create use "group" as both the user name and the password. Fabric needs super user privileges on the group user too.

This user shall be created in all the servers 13001-4

The idea described in this section will eventually break replication when the servers are added to a group and any of them is promoted to master. The replication will try to create the user again and will fail. Hence we, disable writes to the binary log before creating the user.

Disable writes to the binlog

mysql> SET SESSION SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

Create the user

mysql> CREATE USER 'group'@'localhost' IDENTIFIED BY 'group';
Query OK, 0 rows affected (0.00 sec)

Grant the privileges

mysql> GRANT ALL ON *.* TO 'group'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Flush the privileges Reloads the privileges from the grant tables in the mysql database.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Enable Writes to the binlog

mysql> SET SESSION SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

1.2.2 Basic topology that we want to create

The above topology basically contains one master and three slaves in a Fabric Group. The connector contacts Fabric to get the information on the topology and directs the clients to the master in the Fabric group.

1.2.3 Changes in the fabric.cfg.in configuration file

  • Point to the cloned servers
    Change the state store to point to the MySQL Server running on localhost:13000. Set the password to point to the password for the fabric state store.

    The following sections can be found in the Fabric configuration file here /etc/mysql/fabric.cfg

    [storage]
    -address = localhost:3306
    +address = localhost:13000
     user = fabric
    -password =
    +password = fabric
     database = fabric
     auth_plugin = mysql_native_password
     connection_timeout = 6
    
  • Change password to point to the cloned server
    Change the user name and the password for the fabric managed server to group.
    [servers]
    -user = fabric
    -password =
    +user = group
    +password = group
     unreachable_timeout = 5
    
  • Create a user to connect to Fabric
    We shall configure a user who will be used by the mysqlfabric script while connecting to the fabric server. For convenience the user name and the password for this user shall be client.
    [protocol.xmlrpc]
     address = localhost:32274
     threads = 5
    -user = admin
    -password =
    +user = client
    +password = client
     disable_authentication = no
     realm = MySQL Fabric
    

1.2.4 Setup the state store

Setup the Fabric state store. This command creates the necessary tables in the Fabric state store. These tables will be used to store the topology information. The admin user created here can be used to create other users that can connect to fabric as can be seen below.

>> mysqlfabric manage setup
[INFO] 1412767075.000110 - MainThread - Initializing persister: user (root), server (localhost:13000), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc: 
Repeat Password: 
Password set.
Password set.
No result returned

1.2.5 Start the state store

Start the Fabric server. This starts the xmlrpc server and the mysql protocol server for listening to commands that are used to change the topology of MySQL servers.

>> mysqlfabric manage start
[INFO] 1413381481.331419 - MainThread - Initializing persister: user (fabric), server (localhost:13000), database (fabric).
[INFO] 1413381481.337946 - MainThread - Loading Services.
[INFO] 1413381481.365873 - MainThread - MySQL-RPC protocol server started, listening on localhost:32275
[INFO] 1413381481.373076 - MainThread - Fabric node starting.
[INFO] 1413381481.376651 - MainThread - Starting Executor.
[INFO] 1413381481.376767 - MainThread - Setting 5 executor(s).
[INFO] 1413381481.377051 - Executor-0 - Started.
[INFO] 1413381481.377609 - Executor-1 - Started.
[INFO] 1413381481.378253 - Executor-2 - Started.
[INFO] 1413381481.378910 - Executor-3 - Started.
[INFO] 1413381481.380200 - Executor-4 - Started.
[INFO] 1413381481.380325 - MainThread - Executor started.
[INFO] 1413381481.390687 - MainThread - Starting failure detector.
[INFO] 1413381481.394927 - XML-RPC-Server - XML-RPC protocol server ('127.0.0.1', 32274) started.
[INFO] 1413381481.395746 - XML-RPC-Server - Setting 1 XML-RPC session(s).
[INFO] 1413381481.396350 - XML-RPC-Session-0 - Started XML-RPC-Session.

1.2.6 Create the user that connects to the xmlrpc server

Create the user client that will be used by the mysqlfabric scripts to connect to the xmlrpc server.

>> mysqlfabric user add client --user=admin
Password for admin: 
Add a new Fabric user
=====================
Username: client
Protocol (default xmlrpc): xmlrpc
Password: 
Repeat Password: 

Select role(s) for new user
  ID  Role Name   Description and Permissions
  --  ----------  ---------------------------
   1  superadmin  Role for Administrative users
                  + Full access to all core Fabric functionality
   2  useradmin   Role for users dealing with user administration
                  + User administration
                  + Role administration
   3  connector   Role for MySQL Connectors
                  + Access to dump commands
                  + Reporting to Fabric

Enter comma separated list of role IDs or names: 1
Fabric user added.
No result returned

1.2.7 Create the MySQL Fabric Group

Create the Fabric group. Note that this step basically just creates a logical entity, the group is still empty and does not associate any MySQL servers with it.

mysqlfabric group create <GROUP_ID> --description=<GROUP_DESCRIPTION>
parameter nameDesc
GROUP_IDThe name of the group
GROUP_DESCRIPTIONThe description of the group being created

The following is an example for the above command being run on my local machine

>> mysqlfabric group create group-1 --description="Creating Sample Group 1"
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
04d1e55f-a1d6-4a39-bee8-47dc8361e5dd        1       1      1

state success          when                                                        description
----- ------- ------------- ------------------------------------------------------------------
    3       2 1412775810.36 Triggered by <mysql.fabric.events.Event object at 0x7ff0557ba610>.
    4       2 1412775810.37                                  Executing action (_create_group).
    5       2 1412775810.38                                   Executed action (_create_group).

1.2.8 Add the MySQL Servers to the Group

Add the Mysql Servers to the Group. The following is the basic syntax of the command used to register MySQL servers to a Fabric group.

The following represents the basic syntax for adding servers to a group

>> mysqlfabric group add <GROUP_ID> <SERVER_ADDRESS>

The following represents a sample of running the command in my local system and the resulting output

>> mysqlfabric group add group-1 localhost:13001
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
5ef0b8a5-ed7c-4812-9359-533c23d6a20a        1       1      1

state success          when                                                        description
----- ------- ------------- ------------------------------------------------------------------
    3       2 1412814103.78 Triggered by <mysql.fabric.events.Event object at 0x7faee23ed9d0>.
    4       2 1412814103.78                                    Executing action (_add_server).
    5       2  1412814103.8                                     Executed action (_add_server).


>> mysqlfabric group add group-1 localhost:13002
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
9c2759ee-386e-4a7a-8e25-c4333035590f        1       1      1

state success          when                                                        description
----- ------- ------------- ------------------------------------------------------------------
    3       2 1412814108.36 Triggered by <mysql.fabric.events.Event object at 0x7faee23ed9d0>.
    4       2 1412814108.36                                    Executing action (_add_server).
    5       2 1412814108.38                                     Executed action (_add_server).

1.2.9 List all the servers in a group

List all the servers that are present in group-1.

>> mysqlfabric group lookup_servers <GROUP_ID>
parameter namedescription
GROUP_IDThe ID of the group whose servers we are looking up

The following represents an example of running the command in my local machine.

>> mysqlfabric group lookup_servers group-1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status      mode weight
------------------------------------ --------------- --------- --------- ------
0fbc486b-4f4a-11e4-8d39-6afcb1837adc localhost:13002 SECONDARY READ_ONLY    1.0
1ecd83a0-4f4a-11e4-8d3a-69f6960e8264 localhost:13001 SECONDARY READ_ONLY    1.0

1.2.10 Add one more server to the group

>> mysqlfabric group add group-1 localhost:13003
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
f0ce45fe-8531-4043-8183-73d678de01bd        1       1      1

state success          when                                                        description
----- ------- ------------- ------------------------------------------------------------------
    3       2  1412814872.8 Triggered by <mysql.fabric.events.Event object at 0x7faee23ed9d0>.
    4       2  1412814872.8                                    Executing action (_add_server).
    5       2 1412814872.82                                     Executed action
    (_add_server).

1.2.11 Promote one of the servers to be the master

One of the servers in the group is promoted to be the master. If we do not explicitly specify which of the servers should be the master, Fabric will choose one of the servers atomatically.

The general syntax of the command is

>> mysqlfabric group promote <GROUP_ID> --slave_id=<SERVER_UUID>
parameter namedescription
GROUP_IDID of the Group
SERVER_UUIDUUID of the server that needs to made the master
The following presents an example of running the command in my local box and the resulting output.

Lookup the servers in the group to find the UUID of the server that needs to be made the master.

>> mysqlfabric group lookup_servers group-1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status      mode weight
------------------------------------ --------------- --------- --------- ------
070f08e8-4f4a-11e4-8d39-65e756f7d851 localhost:13003 SECONDARY READ_ONLY    1.0
0fbc486b-4f4a-11e4-8d39-6afcb1837adc localhost:13002 SECONDARY READ_ONLY    1.0
1ecd83a0-4f4a-11e4-8d3a-69f6960e8264 localhost:13001 SECONDARY READ_ONLY    1.0

Promote the server running on the address localhost:13003 to master. ./connector_reports_errors.png

>> mysqlfabric group promote group-1 --slave_id="070f08e8-4f4a-11e4-8d39-65e756f7d851"
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
0c886272-a8bb-4bfe-abcb-aef2b326d818        1       1      1

state success          when                                                        description
----- ------- ------------- ------------------------------------------------------------------
    3       2 1412815237.44 Triggered by <mysql.fabric.events.Event object at 0x7faee228fbd0>.
    4       2 1412815237.45                           Executing action (_define_ha_operation).
    5       2 1412815237.46                            Executed action (_define_ha_operation).
    3       2 1412815237.45 Triggered by <mysql.fabric.events.Event object at 0x7faee2445610>.
    4       2 1412815237.46                          Executing action (_check_candidate_fail).
    5       2 1412815237.47                           Executed action (_check_candidate_fail).
    3       2 1412815237.46 Triggered by <mysql.fabric.events.Event object at 0x7faee2445690>.
    4       2 1412815237.47                               Executing action (_wait_slave_fail).
    5       2 1412815237.49                                Executed action (_wait_slave_fail).
    3       2 1412815237.49 Triggered by <mysql.fabric.events.Event object at 0x7faee24457d0>.
    4       2 1412815237.49                           Executing action (_change_to_candidate).
    5       2 1412815237.63                            Executed action (_change_to_candidate).

Verify the topology after the promote

>> mysqlfabric group lookup_servers group-1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
070f08e8-4f4a-11e4-8d39-65e756f7d851 localhost:13003   PRIMARY READ_WRITE    1.0
0fbc486b-4f4a-11e4-8d39-6afcb1837adc localhost:13002 SECONDARY  READ_ONLY    1.0
1ecd83a0-4f4a-11e4-8d3a-69f6960e8264 localhost:13001 SECONDARY  READ_ONLY    1.0

1.3 Running a Client Program for accessing the group

Getting a connection to the MySQL Fabric server.

1.3.1 Getting a connection to MySQL Fabric

Connect to the Fabric server this returns a MySQLFabricConnection that encapsulates the parameters for connecting to the Fabric server.

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274,
            "username": "admin", "password" : "",
            'report_errors': True
           },
    user="root", password="", autocommit=True
    )

1.3.2 Inserting data into the group

Lookup the group and insert the data into the master of the group. Looking up the group automatically returns a connection to the MySQL Servers that represents the master of the group.

conn.set_property(group="group-1", mode=fabric.MODEREADWRITE)

The above looks up the group name "group-1" and sets the READWRITE mode to find the master of the group. The master is looked up from the metadata fetched from Fabric and the connection information is used to take a MySQLConnection to the master. The MysqlConnection can be used to execute INSERTs on the master.

def add_employee(conn, emp_no, first_name, last_name):
    conn.set_property(group="group-1", mode=fabric.MODE_READWRITE)
    cur = conn.cursor()
    cur.execute("USE employees")
    cur.execute(
        "INSERT INTO employees VALUES (%s, %s, %s)",
        (emp_no, first_name, last_name)
        )
    # We need to keep track of what we have executed in order to,
    # at least, read our own updates from a slave.
    cur.execute("SELECT @@global.gtid_executed")
    for row in cur:
        print "Transactions executed on the master", row[0]
        return row[0]

1.3.3 Retrieving data from a group

Lookup the group and select and read information from a slave of the group.

conn.set_property(group="group-1", mode=fabric.MODE_READONLY)

In the above statement "group-1" is the name of the group and fabric.MODE_READONLY searches and finds a slave in the group.

def find_employee(conn, emp_no, gtid_executed):
    conn.set_property(group="group-1", mode=fabric.MODE_READONLY)
    cur = conn.cursor()
    # Guarantee that a slave has applied our own updates before
    # reading anything.
    cur.execute(
        "SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)" %
        (gtid_executed, )
    )
    for row in cur:
        print "Had to synchronize", row, "transactions."
    cur.execute("USE employees")
    cur.execute(
        "SELECT first_name, last_name FROM employees "
        "WHERE empno = %s", (emp_no, )
        )
    for row in cur:
        print "Retrieved", row

1.3.4 Expected Output

The following is the output of running the above code.

>> python test_fabric_query.py
Transactions executed on the master 070f08e8-4f4a-11e4-8d39-65e756f7d851:1-8
Had to synchronize (2,) transactions.
Retrieved (u'John', u'Doe')

1.4 APPENDIX - I

The following code inserts data into Group-1 and looks up data from the same group.

import mysql.connector
from mysql.connector import fabric,  errorcode
import inspect

def add_employee(conn, emp_no, first_name, last_name):
    conn.set_property(group="group-1", mode=fabric.MODE_READWRITE)
    cur = conn.cursor()
    cur.execute("USE employees")
    cur.execute(
        "INSERT INTO employees VALUES (%s, %s, %s)",
        (emp_no, first_name, last_name)
        )
    # We need to keep track of what we have executed in order to,
    # at least, read our own updates from a slave.
    cur.execute("SELECT @@global.gtid_executed")
    for row in cur:
        print "Transactions executed on the master", row[0]
        return row[0]

def find_employee(conn, emp_no, gtid_executed):
    conn.set_property(group="group-1", mode=fabric.MODE_READONLY)
    cur = conn.cursor()
    # Guarantee that a slave has applied our own updates before
    # reading anything.
    cur.execute(
        "SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)" %
        (gtid_executed, )
    )
    for row in cur:
        print "Had to synchronize", row, "transactions."
    cur.execute("USE employees")
    cur.execute(
        "SELECT first_name, last_name FROM employees "
        "WHERE empno = %s", (emp_no, )
        )
    for row in cur:
        print "Retrieved", row

# Address of the Fabric, not the host we are going to connect to.
fabric.connection.extra_failure_report([errorcode.CR_SERVER_LOST_EXTENDED])
conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274,
            "username": "admin", "password" : "",
            'report_errors': True
           },
    user="root", password="", autocommit=True
    )
conn.set_property(group="group-1", mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS employees")
cur.execute("USE employees")
cur.execute("DROP TABLE IF EXISTS employees")
cur.execute(
    "CREATE TABLE employees ("
    "   empno INT, "
    "   first_name CHAR(40), "
    "   last_name CHAR(40)"
    ")"
    )

gtid_executed = add_employee(conn, 12, "John", "Doe")
find_employee(conn, 12, gtid_executed)

Date: 2014-11-11T23:00+0530

Author: Narayanan

Org version 7.9.3f with Emacs version 24

Validate XHTML 1.0

Friday, September 27, 2013

MySQL Fabric - Sharding - Shard Maintenance


Introduction

Let us start by listing the scenarios in which we will need to perform shard maintenance. In addition to periodic maintenance operations the following situations might mandate performing shard maintenance.

A shard is physically a server group. A single non-performant server can impact the latency of requests for the entire group. Hence we need to be able to pull out this non-performant server and replace it by a spare, without impacting the accessibility of the entire shard.

Maintenance activity might involve performing software upgrades on the different servers in the server group of the shard.

We might also need to upgrade the hardware on all the machines in the server group of a shard without impacting the accessibility of the entire data.

What if we want to form an alternate group and migrate the entire shard there? For example during times of heavy load we will want to create a group using more powerful servers, while during light load we might want to migrate to a group of more economical, less power consuming and cheaper machines.

Let us look at what options are available to do the above.

 

Sharding Topology

The topology we create has three shards. There are four server groups. One of the server groups serves as the Global group for the setup. Each of the server group has two MySQL servers, one serving as the master and another as the slave.

The assumption is that you have already gone through the following related blogs to create the above configuration.

Pulling out a non-performant server

We use the intrinsic HA management capabilities built into a Fabric server group to manage moving servers in and out seamlessly out of the shards.



case 1: Server is a master in the group

In the above setup assume we want to retire the MySQL Server running on host3:port3 (master in GROUPID1).

Step 1: Find the UUID of the server


command:


mysqlfabric server lookup_uuid <host:port> <user_id> <password>


where:


host:port – host:port of the server whose uuid we want to lookup (in the above


case host3:port3). 

user_id, password – credentials for the MySQL Server.


(OR)


mysqlfabric group lookup_servers <group-id>


where:



group-id – The group-id of the Group whose servers we want to lookup (in the 


above case GROUPID1).


Step 2: Choose another server in the group and promote that server into 


master


command:


mysqlfabric group promote <group-id><server-uuid>



where:


group-id – The group-id of the Group in which we want to promote a server to 


master (in the above case GROUPID1).


server-id – The server-id of the server which we want to promote as master.


Step 3: Remove the server from the group


command:


mysqlfabric group remove <group-id> <server-uuid>


where:


group-id – The groupid of the Group from which we want to remove a server (in 


the above case GROUPID1).

server-uuid – The uuid of the server which we want to remove from the group.

Now the server is safely out of the group. Now we can perform maintanence operations on the server and restore it later.

case 2: Server is a slave in the group

Step 1: Remove the server from the group
command:
mysqlfabric group remove <group-id> <server-uuid>
where:
group-id – The group-id of the group from which we want to remove a server (in the above case GROUPID1).
Server-uuid – The server UUID of the server we want to remove.

 

Performing software and hardware updates on a server

The same operation as above can be performed to remove the servers one by one and perform upgrades on them (or) we can move the entire shard to an alternate server group, upgrade all the servers in the current server group and move the
shard back.

Moving shards is explained below.


Moving the shard into another group

The sharding implementation supports a Move operations that allows moving a shard into another server group (a group with GROUPID4). The original server group is detached from the global group and will not receive any updates and its servers will not be reflected during lookups either.

While the move operation is in progress transactions can carry on without being affected.

For example to move a shard with shard-id=1 into a new group with new-group-id=GROUPID4. (The shard-id can be found using the command – mysqlfabric store dump_sharding_information)

command:
mysqlfabric sharding move <shard-id> <new-group-id>

where:
shard-id – The shard-id of the shard that needs to be moved (in this case 1)
new-group-id – The group-id of the server group into which this shard needs to be moved. (in this case GROUPID4).

Now we can cleanup and use the servers in GROUPID1 in whichever way we want.

Sunday, September 22, 2013

MySQL Fabric - Sharding - Migrating From an Unsharded to a Sharded Setup


Introduction


Having written an application to work with an unsharded MySQL setup, how do we migrate the application to a sharded setup ?


The above problem can be divided into two parts


  • Migrating the MySQL Store (with minimum disruption to ongoing transactions)
  • Migrating the Application.


This blog deals with the first problem of migrating the MySQL Store. The second problem of migrating the application will be handled in a separate blog post, and we shall assume for now that we just replace the old unsharded non-Fabric aware application with the new Fabric aware application.



 
Most enterprises start with a single server setup to develop a prototype and later realize that they need to scale. This blog presents a way for such a setup to seamlessly scale to a sharded setup with minimal disruption of ongoing transactions.


Unsharded Setup


The following is the schema in the MySQL Server running on localhost:13013


 
The employee database has the following tables

  • employee
  • salaries
  • dept_emp
  • fuel_reimb

 

Creating the Sharded Configuration


Let us assume that our final sharded configuration will have three shards. Each of these shards will be present in one group and there will be one global group.


 
The tables employee, salaries and dept_emp need to be sharded based on emp_no while fuel_reimb is a global table that needs to be present in all the shards.


Bring the Target server into the Fabric ecosystem


Create a Fabric group and add the server to it. Promote the server to master.


Commands:

mysqlfabric group create GROUPID1

mysqlfabric group add GROUPID1 localhost:13013 root ""

mysqlfabric group promote GROUPID1


 
The server is now part of the Fabric setup.


Creating the Global Group


The global group in a sharding setup is used to propagate schema updates to all tables in the sharding setup and updates to global tables throughout the sharding ecosystem. The global group contains

  • The schema of the sharded tables
  • The global tables


NOTE: Once we have restored the schema on the global group, until the global group is connected to the server group of the shard (GROUPID1) i.e. Until we create the sharding setup, we need to ensure that schema changing transactions do not occur on the server group of the shard. If schema changes occur in the server group of the shard, these will need to be propogated to the global group also, otherwise it will result in mismatching schemas in the global group and the server group of the shard. This can cause problems for global operations that expect the new schema. Once the sharding setup is created, schema changes can be sent to the global group.


NOTE: Similarly once the global tables have been restored on the global group, we need to prevent changes to the global groups until the sharding setup is created, after which the changes can be propagated to the global group.


Commands:

mysqlfabric group create GLOBAL_GROUP

mysqlfabric group add GLOBAL_GROUP localhost:13011 root ""

mysqlfabric group promote GLOBAL_GROUP

 

Creating the data in the Global Group


Backup the schema of all the tables and restore them on the on the global group. Also backup the data in the global tables and restore them on the global group.


Commands:

Use mysqldump to backup the schemas

mysqldump -d -u root --single-transaction --all-databases --socket <Server socket file for localhost:13013> > schema.sql

NOTE: --no-data can be used instead of -d in the above command.


Use mysql client to restore the schemas

mysql -u root --socket <Server socket file for localhost:13011> < schema.sql


Use mysqldump to backup the global tables

mysqldump -u root --no-create-info --single-transaction --socket <server socket file for localhost:13013> employee fuel_reimb > global.sql


The GTID_EXECUTED variable would have been set on the destination server. This causes applying the dump using the mysql client to fail. Reset this to allow restoring the global table.

reset master


Restore the data on the global tables

mysql --database=employee -u root --socket <server socket file for localhost:13011> < global.sql

 

Creating the Sharding Setup


Create a sharding definition


A sharding definition introduces a particular sharding scheme to which multiple tables related by a common sharding key can be mapped. A Sharding definition is composed of

  • Sharding scheme – RANGE, HASH
  • Global Group – Gets the global updates for all the shard tables mapped to this sharding definition. It also stores the global tables in this sharding definition.

Command

mysqlfabric sharding define HASH GLOBAL_GROUP

 

Map the tables to the sharding definition


Each sharding definition is associated with a unique ID. We map this unique ID to the table being sharded. Each mapping maps a table and the column containing the shard key to the sharding definition.

Commands:

mysqlfabric sharding add_mapping 1 employee.employee emp_no

mysqlfabric sharding add_mapping 1 employee.dept_emp emp_no

mysqlfabric sharding add_mapping 1 employee.salaries emp_no

 

Define the Shards


The shard definitions define the way the data is sharded based on the shard key. For a hash based sharding scheme, since the shards are automatically defined on the shard key, we do not need to manually define the lower_bounds for each shard.


Each shard is associated with a sharding definition and a group on which the data will be present. When we define a shard we also need to specify if it will be ENABLED for operations on shards.

Commands:

mysqlfabric sharding add_shard 1 GROUPID1 ENABLED


The sharding setup now contains a global group and one shard containing all the data.




Once the sharding setup has been created we can start using the Fabric enabled connector to start executing transactions. This would require shutting down the non-Fabric-aware application and replacing it by a Fabric-aware application.

 

Split the shards


Split the shard containing the data. Splitting helps to distribute the load into another group. This is a way to scale our setup for both read and write loads. The splitting can happen while transactions are ongoing.

Commands:

Add another group to which we want to split the data

mysqlfabric group create GROUPID2


Add the servers that are part of the split group


mysqlfabric group add GROUPID2 localhost:13009 root ""


Promote a server as master in the split group


mysqlfabric group promote GROUPID2


Split the shard


If you do not know the shard ID, dump the sharding information to find the shard ID.

mysqlfabric store dump_sharding_information


split the shard

mysqlfabric sharding split 1 GROUPID2


Now the data is distributed between the GROUPID1 and the GROUPID2.


 

Further Splits


The shards can be split further depending on need. For example to split shard with shard ID = 3 into a group with group ID = GroupID3 we will run the following command,

Commands:

mysqlfabric sharding split 3 GROUPID3


After the above command is executed the topology would look like the following.
 

Please find a few thoughts on maintaining a sharded system here http://vnwrites.blogspot.com/2013/09/mysqlfabric-sharding-maintenance.html