Database Master-Slave Replication in the Cloud

Many developers use master-slave replication to solve a number of different problems, including problems with performance, supporting the backup of different databases, and as a part of a larger solution to alleviate system failures. As you know master-slave replication enables data from one database server (the master) to be replicated to one or more database servers (the slaves). The master logs the updates, which then ripple through to the slaves. The slave outputs a message stating that it has received the update successfully, thus allowing the sending of subsequent updates. Master-slave replication can be either synchronous or asynchronous. The difference is simply the timing of propagation of changes. If the changes are made to the master and slave at the same time, it is synchronous. If changes are queued up and written later, it is asynchronous.

dbreplication17

The target usage for replication in MariaDB and MySQL databases includes:

  • Scale-out solutions – spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.
  • Data security – as data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
  • Analytics – live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.
  • Long-distance data distribution – if a branch office would like to work with a copy of your main data, you can use replication to create a local copy of the data for their use without requiring permanent access to the master.

Let’s examine a few examples on how you can use such replication and take advantage of it:

  • Backups: to use replication as a backup solution, replicate data from the master to a slave, and then back up the data slave. The slave can be paused and shut down without affecting the running operation of the master, so you can produce an effective snapshot of “live” data that would otherwise require the master to be shut down.
  • Scale-out: you can use replication as a scale-out solution; that is, where you want to split up the load of database queries across multiple database servers, within some reasonable limitations. Because replication works from the distribution of one master to one or more slaves, using replication for scale-out works best in an environment where you have a high number of reads and low number of writes/updates. Most Web sites fit into this category, where users are browsing the Web site, reading articles, posts, or viewing products. Updates only occur during session management, or when making a purchase or adding a comment/message to a forum. Replication in this situation enables you to distribute the reads over the replication slaves, while still enabling your web servers to communicate with the replication master when a write is required.
  • Spreading the load: there may be situations when you have a single master and want to replicate different databases to different slaves. For example, you may want to distribute different sales data to different departments to help spread the load during data analysis.
  • Increasing the performance: as the number of slaves connecting to a master increases, the load, although minimal, also increases, as each slave uses a client connection to the master. Also, as each slave must receive a full copy of the master binary log, the network load on the master may also increase and create a bottleneck. If you are using a large number of slaves connected to one master, and that master is also busy processing requests (for example, as a part of a scale-out solution), then you may want to improve the performance of the replication process. One way to improve the performance of the replication process is to create a deeper replication structure that enables the master to replicate to only one slave, and for the remaining slaves to connect to this primary slave for their individual replication requirements.
  • Failover alleviating: You can set up a master and a slave (or several slaves), and to write a script that monitors the master to check whether it is up. Then instruct your applications and the slaves to change master in case of failure.
  • Security: you can use SSL for encrypting the transfer of the binary log required during replication, but both the master and the slave must support SSL network connections. If either host does not support SSL connections, replication through an SSL connection is not possible. Setting up replication using an SSL connection is similar to setting up a server and client using SSL. You must obtain (or create) a suitable security certificate that you can use on the master, and a similar certificate (from the same certificate authority) on each slave.

Now let’s examine a simple example on how to configure master-slave replication on Jelastic PaaS.

Create environments

*The instruction below is fully suitable for MySQL database servers.

First of all we create two environments in Jelastic for our master and slave databases.

1. Go to jelastic.com and sign up if you haven’t done it yet or log in with your Jelastic credentials by clicking the Sign In link on the page.

2. Ask Jelastic to create a new environment.

3. In the Environment topology dialog pick MariaDB (or MySQL) as a database you want to use. Set the cloudlet limit and type the name of your first environment, for example, masterbase.

Wait just a minute for your environment to be created.

4. In the same way create one more environment with MariaDB or just clone it. Let’s name it slavebase.

BTW it will be located on the other hardnode, what is even more secure and reliable for storing your data.

Now you have two identical environments with two databases.

Configure master database

Let’s configure master base now.

1. Click config button for your master database.

