How to set up database replication with MariaDB

If you plan on setting up a database server using MariaDB, you might want to consider including database replication. Master-Slave data replication allows for data to be automatically replicated to multiple computers for backup and analysis purposes.

I’ll show how to quickly set up MariaDB to use replication, so you can be sure your database is always being backed up to another server. This setup is simple, but it does require attention to details. I assume that you have MariaDB set up on two machines, and one machine has a database with data.

With that said, let’s set this up.

Note: I will set this up on a 192.168.1.x network; if your network is different, modify the commands as needed. My Master IP address will be 192.168.1.124, and my Slave IP address will be 192.168.1.177.

SEE: Why some of the fastest growing databases are also the most experimental (TechRepublic)

The Master

There’s not much to set up on the Master. You must open the /etc/mysql/my.cnf file and add the following lines at the bottom.

[mariadb]
​log-bin = mysql-bin
​server-id = 1
​log-basename = master1
​bind-address=0.0.0.0

Save and close that file. Now you must go to the mysql prompt with the command:

mysql -u root -p

Note: I assume your MySQL user is “root.” If you use a different user, you’ll want to modify the commands as we go.

From the mysql prompt, issue the following commands (PASSWORD is the password for the user root).

GRANT REPLICATION SLAVE ON *.* TO root;
​GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.177' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Restart MySQL with the command sudo service mysql restart and that’s all we have to do for the Master.

The Slave

Now we head over to the Slave. Open the /etc/mysql/my.cnf file and add the following at the bottom:

[mariadb]
​server-id = 2
​bind-address=0.0.0.0

Save and close that file. Restart the MySQL database with the command sudo service mysql restart. Now open the mysql prompt with the command mysql -u root -p. From the MySQL prompt, we’ll enter the following commands.

CHANGE MASTER TO master_host="192.168.1.124", master_user="root", master_use_gtid=slave_pos;
​START SLAVE;

At this point, you can run this command from the Slave MySQL prompt.

SHOW SLAVE STATUS;

You should see a quite a bit of output, which will include the line:

Waiting for master to send event | 192.168.1.124

You can test to see if the database is replicating by using a database (such as USE clients;) and then issuing the command (DBNAME is the name of one of your databases on the master).

SELECT * FROM DBNAME;

Your database is now replicating.

The easiest means of database backup

You’d be hard pressed to find an easier method of getting a real-time database backup than replication. And with MariaDB, setting up replication is incredibly simple. Give this a try, and you can rest easier knowing you always have a backup database to use.