Full guide on db replication via SSH tunnel

All commands in red must be replaced with actual values used in your environment.

Prelude

Configuring MySQL replication leaves port 3306 open to the Internet and data between the replication servers is not encrypted. Using SSH tunneling MySQL replication data can be transferred through the SSH connection. This method doesn’t need any additional ports to be opened in the firewall. For this article:

  • The master IP is 1.1.1.1
  • The slave IP is 2.2.2.2

Commands to be entered in the master are marked (master) and commands for slave are marked (slave).

It is assumed that you’ve read this article on MySQL replication.

Step 1: Setup SSH Tunneling

Create a user and assign a password. This user will be used to create the SSH tunnel: (master)

The tunneluser must be allowed to connect only from the slave server so it must be entered in the/etc/ssh/sshd_config file as an allowed user. (master)

Since allowed users are being defined, users not in this list will be denied access, so add the administrative users who will use SSH to login to this VPS. (master)

Restart the SSH server: (master)

The steps to generate a SSH key are mentioned in this article but I’ll repeat the commands here. (slave)

Sample output:

Do NOT enter a passphrase because this key will be used to automatically establish a SSH tunnel which will not be possible if it contains a passphrase. Copy the public key to the master server. (slave)

Sample output:

The tunneluser user will be used only for tunneling and not for administration so change the shell tonologin and remove the password. (master)

Debian and Ubuntu users replace /sbin/nologin with /usr/sbin/nologin.

Create a SSH tunnel using the following command. (slave)

The tunnel has been created, so accessing port 33061 at localhost will connect the slave to the master via SSH. The -f parameter runs this command in the background and the -N parameter means “Do not execute a command” since the tunneluser has a nologin shell.

Step 2: Configuring the replication settings on the MASTER

Open up the mysql configuration file on the master server.

Once inside that file, we need to make a few changes.

The first step is to find the section that looks like this, binding the server to the local host… and… leave it :).  If you were setting up db replication without an ssh tunnel this might be different.

The next configuration change refers to the server-id, located in the [mysqld] section. You can choose any number for this spot (it may just be easier to start with 1), but the number must be unique and cannot match any other server-id in your replication group. I’m going to go ahead and call this one 1.

Move on to the log_bin line. This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. For this step we simply need to uncomment the line that refers to log_bin:

Finally, we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need. Just replace the name below with the name of the database you wish to replicate.

After you make all of the changes, go ahead and save and exit out of the configuration file.

Refresh MySQL.

The next steps will take place in the MySQL shell, itself (NB we are still on the master server here).

We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

Follow up with:

Switch to your database

Following that, lock the database to prevent any new changes:

Then type in:

You will see a table that should look something like this:

This is the position from which the slave database will start replicating. Record these numbers, they are needed later.

If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.

Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQLshell).

Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.

Now you are all done with the configuration of the the master database.

Yay.

Step Three—Configure the Slave Database

Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database.

Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

Get the database dump from the master server to the slave server

With the database dump now on the slave server, import it into the database you just created:

We’re nearly there now…

Now we need to configure the slave configuration in the same way as we did the master:

We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.

NB the replay log probably wont already exist in the my.cnf file… so you will have to create it.

Restart MySQL once again:

 

Step Four — Setting it all off!

The next step is to enable the replication from within the MySQL shell.

Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

The only thing we need to change here is the value of ‘MASTER_LOG_FILE’ and the value of ‘MASTER_LOG_POS’. These tell the slave where to start replication from.

Now.. Activate the slave server:

And you’re done.

Additional info:

To view the status of the slave. From the slave server, log into the mysql shell and run

The \G rearranges the text to make it more readable.

You can always test the connectivity of the slave to the master via the ssh tunnel with this command:

 

MySQL replication has a lot different options, and this was just a brief overview.

If you have any further questions about the specific capabilities of MySQL, feel free to post your questions in our Q&A Forum and we’ll be happy to answer them.

 

This guide has been put together for my own use/reference. The content originates from:

https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-replication-using-ssh-on-a-vps

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

3 thoughts on “Full guide on db replication via SSH tunnel”

  1. Thanks for the article, it has been very useful for me.

    But, what will be happen when the server (slave) is restarted? Would it be necessary to automatically create the SSH connection on boot?

      1. Thank you very much for your article.
        I followed all the explanations and configure the replication scenario with two servers well.

        Its very important to write the mysql parameters in my.cnf ‘located in the [mysqld] section’ I didn’t, and it does not run until I’ve been realised that was for this reason.

        Could you show me where they explain a little how to do an autostart? Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *