0

Started seeing some strange errors from dependent processes this morning. After some digging it appears that the file /mysql/proc.MYD is set to zero length. The file /mysql/db.MYD appears to be truncated as well.

What's even more interesting is that this isn't the case on the replicated slave.

  1. What would cause this?
  2. Why wouldn't it show up on the slave server?
  3. If I copy the files /mysql/proc* from the slave back to the master - will this restore the procedures?

Nearly every file in the /mysql folder was touched at the same time. Some were changed while many were not. My first thought is that it's a failure in the file system but it appears to be localized to this one folder.

EDIT: Pertinent details:

RHEL 5.x
MySQL 5.5

I brought down both MySQL instances and am attempting to get the MySQL folder off of tape. I would run fsck on the volume but the man page has some pretty scary warnings.

  • Even though it "appears to be localized to this one folder" it could certainly still be file system corruption. What Operating System is this? – Max Vernon Oct 28 '13 at 22:33
  • 1
    Although I am by no means a MySQL expert, I would estimate the slave still contains the files because MySQL is not aware of the change in the files affected. In other words, it likely wasn't a MySQL process that changed the files in question. – Max Vernon Oct 28 '13 at 22:41
1

The safest course of action is to stop MySQL on both machines and copy the table files you need from the mysql schema from slave to master, saving copies of the files. As long as they are MyISAM tables (not InnoDB), this is a legitimate operation.

If you can't shut down the machines, then connect to both of them and run FLUSH TABLES WITH READ LOCK; wait for the prompt to return, and then leave the connections open (to hold the locks) while copying the files. Then UNLOCK TABLES; to release the global read lock. MyISAM tables are pretty flexible this way... you need to copy the MYD and MYI files for the tables, together in pairs, and you should probably copy the matching .frm files on the off chance that they aren't identical (though they should be)... just save copies of anything you overwrite, and don't try this with InnoDB tables.

This should restore the procedures; you might need to restart the server for them to be recognized by all threads (but I don't think so).

If changes to tables in mysql normally replicate in your setup, that tends to suggest underlying system trouble ... or someone else with access to the system not quite knowing with they are doing ... or a potential security breach, and, unfortunately, none of these things really jumps out to me as more likely than any other, though my instinct is to be "suspicious of malicious."

Since you do have binary logging enabled, you should review the binlogs from around the period of time the timestamps suggest using mysqlbinlog --verbose --base64-output=decode-rows, since even though the slave didn't change its tables, that doesn't mean there couldn't be something interesting in the binlog that changed something else that you haven't yet discovered, or that did not have the same impact on the slave for any number of other reasons, like replicate-ignore-db.

I would check all of the grant table contents along with everything else in the mysql schema. I would also reconfigure the servers with log_warnings = 2, which logs access denied errors, and I might even be tempted to turn on the general query log for a while if your system has the I/O capacity to support that without performance penalty. I would further configure the local machine to forward a copy of its syslog messages to another system with tightly restricted access, and thoroughly audit the firewall... all of this is a little bit alarmist-sounding, but without some other evidence of filesystem problems, that certainly can't be considered the most likely thing to have happened.

|improve this answer|||||
  • I will check the binlogs but I can't think of a command (or even series of MySQL commands) that would have this kind of effect. – ethrbunny Oct 29 '13 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.