Chinaunix

标题: cacti+mysql 报错 [打印本页]

作者: liang3391 时间: 2009-05-02 14:05
标题: cacti+mysql 报错
cacti+mysql

mysql间隔几分钟就会自动down掉（端口号和进程仍然存在），必须要手动杀掉所有的mysql进程才，然后重启mysql服务才可以正常使用。

FATAL: Cannot connect to MySQL server on 'localhost'. Please make sure you have specified a valid MySQL database name in 'include/config.php'

[ 本帖最后由 liang3391 于 2009-5-3 16:21 编辑 ]

作者: Coolriver 时间: 2009-05-02 15:45
建义你贴一下mysql的错误日值。

初步估计是你的max_connect_errors　这个值太小了。
请在my.cnf中
[mysqld]下面添加：
max_connect_error=10240

作者: stormcc 时间: 2009-05-02 23:40

原帖由 Coolriver 于 2009-5-2 15:45 发表
建义你贴一下mysql的错误日值。

初步估计是你的max_connect_errors　这个值太小了。
请在my.cnf中
[mysqld]下面添加：
max_connect_error=10240

将 max_connect_error 设置的再高，当连接错误数达到这个阈值后，仍然会遇到这个问题。
楼主将错误日志贴出来，分析一下。

作者: Coolriver 时间: 2009-05-02 23:59

原帖由 stormcc 于 2009-5-2 23:40 发表

将 max_connect_error 设置的再高，当连接错误数达到这个阈值后，仍然会遇到这个问题。
楼主将错误日志贴出来，分析一下。

如果那个值也能超过，那只能说明应用程序有问题了。问题很严重了

作者: liang3391 时间: 2009-05-03 01:46
root@testtt [/usr/local/mysql/var]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=root
Starting mysqld daemon with databases from /usr/local/mysql/var
Killed

mysql log:

090503 13:20:12  mysqld started
/usr/sbin/mysqld: File '/usr/local/mysql/log/mysql.log' not found (Errcode: 13)
090503 13:20:12 [ERROR] Could not use /usr/local/mysql/log/mysql.log for logging                                           (error 13). Turning logging off for the whole duration of the MySQL server proc                                           ess. To turn it on again: fix the cause, shutdown the MySQL server and restart i                                           t.
/usr/sbin/mysqld: File '/usr/local/mysql/log/slowquery.log' not found (Errcode:                                           13)
090503 13:20:12 [ERROR] Could not use /usr/local/mysql/log/slowquery.log for log                                           ging (error 13). Turning logging off for the whole duration of the MySQL server                                           process. To turn it on again: fix the cause, shutdown the MySQL server and resta                                           rt it.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090503 13:20:12  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
090503 13:20:12  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: File name ./mysql.sock
InnoDB: File operation call: 'stat'.
InnoDB: Error: os_file_readdir_next_file() returned -1 in
InnoDB: directory .
InnoDB: Crash recovery may have failed for some .ibd files!
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090503 13:20:12  InnoDB: Started; log sequence number 0 184886
090503 13:20:12 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.67-community-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Co                                           mmunity Edition (GPL)

090503 01:35:39  mysqld started
090503  1:35:39  InnoDB: Started; log sequence number 0 43655
090503  1:35:39 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.0.40-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
090503  1:37:32 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown

090503  1:37:34  InnoDB: Starting shutdown...
090503 01:41:12  mysqld started
090503  1:41:12  InnoDB: Started; log sequence number 0 43655
090503  1:41:12 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.0.40-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
090503  1:42:33 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown

090503  1:42:35  InnoDB: Starting shutdown...
090503 01:43:57  mysqld started
/usr/local/mysql/libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)
090503  1:43:57 [ERROR] Aborting

090503  1:43:57 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

090503 01:43:57  mysqld ended

090503 01:44:02  mysqld started
090503  1:44:02  InnoDB: Started; log sequence number 0 43655
090503  1:44:02 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.0.40-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

每间隔几分钟mysql就down掉。但是进程和端口号存在。必须手动把所有mysql进程杀掉，重启采可以启动。

down掉后用 mysql -u root -p 也连不上mysql。

root@testtt [/usr/local/mysql/var]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=root
Starting mysqld daemon with databases from /usr/local/mysql/var
STOPPING server from pid file /usr/local/mysql/var/testtt.com.pid
Killed

安装方式：
mysql-5.0.56

