@Mike Levin, Phorum software has usability issues with long threads so I've continued our thread here.
> why wouldn't a complete disk clone be an adequate backup?
See Phil's excellently pithy answer.
> What is an adequate backup solution for a MySQL database?
As you're finding for many a relational database question, the answer is a long read:
https://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html
The first error message you posted from the mysql errorlog said "Can't open the mysql.plugin table. Please run mysql_upgrade to create it." The two most likely causes of that error would have been (i) a version upgrade did not complete, (ii) the mysql.plugin table was corrupted or deleted. (Restoring from a Time Machine backup would be one way it one of its files could have been corrupted.) You can shed light on corruption by looking for plugin.* files in a datadir. But you've cited
two MySql datadirs ...
/usr/local/mysql/data
/Volumes/ScratchDisk2/MySQLdata/
Let's see full listings for both those folders.
> MailStewardPro, or SequelPro front ends, both log in fine
> as root now, and see no data.
In any db? Do they see no data in the user table in the mysql database?
Quote
SequelPro sees the emaildbase database and says it's 0 rows. The directory structure on disk where the database is kept is showing 171GB so I believe the data are still there.
> it was using the root user and its password, to access the database.
Yikes, terrible idea. Root user has all privs including nuking the whole shebang. An app should be accessible only to users with the minimum privs required for that app to function. If MailStewardPro requires the root user, it's not to be trusted.
Quote
% more /usr/local/etc/my.cnf
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
max_allowed_packet=200M
does this mean that by default it's not logging anything?
Yes, if that's the datadr in use bt see above.
Quote
... because I wasn't running MailStewardPro during the time that the problem happened, they are basically saying they can't do anything about it and that something must have happened on the server end or to the file system.
Does MySQL shut down when MailStewardPro shuts down?
> My file system is fine (I've checked)
Remember,
database <> files, database files may look fine to an OS process yet be corrupted.
The ls listing you posted for /Volumes/ScratchDisk2/MySQLdata/ included ...
-rw------- 1 _mysql wheel 5242880 Apr 14 2018 ib_logfile0
-rw------- 1 _mysql wheel 5242880 Apr 14 2018 ib_logfile1
-rw------- 1 _mysql wheel 171154866176 Apr 14 2018 ibdata1
drwx------ 11 _mysql wheel 374 Apr 14 2018 myemaildb
drwx------ 75 _mysql wheel 2550 Jul 12 2011 mysql
drwx------ 20 root wheel 680 Dec 28 2012 performance_schema
-rw-r----- 1 _mysql wheel 299186 Apr 14 2018 server.local.err
indicating that myemaildb was last changed 14 Apr 2018 and the mysql db (which holds priv info) was last changed 12 Jul 2011. Let's see those listings for the other datadir you identified.
> is there a way to just try to get it to open/import this structure?
That's a file system's picture of some db files.
At the file level, the db structure does not exist---looking for it there would be like trying to play sheet music through a radio. The database is a structure that exists as a large set of detailed relationships inside those files.
Quote
I've been successfully using it for about 6 years, so I think mysql skills are not *normally* required to use it, but I seem to have run into a failure scenario where those skills are needed to fix things.
Use without obvious error is a minimalist notion of success.
If a product relies on a relational database, then either the product or the user has to know enough about the db to maintain it, ie one of them is the DBA. The makers of MailStewardPro have declined to step up, so you're the DBA.
Edited 1 time(s). Last edit at 11/08/2018 11:57AM by Peter Brawley.