MySQL 连接不上的常见问题_mysql连不上

1.windows的ip问题
    ping Linux服务器的ip，检查网络是否通畅
    Linux服务器的网络问题
2.连接的用户是否有授权
    grant 
3.linux里的防火墙是否开启
    iptables -L
    service firewalld stop
4.检查下mysql服务是否开启
    ps aux|grep mysqld
5.检查下端口号是否修改
    netstat -anlput|grep mysqld
6.云服务器的安全组

1.windows里检查，打开cmd，ping Linux服务器ip，检查网络。

 Linux服务器检查，ping百度、windows机器等，检查网络。

[root@mysql mysql]# ping www.baidu.com
PING www.a.shifen.com (112.80.248.75) 56(84) bytes of data.
64 bytes from 112.80.248.75 (112.80.248.75): icmp_seq=1 ttl=128 time=62.2 ms
64 bytes from 112.80.248.75 (112.80.248.75): icmp_seq=2 ttl=128 time=72.0 ms
64 bytes from 112.80.248.75 (112.80.248.75): icmp_seq=3 ttl=128 time=26.5 ms
64 bytes from 112.80.248.75 (112.80.248.75): icmp_seq=4 ttl=128 time=24.9 ms
64 bytes from 112.80.248.75 (112.80.248.75): icmp_seq=5 ttl=128 time=25.9 ms
64 bytes from 112.80.248.75 (112.80.248.75): icmp_seq=6 ttl=128 time=31.4 ms
 
 
[root@mysql mysql]# ping 192.168.1.117
PING 192.168.1.117 (192.168.1.117) 56(84) bytes of data.
64 bytes from 192.168.1.117: icmp_seq=1 ttl=128 time=0.658 ms
64 bytes from 192.168.1.117: icmp_seq=2 ttl=128 time=1.71 ms
64 bytes from 192.168.1.117: icmp_seq=3 ttl=128 time=1.84 ms
64 bytes from 192.168.1.117: icmp_seq=4 ttl=128 time=0.708 ms

文件socket：实现一台电脑里的不同进程之间通信的文件
网络socket：ip+端口（ 实现跨宿主机之间通信）

[root@mysql etc]# cat my.cnf
[mysqld_safe]
 
[client]
#socket=/data/mysql/mysql.sock
 
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
 
[mysql]
auto-rehash
prompt=\u@\d \R:\m  mysql>
 
[root@mysql ~]# mysql -uroot -p123456sc
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
# 使用文件socket
[root@mysql ~]# mysql -uroot -p123456sc  -S /data/mysql/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.41 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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.
root@(none) 12:12  mysql>

[root@mysql ~]# mysql -h 192.168.102.139 -P3306 -uroot -p'your passwd'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'mysql' is not allowed to connect to this MySQL server

创建一个用户并且授权

# 连接MySQL
[root@mysql ~]# mysql -uroot -p'your passwd'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.41 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
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.
 
# 创建一个用户
root@(none) 18:47  mysql>create user 'hanwl'@'%' identified by '123456sc';
Query OK, 0 rows affected (0.00 sec)
 
# 给这个用户授权
root@(none) 18:48  mysql>grant all on *.* to 'hanwl'@'%';
Query OK, 0 rows affected (0.00 sec)
 
'hanwl'@'%'
% 是通配符，代表任意的字符串
 
grant 是mysql里的授权命令
all 代表授予所有的权限 select、insert、update、delete等
on *.* 第1个* 代表库，第2个*代表库
to 'hanwl'@'%' 给具体的用户
 
[root@mysql ~]# mysql -h 192.168.102.139 -P3306 -uhanwl -p123456sc
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.41 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
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.
 
hanwl@(none) 18:49  mysql>

添加一条防火墙规则

[root@mysql ~]# iptables -A INPUT -p tcp --dport 3306 -j DROP
[root@mysql ~]# iptables -L -n -v
Chain INPUT (policy ACCEPT 77 packets, 4484 bytes)
 pkts bytes target     prot opt in     out     source               destination         
    7   700 DROP       tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:3306
 
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination         
 
Chain OUTPUT (policy ACCEPT 48 packets, 3792 bytes)
 pkts bytes target     prot opt in     out     source               destination         
 
[root@mysql ~]# mysql -h 192.168.102.139 -P3306 -uhanwl -p123456sc
mysql: [Warning] Using a password on the command line interface can be insecure.

去除刚添加的防火墙规则

[root@mysql ~]# iptables -D INPUT -p tcp --dport 3306 -j DROP
 
[root@mysql ~]# mysql -h 192.168.102.139 -P3306 -uhanwl -p123456sc
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.41 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
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.
 
hanwl@(none) 18:53  mysql>

mysql服务是否开启

看进程

[root@mysql etc]# ps aux|grep mysqld
root      10231  0.0  0.0  11824  1600 ?        S    12:41   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid
mysql     10387  0.1 13.2 1763324 246660 ?      Sl   12:41   0:23 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=mysql.err --open-files-limit=8192 --pid-file=/data/mysql/mysql.pid --socket=/data/mysql/mysql.sock --port=3306
root      10598  0.0  0.0 112832   988 pts/2    S+   16:31   0:00 grep --color=auto mysqld

看端口

[root@mysql etc]# netstat -anlput|grep mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      10387/mysqld        
tcp6       0      0 192.168.102.139:3306    192.168.102.1:60719     ESTABLISHED 10387/mysqld  

直接进入MySQL

[root@mysql ~]# mysql -uroot -p'Sanchuang1234#'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.37 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
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.
 
root@(none) 18:47  mysql>

看日志

[root@mysql mysql]# tail slave.err 
2023-07-22T10:41:12.871873Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'screpl@192.168.102.139:3306' - retry-time: 60  retries: 143, Error_code: 2003
2023-07-22T10:42:15.913241Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'screpl@192.168.102.139:3306' - retry-time: 60  retries: 144, Error_code: 2003
2023-07-22T10:43:18.950869Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'screpl@192.168.102.139:3306' - retry-time: 60  retries: 145, Error_code: 2003
2023-07-22T10:44:21.983706Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'screpl@192.168.102.139:3306' - retry-time: 60  retries: 146, Error_code: 2003
2023-07-22T10:45:25.014526Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'screpl@192.168.102.139:3306' - retry-time: 60  retries: 147, Error_code: 2003
2023-07-22T10:46:28.043188Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'screpl@192.168.102.139:3306' - retry-time: 60  retries: 148, Error_code: 2003
2023-07-22T10:47:31.062752Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'screpl@192.168.102.139:3306' - retry-time: 60  retries: 149, Error_code: 2003
2023-07-22T10:47:34.379215Z 7 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2023-07-22T10:48:34.081550Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'screpl@192.168.102.139:3306' - retry-time: 60  retries: 150, Error_code: 2003
2023-07-22T10:49:37.122049Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'screpl@192.168.102.139:3306' - retry-time: 60  retries: 151, Error_code: 2003

看服务状态状态

[root@mysql mysql]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since 六 2023-07-22 15:22:47 CST; 3h 28min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 6641 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 32
   Memory: 272.4M
   CGroup: /system.slice/mysqld.service
           ├─6663 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/slave...
           └─6996 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/...
 
7月 22 15:22:43 slave systemd[1]: Starting LSB: start and stop MySQL...
7月 22 15:22:47 slave mysqld[6641]: Starting MySQL.... SUCCESS!
7月 22 15:22:47 slave systemd[1]: Started LSB: start and stop MySQL.

云服务器 --》检查安全组