Parallel Replication In MariaDB

Parallel Replication In MariaDB

Get Social!

Parallel replication has been available in MariaDB since Version 10.0.5, however requires at least version 10.0.5 on both the Master and Slave for parallel replication to work.

Parallel replication can help speed up applying changes to a MariaDB slave server by applying several changes at once.

What is Parallel Replication?

MariaDB replicates data from a master to a slave by shipping all changes that have been applied to the master to the slave in a serialised file. The file is then read by the slave and each change is applied one at a time. A change may be a single row change, such as an INSERT, a DDL change or statement that is applied in it’s entirety such as INSERT INTO… SELECT. The bottle neck to this process is that the changes which need to be applied are read in serial – that is, one at a time.

Parallel replication tries to overcome this by applying DML statements in parallel by reading ahead in the relay log (the log on the slave with changes waiting to be applied) and giving work to each parallel worker to apply, in parallel! Each parallel worker has a cache that allows it to read ahead in the log and apply statements that can be applied in parallel – these are usually statements applied in a single transaction, or statements that have been committed in the same group.

The above diagrams show the differences between the different replication mode. Up to the SQL Thread things work in much the same way, however in parallel replication mode the SQL Thread behaves differently in that it moves work to the Worker threads rather than applying it directly itself.

Enable Parallel Replication

You will need MariaDB 10.0.5 or later running on both the master and the slave for parallel replication to be available.

Edit your MariaDB config file, my.cnf on some installations and edit or add the following parameter.

slave-parallel-threads=12

This will enable 12 parallel workers on the database Slave which will be started when your slave server is next restarted and replication is enabled.

You can see if the required number of workers has been started by running show processlist which will show 12 processes running as system user with various State information.

You can see further information by running show slave status which will show you the replication type, how up to date the replication is and if there are any errors.


MySQL Circular Replication

Category : How-to

Get Social!

mysql-logoI spoke about master – slave replication in my previous blog post and how to set up one way replication. The problem with one way replication is that any changes that are made to the slave would not be replicated to the master and would likely end up causing inconsistencies in future queries. This means that you can only apply updates on your master server.

If only two servers are involved then we can set up each server as both a master and a slave. This allows for two way replication – if changes are made on server 1 they are replicated on server 2, and vice versa. If more than two servers are needed for replication then it can get a little more complicated. Using the circular replication method we set up each server as a master, and as a slave to another master until all the masters have slaves. This results in a circular flow of information as each change is replicated to the next server in the chain. The below diagram indicates how replication changes would travel.

mysql-circular-replication

In the above illustration there are 8 MySQL Server nodes that all replicate changes they are sent directly and changes from the MySQL Server node before it are processed and sent to the next MySQL Server node along. A change made on any one of the MySQL Server nodes would be replicated to the next server along until every server in the chain has processed the update.

There are some drawbacks to to this type of configuration:

  • When many nodes are added it can take a long time for a change to one server to make it round the chain to update all other servers. For example, if a change is made on Server 2, it has to go to Server 3, then server 4, 5, 6 and 7 before Server 8 is notified of the change.
  • Conflicts can occur in MySQL Server circular replication. The way MySQL processes replication updates and direct SQL statements from clients is in parallel. MySQL does not guarantee consistency across all the databases in the chain. This is because a replication request may act on a column at the same time as a direct request from a client. Due to the time it takes to receive a replication update and process it, a direct request from a client could have updated the same row, even though, as far as the overall sequence of events is concerned, the initial event that triggered the replication from another server could have happened first.

Conflicts will have to be considered and affect the way a group of MySQL Servers set up in this way can be used. For example, you may choose to only update each table on one node at a time – Server 1 for the sales table, Server 2 for the payments table and so on. This will have to be built into your process for writing to these MySQL tables.

Setting up MySQL Server circular replication is straightforward – it’s essentially setting up master-slave replication multiple times for how many nodes have.

I’m not going to write out all of the commands for setting up circular replication because all the steps are written down in my blog post about master slave replication. Follow these steps to set up Server 1 as your master and then Server 2 as your slave. Then repeat the steps with Server 2 as your master and Server 3 as your slave. Continue doing this until you make your last server a master and then Server 1 as a slave to complete the circle.

And that’s all there is to it! Just remember to make sure you plan your workloads to protect your data from inconsistency issues.


MySQL Database Replication

Category : How-to

Get Social!

mysql-logoThe MySQL server can replicate a database over TCP to another instance of MySQL to provide a near real time backup for data redundancy. This process is not to be confused with MySQL working in a cluster to share workload and provide high availability. I’ll cover clustering in a later blog post.

MySQL uses a master and slave scenario where the master is where the changes are detected and the slave is where the changes are sent to. This means that changes are only replicated one-way and any changes on the slave will not be replicated on the master. To get round this, you can set up each server as a slave and as a master so that changes are sent both ways. This is called circular replication.

To set up one way replication this post will assume that you have MySQL installed on two servers, one the master and one the slave. If you have not installed MySQL server you can install it on Debian/ Ubuntu with apt-get.

apt-get install mysql-server

The below instructions must be executed on the correct server, either master or slave. Be careful that you are executing the right command on the right server!

