NAME

mysqlbackup - creates MySQL backups on a periodic basis

SYNOPSIS

mysqlbackup [OPTIONS] [database [database [ ... ] ]]

DESCRIPTION

mysqlbackup meant to create MySQL databases backup on a periodic basis.

OPTIONS

-a: Dump all available databases except "information_schema" and "performance_schema" databases.
-u user: The MySQL user name to use when connecting to the server.
-h host: Connect to host.
-p password: Password to use when connecting to server. You should note that specifying a password on the command line should be considered insecure. See the section named "SECURITY".
-P filename|ask: Read the clear password from the file. The file must normally not be readable by "others" and must contain exactly one line. Password will be prompted from the command line if the special keyword "ask" specified here.
-x login-path: MySQL login-path
-o option|no: Additional mysqldump option. To specify multiple options you should repeat this key for each mysqldump-option. The default options are: --opt --skip-lock-tables --quote-names. To not use the default options force "no" option.
-l days: Keep created backups for the specified number of the days. The default is 5 days.
-d directory: Target directory to archive backups. The default is /var/backups (will be created if need).
-z xz|pbzip2|bzip2|gzip|7z|no: Compress dumps with specified program. Unless explicitly set or "no" keyword used, the compressor is selected in the next order: if xz(1) compressor found in $PATH, it will be used. If it not found, bzip2(1), gzip(1) and 7z(1) programs will be searched and used if found. If none found, plain dumps will be created.
-Z: Pipeline mysqldump to compressor program. By default, mysqlbackup create plain SQL dump for whole database and call compressor program afterwards. This help to make MySQL locktime as small as possible. If long locktime for huge databases is not a problem but filesystem space usage is - use this key to save disk space.
-F mode: Create files with given mode access permissions. The default mode is 0600.
-D mode: Create directories with given mode access permissions. The default mode is 0700.
-m path|yes|no: Save my.cnf config or specify it alternate path. Default is: yes, %%DATADIR%%my.cnf.
-C yes|no|keys: Check tables before doing backup or use specified keys for mysqlcheck(1) program while perfoming check. Default: yes, keys: --auto-repair --check-only-changed --extended --silent.
-O yes|no|keys: Optimize tables before doing backup or use specified keys for mysqlcheck(1) program while perfoming optimization. Note that not all table engines supports table optimization. Please refer to "OPTIMIZE TABLE Syntax" paragraph of MySQL documentation. Default: yes, keys: --optimize --silent.
-L lockfile: Alternate default path to lockfile (/var/tmp/mysqlbackup.%%UID%%.lock).
-t seconds: Timeout in seconds to expire existing lockfile. By default lockfile expires after 90000 seconds.
-S: Slave mode. Under this mode mysqlbackup assumes it is running on MySQL slave. Then, prior to his work, mysqlbackup stops the slave and saves "SHOW SLAVE STATUS" output. After work is done, the slave is started up. The output is saved to "slave-status" file.
-I: Ignore errors while dumping database. mysqlbackup will not stop if mysqldump(1) running on any database will return an error. Excludes -Z because there is no way to detect which program has failed.
-v: Be verbose.
-V: Print version and exit.
-H: Print this help and exit.

ENVIRONMENT

MYSQL_PREFIX environment variable specifies your MySQL installation prefix. All used MySQL binaries (mysqldump, mysql, mysqlcheck) will be searched within this prefix at `bin' directory.

DEBUG_IDENT environment variable controls log messages appearance. In verbose mode, by default, log messages from mysqlbackup prefixed with timestamp, process name and PID. When logging to syslog this may not be desired. To disable prefix in log messages, set DEBUG_IDENT environment to "no".

EXAMPLES

mysqlbackup: Do nothing, print help.
mysqlbackup -av: Verbose backup all the accessible databases on the local MySQL server.
mysqlbackup -z no mysql: Backup MySQL system database without output dump being compressed.
mysqlbackup -a -P ask: You are prompted for password to backup all the databases available under current user.
mysqlbackup -aS: Operate in slave mode. Save SLAVE STATUS for further replication restore.

USAGE

mysqlbackup exits on error in mysqldump(1) execution unless -I option given. On exit forced by some error, mysqlbackup clean up its workdir and backup does not created. You may want to track mysqlbackup's errors on a scheduled jobs to avoid the situation when no backups created at all when something prevents mysqlbackup from normal execution. Check .done flag-file within destination directory. Once backup succeeded, this file is created and timestamp is written to it.

mysqlbackup package comes with a number of periodic(8) scripts allowing to create periodic backups even more transparently. There is number of options suitable for periodic.conf(5) that can be used to provide a more accurate configuration to run mysqlbackup:

mysqlbackup_enable: (bool) enables or disables mysqlbackup tool to run with periodic(8). The default is "no". If enabled, mysqlbackup will run by a daily schedule.
mysqlbackup_schedule: (str) run mysqlbackup either daily or weekly or monthly. As mentioned above - the default schedule is daily.
mysqlbackup_args: (str) pass to mysqlbackup program additional arguments. By default they are set to -av, which means to backup all the available databases. Note that this variable automatically adjusted with -l option based upon selected mysqlbackup_schedule. With weekly schedule backups are stored for 30 days; that is, there will be at least four last backups created on every Saturday, for example. As for monthly schedule, backups stays untouched for 120 days, allowing to have minimum four last backups created on the every first day (also, as example) of last months.

If you need to create backups with a bit different schedule - just add command with desired arguments and flags to your crontab(1) to create regular MySQL backups. In order to use mysqlbackup as periodic job - do not forget to allow periodic(8) to run mysqlbackup by setting mysqlbackup_enable to `yes' in either /etc/periodic.conf.local or /etc/periodic.conf file. Check periodic.conf(5) for details.

SECURITY

You should keep your password in secure.

The method of specifying your MySQL password with -p option must be considered extremely insecure and should not be used. Your password becomes visible to system status programs such as ps(1) that may be invoked by other users to display command lines.

Store your password in an option file. For example, you can list your password in the [client] section of the .my.cnf file in your home directory:

: [client] password=your_pass

To keep the password safe, the file should not be accessible to anyone but yourself. To ensure this, set the file access mode to 400 or 600.

Users of MySQL 5.6+ may use "login path" feature with -x option.

AVAILABILITY

The latest version of this distribution is available on-line from:

https://mysqlbackup.googlecode.com

AUTHOR

Written by Alexey Degtyarev

REPORTING BUGS

Report bugs to <alexey@renatasystems.org>