Tutorial Introduction to working with Groups in MySQL Fabric
Table of Contents
- 1 Working with Groups in MySQL Fabric
- 1.1 Prerequisites
- 1.1.1 Downloading mysqlutilities
- 1.1.2 Unzip the utilities
- 1.1.3 Build and install the utilities
- 1.1.4 Verify that server clone is installed
- 1.1.5 Verify that MySQL Fabric is installed
- 1.1.6 Start the first server
- 1.1.7 Use mysqlserverclone to start multiple servers
- 1.1.8 Create the Fabric user on the state store (localhost:13000)
- 1.2 Setup the Fabric Server
- 1.2.1 Create the user for authenticating into the groups
- 1.2.2 Basic topology that we want to create
- 1.2.3 Changes in the fabric.cfg.in configuration file
- 1.2.4 Setup the state store
- 1.2.5 Start the state store
- 1.2.6 Create the user that connects to the xmlrpc server
- 1.2.7 Create the MySQL Fabric Group
- 1.2.8 Add the MySQL Servers to the Group
- 1.2.9 List all the servers in a group
- 1.2.10 Add one more server to the group
- 1.2.11 Promote one of the servers to be the master
- 1.3 Running a Client Program for accessing the group
- 1.4 APPENDIX - I
- 1.1 Prerequisites
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>
Start the MySQL Server that will be cloned into multiple mysql servers.Option Expln –skip-grant-tables Read Here –server-id Read Here –log-bin Read Here –gtid-mode Read Here –log-slave-updates Read Here –enforce-gtid-consistency Read Here 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 name | Desc |
|---|---|
| GROUP_ID | The name of the group |
| GROUP_DESCRIPTION | The 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 name | description |
|---|---|
| GROUP_ID | The 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 name | description |
|---|---|
| GROUP_ID | ID of the Group |
| SERVER_UUID | UUID of the server that needs to made the master |
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.
>> 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)
