Activities

January 2011
M T W T F S S
« Dec   Feb »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

How do I restore mysql Innodb database from hot copy backup

Recently I had a severe server failure. Nagios showing “server down” alert notification. Sooner I tried to ssh and able to login. Found that none of the process can’t write the files on root drive as it is mounted as read only mode.

First of all, I had done a quick back up with the latest web files and I wasn’t able to dump the mysql database as the mysql server was not started because of this read only file system. So I adopt mysql hot copy backup. Unfortunately Cpanel servers are using mysql innodb storage engine by default.

I have replaced all the “mysql” innodb hot backup files in the location where “datadir” used. Try to start the server but it won’t allow me to start. no luck 🙁

The solution is,

Recovering Innodb databases

We need to point out the database engine to check the innodb health before starting to use it. This would help to guide mysql to repair the innodb before starting once it got corrupted.

Go to the recovery mysql server,

[root@rc-090 ~]# service mysqld stop
Stopping MySQL:                                            [  OK  ]
[root@rc-090 ~]# mv /var/lib/mysql /var/lib/mysql_dd
[root@rc-090 ~]# cp -r /root/mysql/ /var/lib/
[root@rc-090 ~]# chown -R mysql.mysql /var/lib/mysql
[root@rc-090 ~]# chmod -R 700 /var/lib/mysql

Open my.cnf file and add a line as follows,

i<strong>nnodb_force_recovery=4</strong>

Then try to start the mysql server.

[root@rc-090 ~]# service mysqld start
Starting MySQL:                                            [  OK  ]

It’s worked !!!

The next issue you facing is how do access the database since you haven’t any idea about root password. it can be operated by the user account earlier it used which known to you.

So I need to get mysql either root login or access the all database,

A. MySQL Password less login

[root@rc-090 ~]# killall mysqld  mysqld_safe
         [root@rc-090 ~]#mysqld_safe --skip-grant-tables &
         Open another terminal and login to mysql without password,
         [root@rc-090 ~]#mysql -uroot

You’re in !!!! 🙂
# show databases;

C. Reset the mysql root password

[root@rc-090 ~]#mysql -uroot mysql
          mysql> update user set Password=PASSWORD('pass') where user='root';
         mysql> flush privileges;

You may check the mysql error log at “/var/log/mysqld.log”

[root@rc-090 ~]# tail -f /var/log/mysqld.log

A good my.cnf entry should look like. I do not added any optimized settings here
[root@rc-090 ~]# cat /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
user=mysql
innodb_force_recovery=4
max_connections = 500
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld-safe.log
pid-file=/var/run/mysqld/mysqld.pid

How can view the mysql root password on WHM/Cpanel server

In my scenario, I don’t know the root password. MySQL root password can be view at “/~/.my.cnf” on every WHM/Cpanel installed servers. This file help you to login the mysql console mode without prompting a authentication. You just need to copy it on your home directory.
#cat /~/.my.cnf
A sample entry should look like this,

[client]
user="root"
pass="mypassword"

Setting default mySQL storage engine

If you do not want to use innodb storage engine you can add this line in mysql configuration file.

#vi /etc/my.cnf
default-storage-engine=MYISAM

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>