Howto Recover crashed InnoDB tables in MySQL
After the server crash, last week. I'm busy with recovering some of
the data.
I was fortunate that our dedicated technical Support in valueweb at
last has
succeeded to bring our server with a failed hard drive online for a
couple of hours.
I couldn't login to the server via SSH, though luckily I could
login through rsync as well
as through sftp.
The first thing I did right after I was able to access the server
was issuing:
# rsync -azve ssh root@my-broken-server:/var/lib/mysql/
mysql
Where /var/lib/mysql contained all my sql binary data:
I've used a couple of similar rsync commands to also backup my
/usr/local/mysql/
I attempted to also copy some of the other websites data
(php,html,css,javascript,jpegs) etc.
But I could copy only partly some of the files before the server
hard drive started loosing temper and failing up
I have to say I've prayed the Lord Jesus to help me recover this
data from the MySQL server since it was so vital for our company to
have a working copy of the sql data.
Well Hallelujah! For The Lord has heard my prayers and now I have a
shiny binary copy of all the MySQL databases.
Next I proceeded extracting the needed database. To succeed with
that I had to run the old MySQL server on a different
port from the standard SQL port 3306. I chose to use port 3308,
here is the command I used to bring up the old sql server as a
second SQL server on the host
# where as you can see -P defines the server to run on port
3308, --skip-grant-tables is an option
# I had to provide because I couldn't remember the root mysql
password
# --datadir specified where your mysql binary data is located
# /root/mysql/bin/mysqld_safe -P 3308 --skip-grant-tables
--datadir=/usr/local/backup/mysql \
--pid-file=/tmp/mysqld1.pid --socket=/tmp/mysqld1.sock
--log-error=/var/log/mysqld1.log
The command above won't work at first, in the
/var/log/mysqld1.log I have noticed the following
error:
[ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only
After checking in Google I've realized there had to be something
wrong with my /usr/local/backup dir permissions
To fix that I had to issue chown -R mysql:mysql /usr/local/backup
dir
At first my mysql binary data was located in /root/backup/mysql;
Thus even though I changed the permissions
of /root/backup/mysql with chown the mysqld_safe won't run still,
after some time I've found out that
the permissions issues I was experiencing was because the user
"mysql" cannot enter to the /root directory, because
of root directory has permissions making it accessible only for
root user.
After moving /root/backup to /usr/local/backup and issuing the
mysqld_safe command above all worked like a charm.
Next I continued dumping some of the databases:
# /root/mysql/bin/mysqldump -P 3308 db -r
db_dump.sql
Unfortunately one of the databases had some corrupt tables:
Table './database/table' is marked as crashed and should be repaired
I tried to repair the corrupt table with the following
command:
mysql> repair table table_name;
mysql> repair table table_name extended
Mifrotunately that couldn't fix my corruped table:
I followed some online suggestions to use the
myisamchk
repair tool:
# /root/mysql/bin/myisamchk
/usr/local/backup/mysql/database_name/table_name.MYI
myisamchk has informed me that the table is corrupt and urged me to
execute
# myisamchk -r or -o as a fix.
So I executed:
/root/mysql/bin/myisamchk -r
/usr/local/backup/mysql/database_name/table_name.MYI
Happily this time the broken table got fixed so I successfully
dumped the table using mysqldump.
Thanks God! :)