2

I am running a mysql database on linux with several InnoDB tables. At some point, the ibdata1 file got quite big and I wanted to delete it. So I made a dump, deleted the file and restarted the server.

Suprisingly, the dump didn't contain all data, but only up to a certain date (Dec 6). I know for certain that before restarting, the database held data up to the current date (Dec 27).

Not all hope was lost though. Every night there is a backup done of the whole file system (simple copy and paste to magtapes), so I recovered the datadir folder from Dec 26. I've then used this folder as mysql's new datadir, but in there I can also only find data up to Dec 6.

How is this possible? Since the data was visibly there, where did mysql store it, if not inside the datadir folder? Was it just in RAM for, like, 3 weeks?

my.cnf looks like this:

[client]
port            = 3306
socket          = /home/user/mysql/mysqld.sock


[mysqld]
port            = 3306
#bind-address   = 0.0.0.0
socket          = /home/user/mysql/mysqld.sock
datadir         = /home/user/mysql/data
log-error       = /home/user/mysql/logs/mysqld.err
#skip-locking
key_buffer = 16M
max_allowed_packet = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names=1

I dont know if you can help me, since this issue is kind of specific, but the most important question here is: If you know for certain that your database holds some data at point X, and you know there was a backup of the file system at point X - how would you go along recovering this data? I mean, it must exist somewhere, doesn't it?

best regards

EDIT

I've looked in the mysqld.err file and I found the following warning:

151227 19:21:35  mysqld started
InnoDB: ##########################################################
InnoDB:                          WARNING!
InnoDB: The log sequence number in ibdata files is higher
InnoDB: than the log sequence number in the ib_logfiles! Are you sure
InnoDB: you are using the right ib_logfiles to start up the database?
InnoDB: Log sequence number in ib_logfiles is 0 2439577407, log
InnoDB: sequence numbers stamped to ibdata file headers are between
InnoDB: 0 2446246316 and 0 2446246316.
InnoDB: ##########################################################
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
151227 19:21:35  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
151227 19:21:35  InnoDB: Error: page 1 log sequence number 0 2446123651
InnoDB: is in the future! Current system log sequence number 0 2439577407.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.

It goes on like that for a while. Any suggestions?

6
  • is there any possibility you restarted MySQL on 27th before taking a dump. BTW, you mean mysqldump, right? – akuzminsky Dec 30 '15 at 17:52
  • hmm, I think i restarted the server shortly before creating the dump with mysqldump. but I didn't delete ibdata before that (obviously). – Philipp Murry Jan 1 '16 at 14:39
  • @akuzminsky, do you have any idea what the problem could be? – Philipp Murry Jan 5 '16 at 8:50
  • The data is too confusing, I can't solve the puzzle. I feel like ibdata1 was deleted at some point, but there are some mismatches. To answer your most important question, the data you see on filesystem may not correspond to the data MySQL sees. For example, if ibdata1 gets deleted MySQL keeps the file descriptor open so it doesn't notice the file was actually deleted. By the way, it's wrong to copy MySQL files for the backup purpose. – akuzminsky Jan 5 '16 at 21:36
  • 1
    When you delete ibdata1 the file is still in the file systems where is was as long as there are processes that have it open. MySQL opens ibdata1 at start so it sees all data until you shut it down. Other processes don't see the file and can't open it. But you can still see it in /proc : # ls -la /proc/15830/fd/5 lrwx------ 1 root root 64 Jan 7 02:38 /proc/15830/fd/5 -> /mnt/data/ibdata1 – akuzminsky Jan 7 '16 at 21:42

Your Answer

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

Browse other questions tagged or ask your own question.