2. Navigate to my.cnf file and add the following properties:

server-id = 1
log-bin = mysql-bin
binlog-format=mixed

dbreplication15

We use binlog format “mixed” (binlog-format=mixed) to allow a replication of operations with foreign keys.

Note: Do not use binlog format “statement” (otherwise you will get errors later on!)

3. Save the changes and restart MariaDB in order to apply the new configuration parameters.

4. Click the Open in browser button for MariaDB. Jelastic sent you an email with credentials to the database. Log in using these credentials.

5. Navigate to the Replication tab and click on Add slave replication user.

dbreplication1

6. Specify the name and password for your slave replication user and click Go.

dbreplication2

Now your slave user is successfully created.

dbreplication3

Configure slave database

Let’s go back to the Jelastic dashboard and configure our slave base.

1. Click config button for your slave database.

dbreplication4

2. Navigate to my.cnf file and add the following strings:

server-id = 2
slave-skip-errors = all

dbreplication14

We allow our slave base to skip all errors from master (slave-skip-errors = all) in order not to stop normal slave operation in case of errors on master base.

3. Save the changes and restart your slave database server in order to apply the new configuration parameters.

dbreplication6

4. Navigate to phpMyAdmin using the credentials which Jelastic sent you when you created the environment for your slave database.

5. Go to the Replication tab click configure for Slave replication.

dbreplication7

6. Configure your master server (enter the name, the password and the host of your slave replication user).

dbreplication8

Now you master server is configured.

dbreplication9

7. Click on Control slave -> Full start for the slave server in order to run Slave SQL and Slave IO threads.

dbreplication10

8. Check the slave status table to ensure that everything is ok.

dbreplication11

Check the results

We have to ensure now that master-slave replication works for our databases

1. Let’s create the new database (e.g. Jelastic) in our master base.

dbreplication12

2. Navigate to slave base and you’ll see that the new database was successfully replicated.

dbreplication13

Connection to master-slave

Here are two examples on how to connect to your master and slave databases from Java and PHP application.

1. As an example here you can see the code of our Java application which connects to master and slave databases.

Database_config.cfg:

master_host=jdbc:mysql://mariadb-master-host/mysql
master_username=root
master_password=abcABC123

slave_host=jdbc:mysql://mariadb-slave-host/mysql
slave_username=root
slave_password=abcABC123

driver=com.mysql.jdbc.Driver

Dbmanager.java:

