Databases Reference
In-Depth Information
.murphys-laws.com
)
. In this section, we'll describe how you can configure automatic,
regular backups using
mysqldump
; you can also use
mysqlhotcopy
if you wish.
Linux and Mac OS X
Under Linux and Mac OS X, you can list the commands you want to be executed in a
crontab
file; commands in the
crontab
file are run at the times you specify. First, you
have to edit a
crontab
file:
$
crontab -e
This opens the crontab file for the current user for editing; the default editor on most
systems is
vi
. If you're not comfortable with this editor, you can specify your preferred
editor by setting the
EDITOR
variable to the name of your favorite editor. For example,
many novice users find the
pico
editor somewhat easier to use:
$
export EDITOR=
pico
$
crontab -e
The general format of a
crontab
entry is:
MINUTE HOUR DAY MONTH DAYOFTHEWEEK COMMAND
If you want a dump to be created from a particular database using the
mysqldump
com-
mand at 4:45 A.M. every Sunday, you can add the line:
45 4 * * sun /usr/local/mysql/bin/mysqldump \
--user=root \
--password=
the_mysql_root_password
\
--result-file=
path_to_backup_file
\
database_to_dump
Note that each entry must be on one line, and you must specify full paths to executables;
the cron program might not inherit your path settings.
SQL files have a lot of repeating information that can be highly compressed. You can
create compressed SQL files by passing the
mysqldump
output to the
gzip
compression
program:
45 4 * * sun /usr/local/mysql/bin/mysqldump \
--user=root \
--password=
the_mysql_root_password
\
database_to_dump
\
| gzip --best --to-stdout \
>
dump_directory
/`date +"%Y.%m.%d.%H.%M"`.MySQL_Backup.sql.gz
Here, we've left out the
result-file
option so that the
mysqldump
output is passed
directly to the standard output (normally the screen), rather than to a file. The pipe
symbol (
|
) then sends this output to the
gzip
compression program. The
best
option
tells
gzip
to compress the data as much as possible, while the
to-stdout
option tells
gzip
to pass its own output to the standard output. Finally, the greater-than symbol
(
>
) redirects this compressed data into a file. We've included the string:
