Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Tables disappeared in mysql. How to diagnose ?

In the mysql database, a number of tables disappeared. My email records show that at 6.29AM (PST) today, a transaction that involved several of the missing tables was conducted succesfully. I discovered the problem around 11AM (PST). The problem occured in that interval of time. In the interval, I did not access the server myself, so I suspect malicious action. Are there log files that I can use to I find out who connected to the database, and what they did ?


3 Solutions
If you started your mysql server with the "bianry log" option (--log-bin=file) you
can use the "mysqlbinlog" command.
You may find something in the /var/log/mysql files *if* the database was stopped or started, but beyond that, if you haven't got logging available on the server, you won't see much.

You might want to have a quick look at:


as this may show who has logged onto the system (not whether they did anything in mysql)....but you can then have a quick look at what type of things that people who logged in during the time window have been doing by looking at their .bash_history. Of course this assumes that the individual logged in rather than connecting from a remote location to the mysql socket.

Unfortunately, there is probably little else you can do, *unless* any of your routers/firewalls etc have som form of log available.

(   (()
(`-' _\
 ''  ''

The binlogs (if present) will show when the commands were executed, but only contain the net-result of any insert/update/delete/drop/create as required for replication, and won't tell you who did it.

If the 'user' connected locally, then you may have some luck looking in the .mysql_history files in the various shell account home directories.

Assuming your mysql users are locked down, you should know which database users have permissions to perform such an action, so look for those usernames in the shell account command histories too as a "mysql -u<user> -p" command.

combining the two histories will give you a good idea who did it, but as said, if the connection came from outside the server, then you will need to interrogate firewall or other access logs.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now