Tag Archives: master-slave

MariaDB master-master configuration over ssh tunnel

MariaDB_Logo_Full_BlueBck-300x137A master-master database cluster is often referred to as an active-active database cluster.  Some other database systems including later MariaDB releases have built in cluster configuration options.  This guide sticks to MariaDB 5.5, as it is still completely compatible with MySQL 5.5.  In a MySQL Master-Master configuration, both nodes have a reciprocal master-slave relationship.  Node A is a master for Node B, and Node B is a master for Node A.  If a change is made to either instance, it is reflected on the other.

This is configured for a number of different reasons.  If the database is extremely active, it can be configured to load balance between two physical database nodes.  It can also be set up for redundancy purposes, so if one fails, all traffic can be instantly redirected to the other.  In this situation, I just want the data available on two nodes in separate networks, and I want the data to sync.  While there are many vpn projects available, I know and trust ssh, and it does not require any additional software to run on the node.

Chances are, if you’ve landed here, you have some knowledge on databases and Linux, but I’ll still warn you.

Warning:  Be very careful writing to two databases in this configuration!  There is no conflict resolution system!

I use this system in two different ways.  First, it is an always-on backup system for some databases that are only needed on one server.  Second, I have a script that writes to the two different instances, but one instance only receives inserts, and the other only receives updates.  Please don’t bug me with data integrity issues if you don’t heed my advice.  Also keep in mind that since these nodes are not on the same network, there may be some additional latency.

Prerequisites

First, you will need two Linux nodes.  I recommend using the same distribution so your packages will remain in sync.  Make sure you are able to ssh between the two, and install MariaDB server.  Please refer to standard documentation in setting up these services, as I’m considering that out of scope, but I have provided some links below.

It would be best to access your nodes via domain name, as IP’s change, and hard coded values can be a royal pain to hunt down and fix.  I personally use no-ip for my dynamic domain name needs.

For this scenario, I am using Ubuntu 14.04 Server Edition with MariaDB 5.5.  This should also work with MySQL 5.5, but I did not test.  This guide should also work with any other Linux distribution, but there may be some slight differences in file names or log file locations.  I’ve personally gone back to Ubuntu because I don’t have time to download and compile software or track down additional repositories.  It seems like any Linux software developer makes releasing on Ubuntu a priority, and that far outweighs any other factor for me.  Since MariaDB is just a drop in replacement for MySQL, many commands still reference MySQL, so no, I am not confused.

It’s best to start with a database that is either already manually in sync, or start with no database in place, then create them.  In my situation, I did not have the database created when I set up replication.  I used an SQL file to create the database, schema, and add data after replication was set up.

Step 1: Configure MariaDB Ports

For the purpose of this guide, I will be referring to the nodes as localnode and remotenode.  The fully qualified domain names I will be using are localnode.localdomain.com and remotenode.remotedomain.com  When possible, use this fully qualified domain name (FQDN).  Go ahead and su to root, as you will need it for most operations we will do here.

If your MariaDB server is currently running, shut it down on both nodes:

root@localnode:~# service mysql status
root@remotenode:~# service mysql status

To keep things sane and easy for me, I just switched the MariaDB listening port on remotenode to 3305.  This can be changed in the my.cnf file:

root@remotenode:~# vi /etc/mysql/my.cnf

The port is defined twice, once under the [client] header, and again under the [mysqld] header.  Find both, and change them to 3305:

[client]
port            = 3305
[mysqld]
...
port            = 3305

You can start the service up to verify that it is running on the port, but let’s go ahead and shut it down immediately after.

