Master-slave replication is used to solve a number of different problems with performance, supporting the backup of different databases, and as a part of a larger solution to alleviate system failures. It 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 to send the 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.
The target usage for replication in MariaDB and MySQL databases includes:
How you can use such replication and take advantage of it:
Now let’s examine a simple example on how to configure master-slave replication on CirrusGrid PaaS.
You are able to set a DB cluster in a two ways:
You can get a MySQL or MariaDB database cluster with required replication type (including the master-slave one) up and running within minutes via CirrusGrid marketplace.
If you prefer to configure the Master-Slave replication manually in order to get more slave replicators or specify some custom configurations, please, follow the instruction below.
Tip: The instruction below is fully suitable for MySQL database servers.
First of all we create two environments in CirrusGrid for our master and slave databases.
1. Log in to CirrusGrid dashboard.
2. Ask CirrusGrid 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. 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.
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 as it is shown below:
server-id = 1 log-bin = mysql-bin binlog-format=mixed
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. CirrusGrid sent you an email with credentials to the database. Log in using these credentials.
5. Navigate to the User accounts tab and click on Add user account.
6. Specify the name and password for your slave replication user.
Now, scroll down and tick the replication client and replication slave administration privileges.
Click Go at the bottom of the page.
Let’s go back to the CirrusGrid dashboard and configure our slave base.
1. Click Config button for your slave database.
2. Navigate to my.cnf file and add the following strings:
server-id = 2 slave-skip-errors = all
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.
This skipping is 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.
3. Next, open the /etc/phpMyAdmin/config.inc.php file and append it with the next option:
$cfg['AllowArbitraryServer'] = true;
4. Save the changes and restart your slave database server in order to apply the new configuration parameters.
5. Navigate to phpMyAdmin using the credentials which CirrusGrid sent you when you created the environment for your slave database.
6. Go to the Replication tab click Configure for Slave replication.
7. Configure your master server (enter the name, the password and the host of your slave replication user).
Now you master server is configured.
8. Click on Control slave > Full start for the slave server in order to run Slave SQL and Slave IO threads.
9. Check the slave status table to ensure that everything is ok.
We have to ensure now that master-slave replication works for our databases.
1. Let’s create the new database (e.g. CirrusGrid) in our master base.
2. Navigate to slave base and you’ll see that the new database was successfully replicated.
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:
er) { printf("Unable to connect master database server. Error: %s\n", 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: %s\n", 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); ?>
Now you have your own database replication in the cloud. Enjoy!
Powered by BetterDocs
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.