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=1Why? 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 startso 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=4This 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