mysql连接不上问题解决

MetaverseMan

已于 2023-11-01 23:55:41 修改

阅读量846

点赞数 3

分类专栏：服务器运维监控文章标签： mysql 数据库服务器

于 2023-03-25 13:38:45 首次发布

本文链接：https://blog.csdn.net/qq_37106501/article/details/129765860

版权

服务器同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

运维监控

4 篇文章 0 订阅

订阅专栏

一服务器上起的物理进程

公司新搭内网测试环境，mysql远程登录问题解决
远程登录:
1 修改host,

mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | auth_socket           |
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
mysql> alter user 'root'@'%' identified by '123456';
//注意这里如果用update的话 可能会报错：
mysql> update user set authentication_string=password("123456"),plugin='mysql_native_password' where user='root';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '("123456"),plugin='mysql_native_password' where user='root'' at line 1

2 配置文件 bind-address = 0.0.0.0 设置这里为你的ip或者全部ip 0.0.0.0代表所有

vim my.cnf

3 防火墙

ufw allow 3306/tcp;
ufw status;
root@base-ubuntu20: ufw allow 3306/tcp
规则已添加
规则已添加 (v6)
root@base-ubuntu20: ufw status;
状态： 激活

至                          动作          来自
-                          --          --
443                        ALLOW       Anywhere                  
80/tcp                     ALLOW       Anywhere                  
22/tcp                     ALLOW       Anywhere                  
3306/tcp                   ALLOW       Anywhere                  
443 (v6)                   ALLOW       Anywhere (v6)             
80/tcp (v6)                ALLOW       Anywhere (v6)             
22/tcp (v6)                ALLOW       Anywhere (v6)             
3306/tcp (v6)              ALLOW       Anywhere (v6)

4 加密方式（plugin=mysql_native_password）,

mysql> update user set plugin='mysql_native_password' where user='root'
    -> ;
Query OK, 1 row affected (0.71 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

5 设置登陆密码(authentication_string)


select host,user,authentication_string,plugin from user;	

update user set plugin='mysql_native_password' where user='root';

alter user 'root'@'%' identified by '123456';

以上每一次做完都flush一次。

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

最后重启mysql 服务 service mysql restart
参考：

https://blog.csdn.net/appleyuchi/article/details/113196484
https://zhuanlan.zhihu.com/p/391311322
https://blog.csdn.net/weixin_51563198/article/details/121529081

或者如果不想用root。自己新建一个账号：

create user 'tom'@'%' identified by 'tom123';

grant all privileges on *.* to 'tom'@'%' WITH GRANT OPTION;
flush privileges;
------------
ps： 删除用户：
mysql> drop user 'tom'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

all privileges ：所有权限
*.* : 所有的数据库中的所有的表
WITH GRANT OPTION ：权限给分享给自己创建的用户

二 docker-compose起的容器进程

首先要给mysql新配置一个newmy.cnf文件。

[mysqld]
skip-grant-tables
#bind-address=0.0.0.0
default-authentication-plugin=mysql_native_password

在这里skip-grant-tables确保第一次登陆时不使用密码，已达到我们修改密码的目的。

version: '3.5'
services:
  mysql:
    image: mysql:8.0
    restart: always
    volumes:
      - ./mysql/data:/var/lib/mysql
      - ./mysql/conf/:/etc/mysql/conf.d/      
    ports:
      - "13306:3306"
    command: --default-authentication-plugin=mysql_native_password
    environment:
      - MYSQL_USER= root
      - MYSQL_PASSWORD= 123456
      - MYSQL_ROOT_PASSWORD= 123456
      - MYSQL_ROOT_HOST= %
    networks:
      compose_network:
        ipv4_address: ${COMPOSE_NETWORK_PREFIX}.12
networks:
  compose_network:
    external: true
    name: ${COMPOSE_NETWORK}

将对应的配置文件放在

volumes:
- ./mysql/data:/var/lib/mysql
- ./mysql/conf/:/etc/mysql/conf.d/

使其正确映射。
然后将容器起起来。

docker exec -it app_mysql_1 /bin/bash

进入容器内部输入mysql
进入mysql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| ndb_binlog_index                                     |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
38 rows in set (0.00 sec)

mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | 123456                                                                 | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

mysql> update user set authentication_string='' where user='root';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

1 先skip-grant-tables,进入mysql.
2 然后就先flush privileges;
3 修改user, host,加密方式，密码。

第一次的话，host应该是localhost ,密码应该是空的。plugin
因为设置了mysql_native_password，所以会是mysql_native_password。
这个时候如果想要root用户在哪里都可以登陆可以将host改为%
update  user set host='%' where user='root';
如果不想可以略过。后边再设置别的账户允许host=%。

注意我这里是前边已经修改过的，所以展示的时候 host和密码已经有了。

注意⚠️：我这里这所以是明文显示就是因为之前用了
update user set authentication_string=“newpassword” where user=“root”; 这种方式。这种方式是错的。修改密码应该用下边的ALTER的方式。所以我这里用下边第二种需要先将它改为空，再ALTER。

执行第一种

> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

或者执行 第二种（此种情况是为了防止当前root用户authentication_string字段下有内容，先将其设置为空。）

用户的authentication_string字段下设置了newpassword值；当再使用ALTER USER ‘root’@‘localhost’ IDENTIFITED BY 'newpassword’时会报错的；因为authentication_string字段下只能是mysql加密后的41位字符串密码；其他的会报格式错误；

update user set authentication_string='' where user='root';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

然后 **flush privileges;**退出。
4 将skip-grant-tables,注释掉，

5 docker-compose 重启mysql服务。使用密码登陆！！

这个时候再登陆进去看，发现密码123456已经被加密🔐了

mysql> select host,user,authentication_string,plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

mysql>

用上边的方法，新建一个用户，将root的host改为localhost

mysql> update  user set host='localhost' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在这里插入图片描述
用新账户在外部登陆：

MetaverseMan

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql连接不上问题解决

mysql登陆
复制链接

扫一扫

专栏目录

mysql连接不上问题解决

一 服务器上起的物理进程

二 docker-compose起的容器进程

“相关推荐”对你有帮助么？

一服务器上起的物理进程