MySQL에 대한 원격 액세스를 허용하는 방법

많은 사이트와 응용 프로그램은 웹 서버와 데이터베이스 백엔드에서 같은 기계에 위탁 관리되기 시작한다.그러나 시간이 지날수록 이런 설정은 번거롭고 확장하기 어려울 수 있다.흔히 볼 수 있는 해결 방안은 원격 데이터베이스를 설정함으로써 이러한 기능을 분리하고 서버와 데이터베이스가 자신의 기계에서 자신의 속도로 성장할 수 있도록 하는 것이다.
사용자가 원격 MySQL 데이터베이스를 설정하려고 시도할 때 더욱 흔히 볼 수 있는 문제는 그들의 MySQL 실례가 로컬 연결만 정탐하도록 설정되어 있다는 것이다.이것은 MySQL의 기본 설정이지만 원격 데이터베이스 설정에는 적용되지 않습니다. 왜냐하면 MySQL은 서버에 접근할 수 있는 외부 IP 주소를 탐지할 수 있어야 하기 때문입니다.이 기능을 사용하려면 mysqld.cnf 파일을 엽니다.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address 명령으로 시작하는 줄로 이동합니다.다음과 같이 표시됩니다.
/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# 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
. . .

기본적으로 이 값은 127.0.0.1 로 설정됩니다. 이는 서버가 로컬 연결만 찾게 된다는 것을 의미합니다.외부 IP 주소를 참조하려면 이 명령을 변경해야 합니다.문제 해결을 위해 이 명령을 와일드카드 IP 주소*, :: 또는 0.0.0.0로 설정할 수 있습니다.
/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
. . .

참고 MySQL의 일부 버전에서 bind-address 명령은 기본적으로 mysqld.cnf 파일에 없을 수 있습니다.이 경우 다음 강조 표시된 행을 파일 하단에 추가합니다.
/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address            = 0.0.0.0

이 행을 변경한 후 파일을 저장하고 닫습니다(CTRL + X,Y, 그리고 ENTER(편집할 경우).
그런 다음 MySQL 서비스를 다시 시작하여 nano 변경 사항을 적용합니다.

sudo systemctl restart mysql

기존 MySQL 사용자 계정이 있는 경우 이 계정을 사용하여 원격 호스트에서 데이터베이스에 연결하려면 로컬 호스트가 아닌 원격 서버로 다시 구성해야 합니다.이를 위해 루트 MySQL 사용자 또는 다른 권한이 있는 사용자 계정으로 MySQL 클라이언트를 엽니다.

sudo mysql

루트 사용자에게 암호 인증을 설정한 경우 다음 명령을 사용하여 MySQL 셸에 액세스해야 합니다.

mysql -u root -p

사용자의 호스트를 변경하려면 MySQL의 mysqld.cnf 명령을 사용합니다.다음 명령을 실행하여 RENAME USER 을 MySQL 사용자 계정의 이름으로 변경하고 sammy 를 원격 서버의 IP 주소로 변경합니다.

RENAME USER 'sammy'@'localhost' TO 'sammy'@'remote_server_ip';

또는 다음 명령을 사용하여 원격 호스트에서만 접속할 수 있는 새 사용자 계정을 만들 수 있습니다.

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED BY 'password';

참고 이 명령은 MySQL의 기본 인증 플러그인remote_server_ip을 사용하여 인증하는 사용자를 생성합니다.그러나 일부 버전의 PHP에 이미 알려진 문제가 있어 이 플러그인에 문제가 발생할 수 있습니다.
이 데이터베이스를 PHP 응용 프로그램(예: phpMyAdmin)과 함께 사용할 계획이라면 원격 사용자를 만들어야 할 수도 있습니다. 이 플러그인은 여전히 안전하지만 비교적 오래된 플러그인caching_sha2_password을 통해 인증됩니다.

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

만약 확실하지 않다면, 항상 mysql_native_password 인증을 사용하는 사용자를 만들고 다음 명령caching_sha2_plugin을 사용하여 인증을 할 수 있습니다.

ALTER USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

그리고 당신의 특수한 수요에 따라 새로운 사용자에게 적당한 권한을 부여합니다.다음 예제에서는 데이터베이스, 테이블 및 사용자의 전역 권한ALTER, CREATE, ALTER 및 서버에 있는 모든 테이블에서 DROP, INSERT 및 UPDATE 데이터를 가져올 수 있는 권한을 사용자에게 부여합니다.또한 사용자에게 DELETE 데이터 조회, SELECT 키워드를 사용하여 외부 키를 만들고, REFERENCES 권한을 사용하여 FLUSH 조작을 수행할 수 있는 능력을 부여한다.단, 사용자에게 필요한 권한만 부여해야 하기 때문에 필요에 따라 사용자 권한을 마음대로 조정할 수 있습니다.

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;

그런 다음 RELOAD 명령을 실행하는 것이 좋습니다.이렇게 하면 서버가 상기 FLUSH PRIVILEGES 및 CREATE USER 문구로 인해 캐시된 모든 메모리를 해제합니다.

FLUSH PRIVILEGES;

그런 다음 MySQL 클라이언트를 종료할 수 있습니다.

exit

마지막으로, 데이터베이스 서버에 방화벽을 설정했다고 가정하면, MySQL의 데이터를 허용하기 위해 포트GRANT-MySQL의 기본 포트를 열어야 합니다.
특정 컴퓨터에서만 데이터베이스 서버에 액세스할 계획이라면 다음 명령을 사용하여 이 컴퓨터에 독점 권한을 부여하여 데이터베이스에 원격으로 연결할 수 있습니다.연결 예정인 시스템의 실제 IP 주소로 교체해야 함3306:

sudo ufw allow from remote_IP_address to any port 3306

나중에 다른 컴퓨터에서 데이터베이스에 접근해야 한다면 이 명령을 사용하여 임시 접근 권한을 부여할 수 있습니다.각각의 IP 주소만 기억하십시오.
또는 다음 명령을 사용하여 모든 IP 주소에서 MySQL 데이터베이스에 연결할 수 있습니다.
경고: 이 명령을 사용하면 누구나 MySQL 데이터베이스에 액세스할 수 있습니다.데이터베이스에 민감한 데이터가 포함되어 있으면 실행하지 마십시오.

sudo ufw allow 3306

그런 다음 다른 컴퓨터에서 데이터베이스에 원격으로 액세스하십시오.
참고 특정 IP 주소에서만 연결할 수 있도록 방화벽 규칙이 추가된 경우 해당 주소와 연결된 컴퓨터를 사용하여 데이터베이스에 액세스해야 합니다.

mysql -u user -h database_server_ip -p

데이터베이스에 액세스할 수 있는 경우 구성 파일의 remote_IP_address 명령이 문제인지 확인합니다.그러나 bind-address 를 bind-address 로 설정하는 것은 안전하지 않습니다. 모든 IP 주소에서 서버에 연결할 수 있기 때문입니다.다른 한편, 데이터베이스에 원격으로 접근할 수 없다면 다른 원인으로 인해 이 문제가 발생할 수 있습니다.이 두 가지 상황에서 저희 안내서How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04에 따라 더욱 안전한 원격 데이터베이스 설정을 설정하는 것이 도움이 될 수 있습니다.