Database Reference
In-Depth Information
Creating Backup Scripts
To automate manyaspects of making backups of databases, it's useful to create a set of
scripts that will execute the
mysqldump
for the databases you want with the settings that
you prefer. It's not too difficult to do this. You don't need to be very advanced in program-
ming if you want to do only a few simple things, such as varying the output slightly each
time.
Let's use the problem presented at the end of the previous section for an example back-up
script. The solution is to change the name of the dump file each day to include the current
date so that there will a unique dump file for each day. Here's an example of a very simple
shell script that may be run on a Linux or Mac system to do this:
#!/bin/sh
my_user
=
'admin_back'
my_pwd
=
'my_silly_password'
db1
=
'rookery'
db2
=
'birdwatchers'
date_today
=
$
(
date
+%
Y
-%
m
-%
d
)
backup_dir
=
'/data/backup/'
dump_file
=
$
db1
-
$
db2
-
$
date_today
'.sql'
/
usr
/
bin
/
mysqldump
--
user
=
$
my_usr
--
password
=
$
my_pwd
--
lock
-
tables
\
--
databases
$
db1
$
db2
>
$
backup_dir
$
dump_file
exit
This script will execute the
mysqldump
with the same options as in our previous ex-
ample. It starts by setting variables with the username, password, and the names of the
databases. It then uses the
date
command to get the numerical values for the year,
month, and day and saves them with dashes in another variable (
date_today
). It uses
the variables for the database names (i.e.,
$db1
and
$db2
), combined with
$d-
ate_today
to assemble the name of the dump file (e.g.,
rookery-birdwatch-
ers-2014-10-25.sql
). All of these variables are then used in the
mysqldump
command.
Because the username and password are included in the script, it can be run automatically
and daily by
cron
without user intervention. It will create a dump file with a new name
every day. This script is by no means flawless and definitely not in good form. It doesn't
allow for errors. If the backup fails, it doesn't notify the administrator that there was a
