Lets set up a simple Master-Master database replication on MariaDB. I have already posted an article on the setting up of Master-Slave database replication. You can test it on Virtual Box or Vmware labs for your own analysis and benchmark. Lets called both MariaDB Master servers as Master1 and Master2.
1. Create the 2 Labs (test it on Centos7) and edit your /etc/hosts so that each server can ping each other via the hostname. On Master1, edit your /etc/my.cnf file and on the top enter the following parameter:
[mysqld]
server-id=1
log-bin=mysql-bin
2. On master1 SQL prompt, create a user for replication slave followed by flush privileges.
MariaDB [(none)]> grant replication slave on *.* to repli@'%' identified by 'replipassword';
MariaDB [(none)]> flush privileges;
3. Flush the tables for read-only access.
MariaDB [(none)]> flush tables with read lock;
4. At this point, if you do a show master status, you need to have a file with the mysql-bin incrementing number as well a position number.
MariaDB [(none)]> show master status\G
File: mysql-bin.000001
Position: 612
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
5. Now, move on to the Master2 server, I assume you have already start MariaDB, setting up mysql_secure_installation, and edit the /etc/hosts file etc..
6. On Master2 edit your /etc/my.cnf file and on top enter the following parameter, after which you can restart the MariaDB service.
[mysqld]
server-id=2
7. Now on the Master1 server, do a dump of the database MySQL and import it to Master2, so that we make sure it starts and synchronize with the same conf. So, I did a dump of the MySQL database on master1 and SCP it to master2
[root@master1 yum.repos.d]# mysqldump -u root -p --database mysql > mysql.mysql
[root@master1 home]# scp mysql.mysql root@master2:/home
8. On Master2 import the MySQL database
[root@master2 home]# mysql mysql -u root -p < mysql.mysql
9. Now, we will temporarily run Master2 as the slave.
MariaDB [(none)]> change master to master_host='master1', master_user='repli', master_password='replipassword', master_log_file='mysql-bin.000001', master_log_pos=612;
MariaDB [(none)]> start slave;
10. At this point, you would notice that the Slave is running correctly by verifying with a show slave status\G. Do check the Slave_IO_Running, Master_User, Log_Pos and the Slave_SQL_Running parameters.
11. Now that Master1 is the master and Master2 is the slave, we will perform the reverse order to reach our goal. At this level, you can unlock the tables which we did at step3. On master1 fire this command.
unlock table
12. On Master2 (which is actually a slave), re-edit my.cnf file and under the [mysqld] and server-id add the following parameter. It should look like this
[mysqld]
server-id=2
log-bin=mysql-bin
13. On Master2 restart MariaDB with the command systemctl restart mariadb and logging to Mysql and do a show master status. The result should be similar to this.
MariaDB [(none)]> show master status\G
File: mysql-bin.000001
Position: 313
Binlog_Do_DB:
Binlog_Ignore_DB:
14. On Master1, a show slave status will prompt you an empty set. Now since we have dumped the MySQL database to Master2, we can now just run Master1 as the slave, i.e to repeat the process as in step 9. Launch this command on Master1 to create it as a slave. The File and Position should be taken from the Master2.
MariaDB [(none)]> change master to master_host='master2', master_user='repli', master_password='replipassword', master_log_file='mysql-bin.000001', master_log_pos=313;
MariaDB [(none)]> start slave;
15. Test by creating database and tables on both servers, it will replicate on each other.
Tips:
-
- Before carrying on with step1 do read the article Master-Slave database replication, as I have covered quite many issues there.
-
- If you have installed a fresh MariaDB, you need to launch the script mysql_secure_installation just in the master-slave database replication article.
-
- Each time you edit my.cnf file, you need to restart MariaDB so that the conf take effect.
-
- After having created the Master-Slave replication at step 10, you can check on both Master1/2 with a netstat -ntpla | egrep -i established.*mysql. You would notice the established connection and the port it’s listening.
-
- The command host with the IP established will also confirm its from the specific host.
-
- Further testing on Master1 can also be done with command as mysql -u root -p -e “show processlist” which will give you an indication of the state of the server.
- The value of File and Position of Master1 when using the command show master status should correspond with the values of Master_Log_File and Read_Master_Log_Pos of Master2 with the command show slave status and vice versa.