MySQL Replication Mode

Before we get into setting up our replication server we need to consider the replication mode to use. There are two types of replication mode, or a third if you include the combination of the two.

  • Statement based replication is where each statement that is sent to the master is also sent to the slave. This means that, in most scenarios, the data will be the same on each server as the same statements have been executed on each server.
  • Row based replication is where each change to a row in a table on the master server is written to a log and sent to the slave. The slave server then updates the required rows with the literal data.
  • Mixed is a combination of the two – the MySQL server chooses which mode to use based on the task being performed.

Configure MySQL Replication

Replication, in this example, is done at a database level and changes will be replicated in one direction from master to slave. This means that any changes made to the slave will not appear on the master.

Master

The below changes should be made on the master MySQL server. A slightly different set of steps will be detailed below for the slave.

Open the my.cnf MySQL configuration file and make the following changes:

vi /etc/my.cnf

Find the bind-address attribute and change it to the IP address of the master server.  You can find your IP address by using ifconfig if you are not sure what it is.

bind-address = 10.1.1.100

Find or add the server-id attribute and make  sure it’s uncommented. You need to assign your master server an ID, let’s use 1 for our master server.

server-id = 1

Find or add the log_bin attribute and make sure it’s uncommented. This is the location where your master server will write all the changes that occur on the database.

log_bin = /var/log/mysql/mysql-bin.log

Add an entry to specify which MySQL database should be included for replication.

binlog_do_db = replication_database

You can add as many databases as you like by repeating the binlog_do_db attribute. For example:

binlog_do_db = replication_database1
binlog_do_db = replication_database2

The next step is to create a user which has the appropriate permission to use the MySQL replication features. Log in to MySQL with the below command, followed by your root MySQL user password.

mysql -u root -p

Create a new user which will be used to connect to the master instance from the slave to transfer the replication data.

GRANT REPLICATION SLAVE ON *.* TO 'mysql_rep'@'%' IDENTIFIED BY '[PASSWORD]';

The above example of granting privileges and creating a user are the easiest to get working but are the least secure. You may need to change this to meet your security requirements. You’ll also need to replace [PASSWORD] with the password you would like to use for the mysql_rep user.

Now lets create the database that will be replicated to our slave server. It’s important that after creating the database that nothing is changed until replication has been completely set up. If you already have a database then you will need to export the database, with all it’s data, and import it into the slave before completing the replication setup. This is because both databases must be in the same state for replication to keep everything in sync.

create database replication_database;

Type quit to exit the MySQL client and restart the MySQL server.

service mysql restart

At this point, we can check that the server is set up to write changes to the log file. Log back into MySQL Server Client and issue the below SHOW command.

mysql -u root -p

SHOW MASTER STATUS;

The output should be similar to the below, and indicates that the master is configured to log the changes.

mysql-show-master-status

And that should be your master MySQL server configured! Onto the slave…

Slave

The slave configuration is very similar to the master, however there are subtle differences.

Open the my.cnf MySQL configuration file and make the following changes:

vi /etc/my.cnf

Find the bind-address attribute and change it to the IP address of the master server.  You can find your IP address by using ifconfig if you are not sure what it is.

bind-address = 10.1.1.200

Find or add the server-id attribute and make  sure it’s uncommented. You need to assign your slave server an ID, let’s use 2 for our slave server. Keep in mind that this has to be unique across your replication environment so you must change it from the default of 1 that will likely be in the file already.

server-id = 2

Find or add the log_bin attribute and make sure it’s uncommented. This is the location where your slave server will write all the changes that occur on the database. In addition to the log_bin you’ll also need a relay-log file on your slave.

log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log

Add an entry to specify which MySQL database should be included for replication.

binlog_do_db = replication_database

Save the file and restart the MySQL server.

service mysql restart

We now need to tell the slave server where it can find the master server. Log into MySQL Server Client as the root user.

mysql -u root -p

Run the following command, and substitute your values as below.

  • MASTER_HOST is the IP address of your master server.
  • MASTER_USER is the user on the master that the slave should use to connect.
  • MASTER_PASSWORD is the password for the above user on the master server
  • MASTER_LOG_FILE is the logfile name on the master server that will be used for replication. This was displayed in the image above when running the command SHOW MASTER STATUS.
  • MASTER_LOG_POS is the location within the log file that your slave should start replicating from. The log position is also displayed with the SHOW MASTER STATUS command. Note, this will increase as changes are made to your master database.
CHANGE MASTER TO MASTER_HOST='10.1.1.100',
MASTER_USER='mysql_rep',
MASTER_PASSWORD='[PASSWORD]', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=  107;

The final steps are to start our slave, from which point any changes made to the master will be replicated, and check the status.

Execute the below to start replication:

START SLAVE;

And finally show the status of the slave replication to make sure everything is working.

SHOW SLAVE STATUS;

If you now make some changes on your master server, they should be immediately replicated to the slave. After making some changes, run the SHOW SLAVE STATUS command again and you should notice that the Position value has incremented.

You should be aware that almost any changes are replicated – new tables, indexes and changes in data will all be replicated in the same way.


Visit our advertisers

Quick Poll

Do you use ZFS on Linux?

Visit our advertisers