-2

I'd like to have a Backup/Recovery tool for MySQL (MariaDB/Percona) servers which has somehow the same features as barman has for postgresql

  • take full backups on a regular basis (xtrabackup, but cool life with others too)
  • collect binary logs (copy them over or connect as slave ...)
  • some 'easy' way, to restore one server to the state of a specific transaction or a point in time (in a perfect world, this tool would copy over the base backup and then replay the logs until the point in time in question automagically)

Things that would be nice:

  • easy way to add new servers to the backup system
  • redention of backups

Any hints, ideas, ... ?

m.sr
  • 1,060
  • 1
  • 8
  • 19
  • Don't really understand why this is closed as off-topic. Al tough there is a marked answer the "answer" doesn't answer the question; Taking incremental backups every X days/hours/etc. is no where near the same as streaming binlogs. Streaming binlogs allow for almost zero dataloss in case of disaster. Taking incremental backups will have a far larger dataloss window. Even 5 years later MySQL does not have a clear cut out of the box application / solution for this. So this is actually a very interesting technical question which apparently the moderators failed to recognize as such. – Jasper Siepkes Jul 31 '18 at 08:36

1 Answers1

2

If you want to take incremental backup use innobackupex

Creating an Incremental Backups with innobackupex

First, you need to make a full backup as the BASE for subsequent incremental backups:

 $ innobackupex /data/backups

This will create a timestamped directory in /data/backups. Assuming that the backup is done last day of the year, BASEDIR would be /data/backups/2013-03-31_23-01-18, for example.

Note You can use the innobackupex --no-timestamp option to override this behavior and the backup will be created in the given directory. If you check at the xtrabackup-checkpoints file in BASE-DIR, you should see something like:

 backup_type = full-backuped
 from_lsn = 0
 to_lsn = 1291135

To create an incremental backup the next day, use the --incremental option and provide the BASEDIR:

 $ innobackupex --incremental /data/backups --incremental-basedir=BASEDIR

and another timestamped directory will be created in /data/backups, in this example,

/data/backups/2013-04-01_23-01-18 containing the incremental backup. We will call this INCREMENTAL-DIR-1.

If you check at the xtrabackup-checkpoints file in INCREMENTAL-DIR-1, you should see something like:

 backup_type = incremental
 from_lsn = 1291135
 to_lsn = 1352113

Creating another incremental backup the next day will be analogous, but this time the previous incremental one will be base:

 $ innobackupex --incremental /data/backups --incremental-basedir=INCREMENTAL-DIR-1

yielding (in this example) /data/backups/2013-04-02_23-01-18. We will use INCREMENTAL-DIR-2 instead for simplicity.

Abhishek Anand Amralkar
  • 1,979
  • 12
  • 16