MySQL8_rpm方式安装过程中常见问题处理

启动失败

启动失败场景1_Could not open file mysqld.log

问题描述

rpm方式安装完数据库后启动数据库提示不能打开mysqld.log

12月 21 10:23:33 dtweb1 mysqld[26916]: 2023-12-21T02:23:33.925906Z 0 [ERROR] [MY-010187] [Server] Could not open file '/topsoft/mysqldb/logs/mysqld.log' for error logging: Permission denied

问题分析

--查看目录权限，发现是755，没问题
ls -ld /topsoft/mysqldb/logs

解决办法

重新单独设置文件权限，再次启动数据库不报错。

chmod 755 /topsoft/mysqldb/logs/mysqld.log

启动失败场景2_File ‘./binlog.index’ not found

问题描述

rpm方式安装完数据库后启动数据库提示无法找到binlog.index文件

mysqld: File './binlog.index' not found (OS errno 13 - Permission denied)
35:24.732753Z 0 [Warning] [MY-010091] [Server] Can't create test file /topsoft/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
2023-12-21T01:35:24.732874Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 108015
2023-12-21T01:35:24.736728Z 0 [Warning] [MY-010091] [Server] Can't create test file /topsoft/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
2023-12-21T01:35:24.736760Z 0 [Warning] [MY-010091] [Server] Can't create test file /topsoft/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
2023-12-21T01:35:24.738710Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-12-21T01:35:24.738957Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23)  MySQL Community Server - GPL.

问题分析

发现/etc/my.cnf中无binlog参数，增加binlog参数再次启动数据库不再提示该错误

解决办法

log_bin = /topsoft/mysql/binlog  #二进制日志文件前缀
binlog_format = ROW
binlog_expire_logs_seconds=604800 #mysql binlog日志文件保存的过期时间7天，过期后自动删除;默认值是0，不限制,这样会占用空间太多 单位秒
max_binlog_size = 1G #限制单个文件大小,默认大小:1,073,741,824,即1G,太大了

启动失败场景3_Can’t create test file

问题描述

rpm方式安装完数据库后启动数据库提示 Can’t create test file。头一回碰到很是懵，无从下手。

[root@dtweb1 mysqldb]# systemctl status mysqld8
● mysqld8.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld8.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since 四 2023-12-21 10:23:33 CST; 2s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 26916 ExecStart=/usr/sbin/mysqld --defaults-file=/etc/my.cnf (code=exited, status=1/FAILURE)
 Main PID: 26916 (code=exited, status=1/FAILURE)

12月 21 10:23:33 dtweb1 mysqld[26916]: 2023-12-21T02:23:33.921118Z 0 [Warning] [MY-010091] [Server] Can't create test file /topsoft/mysqldb/data/mysqld_tmp_file_case_insensitive_test.lower-test
12月 21 10:23:33 dtweb1 mysqld[26916]: 2023-12-21T02:23:33.921225Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 26916
12月 21 10:23:33 dtweb1 mysqld[26916]: 2023-12-21T02:23:33.924665Z 0 [Warning] [MY-010091] [Server] Can't create test file /topsoft/mysqldb/data/mysqld_tmp_file_case_insensitive_test.lower-test
12月 21 10:23:33 dtweb1 mysqld[26916]: 2023-12-21T02:23:33.924694Z 0 [Warning] [MY-010091] [Server] Can't create test file /topsoft/mysqldb/data/mysqld_tmp_file_case_insensitive_test.lower-test
12月 21 10:23:33 dtweb1 mysqld[26916]: 2023-12-21T02:23:33.925906Z 0 [ERROR] [MY-010187] [Server] Could not open file '/topsoft/mysqldb/logs/mysqld.log' for error logging: Permission denied
12月 21 10:23:33 dtweb1 mysqld[26916]: 2023-12-21T02:23:33.925984Z 0 [ERROR] [MY-010119] [Server] Aborting
12月 21 10:23:33 dtweb1 mysqld[26916]: 2023-12-21T02:23:33.926240Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23)  MySQL Community Server - GPL.
12月 21 10:23:33 dtweb1 systemd[1]: mysqld8.service: main process exited, code=exited, status=1/FAILURE
12月 21 10:23:33 dtweb1 systemd[1]: Unit mysqld8.service entered failed state.
12月 21 10:23:33 dtweb1 systemd[1]: mysqld8.service failed.

分析过程

参考一博主的步骤，说是selinux参数未设置禁用导致

解决办法

selinux参数设置禁用，再次启动数据库不再提示该错误

sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
setenforce 0 

3306端口不通

问题描述

rpm方式安装完数据库后，数据库连接工具不能访问数据库，ip通端口不通

分析过程

刚开始以为是tcp中无3306端口导致，设置了禁用ipv6后依然不能访问，排除tcp中无3306端口导致。

[root@dtweb1 logs]# netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 127.0.0.1:59681         0.0.0.0:*               LISTEN      109980/KvEdrSvc     
tcp        0      0 0.0.0.0:58595           0.0.0.0:*               LISTEN      1284/sshd           
tcp        0      0 127.0.0.1:55566         0.0.0.0:*               LISTEN      1700/logagent       
tcp        0      0 0.0.0.0:10007           0.0.0.0:*               LISTEN      114368/../jdk1.8//b 
tcp        0      0 0.0.0.0:10008           0.0.0.0:*               LISTEN      114368/../jdk1.8//b 
tcp6       0      0 :::58595                :::*                    LISTEN      1284/sshd           
tcp6       0      0 :::33060                :::*                    LISTEN      73573/mysqld        
tcp6       0      0 127.0.0.1:8005          :::*                    LISTEN      114677/../jdk1.8/bi 
tcp6       0      0 :::3306                 :::*                    LISTEN      73573/mysqld        
tcp6       0      0 :::63443                :::*                    LISTEN      1700/logagent       
tcp6       0      0 :::8088                 :::*                    LISTEN      114677/../jdk1.8/bi

--查看防火墙 发现并未开启防火墙
systemctl status firewalld

--禁用ipv6
vi /etc/sysctl.conf
##add by disable ipv6
net.ipv6.conf.all.disable_ipv6 =1
net.ipv6.conf.default.disable_ipv6 =1
net.ipv6.conf.eth0.disable_ipv6 =1

--生效
sysctl -p

解决办法

原因暂时未知，参考一博主的做法客户端竟然神奇的能访问数据库了。

--停止数据库
systemctl stop mysqld
--禁用开机启动
systemctl disable mysqld
--启动数据库  ，数据库连接成功
systemctl start mysqld
--设置开机启动
systemctl enable mysqld

服务器本地登录失败

问题描述

rpm方式安装完数据库后，服务器本地登录失败，提示不能连接sock文件

[root@dtweb1 logs]# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

分析过程

查找服务器上都有哪些sock文件

--查找服务器上都有哪些sock文件
[root@dtweb1 logs]# find / -name mysql.sock
-rw-r--r-- 1 mysql mysql  342 Sep 27 14:27  /topsoft/mysql/mysql.sock

--指定-S 参数登录数据库，登录成功
mysql -uroot -p -S  /topsoft/mysql/mysql.sock

解决办法

创建软链接，再次登录正常

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