I’m fairly new to using Percona’s XtraBackup. Our databases have grown to a size where mysqldump is taking up to 12 hours to backup an entire server, and causes locking issues. This obviously is no longer acceptable.
What I like about mysqldump is that I can directly backup and restore individual db’s. My understanding (from a face to face conversation with the CEO of Percona) is that XtraBackup is not so easy in this aspect. There was some talk about doing a backup and then restoring the data elsewhere and copying back only what we need. I’m having a heck of a time finding good documentation for entry level Percona skills.
I feel as though the Percona instructions (PDF) were written for someone with years of experience and I am spending a lot of time doing trial and error on a test VM.
Here is what I would like to do:
Backup all the databases on a server, and if something becomes corrupt, I want to be able to restore a single database or a table, etc. I’m not a fan of having to restore the entire thing when only portions are needed. Does this make sense? Thus far the copy-back seems to require EVERYTHING.
I would like to be able to exclude a database in my backup. We have a tmp db that is constantly changing as temp data and tables are written. This causes a failure on the full backup because XtraBackup will attempt to write files it has already written - I’m guessing because the tables were deleted and recreated during the backup process. I get an error like:
xtrabackup_56: Can’t create/write to file ‘/media/Synology/2015-05-06_14-36-47/tmp/wrk_ReportStores_StoreList.ibd.pmap’ (Errcode: 17 - File exists)
innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2622.
If I scroll up in the SSH screen I see the file was created maybe 5 minutes earlier.
 Copying ./tmp/wrk_reportstores_storelist.ibd to /media/Synology/2015-05-06_14-36-47/tmp/wrk_reportstores_storelist.ibd
This aborts the backup all together. Is there an exclude option?
- The apply-log switch… do you do that AFTER you create the backup of BEFORE you do a restore? I want to make sure we aren’t writing to the production DB. I thought it was taking the log it captured and applying it to the backup files, but I want to be sure.
Here is the command I am running for the full backup:
sudo innobackupex --user=backupuser --password=backuppassword --compact /media/Synology/
Any help would be greatly appreciated!