Xtrabackup allows you to backup and restore databases from full backups or full backups and its incrementals.
Backup client with ssh/rsync remote host:
xtrabackup:
client:
enabled: true
full_backups_to_keep: 3
hours_before_full: 48
hours_before_incr: 12
database:
user: username
password: password
target:
host: cfg01
Note
The full_backups_to_keep parameter states how many backup will
be stored locally on xtrabackup client. More options to relocate local
backups can be done using salt-formula-backupninja.
Backup client using DB API instead of socket (still needs to be run on the same server as DB):
xtrabackup:
client:
enabled: true
full_backups_to_keep: 3
hours_before_full: 48
hours_before_incr: 12
database:
user: username
password: password
host: localhost
port: 3306
target:
host: cfg01
Note
DB user username must have “RELOAD” and “REPLICATION CLIENT”
privileges on all databases.
Backup client with local backup only:
xtrabackup:
client:
enabled: true
full_backups_to_keep: 3
hours_before_full: 48
hours_before_incr: 12
database:
user: username
password: password
Note
The full_backups_to_keep parameter states how many backup will
be stored locally on xtrabackup client.
Backup client with ssh/rsync to remote host with compression, IO throttling and non-default backup directory on server:
xtrabackup:
client:
enabled: true
full_backups_to_keep: 3
hours_before_full: 48
hours_before_incr: 12
compression: true
compression_threads: 2
throttle: 20
database:
user: username
password: password
target:
host: cfg01
server:
enabled: false
backup_dir: /srv/backup
Note
More options to relocate local backups can be done using
salt-formula-backupninja.
Note
If the server section is ommited, backups will be made to
default location, same on both client and server side.
Backup client at exact times:
xtrabackup:
client:
enabled: true
full_backups_to_keep: 3
incr_before_full: 3
backup_dir: /var/backups/mysql/xtrabackup
backup_times:
day_of_week: 0
hour: 4
minute: 52
compression: true
compression_threads: 2
database:
user: user
password: password
target:
host: host01
Note
Parameters in backup_times section can be used to set up exact
time the cron job should be executed. In this example, the backup job
would be executed every Sunday at 4:52 AM. If any of the individual
backup_times parameters is not defined, the defalut * value will
be used. For example, if minute parameter is *, it will run the backup
every minute, which is ususally not desired.
Available parameters include:
day_of_weekday_of_monthmonthhourminute.See the crontab reference for further info on how to set these parameters.
Note
Please be aware that only backup_times section OR
hours_before_full(incr) can be defined. If both are defined. The
backup_times section will be peferred.
Note
New parameter incr_before_full needs to be defined. This
number sets number of incremental backups to be run, before a full backup
is performed.
Backup server rsync and non-default backup directory:
xtrabackup:
server:
enabled: true
hours_before_full: 48
full_backups_to_keep: 5
key:
xtrabackup_pub_key:
enabled: true
key: key
backup_dir: /srv/backup
Note
The hours_before_full parameter should have the same value as
is stated on xtrabackup client
Note
If the backup_dir argument is ommited backups will be made to
default location, same on both client and server side.
Backup server without strict client restriction:
xtrabackup:
server:
restrict_clients: false
Backup server at exact times:
xtrabackup:
server:
enabled: true
full_backups_to_keep: 3
incr_before_full: 3
backup_dir: /srv/backup
backup_times:
day_of_week: 0
hour: 4
minute: 52
key:
xtrabackup_pub_key:
enabled: true
key: key
Note
Parameters in backup_times section can be used to set up exact
time the cron job should be executed. In this example, the backup job
would be executed every Sunday at 4:52 AM. If any of the individual
backup_times parameters is not defined, the defalut * value will
be used. For example, if minute parameter is *, it will run the backup
every minute, which is ususally not desired.
See the crontab reference for further info on how to set these parameters.
Note
Please be aware that only backup_times section OR
hours_before_full(incr) can be defined. If both are defined. The
backup_times section will be peferred.
Note
New parameter incr_before_full needs to be defined. This
number sets number of incremental backups to be run, before a full backup
is performed.
Client restore from local backups:
xtrabackup:
client:
enabled: true
full_backups_to_keep: 5
hours_before_full: 48
hours_before_incr: 12
restore_full_latest: 1
restore_from: local
compression: true
compressThreads: 2
database:
user: username
password: password
target:
host: cfg01
qpress:
source: tar
name: url
Note
restore_full_latest param with a value of 1 means to restore db from the last full backup and its increments. 2 would mean to restore second latest full backup and its increments
Client restore from remote backups:
xtrabackup:
client:
enabled: true
full_backups_to_keep: 5
hours_before_full: 48
hours_before_incr: 12
restore_full_latest: 1
restore_from: remote
compression: true
compressThreads: 2
database:
user: username
password: password
target:
host: cfg01
qpress:
source: tar
name: url
Note
The restore_full_latest parameter with a value of 1
means to restore db from the last full backup and its increments.
2 would mean to restore second latest full backup and its increments