For long time as main backup solution for MySQL on Linux we have been using LVM snapshots. Performance concerns from performance critical envinronment caused us to do LVM performance research which showed horrible results forcing us to look at more performing alternatives.
Innodb Hot Backup is a good working solution but it is not Open Source and so we can’t ensure it will support all XtraDB features – extra undo slots, rollback segments etc.
After considering all alternatives we decided to develop Percona Xtrabackup tool, which will use same backup approach as InnoDB Hot Backup, that is taking online copy of InnoDB table files and transactional logs. This is going to be fully OpenSource, GPL licensed backup solution for MySQL (MyISAM and InnoDB for now).
We gathered basic ideas here https://www.percona.com/docs/wiki/percona-xtrabackup:start, and for the first stage it will be just full backup of files. For next stages we are looking to implement advanced functionality like incremental backup (copy only changes from last backup),table-level backup (copy only specified tables), streaming and parallel copying. Source code will be posted to Xtrabackup Launchpad project (https://launchpad.net/percona-xtrabackup/)
We call for more ideas you would like to see implemented (please leave them in comments). We got sponsorship for the base implementation work, but we’re still looking for sponsorship for additional features, contact us https://www.percona.com/contacts.html if you are interested. Percona will also significantly invest in this project.
Hi,
Great to hear! Here are a few ideas:
* I was always upset with the fact the restore (ibbackup –apply-log) restores the ibdata and log files *in the same path* as the backup files. This made for harder work when disk space is limited.
The backup stage allows for different paths, which is nice. If you could have this simple feature – it would make a great difference.
* The restore part has the property of not letting you know how well it is progressing. I mean, there’s the unpacking of the ibdata file – great. But then it start “sppliting logs”, over and over and over, each time counting 0..100. You just never know if you should keep waiting for an hour or 5 hours (I got 5 hours a couple of weeks ago, and it was frustrating)
So – a progress indicator would be great!
* Obviously – if you could bundle the schema along with the data, it would be beneficial. It happened to me once, that due to a programmer’s mistake, the schema was changed while (or just before) creating the schema, but after I’ve dumped the schema to the new backup machine. So after hours of restoring from backup – I got errors as the schema was incorrect.
Good luck with development
Typo (how did I get it so wrong?) above:
“spliting logs” ==> “applying logs”
Wow, sounds great. Finally some alternative to innodb hotbackup.
Don’t get me wrong, its a great tool but too expensive for my use case and there hasn’t been much development anymore.
Yeah, sounds great!
few ideas:
– mysql/innodb is able to use raw partitions. as there are no .ibd files in this case, it would be nice to backup these as well. for those who made the decision to enable a raw partition, this could be a way to restore the backup using “normal” ibd files instead.
– having a small file indicating the binlog position along with the backup files could help enabling replication. (another way would be adding a “flush tables”, but if one has to use a hot backup tool, its unlikely that one wants to flush tables 😉
Shlomi, Bernd
Great, thank you for ideas, we added them to the list!
Thanks. I’m going to try your software on my DB server
I am using Zmanda, and what I like is easy configuration and scheduling of backups, and reports.
It gives you a good visibility on what is going on with your backups.
Zmanda is not a backup tool but more precisely a backup managing tool,
it uses all known backup solutions already existing.
I dont know if it is good to have this features in Xtrabackup or just think if it would be possible to integrate Xtrabackup into Zmanda like innodb hot backup is. Just some ideas.
Cheers
Claudio
Claudio,
We are open to integrate it, but we can’t force Zmanda to add support. If you are user of Zmanda you probably can send feature request to them to add support of xtrabackup.
I have two databases on this box. The defaul /etc/my.cnf is fine and i can take the backup using innobackexp-ex. The problem is another slave on a non-default location. I’m using the following command and it throws error
xtrabackup –backup –user=root –password=test \
–no-defaults \
–slave-info \
–datadir=/data/comdb-slave/mysql/datafiles/ \
–socket=/data/comdb-slave/var/run/mysqld/mysqld.sock \
–innodb_log_file_size=20M \
–innodb_data_file_path=ibdata1:10M:autoextend \
–innodb_log_group_home_dir=/data/comdb-slave/mysql/datafiles/ \
–innodb_data_home_dir = /data/comdb-slave/mysql/datafiles/ \
–target-dir=/data/mysql-ibbackups/datafiles/
100113 20:19:00 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: File name –target-dir=/data/mysql-ibbackups/datafiles/ibdata1
InnoDB: File operation call: ‘create’.
InnoDB: Cannot continue operation.
But the folder is present and has the permissions:
ls -l /data/mysql-ibbackups/
total 184
-rw-r–r– 1 root root 435 Jan 13 18:53 back1
drwxr-xr-x 3 mysql mysql 4096 Jan 13 20:02 datafiles
-rw-r–r– 1 root root 178688 Jan 13 18:39 xtrabackup_logfile
Not sure why this is happening. The slave is good and replicating data from master.
I hope that backup on slave db can remember the masterdb binlog’s position. Now we use ibbackup to backup innodb and we do this on the slave machine, so the backup wont impact our application. But when we recovery the backup, we just get the binlog’s position of the slvedb. But this is useless . We need to recover it as a new replication. but we don’t know the position. So, if XtrBackup can remeber the position of master’s binlog, it’s perfect.
Juan,
We are working directly with customers and fully understand all practical needs.
Of course xtrabackup stores binary log position on master.
I’m currently trying this out on our MySQL 5 database that has a 950GB ibdata file (stores millions of blob PDF files). The current command I am using is:
“C:\Program Files\Percona Inc\XtraBackup\xtrabackup.exe” –defaults-file=C:\MySQL\my.ini –backup –innodb_data_home_dir=E:\MySQLData\data –innodb_data_file_path=ibdata1:10M:autoextend –target-dir=E:\Backup –tmpdir=E:\TempBackupFiles
Any hints on any other options I should be setting for such a large database?
Thanks!
Scott
Scott,
There no really options to improve backups.
Only you may want to use –use-memory when you run –apply-log stage.
i cannot download and use xtrabackup in windows, can u help me?
We reposted 32bit binaries there
https://www.percona.com/downloads/XtraBackup/XtraBackup-1.6/Windows-alpha/32bit/
i cant download and use xtrabackup in windows,
help me please.
Vadim:
xtrabackup full backup works great with both innodb and myisam. but from my testing, xtrabackup incremental backup ONLY work great with innodb, it does not work with MYISAM (it does not catch the incremental changes of myisam).
Is this a known issue or I did something wrong?
Thanks a lot
That is a known limitation that can’t be fixed.
Thanks, Baron
You guys at Percona a great!
I just saw the comparison of your tool with the mysql tool (https://www.percona.com/doc/percona-xtrabackup/intro.html) and its impressive to see your features list. Thank you very much for open sourcing and sharing such a great tool!
jens