Mysql-5.7版本报错问题解决

2021-09-15   

Mysql-5.7版本报错问题解决

博主mysql是在清华大学镜像站下载的5.7版本，采用的二进制安装，自定义了一些文件路径，安装后使用过mysql指令本地登录的时候测试没问题。但今天使用的时候出现了如下报错问题，现将故障描述和解决办法整理如下：

报错1 mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

故障描述：安装mysql后本地登录建库改密码并没有什么问题，前几天使用了yum -y upgrade升级了系统软件和内核。今天使用mysql指令后出现报错

报错提示：mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

解决办法：这是mysql连接时找不到依赖的libncurses.so.5动态库文件，需要安装对应软件包或者做个libncurses.so.5的软链接

#方法一：查找对应的软件包并进行安装
[root@localhost ~]# yum provides libncurses.so.5
Last metadata expiration check: 0:14:35 ago on Mon 13 Sep 2021 05:47:30 PM CST.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : baseos
Matched from:
Provide    : libncurses.so.5
[root@localhost ~]# yum -y install ncurses-compat-libs-6.1-7.20180224.el8.i686

#方法二：直接安装libncurses相关软件包
[root@localhost ~]# yum -y install libncurses*

#方法三：如果是arm架构的或者系统有更高版本的共享库，就做个软链接
#查找系统内名字以libncurses开头的文件，并将错误信息丢进空洞
[root@localhost ~]# find / -name libncurses.* 2> /dev/null
/usr/lib64/libncurses.so.6
/usr/lib64/libncurses.so.6.1
/usr/lib64/libncurses.so.5.9
#查看使用mysql指令的执行路径
[root@TopLinux ~]# which mysql
/usr/local/mysql/bin/mysql
#查看mysql程序所依赖的共享库列表
[root@localhost ~]# ldd /usr/local/mysql/bin/mysql
	linux-vdso.so.1 (0x00007ffee3963000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fb0097d7000)
	librt.so.1 => /lib64/librt.so.1 (0x00007fb0095cf000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007fb0093cb000)
	libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fb008e10000)
	libm.so.6 => /lib64/libm.so.6 (0x00007fb008a8e000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fb008876000)
	libc.so.6 => /lib64/libc.so.6 (0x00007fb0084b1000)
	/lib64/ld-linux-x86-64.so.2 (0x00007fb0099f7000)
#制作libncurses.so.5的软链接
[root@localhost ~]# cd /usr/lib64/
[root@localhost ~]# ln -sf libncurses.so.5.9 libncurses.so.5
[root@localhost ~]# ln -sf libtinfo.so.5.9 libtinfo.so.5

温馨提醒：如果使用第三种做软链接的办法，只做了libncurses.so.5的软链接。当登录mysql报错其他的，将对应的报错信息再制造软链接即可

报错2 mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory

如下

[root@localhost ~]# mysql -uroot -p
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
[root@localhost ~]# cd /usr/lib64/
[root@localhost ~]# ll libtinfo*
-rwxr-xr-x  1 root root 200000 May 11  2019 libtinfo.so.5.9
lrwxrwxrwx. 1 root root     15 May 11  2019 libtinfo.so.6 -> libtinfo.so.6.1
-rwxr-xr-x. 1 root root 208616 May 11  2019 libtinfo.so.6.1
[root@localhost ~]# ln -sf libtinfo.so.5.9 libtinfo.so.5
[root@localhost lib64]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1039
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

报错3 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

故障描述：使用mysqldump备份数据库数据的时候出现报错，而使用mysql命令进入数据库并没有任何问题

报错提示：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

解决办法：使用mysql命令进入数据库没有问题，说明mysql的服务是正常运行的。而使用mysqldump进行本地备份的时候，mysqldump尝试使用“socket文件”进行连接时，却无法获取到“socket文件”的位置，导致mysqldump使用默认的“socket文件”位置去寻找“socket文件”，但是未找到“socket文件”而引发此错误。完善mysql的配置文件即可

报错前/etc/my.cnf配置文件的内容如下：

[root@localhost ~]# cat /etc/my.cnf
[mysql]  
default-character-set=utf8mb4  
socket=/usr/local/mysql/mysql.sock   

[mysqld]
user=mysql        
port=3306         
basedir=/usr/local/mysql   
datadir=/usr/local/mysql/data   
socket=/usr/local/mysql/mysql.sock   
log-error=/usr/local/mysql/logs/error.log
pid-file=/usr/local/mysql/pids/mysqld.pid
character-set-server=utf8    
max_connections=8000 
skip-name-resolve=ON
default-storage-engine=INNODB
lower_case_table_names=1 
max_allowed_packet=16M

其中“socket=”的路径就是socket文件的位置，我们只要修改my.cnf文件,告诉mysql、mysqldump、mysqladmin、client等mysql服务的socket位置在哪里就可以

[root@localhost ~]# cat > /etc/my.cnf << EOF

[mysqldump]
socket=/usr/local/mysql/mysql.sock

[mysqladmin]
socket=/usr/local/mysql/mysql.sock

[client]
socket=/usr/local/mysql/mysql.sock
EOF

#重启mysqld服务，如果是写进system文件，就用systemctl restart mysqld
[root@localhost ~]# service mysqld restart

#再使用mysqldump进行全量备份发现已恢复正常
[root@localhost ~]# mysqldump -uroot -p -A > allbak.sql
Enter password:

最后附加**php、python、php pdo连接mysql报错"Can't connect to local MySQL server through socket..."**的解决办法：

#php的解决方法：在/etc/php.ini文件中"[MySQL]"项下找到"mysql.default_socket"，并设置其值指向正确的mysql服务socket文件即可
[root@localhost ~]# vim /etc/php.ini
……
[MySQL]
……
mysql.default_socket="/usr/local/mysql/mysql.sock"

#python的解决办法：在连接mysql数据库函数中指定socket文件
#!/usr/bin/python
from MySQLdb import connect
conn = connect(db="pzy", user="root", host="localhost", unix_socket="/usr/local/mysql/mysql.sock")
cur = conn.cursor()
count=cur.execute("show databases")
print 'there has %s dbs' % count
conn.commit()
conn.close()

#pho pdo的解决办法：同样在连接字符串添加mysql socket文件的位置即可
<?php
$dsn = "mysql:host=localhost;dbname=pzy;unix_socket=/usr/local/mysql/mysql.sock";
$db = new PDO($dsn, 'root', '');
$rs = $db->query("SELECT * FROM qrtest");
while($row = $rs->fetch()){
    print_r($row);
}
?>

Q.E.D.