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

[Stephen Warren]

On 08/08/2013 05:01 PM, phil marsh wrote:
> Hi Stephen,
> Some of the tables contain links to files on disk.
> Relationships are database structures that link one table to another in the database.

It's been a while since I've done much DB stuff, but I don't believe
there is actually a specific "relationship" object in a database; it's
more a facet of the fact that rows in one table contain the key for
another table. Hence, dumping/restoring the table content should be enough.

I wonder if some of the columns in your DB are auto-increment variables,
and the restore process is not forcing those column values, but instead
is assigning new IDs as if new inserts were being performed? It would be
instructive to:

* dump the DB
* restore the DB
* dump the DB
* diff the two dump files

Finally, if you could post the concrete SQL schema and/or dump file for
your tables, that would help greatly. (Preferably a small example DB
that demonstrates the problem rather than a real, large, DB). Also,
double check your DB schema against the dump file, and manually search
for anything that's missing.