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

[Bob Proulx]

phil marsh wrote:
> Looking into this last night, it appears that you're right Chris,
> when using the MyISAM engine, phpmyadmin appears to store all the
> database's relations in a special database, the pmadb specified by
> the configuration command $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
> where the rest is specifed via the /etc/phpmyadmin/config.inc.php
> entries below:
> $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
> $cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
> $cfg['Servers'][$i]['relation'] = 'pma_relation';
> $cfg['Servers'][$i]['table_info'] = 'pma_table_info';
> $cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
> $cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
> $cfg['Servers'][$i]['column_info'] = 'pma_column_info';
> $cfg['Servers'][$i]['history'] = 'pma_history';
> $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
> $cfg['Servers'][$i]['tracking'] = 'pma_tracking';

But aren't all of those related to the phpmyadmin interface?  None of
that has anything to do with your application database.  For example
none of the above would even exist on the machine if did not install,
or removed, phpmyadmin.  Therefore they won't have any affect on your
application data.

> However, I noticed that even if database tables were changed to use
> the InnoDB engine and the relationships set up via phpmyadmin,
> phpmyadmin still did not save the relationships in the database
> export file.

It would be great if you could create a small example with one or two
entries in a table to illustrate the problem you are seeing.  Until
then I know that I for one am still confused by what problem you are
having.  It doesn't make sense to me yet.

> I suspect that besides the above, I also need to change the default
> storage engine in MySQL to InnoDB. Any other advice is most welcome.

Using InnoDB is good for many reasons even if it isn't related to your
current problem.  Because InnoDB is ACID compliant.  A lot of things
will "just work" with InnoDB but not with MyIASM.  But I don't think
that is related to your current issue.

Bob