Replication in SQL databases is the process of copying data from the source database to another one (or multiple ones) and vice versa. Data from one database server are constantly copied to one or more servers. You can use replication to distribute and balance requests across a pool of replicated servers, provide failover and high availability of MariaDB databases. The MariaDB (and MySQL) allows to use two types database replication mades: Master-Master and Master-Slave.
In a Master-Master replication scheme, any of the MariaDB/MySQL database servers may be used both to write or read data. Replication is based on a special binlog file, a Master server saves all operations with the database to. A Slave server connects to the Master and applies the commands to its databases.
One Mysql client server : AWS linux Ec2
Two DB servers named DB1 and DB2 for our Master-Master Replication with private IPs
Install mariadb-server on both DB1 and DB2
yum install mariadb-server -y
systemctl mariadb.service enable
systemctl mariadb.service start
After installation we can enable the log_bin on both DB servers.
vim /etc/my.cnf.d/server.cnf
On DB1
[mysqld]
server-id=1
log_bin=/var/log/mariadb/mariadb-bin.log
On DB1
[mysqld]
server-id=2
log_bin=/var/log/mariadb/mariadb-bin.log
Restart mariadb on both servers
Now we can setup root password using "mysql_secure_installation" and create a secure password on both DB servers.
Restart mariadb on both servers
Access mysql on DB1:
mysql -u root -p
grant replication slave on *.* to master_user1 identified by 'password@123';
flush privileges;
flush tables with read lock;
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 1710 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Access mysql on DB2:
mysql -u root -p
grant replication slave on *.* to master_user2 identified by 'password@123';
flush privileges;
flush tables with read lock;
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 470 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Restart mariadb on both servers
MariaDB [(none)]> stop slave;
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='DB2_private_IP',
MASTER_USER='DB2_master_user',
MASTER_PASSWORD='master_user_password',
MASTER_LOG_FILE='DB2Log_file_name',
MASTER_LOG_POS=DB2Log_file_position;
MariaDB [(none)]> start slave;
MariaDB [(none)]> stop slave;
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='DB1_private_IP',
MASTER_USER='DB1_master_user',
MASTER_PASSWORD='DB1master_user_password',
MASTER_LOG_FILE='DB1Log_file_name',
MASTER_LOG_POS=DB1Log_file_position;
MariaDB [(none)]> start slave;
Creating User 'project' with remote access.
create user 'project'@'%' identified by 'dbuserpass';
grant all on *.* to 'project'@'%';
flush privileges;
Creating Database 'company' and table 'employees' with dummy data:
MariaDB [(none)]> create database company;
MariaDB [(none)]> use company;
MariaDB [company]> create table employees(ID int(5), Name varchar(50));
MariaDB [company]> insert into employees(ID, Name) values('11', 'Robin');
Query OK, 1 row affected (0.00 sec)
MariaDB [company]> insert into employees(ID, Name) values('15', 'Mike');
Query OK, 1 row affected (0.00 sec)
MariaDB [company]> insert into employees(ID, Name) values('20', 'Jessica');
Query OK, 1 row affected (0.00 sec)
MariaDB [company]> insert into employees(ID, Name) values('21', 'Carl');
Query OK, 1 row affected (0.00 sec)
MariaDB [company]> insert into employees(ID, Name) values('22', 'Robert');
Query OK, 1 row affected (0.00 sec)
MariaDB [company]> insert into employees(ID, Name) values('23', 'Louis');
Query OK, 1 row affected (0.00 sec)
exit
Install mysql-client on test server:
yum install mysql -y
mysql -u project -p -h DB1_private_IP -e 'select * from company.employees;'
Enter password:
+------+---------+
| ID | Name |
+------+---------+
| 11 | Robin |
| 15 | Mike |
| 20 | Jessica |
| 21 | Carl |
| 22 | Robert |
| 23 | Louis |
+------+---------+
mysql -u project -p -h DB2_private_IP -e 'select * from company.employees;'
Enter password:
+------+---------+
| ID | Name |
+------+---------+
| 11 | Robin |
| 15 | Mike |
| 20 | Jessica |
| 21 | Carl |
| 22 | Robert |
| 23 | Louis |
+------+---------+
Succesfulyy created Master-Master Replication mariadb servers.
