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
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:
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