package com.jelastic.test;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DbManager {

    private final static String createDatabase = "CREATE SCHEMA IF NOT EXISTS jelastic";
    private final static String showDatabases = "SHOW DATABASES";

    public Connection createMasterConnection() throws IOException, ClassNotFoundException, SQLException {
        Connection masterConnection;
        Properties prop = new Properties();
        prop.load(new FileInputStream(System.getProperty("user.home") + "/database_config.cfg"));
        String master_host = prop.getProperty("master_host").toString();
        String master_username = prop.getProperty("master_username").toString();
        String master_password = prop.getProperty("master_password").toString();
        String driver = prop.getProperty("driver").toString();

        Class.forName(driver);
        masterConnection = DriverManager.getConnection(master_host, master_username, master_password);
        return masterConnection;
    }

    public Connection createSlaveConnection() throws IOException, ClassNotFoundException, SQLException {
        Connection slaveConnection;
        Properties prop = new Properties();
        prop.load(new FileInputStream(System.getProperty("user.home") + "/database_config.cfg"));
        String slave_host = prop.getProperty("slave_host").toString();
        String slave_username = prop.getProperty("slave_username").toString();
        String slave_password = prop.getProperty("slave_password").toString();
        String driver = prop.getProperty("driver").toString();

        Class.forName(driver);
        slaveConnection = DriverManager.getConnection(slave_host, slave_username, slave_password);
        return slaveConnection;
    }

    public boolean runSqlStatementOnMaster() {
        boolean execute = false;
        Statement statement = null;
        try {
            statement = createMasterConnection().createStatement();
            execute = statement.execute(createDatabase);
        } catch (IOException ex) {
          Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
          Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return execute;
    }

    public List<String> runSqlStatementOnSlave() {
        List<String> stringList = new ArrayList<String>();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = createSlaveConnection().createStatement();
            resultSet = statement.executeQuery(showDatabases);
            while (resultSet.next()) {
                stringList.add(resultSet.getString(1));
            }
        } catch (IOException ex) {
          Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
          Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return stringList;
    }
}

2. Connection to master and slave databases for your PHP application:

<?php 
/* Master settings */
$master_server = "xx.xxx.x.xx";
$master_username = "root";
$master_password = "abcABC123";

/* Slave settings */
$slave_server = "xx.xxx.x.xx";
$slave_username = "root";
$slave_password = "abcABC123";

$link_to_master = mysqli_connect(
 $master_server,
 $master_username,
 $master_password,
 'mysql');

if (!$link_to_master) {
 printf("Unable to connect master database server. Error: %sn", mysqli_connect_error());
 exit;
}

$link_to_slave = mysqli_connect(
 $slave_server,
 $slave_username,
 $slave_password,
 'mysql');

if (!$link_to_slave) {
 printf("Unable to connect slave database server. Error: %sn", mysqli_connect_error());
 exit;
}

print("
 Creating database with name Jelastic on Master node ");

$result = mysqli_query($link_to_master, 'CREATE DATABASE JelasticX');

sleep (3);

print("
 Checking if created database was replciated to slave ");

if ($result = mysqli_query($link_to_slave, 'SHOW DATABASES LIKE "JelasticX"')) {
 $result_text = mysqli_fetch_array($result);
 print ("
 Replicated database is ".$result_text[0]);
}

mysqli_close($link_to_master);
mysqli_close($link_to_slave);
?>

Database replication with MariaDB adds redundancy, helps to ensure high availability, simplifies certain administrative tasks such as backups, may increase performance and much more. Now you can forget about all problems with performance, backups of different databases and system failures. It’s become easy to configure database replication in the cloud, only few minutes and everything is ready. Enjoy!

9 Responses to “Database Master-Slave Replication in the Cloud”

  1. Layershift Limited (@Layershift)

    http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_slave-skip-errors emphatically warns NOT to use slave-skip-errors = all

    Can you discuss more about why you recommend it?

    Reply
    • Marina Sprava

      It’s not recommended for using at the development stage in order to find out bugs etc. But here we talk about production, when your code has been already tested. Any error on the master’s side can stop slave or lead to its unsynchronization. In this case the error may be even innocuous. Some minor errors will probably occur on production. So the replication can be stopped because of small errors.

      Reply
  2. Layershift Limited (@Layershift)

    Yes, errors halting the slave (as can happen without slave-skip-errors = all) can cause the slave to fall behind the master (e.g. if the error is fixed on the master but not manually skipped on the slave), but ignoring the errors can also cause the same consequences – only worse because it can be harder to recover from and also more difficult to notice.

    Errors occur because something went wrong; you don’t want your replication to ignore that and keep going regardless! That’s probably the worst thing to do in this situation (*especially* in a production environment).

    I hope that readers will take a warning that database inconsistency can occur by using this setting – it should not be used like this for production use. slave-skip-errors should only be used by advanced users on specific targeted error codes where they fully understand the consequence of what they’re doing and why they’re doing it.

    Normally the point of replication is to keep the data the same between master and slave, so for that to happen the errors should not be skipped without any thought. Instead when an error occurs it should be properly investigated/understood and resolved; you have to be very careful to make sure that you’re not creating more problems than you’re solving by skipping errors like this.

    Instructions like this just telling a user to make certain settings without any indication about the possible/likely negative side effects from them are potentially very dangerous.

    I urge to be more careful about the completeness of recommendations put forward here because it could lead to a situation like: “I configured as instructed on the Jelastic blog, but I had data loss issues – even though this is the recommended setup.”

    I’m not aware of any best practice recommendations (before here) that suggest ‘skip-slave-errors = all’ is a wise setting to use for production deployments. If you know of some references that explain why it makes sense (contrary to the MySQL documentation) it would probably make a good new blog post to discuss it a bit :)

    Reply
  3. Ihor Kolodyuk

    Hi all,

    @Layershift Limited (@Layershift)

    I really respect your opinion, but cannot agree with you and I will try to explain why:

    Actualy this comment should be splited into two different parts for clarifying:

    Part I – Development
    Responsibility – Development team.

    One of the main targets that are spoted to the development team – is to create code that is ready for different production situation that can suddenly happen and appear in the database they are working on.
    Skipping errors on that stage is not good idea, because in case if error occured – this need to be good investigated and based on that to be good procceded in a code to avoid similar situations on production.

    Part II – Production usage
    Responsibility – Operations team / System admins.

    Usualy operations team is not good enough familiar with all the projects to check and resolve each problem that occurs, actualy all they can do when they found an exotic problem in database – is just to report the problem to developemnt team, fixing this bug/problem can take days/weeks untill the project can be finaly updated in production, especial when the project is realy big and the decisions to fix something on production are not fast.

    I was working in development/hosting services company for long time and had been servicing houndreds of heavy projects that been using databases > 100Gb. In 99% of cases code that is comming to production is not ideal enough for working with databases and there are many of stupid errors occurs in each database during each usual working day.
    Most of such errors usualy are result of:

    - attempting to insert a reccord that already exists
    - dropping table that no more exists
    - creating a database without “if not exists” statement when such databases already exisits
    - dropping database that no more existis
    - inserting a reccord with invalid data into logging table ( that is also not a reason to stop full production )
    - etc

    Yes, yes, I know that this errors are ridiculous and it’s realy bad that such a code is comming to production, but unfortunately in life expecience it is usualy things.

    At that point you should say – you can use option “slave-skip-errors = CODE1, CODE2, CODE3, CODE4, CODE5 …”

    True, but nobody really know which stupid error to expect then.

    Downtime for production is very critial issue, most well known fact is “TIME is MONEY”.
    Usualy you cannot allow even slave to be halted and be not operable because the code that came to production is not ideal.

    In fact, it is more cheaper and comfortable for most OPS departments to set option “slave-skip-errors = all”, create regular dumps from slave and report problems to development then just spent a day in catching errors manualy and stopping production each time when stupid error occurs making companies loose their money because of that.

    Reply
  4. Layershift Limited (@Layershift)

    @Ihor

    The point I’m making is not so much about whether this setting should be used or not – that’s a decision for each individual user to decide for themselves depending on their own requirements.

    The reason I’m commenting about this is only that this slave-skip-errors = all setting is potentially dangerous, but the article does not acknowledge that point at all. That’s dangerous.

    You make some good points about why in some cases it could be desirable to set it like that, but a user should fully understand the consequence of what settings they put in place and why they’re doing it.

    The article only says (part of) WHY, but not about the potential bad things that might happen as a result of applying this setting. It’s important to give the full context so users have all of the appropriate information…

    Reply
  5. Database Master-Slave Replication in the Cloud « The MariaDB Blog

    [...] can also be done with cloud database servers. This guest post from Jelastic (originally published here) describes how to set up MariaDB master-slave replication using their Jelastic PaaS (Platform as a [...]

    Reply
  6. MongoDB Master Slave Replication

    [...] we’ve already told you in our previous post about MySQL master-slave replication the database replication offers various benefits depending on its type and the options you [...]

    Reply
  7. Database Master-Slave Replication in the Cloud | phamvanhungkeio

    [...] also be done with cloud database servers. This guest post from Jelastic (originally published here) describes how to set up MariaDB master-slave replication using their Jelastic PaaS (Platform as a [...]

    Reply
  8. Running WordPress Cluster on Jelastic

    [...] Use the “Database Master-Slave Replication in the Cloud” tutorial to get a MySQL cluster configured in just a few [...]

    Reply

Leave a Reply