I have this database setup:

Master -> Slave A

I would like to accomplish the following:

Master -> Slave A -> 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 & opens new binary log with consecutive number ) will be beneficial or not :

  1. 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 & can be retrived from binary logs.

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

  3. 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 & 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' > before_rd.sql

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

mysql -uuser -pXXX -h192.168.XX.XX -e 'show master status' > after_rd.sql
  • If No of Binary logs are less or none during backup time :

    Take after backup binary logs reading from master & can be used for starting replication ( -- flush-logs can be used ) as data consistency is maintained.

| improve this answer | |

What is MSP? (Perhaps it is not relevant to the Question.)

File copy

To turn M->S into M->S->S2, do the following. Copying a file tree should be faster than dump and reload.

  1. Stop MySQL on S.
  2. Copy the directory tree where mysql lives from S to S2.
  3. Start MySQL on S.
  4. Set up my.cnf on S2; be sure to have a unique server-id.
  5. Do a CHANGE MASTER on S2 to point to S.

(Please be careful -- I may have left out a minor step.)

Try not to copy binlogs, slowlogs, general logs, but copy iblog* and ibdata1 (plus other stuff).

Dump & reload

Dump and reload is perhaps fastest by doing:

mysqldump --host S ... | mysql --host S2 ...

That way, the 1-2TB of dump file never hits the disk. If you have unnecessary indexes, they should be removed first; some of the import time on S2 is rebuilding indexes.

| improve this answer | |
  • Thank you! MSP is Managed Service Provider. Also, do you have any documentation for the file copy method? – katiekeel Apr 25 '19 at 16:38
  • I've found it, after a bit of poking (was blocked by not knowing what, precisely, to Google): dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/… – katiekeel Apr 25 '19 at 16:48
  • @katiekeel - sorry, I do not have a good link to give you. The link you found agrees with my instructions except for not talking about creating another Slave. (I added a new step 4.) – Rick James Apr 25 '19 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.