File Logs - Where do I find the logs relating to deletion of database tables?

[JesseLee]

Good Evening All,

Our website/systems administrator recently decided to 'get us back' for his dismissal and consequently we found that he managed to drop one of our databases via an open (unknown to me) SSH account.

Once I realized the database was dropped, I disabled the rouge SSH account and restored a database backup however I'm currently looking for is the logs relating to this directory "/var/lib/mysql/<mydatabase>".

I'm aware that logs are kept for system access like SSH, but does it also keep record of deleted files? I would have normally posted this under the 'general' discussion area however since it's database specific I was hoping someone would be able to assist.

Any information is appreciated
Regards,
J.

[cpanelkenneth]

By default MySQL will not log general SQL statements. You would need to configure MySQL to do so. The MySQL documentation on logging (MySQL :: MySQL 5.1 Reference Manual :: 5.2 MySQL Server Logs) should help you do that.

[JesseLee]

Thank you Kenneth,

I'm actually looking for the logs pertaining to file deletions of the path to the SQL databases (File System)
If I was unclear, please let me know - Newbie to WHM.

Regards,
J.

[cPanelTristan]

If the file was deleted in SSH, then you'll have to check .bash_history for the users on the system, which doesn't normally even have time stamps:

grep databasename /root/.bash_history /home/*/.bash_history

I'm uncertain how else it could be deleted other than SSH, MySQL command line or PhpMyAdmin, and if the person did an su - to root level, which is required to even get to /var/lib/mysql location for SSH, you aren't going to get much detail. You can always go through the .bash_history file where you find the entry to see what other commands were processed around that time, provided the person didn't clear the history to cover their tracks.

For MySQL command line or PHPMyAdmin commands, you can check /root/.mysql_history file to see if there are any indications in the file for a table or database being dropped.

[JesseLee]

Thanks for that Tristan,

The 'user' did have root access, taking out the tables from the database via the File System appeared to be what happened. I can see the bash history (provided by your command) only lists the items post-deletion, I suppose it was wiped before he left as you mentioned.

Private server.  All activity is being logged.
root@server [~]# grep mydatabasename /root/.bash_history /home/*/.bash_history
/root/.bash_history:cp -a mydatabasename.sql /home/useracc/public_html/
/root/.bash_history:chmod useracc:useracc mydatabasename.sql
/root/.bash_history:chown useracc:useracc mydatabasename.sql
/root/.bash_history:cp -a mydatabasename /home/
/root/.bash_history:cp -a mydatabasename.sql /home/useracc/
/root/.bash_history:grep /mydatabasename/ /etc/httpd/domlogs/useracctelectric.com >> sqllogs.txt
/root/.bash_history:cd mydatabasename

connect mydbname
connect database mydbname
open mydbname
open mydbname dir;
select database mydbname
select database mydbname
select database mydbname
use mydbname
source /home/useracc/mydbname.sql

This was exactly what I required, thank you very much!

Regards,
Jesse-Lee Stringer