MYSQLBACKUP(1) User Commands MYSQLBACKUP(1)NAMEmysqlbackup - creates MySQL backups on a periodic basis
SYNOPSISmysqlbackup [OPTIONS] [database [database [ ... ] ]]
DESCRIPTIONmysqlbackup 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/back‐
ups (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, mysql‐
backup create plain SQL dump for whole database and call com‐
pressor 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/mysql‐
backup.%%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 envi‐
ronment to "no".
EXAMPLESmysqlbackup
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 com‐
pressed.
mysqlbackup-a -P ask
You are prompted for password to backup all the databases avail‐
able under current user.
mysqlbackup-aS
Operate in slave mode. Save SLAVE STATUS for further replica‐
tion restore.
USAGEmysqlbackup 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 peri‐
odic(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 men‐
tioned 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_sched‐
ule. 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 back‐
ups 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 mysql‐
backup_enable to `yes' in either /etc/periodic.conf.local or /etc/peri‐
odic.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>
SEE ALSOmysqldump(1), mysqlcheck(1), mysql(1), periodic.conf(5), periodic(8)mysqlbackup 2.8 May 2014 MYSQLBACKUP(1)