August 1, 2011 at 12:15MySQL database recovery history from files (InnoDB)
According to popular wisdom, "admins are divided into two categories: those that make backups, and those that already do." In my case, the responsibility for an unmade backup fell on the developer, that is, on myself. This article is about how to find a way out of a situation similar to that described. I hope it will be useful to those who, having no such experience, may encounter a similar situation.
In general, it was like that ...
In a distant kingdom, in the far-off state In one of the state administrative institutions, which is serviced by our enterprise, there was a server. As a server, we used a regular desktop computer with a not-so-powerful dual-core processor, a 320 GB hard drive, without any “unnecessary” RAID arrays there, nothing “superfluous", with the server Windows installed on it, it seems, 2003. And he did some server tasks, which I was not particularly aware of by virtue of my position. He worked like this almost without interruption for about several years.
And then, one fine day, the head of the department called me with my partner to me, gave us the task to develop a system for this institution using MySQL Server 5.1 as a DBMS. We developed a system, everyone was very happy with it, and it was time to implement it. Without any problems, one of our admins and I, having arrived at the office of the institution, installed MySQL 5.1 on the aforementioned server, deployed a database on it, installed the client application on working machines, and launched the implementation process. Employees of the institution were very pleased with the system, but mastered it slowly due to workload, meetings, meetings, appeals of citizens, etc. Slowly entered information, but greatly “stretched pleasure”. I should have already begun to slowly think about the backup / restore strategy, but I kept thinking that this wasn’t quite my thing, because I'm not the admin, but the developer. But the administrators were completely indifferent. I consoled myself only by the fact that while there is not enough information, there will be more, and if no one starts making backups, I will start. Looking back, I now see how easy it was to predict subsequent events.
After some time, “enlightenment” overtook one of the key employees, and in a very short period of time a significant amount of information was introduced into the system, almost all the information that could have been entered at that time. I didn’t think about it, I believed that they were also slowly looking at the system, without really introducing anything, working for themselves, as before, only with their papers. The employee quickly entered information and went on vacation for a month.
And so, another “beautiful” day came, the server crashed. Moreover, not software, but hardware - the hard drive “flew”. Next, the hard drive was handed over to a company engaged in data recovery, and a significant part of the information from it was restored. However, admins, when restoring the server itself, recovered only all the information from disk D, no one remembered our MySQL (by “lucky chance”, it was put on disk C). All this time I calmly worked on other projects, I didn’t know about these events.
Some more time has passed, and the aforementioned employee of the institution leaves the leave. Calls me and says that he can’t log in. I, as usual, ask what message the system gives out, and all that, I immediately understand that something is wrong with the connection to the database. Further, I already learn from her this story with the server crash, so that they “changed something”, and my understanding becomes even deeper: now I realized that our database is not on the server at all. Moreover, as it turned out, there is neither a database nor the DBMS itself. I ask the admins what was there, what was restored, what wasn’t ... I come to the disappointing conclusion that there is no base, and the big question is whether it can be restored. I take a copy of the information recovered from the killed hard drive and start digging into it.
First of all, I am looking for the Program Files folder, where MySQL is installed by default. I don’t find it, but I find a folder with MySQL data files, in it there is a folder with the name of our very database. A small charge of optimism slightly dispersed the clouds of my despair, but ... looking at the * .MYD files , which in theory should have table entries, I see that their size is too small for the amount of information that has been entered. Binary logs were also not included, and this option automatically disappeared. I start thinking about this issue and remember that you can take a look at the development base on our test server, which I did. There I saw that most of the tables - and the most important ones - work on the InnoDB engine, which means that their records are stored separately in the ibdata * file (in my case, only ibdata1) I go one level higher and see this treasured file. Nearby are also two logs of the InnoDB engine: ib_logfile0 and ib_logfile1 . Having opened one of the logs for viewing in a text editor among the “crooks”, I also saw pieces of the information itself, and then I realized that the database can be restored, at least theoretically.
It was necessary to choose a car, where to make an attempt to restore the base itself. Because during development, I use a test server, access to which at the file system level no one will give me without a fight, I decide to install MySQL Server 5.1 and MySQL GUI Tools directly on my development machine running Windows XP. So, everything is worth it, the DBMS is running, and thinking about how to approach the task, I decide to first export the database creation script from the test server, and then run it on my machine. Now I have an empty database with a completely identical structure and with all stored procedures. I stop the MySQL service, and copy all the contents from the folder with the name of our database from the crashed server to a similar folder of the newly installed MySQL local (the folder with the name of the database, located in the data folderlocated along the path for storing data files specified during the installation of MySQL). When setting up a local MySQL Server, I decided to specify a separate folder for InnoDB data files, where I then drop the restored ibdata1 file . After that I transfer the InnoDB log files ( ib_logfile0 and ib_logfile1 ) directly to my data folder , where they should be by default.
Another attempt to start MySQL was again unsuccessful. Having no real combat experience in DBMS administration (with the exception of experience gained in courses, but, again, in other DBMSs), I decided to go through the experiment, although now I understand that it was not necessary at all. I delete the InnoDB log files, start the MySQL service and see that it creates the InnoDB log files themselves with the same names, but with a size of 20 MB.
I look at the size of “my” log files from the crashed server and see that it is significantly different and is 81 MB. Well ... I stop the server, and through the MySQL Administrator I go to the Startup Variables settings, InnoDB tab. I set the size of the log files to 81 MB and drop back “my” log files. MySQL started up, but I did not see the data in the tables of interest to me (although they have already been restored in tables running on the MyISAM engine). After a bit of searching through the network, I read that you need to start MySQL in recovery mode, for which you need to set the start variable InnoDB_force_recovery to 6. I tried to do this through MySQL Administrator (it’s clear that you can set parameters via the GUI, or you can edit my files .ini or my.cnf [depending on the version of MySQL and the selected OS] manually), and, either I didn’t finish something, or I overlooked something, but I didn’t see the desired result. MySQL started as usual, but did not say a word about recovery. “Well,” I thought, “let’s try then through the console, and again explicitly write the parameterinnodb_force_recovery ”.
I launch the Windows command line and enter:
To which I get the answer: There wasn’t much sense to sin on the antivirus, and I decided to stop the command using CTRL + break, then repeat the command again with the Task Manager turned on. Before running the execution command, I looked at the list of processes to make sure that no instance of the MySQL service is running. And again he repeated the command. Seeing the same message, I looked in the Task Manager and saw that for some reason two processes were launched at once with the name mysqld, moreover, one was launched as a system one and the other as my user. Killing the system process, I “broke the vicious circle”, and the execution went further:
So far, I did not understand what was the matter, why two processes were launched at once. Including because the joy swept over me, and having connected to the database through Toad for MySQL, I saw that the data was restored! This joy pushed all questions to the background.
“Without leaving the cash desk”, I dump the database in order to subsequently restore it to the working server - the very hero of this “fairy tale” who just changed the hard disk and reinstalled the system.
I enter the password, and I get a script to create and populate all the database tables, as well as all stored procedures in the db_name.sql file in the folder specified in the parameter. Information saved!
Then, on the production server, I install MySQL itself, configure it, create an empty database and dump a dump into it: I
did not have to change anything in the client application settings, because The IP address and network name of the server remained the same as they were before the fall. We went into the system, checked, and - oh, a miracle! - all information is in place. Here and the tale ends ... but dwell on the "morality of this fable."
For myself, I once again understood several important things, namely:
Initially, I thought just to give a brief instruction on what and how to do in this case, but, firstly, such instructions, in principle, are available on the network; and, secondly, he decided to describe in more detail everything as it was, with “pitfalls” and errors; and also found that such a presentation could be somewhat more interesting. In fact, I'm sure that a minority will encounter such pitfalls (more precisely, small pebbles), but nonetheless, I consider it important that they be described.
And, finally, a brief recipe for what to do in a similar situation when only InnoDB data and log files are left from the working base with InnoDB tables:
A similar operation on Linux or another Unix-like system will look something like this. In addition, as I understand it, the data and log files of MySQL (both binary logs and InnoDB logs) are universal for all systems, and what fell on Windows can be restored in the same way on Linux, OS X, and any other system where MySQL itself works.
InnoDB Engine Documentation
PhantomTLT prompts in a comment (and, based on his advice, I slightly corrected the memo above):
If you still have the entire datadir, then you do not need to create any empty databases with the same structure. Simply replace all the files and adjust the size of the InnoDB logs.
Next, start MySQL and carefully look at the log. Must start and perform recovery.
If the recovery fails, then in the config set innodb_force_recovery = 1 (http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html) and try to start. If we do not start, set innodb_force_recovery = 2 and try to start. Etc. until 6.
If you start immediately with innodb_force_recovery = 6, then you run the risk of getting a database in inconsistent state. Those. data integrity (consistency) may be compromised.
If you reached innodb_force_recovery = 6 and this did not help, then it is very sad - you need to restore it in semi-manual mode.
In general, it was like that ...
Introduction
And then, one fine day, the head of the department called me with my partner to me, gave us the task to develop a system for this institution using MySQL Server 5.1 as a DBMS. We developed a system, everyone was very happy with it, and it was time to implement it. Without any problems, one of our admins and I, having arrived at the office of the institution, installed MySQL 5.1 on the aforementioned server, deployed a database on it, installed the client application on working machines, and launched the implementation process. Employees of the institution were very pleased with the system, but mastered it slowly due to workload, meetings, meetings, appeals of citizens, etc. Slowly entered information, but greatly “stretched pleasure”. I should have already begun to slowly think about the backup / restore strategy, but I kept thinking that this wasn’t quite my thing, because I'm not the admin, but the developer. But the administrators were completely indifferent. I consoled myself only by the fact that while there is not enough information, there will be more, and if no one starts making backups, I will start. Looking back, I now see how easy it was to predict subsequent events.
After some time, “enlightenment” overtook one of the key employees, and in a very short period of time a significant amount of information was introduced into the system, almost all the information that could have been entered at that time. I didn’t think about it, I believed that they were also slowly looking at the system, without really introducing anything, working for themselves, as before, only with their papers. The employee quickly entered information and went on vacation for a month.
Main fighting
And so, another “beautiful” day came, the server crashed. Moreover, not software, but hardware - the hard drive “flew”. Next, the hard drive was handed over to a company engaged in data recovery, and a significant part of the information from it was restored. However, admins, when restoring the server itself, recovered only all the information from disk D, no one remembered our MySQL (by “lucky chance”, it was put on disk C). All this time I calmly worked on other projects, I didn’t know about these events.
Some more time has passed, and the aforementioned employee of the institution leaves the leave. Calls me and says that he can’t log in. I, as usual, ask what message the system gives out, and all that, I immediately understand that something is wrong with the connection to the database. Further, I already learn from her this story with the server crash, so that they “changed something”, and my understanding becomes even deeper: now I realized that our database is not on the server at all. Moreover, as it turned out, there is neither a database nor the DBMS itself. I ask the admins what was there, what was restored, what wasn’t ... I come to the disappointing conclusion that there is no base, and the big question is whether it can be restored. I take a copy of the information recovered from the killed hard drive and start digging into it.
First of all, I am looking for the Program Files folder, where MySQL is installed by default. I don’t find it, but I find a folder with MySQL data files, in it there is a folder with the name of our very database. A small charge of optimism slightly dispersed the clouds of my despair, but ... looking at the * .MYD files , which in theory should have table entries, I see that their size is too small for the amount of information that has been entered. Binary logs were also not included, and this option automatically disappeared. I start thinking about this issue and remember that you can take a look at the development base on our test server, which I did. There I saw that most of the tables - and the most important ones - work on the InnoDB engine, which means that their records are stored separately in the ibdata * file (in my case, only ibdata1) I go one level higher and see this treasured file. Nearby are also two logs of the InnoDB engine: ib_logfile0 and ib_logfile1 . Having opened one of the logs for viewing in a text editor among the “crooks”, I also saw pieces of the information itself, and then I realized that the database can be restored, at least theoretically.
It was necessary to choose a car, where to make an attempt to restore the base itself. Because during development, I use a test server, access to which at the file system level no one will give me without a fight, I decide to install MySQL Server 5.1 and MySQL GUI Tools directly on my development machine running Windows XP. So, everything is worth it, the DBMS is running, and thinking about how to approach the task, I decide to first export the database creation script from the test server, and then run it on my machine. Now I have an empty database with a completely identical structure and with all stored procedures. I stop the MySQL service, and copy all the contents from the folder with the name of our database from the crashed server to a similar folder of the newly installed MySQL local (the folder with the name of the database, located in the data folderlocated along the path for storing data files specified during the installation of MySQL). When setting up a local MySQL Server, I decided to specify a separate folder for InnoDB data files, where I then drop the restored ibdata1 file . After that I transfer the InnoDB log files ( ib_logfile0 and ib_logfile1 ) directly to my data folder , where they should be by default.
Another attempt to start MySQL was again unsuccessful. Having no real combat experience in DBMS administration (with the exception of experience gained in courses, but, again, in other DBMSs), I decided to go through the experiment, although now I understand that it was not necessary at all. I delete the InnoDB log files, start the MySQL service and see that it creates the InnoDB log files themselves with the same names, but with a size of 20 MB.
I look at the size of “my” log files from the crashed server and see that it is significantly different and is 81 MB. Well ... I stop the server, and through the MySQL Administrator I go to the Startup Variables settings, InnoDB tab. I set the size of the log files to 81 MB and drop back “my” log files. MySQL started up, but I did not see the data in the tables of interest to me (although they have already been restored in tables running on the MyISAM engine). After a bit of searching through the network, I read that you need to start MySQL in recovery mode, for which you need to set the start variable InnoDB_force_recovery to 6. I tried to do this through MySQL Administrator (it’s clear that you can set parameters via the GUI, or you can edit my files .ini or my.cnf [depending on the version of MySQL and the selected OS] manually), and, either I didn’t finish something, or I overlooked something, but I didn’t see the desired result. MySQL started as usual, but did not say a word about recovery. “Well,” I thought, “let’s try then through the console, and again explicitly write the parameterinnodb_force_recovery ”.
I launch the Windows command line and enter:
> mysqld --console --innodb_force_recovery=6To which I get the answer: There wasn’t much sense to sin on the antivirus, and I decided to stop the command using CTRL + break, then repeat the command again with the Task Manager turned on. Before running the execution command, I looked at the list of processes to make sure that no instance of the MySQL service is running. And again he repeated the command. Seeing the same message, I looked in the Task Manager and saw that for some reason two processes were launched at once with the name mysqld, moreover, one was launched as a system one and the other as my user. Killing the system process, I “broke the vicious circle”, and the execution went further:
110727 10:31:52 [Note] Plugin 'FEDERATED' is disabled.
110727 10:31:52 InnoDB: Initializing buffer pool, size = 40.0M
110727 10:31:52 InnoDB: Completed initialization of buffer pool
110727 10:31:52 InnoDB: Operating system error number 32 in a file operation.
InnoDB: The error means that another program is using InnoDB's files.
InnoDB: This might be a backup or antivirus software or another instance
InnoDB: of MySQL. Please close it to get rid of this error.> mysqld --console --innodb_force_recovery=6
110727 10:31:52 [Note] Plugin 'FEDERATED' is disabled.
110727 10:31:52 InnoDB: Initializing buffer pool, size = 40.0M
110727 10:31:52 InnoDB: Completed initialization of buffer pool
110727 10:31:52 InnoDB: Operating system error number 32 in a file operation.
InnoDB: The error means that another program is using InnoDB's files.
InnoDB: This might be a backup or antivirus software or another instance
InnoDB: of MySQL. Please close it to get rid of this error.
110727 10:32:02 InnoDB: Operating system error number 32 in a file operation.
InnoDB: The error means that another program is using InnoDB's files.
InnoDB: This might be a backup or antivirus software or another instance
InnoDB: of MySQL. Please close it to get rid of this error.
110727 10:32:12 InnoDB: Operating system error number 32 in a file operation.
InnoDB: The error means that another program is using InnoDB's files.
InnoDB: This might be a backup or antivirus software or another instance
InnoDB: of MySQL. Please close it to get rid of this error.
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
110727 10:32:22 InnoDB: Started; log sequence number 0 0
InnoDB: !!! InnoDB_force_recovery is set to 6 !!!
110727 10:32:22 [Note] Event Scheduler: Loaded 0 events
110727 10:32:22 [Note] mysqld: ready for connections.
Version: '5.1.58-community' socket: '' port: 3306 MySQL Community Server (GPL)So far, I did not understand what was the matter, why two processes were launched at once. Including because the joy swept over me, and having connected to the database through Toad for MySQL, I saw that the data was restored! This joy pushed all questions to the background.
Victory awareness and control shot
“Without leaving the cash desk”, I dump the database in order to subsequently restore it to the working server - the very hero of this “fairy tale” who just changed the hard disk and reinstalled the system.
> mysqldump --routines -u "user" -p db_name > [path\]db_name.sqlI enter the password, and I get a script to create and populate all the database tables, as well as all stored procedures in the db_name.sql file in the folder specified in the parameter. Information saved!
Then, on the production server, I install MySQL itself, configure it, create an empty database and dump a dump into it: I
> mysql -u "user" –p db_name < [path\]db_name.sqldid not have to change anything in the client application settings, because The IP address and network name of the server remained the same as they were before the fall. We went into the system, checked, and - oh, a miracle! - all information is in place. Here and the tale ends ... but dwell on the "morality of this fable."
Conclusions and Conclusion
For myself, I once again understood several important things, namely:
- The backup / recovery strategy should be determined already at the development stage;
- In matters of backup / recovery, one should not rely on admins if you can do it yourself; and even more so if it’s not difficult for you to automate this process (which can be done at a primitive level by regular means);
- Organization at the enterprise and clear regulation of the responsibilities of each individual employee in each specific project are very important when we deal with IT; and modern standards for the division of labor in IT enterprises are very, very relevant and have grown out of nowhere;
- And yet, if the base flew and there is no backup - this is not a reason to immediately despair and give up.
Initially, I thought just to give a brief instruction on what and how to do in this case, but, firstly, such instructions, in principle, are available on the network; and, secondly, he decided to describe in more detail everything as it was, with “pitfalls” and errors; and also found that such a presentation could be somewhat more interesting. In fact, I'm sure that a minority will encounter such pitfalls (more precisely, small pebbles), but nonetheless, I consider it important that they be described.
And, finally, a brief recipe for what to do in a similar situation when only InnoDB data and log files are left from the working base with InnoDB tables:
- We substitute the data and description files of the tables into the data folder ( * .frm , * .MYI and * .MYD files , everything in the folder with the database name - that is, copy the entire folder as a whole);
- In the folder with InnoDB data files (by default, most likely this is the data folder, where both binary logs and folders with database names are stored; in my case, I made a separate folder for InnoDB data) we delete all ibdata * files . Here, probably, it should be noted that the file size must correspond to that specified in the settings. If the sizes vary, you can do this: delete the file (or files) created by the server manually and create a new (or, accordingly, new) with a size equal to the size of the existing one, then start the server and stop it, and substitute your own files instead of the files created by it. MySQL Administrator can facilitate this operation - so that you do not have to play with the settings manually. Or you can make it even easier - simply replace all the files (or the file, if it is one), and in the settings file my.ini / my.cfg just resizing - in theory it should work.
- We drop InnoDB logs (or replace them, if they already exist) to where they should be (by default, this is the same data folder where the files with data and descriptions are stored, but this, again, can be changed in the settings);
- We set the size of InnoDB logs equal to the size of our logs (if manually - in bytes, if in MySQL Administrator - in MB);
- We start MySQL daemon
> mysqld --console
carefully we look at a broad gull , the service has to be started and recovery is performed; - If recovery does not occur, then in the configuration or parameter set innodb_force_recovery = 1 and try to run. If it does not work, set innodb_force_recovery = 2 and try to run. Etc. until 6.
- And finally, we look at whether our data is in place, and if in place - we make a database dump, which we then deploy to a working server.
A similar operation on Linux or another Unix-like system will look something like this. In addition, as I understand it, the data and log files of MySQL (both binary logs and InnoDB logs) are universal for all systems, and what fell on Windows can be restored in the same way on Linux, OS X, and any other system where MySQL itself works.
References
InnoDB Engine Documentation
In addition
PhantomTLT prompts in a comment (and, based on his advice, I slightly corrected the memo above):
If you still have the entire datadir, then you do not need to create any empty databases with the same structure. Simply replace all the files and adjust the size of the InnoDB logs.
Next, start MySQL and carefully look at the log. Must start and perform recovery.
If the recovery fails, then in the config set innodb_force_recovery = 1 (http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html) and try to start. If we do not start, set innodb_force_recovery = 2 and try to start. Etc. until 6.
If you start immediately with innodb_force_recovery = 6, then you run the risk of getting a database in inconsistent state. Those. data integrity (consistency) may be compromised.
If you reached innodb_force_recovery = 6 and this did not help, then it is very sad - you need to restore it in semi-manual mode.