1. 목표

• 클라우드 리눅스에 MySQL 설치
  • AWS EC2 Ubuntu 18.04 사용
• 외부에서 원격 접속이 되도록 설정

2. 설치

2.1. MySQL 5

• 패키지 인덱스 업데이트
  sudo apt-get update

• MySQL 서버 설치
  sudo apt-get install mysql-server

• 비밀번호 설정 (비밀번호 없는 상태)

  • root 사용자로 MySQL 접속
    sudo mysql -u root -p

  • 비밀번호 입력 cli 나오면 엔터

  • root 사용자 인증 방식 확인
    use mysql;
select User, Host, plugin from mysql.user;

    +------------------+-----------+-----------------------+
    | User             | Host      | plugin                |
    +------------------+-----------+-----------------------+
    | root             | localhost | auth_socket           |
    | mysql.session    | localhost | mysql_native_password |
    | mysql.sys        | localhost | mysql_native_password |
    | debian-sys-maint | localhost | mysql_native_password |
    +------------------+-----------+-----------------------+

  • auth_socket을 mysql_native_password로 변경
    update user set plugin='mysql_native_password' where User='root';

    +------------------+-----------+-----------------------+
    | User             | Host      | plugin                |
    +------------------+-----------+-----------------------+
    | root             | localhost | mysql_native_password |
    | mysql.session    | localhost | mysql_native_password |
    | mysql.sys        | localhost | mysql_native_password |
    | debian-sys-maint | localhost | mysql_native_password |
    +------------------+-----------+-----------------------+

  • 현재 root 사용자 비밀번호 확인
    select User, authentication_string from user

    +------------------+-------------------------------------------+
    | User             | authentication_string                     |
    +------------------+-------------------------------------------+
    | root             |                                           |
    | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | debian-sys-maint | *9B9D78269A2D4C24777F0F0E0619785C63B5974B |
    +------------------+-------------------------------------------+

  • 비밀번호가 비어있다면, 원하는 비밀번호로 수정
    alter user 'root'@'localhost' identified with mysql_native_password by '사용할비밀번호';

  • 변경된 비밀번호 확인
    select User, authentication_string from user

    +------------------+-------------------------------------------+
    | User             | authentication_string                     |
    +------------------+-------------------------------------------+
    | root             |                                           |
    | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | debian-sys-maint | *9B9D78269A2D4C24777F0F0E0619785C63B5974B |
    +------------------+-------------------------------------------+

• 원격 접속 허용

  • 원격에서 접속하는 root 계정을 생성하면서 모든 데이터베이스 권한 허용
    grant all privileges on *.* to 'root'@'%' identified by '사용할비밀번호'
  • MySQL 설정파일 수정 (bind-address 제거)
    sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

    # 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

2.2. MySQL 8

• MySQL 8 repository 추가
  wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb

  • GPG 키가 없다는 에러 발생 시

      W: GPG error: http://repo.mysql.com/apt/ubuntu bionic InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY {PUBKEY}
      E: The repository 'http://repo.mysql.com/apt/ubuntu bionic InRelease' is not signed.
      N: Updating from such a repository can't be done securely, and is therefore disabled by default.
      N: See apt-secure(8) manpage for repository creation and user configuration details.

    sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys <PUBKEY>

• 패키지 다운로드
  sudo dpkg -i mysql-apt-config_0.0.10-1_all.deb

• 업데이트 & MySQL client, server 설치
  sudo apt update
sudo apt install mysql-server mysql-client

• MySQL 접속
  `sudo mysql -u root -p

• 원격 접속 root 계정 권한 설정

  • 버전 8에서는 위에서 사용했던 gran all privileges on *.* to 'root'@'%' identified by '사용할비밀번호' 명령을 지원하지 않는다.

  create user 'root'@'%' identified by 'password';
  
  grant all privileges on *.* to 'root'@'%' with grant option;
  
  flush privileges;