scott.hodson.blog

A server that had some MySQL datases (including the one for this blog) on it became unbootable after performing some Windows Updates, so I had to migrate everything to another server. When I went to get the mysqldump’s of the databases a few of the database dumps were blank for some reason. I’ll have to figure out why later. So now I had no backup when I thought I did.

Luckily, I figured out how to pull the raw data files MySQL uses and was able to restore the databases using the native DB files. Here are the steps I took. This was a MySQL 4.1 server.

1. Each DB is stored in a folder in the “data” folder where MySQL Server runs.
2. Copy the folders of the databases you want to store to another location
3. Since the server I wanted to migrate to was running MySQL 5.0 and not 4.1, to get access to my data I had to install an instance of MySQL 4.1 on another machine, stop the MySQL service, then copy the data folders of my databases to this other 4.1 server’s data folder. Then restart the MySQL server.
4. Then, with the databases back online, I attempted to perform a mysqldump, but I got errors that the “.cnf” on some of the tables were not available. Then I tried doing a backup from the MySQL Administrator GUI tool and got a similar error message when it showed the list of tables. The tables that didn’t have errors were MyISAM tables, and the ones that did have errors were InnoDB tables. From the MySQL website about MyISAM Table Storage:
   

   Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

   These three files for each table are stored in the database folders that we copied. The reason some of the other tables were not working was that InnoDB tables are stored differently. Again, from the MySQL website about InnoDB Table Storage:

   If you specify no InnoDB configuration options, MySQL creates an auto-extending 10MB data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logfile1 in the MySQL data directory. To get good performance, you should explicitly provide InnoDB parameters as discussed in the following examples. Naturally, you should edit the settings to suit your hardware and requirements.

5. These InnoDB data and log files are stored in the root of the MySQL data folder and I was getting the errors because I had not copied those over as well. The new machine that has 4.1 on it already had these files but I didn’t care since I didn’t have anything important on that server’s databases so I just deleted the new server’s InnoDB files and replaced them with the ones from the server that I was trying to recover.
6. When I started the server with these updated InnoDB files I got another error, something about the file length not matching the correct file length. So I just deleted the log files and left the ibdata1 file there by itself. When I restarted the server hat state t came up fine. I quickly did a mysqldump of these databases then restored the dumps onto the server running MySQL 5.0 and I was back up and running

Now I need to figure out why the dumps weren’t executing properly in the first place, which caused me to go through all of this. This teaches an important lesson that I already knew: practice disaster recovery so when a disaster actually hits you’re sure that you have the knowledge, data and files that you need in the correct state that you need them in. If I had practiced recovering from a server crash and tried restoring my databases from my backups I would have noticed that some of the databases weren’t creating good MySQL dumps and I could have prevented myself from doing a MySQL restore without good .sql dumps.