Setting up MySQL Master – Master Replication
Table of Contents
Introduction
One major reason I really appreciate MySQL is it’s replication simplicity, I agree it’s not perfect but quite straightforward. There is only one way to do it so very minimal complexities . All our customers are either an internet / mobile property or Internet of Things services provider so scaling their MySQL infrastructure for performance, availability and reliability is very important. MySQL replication is an quick and easy solution for performance, scalability and availability. I have written blogs on MySQL master – slave replication : https://minervadb.com/index.php/2018/01/24/step-by-step-mysql-master-slave-replication-on-centos/ (CentOS) and https://minervadb.com/index.php/2018/01/24/mysql-master-slave-replication-on-ubuntu-xenial-16-04/ (Ubuntu) . MySQL Master-Slave replication really works great if you are just looking for scaling reads or splitting read-write but what about availability in a simple Master-Slave replication ? You have to manually graduate / promote slave to master and there is downtime expected here so if you are looking for maximum availability of MySQL infrastructure operations MySQL Master-Master replication is recommended. I am not going to explain here about MySQL installation, I will be starting with you my.cnf configuration in both master instances, Including the next steps for an successful Master-Master MySQL replication
Master 1 – 192.168.56.13
Master 2 – 192.168.56.23
Configure my.cnf of Master 1 – 192.168.56.13 and Master 2 – 192.168.56.23
my.cnf of MySQL instance 192.168.56.13
root@DA:/home/shiv/sakila# vi /etc/mysql/my.cnf
[mysqld] server-id = 900 log-bin = mysql-bin bind-address = 192.168.56.13 binlog-do-db = sakila
my.cnf of MySQL instance 192.168.56.23
root@DBA:/home/shiv/sakila# vi /etc/mysql/my.cnf
[mysqld] server-id=901 log-bin = mysql-bin bind-address = 192.168.56.23 binlog-do-db = sakila
Restart MySQL on both instances (192.168.56.13 and 192.168.56.23)
We need to create a pseudo-user that will be used for replicating data between our two VPS (192.168.56.13 and 192.168.56.23). The examples in this article will assume that you name this user “repl” and password used here is “repl” .
User created in 192.168.56.13
mysql> create user 'repl'@'192.168.56.23' identified by 'repl' ; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave on *.* to 'repl'@'192.168.56.23'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
User created in 192.168.56.23
mysql> create user 'repl'@'192.168.56.13' identified by 'repl'; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave on *.* to 'repl'@'192.168.56.13'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
The last thing we have to do before we complete the mysql master-master replication is to make note of the master log file and position. We need this data to hook both MySQL instances on each other, We are doing an Master-Master replication so we need details of both the MySQL instances, 192.168.56.13 and 192.168.56.23
Master log file and position of 192.168.56.13
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | sakila | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
Master log file and position of 192.168.56.23
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 771 | sakila | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
Completing MySQL Master-Master replication
The following steps will start replicating data between both MySQL instances (192.168.56.13 and 192.168.56.23)
Run this command on MySQL instance in 192.168.56.13 (confirm replication is successful and no error is reported with command “show slave status\G” command)
mysql> stop slave; mysql> change master to -> MASTER_HOST='192.168.56.23', -> MASTER_USER='repl', -> MASTER_PASSWORD='repl', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS= 771; Query OK, 0 rows affected, 2 warnings (0.04 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.23 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 771 Relay_Log_File: DA-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 771 Relay_Log_Space: 524 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 901 Master_UUID: cd1eedbe-fed3-11e7-a414-08002720a95a Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
Run this command on MySQL instance in 192.168.56.23 (confirm replication is successful and no error is reported with command “show slave status\G” command)
mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> change master to -> MASTER_HOST='192.168.56.13', -> MASTER_USER='repl', -> MASTER_PASSWORD='repl', -> MASTER_LOG_FILE='mysql-bin.000002', -> MASTER_LOG_POS= 154; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.13 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: DBA-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 525 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 900 Master_UUID: a1546678-febf-11e7-9618-080027275ccc Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
Test MySQL Master-Master replication
create this table in 192.168.56.13 MySQL instance
mysql> create table tab1 ( -> col1 integer); Query OK, 0 rows affected (0.02 sec)
confirm this table replicated to the MySQL instance in 192.168.56.23 successfully
mysql> show tables like 'tab1%'; +--------------------------+ | Tables_in_sakila (tab1%) | +--------------------------+ | tab1 | +--------------------------+ 1 row in set (0.00 sec)
create this table in 192.168.56.23 MySQL instance
mysql> create table tab11 -> ( col11 integer); Query OK, 0 rows affected (0.02 sec)
confirm this table replicated to the MySQL instance in 192.168.56.13 successfully
mysql> show tables like 'tab11%'; +---------------------------+ | Tables_in_sakila (tab11%) | +---------------------------+ | tab11 | +---------------------------+ 1 row in set (0.00 sec)
Conclusion
The successful completion of above tests confirms you have MySQL Master-Master replication in place !
