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

[phil marsh]

Hi Jared and Bob,
I also notice that in the exported database file (.sql format) that there are no foreign keys specified. Pardon my ignorance but aren't the relations specified via foreign keys?
If so, does that mean I need to be using the InnoDB engine instead of the default MyISAM engine?
If the above are true, then how does mysql even use relationships with the MyISAM engine in phpmyadmin (front end for MySQL) (which I'm doing quite well)?
Thanks so much,
Phil




________________________________
 From: Jared Hall <jrhall at gmail.com>
To: nclug at lists.nclug.org 
Sent: Thursday, August 8, 2013 6:40 PM
Subject: Re: [NCLUG] mysql: backing up database - cannot save relationships
 

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

_______________________________________________
NCLUG mailing list      NCLUG at lists.nclug.org

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