ec2에 mysql 설치 및 설정

설치 및 root 계정 설정

sudo apt-get update
sudo apt install mysql-server
sudo mysql -uroot
mysql> use mysql
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| 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 |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+

root 계정이 auth_socket으로 되어있으면 password 없이 sudo권한만으로 접속이 가능하다.

mysql> UPDATE user SET plugin='caching_sha2_password' WHERE User='root';
mysql> select user, host, plugin from user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| 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 |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '1234';
mysql> flush privileges;
mysql> exit
$ mysql -uroot -p
Enter password: 

패스워드 재입력시 잘 된다면 완료.

새 계정 생성

mysql> create user 'kangaroo'@'%' identified by 'password';
Query OK, 0 rows affected (0.01 sec)

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

mysql> SELECT USER, HOST FROM mysql.user;
+------------------+-----------+
| USER             | HOST      |
+------------------+-----------+
| kangaroo         | %         |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql> grant all privileges on *.* to 'kangaroo'@'%'

mysql> flush privileges;