[Moved to WP-Advanced]
I have a wordpress site that encountered a database error after server conversion from MyISAM -> InnoDB conversion and insufficient RAM. I have a InnoDB MySQL dump of changes I made on that site. VPS techs returned the server InnoDB ->MyISAM and restored an older backup to get the sites back online. Can I recover my changes to this one site from the InnoDB MySQL dump? Will it then need a conversion to MyISAM?
I thought perhaps I could install a subdomain wordpress. Upload the plugin folder etc, the techs say they can restore that InnoDB MySQL dump database into the empty wordpress. Then assuming it works, I can change the url to the restored site.
1. Can you advise as to how difficult or practical this will be.
2. What potential problems I may encounter.
3. Whether it is worth even trying to do this, or if it would be better to just spend two days rewriting the changes I made (that I hope to recover doing it this way at hopefully less time).
I started getting memory (RAM) issues on managed VPS server. I have over 20 instances of wordpress on the server. I am not very advanced on server issues so asked for help from techs. I know now, I should have increased RAM first. But I was advised to make various other changes first, which the server techs did. They installed memcache and extension, did a PHP rebuild to add in mod_ruid, and switched to DSO, implemented gzip compression and expire rules globally, installed Eaccelerator, recompiled Apache with support for mod_security to block brute force attacks. All these were good changes, but may have put a further load on the server.
Then they made a MyISAM -> InnoDB conversion.
The reasoning was that I should then, wordpress side, implement a cache plugin to reduce the RAM usage. Starting to do this however was my downfall.
I had some work to do for a client, so I spent two days working on optimising his site. I made plugin changes, updated the directory with 10 new posts, deleted venues in the directory that were no longer active, and switched to WordPress SEO and made 40 odd entries into its SEO area, fixed maps and made a few new posts. I turned off the w3tc plugin while I did the work, and turned it back on with memcache after I finished.
I had no trouble while working in wordpress, but in the next few hours after turning the w3tc back on with memcache settings, (not blaming the plugin - the problem may be my settings and def insufficient RAM) I encountering a cannot connect to database problem on the site I had just worked on, after saves started to fail that day MySql crashed repeatedly.
On the site with possible corruption this error message
40609 03:42:56 mysqld_safe mysqld from pid file /var/lib/mysql/host.xxxxxx.com.pid ended
140609 03:49:56 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140609 3:49:56 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
140609 3:49:56 [Note] Plugin 'FEDERATED' is disabled.
140609 3:49:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140609 3:49:56 InnoDB: Compressed tables use zlib 1.2.3
140609 3:49:56 InnoDB: Initializing buffer pool, size = 256.0M
140609 3:49:56 InnoDB: Completed initialization of buffer pool
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
140609 3:49:56 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex b7ea64e300000005000000000000000000000002c9f1318500070000000000000000000000000000000001b4
Host unable to bring site back/restored old backup
Host said "seeing what appear to be issues with what appears to be an unrecoverable InnoDB ibdata table that was causing MySQL to fail to start. I think the best solution is probably stopping MySQL and restarting wtih the new data, taking a backup, and then seeing if we can get the new data working as well." They were unable to remove white screen with new data, so they eventually restored from a backup done just before I made all the changes.
I have a backup of the site files and database within the old MySQL at /var/lib/mysql.
Returned to MyISAM
As I also had minor problems with the mass update software I use for wordpress as well as continuing memory issues, I asked them to roll back to the SQL dumps done prior to the MyISAM -> InnoDB conversion and return the server to MyISAM. More white screens at first which are now fixed. I upgraded RAM Lastly I asked them to remove eaccelerator and instead they recommended and installed zend opcache.
So now I seem to have a stable server. No crashes, faster, and most important happy with wordpress and my software.
I haven't (but will if needed) include a site link as the site is fine, it just needs updating.
Server Apache/2.2.27 PHP/5.4.28 MySQL 5.5 WordPress 3.9.1