If we want to run the application presented in "Writing a Fault-tolerant Database Application
using MySQL Fabric" with MySQL Fabric 1.4.2 Release
Candidate, some changes to the application are required. In
the previous post, we used MySQL Fabric 1.4.0 Alpha and many
changes have been made since this version. We can find an updated
version of the application here:
- Updated application, ready to use with MySQL Fabric 1.4.2 Release Candidate.
Recall that the application creates a simple database, a high
availability group, registers the MySQL Servers into Fabric and
runs a thread that mimics a client and another one that
periodically executes a switch over.
Configuring and running MySQL Fabric
To run the application, we need:
- Python 2.6 or 2.7
- Three or more MySQL Servers:
- One backing store (5.6.10 or later)
- Two managed servers (5.6.10 or later)
- MySQL Fabric 1.4.2 or later
- Connector Python (MySQL Fabric-aware Version 1.2.1 or later) installed
In this post, we omit how all the components are installed and
configured and focus only on how to set up MySQL Fabric. We also
assume Unix-like systems, specifically Ubuntu as our environment.
For further information on installing and configuring other
components and different environments, please, check Configuring and running MySQL Fabric.
To run MySQL, we need to configure MySQL Fabric according to our
environment. In this context, change the
/usr/local/etc/mysql/fabric.cfgconfiguration file:
[storage]
address = localhost:13000
user = root
password =
database = fabric
[servers]
user = root
password =
[protocol.xmlrpc]
address = localhost:32274
disable_authentication = no
user= admin
password = adminpass
realm = MySQL Fabric
(Only the relevant information for this post is presented here)
Choose one of the MySQL servers as the backing store. The backing
store's address can be found in the "storage" section along with
the "user" and "password" that MySQL Fabric will use to access
it. The user does not need to be "root" though. Any user with the
following privileges is fine:
CREATE USER 'fabric'@'localhost' IDENTIFIED BY 'secret';
GRANT ALL ON fabric.* TO 'fabric'@'localhost';
After configuring a user to access the backing store, we need to
set up a user to access the MySQL servers that will be
managed by MySQL Fabric. The user does not need to be "root"
though. Any user with the following privileges is fine:
GRANT ALL ON *.* TO 'fabric'@'localhost';
SET PASSWORD FOR 'fabric'@'localhost' = PASSWORD('secret');
Then we need to configure an address where MySQL Fabric will be
listening to incoming request to fetch and update information on
shards, servers, etc. It is worth mentioning that MySQL Fabric
provides an authentication mechanism to check whether users have
the necessary privileges to fetch or update any information.
However, we will ignore this for now to avoid cluttering the post
with too much information and revisit this specific feature in
another post. So after changing the necessary options in the
configuration file, we are ready to set up the backing
store:
$ mysqlfabric manage setup
To run MySQL Fabric, execute the following
command:
$ mysqlfabric manage start
Now that MySQL Fabric is up and running, we can start playing
with it.
Running the Sample Application
The application can be executed as follows:
python switchover_application.py --user=root --passwd="" --group="YYZ" \
--servers-addresses="localhost:13002 localhost:13003" \
--fabric-user=admin --fabric-passwd="adminpass" \
--fabric-addresses="localhost:8080"
Please, note that MySQL Fabric and the MySQL instances may be
running in different addresses in our environment. So change this
information accordingly.
In what follows, we are going to present some key changes that
have a direct impact on the application.
Change to the error reported while trying to write to a backup
server
One of the changes is related to how the
ER_OPTION_PREVENTS_STATEMENT error is being handled. In
MySQL Fabric 1.4.0 release, an attempt to write to a secondary
server would trigger the DatabaseError exception
which is used to report a variety of issues. So to figure out
whether a server was set to read-only, after a switchover, for
example, we had to check the error code. Since the MySQL Fabric
1.4.1 release, the connector hides this logic and reports a
MySQLFabricError exception to the application. This makes
it easier to write fault-tolerant applications:
try:
data = [(i, 'name') for i in range(1, 4)]
cur.executemany(
"INSERT INTO employee (id, name) VALUES (%s, %s)", data
)
self.__cnx.commit()
except MySQLFabricError as error:
cur = self._get_cursor()
Change to the set_property parameters
Besides specifying the high-availability group which will be
accessed, we must set whether we want to execute read-only or
read-write transaction(s). This is is done through the mode
parameter which accepts MODE_READONLY or
MODE_READWRITE (default value), respectively
read-only or read-write transactions.
self.__cnx = mysql.connector.connect(**fabric_params)
self.__cnx.set_property(group=group, mode=MODE_READWRITE)
We are going to revisit this in another blog post where we intent
to describe how to scale out reads using MySQL Fabric. Stay
tuned.
Introduced credentials
fabric_params = {
"fabric" : {
"host" : fabric_host, "port" : fabric_port,
"username" : fabric_user, "password" : fabric_passwd
},
"user" : user, "passwd" : passwd, "database" : "mysql",
}
self.__cnx = mysql.connector.connect(**fabric_params)
We must provide a user and password to access MySQL Fabric. In
this example, we have used the default "admin" user who is
created when the backing store is set up. We will explain this
new feature in another post. Stay tuned.
Changed how to get a reference to Fabric (i.e. a proxy)
through the connector
fabric = Fabric(host=fabric_host, port=fabric_port,
username=fabric_user, password=fabric_passwd
)
fabric.seed()
self.__proxy = fabric.get_instance().proxy
Note that we need to specify a user and password to access a
MySQL Fabric instance.