Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

MySQL Server 1 is running as Master.
MySQL Server 2 is running as Slave.

With both DBs online, they are in "perfect sync". If Slave goes offline, there's no problem if Master still online; they will go back sync once the Slave is online again.

Besides the server configuration, I redirected the connection (with JSP code) for the Slave DB if the Master goes offline (I tested of course with /etc/init.d/mysqld stop).

When the Master goes back online, is there any automatic method to sync the Master with Slave updates?

share|improve this question

migrated from stackoverflow.com May 15 '11 at 20:35

This question came from our site for professional and enthusiast programmers.

3 Answers 3

One good way to pull off something of that nature is to set up Master-Master Replication or Circular Replication. This is not to be confused with MultiMaster Replciation.

Setting up Circular Replication is actaully very easy if you have setup Master-Slave Replication. Here is what you need to do in order to configure it.

For this example, we will assume Master-Slave Replication is active but you will experience a bit of downtime (1-2 minutes):

Step 1) Add this line to /etc/my.cnf on the Master.

log-slave-updates

Step 2) Add these line to /etc/my.cnf on the Slave:

log-bin=mysql-bin (or have whatever the master has for this) log-slave-updates

WARNING : Here is the brief moment of downtime !!!

Step 3) On the Slave, service mysql restart

This will activate binary logs on the Slave

Step 4) On the Master, service mysql stop

Step 5) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master.

WARNING : Here is the longer moment of downtime !!!

Step 6) On the Slave, service mysql stop

Step 7) On the Slave, find out the last binary log

Step 8) On the Slave, find out the filesize of the last binary log

Step 9) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master. This should be a faster copy.

Step 10) On the Master, edit
Line 2 of master.info with the last binary log of the Slave.
Line 3 of master.info with the filesize of the last binary log of the Slave.
Line 4 of master.info with the IP of the Slave.
Line 5 is the userid of replication user (DO NOT TOUCH)
Line 6 is the password of replication user (DO NOT TOUCH)

Step 11) Delete all binary logs and binary log index file of the Master.

Step 12) On the Slave, service mysql start, wait 15 seconds

Step 13) On the Master, service mysql start

Step 14) On the Master, run STOP SLAVE; SHOW MASTER STATUS;

Step 15) On the Slave, run CHANGE MASTER TO MASTER_HOST='IP of Slave',MASTER_USER='userid of replication user from Step10',MASTER_PASSWORD='password of replication user from Step10',MASTER_LOG_FILE='binary log from Step14',MASTER_LOG_POS=LogPos from Step14.

Step 16) On the Slave, run START SLAVE;

Step 17) On the Master, run START SLAVE;

I performed steps similar to this for another StackExchange question I answered.

Give it a Try !!!

share|improve this answer
    
Very nice! Is it possible to have more than one slave database syncing into master, more like an "Master-Master-Master" solution? –  Herberth Amaral Sep 18 '12 at 11:30
    
This broke my setup beyond repair. Had to completely reinstall my VPS. I guess I'll take a snapshot before reading dodgy advice next time. –  Vasili Syrakis May 21 at 3:13
    
@VasiliSyrakis I am sorry you feel my advice was dodgy. Notwithstanding, in my 10 years as a MySQL DBA, I have never once destroyed a VPS or a MySQL install when realigning binary logs for replication. I have been doing this for Drupal and Wordpress clients for many years without incident. Sorry for my advice. –  RolandoMySQLDBA May 21 at 4:14
    
Hmm. I wouldn't recommend to use rsync to copy a running instance. I'd use Percona XtraBackup to reinitialize the master from the slave. Also you don't need log-slave-updates unless the masters are going to have additional slaves. –  Bill Karwin May 21 at 7:05

Not with Asynchronous replication which is what MySQL offers. You just hit the proverbial nail of why the 'out of the box' MySQL replication (pre 5.5) is not a high availability solution in and of itself. Things get slightly better with 5.5 with semi-synchronous replication (http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html) but at the cost of slower transaction time as the master waits for the ack from slave.

If accepting the possibility of data loss when master goes down is not an option, I'd say a more sophisticated setup than simple master/slave is in order.

Master to Master replication has been deemed more trouble than benefit by many MySQL famed people (even MySQL AB itself no longer recommends it as a high availability solution). So, I think using DRBD setup to keep the active master and the passive slave in sync using block level copies is what you actually need here.

share|improve this answer
1  
I love DRBD myself. I work with it regularly with many clients using ucarp as my failover mechanism. DRBD is actually very good and preferrable for HA, provided the database is all InnoDB. Any MyISAM table open during a failover is marked crashed even in the DRBD Secondary. InnoDB just goes through crash recovery when failing over to the new DRBD Primary. So, I see DRBD and InnoDB being used together. Thank you for your good sense to bring up DRBD. +1 for your answer. –  RolandoMySQLDBA May 17 '11 at 15:08
    
Thank you :) and I guess in my answer I am making the presumption that if you care so much about data consistency between your replicas, you're already all or mostly InnoDB :) –  TechieGurl May 17 '11 at 16:01

IMHO, First of all, in a non-multi-master (master/slave) configuration, your slave should never take writes. The slave my.cnf should be configured and server started with:

# Flag to not take writes from network
read-only

Next, to cure the problem of a master out of sync with a writable slave accidentally taking writes, you have to diff the data on both hosts. If there's no key collision, you should promote your former slave host to master and re-image your old master as a slave host replicating from the new master. (there could be/probably is data problems here)

Finally, if this scenario of outage/downtime is even a possibility going forward, take the time to set both hosts up as multi-master (log-bin, server-id, offsets, etc.). This will help you mitigate outages and downtimes to some degree.

If you have to run master/slave, at least get some bonus points for separating read and write user connections in your ACL and applications.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.