Intro

The simplest and probably most common replication method is master-slave replication. Basically, data is replicated from master database to the slave. In case of master database failure, you must get the slave database up-to-date before failover and then promote the slave to be the new master.

Another method is to set up replication in both directions called master-master replication. But you must be aware that this setup brings some potential issues as data changes are happening on both nodes. It could be a problem if you have tables with auto_increment fields. If both servers are inserting or updating in the same table replication will break on one server due to “duplicate entry” error. To resolve this issue you have "auto_increment_increment" and "auto_increment_offset" settings.

In my case, it's best to use master-master setup as active-passive replication. If we know that only one node is performing data modifications we can avoid any possible problems. In case of the failover "slave" could be easily promoted to a new master. Data modifications are automatically replicated to failed node when it comes back up.

Of course, this simple setup is not suitable for all situations and it has it's drawbacks but luckily you have several other options at your disposal, like MariaDB Galera Cluster.

Install MariaDB 10 (both nodes (Server1 , Server2 )  ).

The examples in this article will be based on two VPS, named Server 1 and Server 2.

Server 1

Server 2

Install MariaDB 10.1 on Ubuntu14.04/15.10/16.04

I always like to install software packages from official repository (if there’s one) rather than from my Linux distribution repository. For one thing, I can install the latest stable version. For another, I don’t have to worry about distribution specific modifications. In other words, what I got is a bog-standard package no matter what Linux distribution I use.

This tutorial will guide you through the process of installing the latest stable version of MariaDB and that’s MariaDB 10.1 on Ubuntu 14.04 and 15.10, 16.04. Ubuntu repository has MariaDB 10.0, but no MariaDB 10.1.

Step1: Install software-properties-common

$ sudo apt-get install software-properties-common

Step2: Fetch MariaDB signing key from Ubuntu’s key server.

$ apt-key adv --keyserver ha.pool.sks-keyservers.net --recv-keys F1656F24C74CD1D8

Step3: add MariaDB repository to your system.
ubuntu 14.04

$ sudo add-apt-repository 'deb [arch=amd64,i386] http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu trusty main'

ubuntu 15.10

$ sudo add-apt-repository 'deb [arch=amd64,i386] http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu wily main'

Ubuntu 16.04

$ sudo add-apt-repository 'deb [arch=amd64,i386] http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu xenial main'

Step4: Update server

$ sudo apt-get update

Step4: Update server

$ sudo apt-get install mariadb-server

You will be asked to set a password for the MariaDB root user.



After it’s installed, the mysqld process will be automatically started.

Check version

root@server1:~# mysql --version

Output:
mysql Ver 15.1 Distrib 10.1.16-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

root@server1:~# 

Finally, we should execute the secure installation script to remove an anonymous user, disable remote root login and remove test database for security reasons.

root@server1:~# mysql_secure_installation

You will be asked these questions:
Enter current password for root (enter for none): press enter
Set root password? [Y/n] y
New password: Re-enter new password: Repeat the password
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] <-- y
Reload privilege tables now? [Y/n] y

Test the login to MariaDB with the "MySQL command"

root@server1:~# mysql -uroot -p
Output: 

Welcome to the MariaDB monitor. Commands end with ; or \g
Your MariaDB connection id is 9
Server version: 10.1.16-MariaDB-1~xenial mariadb.org binary distribution
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

To leave the MariaDB shell, enter the command "quit" and press enter.

Stop MariaDB on both nodes:

root@server1:~# sudo service mysql stop

MASTER1
Edit /etc/mysql/my.cnf parameter file.

root@server1:~# nano /etc/mysql/my.cnf

# bind-address = 127.0.0.1
server-id = 61
report_host = master1
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
# replicate-do-db = testdb

# bind-address = 127.0.0.1
By default, mysql will accept connections only from the local host. We will comment this line to enable connections from other hosts. This is important for replication to work.

server-id = 1
report_host = server1
Choose ID that will uniquely identify your host. I will use last two digits of my IP address. Optionally you could set report_host parameter for servers to report each other their hostnames.

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

relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
Enable creating relay log files. Events that are read from master’s binary log are written to slave relay log.

replicate-do-DB = testdb
With this parameter, we are telling to MariaDB which databases to replicate. This parameter is optional.

Now we can start MariaDB server.

root@server1:~# sudo service mysql start

root@server1:~# sudo mysql -uroot -p
                MariaDB [(none)]> create user 'replusr'@'%' identified by 'replusr';
                MariaDB [(none)]> grant replication slave on *.* to 'replusr'@'%';

MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB               |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000009 | 634      |              |                  |
+--------------------+----------+--------------+------------------+

MASTER2
Edit /etc/mysql/my.cnf parameter file.

root@server2:~# nano /etc/mysql/my.cnf
# bind-address = 127.0.0.1
server-id = 2
report_host = server2
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
# replicate-do-db = testdb

Start MariaDB server.

root@server2:~# sudo service mysql start

Create a user which will be used for replication and grant privileges to the user.

root@server2:~# sudo mysql -uroot -p
                MariaDB [(none)]> create user 'replusr'@'%' identified by 'replusr';
                MariaDB [(none)]> grant replication slave on *.* to 'replusr'@'%';

To start replication enter following commands.

 MariaDB [(none)]> STOP SLAVE;
 
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='server1', MASTER_USER='replusr',
-> MASTER_PASSWORD='replusr', MASTER_LOG_FILE='mariadb-bin.000009', MASTER_LOG_POS=634;
 
MariaDB [(none)]> START SLAVE;

For MASTER_LOG_FILE and MASTER_LOG_POS, I have used information from "show master status" on the first node.

Check status information of the slave threads.

 MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: server1
 Master_User: replusr
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mariadb-bin.000009
 Read_Master_Log_Pos: 634
 Relay_Log_File: relay-bin.000002
 Relay_Log_Pos: 537
 Relay_Master_Log_File: mariadb-bin.000009
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: testdb
 Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 634
 Relay_Log_Space: 828
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Using_Gtid: No
 Gtid_IO_Pos:

Notice that Read_Master_Log_Pos and Exec_Master_Log_Pos are in sync which is a good indicator that our databases are in sync.


Check status information about binary log files of the MASTER2 node. We will need this information to start replication on MASTER1 node.

 MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB               |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000009 | 759      |              |                  |
+--------------------+----------+--------------+------------------+

MASTER1

Start replicating data from MASTER2 to MASTER1 node.

MariaDB [(none)]> STOP SLAVE;
 
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='server2', MASTER_USER='replusr',
-> MASTER_PASSWORD='replusr', MASTER_LOG_FILE='mariadb-bin.000009', MASTER_LOG_POS=759;
 
MariaDB [(none)]> START SLAVE;

MariaDB [(none)]> STOP SLAVE;MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: server2
 Master_User: replusr
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mariadb-bin.000009
 Read_Master_Log_Pos: 759
 Relay_Log_File: relay-bin.000002
 Relay_Log_Pos: 537
 Relay_Master_Log_File: mariadb-bin.000009
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: testdb
 Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 759
 Relay_Log_Space: 828
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 62
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Using_Gtid: No
 Gtid_IO_Pos: 

Everything seems to be OK.


Let’s create the test table and insert some rows to test our replication.

Master1

MariaDB [(none)]> create database testdb;
 
MariaDB [(none)]> use testdb;
Database changed
 
MariaDB [testdb]> CREATE TABLE users (id INT AUTO_INCREMENT,
 -> name VARCHAR(30),
 -> datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 -> PRIMARY KEY(id));
Query OK, 0 rows affected (0.50 sec)
 
MariaDB [testdb]> INSERT INTO users(name) VALUES ('Marko');
Query OK, 1 row affected (0.06 sec)
 
MariaDB [testdb]> select * from users;
+----+-------+---------------------+
| id | name | datum                |
+----+-------+---------------------+
| 1 | Marko | 2015-02-01 00:41:41  |
+----+-------+---------------------+
1 row in set (0.00 sec)

