History of MySQL database recovery from files (InnoDB)
As the proverb says, "admins are divided into two categories: those that do backups, and those that already do." In my case, responsibility for an unfulfilled backup fell on the developer, that is, on myself. This article focuses on how to find a way out of a situation similar to that described. I hope it will be useful to those who, without such experience, may be faced with a similar situation.
In general, it was like this ...
In the far kingdom, in the far-off state In one of the state administrative institutions, which is served by our enterprise, there was a server. The server used was a regular desktop computer with a not very powerful dual-core processor, a 320 GB hard drive, without any “unnecessary” RAID arrays, nothing “extra”, with the Windows server installed on it, it seems, 2003. And it did some server tasks that I, by virtue of my position, were not particularly aware of. He worked almost without interruption for several years.
')
And then, one day, the head of the department called me and my partner, gave us the task to develop a system for this institution using MySQL Server 5.1 as a DBMS. We developed the system, everyone was very pleased with it, and it was time to implement it. Without any problems, we, with one of our administrators, came to the office of the institution, installed MySQL 5.1 on the aforementioned server, deployed the database on it, installed the client application on the working machines, started the deployment process. Employees of the institution were very pleased with the system, but mastered it slowly due to workload, meetings, meetings, citizens' appeals, etc. Slowly entered information, but strongly “stretched pleasure”. It was necessary to start thinking slowly about the backup / recovery strategy, but I was still thinking that this was not really my business, because I’m not an admin, but a developer. But the admins it was absolutely indifferent. I consoled myself only with the fact that while there is little information, there will be more, and if no one starts doing backups, I'll start. Looking back, I now see how easy it was to predict subsequent events.
After a while, one of the key employees was overtaken by “enlightenment”, and in a very short period of time a significant amount of information was entered into the system, almost all the information that could be entered at that time. I didn’t even think about it, I thought that they were also slowly looking at the system, without really introducing anything, working for themselves, as before, only with their own papers. The employee also quickly entered the information and went on vacation for a month.
And now, another “fine” day has come, the server has fallen. Moreover, not in software, but in hardware - the hard disk “flew”. Then the hard drive was handed over to the data recovery company, and much of the information was recovered from it. However, admins, while restoring the server itself, restored only all the information from disk D, no one remembered about our MySQL (by “lucky chance” it was put on disk C). All this time I have been quietly doing other projects for myself, I did not even know about these events.
Some more time passed, and the aforementioned employee of the institution goes from vacation. He calls me and says that he cannot log in. I, as usual, ask what message the system gives, and so on, I immediately understand that there is something wrong with connecting to the database. Further, I already learn this story from her with the fall of the server, with the fact that there is “something changed”, and my understanding becomes even deeper: now I realized that our database is not on the server at all. And, as it turned out, there is neither a base, nor the DBMS itself. I ask admins what was there after all, what was restored, what is not ... I come to the disappointing conclusion that there is no base, and the big question is whether it will be possible to restore it. I take myself a copy of the information recovered from the killed hard disk and begin to dig into it.
First of all I look for the Program Files folder, where MySQL is placed by default. I do not find it, but I find a folder with MySQL data files, in it is a folder with the name of that very database of ours. 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 was entered. Binary logs were also not included, and this option automatically disappeared. I start thinking about this issue and remember that you can look at the developer base on our test server, which I did. There I saw that most of the tables - and, most important - work on the InnoDB engine, which means that their records are stored separately in the ibdata * file (in my case, only ibdata1 ). I go up a level and see this coveted 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 “krakozyabr”, I also saw pieces of the information itself, and then I realized that it was possible to restore the base, 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 without a fight nobody will give me, I decide to install MySQL Server 5.1 and MySQL GUI Tools directly to my developer machine running under Windows XP. So, everything costs, the DBMS is running, and thinking about how to approach the task, I decide first to export the database creation script from the test server, and then start it on my machine. Now I have an empty database with a completely identical structure and all stored procedures. I stop the MySQL service, and copy all the contents from the folder with the name of our database from the fallen server to a similar folder freshly installed locally in MySQL (the folder with the name of the database, nested in the data folder, located along the way to store the data files specified during the MySQL installation) . When setting up a local MySQL Server, I decided to specify a separate folder for the InnoDB data files, to which I discard the restored ibdata1 file. After that, I transfer InnoDB-log files ( ib_logfile0 and ib_logfile1 ) directly to my data folder, where they should be by default.
Another attempt to start MySQL again failed. Having no real combat experience in administering the DBMS (except for the experience gained in the courses, but, again, on other DBMS) I decided to go through the experiment, although now I understand that it was not at all necessary. 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” files with logs from the fallen server and see that it is significantly different and is 81 MB. Well ... I stop the server, and go through the MySQL Administrator to the settings of startup variables (Startup Variables), InnoDB tab. I set the size of the log files to 81 MB and throw back my “own” log files. MySQL started, but I did not see the data in the tables of interest to me (although in the tables that worked on the MyISAM engine, they were already restored). Having a little fumbled over the network, I read that MySQL should be started in recovery mode, for which you need to set the starting variable InnoDB_force_recovery to 6. I tried to do it through MySQL Administrator (it's clear that you can set parameters through the GUI, and you can also edit my .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, “then we will try through the console, and once again we explicitly set the innodb_force_recovery parameter”.
I launch the Windows command line and enter:
To which I get in response:
There was no special sense to sin on the antivirus, and I decided to stop the execution of the command with CTRL + break, then repeat the command again with the Task Manager enabled. Before running the command, I looked through the process list to make sure that no instance of the MySQL service was running. And again repeated the command. Having seen the same message, I looked into the Task Manager and saw that for some reason two processes were running at once with the name mysqld, and one was running as a system and the other on behalf of my user. After killing the system process, I “broke a vicious circle,” and the implementation went further:
So far, I did not understand what was the matter, why two processes were launched at once. Including because the joy swept 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 departing from the cash register,” I make a dump of the database in order to restore it to the working server later - the very hero of this “fairy tale”, who just had to change the hard drive and reinstall the system.
I enter the password and get the script for creating and populating all the tables in the database, as well as all the 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 myself, configure it, create an empty database and dump a dump into it:
There was nothing to change in the settings of the client application, since The ip-address and network name of the server remained the same as they were before the fall. Signed in, checked, and - oh, a miracle! - All information is in place. Here and the fairy-tale is the end ... but let us dwell on the “morality of this fable."
For myself, I once again understood several important things, namely:
Initially, I thought simply to give a brief instruction on what and how to do in this case, but, first, such instructions, in principle, are available on the network; and, secondly, I decided to describe everything in more detail as it was, with “pitfalls” and errors; and also considered that this form of presentation may be somewhat more interesting. In fact, I am sure that a minority will encounter similar pitfalls (more precisely, small pebbles), but nevertheless, I consider it important that they be described.
And, finally, a brief memo-recipe of what to do in a similar situation, when only the InnoDB data and log files are left from the working database with InnoDB tables:
A similar operation on Linux or another Unix-like system will look similar. In addition, as far as I understand, the data files and MySQL logs (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, and on OS X, and any other system where MySQL itself works.
InnoDB Engine Documentation
PhantomTLT in the comments suggests (and, based on his advice, I slightly adjusted the memo above):
If you have all the datadir preserved, then no empty databases with identical structures need to be created. Simply replace all the files and adjust the size of the InnoDB logs.
Next, start MySQL and carefully look at the log. Should start and perform a restore.
If the recovery fails, then we put innodb_force_recovery = 1 in the config (http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html) and try to start. If we don’t start, set innodb_force_recovery = 2 and try to start. Etc. until 6.
If you immediately start with innodb_force_recovery = 6, then you risk getting a database in an inconsistent state. Those. integrity (consistency) of data may be compromised.
If you have reached innodb_force_recovery = 6 and this did not help, then it is very sad - you need to restore it in a semi-manual mode.
In general, it was like this ...
Introduction
')
And then, one day, the head of the department called me and my partner, gave us the task to develop a system for this institution using MySQL Server 5.1 as a DBMS. We developed the system, everyone was very pleased with it, and it was time to implement it. Without any problems, we, with one of our administrators, came to the office of the institution, installed MySQL 5.1 on the aforementioned server, deployed the database on it, installed the client application on the working machines, started the deployment process. Employees of the institution were very pleased with the system, but mastered it slowly due to workload, meetings, meetings, citizens' appeals, etc. Slowly entered information, but strongly “stretched pleasure”. It was necessary to start thinking slowly about the backup / recovery strategy, but I was still thinking that this was not really my business, because I’m not an admin, but a developer. But the admins it was absolutely indifferent. I consoled myself only with the fact that while there is little information, there will be more, and if no one starts doing backups, I'll start. Looking back, I now see how easy it was to predict subsequent events.
After a while, one of the key employees was overtaken by “enlightenment”, and in a very short period of time a significant amount of information was entered into the system, almost all the information that could be entered at that time. I didn’t even think about it, I thought that they were also slowly looking at the system, without really introducing anything, working for themselves, as before, only with their own papers. The employee also quickly entered the information and went on vacation for a month.
Main fighting
And now, another “fine” day has come, the server has fallen. Moreover, not in software, but in hardware - the hard disk “flew”. Then the hard drive was handed over to the data recovery company, and much of the information was recovered from it. However, admins, while restoring the server itself, restored only all the information from disk D, no one remembered about our MySQL (by “lucky chance” it was put on disk C). All this time I have been quietly doing other projects for myself, I did not even know about these events.
Some more time passed, and the aforementioned employee of the institution goes from vacation. He calls me and says that he cannot log in. I, as usual, ask what message the system gives, and so on, I immediately understand that there is something wrong with connecting to the database. Further, I already learn this story from her with the fall of the server, with the fact that there is “something changed”, and my understanding becomes even deeper: now I realized that our database is not on the server at all. And, as it turned out, there is neither a base, nor the DBMS itself. I ask admins what was there after all, what was restored, what is not ... I come to the disappointing conclusion that there is no base, and the big question is whether it will be possible to restore it. I take myself a copy of the information recovered from the killed hard disk and begin to dig into it.
First of all I look for the Program Files folder, where MySQL is placed by default. I do not find it, but I find a folder with MySQL data files, in it is a folder with the name of that very database of ours. 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 was entered. Binary logs were also not included, and this option automatically disappeared. I start thinking about this issue and remember that you can look at the developer base on our test server, which I did. There I saw that most of the tables - and, most important - work on the InnoDB engine, which means that their records are stored separately in the ibdata * file (in my case, only ibdata1 ). I go up a level and see this coveted 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 “krakozyabr”, I also saw pieces of the information itself, and then I realized that it was possible to restore the base, 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 without a fight nobody will give me, I decide to install MySQL Server 5.1 and MySQL GUI Tools directly to my developer machine running under Windows XP. So, everything costs, the DBMS is running, and thinking about how to approach the task, I decide first to export the database creation script from the test server, and then start it on my machine. Now I have an empty database with a completely identical structure and all stored procedures. I stop the MySQL service, and copy all the contents from the folder with the name of our database from the fallen server to a similar folder freshly installed locally in MySQL (the folder with the name of the database, nested in the data folder, located along the way to store the data files specified during the MySQL installation) . When setting up a local MySQL Server, I decided to specify a separate folder for the InnoDB data files, to which I discard the restored ibdata1 file. After that, I transfer InnoDB-log files ( ib_logfile0 and ib_logfile1 ) directly to my data folder, where they should be by default.
Another attempt to start MySQL again failed. Having no real combat experience in administering the DBMS (except for the experience gained in the courses, but, again, on other DBMS) I decided to go through the experiment, although now I understand that it was not at all necessary. 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” files with logs from the fallen server and see that it is significantly different and is 81 MB. Well ... I stop the server, and go through the MySQL Administrator to the settings of startup variables (Startup Variables), InnoDB tab. I set the size of the log files to 81 MB and throw back my “own” log files. MySQL started, but I did not see the data in the tables of interest to me (although in the tables that worked on the MyISAM engine, they were already restored). Having a little fumbled over the network, I read that MySQL should be started in recovery mode, for which you need to set the starting variable InnoDB_force_recovery to 6. I tried to do it through MySQL Administrator (it's clear that you can set parameters through the GUI, and you can also edit my .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, “then we will try through the console, and once again we explicitly set the innodb_force_recovery parameter”.
I launch the Windows command line and enter:
> mysqld --console --innodb_force_recovery=6To which I get in response:
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.There was no special sense to sin on the antivirus, and I decided to stop the execution of the command with CTRL + break, then repeat the command again with the Task Manager enabled. Before running the command, I looked through the process list to make sure that no instance of the MySQL service was running. And again repeated the command. Having seen the same message, I looked into the Task Manager and saw that for some reason two processes were running at once with the name mysqld, and one was running as a system and the other on behalf of my user. After killing the system process, I “broke a vicious circle,” and the implementation went further:
> 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 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 test shot
“Without departing from the cash register,” I make a dump of the database in order to restore it to the working server later - the very hero of this “fairy tale”, who just had to change the hard drive and reinstall the system.
> mysqldump --routines -u "user" -p db_name > [path\]db_name.sqlI enter the password and get the script for creating and populating all the tables in the database, as well as all the 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 myself, configure it, create an empty database and dump a dump into it:
> mysql -u "user" –p db_name < [path\]db_name.sqlThere was nothing to change in the settings of the client application, since The ip-address and network name of the server remained the same as they were before the fall. Signed in, checked, and - oh, a miracle! - All information is in place. Here and the fairy-tale is the end ... but let us dwell on the “morality of this fable."
Conclusions and Conclusion
For myself, I once again understood several important things, namely:
- The backup / restore strategy should be determined already at the development stage;
- In terms of backup / recovery, do not rely on admins if you can do it yourself; and even more so if it will not be difficult for you to automate this process (which can be done at the primitive level by regular means);
- Organization in the enterprise and clear regulation of the duties of each individual employee in each specific project are very important when dealing with IT; and modern standards for the division of labor in IT enterprises are very, very relevant and have not grown from scratch;
- And yet, if the base has flown and there is no backup, this is not a reason to immediately despair and give up.
Initially, I thought simply to give a brief instruction on what and how to do in this case, but, first, such instructions, in principle, are available on the network; and, secondly, I decided to describe everything in more detail as it was, with “pitfalls” and errors; and also considered that this form of presentation may be somewhat more interesting. In fact, I am sure that a minority will encounter similar pitfalls (more precisely, small pebbles), but nevertheless, I consider it important that they be described.
And, finally, a brief memo-recipe of what to do in a similar situation, when only the InnoDB data and log files are left from the working database with InnoDB tables:
- Substitute data files and descriptions of tables into the data folder ( * .frm , * .MYI and * .MYD files, all in the folder with the name of the database - that is, we copy this entire folder);
- In the folder with the 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 the InnoDB data) we drop all ibdata files * . Here, probably, it should be noted that the file size should correspond to the one specified in the settings. If the sizes are different, you can do this: delete the file (or files) created by the server manually, and create a new (or, respectively, new) with a size equal to the size of the existing one, then start the server and stop it, and replace the files created by it. To facilitate this operation - so that you do not have to play with the settings manually - maybe MySQL Administrator. Or you can make it even easier - just change all the files (or a file, if it is one), and in the my.ini / my.cfg settings file, just change the size - in theory it should work.
- We drop the InnoDB logs (or replace them if they already exist) where they should be (by default, this is the same data folder where data and description files are stored, but this, again, can be changed in the settings);
- We set the size of InnoDB logs to the size of our logs (if manually - then in bytes, if in MySQL Administrator - in MB);
- Start MySQL daemon
> mysqld --console
attentively we look at the log, the service must be started and recovery is performed; - If the recovery does not occur, then in the configuration or parameter set innodb_force_recovery = 1 and try to start. If it fails, set innodb_force_recovery = 2 and try to start. Etc. until 6.
- And, finally, we look at our data on the spot, and if on the spot, we dump the database, which we then deploy to a working server.
A similar operation on Linux or another Unix-like system will look similar. In addition, as far as I understand, the data files and MySQL logs (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, and on OS X, and any other system where MySQL itself works.
Links
InnoDB Engine Documentation
In addition
PhantomTLT in the comments suggests (and, based on his advice, I slightly adjusted the memo above):
If you have all the datadir preserved, then no empty databases with identical structures need to be created. Simply replace all the files and adjust the size of the InnoDB logs.
Next, start MySQL and carefully look at the log. Should start and perform a restore.
If the recovery fails, then we put innodb_force_recovery = 1 in the config (http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html) and try to start. If we don’t start, set innodb_force_recovery = 2 and try to start. Etc. until 6.
If you immediately start with innodb_force_recovery = 6, then you risk getting a database in an inconsistent state. Those. integrity (consistency) of data may be compromised.
If you have reached innodb_force_recovery = 6 and this did not help, then it is very sad - you need to restore it in a semi-manual mode.
Source: https://habr.com/ru/post/125358/
All Articles