shell> groupadd mysql
   shell> useradd -g mysql mysql
   shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
   shell> cd mysql-VERSION
   shell> ./configure --prefix=/usr/local/mysql
   shell> make
   shell> make install
   shell> cp support-files/my-medium.cnf /etc/my.cnf
   shell> cd /usr/local/mysql
   shell> chown -R mysql .
   shell> chgrp -R mysql .
   shell> bin/mysql_install_db --user=mysql
   shell> chown -R root .
   shell> chown -R mysql var
   shell> bin/mysqld_safe --user=mysql &

[ 本帖最后由 liang3391 于 2009-5-3 14:12 编辑 ]

作者: liang3391 时间: 2009-05-03 16:09
求助

作者: Coolriver 时间: 2009-05-03 16:17
权限的问题。
chown -R mysql:mysql /usr/local/mysql/

作者: liang3391 时间: 2009-05-03 16:31
标题: cat /usr/local/mysql/log/error.log
090503 16:25:53  InnoDB: Page checksum 1612628608, prior-to-4.0.14-form checksum                                              2673809632
InnoDB: stored checksum 808464432, prior-to-4.0.14-form stored checksum 80846443                                           2
InnoDB: Page lsn 808464432 808464432, low 4 bytes of lsn at page end 808464432
InnoDB: Page number (if stored to page already) 808464432,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 808464432
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery                                           .html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
090503 16:25:53  mysqld ended
InnoDB: Crash recovery may have failed for some .ibd files!
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of page 2.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Error: page n

stored in the page read in is 808464432, should be 1!
090503 16:25:53  InnoDB: Error: page 808464432 log sequence number 808464432 808464432
InnoDB: is in the future! Current system log sequence number 0 193521.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1.
InnoDB: You may have to recover from a backup.
090503 16:25:53  InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex 30303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030303030
InnoDB: stored checksum 858796848, prior-to-4.0.14-form stored checksum 858796848
InnoDB: Page lsn 858796848 858796848, low 4 bytes of lsn at page end 858796848
InnoDB: Page number (if stored to page already) 858796848,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 858796848
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
090503 16:40:52  mysqld ended

root@testtt [~]# cat /usr/local/mysql/log/
error.log    mysql.log    slowquery.log
root@testtt [~]# cat /usr/local/mysql/log/slowquery.log
/usr/local/mysql/libexec/mysqld, Version: 5.0.40-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                Id Command Argument
/usr/local/mysql/libexec/mysqld, Version: 5.0.40-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                Id Command Argument
/usr/local/mysql/libexec/mysqld, Version: 5.0.40-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                Id Command Argument
/usr/local/mysql/libexec/mysqld, Version: 5.0.40-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                Id Command Argument
/usr/local/mysql/libexec/mysqld, Version: 5.0.40-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                Id Command Argument
/usr/sbin/mysqld, Version: 5.0.67-community-log (MySQL Community Edition (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                Id Command Argument
/usr/sbin/mysqld, Version: 5.0.67-community-log (MySQL Community Edition (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                Id Command Argument
/usr/sbin/mysqld, Version: 5.0.67-community-log (MySQL Community Edition (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                Id Command Argument

[ 本帖最后由 liang3391 于 2009-5-3 16:44 编辑 ]

作者: Coolriver 时间: 2009-05-03 16:48

root@testtt [/usr/local/mysql/var]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=root

mysql 不能以root运行。
现在日值显示，表空间错误，需要恢复。你的数据是不是直接cpoy过来的。而且里有innodb的表。

去查一下innodb怎么迁移的。就可以必免了。

作者: liang3391 时间: 2009-05-03 17:02
mysql 不以root运行的话就不能启动。mysql没有做任何处理。这服务器上mysql安装了几次。也没有做数据直接cpoy。

作者: Coolriver 时间: 2009-05-03 19:29
如只是安装一个新的mysql 那么，你把/usr/local/mysql/var 下面一个id 开头的文件给删掉，在启动。让mysql重新创建试一下。

作者: shihao138096 时间: 2009-07-03 14:29
标题: 回复 #1 liang3391 的帖子
楼主首先确定一下<cacti_dir>/include/下面的config.php以及global.php文件中mysql的数据库相关选项是否设置正确

作者: thinkc 时间: 2009-07-13 09:14
提示: 作者被禁止或删除内容自动屏蔽

作者: alang85 时间: 2012-04-11 12:19
回复 1# liang3391

根据MYSQL错误日志处理问题。网上有些说找不到mysql.sock。

作者: alang85 时间: 2012-04-11 12:21
回复 6# liang3391

你先清理之前的日志，然后在连接，看报什么错误，然后再打印出来。先解决数据库能正常启动。

作者: devilkin0312 时间: 2012-04-12 18:38
所有的，意思是很多？一致杀线程不怕崩掉么回复 1# liang3391

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)