Master-To-Master MySQL Replication Via SSH
This is officially called "Circular replication with two nodes", where each one is at the same time master and slave of the other.
Similar to MSSQL Merge Replication, aka two-way replication (aka Multi-Master Replication), where simultaneous changes to the database can be made by two separated MySQL servers. Changes can be made on either servers without an active network connection. Upon establishing active connection, the synchronisation process begin to send data between them till both have identical data in them.
Only beginning with MySQL 5.1.18 (June 2007), it is possible to perform "stable" multi-master replication in MySQL.
Prerequisite: MyEnTunnel from nemesis2.qx.net, PLINK.exe from www.chiark.greenend.org.uk
MyEnTunnel Settings Tab: Enter Your SSH Server Name, SSH Port, Login name, Password. Tick all the check boxes
MyEnTunnel Tunnels Tab:
On server-id=2
Modify server-id=2 my.ini
log-bin=id2
expire_logs_days=30
max_binlog_size=100M
# input the database which should be replicated
binlog-do-db=ksdb
# database to be ignore
binlog-ignore-db=mysql
binlog-ignore-db=test
# Creating some room between pk ids, we can always manually insert if need be.
auto_increment_increment = 10
auto_increment_offset = 2
# This is default 0 or false, but let’s be safe
replicate-same-server-id = FALSE
# allow slave to writes going to both masters?
# very important if you want to chain master replication servers
log-slave-updates = TRUE
# If there’s a reboot, please auto start slave (replication)
skip-slave-start = FALSE
Restart server-id=2
run mysql
grant replication slave on *.* to ’replication’@’127.0.0.1’ identified by ’slavery’;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
# Please Note Following...
# File: HPMINI-bin.000002
# Position: 245
UNLOCK TABLES;
On server-id=1
# Creating some room between pk ids, we can always manually insert if need be.
auto_increment_increment = 10
auto_increment_offset = 1
# This is default 0 or false, but let’s be safe
replicate-same-server-id = FALSE
# allow slave to writes going to both masters?
# very important if you want to chain master replication servers
log-slave-updates = TRUE
# If there’s a reboot, please auto start slave (replication)
skip-slave-start = FALSE
run mysql
CHANGE MASTER TO MASTER_LOG_FILE=’HPMINI-bin.000002’, MASTER_LOG_POS=245, MASTER_HOST=’127.0.0.1’, MASTER_PORT=3307, MASTER_USER=’replication’, MASTER_PASSWORD=’slavery’;
START SLAVE;
SHOW SLAVE STATUS;
multi-master replication Notes:
MySQL 5.0.2 (2004-12-01) Introduce auto_increment_increment to manage Primary Key conflict. Each of these variables has a default and minimum value of 1, and a maximum value of 65,535.
MySQL 5.0.3 (2005-03-23) Binary log format changes for handling of character sets
MySQL 5.0.4 ((2005-04-16) Binary log format changes for handling of time zones
MySQL 5.1.5 (2006-01-10) Row-based replication was implemented
SQL incompatibilities. You cannot replicate from a newer master to an older slave using statement-based replication if the statements to be replicated use SQL features available on the master but not on the slave.
So to be safe, it is better to have MySQL 5.1.5 and above to implement MySQL Replication.
Updated On: 12.10.06