I am Avinash,

I have a slight confusion about incremental backups. I am going through the MySQL 5.7 Docs to study the backup process using mysqldump. I am not using any master-slave configuration. I am only willing to have Point in Time Recovery capabilities. I am aware of the xtrabackup tool, but I want to use the logical backups as well.

The Docs explain how to make a full backup of InnoDB tables

mysqldump -u user -ppasword --single-transaction --flush-logs --master-data --all-databases > full_backup_sunday_1PM.sql

This will make a full backup of all the changes made since mysql-bin.0001 till let's say mysql-bin.0007. Flush logs will flush the logs and switch the current file and all the changes till mysql-bin.0007, mysqldump will be aware of.

--master-data option will print two lines to indicate the point in time recovery beginning time and master log position.

My confusion is at the following command (printed as it is from the docs)

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
--all-databases > backup_sunday_1_PM.sql

in the first command the author uses only --master-data and in the second command he/she uses --master-data=2. I don't understand the difference. Could you experts please help me out?

Does --master-data=2 indicate that it's an incremental backup of level 1? Does it indicate anything related to master-slave configuration? What command should I use to make incremental backups?

The second command again mentions backup of Sunday itself, that's why may be I am getting confused. The author uses Sunday as full backup (in the first command) and under incremental backup (for Monday) explanation (second command) also he uses Sunday in the dump file name.

Thanking you. Regards, Avinash

  • mysqldump --help tells me that --> if equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. (DOES THIS MEAN THAT THE change master COMMAND WILL BE COMMENTED IN THE DUMP FILE?). Thank you. Feb 26, 2018 at 5:35
  • I think after flush logs when the new log file is generated, I will have to start saving these new log files and those log files will be used as my incremental backups. Also I will have to use mysqlbinlog to restore the changes made after full backup. :-). Thank you. Feb 26, 2018 at 6:23
  • MySQL community doesn’t support Incremental updates, you need enterprise for that or a 3rd party tool. Feb 15, 2022 at 16:47


Your Answer

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

Browse other questions tagged or ask your own question.