服务器搭建过程中遇到的数据库问题

服务器搭建过程中遇到的问题

最近在维护之前的项目的时候，发现数据库意外连接不上，mysql显示后台运行，但是输入mysql -uroot -p却显示不能连接到sock文件

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

最初查网上的解法是查看文件夹是否存在，如果不存在就创建一个，结果创建完还是报错。

打开/etc/my.cnf文件查看mysql的配置文件，其实发现在配置文件的mysqld一栏的socket指向的是var中的sock文件，但是没有找到

开启全局扫描，查找mysqld.sock文件，即使用find命令

find mysqld.sock

查找到该文件在tmp文件下而不是var，所以改变文件指向到tmp，重新启动后发现可以正常启动

但是后面在登陆时又忘记了MYSQL密码

所以想要获取mysql的初始密码登录，之后再进行修改，使用如下查找命令

1	sudo grep 'temporary password' /var/log/mysqld.log

查看初始密码

将该密码当作密码登录，发现居然报密码错误

1	mysql -uroot -peejVY2MLgS+e

结果还是走不通，所以就想能不能跳过密码

在[mysqld]后添加skip-grant-tables（登录时跳过权限检查）

1	systemctl restart mysql

重启之后直接mysql -uroot -p，然后直接回车，进入到mysql命令行了

此时进行密码的设置

1	mysql> set password for 'root'@'localhost' = password('root123');

设置完之后要进行刷新

1	mysql> flush privileges;

最后进行验证：

[root@104 etc]# systemctl restart mysqld
[root@104 etc]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2020-06-08 14:19:44 CST; 11s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 30768 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 30792 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─30792 /usr/sbin/mysqld

Jun 08 14:19:43 104.225.233.221.16clouds.com systemd[1]: Stopped MySQL Server.
Jun 08 14:19:43 104.225.233.221.16clouds.com systemd[1]: Starting MySQL Server...
Jun 08 14:19:44 104.225.233.221.16clouds.com systemd[1]: Started MySQL Server.
[root@104 etc]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.20 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>

之前碰到进入mysql时发现service没启动的报错，查看mariadb状态，发现是服务初始化失败，

[root@db01 ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2021-08-10 10:04:21 EDT; 18s ago
  Process: 2509 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=217/USER)

Aug 10 10:04:21 db01 systemd[1]: Starting MariaDB database server...
Aug 10 10:04:21 db01 systemd[1]: mariadb.service: control process exited, code=exited status=217
Aug 10 10:04:21 db01 systemd[1]: Failed to start MariaDB database server.
Aug 10 10:04:21 db01 systemd[1]: Unit mariadb.service entered failed state.
Aug 10 10:04:21 db01 systemd[1]: mariadb.service failed.

根据错误提示去通过journalctl -xe进行查看报错信息

[root@db01 ~]# journalctl -xe
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mariadb.service has begun starting up.
Aug 10 10:04:21 db01 systemd[2509]: Failed at step USER spawning /usr/libexec/mariadb-prepare-db-dir: No such pro
-- Subject: Process /usr/libexec/mariadb-prepare-db-dir could not be executed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- The process /usr/libexec/mariadb-prepare-db-dir could not be executed and failed.
-- 
-- The error number returned by this process is 3.
Aug 10 10:04:21 db01 systemd[1]: mariadb.service: control process exited, code=exited status=217
Aug 10 10:04:21 db01 systemd[1]: Failed to start MariaDB database server.
-- Subject: Unit mariadb.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mariadb.service has failed.
-- 
-- The result is failed.
Aug 10 10:04:21 db01 systemd[1]: Unit mariadb.service entered failed state.
Aug 10 10:04:21 db01 systemd[1]: mariadb.service failed.
Aug 10 10:04:21 db01 polkitd[700]: Unregistered Authentication Agent for unix-process:2503:157501 (system bus nam

应该是之前的mariadb没有卸载干净，安装了新的版本导致了不兼容的现象

解决方法如下：

首先确保完全删除mariadb
1.yum remove mariad*
卸载mariadb，同时也卸载了mariadb-server
2.yum list installed | grep mariadb
发现在安装mariadb时作为依赖项的mariadb-libs没有被删除。
3.yum remove mariadb-libs
将其卸载
4.rm -rf /etc/my.cnf
5.rm -rf $(find / -name mysql)
删除所有包含mysql的文件（夹）
6.reboot 重启
————————————————

一定要完全卸载之前的版本再进行安装新版本，不然很可能产生冲突问题或者引擎不兼容的问题。

如果您喜欢此博客或发现它对您有用，则欢迎对此发表评论。也欢迎您共享此博客，以便更多人可以参与。如果博客中使用的图像侵犯了您的版权，请与作者联系以将其删除。谢谢！

服务器搭建过程中遇到的数据库问题

Linux搭建过程中的Mysql问题

服务器搭建过程中遇到的问题

所以想要获取mysql的初始密码登录，之后再进行修改，使用如下查找命令

结果还是走不通，所以就想能不能跳过密码

此时进行密码的设置

解决方法如下：

特色标签

链友