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

[phil marsh]

Hi Bob,
I've tried 

mysqldump -u montanaviking -pmypassword library > library.sql (in Ubuntu 10.04)

note: the database name is library.
And to restore it, I dropped the old library database then generated a new blank database called library.
Next, I transferred the library.sql file as was generated from the export above to another machine (ubuntu 12.04) then executed 

mysql -u montanaviking -pmypassword  library < library.sql (run on the ubuntu 12.04 machine's command line).
The resulting library database, on the Ubuntu 12.04 machine, still had no relationships between the tables (as it originally did prior to export from the 10.04 machine) and also the links were lost from the table structures.
Thanks so much,
Phil



________________________________
 From: Bob Proulx <bob at proulx.com>
To: nclug at lists.nclug.org 
Sent: Thursday, August 8, 2013 5:20 PM
Subject: Re: [NCLUG] mysql: backing up database - cannot save relationships
 

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
_______________________________________________
NCLUG mailing list      NCLUG at lists.nclug.org

To unsubscribe, subscribe, or modify
your settings, go to:
http://lists.nclug.org/mailman/listinfo/nclug