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