Master-Master Replication

Master 1 IP Address : 192.168.0.2
Master 2 IP Address: 192.168.0.3
Database to replicate: db1, db2, and db3


Master 1
On 192.168.0.2

mysql> CREATE USER 'repl'@'192.168.0.3' IDENTIFIED BY 'gmi123W';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.3' IDENTIFIED BY 'gmi123W';
mysql> FLUSH PRIVILEGES;

mysql> CREATE USER 'repl'@'192.168.0.%' IDENTIFIED BY 'gmi123W';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'gmi123W';
mysql> FLUSH PRIVILEGES;


skip-locking
key_buffer = 100M
max_allowed_packet = 64M
log-error=/var/log/mysql/mysql-error.log
long_query_time = 3
log-slow-queries=/var/log/mysql/mysql-slow.log
table_cache = 512
sort_buffer_size = 10240K
read_buffer_size = 10240K
read_rnd_buffer_size = 10240K
net_buffer_length = 10240k
myisam_sort_buffer_size = 100M
max_connections=2048
set-variable = max_user_connections=2048
set-variable = max_connect_errors=999999
set-variable = query_cache_size=512M
query_cache_limit = 512M
set-variable = join_buffer=512M
thread_cache_size = 1024

server-id = 3

master-host = 192.168.0.3
master-user = repl
master-password = gmi123W
master-connect-retry = 30
slave-net-timeout = 30
replicate-do-db = arvind


log-bin = /var/log/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/master-log-bin.index
binlog-do-db = arvind


relay-log = /var/lib/mysqld-relay-bin
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

mysql>USE arvind;
mysql>UNLOCK TABLES;
mysql>FLUSH TABLES WITH READ LOCK;


mysql> CHANGE MASTER TO MASTER_HOST = '192.168.0.3', MASTER_USER = 'repl', MASTER_PASSWORD = 'gmi123W', MASTER_LOG_FILE = 'mysql-bin.000001',

MASTER_LOG_POS=789;

FLUSH PRIVILEGES;

Master2

On 192.168.0.3

mysql> CREATE USER 'repl'@'192.168.0.2' IDENTIFIED BY 'gmi123W';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.2' IDENTIFIED BY 'gmi123W';
mysql> FLUSH PRIVILEGES;

mysql> CREATE USER 'repl'@'192.168.0.%' IDENTIFIED BY 'gmi123W';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'gmi123W';
mysql> FLUSH PRIVILEGES;

skip-locking
key_buffer = 100M
max_allowed_packet = 64M
log-error=/var/log/mysql/mysql-error.log
long_query_time = 3
log-slow-queries=/var/log/mysql/mysql-slow.log
table_cache = 512
sort_buffer_size = 10240K
read_buffer_size = 10240K
read_rnd_buffer_size = 10240K
net_buffer_length = 10240k
myisam_sort_buffer_size = 100M
max_connections=2048
set-variable = max_user_connections=2048
set-variable = max_connect_errors=999999
set-variable = query_cache_size=512M
query_cache_limit = 512M
set-variable = join_buffer=512M
thread_cache_size = 1024

server-id = 4

master-host = 192.168.0.2
master-user = repl
master-password = gmi123W
master-connect-retry = 30
slave-net-timeout = 30
replicate-do-db = arvind

log-bin = /var/log/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/master-log-bin.index
binlog-do-db = arvind

relay-log = /var/lib/mysqld-relay-bin
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index


mysql>USE arvind;
mysql>UNLOCK TABLES;
mysql>FLUSH TABLES WITH READ LOCK;

CHANGE MASTER TO MASTER_HOST = '192.168.0.2', MASTER_USER = 'repl', MASTER_PASSWORD = 'gmi123W', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS=193;

FLUSH PRIVILEGES;




To Check run below query

create database arvind;


use arvind;

INSERT INTO `citnet_patent` ( `patent_id` , `patent_no` , `patent_f_cit_depth1` , `patent_b_cit_depth1` , `patent_rel_depth1` , `patent_matrix_depth1` ,

`patent_f_cit_depth2` , `patent_b_cit_depth2` , `patent_rel_depth2` , `patent_matrix_depth2` , `patent_first_srch_date` , `patent_update_date` ,

`patent_type` )
VALUES (
'5001', 'ep717426', '', '', '', NULL , NULL , NULL , NULL , NULL , NULL , NULL , '0'
);



select * from citnet_patent;

UPDATE `citnet_patent` SET `patent_no` = 'AS717426',
`patent_matrix_depth1` = NULL ,
`patent_f_cit_depth2` = NULL ,
`patent_b_cit_depth2` = NULL ,
`patent_rel_depth2` = NULL ,
`patent_matrix_depth2` = NULL ,
`patent_first_srch_date` = NULL ,
`patent_update_date` = NULL WHERE `patent_id` =5001 LIMIT 1 ;

select * from citnet_patent;