I am a new user to xtrabackup. I have a WHM server and was worried about the integrity of backups in the nightly routine. My DB is approx 1GB so getting a little big for mysqldump and I was wondering if xtrabackup would be better.
I have tried a round-trip and it seems to work but I wanted to check if what I am doing it correct. Here goes…
Backup using --database and --include options to take just the one DB (all I want, for now)
Run --apply-log to the backup to make it ready for restore (I think)
When I want to restore :
Copy the contents of the backup folder (the one with the ibd files in it) to the equiv location in the mysql data directory
Bounce MySQL (is that the quickest way, other than discarding and importing each tablespace?)
Like I said, I tested and it worked, but I am not 100% sure if it is the best way, so would love some advice is someone has the time.
To take a backup of a single database you just need to use --include. --databases has no effect for InnoDB files. For example, to backup only the “test” database you can do the following:
innobackupex --include=“^test.” /tmp/
This kind of backups are called “partial backups” and the restore process is more complicated. First before taking the backup you need to have innodb_file_per_table enabled.
During the preparation stage you need to use --export --apply-log. That will create .exp files that will allow you to recover tables one by one. Then you will need to import tables one by one following this procedure:
You cannot just move the backup to the datadir of mysql if you are backing up a single database, because the shared tablespace (ibdata) contains information from all the InnoDB tables. If you restore a partial backup without following the restore procedure of that link you can lose the access to the access to all other databases.
Thanks for that, it makes sense. I’m not sure I feel that comfortable backing up a single big DB that way though, having to go through that process for every table on restore. The database I have in mind is <2GB and I have been looking at msqldump --single-transaction to handle getting a valid backup from it. Am I correct in thinking this is probably the most straight forward way of doing it and that xtrabackup is maybe a better option if I were to be backing up all the DB’s on a server?
mysqldump should be fine on a database that small. With --single-transaction and all InnoDB tables, you should be able to get a consistent backup with minimal impact on the server. It is generally a good idea to have a logical type backup to supplement your binary backups anyway. The main downside to a logical backup like mysqldump is that the restore time will be longer in most cases, however with only a 2GB database that should not be too big of an issue as long as the server has decent I/O capacity.
Another possible option would be to setup a slave server that contains (and replicates) only the database you want to backup, and then just use Xtrabackup to backup that slave.
Thanks, that makes a lot of sense. I think for what I need at the moment mysqldump is what I need, but I am keen to test and try out xtrabackup as you suggest as my needs will no doubt grow over time and I see xtrabackup is quite powerful. I think I’ll use mysqldump on my production box for now and setup xtrabackup on a development machine just so I don’t accidentally trash it
To help, to the point, the link mentioned is from the documentation in general (I believe that because I see a partial link name ‘…porting-tables’ this page has been removed and directed to the general documentation), the specific link for restoring partial backups is this:
