Following the steps to replicate an existing server, it seems most methods rely on the ability to either completely stop the master server or at least prevent it from writing using flush tables with read lock; which effectively creates down time as many apps cannot respond properly when unable to write to the database.
Is there a safe way to replicate a master to a slave, ensuring zero down time and complete sync of the database information?
In our scenario we have one master and once slave and they both worked well. A MySQL bug during upgrade from 5.5 to 5.6 got the slave to be slightly out of sync and now we want to re-create its databases completely. For now when we get some sporadic errors we skip them using STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; but this only means the replication isn't 100% like the master.
Thanks
Best Answer
Though it's an old question, but if you still looking for answer - try percona xtrabackup. If you have MyISAM tables, use --rsync option. With it we achieved almost seamless dump (lock took around 5-10 seconds for DB around 1Gb).