search

sixhop / AutoMySQLBackup

A fork and further development of AutoMySQLBackup from sourceforge. http://sourceforge.net/projects/automysqlbackup/
GNU General Public License v2.0
341 stars 103 forks source link

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

Open rpedrica opened 5 years ago

rpedrica commented 5 years ago

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

BigAndini commented 5 years ago

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?