mysql故障和错误总结-阿里云开发者社区

开发者社区> 余二五> 正文

mysql故障和错误总结

简介:
+关注继续查看

1.用户和网站无法访问数据库中的表

提示:Table ‘xxx’ is marked as crashed and should be repaired

图:

wKiom1LMqXaBA0raAAAfRuGh21U287.jpg

   

解决方法:myisamchk -c -r ../data/tablename/ecs_pay_log.MYI      --忧化和修复一下表


2.启动mysql出现错误(权限有问题)

[root@centos bin]# ./mysql -u root -p   --启动mysql出错
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@centos bin]#


解决方法:

[root@centos bin]# useradd -s /sbin/nolog -r -M mysql    --创建mysql系统用户
[root@centos mysql]# pwd
/usr/local/mysql
[root@centos mysql]# chown -R root:mysql .   --修改mysql根目录的权限
[root@centos mysql]# chown -R mysql:mysql /var/lib/mysql/  --修改数据文件目录权限
[root@centos mysql]# cd bin/
[root@centos bin]# ./mysql_install_db &    --初使化数据库
[2] 2973
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...
OK
Filling help tables...
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:
/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h centos password 'new-password'
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr/local/mysql ; /usr/local/mysql/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 with the /usr/local/mysql/bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[2]+  Done                    ./mysql_install_db
[root@centos bin]# ./mysqld_safe &    --启动数据库
[1] 3040
Starting mysqld daemon with databases from /var/lib/mysql
[root@centos bin]#


测试:

[root@centos bin]# ./mysql -u root -p   --成功登陆
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.40 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.03 sec)
mysql>


3.缺少ncurses-devel包

checking for tgetent in -lcurses... no
checking for tgetent in -ltermcap... no
checking for tgetent in -ltinfo... no
checking for termcap functions library... configure: error: No curses/termcap library found


解决方法:

[root@centos1 mysql-5.0.40]$ yum install ncurses-devel


4.没有mysql.sock文件

[root@centos1 bin]$ ./mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@centos1 bin]$


解决方法:

[root@centos1 bin]$ find / -name mysql.sock
/var/lib/mysql/mysql.sock
[root@centos1 bin]$ ln -s /var/lib/mysql/mysql.sock  /tmp/mysql.sock
[root@centos1 bin]$ ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.40 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>


5.没有mysql.sock文件

[root@centos bin]# ./mysql -u root -p -S /var/lib/mysql/mysql.sock
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
[root@centos bin]#


解决方法:

[root@centos mysql5.0]#  share/mysql/mysql.server restart
ERROR! MySQL manager or server PID file could not be found!
Starting MySQL SUCCESS!
[root@centos mysql5.0]#


测试:

[root@centos mysql5.0]# netstat -anp |grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      30734/mysqld
[root@centos mysql5.0]#  cd bin/
[root@centos bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.40 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>


6.启动数据库出现(

shared libraries:libmysqlclient.so.16:cannot open shared object file:No such file or directory)

错误信息:

[root@redhat1 mysql-5.1.51]# ./configure --prefix=/usr/local/mysql5.1/ && make && make install    --安装数据库
[root@redhat1 mysql-5.1.51]# cd /usr/local/mysql5.1/
[root@redhat1 mysql5.1]# cp -a share/mysql/mysql.server  /etc/init.d/mysqld
[root@redhat1 mysql5.1]# chmod  755 /etc/init.d/mysqld   --服务启动文件
[root@redhat1 mysql5.1]# /etc/init.d/mysqld restart
Shutting down MySQL.                                       [  OK  ]
Starting MySQL.                                            [  OK  ]
[root@redhat1 mysql5.1]# netstat -an |grep 3306    --启动成功
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN
[root@redhat1 mysql5.1]# ./bin/mysql -u root -p    --登陆出错
mysql: error while loading shared libraries: libmysqlclient.so.16: cannot open shared object file: No such file or directory


解决方法:

[root@redhat1 mysql5.1]# ldconfig -v |grep mysql   --没有mysql模块
[root@redhat1 mysql5.1]# vim /etc/ld.so.conf     --添加模块
/usr/local/mysql5.1/lib/mysql/
/usr/lib/mysql
[root@redhat1 mysql5.1]# ldconfig  -v |grep mysql   --查看mysql模块
/usr/local/mysql5.1/lib/mysql:
libmysqlclient.so.16 -> libmysqlclient.so.16.0.0
libmysqlclient_r.so.16 -> libmysqlclient_r.so.16.0.0
/usr/lib/mysql:
libmysqlclient.so.16 -> libmysqlclient.so1
[root@redhat1 mysql5.1]# ln -s /usr/local/mysql5.1/lib/mysql/libmysqlclient.so /usr/lib/mysql/libmysqlclient.so
[root@redhat1 mysql5.1]# ./bin/mysql -u root -p    --启动成功
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

 

7.启动mysql报错

2015-04-17 10:17:18 26168 [Note] InnoDB: Completed initialization of buffer pool
2015-04-17 10:17:18 26168 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2015-04-17 10:17:19 26168 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2015-04-17 10:17:20 26168 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-04-17 10:17:20 26168 [Warning] InnoDB: New log files created, LSN=1648900
2015-04-17 10:17:20 7fddd7d9d720 InnoDB: Expected to open 3 undo tablespaces but was able
2015-04-17 10:17:20 7fddd7d9d720 InnoDB: to find only 0 undo tablespaces.
2015-04-17 10:17:20 7fddd7d9d720 InnoDB: Set the innodb_undo_tablespaces parameter to the
2015-04-17 10:17:20 7fddd7d9d720 InnoDB: correct value and retry. Suggested value is 0
2015-04-17 10:17:20 26168 [ERROR] Plugin 'InnoDB' init function returned error.
2015-04-17 10:17:20 26168 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-04-17 10:17:20 26168 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-04-17 10:17:20 26168 [ERROR] Aborting


2015-04-17 10:17:20 26168 [Note] Binlog end
2015-04-17 10:17:20 26168 [Note] ./bin/mysqld: Shutdown complete
[root@tong1 mysql-5.6.22]#


解决方法:

[root@tong1 mysql-5.6.22]# rm -rf data/ib_logfile*        --删除ib_logfile0或1事物日志文件,或者是修改了数据库的配置文件参数产生异常

[root@tong1 mysql-5.6.22]# /etc/init.d/mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL... SUCCESS! 
[root@tong1 mysql-5.6.22]# 


8.MHA架构中的错误

Tue Apr 28 15:15:36 2015 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln193] There is no alive slave. We can't do failover
Tue Apr 28 15:15:36 2015 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln383] Error happend on checking configurations.  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 298
Tue Apr 28 15:15:36 2015 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln478] Error happened on monitoring servers.


