I have a Master Master Replication setup using mysql Percona Server 5.7, but for some unknown reasons, seems like my passive server is broken "thats not the issue", the replication is bad and the data been bad in the passive for a while.

I have decided to create a new Passive server from the current active. What is the best way to create a new passive server "from the active one" without or with minimum downtime? given that:

  • bin logs set on Active and current passive
  • Relay log is set
  • log expire days = 3
  • sync-binlog = 1
  • binlog-format = ROW
  • log-slave-updates = ON

please help

  • Windows, Linux or something else? How large is your database? How much memory you have available on your active instance? – jynus Oct 11 '18 at 19:49
  • linux, the size is about 300GB, i have enough memory, – Eddy Oct 11 '18 at 20:02
  • It's a pity you did not already have a Slave. You could reconstruct the bad Master from it with zero down time. – Rick James Oct 18 '18 at 21:06

Based on your comments- using Linux with a non-trivially small sized database, I will recommend you to use Percona XtraBackup ( https://www.percona.com/software/mysql-database/percona-xtrabackup ) to generate a copy of your current active database in a fast way and in an almost 100% hot way (no read or write blocking except to gather the binlog position, which normally should only take less than 2 seconds).

You can follow the steps at: https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/setting_up_replication.html (How to setup a slave for replication in 6 simple steps with Percona XtraBackup).

I asked those questions because the alternative is mysqldump/mydumper, but that will take more to recover for your data size (also XtraBackup didn't use to support non-linux). Here is how to do it ina hot way just in case: https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

A common mistake in both cases is thinking you need to stop writes or shutdown your master- you don't, just make sure you start replicating from the coordinates corresponding to the moment you took your backup (the end of XtraBackup run or the start of mysqldump/mydumper).

To prevent data drifts in the future consider performing regular consistency checks with tools such as pt-table-checksum or migrating to row based replication.

| improve this answer | |

Your Answer

