Here we will be implementing Master Master Replication in MySQL 5.7

For more interesting posts on MySQL, please click on INDEX

Below are the server details which will be used :

Server 1 :
Hostname : Master1
IP Address : 172.168.190.1

Server 2 :
Hostname : Master2
IP Address : 172.168.190.2

Steps :

1. Take dump on master1 and restore on master2

cd /mysqldatabase/product/binary/5.7.33
bin/mysqldump --defaults-file=/mysqldatabase/TEST/admin/my.cnf -uroot -pmysql --master-data=2 --quick --single-transaction -vvv --force -A > /mysqldatabase/dump/TEST_all.sql

 

2. Restore in Master2 server 

nohup /mysqldatabase/product/binary/5.7.33/bin/mysql -uroot -pmysql -S/mysqldatabase/TEST/admin/mysql.sock < /mysqldatabase/dump/TEST_all.sql &

 

3. Make changes in the MASTER2 CNF File

[client]
port = 3306
socket = /mysqldatabase/TEST/admin/mysql.sock

[mysqld_safe]
log-error=/mysqldatabase/TEST/logs/mysql.log

[mysqld]
port = 3306
socket = /mysqldatabase/TEST/admin/mysql.sock
max_allowed_packet = 32M
query_cache_size = 32M
table_open_cache = 512K
sort_buffer_size = 4M
read_buffer_size = 2M
slow_query_log
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M
thread_cache_size = 25
sql_mode=ANSI,TRADITIONAL,NO_BACKSLASH_ESCAPES
log_output=TABLE
key_buffer_size = 32M
max_connections=100
tmp_table_size=32M
character_set_server=utf8
tmpdir=/mysqldatabase/TEST/tmp
max_heap_table_size=32M
join_buffer_size=4M
basedir=/mysqldatabase/product/binary/5.7.35
datadir=/mysqldatabase/TEST/data
default_storage_engine=InnoDB
innodb_file_per_table
log-bin=/mysqldatabase/TEST/binlogs/TEST-mysql-bin
log-bin-trust-function-creators=1
sync_binlog=1
binlog_format=mixed
net_read_timeout = 60
innodb_data_home_dir = /mysqldatabase/TEST/data
innodb_data_file_path = ibdata1:200M;ibdata2:200M:autoextend
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 1024M
innodb_log_buffer_size= 512M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 500
server-id = 2
relay-log = /mysqldatabase/TEST/logs/target-relay-bin.index
user=mysql
symbolic-links=0
#read_only = 1
#super_read_only=on
#tmpdir = /tmp
#binlog_format = ROW
#max_binlog_size = 500
expire-logs-days = 7
##Master-Master-configuration
log_slave_updates=1

 

4. Make changes in the MASTER2 CNF File

[client]
port = 3306
socket = /mysqldatabase/TEST/admin/mysql.sock

[mysqld_safe]
log-error=/mysqldatabase/TEST/logs/mysql.log

[mysqld]
port = 3306
socket = /mysqldatabase/TEST/admin/mysql.sock
max_allowed_packet = 32M
query_cache_size = 32M
table_open_cache = 512K
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M
thread_cache_size = 25
sql_mode=ANSI,TRADITIONAL,NO_BACKSLASH_ESCAPES
log_output=TABLE
key_buffer_size = 32M
max_connections=100
tmp_table_size=32M
character_set_server=utf8
tmpdir=/mysqldatabase/TEST/tmp
max_heap_table_size=32M
join_buffer_size=4M
basedir=/mysqldatabase/product/binary/5.7.33
datadir=/mysqldatabase/TEST/data
default_storage_engine=InnoDB
innodb_file_per_table
log-bin=/mysqldatabase/TEST/binlogs/TEST-mysql-bin
log-bin-trust-function-creators=1
sync_binlog=1
binlog_format=mixed
server-id = 1
net_read_timeout = 60
innodb_data_home_dir = /mysqldatabase/TEST/data
innodb_data_file_path = ibdata1:200M;ibdata2:200M:autoextend
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 1024M
innodb_log_buffer_size= 512M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 500
expire-logs-days = 7
relay-log = /mysqldatabase/TEST/logs/target-relay-bin.index
slow_query_log
log_slave_updates=1

 

5. Stop , Start both Master1 and Master2 instances

 

6. Create replication user in Master1 instance


CREATE USER 'repl'@'172.168.190.2' IDENTIFIED BY 'repl';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.168.190.2' IDENTIFIED BY 'repl';

FLUSH PRIVILEGES;

 

7. Create replication user in Master2 instance


CREATE USER 'repl'@'172.168.190.1' IDENTIFIED BY 'repl';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.168.190.1' IDENTIFIED BY 'repl';

FLUSH PRIVILEGES;

 

8. Run the below command in Master2


CHANGE MASTER TO MASTER_LOG_FILE='TEST-mysql-bin.000004', MASTER_LOG_POS=154,master_host='172.168.190.1', master_user='repl',master_password='repl',master_port=3306, MASTER_CONNECT_RETRY=10;

 

9. Start Slave in Master2 

Start Slave;
Show Slave status\G

 

10. Take the Master status from Master2 

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

 

11. Run the below command in Master1 

CHANGE MASTER TO MASTER_LOG_FILE='TEST-mysql-bin.000005', MASTER_LOG_POS=1705,master_host='172.168.190.2', master_user='repl',master_password='repl',master_port=3306, MASTER_CONNECT_RETRY=10;

 

12. Start Slave in Master1 

Start Slave;
Show Slave status\G