Recover & Restore Mysql Database from Crashed Server

What can be done when Mysql Server got corrupted or crashed and can't able to boot mysql server, Which contain more important databases without proper backup. To avoid this situation take proper backup for mysql server in multiple ways which help you more.


                             


Follow the following steps to recover mysql database from crashed server

Step 1:

Setup a new Mysql Server with good configuration and start the service.

Step 2:

Compress the data from crashed mysql server.

First run the crashed server in recovery mode and find the data directory for mysql server with help of
mysql configuration file and by default /var/lib/mysql  is data directory for mysql server.

root@linuxstorages:~# vi /etc/my.cnf

root@linuxstorages:~#cd /var/lib

root@linuxstorages:~#zip -r mysql.zip mysql

root@linuxstorages:~#scp mysql.zip root@newserver:/root

Step 3:

Now return to the new server. Stop the mysql service

root@linuxstorages:~#service mysqld stop

Rename the new mysql data directory for backup process.

root@linuxstorages:~#cd /var/lib

root@linuxstorages:~#mv mysql mysql_backup

Step 4:

Now extract the crashed mysql.zip file in home directory and move it to default mysql data directory path.

root@linuxstorages:~#cd /root

root@linuxstorages:~#unzip mysql.zip

root@linuxstorages:~#cp -r mysql /var/lib

Step 5:

Change the ownership of the whole mysql directory.

root@linuxstorages:~#chown -R mysql:mysql  mysql/

Step 6:

Now we need to start the mysql service in safe mode to recover the mysql data in .sql format with help of mysqldump command

root@linuxstorages:~# mysqld_safe --skip-grant-tables &

 
 we can able to login mysql server as root without password in safe mode.

root@linuxstorages:~# mysql -u root

Now it's time to dump all databases. we can dump all database in single command but best way to dump is one by one so we can export and restore the important database first.

Create a new directory to save dumped database.

root@linuxstorages:~#mkdir /mysql_backup

root@linuxstorages:~#cd mysql_backup

root@linuxstorages:~#mysqldump -u root databasename > databasename.sql


After taking complete backup stop the mysql service.

Step 7:

Return new server to original mysql data setting.

root@linuxstorages:~#cd /var/lib

root@linuxstorages:~#mv mysql mysql_old

root@linuxstorages:~#mv mysql_backup mysql

Now reboot the server.

Step 8: 

Restore the database from dump.

root@linuxstorages:~#cd /mysql_backup

root@linuxstorages:~#mysql -u root -p

mysql> create database databasename;

mysql> use databasename;

mysql> source databasename.sql;

Now you have recovered your mysql data from crashed server to new server.

Browse More Articles

Tweet

Written by Unknown