How I resolved my corrupted InnoDB database

[fableman]

This is a guide for people that normally never work with mysql stuff.

My main problem is that my zabbix InnoDB database get corrupted now and then (sometimes every month , sometimes less, Running under centos , vmware)
(problem started after a few years of collecting data)

I just wanted to share how to repaired my InnoDB and got my Zabbix up and running again.

(Iam using Centos and webmin installed)

1. service zabbix_server stop

2. Edit /etc/my.cnf
Add this line to the config: innodb_force_recovery = 4

3. service mysqld restart

4. If you don't have webmin installed you can download it from www.webmin.com or use any other mysql tool.

Backup the databse called zabbix

Example: Successfully backed up 236040666 bytes from database zabbix to file /root/zabbix.sql

!!!Make sure you made a full backup and know where you placed it!!!

5. Edit /etc/my.cnf
Remove the line by adding a # infront of it. #innodb_force_recovery = 4

6. service mysqld restart

7. Enter webmin or any other mysqltool and DROP zabbix database (will remove all data)

8. Create a new database and name it zabbix

9. Enter the new databse named zabbix and perform the command: execute sql file and select the backup you made in step 4 (/root/zabbix.sql)

This step will take some time if you have lots of data. (2-15 mins)

10. service zabbix_server start

11. done, errors gone, data base is fixed, atleast for me!
Good luck!

[jluvs2ride]

Reviving an old thread, but;

I've had a Zabbix Proxy running at a remote site for some time and performing very well.

Yesterday I spent a good deal of time configuring devices discovered from this site to the Zabbix server. I connected to the Proxy, made a minor change in the my.cnf and rebooted the proxy.

Now mysql absolutely refuses to start. I reverted the change to the original default, but mysql absolutely refuses to start. The logs point to InnoDB corruption. A number of errors appear in the log, including "Database page corruption on disk or a failed file read of page 5" and this one; "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)"

I have tried starting mysql with each of the innodb_force_recovery options 1 - 6 and mysql still absolutely refuses to start.

I spent the day yesterday researching recovery options and attempted all that seemed applicable but nearly all seem to depend on being able to start mysql in some fashion or other.

This proxy has been running about a week, I think deleting the existing database and starting over might be my best bet but I'm unsure exactly where to start to do this.

Any ideas?