Solved

data from MySQL missing

Posted on 2011-03-02
5
848 Views
Last Modified: 2012-05-11
Hi,

Some data from our MySQL tables has just gone missing at random. There is no commonality between the data, just information here and there is now gone. This is a custom built PHP based CMS web application which was running fine for several months. We have have looked at the code and have not found any malicious code or worms.

How should we be doing to tackel this problem? Appreciate any help.

Thanks
0
Comment
Question by:Web_Sight
5 Comments
 
LVL 3

Expert Comment

by:pius_babbun
ID: 35024418
If you could check your sever log you may find some hooks to get to know what had caused this problem. Try to get the log based on you web server and mysql configuration.
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 35024936
Do you have a cron job setup to backup the mysql tables or complete databases at regular intervals? If so you are in luck but if not the data is lost for good.
0
 
LVL 11

Expert Comment

by:mattibutt
ID: 35025512
If its a cms then the user who manage the application might have accidently deleted other possibility is database is corrupted have you made backup of the database how much data do you think is missing.
I would say change the password of the database also monitor cms admins and change their password as well
0
 
LVL 3

Accepted Solution

by:
mwiercin earned 250 total points
ID: 35032538
Few things you can look into :

1. Check if by any chance you will have full query log enabled:

mysql> show variables like 'log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log           | OFF   | 
+---------------+-------+
1 row in set (0.00 sec)

Open in new window


If you have it ON consult your my.cnf for the  path  (at the database server) to full query log (it will be raw text file), of which you can derive what queries have deleted your data. Next step would be to trace them back to the part of application and collate with web server logs.

2. You may also have binary logging enabled (this is used primarily for cross database replication), check by

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON   | 
+---------------+-------+
1 row in set (0.00 sec)

Open in new window

In such case check my.cnf variable log_bin. It will show you the pattern used for binary logs. i.e.

log_bin                 = /mnt/mysql.data/mysql-bin.log

Open in new window


, where you will find bunch of files starting with this name and suffixed with numbers. These are partial binary, but you can examine them easily by using command line tool mysqlbinlog.

3. If you don't have full or binary log, your last resort would be to look into slow query log

mysql> show variables like 'log_slow_queries';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | ON   | 
+------------------+-------+
1 row in set (0.00 sec)

Open in new window


Again you will find the exact location in my.cnf. Unfortunately this will include the queries only if they took more time then defined threshold (some MySQL distributions come with 2 seconds  preconfigured).

If you don't have any of these three logs, then unfortunately there is nothing more that would help you at MySQL level. I recommend auditing websever and/or application logs if you have any. Also, you can easily audit grants by using Perl tool http://maatkit.org/get/mk-show-grants.
0
 

Author Closing Comment

by:Web_Sight
ID: 35350362
not a good answer
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

773 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