[MySQL] MySQL5.7 설치 in ubuntu

황인용·2021년 2월 23일

aws ec2 grant mycnf mysql mysql5.7 ubuntu

Database

목록 보기

15/16

1. ubuntu 버전 확인

$ lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 18.04.2 LTS
Release:	18.04
Codename:	bionic

2. apt-get mysql

apt를 최신으로 update 하고, mysql-server를 설치한다.
2.1 install mysql

$ sudo apt-get update
$ sudo apt-get install mysql-server -y

2.2 configuring mysql-server

$ sudo mysql_secure_installation

VALIDATE PASSWORD PLUGIN을 사용할지 : Y(사용)
거의 서비스 목적으로 DB를 사용하기에 password 사용을 활성화 한다

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: Y

password 유효성 레벨 : 0
사용목적에 따라 번호를 입력

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

새로운 root password 입력

Please set the password for root here.

New password:

Re-enter new password:

root password 강도(25)
root 패스워드 강도를 그대로 사용할지 : Y(사용)

Estimated strength of the password: 25
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y

Anonymous User를 삭제할지 : Y(삭제)
필요에 따라 Y|N 선택

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y

root계정으로 외부에서 접속을 허용할지 : Y(허용)
필요에 따라 Y|N 선택

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y

Test Database를 삭제할지 : Y(삭제)
필요에 따라 Y|N 선택

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y

privileges table을 reload 할지 : Y(허용)
필요에 따라 Y|N 선택

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

필요에 따라 mysql_secure_installation은 다시 설정 가능

3. mysql service 확인

mysql service port(3306)이 열려있는지 확인

$ netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

만약 mysql service port(3306)가 활성화 되어 있지 않다면, 방화벽에서 그리고 해당 mysql 서비스를 구동시켜야 한다.

$ sudo ufw allow mysql
Rules updated
Rules updated (v6)

local root 접속

$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.33-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2021, 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>

4. 외부에서 mysql 접속

외부에서 접속하기 위해서는 Mysql의 config 설정 중 bind-address를 ALL IP(0.0.0.0)으로 허용 및 해당 서버에 들어오는 port에 대해 방화벽을 허용해주어야한다.
AWS EC2를 사용할 경우, 인스턴스의 보안그룹에서 mysql service port(3306)을 인바운드 허용해줘야 한다.

4.1 mysql.conf.d 수정

mysql 5.7 이상에서는 /etc/mysql/mysql.conf.d 디렉토리에 Mysql 기본 config의 프로파일(mysqld.cnf)이 저장되어 있다

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address 를 ALL_IP(0.0.0.0)로 변경 또는 추가해준다.

...
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
bind-address            = 0.0.0.0
...

mysql service restart

$ sudo service mysql restart 
$ sudo systemctl restart mysql.service

4.2 MySQL grant modify

Mysql은 기본적으로 root 계정을 가지고 있다.
접속하고자 하는 계정을 추가로 만들거나, root 계정을 외부에서 접속할 수 있도록
Mysql에서 접속 권한을 부여해야한다.
'root'계정으로 어떤 ip든 접속 할 수 있도록 권한 부여
접속하고자 하는 계정의 password 설정

mysql> grant all privileges on *.* to 'root'@'%' identified by '루트계정 비밀번호';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

만약 password 길이나 level 관련 에러가 발생할 경우

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

validate_password_lenth와 validate_password_policy 확인
필요에 따라 길이와 레벨을 수정

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL validate_password_length = 0;
Query OK, 0 rows affected (0.01 sec)