I updated a drupal 7 install without much thought, and I didn’t have a backup. Arg!!! Why would I do that? So it messed the datatables up royally, and I thought no worries, I am backed up with my server restore feature. But for some reason R1 backup was not working properly to restore, try as I might I kept coming to the same error.
AGENT: !InnoDB table Restore failed, mysql exited with error.
What does it mean, well, I am not exactly sure, but the data wasn’t restored. I started to work on the the server and then I remembered that working on a live server isn’t always the best idea. I found the backup restore files that R1Soft had sent back to the server in /var/cache/buagent
I downloaded the whole folder, and was determined to figure out how to restore it locally first. I found an answer here.
I am using Navicat, and MAMP PRO on a mac.
I created a new database in Navicat with the exact name of the server database. I went into the MAMP folder for database storage which is: /Library/Application Support/appsolute/MAMP PRO/db/mysql/… If you can’t see it on your root of your mac, go into the root folder and then select Go to Folder from the Go menu and type in library. Voila you are in the root library folder.
I copied the .frm files into the directory of the new database. I renamed the ibdata1 to ibdata1-old and overwrote the ibdata1 with the server backup copy.
After starting up mysql in Mamp Pro, all of the tables were restored to their previous state. I could then take this copy and send it up to the server to restore it.
To clean up the files, I used Navicat to dump the new database file to an SQL file, I removed the copyied ibdata1 file and restored the one that was there originally. Then I restarted Mysql, and then executed the dump file from earlier. This brought all of the tables and data back into mysql.
The most important step is to simply copy the contents of the /data folder and the ibdata1 file. Hope this helps!
It was a bit of an ordeal, and I hope to have the database restore feature working perfectly again before I need to do all of that again.
Lesson learned: Make a backup before major changes, and test before working on a live site.
Update: It turns out it was a bug int he r1Soft resotre functionality. Hurray it wasn’t my fault, but it took r1Soft and iweb.com over a week to conclude the bug was there.
