Any way to do incremental mysql backup

dnwk

Is there anyway to backup a Mysql database with daily Incremental? I don't want to run mysqldump too often as it took too much resources and the database is large. Is there anyway that I can run a full backup of the database at the beginning and then incremental?

taronyu

How about setting up a slave server?

mikho

http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/mysqlbackup.incremental.html

GoodHosting

Ever since MySQL 4.0 you've been able to do an incremental backup using mysqldump. That being said, depending on your data structure I would advise on using a technique coined as 'smart dumping' instead, where you only dump what you know is new.

This method requires you to know the schema of your database, such as if you wrote the application in question; and is completely incompatible with SolusVM, WHMCS, and a lot of skiddy systems due to their lack of intelligence and design when it comes to the database.

The idea behind it is simple: [psuedocode]

let LastID equal IDofLastDumpedRecord
let LastUpdate equal LastUpdateTime

for records where record.ID > LastID
  dump record

for records where record.LastUpdate > LastUpdate
  dump record

This technique is sufficient in cases where you know that your data will not update without the lastupdate time changing, and that all new records will have a greater ID than the previous records (ie: any sane data structure.) This technique is also much faster (depending on the language done in***) than using incremental mysqldump as it does not require a fullscan on the database. The "ID" should be a primary key in this example for the best possible results.

dnwk

@taronyu said:
How about setting up a slave server?

I use to use Slave MySQL. However, it is very easy for it to be out of sync.

dnwk

@MikHo said:
http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/mysqlbackup.incremental.html

That's only for MySQL 3.7. It is no longer exist for 5.5

dnwk

@HardCloud said:
Ever since MySQL 4.0 you've been able to do an incremental backup using mysqldump. That being said, depending on your data structure I would advise on using a technique coined as 'smart dumping' instead, where you only dump what you know is new.

em... That's something pretty painful to setup.

dnwk

Anybody use Percona XtraBackup before?

Chumbi

@dnwk said:
Anybody use Percona XtraBackup before?

Yes, on a daily basis. The other Percona tools are also very nice (for instance, you can checksum test your slave on a regular basis to detect out of sync).

dnwk

@Chumbi said:
Yes, on a daily basis. The other Percona tools are also very nice (for instance, you can checksum test your slave on a regular basis to detect out of sync).

Have you tried the incremental feature?

Chumbi

I don't use the incremental feature of XtraBackup because incremental w/ XtraBackup only works with innodb. But the databases I am backing up are mixed (innodb and myisam).

MassNodes

maybe mysql replication with a dump backup script on a crontab?

Chumbi

If you want something like incremental, I suggest you take a look at "mysqlbinlog --raw" (mysql must be 5.6+). It makes raw copies of the binlogs (master must use them) in real-time.... meaning you can go back in time at any point.

[Edit] linky for more details: http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-backup.html

MitchellRobert

ZFS is the way to go for snapshots/backups.

tchen

Along the same lines as the binlog is Zmanda Recovery Manager.

joepie91

Can always just use some method of dumping into SQL format, plus Git

crossing

@joepie91 said:
Can always just use some method of dumping into SQL format, plus Git

Right !

dnwk

@joepie91 said:
Can always just use some method of dumping into SQL format, plus Git

So what method you can dumping into SQL without pushing server load to 2.5?