[NCLUG] mysql: backing up database - cannot save relationships

[Bob Proulx]

Jesse Griffin wrote:
> If you are trying to backup all the databases, then I would use the
> --all-databases (-A) option.  Try something like this:
> 
>     mysqldump -u $USER -p$PASS --opt -A -E >mysql-dump.sql
> 
> Then bzip it if space is an issue: `bzip2 mysql-dump.sql`

I always put in a nightly dump script so that the database can be
backed up by the normal backup system.

  # cat /etc/cron.d/local-mysql 
  30 3 * * *       root umask 077 ; mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases --events | gzip > /var/backups/mysql.dump ; savelog -q -d -l -C /var/backups/mysql.dump

That will be a little hard to visualize.  It results in the following
files being produced.

  $ ls -l /var/backups
  -rw------- 1 root root    140079 Aug  3 03:30 mysql.dump.20130803033006
  -rw------- 1 root root    140080 Aug  4 03:30 mysql.dump.20130804033002
  -rw------- 1 root root    140080 Aug  5 03:30 mysql.dump.20130805033002
  -rw------- 1 root root    140080 Aug  6 03:30 mysql.dump.20130806033003
  -rw------- 1 root root    140080 Aug  7 03:30 mysql.dump.20130807033002
  -rw------- 1 root root    140079 Aug  8 03:30 mysql.dump.20130808033002
  -rw------- 1 root root    140080 Aug  9 03:30 mysql.dump.20130809033002

Upon a restoration I must remember that the mysql database is part of
it.  If I only want to restore one db then I probably don't want to
restore the entire file including other databases for other projects.
I would restore to a scratch system first, then extract just the db I
wanted, then restore just that one db.  But I backup all of them then
I can't forget to back up a new db that way.

Bob