MySQL问题记录
在使用mysql时, 以下是寇墨出现并记录解决方案的一些问题:
1.无法登录【Can't connect to local MySQL】
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
[root@self-server: ~ ]$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
[root@self-server: ~ ]$ mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
[root@self-server: ~ ]$ mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)1.1 排查-检查进程
无论我使用什么命令, 想尝试登录mysql, 都提示这个: 无法通过socket连接mysql服务。但其实在第一次看到这个报错提示时，就应该先查看mysql进程有无正在运行：
[root@self-server: ~ ]# ps -ef | grep mysql
root 2559 2185 0 10:03 pts/0 00:00:00 grep --color=auto mysql使用命令进行排查，可以发现我们的MySQL根本都没有在运行，因此首先让我们把MySQL启动起来把！
1.2 启动MySQL【mysql.service: Unit not found.】
[root@self-server: ~ ]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
Failed to start mysqld.service: Unit not found.
[root@self-server: ~ ]# systemctl status mysqld
Unit mysqld.service could not be found.
[root@self-server: ~ ]# systemctl status mysql
Unit mysql.service could not be found.
[root@self-server: ~ ]# service mysql start
Redirecting to /bin/systemctl start mysql.service
Failed to start mysql.service: Unit not found.可以发现,通过这些命令都无法把MySQL启动起来, 那这是什么问题呢?这个需要我们自己回答!!
【安装时是否直接使用了MariaDB 😂🧐】
可以通过执行如下的命令发现: 当初是安装了MariaDB!!!
[root@self-server: ~ ]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: inactive (dead)
[root@self-server: ~ ]# systemctl start mariadb结果很明显了! 那就简单了, 先让我们把MariaDB启动起来吧! 可以看到我们的MariaDB成功启动了!
[root@self-server: ~ ]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since Fri 2024-05-03 10:06:14 CST; 1s ago
Process: 2761 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 2725 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 2760 (mysqld_safe)
Tasks: 20
Memory: 107.4M
CGroup: /system.slice/mariadb.service
├─2760 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─2925 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-e...
May 03 10:06:12 self-server systemd[1]: Starting MariaDB database server...
May 03 10:06:12 self-server mariadb-prepare-db-dir[2725]: Database MariaDB is probably initialized in /var/lib/mysql a...one.
May 03 10:06:12 self-server mariadb-prepare-db-dir[2725]: If this is not the case, make sure the /var/lib/mysql is emp...dir.
May 03 10:06:12 self-server mysqld_safe[2760]: 240503 10:06:12 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
May 03 10:06:12 self-server mysqld_safe[2760]: 240503 10:06:12 mysqld_safe Starting mysqld daemon with databases from...mysql
May 03 10:06:14 self-server systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.让我们再次尝试下是否可以连接mysql把!
[root@self-server: ~ ]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)问题解决!
但是, 我这个问题的产生本质上是因为自己没有设置开机自启动同时对自己的MySQL版本有些遗忘.
1.3 开机自启动
可以直接使用systemctl命令将该服务[mariadb]设置为开机自启动
$ systemctl enable mariadb可以通过所示命令查看目前设置为开机自启动的所有服务:
$ systemctl list-unit-files | grep enabled
auditd.service enabled
autovt@.service enabled
crond.service enabled
dbus-org.freedesktop.nm-dispatcher.service enabled
docker.service enabled
getty@.service enabled
haproxy.service enabled
irqbalance.service enabled
kdump.service enabled
keepalived.service enabled
kubelet.service enabled
lvm2-monitor.service enabled
mariadb.service enabled
microcode.service enabled
NetworkManager-dispatcher.service enabled
NetworkManager-wait-online.service enabled
NetworkManager.service enabled
postfix.service enabled
rhel-autorelabel-mark.service enabled
rhel-autorelabel.service enabled
rhel-configure.service enabled
rhel-dmesg.service enabled
rhel-domainname.service enabled
rhel-import-state.service enabled
rhel-loadmodules.service enabled
rhel-readonly.service enabled
rsyslog.service enabled
sshd.service enabled
sysstat.service enabled
systemd-readahead-collect.service enabled
systemd-readahead-drop.service enabled
systemd-readahead-replay.service enabled
tuned.service enabled
dm-event.socket enabled
lvm2-lvmetad.socket enabled
lvm2-lvmpolld.socket enabled
default.target enabled
multi-user.target enabled
remote-fs.target enabled
runlevel2.target enabled
runlevel3.target enabled
runlevel4.target enabled
dnf-makecache.timer enabled重启后检查是否生效:
[root@self-server: ~ ]$ reboot
Connection closing...Socket close.
Connection closed by foreign host.
Disconnected from remote host(192.168.160.201(Self_Server)) at 11:04:17.
Type `help' to learn how to use Xshell prompt.
[C:\~]$
Connecting to 192.168.160.201:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.
Last login: Fri May 3 11:00:10 2024 from 192.168.160.1
[root@self-server: ~ ]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2024-05-03 11:01:45 CST; 27s ago
Process: 1070 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 935 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 1069 (mysqld_safe)
Tasks: 20
Memory: 96.1M
CGroup: /system.slice/mariadb.service
├─1069 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─1289 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/...
May 03 11:01:43 self-server systemd[1]: Starting MariaDB database server...
May 03 11:01:43 self-server mariadb-prepare-db-dir[935]: Database MariaDB is probably initi....
May 03 11:01:43 self-server mariadb-prepare-db-dir[935]: If this is not the case, make sure....
May 03 11:01:43 self-server mysqld_safe[1069]: 240503 11:01:43 mysqld_safe Logging to '/va...'.
May 03 11:01:43 self-server mysqld_safe[1069]: 240503 11:01:43 mysqld_safe Starting mysqld...ql
May 03 11:01:45 self-server systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full. 2.如何修改默认端口
有的时候，为了防止其他人直接使用默认端口就可以访问到我们的服务器数据库，我们会对数据库的默认端口进行修改，如下便是操作方法：
$ vim /etc/my.cnf进入mysql的配置文件路径下/etc/my.cnf,如果对自己的配置文件路径不清楚的,可以使用如下命令查看mysql默认读取的my.cnf文件路径
$ mysql --help | grep 'my.cnf'
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf其实在使用, 这个命令的时候还有一行, 但是这一行是解释了其他的问题:
$ mysql --help | grep 'my.cnf'
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT, 让我们查看完整的帮助文档:
其中有这样一行:
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).用mysql --help | grep 'my.cnf'这个的时候, 只需要关注自己需要的那一部分即可.
当然寇墨肯定还是推荐,各位把完整的help文档阅读一遍
进入my.cnf配置文件之后, 可以在[mysqld]下方添加一行配置 port=4200, 完整如下所示:
在修改完之后,记得重启 MySQL!
[root@self-server: ~ ]# systemctl restart mariadb
[root@self-server: ~ ]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 4200 |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye重启完之后, 可以进入MySQL查看端口是否进行修改过了.可见修改生效了,其实不进入MySQL也可以查看配置是否生效, 如下所示, 可见在启动MySQL时有这样一个参数 --port=4200可知生效方式就是在启动MySQL时,将配置文件中的配置项在启动时作为自定义参数进行启动!!
$ ps aux | grep mysql
mysql 4072 0.0 0.0 113420 1616 ? Ss 10:26 0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 4252 0.0 4.6 968832 87584 ? Sl 10:26 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock --port=4200不同版本的MySQL的方式可能会有区别[如有不同, 可以告知寇墨], 我的版本如下:
$ mysql --version
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.13.如何远程连接MySQL
首先可以使用telnet命令查看网络、端口通不通，寇墨在这里网络已经通了，就不详细说明了。
但是在使用root用户，通过Navicat连接数据库时，发现连接不了，首先检查root用户是否可以远程连接：
MariaDB [(none)]> select host,user,Password from mysql.user;
+------------+------+-------------------------------------------+
| host | user | Password |
+------------+------+-------------------------------------------+
| % | root | *A767B1F6741DF7210C1ABAEFD281FFC3403BEC12 |
| k8s-node01 | root | *A767B1F6741DF7210C1ABAEFD281FFC3403BEC12 |
| 127.0.0.1 | root | *A767B1F6741DF7210C1ABAEFD281FFC3403BEC12 |
| ::1 | root | *A767B1F6741DF7210C1ABAEFD281FFC3403BEC12 |
| localhost | | |
| k8s-node01 | | |
| localhost | root | *A767B1F6741DF7210C1ABAEFD281FFC3403BEC12 |发现root用户是可以通过远程建立连接的, 再去查看MySQL是否开启允许远程访问:
MariaDB [(none)]> show variables like 'skip_networking';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+
1 row in set (0.00 sec)结果显示OFF, 表示已经开启了远程连接; 如果为ON, 则表示未开启.但是我们看到已经开启了但是还是不能连接,这是为什么呢?
因为服务器IP写错了.....
修改ip后, 连接成功!
4.windows使用
4.1 无法启动
在任务管理器中查看mysql的服务状态是关闭的，但是点击start之后，返回如下问题：
在cmd窗口中cd至mysql的bin目录下。使用net start mysql提示启动有问题, 使用mysqld -install会提示安装成功, 如果还有问题就可以使用这个:mysqld --initialize命令。最终成功启动。
4.2 root密码忘记
成功启动之后，发现密码忘记了，可以使用这个命令登录。
mysqld --console --skip-grant-tables --shared-memory然后另开一个新的cmd窗口，输入mysql -uroot -p输入密码时直接回车即可。进入到mysql中，将root的密码更新为空值，再正常重启下mysql即可解决问题，记得重新设置密码！
update user set authentication_string='' where user='root';在将密码设为空值以后，如果使用mysql官方的work bench连接mysql， 会提示密码过期，需要重新设置密码，此时不需要输入老密码（虽然会显示老密码的输入框，但是不用输入也能重置密码），直接输入需要设置的新密码即可了！
Comments NOTHING