2021 阿里巴巴双十一西溪园区正大门

阿里云ECS 中安装mysql 连接失败解决

1. 背景

最近干了件傻事情,就是自己明明有阿里云的主机了,然后还买了阿里云的RDS,然后现在就想着,能不能把阿里云的 RDS 退掉,在阿里云的ECS里面,直接装一个Mysql。

2. 安装mysql方法

您可以使用以下步骤在Ubuntu上安装MySQL Server:

打开终端。
运行以下命令以更新软件包列表:
sudo apt update
运行以下命令以安装MySQL Server:
sudo apt install mysql-server
在安装过程中,您将被要求设置MySQL root用户的密码。请设置一个强密码并记下它。
安装完成后,MySQL Server将自动启动并在系统启动时运行。您可以使用以下命令检查MySQL Server的运行状态:
sudo systemctl status mysql
如果MySQL Server正在运行,您应该看到“active (running)”的消息。

您可以使用以下命令启动、停止或重新启动MySQL Server:
启动MySQL Server:
sudo systemctl start mysql
停止MySQL Server:
sudo systemctl stop mysql
重新启动MySQL Server:
sudo systemctl restart mysql
安装完成后,您可以使用MySQL客户端连接到MySQL Server并管理数据库。

登录进去:mysql -h localhost -u root -p

mysql:改密码:
ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password by ‘yourpass’;

修改可以访问的范围:这个一定要做!不然不过
alter user ‘root’@’%’ identified with mysql_native_password by ‘yourpass’;
FLUSH PRIVILEGES;

实在不行,自己到表里面去更新
use mysql;

update user set host = ‘%’ where host = ‘localhost’ and user = ‘root’;

3. 安装好后,结果死活连不上,提示如下:

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘47.88.13.203:3306’ (111)
用户名密码都是正确的。

问了阿里云客服,说是网络授权的原因。

4. 所以我们需求修改两个地方

4.1 修改数据库相关

mysql8放权命令为:
alter user ‘root’@’%’ identified with mysql_native_password by ‘123456’;
FLUSH PRIVILEGES;

但我执行不成功。所以 还有另外一个方案:
`Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.33-0ubuntu0.20.04.2 (Ubuntu)

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.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| superdeals |
| sys |
+——————–+
5 rows in set (0.01 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 |
| 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 |
+——————————————————+
37 rows in set (0.01 sec)

mysql> select host,user from user;
+———–+——————+
| host | user |
+———–+——————+
| % | root |
| localhost | debian-sys-maint |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+———–+——————+
5 rows in set (0.00 sec)

mysql> update user set host = ‘%’ where host = ‘localhost’ and user = ‘root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

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

mysql>
`
然后再重启一下mysql。结果还是不行。

mysql> alter user ‘root’@’%’ identified with mysql_native_password by ‘xxx’;
ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’%’

原来还要改文件

出现上述问题,可能有以下几种可能

cd /etc/mysql

  1. my.cnf 配置文件中 skip-networking 被配置

skip-networking 这个参数,导致所有TCP/IP端口没有被监听,也就是说出了本机,其他客户端都无法用网络连接到本mysql服务器

所以需要把这个参数注释掉。

2./etc/mysql/mysql.conf.d/mysqld.cnf 配置文件中 bindaddress 的参数配置

bindaddress,有的是bind-address ,这个参数是指定哪些ip地址被配置,使得mysql服务器只回应哪些ip地址的请求,所以需要把这个参数注释掉。

类似文章

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注