MySQL Forums
Forum List  »  InnoDB

Corrupted Magento InnoDB Recovery
Posted by: Eddie Shipman
Date: May 17, 2016 10:34AM

Below is outlined my plan to restore the MySQL DB on our Production server that has gotten a corrupted InnoDB file.

1. Create TAR archive of /lib/mysql
tar –zcvf mysql-data-dir.tar.gz /lib/mysql

2. Perform a mysqldump of all databases.
One issue we have here is that the Magento tables on our Production server are still MyISAM because they converted when I brought it up from being down. I have a correct dump of the structure of the DB on another server that can be used to rebuild the correct structure of the Magento tables
mysqldump -AER > /root/recovery_dump.sql

3. Edit recovery_dump to modify all Magento tables to InnoDB or cut all Magento table creation code and use other structure dump.

4. Drop all affected InnoDB databases.
mysql> DROP DATABASE na_db1;

5. Stop mysqld, after disabling innodb_fast_shutdown, which ensures a clean, full shutdown is performed.
mysql -e "SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql stop

6. Relocate the InnoDB data and log files
mv /var/lib/mysql/ibdata* /tmp/
mv /var/lib/mysql/ib_log* /tmp/

7. Comment out or remove any innodb_force_recovery entries you currently have in /etc/my.cnf.
sed -i '/innodb_force_recovery/d' /etc/my.cnf
No real need to do this since we don’t have the innodb_force_recovery flags set in my.cnf

8. Start mysqld and monitor the logs to ensure that it comes online and initializes the data and redo log files appropriately
nohup /etc/init.d/mysql start & tail -f /var/lib/mysql/`hostname`.err

9. Restore the dump, once your confident that MySQL is still functionally online and ready to import data into.
Code: mysql < /root/recovery_dump.sql

10. If any errors occur, restore the archived backup
tar –zxvf mysql-data-dir.tar.gz –C /lib/mysql

1. I know that InnoDB uses the *.frm files for the structure but what will this do to the Magento .MYD and .MYI files? Can/should I just remove them before I run the structure recovery script?
2. We are using InnoDB on both 2 other servers however, in their respective my.cnf files I see that all the Innodb variables are commented out. They are also commented out on our production server. Is that going to hurt anything? What if we enable them and set them to the correct values? What would be the correct values to use for Magento?

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

3. Does this look like a safe and effective plan to recover from this problem? It is causing numerous issues with Magento orders, such as declined payments also creating orders, order confirmation emails being sent blank, etc, making me have to manually process a lot of orders that were normally automatically processed.

Options: ReplyQuote

Written By
Corrupted Magento InnoDB Recovery
May 17, 2016 10:34AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.