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

[Jared Hall]

What is the output of:

SHOW TABLE STATUS FROM database WHERE name='table'\G;

For the given tables on both machines?

Jared
 
On 08/08/2013 04:20 PM, phil marsh wrote:
> Could it be that the relationships are not stored in each database? Does that mean I need to export and import something else too?
> Basically, I'm attempting to transfer all my databases from my Ubuntu 10.04 machine to my Ubuntu 12.04 machine so I can use them while I rebuild my Ubuntu 10.04 machine.
> Thanks,
> 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
> _______________________________________________
> NCLUG mailing list       NCLUG at lists.nclug.org
>
> To unsubscribe, subscribe, or modify
> your settings, go to:
> http://lists.nclug.org/mailman/listinfo/nclug