Existem duas abordagens básicas para esse problema que eu conheço. Primeiro, se você estiver executando o InnoDB em vez do Myisam, então você pode fazer o backup em uma transação (--single-transaction --lock-tables = FALSE), que combinado com --flush-logs (não obrigatório mas legal) e --master-data lhe dará um backup consistente com informações de posição de replicação. Os logs de limpeza redefinirão os logs antes que o dump seja criado, o que significa que a posição sempre será 106 e --master-data colocará o nome e a posição do arquivo de log no arquivo de dump. Claro, você tem que executar isso no mestre para --master-data para trabalhar.
A segunda maneira, que você mencionou, é usar um terceiro host para criar os backups. Nesse caso, você precisa parar a replicação, certifique-se de que o banco de dados seja read_only (embora, na verdade, todas as suas réplicas devem ser lidas somente porque isso não afeta gravações da replicação) e crie seu backup E registre a posição de replicação. Você não pode usar --master-data neste caso. Em vez disso, você pode fazer algo assim:
echo 'stop slave' | mysql {options)
mysqldump {your options} > DB.sql
echo 'show slave status\G' > DB.replication
echo 'start slave' | mysql {options)
Se você precisar restaurar a partir desse backup, execute a restauração e, em seguida, configure a replicação em que os dois parâmetros master_log_file e master_log_pos vêm do arquivo DB.replication:
master_log_file = value of Master_Log_File
master_log_pos = value of Exec_Master_Log_Pos
Nota: você pode E DEVE testar isso de outra réplica.
Nota adicional: se você tiver um pool de réplicas (por exemplo, se tiver separado as leituras de gravações de um aplicativo da Web), é possível que as réplicas estejam fora de sincronia com o novo mestre; isso pode acontecer se o failover ocorrer durante um período de E / S de gravação pesada, já que o fluxo de réplicas é assíncrono e não há garantia de que o seu modo de espera esteja na mesma posição que as outras réplicas ao realizar failover. No entanto, isso não aconteceu comigo ainda ...