How Innodb crash can happen ?
- Innodb table can crash if there is power lose for your dedicated server.
- Force shutdown of MySQL service can end up in Innodb crash.
- Space issue on the serve can case Innodb crash, if the server space is 100% filled and mysql not able to write on the disk.
- High load on the server can cause Innodb crash.
- High I/O wait on the disk can also cause MySQL innodb crash.
How to determine if innodb is crashed.?
You can determine this by checking MySQL logs, On most of the servers location of the Log is /var/lib/mysql/HOSTNAME.err
You can also use commands:
systemctl status mariadb.service journalctl -xe
Full MySQL backup
Backup MySQL users and permissions
pt-show-grants > /root/pt-show-grants.sql
Backup Full MySQL folder, Make sure MySQL is STOPPED for this step
rsync -a /var/lib/mysql /home/mysql_bkp/
Instructions for full mysql reinstall and recovery
If you can't start mysql, you can try by adding the following code in file /etc/my.cnf.d/server.cnf under [galera] settings
innodb_force_recovery = 1
Now try to start/restart MariaDB
systemctl restart mariadb.service
* If started successfully then you can start with backup, please note that MySQL is now in read-only mode.
* If not starting then try increasing recovery number for one number in each test
innodb_force_recovery = 2
innodb_force_recovery = 3
innodb_force_recovery = 4
innodb_force_recovery = 5
innodb_force_recovery = 6
When the MySQL is started in recovery mode we need to take backup of all the crashed databases and restore it
Create backup location folders
mkdir /home/mysql_innodb_recovery mkdir /home/mysql_innodb_recovery/database_backup
Create the list of databases
mysql -e 'show databases;' | grep -v information_schema | grep -v Database > /home/mysql_innodb_recovery/database_list.txt
* Please confirm that all databases are listed in the file /home/mysql_innodb_recovery/database_list.txt
Make backup of each databases including the database called mysql which is very important.
for db in `cat /home/mysql_innodb_recovery/database_list.txt`; do mysqldump $db > /home/mysql_innodb_recovery/database_backup/$db.sql;done
* this process can take a lot of time depending on your MySQL disk usage.
* Make sure you confirm that all .sql files in folder /home/mysql_innodb_recovery/database_backup/ are not empty.
Drop current databases (We can remove them if the backup was successful)
for db in `cat /home/mysql_innodb_recovery/database_list.txt`; do mysqladmin drop $db;done
Some database won’t get dropped which can be directly removed using the rm -Rf command
rm -Rf /var/lib/mysql/DATABASENAME
Next is to remove ibdata and ib_log files
rm -f ibdata* ib_logfile*
Restarting MariaDB
Remove recovery line innodb_force_recovery from /etc/my.cnf.d/server.cnf and restart MariaDB
systemctl restart mariadb.service
Creating Databases
If the MySQL now works we can start restoring databases, first create databases
for db in `cat /home/mysql_innodb_recovery/database_list.txt`; do mysqladmin create $db;done
Restoring Databases
Now we will restore the backups we have taken.
for db in `cat /home/mysql_innodb_recovery/database_list.txt`; do mysqldump $db < /home/mysql_innodb_recovery/database_backup/$db.sql;done
* This process will take some time, after it is done confirm that there are no any issues in the MySQL logs.
In case of some issues you still have the exact replica of databases in folder /home/mysql_bkp/
MySQL users and permissions are stored in file /root/pt-show-grants.sql
Individual databases backups are stored in .sql files in folder /home/mysql_innodb_recovery/database_backup/
* We suggest to keep all those files few days after the successful restore.