Error connecting to database: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

登录Zabbix之前，却确认Nginx服务打开，php-fpm打开，service zabbix_server start server_agentd start

意外断电Zabbix登录出现如下错误

Database error

  • Error connecting to database: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

无法连接到数据库，请确认数据库是否开启

当我要开启数据库服务的时候，数据库又出错，因为我没有开启热备份。。。。

[root@dep5 ~]# service mysqld status
MySQL is not running, but lock file (/var/lock/subsys/mysql[失败]ts
[root@dep5 ~]# service mysqld start
Starting MySQL...The server quit without updating PID file [失败]mysql.pid).
#查看日志 
#[root@dep5 ~]# vim /data/mysqldb/log/mysql-error.log 
2016-09-03 16:26:43 10550 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace zabbix/groups uses space ID: 3 at filepath: ./zabbix/groups.ibd. Cannot open tablespace mysql/slave_relay_log_info which uses space ID: 3 at filepath: ./mysql/slave_relay_log_info.ibd
2016-09-03 16:26:43 7f4097e0a720  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./mysql/slave_relay_log_info.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
160903 16:26:43 mysqld_safe mysqld from pid file /tmp/mysql.pid ended

mysql 日志中给出了猜测和各自的解决方案

1)权限问题，修改权限就OK

2）就是说你不需要这些表的话，清空表，删除.ibd文件，就会恢复（这样的话你的zabbix也会没有，我想一下第三种方法）

3）如富哦这是文件系统或者磁损坏，你不能移除,你可以在你的my.cnf里面将设置innodb_force_recovery > 0，强制InnoDB引擎来.....

解决：

[root@dep5 ~]# vim /etc/my.cnf
#innodb
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:2048M:autoextend
innodb_log_file_size = 128m
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 60M
innodb_buffer_pool_instances = -1
innodb_max_dirty_pages_pct = 70
#innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16m
innodb_flush_log_at_trx_commit = 2
innodb_force_recovery = 1  #添加这个就Ok了


#[root@dep5 ~]# vim /etc/my.cnf 
#[root@dep5 ~]# service mysqld start
#Starting MySQL.......  

我看了一下启动成功之后的数据库日志有如下片段，猜测Zabbix无法正常打开=  =

2016-09-03 16:41:33 18646 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2016-09-03 16:41:33 18646 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-09-03 16:41:33 18646 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-09-03 16:41:33 18646 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
2016-09-03 16:41:34 18646 [Note] Event Scheduler: Loaded 0 events
2016-09-03 16:41:34 18646 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.31-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
2016-09-03 16:41:34 18646 [Note] Event Scheduler: scheduler thread started with id 1
2016-09-03 16:41:39 7feb5261e700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-09-03 16:41:39 7feb5261e700 InnoDB: Error: Fetch of persistent statistics requested for table "zabbix"."users" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2016-09-03 16:41:39 7feb5261e700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

这个就是Zabbix打开出现的界面，，

 后面想着注释在my.cnf添加的哪一行，，

虽然mysql重新启动是OK了，但是mysql日志被刷新了一次...

2016-09-03 16:48:11 7f37cdfb7700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-09-03 16:48:11 7f37cdfb7700 InnoDB: Error: Fetch of persistent statistics requested for table "zabbix"."media_type" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

我就想着修复表。。。

[root@dep5 ~]# mysqlcheck -r zabbix
zabbix.acknowledges
note     : The storage engine for the table doesn't support repair
zabbix.actions
note     : The storage engine for the table doesn't support repair
zabbix.alerts

悲剧了，我猜zabbix数据库的引擎应该为myisam，看不到引擎啊。。

使用MySQL5.6或者更高版本，自从MySQL被Oracle收购了，它的性能确实有不少的提升。请一定选择innodb，别选择myisam，因为zabbix在innodb的性能比在myisam快1.5倍，而且myisam不安全，zabbix监控数据量很大，一旦表坏了，那就是一个悲剧。

悲剧啊！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

注意：

毕竟我也是新手，然后能想到的最笨的办法就是所有重来（没做配置备份，引擎没有修改，好尴尬）

最后的处理办法，闪库，重新建库建表，并且重新导入zabbix表把。。想搭建zabbix服务器那样，前面做了什么全部清理掉，然后重新来

losbyday 基于LNMP的Zabbbix之Zabbix Server源码详细安装，但不给图

zabbix里面我随便看的几张表都是InnoDB引擎，为什么前面修复的时候会说引擎不支持呢？？？？？？？？？？

 

如果在公司绝对像上面那样做！！！！！！！！！！！！！！！！！！！！！！！！！！！删除zabbix数据库是什么概念？删除是要准备跑路了么？哈哈

可以试一下其他操作

修复方式之一（我没试过啊）：http://www.52ij.com/pc/229507.html

借助修复表的工具把

posted on 2016-09-03 17:09  losbyday  阅读(2017)  评论(0)  编辑  收藏  举报

导航