Tables disappeared in mysql. How to diagnose ?

Posted on 2006-06-09
Last Modified: 2010-04-22
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 ?


Question by:qtluong
LVL 15

Accepted Solution

DonConsolio earned 168 total points
ID: 16875165
If you started your mysql server with the "bianry log" option (--log-bin=file) you
can use the "mysqlbinlog" command.
LVL 22

Assisted Solution

pjedmond earned 166 total points
ID: 16973999
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.

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

Assisted Solution

arpoodle earned 166 total points
ID: 17081843

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.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello EE, Today we will learn how to send all your network traffic through Tor which is useful to get around censorship and being tracked all together to a certain degree. This article assumes you will be using Linux, have a minimal knowledge of …
BIND is the most widely used Name Server. A Name Server is the one that translates a site name to it's IP address. There is a new bug in BIND (, affecting all versions of BIND 9 from BIND 9.1.0 (inclusive) thro…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question