Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
rpedrica opened this issue Aug 21, 2019 · 0 comments
Open

Comments

@rpedrica
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant