Backing Up and Restoring Databases - Learning MySQL and MariaDB - page 345

problem. It also doesn't address older backup files. A good script could remove the older
dump files after a certain amount of time. Of course, having an automated script delete
files can be a little disturbing. This script is provided only to give you an idea and starting
point for constructing your own backup scripts. The ones that you create and use should
be much more complex and allow for many possibilities, handle errors, and provide some
sort ofreporting.
Backing Up Specific Tables
For very large andactive databases, you may want to back up the data for individual
tables rather than the whole database. You could back up the entire database weekly, per-
haps and then do daily backups for tables whose data changes often. For most databases,
developing a strategy like this can be prudent.
Take our two databases. The data in the rookery tables will rarely change: new species
of birds aren't discovered daily, and bird families and orders are rarely changed. Once we
have all of the details for each bird in each table entered, there will hardly be any changes.
Conversely, if our site is very active, almost all of the tables in the birdwatchers data-
base will have new rows and changes frequently, so we would want to back up all of its
tables every day. A reasonable strategy, then, is to back up the whole rookery database
once a week and all of the birdwatchers database each day.
Still, suppose our boss is overly concerned about losing any data entered by our members.
Suppose he insists that we make a backup of the humans table twice a day, once at noon
and again at midnight. We could write a shell script like the one in previous section to
vary the filenames to include the date and just add a bit more to indicate the time during
the day when the dump was made (e.g., birdwatchers-humans-2014-09-14-midday.sql and
birdwatchers-humans-2014-09-14-midnight.sql ). The only other change is to create a
mysqldump command to back up just one table, humans . Try executing the following on
your server from the command line:
mysqldump --user= admin_backup --password --lock-tables \
--databases birdwatchers --tables humans >
birdwatchers-humans.sql
This is similar to the previous examples, but with the additionof the --tables option
followed by the table name. If you want to make a backup for more than one table in the
same database, you would just list them after the --tables option, each table name sep-
arated by a space. But this example is wordier than necessary. Because we're backing up
tables in only one database, we don'tneed the --databases option. We also don't need