Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Is there a program with which I can create daily MySQL, PostgreSQL and MariaDB backups with these requirements:

  • The first time, make a full backup.
  • The second time, only the changes since the last backup, and so on.

Only with some software or script, not PHP.

share|improve this question
Off-topic for SO; belongs on Database Administrators – Jim Garrison May 2 at 21:45
thank you for you reply – mlopezdev May 2 at 22:03
1  
possible duplicate of Backup daily for MySQL, MariaDB or PostgreSQL – dezso May 3 at 0:04

4 Answers

up vote 4 down vote

If you're using MySQL with InnoDB tables, then you could take incremental backups with Percona's xtrabackup, or talk to Oracle about enterprise licensing, which would net you MySQL Enterprise Backup.

Xtrabackup is a brilliant piece of software, but it behaves very differently from traditional MySQL backups using mysqldump, and it's worth spending some time studying and testing it before you look at rolling it into production.

If you're using MySQL with MyISAM, you're pretty much out of luck. You could perhaps do something logically, like maintain "last modified" timestamps on your tables that are maintained by triggers, but there's manual maintenance and several caveats that might make that unwise.

Recent versions of Xtrabackup should also work with MariaDB, though again you'll probably want to test this yourself.

share|improve this answer
Thanks, but I have noticed that this program is only for linux, and I need it for Windows Server. – mlopezdev May 2 at 23:11
It looks like Oracle's backup tools are available for Windows. Alternatively, you could take mysqldump logical backups and apply the binary log (if you've got it enabled). I can flesh this answer out more if you need. – Nathan Jolly May 3 at 9:20
up vote 2 down vote

Postgres: The write ahead log(s), WAL; it's a complimentary component of your backup strategy. It's exactly what you want.

share|improve this answer
up vote 0 down vote

For Postgresql on MS Windows, the Postgresql wiki has an "Automated Backup on Windows" article that may be of use.

There is also "Instant PostgreSQL Backup and Restore How-to" available from Packt that provides a good overview of the various options available using the tools that come with Postgresql. There is nothing in the booklet that can't be found on the web but they do a nice job of pulling that all together in one document and the price is very reasonable.

share|improve this answer
up vote 0 down vote

For MySQL and PostgreSQL you can use the Snapshot Backups in order to obtain full or incremental backups:

Some file system implementations enable “snapshots” to be taken. These provide logical copies of the file system at a given point in time, without requiring a physical copy of the entire file system. (For example, the implementation may use copy-on-write techniques so that only parts of the file system modified after the snapshot time need be copied.) It is available through third-party solutions such as Veritas, LVM, or ZFS.

Personally, for MySQL, I use the ZFS file system on FreeBSD

share|improve this answer
1  
Note that this is only safe if all parts of the DB are on the same filesystem so you get a consistent snapshot. Otherwise for Pg you can use pg_start_backup with rsync per the documentation. – Craig Ringer May 3 at 7:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.