mysql backup

what is best option to take mysql backup ( incremental backup ) without any database locking for a database having size more than 500 GB and increasing continuously.
also how to keep backup on different geographical location.

The method I used:

mysqldump : lock the table, not suitable for big databases and for real time application.

ibbackup : good tool but i do not find any incremental backup option there.

using binary log for incremental backup is time consuming process.

Please suggest.

Hi,

If you are using only InnoDB tables, then you should consider xtrabackup. Xtrabackup is similar to ibbackup, but it does support incremental backups:
http://www.percona.com/docs/wiki/percona-xtrabackup:start

Unfortunately Xtrabackup can’t stream an incremental backup to a remote machine, so you will have to make a local backup then rsync it to a remote location.

–Swany

there are few myisam tables also, can xtrabackup handles both innodb and myisam?

this is possible, i read it in documentation.
http://www.percona.com/docs/wiki/percona-xtrabackup:xtraback up_manual#making_a_partial_backup

If xtrabackup provides all required backup feature then how xtrabackup is different from ibbackup which is paid version?

ibbackup is not a paid version of XtraBackup. ibbackup is a closed-source backup utility owned by Oracle. XtraBackup is an open-source backup utility created by Percona about a year and a half ago. They are different products from different companies. They just happen to work in a similar fashion.

@yogesh77

I might be looking at your situation the wrong way. But if you install mysql 5.1 from RPM, xtrabackup have RPMs available as well.

http://www.percona.com/downloads/XtraBackup/

I am able to install RPM xtrabackup,
some more error after running backups.

100814 10:20:21 InnoDB: Operating system error number 9 in a file operation.
InnoDB: Error number 9 means ‘Bad file descriptor’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-erro r-codes.html
InnoDB: File operation call: ‘close’.
InnoDB: Cannot continue operation.

any body facing this issue while taking backup?