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

Bob Proulx bob at proulx.com
Thu Aug 8 17:20:08 MDT 2013


phil marsh wrote:
> Some of the tables contain links to files on disk.

Are you saying that the file names are changing?  That doesn't make sense.

> Relationships are database structures that link one table to another
> in the database. Say if one table contains a person's name, and
> another table in the database also contains the person's name with
> other columns containing personal information, a relationship will
> link the two columns containing the person's name in the two tables
> of the database.

Normally that will simply be the id field for that entry.  A persons
table would have an id field.  Another table would reference it with a
person_id field.  If the id were 42 then the integer 42 would be in
that person_id field.

Are you saying that the id field numbers are being changed?  That
would definitely break the relationship between objects!

That makes me think that when you did the import that you did not
import into an empty table and the data appended to it and all of the
ids were given new id numbers instead of restoring the previous
numbers.  Just guessing here though.

> These above two structures are apparently not being saved in the
> export database backups I'm trying to make. My Google efforts have
> been fruitless and it's almost as if nobody else has seen this, so
> my error is likely obvious and/or simple but I just can't find it.

What command are you using to dump the database?  And what are you
using to restore?

  mysqldump --defaults-file=/etc/mysql/debian.cnf foo | gzip > foo.sql.gz

Although normally I use --all-databases --events to ensure I get
everything and can't forget to back up a new db.  But then doing a
single db restore is more complicated.

Also MySQL keeps a special database "mysql" for holding account access
and other related things.  In addition to your normal database you
will also want to dump the mysql database too.  You can almost always
recreate this with some work so it isn't as critical.  But if you are
doing backup then might as well grab it too.

  mysqldump --defaults-file=/etc/mysql/debian.cnf mysql | gzip > mysql.sql.gz

Bob


More information about the NCLUG mailing list