MySQL下常见的启动失败与备份失败问题的解决教程

[root@fisheye ~]# ps -ef | grep mysql
root   25555 21974 0 11:28 pts/0  00:00:00 grep mysql

[root@fisheye data]# service mysql start

MySQL server PID file could not be found![失败]
Starting MySQL.............. ERROR! The server quit without updating PID file (/mydata/data/fisheye..pid).[失败]

[root@fisheye data]# tail -100 fisheye.err

InnoDB: Last MySQL binlog file position 0 337403929, file name ./mysql-bin.000016
141013 1:13:28 InnoDB: Waiting for the background threads to start
141013 1:13:29 InnoDB: 5.5.33 started; log sequence number 1006647152
17:13:29 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
di141013 01:13:29 mysqld_safe mysqld from pid file /mydata/data/fisheye.pid ended

[root@fisheye data]# touch /mydata/data/fisheye.pi

[root@fisheye data]# service mysql restart

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

[root@fisheye data]# df -h

/dev/sda1       9.5G 9.5G 0 100% /
/dev/sda4       5.5G 1.3G 4.0G 24% /mnt/backup
/dev/mapper/IhuilianVG-IhuilianLV00
            22G 4.2G  17G 20% /var//app
tmpfs         1.3G   0 1.3G  0% /dev/shm

[root@test100 data]# mysqldump fx > fx.sql

mysqldump: Got error: 1146: Table 'user_suggest_report' doesn't exist when using LOCK TABLES

[root@test100 data]#mysqldump --skip-lock-tables fx > fx.sql

Error: Couldn't read status information for table vote_results () mysqldump: Couldn't execute 'show create table `user_suggest_report`': Table 'fx.user_suggest_report' doesn't exist (1146)

 [root@test100 data]#mysql -h127.0.0.1 -D fx
 mysql> show tables;      #查看所有的表 --> 发现是表存在的

+--------------------------------+
| Tables_in_fx          |
+--------------------------------+
| user_suggest_report      |
+--------------------------------+
80 rows in set (0.00 sec)

mysql> drop table user_suggest_report;      #既然是存在的，系统却认定不存在说明存在问题，索性想删除试试

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_suggest_report' at line 1

[root@test100 data]# cat /etc/my.f | grep datadir
datadir=/var/lib/mysql
[root@test100 data]# cd /var/lib/mysql/fx/
[root@test100 fx]# mv user_suggest_report.frm /data

[root@test100 fx]# service mysqld restart

[root@test100 data]# mysqldump fx > fx.150109.sql  #操作成功