MariaDB(MySQL) Master-Master Replication

MariaDB_Master-Master_Replication

I’m going to develop my cluster further and create MariaDB(MySQL) geo-cluster by enabling MariaDB(MySQL) Master-Master Replication. I’ve already deployed MariaDB cluster in the second datacenter (nodes 4, 5 and 6) and now I need to configure Master-Master replication between two MariaDB clusters. The replication will take place between node 1 (first datacenter, we will name it Master1) and node 4 (second datacenter, we will name it Master2).

First of all we need to check a couple of variables that may impact or break replication. 

  • skip-networking. If skip-networking=1, the server will limit connections to localhost only, and prevent all remote slaves from connecting.
  • bind_address. Similarly, if the address the server listens for TCP/IP connections is 127.0.0.1 (localhost), remote slaves connections will fail.

Stop MariaDB on both nodes:

Master1

Edit /etc/my.cnf.d/server.cnf file. I’ve added under the [mysqld] section:

Now start MariaDB server.

And create user that will be used for replicating data between our servers. Grant appropriate privileges to the user.

Now you need to prevent any changes to the data while you view the binary log position. You’ll use this to tell the slave at exactly which point it should start replicating from.

  • Flush and lock all tables by running FLUSH TABLES WITH READ LOCK. Keep this session running – exiting it will release the lock.
  • Get the current position in the binary log by running SHOW MASTER STATUS:

  • Record the File and Position details. If binary logging has just been enabled, these will be blank.
  • Now, with the lock still in place, copy the data from the Master1 to the Master2. Open second terminal window and use mysqldump:

  • Once the data has been copied, you can release the lock on the Master1 by running UNLOCK TABLES.

Master2

Edit /etc/my.cnf.d/server.cnf file. I’ve added under the [mysqld] section:

Now start MariaDB server.

Restore DB from dump we sent from Master1:

And create user that will be used for replicating data between our servers. Grant appropriate privileges to the user.

To start replication enter following commands.

For MASTER_LOG_FILE and MASTER_LOG_POS I have used information from “show master status” on the Master1.
Check status information of the slave threads.

Note that Read_Master_Log_Pos and Exec_Master_Log_Pos are in sync which is 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.

Master1

Start replicating data from Master2 to Master1 node.

Everything seems to be OK.

You can now create a test database (testdb) and check that replication is working!

Want me to do this for you? Drop me a line: itgalaxyzzz {at} gmail [point] com