Replication in MariaDB is a technique to ensure data from one MariaDB database server (master) is continuously copied to one or more MariaDB database servers (slaves). Replication is asynchronous by default – slaves might not get updates immediately.
Why use replication?
- To scale out read queries (SELECT), offloading them from the master to slave servers.
- Data safety, master server can fail, but data is safe as long as at least one slave server is intact.
- To improve performance of your application.
Follow these steps to configure replication:
1. First, you need to set up the master server. Edit the configuration file:
```
vim /etc/mysql/my.cnf
```
Add these lines under [mysqld]:
```
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = database_to_replicate
```
Where ‘database_to_replicate’ is the database you want to replicate. Save and close the file.
1. Restart MariaDB server:
```
sudo service mysql restart
```
1. Log in to the MariaDB shell and create a user for replication:
```
GRANT REPLICATION SLAVE ON . TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;
```
Where ‘slave\_user’ is the username and ‘password’ is the password.
1. Find out some information you will need when setting up the slave server:
```
SHOW MASTER STATUS;
```
You’ll need the filename and position later on.
1. Now, set up the slave server. Edit the configuration file:
```
vim /etc/mysql/my.cnf
```
Add these lines under [mysqld]:
```
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = database_to_replicate
```
1. Restart MariaDB server on the slave server:
```
sudo service mysql restart
```
1. On the slave server, login to MariaDB shell and run the following commands, using data you’ve got from the ‘SHOW MASTER STATUS’ command on the master server.
```
CHANGE MASTER TO MASTER_HOST=‘master_IP’,MASTER_USER=‘slave_user’, MASTER_PASSWORD=‘password’, MASTER_LOG_FILE=‘filename_from_master_status’, MASTER_LOG_POS=position_from_master_status;
```
Replace master_IP with the IP address of the master server, slave_user and password with the replication user credentials you created on the master server, filename_from_master_status and position_from_master_status with the values you got from the ‘SHOW MASTER STATUS’ command.
1. Start the slave:
```
START SLAVE;
```
1. Check the slave status:
```
SHOW SLAVE STATUS\G;
```
You should see ‘Yes’ in both Slave_IO_Running and Slave_SQL_Running. That means your setup is working.
Remember to backup your databases regularly. Although replication is a nice solution for data safety, it won’t help you if you accidentally delete data.
