HOW TO RECOVER DATA FROM
CRASHED MYSQL SERVER
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.
No comments:
Post a Comment