Master2

 MariaDB [testdb]> use testdb
Database changed
 
MariaDB [testdb]> select * from users;
+----+-------+---------------------+
| id | name | datum                |
+----+-------+---------------------+
| 1 | Marko | 2015-02-01 00:41:41  |
+----+-------+---------------------+
1 row in set (0.00 sec)
 
MariaDB [testdb]> INSERT INTO users(name) VALUES('John');
Query OK, 1 row affected (0.39 sec)
 
MariaDB [testdb]> select * from users;
+----+-------+---------------------+
| id | name | datum                |
+----+-------+---------------------+
| 1 | Marko | 2015-02-01 00:41:41  |
| 2 | John | 2015-01-31 16:17:55   |
+----+-------+---------------------+
2 rows in set (0.00 sec)

Master1

MariaDB [testdb]> select * from users;
+----+-------+---------------------+
| id | name | datum                |
+----+-------+---------------------+
| 1 | Marko | 2015-02-01 00:41:41  |
| 2 | John | 2015-01-31 16:17:55   |
+----+-------+---------------------+
2 rows in set (0.00 sec)

As we can see our table and rows are replicated successfully.



Let’s simulate crash of the MASTER1 node and power off the server.

root@server1:~# sudo shutdown -h now

While the server is down inserted some rows on MASTER2 node.

Master2

MariaDB [testdb]> INSERT INTO users(name) VALUES ('Eric');
Query OK, 1 row affected (0.41 sec)
 
MariaDB [testdb]> INSERT INTO users(name) VALUES ('Clive');
Query OK, 1 row affected (0.08 sec)
 
MariaDB [testdb]> INSERT INTO users(name) VALUES ('Maria');
Query OK, 1 row affected (0.09 sec)
 
MariaDB [testdb]> select * from users;
+----+-------+---------------------+
| id | name | datum                |
+----+-------+---------------------+
| 1 | Marko | 2015-02-01 00:41:41  |
| 2 | John | 2015-01-31 16:17:55   |
| 3 | Eric | 2015-01-31 16:19:49   |
| 4 | Clive | 2015-01-31 16:19:55  |
| 5 | Maria | 2015-01-31 16:20:01  |
+----+-------+---------------------+
5 rows in set (0.00 sec)

MariaDB [testdb]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
 Slave_IO_State: Reconnecting after a failed master event read
 Master_Host: server1
 Master_User: replusr
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mariadb-bin.000010
 Read_Master_Log_Pos: 1828
 Relay_Log_File: relay-bin.000012
 Relay_Log_Pos: 1083
 Relay_Master_Log_File: mariadb-bin.000010
 Slave_IO_Running: Connecting
 Slave_SQL_Running: Yes
 Replicate_Do_DB: testdb
 Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 1828
 Relay_Log_Space: 1663
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 2003
 Last_IO_Error: error reconnecting to master 'replusr@master1:3306' - retry-time: 
 60 retries: 86400 message: Can't connect to MySQL server 
 on 'master1' (111 "Connection refused")
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Using_Gtid: No
 Gtid_IO_Pos:

Check Last_IO_Error message while MASTER1 is down.

Now turn on MASTER1 node again.
MariaDB server and replication will start automatically and MASTER1 should catch up MASTER2.

MASTER1

Check "users" table - it's synchronized again.

root@server1:~#  mysql -u root -p -D testdb
 
MariaDB [testdb]> select * from users;
+----+-------+---------------------+
| id | name | datum                |
+----+-------+---------------------+
| 1 | Marko | 2015-02-01 00:41:41  |
| 2 | John | 2015-01-31 16:17:55   |
| 3 | Eric | 2015-01-31 16:19:49   |
| 4 | Clive | 2015-01-31 16:19:55  |
| 5 | Maria | 2015-01-31 16:20:01  |
+----+-------+---------------------+
5 rows in set (0.00 sec)

Please let me know if you see possible problems in this configuration. I will update post gladly. Thanks for reading!