MySQL数据库修复

Roy

2016-07-18

把博客服务器搬到香港之后，发现这个供应商的服务器经常自动重启，不过mysql、nginx都设定了开机自启动，所以也就没当事。结果今天出了大问题：服务器ip可以ping的通，但博客就是无法访问，登录到服务器一看，我擦——整个数据盘不见了！由于把网站放在了/home目录下，而现在home目录空荡荡的啥也没有。

联系了客服，说是因为数据盘没自动挂载。等他们处理好后，虽然网站程序文件都找回来了，可mysql数据库却崩了，无法启动并报错：

[root@MyServer default]# service mysql start
Starting MySQL..The server quit without updating PID file (/usr/local/mysql/var/MyServer.pid).                                                       [FAILED]

查看错误日志：

less MyServer.err

...
InnoDB: Cannot continue operation.
InnoDB: You can try to recover the database with the my.cnf
InnoDB: option:
InnoDB: innodb_force_recovery=6
...

完，看到Cannot continue operation我这心就凉了，这是费费了，只能看数据能不能抢救回来了。

首先编辑/etc/my.cnf，添加：innodb_force_recovery=6

然后启动mysql，注意此时虽然数据库启动了，但是是只读模式，换言之不能进行任何新增、修改、删除操作，否则报错： mysql Got error -1 from storage engine

而且需要注意，此时博客能够打开浏览，但却无法进行登录，登录页面会无限循环而且不给任何提示信息。

接下来就是使用mysqldump命令进行数据库备份了，备份好后，删除mysql数据库目录的内容，并重新初始化：

rm -rf /usr/local/mysql/var/
./mysql_install_db
WARNING: The host 'MyServer' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
160718 21:29:50 [Note] ./bin/mysqld (mysqld 5.5.48-log) starting as process 22978 ...
OK
Filling help tables...
160718 21:29:50 [Note] ./bin/mysqld (mysqld 5.5.48-log) starting as process 22985 ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h MyServer password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

此时启动mysql服务会报错：

160718 21:34:32 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
160718 21:34:32 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.48-log) starting as process 24777
...
/usr/local/mysql/bin/mysqld: File './mysql-bin.index' not found (Errcode: 13)
160718 21:34:32 [ERROR] Aborting
160718 21:34:32 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
160718 21:34:32 mysqld_safe mysqld from pid file /usr/local/mysql/var/MyServer.pid ended

需要使用chown命令把新初始化的目录赋予给mysql用户，然后注释掉/etc/my.cnf中添加的＃innodb_force_recovery ＝ 6

重启数据库，修改root密码、倒入备份好的数据库即可。

后记：虽然从记录上来看一切都如行云流水般，但在真正处理时候也是消耗了不少的时间的。真就像阮一峰前辈说的：

第一阶段，刚接触Blog，觉得很新鲜，试着选择一个免费空间来写。

第二阶段，发现免费空间限制太多，就自己购买域名和空间，搭建独立博客。

第三阶段，觉得独立博客的管理太麻烦，最好在保留控制权的前提下，让别人来管，自己只负责写文章。幸好这次数据得以恢复，否则6月份的文章就真的丢了。所以，我目前想在github上建立静态博客，只是不知道有没有什么方便的工具进行一键迁移呢？