A MySQL physical backup is a regular file archive. Backups are created in Binary format.
Requirements:
Here is an example of configs for creating a backup of all databases on MySQL server with datadir in /var/lib/mysql with exclude of “test” table of “test_db” databases.
This job config defines rules to make database backups on mysql server running on localhost:
job_name: mysql_xtrabackup
type: mysql_xtrabackup
tmp_dir: /var/backup/dump_tmp
safety_backup: false
deferred_copying: false
sources:
- name: mysql_xtrabackup
connect:
db_host: localhost
db_port: "3306"
db_user: root
db_password: rootP@5s
excludes:
- test_db.test
gzip: true
is_slave: true
db_extra_keys: "--datadir=/var/lib/mysql"
prepare_xtrabackup: true
storages_options:
- storage_name: local
backup_path: /var/backup/dump
retention:
days: 7
weeks: 4
months: 3
If you want to use remote storage to copy your backups for long storage, you should use the corresponding storage name and its retention parameters
Reference for mysql_xtrabackup.conf:
# Job unique name
job_name: string # Required
# Type of the backup job. `mysql_xtrabackup`
type: string # Required
# Path to the directory used to temporarily store the backup during the creation process
tmp_dir: string # Required
# The option defines the order of rotation (deletion of old) backups, before creating a new copy or afterwards
safety_backup: bool # Default: false
# The option allows you to postpone copying until all backup parts of the job are complete before its delivery
deferred_copying: bool # Default: false
# Contains a list of individual subtasks for creating backups
sources: list
# Unique subtask name, used to separate storage paths
- name: string # Required
# Defines a set of parameters for connecting to the database. You may use either auth_file or db_host,db_port or socket options. Options priority follows: auth_file → db_host → socket
connect: map
# Path to MySQL auth file
mysql_auth_file: string # Optional
# DB host address
db_host: string # Optional
# DB port
db_port: string # Optional
# DB user
db_user: string # Optional
# DB password
db_password: string # Optional
# Path to DB socket
socket: string # Optional
# List of databases/tables to be excluded from backup
excludes: list
# Entry to exclude from the backup.
- string # Optional
# The option enables compression of the backup archive
gzip: bool # Default: false
# The option stops replication while collecting backups
is_slave: bool # Default: false
# Special parameters for collecting database backups. Will be passed as arguments to the `mysqldump` command
db_extra_keys: string # Optional
# Whether you need to make an xtrabackup prepare.
prepare_xtrabackup: bool # Default: false
# List of storages to which backups should be delivered and their rotation rules
storages_options: list
# The name of the storage added to the main config. The name `local` is used to store a copy on host.
- storage_name: string # Reuired
# Path to directory where backups will be stored
backup_path: string # Reuired
# Set of rules to store and rotate the backups.
retention: map
# Use backup retention count instead of retention period.
count_instead_of_period: bool # Default: false
# Count of days/daily copies to store backups. Multiple copies can be created in one day.
days: numeric # Default: 7
# Count of weeks/weekly copies to store backups created on sunday.
weeks: numeric # Default: 5
# Count of months/montly copies to store backups created on the first day of month.
months: numeric # Default: 12