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

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi NV ,

    While implementing MySQL fabric, when I am adding servers(different IP) in groups. we are getting issue/error like fabric user doesn't have priviledges on other servers. After trying many time i get response like connection reset by peers.Please help Thanks

    ReplyDelete