解决方法:

(1).删除健康检查文件  rm -rf /var/log/masterha/app1/app1.master_status.health 
(2).在宕机的数据库中重新同步到主服务器 change master to master_host...............

(3).检查复制是否出错 masterha_check_repl --conf=/etc/mysqlmha/conf/app1.cnf 



9.MMM架构中的错误

[root@tong3 ~]# /etc/init.d/mysql-mmm-monitor  start
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: Base class package "Class::Singleton" is empty.
    (Perhaps you need to 'use' the module which defines that package first,
    or make that module available in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
 at /usr/share/perl5/vendor_perl/MMM/Monitor/Agents.pm line 2
BEGIN failed--compilation aborted at /usr/share/perl5/vendor_perl/MMM/Monitor/Agents.pm line 2.
Compilation failed in require at /usr/share/perl5/vendor_perl/MMM/Monitor/Monitor.pm line 15.
BEGIN failed--compilation aborted at /usr/share/perl5/vendor_perl/MMM/Monitor/Monitor.pm line 15.
Compilation failed in require at /usr/sbin/mmm_mond line 28.
BEGIN failed--compilation aborted at /usr/sbin/mmm_mond line 28.
failed
[root@tong3 ~]#


解决方法:

故障说明:是没有编译Class::Singleton相关的包

[root@tong3 init.d]# perl -MCPAN -e shell
Terminal does not support AddHistory.
cpan[1]> Class::Singleton         --没有找到相关的包
Catching error: "Can't locate object method \"Singleton\" via package \"Class\" (perhaps you forgot to load \"Class\"?) at /usr/share/perl5/CPAN.pm line 375, <FIN> line 1.\cJ" at /usr/share/perl5/CPAN.pm line 391
    CPAN::shell() called at -e line 1

cpan[2]> Class
Unknown shell command 'Class'. Type ? for help.

cpan[3]> install Class::Singleton      --安装包
CPAN: Storable loaded ok (v2.20)
Going to read '/root/.cpan/Metadata'
  Database was generated on Tue, 28 Apr 2015 04:17:02 GMT
CPAN: LWP::UserAgent loaded ok (v5.833)
CPAN: Time::HiRes loaded ok (v1.9721)
Warning: no success downloading '/root/.cpan/sources/authors/01mailrc.txt.gz.tmp19898'. Giving up on it. at /usr/share/perl5/CPAN/Index.pm line 225
Fetching with LWP:
  http://www.perl.org/CPAN/authors/01mailrc.txt.gz
Going to read '/root/.cpan/sources/authors/01mailrc.txt.gz'
............................................................................DONE
.............................省略  .......................

  /usr/bin/make test -- OK
Warning (usually harmless): 'YAML' not installed, will not store persistent state
Running make install
Prepending /root/.cpan/build/Class-Singleton-1.5-nrETFl/blib/arch /root/.cpan/build/Class-Singleton-1.5-nrETFl/blib/lib to PERL5LIB for 'install'
Manifying blib/man3/Class::Singleton.3pm
Installing /usr/local/share/perl5/Class/Singleton.pm
Installing /usr/local/share/man/man3/Class::Singleton.3pm
Appending installation info to /usr/lib64/perl5/perllocal.pod
  SHAY/Class-Singleton-1.5.tar.gz
  /usr/bin/make install UNINST=1 -- OK         --安装成功
Warning (usually harmless): 'YAML' not installed, will not store persistent state

cpan[4]> exit
Terminal does not support GetHistory.
Lockfile removed.
[root@tong3 init.d]# 


9.备份mysql时出现表损坏

错误提示:cannot load form mysql.proc the table is probably corrupted


解决方法:

[root@goodsph2 ~]# mysql_upgrade -u root -p    --更新表结构

[root@goodsph2 ~]# /etc/init.d/mysqld restart      --重启数据库就好了

Shutting down MySQL... SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@goodsph2 ~]# 









本文转自 z597011036 51CTO博客,原文链接:http://blog.51cto.com/tongcheng/1349528,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
8643 0
使用NAT网关轻松为单台云服务器设置多个公网IP
在应用中,有时会遇到用户询问如何使单台云服务器具备多个公网IP的问题。 具体如何操作呢,有了NAT网关这个也不是难题。
26538 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
2840 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
11010 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10470 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
8802 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
12293 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
7340 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
21734 0
+关注
20382
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载