root@remotenode:~# service mysql start
 * Starting MariaDB database server mysqld                                            [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

root@remotenode:~# netstat -anp | grep :3305
tcp        0      0 127.0.0.1:3305          0.0.0.0:*               LISTEN      31586/mysqld
tcp        0      0 127.0.0.1:3305          127.0.0.1:35704         ESTABLISHED 31586/mysqld

root@remotenode:~# service mysql stop
 * Stopping MariaDB database server mysqld                                            [ OK ]

This shows that you’ve changed the port on the server, but before we move on, let’s try a test connection with the client:

root@remotenode:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 58
Server version: 5.5.37-MariaDB-0ubuntu0.14.04.1-log (Ubuntu)
...
MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 5.5.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1
...
MariaDB [(none)]> exit
Bye

Let’s move on!

Note: this configuration is my personal preference only.  You may also set them to both listen on 3306, but you will need to set up the ssh tunnel slightly differently.  I will note this in the next section.  This setup is ideal for me, because I want to control which instance the database is connecting to.

Step 2: Configure SSH

For this step, you will have to create a private/public key pair.  Here are the commands to generate and copy the keys from the localnode to the remotenode.  Do not set a password on the key.  Follow the prompts and enter passwords when necessary.  If you are unfamiliar with this process, it would be best to read up on it using the link below.

root@localnode:~# ssh-keygen -b 4096
root@localnode:~# root@remotenode.remotedomain.com

Now, repeat the process from the other.

root@remotenode:~# ssh-keygen -b 4096
root@remotenode:~# root@localnode.localdomain.com

You should now be able to ssh back and forth without being prompted for a password.

root@localnode:~$ ssh root@remotenode.remotedomain.com
Welcome to Ubuntu 14.04 LTS (GNU/Linux 3.13.0-30-generic x86_64)
...
root@remotenode:~$ ssh root@localnode.localdomain.com
Welcome to Ubuntu 14.04 LTS (GNU/Linux 3.13.0-30-generic x86_64)
...
root@localnode:~$

Step3: Set Up SSH Tunnel

Hat tip to the comments on this blog post!

Edit the crontab on your localnode.

root@localnode:~# crontab -e

Add the following:

* * * * * nc -z localhost 3305 || ssh -f root@remotenode.remotedomain.com -L 3305:127.0.0.1:3305 -N

Edit the crontab on remotenode.

root@remotenode:~# crontab -e

Add the following:

* * * * * nc -z localhost 3306 || ssh -f root@localnode.localdomain.com -L 3306:127.0.0.1:3306 -N

Now, once a minute, crontab will check for a connection, and if it does not exist, it will open an ssh tunnel.  Once this tunnel is established, 3305 on localnode will connect directly to 3305 on the remotenode.  Also 3306 on remotenode will connect to 3306 on the localnode.

root@remotenode:~# netstat -anp | grep :3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      24931/ssh
tcp        0      0 127.0.0.1:3306          127.0.0.1:52513         ESTABLISHED 24931/ssh
tcp6       0      0 ::1:3306                :::*                    LISTEN      24931/ssh
root@localnode:~# netstat -anp | grep :3305
tcp        0      0 127.0.0.1:3305          0.0.0.0:*               LISTEN      2104/ssh
tcp        0      0 127.0.0.1:3305          127.0.0.1:34414         ESTABLISHED 2104/ssh
tcp6       0      0 ::1:3305                :::*                    LISTEN      2104/ssh

I show the mysqld connection becauseIf you chose to leave the database listening port as 3306 on both nodes, you would use crontab entries similar to this:

* * * * * nc -z localhost 3305 || ssh -f root@remoteserver.remotedomain.com -L 3305:127.0.0.1:3306 -N
* * * * * nc -z localhost 3305 || ssh -f root@localserver.localdomain.com -L 3305:127.0.0.1:3306 -N

 Step 4: Configure Master Master configuration for MariaDB

Another hat tip to this tutorial.

Time to edit the my.cnf again, but we will be making the same changes to both nodes.

root@bothnodes:~# vi /etc/mysql/my.cnf

We will be making several changes, first up is server-id.  Make sure they are different.  I set localnode to 1, and remotenode to 2.

server-id               = 1 # localnode
server-id               = 2 # remotenode

Next, make sure log_bin and log_bin_index are uncommented.

log_bin                 = /var/log/mysql/mariadb-bin.log
log_bin_index           = /var/log/mysql/mariadb-bin.index

Now, we need to add the databases to the my.cnf.  In order to add multiple databases to the master-master or any master-slave configuration, you just need to add separate lines to the binlog_do_db parameter.

binlog_do_db            = database_number_1
binlog_do_db            = database_number_2

There are other tuning paramters that can be changed should you experience performance issues.  The notes I have in my default my.cnf file say that this is geared towards safety, not performance.

expire_logs_days        = 10
max_binlog_size         = 100M

Now, we can bring up the database on both nodes.

root@remotenode:~# service mysql start
 * Starting MariaDB database server mysqld                                            [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
root@localnode:~# service mysql start
 * Starting MariaDB database server mysqld                                            [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

Now, we need to log into the database on both nodes.

root@bothnodes:~# mysql -u root -p
Enter password:
...
MariaDB [(none)]>

First, we need to create the slave replication user on both nodes.  Of course, feel free to use a different username or password.  Normally, this user is not created as a local user only, but since we have the ssh tunnel in place, we will.  Run this on both nodes.

# both nodes
MariaDB [(none)]> create user 'replicator'@'localhost' identified by 'password'; 
MariaDB [(none)]> grant replication slave on *.* to 'replicator'@'localhost';

Now, we are ready to start the replication.  On localnode, we use the show master status command in the mysql client to get the rest.  Note the file and Position.

#localnode
MariaDB [(none)]> show master status;
+--------------------+----------+---------------------------------------+------------------+
| File               | Position | Binlog_Do_DB                          | Binlog_Ignore_DB |
+--------------------+----------+---------------------------------------+------------------+
| mariadb-bin.000019 |   780421 | database_number_1,database_number_2   |                  |
+--------------------+----------+---------------------------------------+------------------+

Now that we have this information, we go over to the remotenode to start the slave replication, using the File and Position output from the master status.  We also use the port definition of 3306.

 #remotenode
MariaDB [(none)]> slave stop; 

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_PORT = '3306', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mariadb-bin.000019', MASTER_LOG_POS = 780421; 

MariaDB [(none)]> slave start;

Now, we do the same in reverse.

#remotenode
MariaDB [(none)]> show master status;
+--------------------+----------+---------------------------------------+------------------+
| File               | Position | Binlog_Do_DB                          | Binlog_Ignore_DB |
+--------------------+----------+---------------------------------------+------------------+
| mariadb-bin.000025 |      245 | database_number_1,database_number_2   |                  |
+--------------------+----------+---------------------------------------+------------------+

Then we set the localnode as a slave to the remotenode.

#localnode
MariaDB [(none)]> slave stop; 

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_PORT = '3305', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mariadb-bin.000025', MASTER_LOG_POS = 245; 

MariaDB [(none)]> slave start;

Of course, in the situation where both databases are listening on 3306, and the ssh tunnel is listening on 3305, you would need to define the port as 3305 for both CHANGE MASTER commands.

Now we can test!  From the localnode, create a database.

MariaDB [(none)]> create database database_number_1;

If you go over to the remotenode, you will see that the db has been created.  Similarly, when you make a change to the secondary, it will be reflected in the primary.

Have fun!  My experience has been that the two instances sync instantaneously.  I have a standard Time Warner internet connection, and I was unable to detect a lag in the remote node, even when scripting thousands of inserts and updates on the local node.  Your results may vary, depending on connection reliability and connection speed.

References:

MariaDB: https://mariadb.com/

MariaDB Installation Guide: https://downloads.mariadb.org/mariadb/repositories/

Ubuntu Documentation: SSH/OpenSSH/Keys – https://help.ubuntu.com/community/SSH/OpenSSH/Keys

How to Set Up MySQL Master-Master Replication – https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication