1.windows的ip問題
?? ?ping Linux服務(wù)器的ip，檢查網(wǎng)絡(luò)是否通暢
?? ?Linux服務(wù)器的網(wǎng)絡(luò)問題
2.連接的用戶是否有授權(quán)
????grant?
3.linux里的防火墻是否開啟
????iptables -L
????service firewalld stop
4.檢查下mysql服務(wù)是否開啟
????ps aux|grep mysqld
5.檢查下端口號是否修改
????netstat -anlput|grep mysqld
6.云服務(wù)器的安全組

1.windows里檢查，打開cmd，ping Linux服務(wù)器ip，檢查網(wǎng)絡(luò)。

?Linux服務(wù)器檢查，ping百度、windows機器等，檢查網(wǎng)絡(luò)。

[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：實現(xiàn)一臺電腦里的不同進程之間通信的文件
網(wǎng)絡(luò)socket：ip+端口（?實現(xiàn)跨宿主機之間通信）

[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

創(chuàng)建一個用戶并且授權(quán)

# 連接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.

# 創(chuàng)建一個用戶
root@(none) 18:47 ?mysql>create user 'hanwl'@'%' identified by '123456sc';
Query OK, 0 rows affected (0.00 sec)

# 給這個用戶授權(quán)
root@(none) 18:48 ?mysql>grant all on *.* to 'hanwl'@'%';
Query OK, 0 rows affected (0.00 sec)

'hanwl'@'%'
% 是通配符，代表任意的字符串

grant 是mysql里的授權(quán)命令
all 代表授予所有的權(quán)限 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>

添加一條防火墻規(guī)則

[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.

去除剛添加的防火墻規(guī)則

[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服務(wù)是否開啟

看進程

[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

看服務(wù)狀態(tài)狀態(tài)

[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.

云服務(wù)器 --》檢查安全組

?文章來源地址http://www.zghlxwxcb.cn/news/detail-778515.html

到了這里，關(guān)于MySQL 連接不上的常見問題的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！