Mysql 常见报错和疑问汇总

1、初始化数据库的时候报错 error while loading shared libraries: libstdc++.so.5: cannot open shared object file: No such file or directory 

是因为libstdc++.so.5没有装

yum install -y compat-libstdc++-33

2、Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

# yum install -y libaio

3、无法启动mysql

启动不了，到 /data/mysql 目录下查看错误日志，错误日志名称 hostname.err 

4、如果之前使用 yum 或者 apt-get 安装过 mysql，怎么样再安装免编译的包

# vim /usr/local/mysql/INSTALL-BINARY 

If you have previously installed MySQL using your operating system  native package management system, such as yum or apt-get, you may  experience problems installing using a native binary. Make sure  your previous MySQL previous installation has been removed  entirely (using your package management system), and thatany  additional files, such as old versions of your data files, have also been removed. You should alsocheck the existence of  configuration files such as /etc/my.cnf or the /etc/mysql directory have been deleted.

停止服务、yum remove mysql、删除data、/etc/my.cnf、/etc/mysql、启动脚本、删除mysql用户

5、Curses library not found. Please install appropriate package,

remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.

# rm CMakeCache.txt

6、Starting MySQL...The server quit without updating PID file (/data/mysql_data/centos2.pid).[失败]

# chown -R mysql:mysql /usr/local/mysl

# vim /data/mysql_data/centos2.pid

随机写入一个 pid 号码，如 1583， 确定系统中没有被占用就好。

/etc/my.cnf 没有配置 datadir 以及 basedir

             

7、Starting MySQL. ERROR! Manager of pid-file quit without updating file.

主要原因应该是之前安装的 MySQL 没有卸载干净，尝试删除干净之后，再重新安装

8、'--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.

# vim /etc/my.cnf

将/etc/my.cnf里面的skip-locking 改为skip-external-locking

9、ERROR 1130 (HY000): Host '    ' is not allowed to connect to this MySQL server

MySQL 还没有给客户机授权，所以不能连接远程 MySQL

# mysql

> grant all on *.* to 'root'@'client ip address' identified by 'password';

10、ERROR 1045 (28000): Access denied for user 'root'@'192.168.32.142' (using password: YES)

root 的密码不对，这里的密码是在 MySQL 里使用 grant 授权命令指定的密码。

11、xtrabackup: Error: --defaults-file must be specified first on the command line

英文释义也已经说明了解决方法， --defaults-file 必须在命令行的第一位，移动 --defaults-file 的位置即可

12、Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2).

MySQL 通信有两种方式，一种是 socket，一种是 host，可以再命令行指定 host 来进行通信。

# mysql -h192.168.32.142 -uroot -p     该方法适合远程登录mysql，前提是在主机 mysql 库中对远程客户端进行授权。

# xtrabackup --host=192.168.32.142

13、其他用户登陆的时候报错 Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

原因是授权的问题，如果是远程登录，先撤销权限， revoke；再重新赋予权限，grant 权限 on database.table to username WITH GRANT OPTION;   Access Denied 出现的原因就是没有加 with grant option.

14、MySQL 正常启动，命令行输入 mysql 提示 bash: mysql: command not found

原因：/usr/local/bin 目录下确实 mysql 导致的，在目录下建立软连接即可。 

ln -fs /MYSQYPATH/bin/mysql /usr/local/bin/mysql

15、MySQL 修改密码，命令行输入 mysqladmin ，提示 bash:mysqladmin: command not fount

原因和上面的一样，缺少了 msyqladmin 导致，目录下建立软连接即可，类似的命令还有 mysqldump

16、MySQL 配置文件增加log参数 ，提示错误MySQL server PID file could not be found![失败]

Starting MySQL.The server quit without updating PID file (/var/lib/mysql/xuyou58.com.pid).[失败]

原因：log 参数已经被 general_log 替代，修改配置文件的 log 为

general_log = ON

general_log_file = /data/mysql/mysql.log

17、ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

原因：一般是因为免编译安装的包和系统安装的包有冲突，造成无法使用 socket

方法一：

mkdir /var/liba/mysql

chown -R mysql:mysql !$

修改配置文件 my.cnf 

socket = /var/lib/mysql/

方法二：

mysql -S /tmp/mysql.sock

方法三：

mkdir /var/lib/mysql

ln -s /tmp/mysql.sock /var/lib/mysql

方法四：

# vim /etc/profile.d/path.sh

export PATH=$PATH:/usr/local/mysql/bin

# source /etc/profile.d/path.sh

18、mysqladmin: connect to server at 'localhost' failed

# vim /etc/my.cnf

    在 [mysqld] 字段下面增加

    skip-grant

# /etc/init.d/mysqld restart

# mysql

> use mysql;

> update user set password=password('your password') where user='root';

> flush privileges;

> quit;

# vim /etc/my.cnf

    注释 skip-grant

19、mysqldump 备份的时候报错

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': 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 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

原因：mysqldump 命令使用的是 mysql 5.5 的路径，实际使用的是 5.6 版本

# which mysqldump

/usr/bin/mysqldump

解决：使用自定义的mysqldump 路径 /usr/local/mysql/bin/mysqldump 或者创建软连接

20、mysql 5.5 启动的时候报错  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

原因：系统启动的时候，和自带的 mysql 冲突，要先卸载自带的mysql

解决：

# yum remove -y mysql