Backup files for slave setup so not have correct binlog file/pos

[rpedrica]

When doing backups of a master server that are going to be used for slave restore and replication (eg. CONFIG_mysql_dump_master_data=2), and where 1 or more of the dbs on the master server are large, there is the possibility that the binlog file/pos values may be different between the dbs due to --single-transaction happening on a per db basis and time passes between the 1st db being done and the others. Here is an example:

• Backups are set to start at 3am
• CONFIG_mysql_dump_single_transaction='yes'
• CONFIG_mysql_dump_master_data=2

After backup, and when checking data/time of backup files and binlog file/pos:

Aug 21 05:18 db1.sql.gz (large) - mysql-bin.009304/200746006
Aug 21 05:18 db2.sql.gz (small) - mysq-bin.006306/978513887
Aug 21 05:18 db3.sql.gz (small) - mysq-bin.006306/978513887

This makes sense because binlog file/pos is taken at start of backup and time passes between the 1st backup and the other 2. Is there a way to use automysqlbackup for slave restores where the dbs are large and keep same binlog file/pos across all dbs?

The only other option is to do manual backups with 'flush tables with read lock' enabled.

Regards, Robby

[BigAndini]

Thank you for your patience. What would you suggest? Saving the binlog position for each database? I don't know if it's a good idea to have a per database dump when setting up a slave server. How would you solve that?