Master-Master configuration allows two nodes to exchange information. Thus, we are able to server local data fast and at the same time provide fail-over for other data nodes. For example, let's consider the following scenario. We have a 2 database nodes: 1 in Paris and 1 in Berlin, who are in Master - Master replication configuration. The Paris data nodes, servers the customers in Paris and the database in Berlin, servers the Berlin's clients. In case the database in Paris crashes, then we can simply redirect the application to point to the Berlin data node, since all the data has been already replicated.

Let's start with the configuration of node A, as with Master/Slave replication, we have to:

1. Create user.
2. Edit configuration.
3. Flush logs.
4. Create a backup.

So let's start going, shall we :)

The creation of a user, as the master/slave replication is easy:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'oracle_4U';
Query OK, 0 rows affected, 1 warning (0.03 sec)

This user, will be used by the other master, to pull binlogs.

You should choose the master master database if I can say so :) Since the databases should have different server_id (in order to differentiate log files), let's leave the first node with server-id=1. Other settings are to: allow binlog for the specific object: system, database or table. Let's go with the following settings:

[root@mysqlmaster ~]# cat /etc/my.cnf
[mysqld]
datadir=/mysqldata
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=1   <- Our server id, SHOULD BE DIFFERENT FOR EACH SERVER, used for differentiating binlog's origin.
log-bin=/binlog/mysql-bin   <- Our binlog location
binlog-do-db=example  <- The database for which we are producing binlogs. 

Flushing the logs is important prior a backup, so we can take a clear log position:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 |      509 | example      |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |      154 | example      |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

From this, our log file is: mysql-bin.000012 with log position: 154. Let's keep going

Do whatever CONSISTENT backup of the object: system, database or table you want to replicate, I will replicate a single database (called example, already existing) and since I have my log position, I will do a logical backup with a read lock. You are free to do it without locking in case you have only MyISAM tables or use Percona/Enterprise Backup for all your InnoDB tables.

mysql> flush tables with read lock;
mysql> Bye!
[root@mysqlmaster backup]# mysqldump -p --opt example> example.sql
[root@mysqlmaster backup]#
mysql> unlock tables;

This will generate me SQL commends in order to re-create my database. This conclude our configuration on the primary. P.S. Don't forget to unlock the tables after the backup (or include the locking in the mysqldump)

The configuration of Node B will be similar as Node A with couple differences.

1. Edit Configuration
2. Create user
3. Copy & Restore Backup
4. Start Slave
5. Flush Logs & Take Log Position
6. Start slave (On Node A)

So, let's get done with it :)

First, since we already use server-id=1, we have to use different server-id for Node B. For simplicity, let's use server-id=2. Thus, our configuration will look as so:

[root@mysqlslave ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
datadir=/mysqldata
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=2  <- Server ID (Different than the other master data node)
binlog_do_db = example   <- The object we want to replicate (System, database or Table)
log-bin=mysqlslave-bin  <- Enabling bin logs

Same as the other node:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'oracle_4U';
Query OK, 0 rows affected, 1 warning (0.03 sec)

Simply copy the file and execute it on the Node B:

[root@mysqlslave mysqldata]# scp [email protected]:/mysqldata/example.sql .
[email protected]'s password:
example.sql                                                                                                                                                                         100%    0     0.0KB/s   00:00
[root@mysqlslave mysqldata]#

You are free to use, whatever configurations you want: Percona, Enterprise Manager, LVM snapshots (like we did in System replication). Choice is yours :)

Simple execute of the query will suffice:

[root@mysqlslave backup]# mysql -uroot -p -D example < example.sql

This will execute script: example.sql on that database.

In order to start the slave and change the master, we will have to use the details from configuration of Node A, I mean: replication user, replication password, log file and log position as follows:

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.0.129', MASTER_USER = 'slave_user', MASTER_PASSWORD = 'oracle_4U', MASTER_LOG_FILE = 'mysql-bin.000012', MASTER_LOG_POS = 154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

No we simply configured Master/Slave replication, let's configure the other Node, Node A now to also pull data:

On Node B, let's flush the logs and take a log position, which we will use to configure Node A:

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysqlslave-bin.000016 |      882 | example      |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)
mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysqlslave-bin.000017 |      154 | example      |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

Now, On Node A, we will change the Master to point to Node B as follows:

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.0.130', MASTER_USER = 'slave_user', MASTER_PASSWORD = 'oracle_4U', MASTER_LOG_FILE = 'mysqlslave-bin.000017', MASTER_LOG_POS = 154 ;
Query OK, 0 rows affected, 2 warnings (0.24 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

Congrats you have a brand new Master-Master replication for Database: Example. Now every change you make on Node A, will be replicated to Node B and visa - versa.

IMPORTANT THINGY HERE, if for a reason, someone tries to delete a data which is already deleted OR a bin logs fails to apply for WHATEVER reason, the node will HANG. The solution for this initially is to SKIP binlog as follows:

mysql> SHOW master STATUS
 → ;
+———————–+———-+————–+——————+——————-+
| File                  | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+———————–+———-+————–+——————+——————-+
| mysqlslave-bin.000016 |      611 | example      |                  |                   |
+———————–+———-+————–+——————+——————-+
1 ROW IN SET (0.00 sec)
 
mysql> SET global sql_slave_skip_counter =1;   ← This will SKIP the CURRENT binlog AND skip it WITH 1, so IF we have couple binlogs who fails TO apply, keep skipping BY 1, until there aren't errors in the mysqld.log file anymore. THEN return and examine what was skipped and decide if you want to apply part of it or no.