Effect of --flush-logs on a slave mysqldump? I have this database setup:

Master -&gt; Slave A


I would like to accomplish the following:

Master -&gt; Slave A -&gt; Slave B


I have reviewed the documentation. 

Master and Slave A are both in an MSP environment, while Slave B will be an RDS instance. I can connect to Slave A in the MSP just fine. Master binlog files are being copied over to the instance running Slave A. All binlog files are being kept around for a long time. 

However, my database size is 1.3 TB. Thus, a mysqldump on Slave A takes several hours to complete, and even longer to load into my new empty Slave B before beginning replication. In the best case scenario, it takes ~36 hours from starting the dump to when replication can start.

I would like to use --flush-logs, as described in the third code snippet here, to reduce the chances of having to use CALL mysql.rds_skip_repl_error; repeatedly after I begin replication. This is especially important because there is currently no way to accomplish the equivalent of slave-skip-errors = 1062 in RDS, and also because I'm unsure what the effect is on data consistency.

Is my assumption correct, that using --flush-logs reduces the likelihood of duplication errors arising after replication has begun?

If it is correct, what would the effect be of using mysqldump --single-transaction --flush-logs on Slave A? Would it behave as a tunnel and actually flush the logs on Master, or would it only affect the logs present on Slave A?
Point to be considered whether your --flush logs ( Closes current binary log &amp; opens new binary log with consecutive number ) will be beneficial or not :

One thing that you are using --single-transaction option (It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications). This means a snapshot of current database is taken for backup where data modification during this timestamp is not considered in backup &amp; can be retrived from binary logs.

To check the write operation is performed during backup activity on database, you can verify by no of binary logs written during that time.

If write operation intensity is more during this time, then you can afford to start replication from last file written during backup completion.


To overcome this thing,

Measure no of binary logs written during backup time. ( Hope backup time is preferred where less write operation are performed on db).

If No of Binary logs are more during backup time :
Take before backup binary logs reading from master &amp; can be used for starting replication ( Probably there may duplicate error, need to skip it) as data consistency is concerned.


mysql -uuser -pXXX -h192.168.XX.XX -e 'show master status' &gt; before_rd.sql

time mysqldump  -uuser -pXXx -h192.168.XX.XX --force --all-databases --max_allowed_packet=360M --lock-tables=false --triggers --routines &gt; backup_$dd.sql 2&gt; bkp_mep_$dd.log

mysql -uuser -pXXX -h192.168.XX.XX -e 'show master status' &gt; after_rd.sql


If No of Binary logs are less or none during backup time :
Take after backup binary logs reading from master &amp; can be used for starting replication ( -- flush-logs can be used ) as data consistency is maintained.