Configuring the MySQL Front-End Modules

Introduction

Whether written using the native MySQL API, or a connector of some sort, MySQL apps comminucate with the database using the MySQL client-server protocol.

Whether written using the native SQL Relay API, or a connector of some sort, SQL Relay apps generally communicate with SQL Relay using the SQL Relay client-server protocol.

However, the MySQL Front-End Modules enable SQL Relay to speak the MySQL client-server protocol and authorize/authenticate users in the manner that the MySQL database does. This allows MySQL apps to communicate directly with SQL Relay, rather than to a MySQL database, without modification, and without using a drop-in replacement library.

In this configuration, SQL Relay becomes a transparent proxy. MySQL apps aimed at SQL Relay still think that they're talking to a MySQL database, but in fact, are talking to SQL Relay.

Basic Configuration

Before the SQL Relay Enterprise MySQL Front-End Modules can be configured, they must first be installed and licensed. Once they are, basic configuration consists of adding a set of tags that instruct SQL Relay to load the MySQL protocol module and the appropriate MySQL auth module to the SQL Relay configuration file (usually sqlrelay.conf).

<?xml version="1.0"?>
<instances>

	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3307"/>
		</listeners>
		<auths>
			<auth module="mysql_userlist">
				<user user="sqlruser" password="sqlrpassword"/>
			</auth>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>

</instances>

In this configuration:

The instance can be started using:

sqlr-start -id example

and accessed from the local machine using:

mysql --host=localhost --port=3307 --user=sqlruser --password=sqlrpassword

It can also be accessed from a remote machine by replacing localhost with the hostname of the local machine.

It can be stopped using:

sqlr-stop -id example

This instance speaks the MySQL client-server protocol, and uses the native MySQL authorization/authentication procedure. Any any client that wishes to use it must also speak the MySQL client-server protocol and present credentials in the native MySQL format. Basically this means that most software written using the MySQL native API, or written using a database abstraction layer which loads a driver for MySQL can access this instance. It also means that SQL Relay's standard sqlrsh client program cannot access this instance.

Listener Configuration

Note that in the example above, the instance is configured to listen on port 3307. The MySQL database typically listens on port 3306, so port 3307 was chosen to avoid collisions with the database itself.

However, if SQL Relay is run on a different server than the database itself, then it too can be configured to run on port 3306. This is desirable when using SQL Relay as a transparent proxy.

<?xml version="1.0"?>
<instances>

	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<auths>
			<auth module="mysql_userlist">
				<user user="sqlruser" password="sqlrpassword"/>
			</auth>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>

</instances>

If the server has multiple network interfaces, SQL Relay can also be configured to listen on specific IP addresses.

<?xml version="1.0"?>
<instances>

	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" addresses="192.168.1.50,192.168.1.51" port="3306"/>
		</listeners>
		<auths>
			<auth module="mysql_userlist">
				<user user="sqlruser" password="sqlrpassword"/>
			</auth>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>

</instances>

When configured this way, it can be accessed on 192.168.1.50 and 192.168.1.51 but not on 127.0.0.1 (localhost).

SQL Relay can also be configured to listen on a unix socket by adding a socket attribute to the listener tag.

<?xml version="1.0"?>
<instances>

	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306" socket="/var/lib/mysql/mysql.sock"/>
		</listeners>
		<auths>
			<auth module="mysql_userlist">
				<user user="sqlruser" password="sqlrpassword"/>
			</auth>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>

</instances>

(Note that the user that SQL Relay is run as must be able to read and write to the path of the socket.)

User-List Auth

In all examples above, the instance is configured to load the mysql_userlist auth module and defines a user sqlruser with password sqlrpassword.

In this configuration, a client must user sqlruser/sqlrpassword to access the database via SQL Relay. However, any number of user tags may be specified inside of the auth tag, defining any number of users and passwords.

<?xml version="1.0"?>
<instances>

	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<auths>
			<auth module="mysql_userlist">
				<user user="oneuser" password="onepassword"/>
				<user user="anotheruser" password="anotherpassword"/>
				<user user="yetanotheruser" password="yetanotherpassword"/>
			</auth>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>

</instances>

When using SQL Relay as a transparent proxy, it is desirable to define the same set of users that are defined in the database itself. While this may sound inconvenient, in practice, most apps use a single user to access the database, and it's not uncommon for multiple apps to share a user. So, the list is typically short.

Database Auth

If the database has a lot of users, or users are added, deleted, or updated regularly, then it might be prohibitively inconvenient to maintain a duplicate list of users in the configuration file. In this case, the mysql_database module can be used to auth users directly against the database.

<?xml version="1.0"?>
<instances>

	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<auths>
			<auth module="mysql_database"/>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>

</instances>

In this configuration, SQL Relay initially logs in to the database as mysqluser/mysqlpassword as specified in the connection tag. But each time a client connects, SQL Relay logs out and attempts to log back in as the user specified by the client, unless the user/password are the same as the current user.

For example, if a client initially connects using:

mysql --host=localhost --port=3307 --user=mysqluser --password=mysqlpassword

...then SQL Relay doesn't need to log out and log back in.

But if the client then tries to connect using:

mysql --host=localhost --port=3307 --user=anotheruser --password=anotherpassword

..then SQL Relay does need to log out and log back in.

A subsequent connection using anotheruser/anotherpassword won't require a re-login, but a subsequent connection using any other user/password will.

Though this module is convenient, it has two disadvantages.

First, logging in and out of the database over and over partially defeats SQL Relay's persistent connection pooling.

Second, the mysql_database module requires that the MySQL client send it an unencrypted password.

This may or may not be a problem, depending on how secure communications on your network need to be.

Also, it may require reconfiguration (or modification) of your app to use the mysql_clear_password auth plugin. For example, to send an unencrypted password from the mysql command line client:

mysql --host=localhost --port=3307 --user=anotheruser --password=anotherpassword --default-auth=mysql_clear_password

And, another issue is that some MySQL and MariaDB distributions omit the necessary mysql_clear_password module. For example, the distributions available in the default CentOS 6 and 7 repositories omit the module. On these platforms, an alternative distribution of MySQL/MariaDB would have to be installed or built from source.

Standard SQL Relay Features

Once the app is talking to SQL Relay, most of SQL Relay's features become available to the app, including Connection Pooling, Throttling, High Availability Features, Query Routing, Query Filtering, and Connection Schedules.

For the most part, these can be configured as defined in the SQL Relay Configuration Guide.

There are a few incompatible configurations though.

Otherwise, all other tags and attributes are compatbile.

Targeting a Foreign Backend

Since SQL Relay supports a variety of database backends, the app can also be redirected to any of these databases, instead of the MySQL database it was originally written to use.

This is as simple as specifying a different database using the dbase parameter in the instance tag and specifying the appropriate database connection string in the string attribute of the connection tag.

<?xml version="1.0"?>
<instances>

	<instance id="example" dbase="mysql">
		<listeners>
			<listener protocol="mysql" port="3306"/>
		</listeners>
		<auths>
			<auth module="mysql_database"/>
		</auths>
		<connections>
			<connection string="user=mysqluser;password=mysqlpassword;db=mysqldb;host=mysqlhost"/>
		</connections>
	</instance>

</instances>

In this example, a MySQL frontend provides MySQL applications access to an Oracle backend database.

When targeting a foreign backend, some queries may have to be modified to use the syntax of the new database and some code may need to be changed, but a full rewrite of the app should not be necessary.

Limitations

The implementation of the MySQL protocol is likely sufficient for most applications, but it isn't 100% complete. Some notable limitations follow: