Skip to content
Backup MySQL databases.
Pull request Compare This branch is 41 commits behind renard:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.
README.asciidoc
default.cnf
mysql-backup
mysql-backup.1
mysql-backup.1.txt
mysql.example.com.cnf

README.asciidoc

mysql-backup(1)

NAME

mysql-backup - Backup MySQL databases.

SYNOPSIS

mysql-backup [ options ] command [ command options ]

DESCRIPTION

mysql-backup performs daily, weekly and monthly backup of MySQL databases. Features:

  • Backup multiple databases

  • Compress backup files

  • Backup remote servers

  • Rotate backups

  • Perform restoration

  • more…​

ARGUMENTS

Options

-c CONFIG , --config CONFIG

A configuration file to use instead of default one. This option allows different configuration for different MySQL servers.

-v, verbose

Run in verbose mode.

-h, --help

Show a short help screen.

Commands

There are only three commands:

dump

Perform a mysqldump backup.

restore

Performs the restoration of a previously done mysqldump backup.

Note
To restore a LVM snapshot you just need to untar the archive.
snapshot

Perform a LVM snapshot backup.

Command options

Each command has its own set of options.

dump options

--dry-run

Run backup in a dry-run mode: nothing would be done for real. Commands to run are displayed on the screen.

Restore options

--dry-run

Run restore in a dry-run mode: nothing would be done for real. Commands to run are displayed on the screen.

--date DATE

Restore backups for given date. If this option is not set, the last found backup would be restored.

--bases BASES

Restore backups for given set of databases. If this option is not set all found databases would be restored.

snapshot options

--dry-run

Run backup in a dry-run mode: nothing would be done for real. Commands to run are displayed on the screen.

--no-archive

Simply do the snapshot and mount the LVM volume. The user is responsible of the snapshot umount an deletion.

CONFIGURATION FILE

Default configuration

mysql-backup looks for a default configuration file in that order:

  • '/etc/mysql-backup/default.cnf'

  • '~/.mysql-backup.cnf'

  • './.mysql-backup.cnf'

If no default configuration file is found, default hard-coded values would be used.

Specific configuration file

In addition of the default configuration, a specific configuration file can be used (with the --conf option).

General options

BACKUP_DIR

Where the backups files would be stored. A generic backup file schema is:

<BACKUP_DIR>/<host>/<daily|weekly|monthly>/<base>_YYYY-MM-DD.sql[.<COMPRESSION_EXTENSION>]

MySQL options

DATABASES

Databases to backup. If this value is set to ALL then all databases would be backuped. mysql-backup would determine the database using the "SHOW DATABASES" query.

IGNORE_DATABASES

A list of database to ignore during backup. By default, information_schema is ignored.

In addition to that option all mysql(1) and mysqldump(1) options are also recognized as long as dash (-) are changed to underscore (_).

Note
However some options are not recognized: help, pipe, table, version, databases, ignore-table, ssl*, execute.

EXAMPLE: This is the default MySQL configuration:

batch=1
skip_column_names=1
quote_names=1
opt=1
add_drop_database=1

Archive options

COMPRESSION

The tool to use for compression. Currently gzip, bzip2 and xz are recognized. If compression if not known then no compression would be used.

Note
gzip generates bigger files than the others but needs less CPU time.
COMPRESSION_OPTS

Options to pass to the compression tool.

DAILY_RETENTION

How many days a daily backup should be kept. By default daily archives are kept 7 days.

WEEKLY_RETENTION

How many days a weekly backup should be kept. By default weekly archives are kept 35 days (5 weeks).

MONTHLY_RETENTION

How many days a monthly backup should be kept. By default monthly archives are kept 365 days (12 months).

WEEKLY_DAY

Which day weekly backup are done (0..6, 0 is Sunday).

MONTHLY_DAY

Which day monthly backup are done (00..31).

HOST

Name of the host to backup for logging purposes.

Note
This is not the mysql host to backup (use "host" in lowercase for that).

LVM Options

LVM_EXT

Extention for the LVM snapshot (Default: "_bkp") that would be added to the current LVM volumen name.

LVCREATE_OPTS

Options to pass to lvcreate(1) when doing LVM snapshot (Default: "--chunksize=256").

LVREMOVE_OPTS

Options to pass to lvremove(1) when purging a snapshot (Default: "-f").

TARGET_MOUNT

Where to mount the LVM snapshot before archiving the data (Default: "/tmp/mysql-snapshot").

Hooks

Hooks are scripts that can be run via run-parts(8). Each hook parameter consists of a directory path suitable for run-parts(8).

See run-parts(8) for further information on how hooks are run.

See HOOK DETAILS section for details.

ARCHIVE PROCEDURE

Every day backups are done in the daily directory. On WEEKLY_DAY the daily backup is hard linked to the weekly directory (the same is done for monthly backups on MONTHLY_DAY and monthly directory).

After that archives older that DAILY_RETENTION, WEEKLY_RETENTION and MONTHLY_RETENTION are removed from their specific directories.

This system keeps space on the backup server by the use of hard links.

Note
This only woks if all backups are in a single partition.

HOOKS DETAILS

Dump hooks

pre_dump_backup_hook

Hook to be run before the dump backup process really starts.

post_dump_backup_hook

Hook to be run after the dump backup process is done.

pre_dump_restore_hook

Hook to be run before the dump restore process really starts.

post_dump_restore_hook

Hook to be run after the dump restore process is done.

Note
In addition hook names could be postfixed with a database name. This means a hook could be defined for a specific database.

EXAMPLE: post_dump_backup_hook_a_database is ran before a_database would be backuped.

Snapshot hooks

pre_snapshot_backup_hook

Hook to be run before a snapshot really stats.

post_snapshot_backup_hook

Hook to be run when a snapshot is done.

pre_snapshot_backup_lvm_snaphost_hook

Hook to be run before the LVM snapshot is started.

post_snapshot_backup_lvm_snaphost_hook

Hook to be run after the LVM snapshot is done.

pre_snapshot_backup_archive_hook

Hook to be run before the archive process is started.

post_snapshot_backup_archive_hook

Hook to be run after the archive process is done.

Note
There is no batabase postfix for snapshot hooks since there would be nonsense.

SEE ALSO

  • mysql(1)

  • mysqldump(1)

  • gzip(1), bzip2(1), xz(1)

  • run-parts(8)

HISTORY

Version 1.9

  • Add snapshot option

  • Add LVCREATE_OPTS

Version 1.0

2010-09-06

First release.

BUGS

No time to include bugs, command actions might seldom lead astray user’s assumption.

AUTHORS

mysql-backup is written by Sébastien Gross <seb•ɑƬ•chezwam•ɖɵʈ•org>.

Copyright © 2010-2012 Sébastien Gross <seb•ɑƬ•chezwam•ɖɵʈ•org>.

Released under GNU GPL version 3 or higher (http://www.gnu.org/licenses/gpl.html).

Something went wrong with that request. Please try again.