By Frank in AWS — Apr 9, 2020

[AWS]记一次连接EC2 MySQL的问题

帮实习生解决连不上ec2上mysql的问题。
AMI是Amazon Linux 2, MySQL版本是Ver 14.14 Distrib 5.7.29

先看了眼security group的inbound rules，是允许公司内网所有端口的。
检查本地连接,尝试本地登录

安装后尝试本地连接报错

(env) [root@XXXXXXX ec2-user]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

也不允许看版本

(env) [root@XXXXXXX ec2-user]# mysql -v
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

经网络查找后，现在的mysql安装时会产生随机密码，于是去log里寻找

(env) [root@XXXXXXX log]# grep 'password' /var/log/mysqld.log
2020-04-07T07:21:09.342519Z 1 [Note] A temporary password is generated for root@localhost: tuem>Mb5X09S

杀掉旧进程，重启服务，用新密码登录成功

(env) [root@XXXXXXX etc]# pkill mysqld
(env) [root@XXXXXXX etc]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
(env) [root@XXXXXXX etc]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29

端口监听也正常

(env) [root@XXXXXXX etc]# lsof -i -P -n | grep LISTEN
rpcbind  2855      rpc    8u  IPv4  17786      0t0  TCP *:111 (LISTEN)
rpcbind  2855      rpc   11u  IPv6  17789      0t0  TCP *:111 (LISTEN)
master   3343     root   13u  IPv4  20052      0t0  TCP 127.0.0.1:25 (LISTEN)
sshd     3600     root    3u  IPv4  19399      0t0  TCP *:22 (LISTEN)
sshd     3600     root    4u  IPv6  19401      0t0  TCP *:22 (LISTEN)
mysqld   9601    mysql   28u  IPv6 184000      0t0  TCP *:3306 (LISTEN)

被逼修改初始密码

mysql> status
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

同时syntax报错

mysql> GRANT ALL PRIVILEGES on . to 'root'@'xxxxxxx' IDENTIFIED BY 'tuem>Mb5X09S';
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 'to 'root'@'xxxxxxx' IDENTIFIED BY 'tuem>Mb5X09S'' at line 1

于是重置, 也是磕磕绊绊

mysql> UPDATE mysql.user SET Password=PASSWORD('R66EVkwafcwpZd6q') WHERE User='root';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'R66EVkwafcwpZd6q';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 's6e#y%4sN6KF6^]k';
Query OK, 0 rows affected (0.00 sec)

5.尝试赋权，失败

mysql> GRANT ALL PRIVILEGES on . to 'root'@'xxxxxxx' IDENTIFIED BY 's6e#y%4sN6KF6^]k' WITH GRANT OPTION;
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 'to 'root'@'xxxxxxx' IDENTIFIED BY 's6e#y%4sN6KF6^]k' WITH GRANT OPTION' at line 1

最后尝试，成功

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 's6e#y%4sN6KF6^]k';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

修改/etc/my.cnf
在[mysqld]里加入bind-address=0.0.0.0，看起来像

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=0.0.0.0

重启mysqld即可。

Subscribe to 隅