MaxScale Binlog Server HOWTO: Install and Configure

Updated 2015-04-25: add the link to the slides of my PLMCE talk and a link to a bug number.

MaxScale 1.1.0 is out and includes the new Binlog Server module. This is the first post in s series of three. The two others are about Operations and High Availability. The links to the 2 other posts are at the end of this page.

In this post, I present how to install and configure MaxScale as a Binlog Server using the Binlog Router plugin.

This post assumes that you already have the replication topology below with:

the 2 nodes running MySQL 5.6 (I did tests with MySQL 5.6.24 using Amazon VMs),
binary logging enabled on both master (A) and slave (B): log-bin=binlog,
log-slave-updates DISABLED on both nodes,
the replication user 'repl'@'%' existing on both nodes with its password being slavepass.

-----    -----
| A | -> | B |
-----    -----

Our target is to obtain the following topology, with X as a MaxScale Binlog Server.

-----     / \     -----
| A | -> / X \ -> | B |
-----    -----    -----

I will go on the step by step operation to setup X. I used an Amazon Linux VM of type t2.micro to prepare this HOWTO but it should work on any RHEL / CentOS / Fedora system (and it should be easy to extrapolate to Debian / Ubuntu, SLES / OpenSUSE or to a tarball installation). At this point, I suppose that you already have root/sudo access to a server (or VM) that will be your future MaxScale Binlog Server (X above).

First, install MaxScale 1.1.0 on X:

Follow the instructions in https://mariadb.com/my_portal/download/maxscale
(You will need a MariaDB account do access this page.)
(You might need to accept importing the Maxscale-GPG-KEY when running yum.)
(If all fails, try to access the URL reported by yum and fix it in /etc/yum.repos.d/maxscale.repo.)

Then, create the MaxScale configuration file from the template by running the following commands in bash (and make sure this file is not world readable as it will contain usernam and password to connect to your master):

cd /usr/local/mariadb-maxscale/etc/;
sudo cp MaxScale_BinlogServer_template.cnf MaxScale.cnf;
sudo chmod og-rwx MaxScale.cnf;
ls -l MaxScale.cnf;

After creating the configuration file, it needs to be modified for your environment. For that, in the same directory as above run sudo vi MaxScale.cnf (or your favorite text editor) and do the following modifications:

(No need to change the user and password, we are using the same as in the template),
Change version_string=5.6.15-log to version_string=5.6.24-log
(MaxScale will advertise itself to the master as 5.6.24-log),
Change server-id=1000000000 to server-id=<a uniq server-id>
(this is the server-id used by MaxScale to connect to the master; MaxScale will present itself to slaves using the server-id of the master),
Change filestem=mysql-bin to filestem=binlog
(this is the basename of the binary log file to download from the master; it must be the same as what is configured on the master; and a prerequisite/simplification above is to configure all nodes with log-bin=binlog),
In the [master] section, change address=master.example.com
to address=<hostname or IP address of your master>
([master] is the servers option referenced in the [Binlog_Service] section which indicates the Binlog Router which master to download binary logs from),
In the [Binlog Listener] section, change port=5306 to port=3306
(this section makes the link between the module/router section and network/listener layer and we want the MaxScale Binlog Router to listen on the same port as a standard MySQL).

Then, modify the repl user to be used by MaxScale. In addition to downloading binary logs from the master, MaxScale needs to download the list of users to authenticate client connections. A different user can be used for that but I decide to use the same to simplify things and stick to the Binlog Server template configuration file . If you want to use a different user, you can change the user and passwd options in the [Binlog_Service] section (not the one on the router_options line, this last one is used to download the binary logs from the master). So on your master (A in the topology above), in a MySQL client session, run the three following commands to give access to the user list to MaxScale:

GRANT SELECT ON mysql.user  TO 'repl'@'%';
GRANT SELECT ON mysql.db    TO 'repl'@'%';
GRANT SHOW DATABASES ON *.* TO 'repl'@'%';

Note: this will also update the user on the slave (a prerequisite above is to also have this user created on the slave: this will be needed in a next HOWTO).

If your master still has the binary log file binlog.000001 on disk, you can start MaxScale right away. If not, you must tell the Binlog Router at which file it must start downloading binary log files from the master. There are two methods for that. The more simple is to add ",initialfile=<binlog file number>" at the end of the router_options line in the MaxScale configuration file. The other method is explained in the Operation HOWTO (link at the end of this article). This option will be ignored when the binary log directory will contain binary logs files.

You are now ready to start MaxScale by running:

sudo service maxscale start;

If MaxScale starts correctly:

a data directory should be created under /usr/local/mariadb-maxscale/,
a Binlog_Service directory should be created under /usr/local/mariadb-maxscale/,
binary logs should be created in the Binlog_Service directory,
log files should be created in the directory /usr/local/mariadb-maxscale/log/.

Check the logs (in /usr/local/mariadb-maxscale/log/) for scary messages. Below are the messages you might have and the corresponding corrective actions:

Encrypted password file /usr/local/mariadb-maxscale/etc/.secrets can't be accessed (No such file or directory). Password encryption is not used.
This is not a scary message, you can ignore it.
Error : Unable to get user data from backend database for service [Binlog_Service]. Missing server information.
Binlog_Service: Master connection error '#HY000 Lost connection to backend server.' in state 'Timestamp retrieval', attempting reconnect to master
Binlog_Service: Master mysql1 disconnected after 0 seconds. 0 events read.
MySQL is not started on your master, or your configuration file does not hove the right address/port for your master. If MySQL was not started on the master, restart MaxScale after starting MySQL to avoid falling in one of the pitfalls described below.
Error : Loading users for service [Binlog_Service] encountered error: [SELECT command denied to user 'repl'@'x.y.z.a' for table 'user'].
Error : Unable to load users from 0.0.0.0:3306 for service Binlog_Service.
Failed to start service 'Binlog_Service'.
You missed the GRANT part on the master, read back above to add GRANTs to the repl user.
Packet length is 72, but event size is 1684829551, binlog file mysql-bin.000001 position 4 reslen is 72 and preslen is -1, length of previous event -1. No residual data from previous call
Binlog_Service: Master mysql1 disconnected after 0 seconds. 1 events read.The MaxScale Binlog Router is not able to download the binary log file from the master: you probably have the wrong filestem or initialfile in the configuration file, see above.

Once the logs are clean and MaxScale is running, you should see the binary logs from the master in the /usr/local/mariadb-maxscale/Binlog_Service directory. To test that everything is working well, try the following in a MySQL client session on the master:

CREATE DATABASE test_mbls;
FLUSH BINARY LOGS;
DROP DATABASE test_mbls;

After each of those operations, the binary logs in the Binlog_Service directory should grow, rotate and grow again respectively. And they should be exactly the same as on the master (you can run the command sha1sum on the binary log files to convince you of that; it will not work on the latest binary log file though as MySQL still has it opened on the master: do a FLUSH BINARY LOGS on the master before running sha1sum to avoid that).

Once everything is working well with MaxScale, it is time to put a slave under the Binlog Server. On a slave replicating from the master, run the following commands in bash (after setting the correct hostname/IP address for MaxScale):

# Edit the line below.
maxscale="<hostname or ip address of your maxscale>";

sudo mysql <<< "STOP SLAVE;";
sss=$(sudo mysql <<< "SHOW SLAVE STATUS\G");
rmlf=$(printf "$sss" |
          awk '$1 == "Relay_Master_Log_File:"{print $2}');
emlp=$(printf "$sss" |
          awk '$1 == "Exec_Master_Log_Pos:"{print $2}');
cmd="CHANGE MASTER TO MASTER_HOST='$maxscale'";
cmd="$cmd, MASTER_LOG_FILE='$rmlf'";
cmd="$cmd, MASTER_LOG_POS=$emlp;";
sudo mysql <<< "$cmd";
sudo mysql <<< "START SLAVE;";

That slave is now replicating from MaxScale. You can verify this by running "SHOW SLAVE STATUS\G" and/or running some commands on the master (CREATE DATABASE ...; FLUSH BINARY LOGS; DROP DATABASE ...;) and checking their side effect on the slave.

And you are done ! If you want your slave replicating back from the master, run the above bash commands using the hostname/IP address of the master in the maxscale variable.

I hope you had fun trying the MaxScale Binlog Server and that you will test it more. If you have any questions about this article, feel free to post a comment below. If you have any questions about the MaxScale Binlog Router, you can send a mail to the MaxScale mailing list: maxscale@googlegroups.com. If you find bugs in MaxScale, you can report them via JIRA.

Other MaxScale Binlog Server HOWTOs:

Known Issues:

MaxScale is running as root by default after yum install: MXS-116.
The Binlog Router is not crash-safe: bug number to come soon... (see the Operations HOWTO for a work-around),
If MySQL is stopped on the master when MaxScale is started, MaxScale will not download GRANTs after successfully connecting to the master: bug number to come soon... (to work around that bug, restart MaxScale after starting the master),
Configuring the Binlog Router to download a non-existing binlog file from the master lead to a connection storm to the master and to a log storm in MaxScale: bug number to come soon...
A CHANGE MASTER TO MaxScale without file and position triggers a log storm in MaxScale: bug number to come soon...

J-F Gagné's MySQL Blog

Wednesday, April 15, 2015