Today one of our hosting servers crashed and when we managed to get it up and running, MySQL wouldn’t start.

I started reading to see what could have happened and in the log I came across this:

“The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash.” So there I went and I after reading through the whole page, I came out with nothing clear!

So I went back to the log and saw this:

 InnoDB: Database was not shut down normally!
 InnoDB: Starting crash recovery.
 InnoDB: Reading tablespace information from the .ibd files...
 InnoDB: Restoring possible half-written data pages from the doublewrite
 InnoDB: buffer...
 InnoDB: Doing recovery: scanned up to log sequence number 135274039561
 InnoDB: Database page corruption on disk or a failed
 InnoDB: file read of page 54838.
 InnoDB: You may have to recover from a backup.

I then decided I had to recover MySQL. Being a thing I had never done, I started reading as much as I could and I found this very interesting page: http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/ which actually got to the problem of solving corrupt databases but not the problem I had: MySQL as such was corrupt!

These are the steps I took:

1. I stopped the MySQL service with the following command (BTW, I was running  CentOS 6):

service mysqld stop

2. I edited the file with the MySQL configuration:

nano /etc/my.cnf

(vi /etc/my.cnf will also work, of course)

3. I added the following line:

innodb_force_recovery=1

Why? Here are the main options for this parameter: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

4. I started MySQL

service mysqld start

so MySQL started and after a few seconds it crashed again!

5. I then changed the innodb_force_recovery value to 2 with no result. Then I tried 3 and the same: crash! So in the end I left this value:

innodb_force_recovery=4

This is the maximum level of innodb_force_recovery at which no data is lost. Levels 5 and 6 could lead to a loss of data.

At this point MySQL was up and the databases running but no changes coud be done to any database. (Remember that any innodb_force_recovery level different from 0 puts the databases in “read only” mode).

In the mysqld.log I had thousands of lines like these:

 InnoDB: A new raw disk partition was initialized or
 InnoDB: innodb_force_recovery is on: we do not allow
 InnoDB: database modifications by the user. Shut down
 InnoDB: mysqld and edit my.cnf so that newraw is replaced
 InnoDB: with raw, and innodb_force_... is removed.

At this stage it was clear MySQL was clearly corrupt and I had to recover it.

This was the plan:

– Backup all the databases.
– Reinstall MySQL
– Recover all the databases.

6. I started by backing up all the databases I had. Just to be safe, I did it in two different ways: a full backup with all the databases in one file and a full backup with every database in its own file.

I wanted all the backups to be in one place, so I created a special folder for this purpose:

mkdir /databasebackups

To have all the databases in one file I ran this command:

mysqldump -u root -p --all-databases > /databasebackups/alldb.sql

(If you don’t have the root MySQL password, you can change it via any of these methods: https://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html)

And just in case, I ran this little script (written by JRuzafa – http://stackoverflow.com/users/944571/jruzafa) to have every database in its own file:

#!/bin/bash

USER="root"
 PASSWORD="here-goes-the-root-mysql-password"
 #OUTPUT="/databasebackups/independentdatabases"

#rm "$OUTPUTDIR/*gz" > /dev/null 2>&1

databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
 if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
 echo "Dumping database: $db"
 mysqldump -u $USER -p$PASSWORD --databases $db > $OUTPUTDIR/`date +%Y%m%d`.$db.sql
 # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
 fi
 done

If you don’t know how to save it, just create a file called dbbackup.sh and copy and paste the content. Then run sh dbbackup.sh

7. With the databases backed up, I now had to reinstall/recover MySQL. The first thing was to stop the MySQL service:

service mysqld stop

8. I wasn’t 100% sure if I was going to mess everything up, so just in case I also backed up the actual MySQL files:

ibdata1
ib_logfile0
ib_logfile1

with the following command:

cp /var/lib/mysql/ib* /databasebackups/

9. I then deleted all the MySQL databases. I went to the database location:

cd /var/lib/mysql/

and deleted all the database folders and their contents:

 rm -rf here-goes-the-name-of-your-database-1
 rm -rf here-goes-the-name-of-your-database-2
 rm -rf here-goes-the-name-of-your-database-3
 rm -rf here-goes-the-name-of-your-database-4

but DO NOT DELETE the mysql folder!

Once you’ve deleted all the databases, in your database folder you should have the following content:

ibdata1
ib_logfile0
ib_logfile1
mysql/
mysql.sock

The last step is to delete the innodb file and logs:

 rm ibdata1
 rm ib_logfile0
 rm ib_logfile1

after this the content of the database folder should be:

mysql/
mysql.sock

10. At this stage all I had to do was start MySQL and import all the databases. To start MySQL I ran the following command:

service mysqld start

And I checked in the log that it had started correctly:

 [Note] /usr/libexec/mysqld (mysqld 5.5.47) starting as process 11927 ...
 [Note] Plugin 'FEDERATED' is disabled.
 InnoDB: The InnoDB memory heap is disabled
 InnoDB: Mutexes and rw_locks use GCC atomic builtins
 InnoDB: Compressed tables use zlib 1.2.3
 InnoDB: Using Linux native AIO
 InnoDB: Initializing buffer pool, size = 128.0M
 InnoDB: Completed initialization of buffer pool
 InnoDB: The first specified data file ./ibdata1 did not exist:
 InnoDB: a new database to be created!
 InnoDB: Setting file ./ibdata1 size to 10 MB
 InnoDB: Database physically writes the file full: wait...
 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
 InnoDB: Setting log file ./ib_logfile0 size to 5 MB
 InnoDB: Database physically writes the file full: wait...
 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
 InnoDB: Setting log file ./ib_logfile1 size to 5 MB
 InnoDB: Database physically writes the file full: wait...
 InnoDB: Doublewrite buffer not found: creating new
 InnoDB: Doublewrite buffer created
 InnoDB: 127 rollback segment(s) active.
 InnoDB: Creating foreign key constraint system tables
 InnoDB: Foreign key constraint system tables created
 InnoDB: Waiting for the background threads to start
 InnoDB: 5.5.47 started; log sequence number 0

and 3 new files were created in the database folder (remember, /var/lib/mysql):

ibdata1
ib_logfile0
ib_logfile1

11. To import all the databases there are 2 ways:

a) To import the alldb.sql file that we created in step 6. Here is the command to do it:

mysql -u root -p < /databasebackups/alldb.sql

No progress is shown, so you’ll just have to wait until the command finishes its execution.

b) To import all the independent databases with each one having it’s own .sql file (stored in the /databasebackups/independentdatabases folder)

The long way would be:

 mysql -u root -p < /databasebackups/here-goes-the-name-of-your-database-1
 mysql -u root -p < /databasebackups/here-goes-the-name-of-your-database-2
 mysql -u root -p < /databasebackups/here-goes-the-name-of-your-database-3
 mysql -u root -p < /databasebackups/here-goes-the-name-of-your-database-4

And the short way would be using a script written by Corey (http://stackoverflow.com/users/290699/corey) which can be found here:  https://gist.github.com/tenold/aa5e107d93c0f54436cb

#!/bin/bash

USER="root"
 PASSWORD="here-goes-your-root-mysql-password"

FILES="/databasebackups/independentdatabases/*"

for f in $FILES
 do
 echo "Processing $f file..."
 mysql -u $USER -p$PASSWORD < $f
 done

And after all this process, your MySQL server and databases should be up and running!

Please leave comments if you wish to!

Daniel