XAMPP – MySQL Corruption

Having used XAMPP for a time on a Mac, I have encountered a few situations in which my databases have become corrupted. I cannot say why, and if I knew I would definitely attempt to avoid this, as it often sets me back a reasonable amount of time.

Whilst this is not an instructional guide on recovering your data, this is a way to simply try and get your environment working again once such a situation has occurred as I have been left unable to start the MySQL server on my local machine and resorted to all sorts of re-installations of software in an attempt to get my XAMPP environment operational again.

So if faced with an inability to start the MySQL Database service, consider implementing the following:

1. Look to /Applications/XAMPP/xamppfiles/var/mysql/ directory and there should b a file that ends with .local.err. Open this file and this will provide some possible insight into what may be going on. Often in my situations I would see something like:

The exact text will differ, but this may provide with some assistance.

2. Then try using: innodb_force_recovery = 1 after the [mysqld] line in the /Applications/XAMPP/xamppfiles/etc/my.cnf file. This may require use of sudo when modifying this file; for example: sudo vi /Applications/XAMPP/xamppfiles/etc/my.cnf and then enter the password. Save the file; if using vi this is achieved with :w command (more information here: http://www.howtogeek.com/102468/a-beginners-guide-to-editing-text-files-with-vi/).

3. Retry starting the XAMPP MySQL Database server. Check the output of the .local.err file to see what information has been introduced to the end of this file.

4. Database repair is possible with the use of the command: sudo /Applications/XAMPP/xamppfiles/bin/mysqlcheck -c –all-databases. This will go through all the DB instances and attempt to repair them.

5. Sometimes this will not work however, and you just need to cut and run. If that is the situation, then it is possible to ensure that the MySQL Database service is disabled and then manually delete the database directories. When you then restart the MySQL Database service these will not longer be visible in the list.

6. Restore the database from a backup, and make any modifications that may be necessary if it is a remote backup, to ensure that the local parameters are set.

Hopefully that will be enough to get the system functional once again.

Leave a Reply

Your email address will not be published. Required fields are marked *