Restore WP mySQL database from files – Windows server

  • This is what happened?
    I have a Windows Server 2012 that crashed which had WordPress on it and the MySQL 5.1 database for WordPress with no backups. Is there no way to recover from this with a database backup even if you have all the files?

    What I have tried!
    Attempt 1 recovery (try to add the database from the crashed server to the current mySQL instance):
    1.I reinstalled a new MySQL 5.1 on to the new Windows Server 2012 system and I moved just the /data/wordpress954 folder over “completely” from the crashed system (which was working fine) into the /data folder of the new MySQL install.
    2.I start up MySQL and login just fine. I see my database. I see all the tables. However, if I go to a table to view it and I get the following error.

    ERROR #1146 = Table ‘Wordpress953.wp_commentmeta’ doesn’t exist

    Attempt 2 recovery (try to completely replace the current mySQL instance files from the crashed server on top and overwrite the new instance):
    1.I completely move over the /data folder from the old server replacing it on the new server
    2.I completely move over the /program files/mysql folder over from the old server onto the new server
    3.I completely move over the /program files (x86)/mysql folder over from the old server
    4.I startup mysql and go to phpmyadmin and get the following error when I try and login:

    ERROR #1045 Cannot log in to the MySQL server

    I figure I just need to fix the root password so I attempt to change the root password using this process https://www.youtube.com/watch?v=dyc5b3yT2tI but it fails to even start mySQL to change the password.

    I have tried everything I can find now for over 3 days straight to recover a mySQL database on Windows. Please help with an answer of something I haven’t tried….

Viewing 7 replies - 1 through 7 (of 7 total)
  • Dion

    (@diondesigns)

    First, take a look in the database folder. Does it have a file named ibdata1? If so, then the news is bad…in all likelihood your database is lost forever.

    If it doesn’t have a file names ibdata1, then your tables are all MyISAM, and you should be able to move the datbase folder to a new MySQL installation. However, you should also move the /data/mysql directory along with your WordPress database directory.

    Your attempt #1 does have one problem. The error message references a WordPress953 database, but the folder you said you moved was named wordpress954. Maybe that is also an issue?

    Finally, if you get this fixed, make sure to use phpMyAdmin (or the mysqldump utility) to make a backup of your database!

    Thread Starter salescart

    (@codeaholic)

    953 -vs- 954 = typo (sorry)

    YES, it does. I have ALL of the files including the ibdata1, ibdata0, .frm, etc. files. In both attempt 1 and attempt 2, I am moving the WHOLE /data folder with all files including the ibdata1 file. In fact, there is a .err file created in attempt1 complaining about the isam id problem in the log file.

    It’s simply not working.

    In attempt 1, I am NOT moving /data/MySQL. I can try that. However, in attempt 2, I am also moving the /data/MySQL!

    It is NOT working in either case. Of course, Attempt 2 might be working but I have no way to start MySQL or login to see the database tables. So who knows in that case. I changed the MySQL root password only about 2 weeks ago so I am 100% correct of what it was and I made the new instance with the same root password.

    I’ll retry attempt1 moving both the WP MySQL data folder as well as the MySQL data folder ONLY

    Dion

    (@diondesigns)

    If you have an ibdata1 file, that means your tables are InnoDB. While InnoDB has a number of advantages over MyISAM, its biggest disaadvantage is that tables almost never survive a system crash.

    You should first go through the /data/mysql.err file (or whatever it’s named in your installation) to see exactly what the problem is. Then read the following documentation that describes the only way you might be able to recover your tables:

    https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

    Good luck!

    Thread Starter salescart

    (@codeaholic)

    This is NOT a system crash! The hard drive was operating at half speed we believe for some reason so we just moved to a new drive. It was taking 45 mins to reboot and an hour to log in with RDP.

    I have ALL the data on the old drive. It is “as if” the mySQL went down for a reboot. No data has changed in the WordPress953 files since 2/22/18 and in the mysql files since 5/31/18 the day before we even had issues. That is the day I changed the root password. It doesn’t make sense to me why I can’t just replace the files and reboot since I have ALL the data. BTW, I have recovered MS SQL and a dozen of other databases like this before no problem and restored 3 MS SQL databases this time as well from just the files.

    Attempt 3:
    I shut down the mysql server, I put the /mysql and the /wordpress953 folders in the /data folder and the files in the root ibdata1, ib_logfile0, and ib_logfile1 as you indicated. I rebooted the system. I tried to login and I get the same as Attempt 2:

    ! #1045 Cannot log in to the MySQL server

    Where is the root password saved? In ibdata1? I would have thought this is saved in the /mysql. Isn’t that the master tables?

    Thread Starter salescart

    (@codeaholic)

    I tried again to reset the password for mySQL but it fails. Here is an image of what I see:
    Junk

    I don’t understand why I can’t reset the mysql password???

    Thread Starter salescart

    (@codeaholic)

    Ok, I got it!!
    The procedure is.

    • Install vanilla mySQL on new server (I had already done that)
    • Stop mySQL service
    • Move the /data/wordpress953 folder over “intact” “as is”.
    • and move ONLY the ibdata1 file and ib_logfiles off of the root over.
    • Leave the /mysql data “as is” from the vanilla install
    • Start mySQL service

    It was basically an “incarnation” of what I was doing but you have to only change certain things. The ibdata1 file in the root went with the /wordpress953 folder but apparently not with anything else.

    Thread Starter salescart

    (@codeaholic)

    Ok, it doesn’t look like I am 100% there yet. The database is restored but WordPress is giving the error:
    Error establishing a database connection

    The issue is I have the “vanilla” /mysql database. When I look at the tables_priv in /mysql it is empty. I’m guessing somehow I need to get the privileges for this WordPress database into /mysql right?

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Restore WP mySQL database from files – Windows server’ is closed to new replies.