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

[Phil Marsh]

Hi all,
Thanks for your help with this! I found that as you said, I need to use the InnoDB storage engine to get the foreign keys into the databases themselves as the MyISAM storage engine does not directly support foreign keys. Therefore, exported MyISAM database tables will not reflect the relationships in the database as seen in the phpmyadmin GUI. 
However, the thing that made this deceptive is that phpmyadmin has provisions for "artificially" supplying relationship capability while using even the MyISAM storage engine for tables in databases. Apparently one can use phpmyadmin + MySQL with MyISAM to define relationships between table columns! HOWEVER, when using the above, the MyISAM engine DOES NOT actually store the relationships in the database as real foreign keys because it does NOT have this capability.  Therefore, exporting such a database with MyISAM tables cannot save the relationships!
So how does phpmyadmin allow relationships between table columns while using MyISAM?
As far as I can tell when using tables with the MyISAM engine, phpmyadmin stores the relations in a special database that's specified in its configuration file namely the pmadb database.
Therefore, if one wants to transfer their databases to another server (call is server B) from server A, they can export all the databases they want to transfer INCLUDING the pmadb database (its name is specified in the phpmyadmin config file). Next, they should blow away the pmadb database on server B, (you will get an error message), then import all the previously exported database to server B. The last step should be to import the pmadb database to server B that was exported from server A. 
When databases are backed up via exporting them, one should ALWAYS export the pmadb database as well because it contains the relationship information for all their databases when using the MyISAM engine.
The pmadb database also apparently contains the saved MIME and LINK information that produces clickable links in the phpmyadmin GUI for the database data (so you can open files via clicking links in table columns).
When one imports a backed-up database, I found that in order to recover the relationships, I had to follow this import with an import of the pmadb database as well.

The above apply when using the MyISAM engine. If one instead uses the InnoDB engine, the relationships are then preserved in the exported files and not in the pmadb database.
However, it appears that the MIME and LINK information (properties of columns that allow clickable links to files and websites within tables) still do not get preserved in the exported backup files. Therefore, it appears that even if one uses the InnoDB engine, they still will want to backup and restore phpmyadmin's pmadb database when backing up and restoring.
I find this a bit spooky and it appears this could lead to problems. Therefore, if one wants to use phpmyadmin to administer and as the GUI for their databases, then it seems really advisable to back up and restore all the databases including pmadb together to preserve consistency.
Also, while choosing the InnoDB engine would appear to solve the consistency problem, I suspect it would not preserve the link properties of the databases on restore.
Please comment.
Thanks,
Phil

Sent from my iPad

On Aug 8, 2013, at 5:29 PM, Brian Grossman <brian-nclug at admin.softhome.net> wrote:

> So your link tables are missing?  (http://en.wikipedia.org/wiki/Junction_table)
> 
> Or do you mean foreign keys? (http://en.wikipedia.org/wiki/Foreign_key)
> 
> Does it work if you use mysqldump directly?  If not, what options did you use?
> 
> Brian
> 
> On Thu, 8 Aug 2013 16:01:07 -0700 (PDT)
> phil marsh <montanaembassy at yahoo.com> 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. 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.
>> 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. Thanks, Phil
>> 
>> 
>> 
>> 
>> ________________________________
>> From: Stephen Warren <swarren at wwwdotorg.org>
>> To: Northern Colorado Linux Users Group <nclug at lists.nclug.org> 
>> Cc: phil marsh <montanaembassy at yahoo.com> 
>> Sent: Thursday, August 8, 2013 4:50 PM
>> Subject: Re: [NCLUG] mysql: backing up database - cannot save
>> relationships 
>> 
>> On 08/08/2013 03:59 PM, phil marsh wrote:
>>> Hi all,
>>> I was wondering if any of you saw this issue and could kindly give me
>>> your opinion? When I attempt to backup my mysql databases from
>>> phpmyadmin or mysql directly via export (SQL data) then import them,
>>> the imported database lacks the relationships and also the links.
>> 
>> What exactly are "relationships" and "links". Are they simply tables
>> that contain the keys of other tables? Do those tables appear in the
>> dump itself?
>> _______________________________________________
>> 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