MySQL bi-directional table replication

MySQL bi-directional table replication

On first server with ip address 10.10.10.1

/etc/my.cnf
server-id=1
master-host=10.10.10.2
master-user=slave_user
master-password=password
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index=/var/lib/mysql/mysql-bin.index
log-slave-updates
replicate-do-db=db_name
binlog-do-db=db_name
relay-log = /var/lib/mysql/relay-bin
relay-log-index = /var/lib/mysql/relay-bin.index
relay-log-info-file = /var/lib/mysql/relay-bin.info

/etc/init.d/mysqld restart

mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
mysql>FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G


On first server with ip address 10.10.10.2

/etc/my.cnf
server-id=2
master-host=10.10.10.1
master-user=slave_user
master-password=password
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index=/var/lib/mysql/mysql-bin.index
log-slave-updates
replicate-do-db=db_name
binlog-do-db=db_name
relay-log = /var/lib/mysql/relay-bin
relay-log-index = /var/lib/mysql/relay-bin.index
relay-log-info-file = /var/lib/mysql/relay-bin.info

/etc/init.d/mysqld restart

mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
mysql>FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G


Default path for bin logs /var/run/mysql is cleaned every restart.

For an emergency
reset slave;
share on: facebook