Learning Mysql

.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 command at 4:45 AM. every Sunday, you can add the line 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 bypassing the mysqldump output to the gzip compression program 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:

Download 4.24 Mb.

Share with your friends: