xtrabackup everyday backup -- how to?

Hello experts,
I am Avinash.
I am very very new to MySQL and Percona xtrabackup utility.
I have recently been hired as a MySQL DBA. I come from Oracle background. i started liking it :-).
I am working on MySQL 5.7.14 Community Server configured on RHEL7.

Everyday logical dumps are scheduled, using mysqldump --single-transaction (only for InnoDB tables).
No other schemas are backed up.
I was searching for an option for configuring physical hot backups. I came across xtrabackup utility and I got it installed and configured as per the Percona Documentation provided. I have yet to complete the documentation :-).

I configured the my.cnf file for xtrabackup by adding target_dir option under [xtrabackup] section there.
target_dir = /mysql_57_backup
port = 3306
socket = /var/lib/mysql/mysql.sock

I back the MySQL schemas using the following command:
xtrabackup -uapawar -p********* --backup --target-dir=/mysql_57_backup -P 3306 -H localhost -S /var/lib/mysql/mysql.sock

My first doubt/confusion

  1. While going through Ronald Bradford’s Backup and Recovery book, I read/understood that, to be able to have point in time recovery of the database, I must turn on log_bin, sync_binlog and it’s a good practice to set innodb_flush_log_at_trx_commit to 1. I did it. Am I correct here?

My question about the physical backups is as follows:
I backup the database using above command, on let’s say 16th Feb. It was successful.
xtrabackup: Transaction log of lsn (555871761) to (555871770) was copied.
160218 18:39:57 completed OK!

But when I use the same command next day let’s say on the 17th, I get
xtrabackup: Can’t create/write to file ‘/mysql_57_backup/xtrabackup_logfile’ (Errcode: 17 - File exists)
xtrabackup: error: failed to open the target stream for ‘xtrabackup_logfile’.

This behaviour is explained in the Documentation. The error is understood and when I delete the files in the target_dir, the backup is successful. (I am trying this on my personal laptop on a VM).

My question is what is the meaning of putting target_dir in the my.cnf file? Also, in order to keep everyday backups, let’s say, for a week, should I change the directory everyday?

I need to maintain a seven day retention policy, and I am not allowed to shut down the instance, it’s an online store. I don’t want to rely only on mysqldump, because there’s a lot of talk on mysqldump backups, such as some options won’t be applied to MyISAM and some won’t be applied to InnoDB and there’s recovery time issue if the dumps are very big. Hence the schemas that I backup, using mysqldump --single-transaction, are InnoDB schemas only. I don’t backup other schemas. There are only InnoDB tables those matter to the client.

Does xtrabackup read configuration from my.cnf? Because every time I try to take backup, I have to pass --target-dir, --port, --socket options. It’s not a problem, I am just trying to understand if I am doing any mistake.

Please, forgive me for the following question because it is not related to xtrabackup. I am just trying to gain understanding. I am really short on understanding of MySQL.

Is my mysqldump --single-transaction choice correct to create consistent backups?

Also please accept my sincere apology, if the question is too long and confusing.
Thanking you.


Well, I have taken a full backup using the xtrabackup --backup command, now I will have to take an incremental backup. Let me see what happens.

The document further explains how to use the incremental backup. I think I posted the question too early. It turned out to be a stupid question. My apologies.

Hey, no worries! There are NO stupid questions, just answers you haven’t found yet :slight_smile:
Welcome, anyway, to our Forums and I look forward to helping you find some answers in the near future!

Thanks a lot :-).


I’m trying to use percona to perform full and incremental backup.

At the end of the backup I don’t have: completed OK!

I just have:
xtrabackup: Transaction log of lsn (289671176838) to (289671187227) was copied.

When i looked into the log output. I don’t any message like:

All tables unlocked

Some information from the output log:
xtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 289671069058 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mnt/cluster/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:500M;ibdata2:500M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880

log scanned up to (289671178616)
xtrabackup: Generating a list of tablespaces

log scanned up to (289671179328)
xtrabackup: using the full scan for incremental backup
xtrabackup: Starting 2 threads for parallel data files transfer

log scanned up to (289671179868)
[01] Compressing, encrypting and streaming ./ibdata1
[02] Compressing, encrypting and streaming ./ibdata2

log scanned up to (289671181257)
[02] …done
[02] Compressing, encrypting and streaming ./suitecrm/aos_product_categories.ibd
[02] …done

[02] Compressing, encrypting and streaming ./sms_sahelmoney/com_sms_clients.ibd
[01] Compressing, encrypting and streaming ./sms_sahelmoney/srv_adresses.ibd
[01] …done
[02] …done

log scanned up to (289671187227)
[01] Compressing, encrypting and streaming ./sms_sahelmoney/pays_passerelles.ibd
[01] …done
[02] Compressing, encrypting and streaming ./sms_sahelmoney/devises.ibd
[02] …done

log scanned up to (289671187227)
xtrabackup: The latest check point (for incremental): ‘289671187227’
xtrabackup: Stopping log copying thread.
.>> log scanned up to (289671187227)

xtrabackup: Transaction log of lsn (289671176838) to (289671187227) was copied.

Could you please help me?


I just update XtraBackup

I hade XtraBackup 2.1 and now I have the version 2.4.9.

Excellent - thank you for letting us know, appreciated!