MySQL로 사이트 성능을 최적화하기 위해 원격 데이터베이스를 설정하는 방법

소개

애플리케이션이나 웹 사이트가 성장함에 따라 현재 설정보다 커진 지점에 도달할 수 있습니다. 현재 동일한 VPS에서 웹 서버와 데이터베이스 백엔드를 호스팅하고 있다면 이 두 기능을 분리하여 각각 자체 시스템에서 작동하고 성장할 수 있도록 하는 것이 좋습니다.

이 가이드에서는 웹 서버가 동적 콘텐츠를 위해 연결할 수 있는 원격 데이터베이스 서버를 구성하는 방법에 대해 설명합니다. 우리는 WordPress를 예로 사용하여 작업할 수 있습니다. 웹 서버에서 Nginx를 구성한 다음 원격 시스템의 MySQL 데이터베이스에 연결합니다. 데모를 위해 Ubuntu 12.04 VPS 인스턴스에서 이 모든 작업을 수행할 것입니다.

데이터베이스 서버에 MySQL 설치

시작하려면 하나의 VPS 인스턴스를 MySQL 서버로 구성합니다. 데이터를 별도의 컴퓨터에 저장하는 것은 하나의 시스템 구성에서 한계에 도달했을 때 우아하게 확장할 수 있는 좋은 방법입니다. 또한 로드 밸런스를 조정하고 나중에 설정을 더욱 확장하는 데 필요한 기본 구조를 제공합니다.

시작하려면 데이터베이스 서버에 몇 가지 기본 패키지를 설치해야 합니다. 이는 기본적으로 기존 LEMP 스택용 데이터베이스를 설정할 때 수행하는 단계와 거의 동일하지만 모든 구성 요소가 필요하지는 않습니다(일부는 다른 서버에 있음).

패키지 캐시를 업데이트하고 MySQL 서버를 설치하여 시작하십시오.

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

설치 절차 중에 MySQL의 루트 암호를 선택하고 확인하라는 메시지가 표시됩니다.

완료되면 데이터베이스 설치 명령을 실행해야 구성 요소를 관리할 적절한 디렉토리 구조가 생성됩니다.

sudo mysql_install_db

그런 다음 일부 안전하지 않은 기본값을 비활성화할지 묻는 스크립트를 실행하여 보안을 약간 강화해야 합니다.

sudo mysql_secure_installation

위 단계에서 설정한 MySQL 관리자 비밀번호를 입력해야 합니다. 그런 다음 비밀번호를 변경할 것인지 묻습니다. 현재 암호에 만족하면 아니오를 의미하는 "N\을 입력합니다.

모든 추가 질문에 대해 ENTER를 눌러 일부 테스트 데이터베이스를 제거하고 액세스를 잠그는 기본 옵션을 선택해야 합니다.

원격 액세스를 허용하도록 MySQL 구성

이제 데이터베이스를 가동하고 실행 중이므로 다른 컴퓨터에서 연결할 수 있도록 일부 값을 변경해야 합니다.

편집기에서 루트 권한으로 MySQL의 기본 구성 파일을 엽니다.

sudo nano /etc/mysql/my.cnf

이 파일은 괄호 안의 단어([ 및 ])로 표시된 섹션으로 나뉩니다. mysqld 섹션을 찾습니다.

[mysqld]

이 섹션 내에서(이 태그와 다음 섹션 마커 사이의 영역) bind-address라는 매개변수를 찾아야 합니다. 이것은 기본적으로 연결을 수신할 네트워크 주소를 데이터베이스 소프트웨어에 알려줍니다.

현재 MySQL은 자신의 컴퓨터에서만 연결을 찾도록 구성되어 있습니다. 서버에 연결할 수 있는 외부 IP 주소를 참조하도록 변경해야 합니다.

사설 네트워킹 기능이 있는 데이터 센터에서 호스팅하는 경우 서버의 사설 네트워크 IP를 사용하십시오. 그렇지 않으면 여기에서 공용 IP 주소를 사용할 수 있습니다.

bind-address        = your_database_IP

완료되면 파일을 저장하고 닫습니다.

방금 구현한 새로운 변경 사항을 MySQL이 읽도록 강제하려면 데이터베이스를 다시 시작할 수 있습니다.

sudo service mysql restart

원격 WordPress 자격 증명 및 데이터베이스 설정

외부 주소에서 수신 대기하도록 MySQL을 구성했으므로 이제 데이터베이스를 만들고 원격 사용자를 설정해야 합니다. MySQL 자체는 이제 다른 시스템이 연결할 수 있는 IP 주소에서 수신 대기하지만 현재 액세스할 수 있는 데이터베이스는 없습니다.

이것은 또한 사용자가 연결하는 위치에 따라 다른 권한을 설정할 수 있는 기회이기도 합니다. 실제로 동일한 사용자 이름일 수 있지만 다른 호스트와 연결된 두 개의 "사용자\를 만들 수 있습니다.

이것이 의미하는 바는 데이터베이스 서버 자체에 바인딩된 사용자를 생성하고 매우 광범위한 권한을 부여할 수 있다는 것입니다. 그런 다음 동일한 사용자 이름을 사용할 수 있지만 웹 서버와 연결되어 있으며 WordPress에 필요한 권한만 부여합니다.

이렇게 하면 데이터베이스 서버에 로그인한 동안 무거운 작업을 수행할 수 있으며 웹 서버에는 작업을 수행하는 데 필요한 최소한의 권한만 제공됩니다. 이는 웹 서버가 손상된 경우 데이터베이스 서버를 부분적으로 보호하는 좋은 보안 정책입니다.

구성한 루트 계정과 관리 암호를 사용하여 MySQL에 연결하여 시작합니다.

mysql -u root -p

MySQL 루트 암호를 묻는 메시지가 표시되면 MySQL 프롬프트가 표시됩니다.

이를 시작하기 위해 WordPress에서 사용할 데이터베이스를 생성해 보겠습니다. 나중에 쉽게 식별할 수 있도록 이 wordpress를 호출합니다.

CREATE DATABASE wordpress;

이제 데이터베이스가 있으므로 필요한 경우 더 집중적인 데이터베이스 작업을 수행하는 데 사용할 로컬 사용자를 만들어야 합니다. 우리는 이 사용자 wordpressuser를 호출하고 이 계정이 선언에서 localhost를 사용하여 데이터베이스 서버 자체에서 시작된 연결 시도와만 일치하도록 만들 것입니다.

CREATE USER 'wordpressuser'@'localhost' IDENTIFIED BY 'password';

계속해서 이 계정에 데이터베이스에 대한 전체 액세스 권한을 부여하겠습니다.

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost';

이 사용자는 이제 WordPress용 데이터베이스에서 모든 작업을 수행할 수 있지만 이 계정은 로컬 시스템의 연결과만 일치하므로 원격으로 사용할 수 없습니다.

웹 서버의 연결과만 일치하는 컴패니언 계정을 만들어 봅시다. 이를 위해서는 웹 서버의 IP 주소가 필요합니다. 이 계정의 이름은 무엇이든 지정할 수 있지만 보다 일관된 경험을 위해 호스트 부분만 수정하여 위와 동일한 사용자 이름을 사용할 것입니다.

my.cnf 파일에서 구성한 것과 동일한 네트워크를 활용하는 IP 주소를 사용해야 합니다. 즉, 사설 네트워킹 IP를 사용한 경우 웹 서버의 사설 IP를 사용하려면 아래 규칙을 만들어야 합니다. 공용 인터넷을 사용하도록 MySQL을 구성한 경우 웹 서버의 공용 IP 주소와 일치시켜야 합니다.

CREATE USER 'wordpressuser'@'web_server_IP' IDENTIFIED BY 'password';

이제 원격 계정이 있으므로 WordPress가 정상적인 상황에서 작동하는 데 필요한 사용 가능한 권한의 하위 집합을 원격 계정에 부여할 수 있습니다. select, delete, insert 및 update입니다.

이것이 최종 목표이지만 실제로 현 시점에서 이를 구현할 수는 없습니다. 이는 특정 작업 중에 더 많은 액세스를 허용하기 위해 일시적으로 권한을 조정해야 하기 때문입니다. 이 중 하나는 실제로 초기 설치입니다. 현재 모든 권한을 부여하는 것이 가장 쉬운 방법이며 설치가 끝나면 권한을 제한합니다.

참고로 계정을 잠그는 데 사용할 명령은 다음과 같습니다(걱정하지 마세요. 이 명령은 필요할 때 다시 알려드립니다).

GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip';

그러나 지금은 일시적으로 모든 권한을 부여하여 당분간 로컬 계정과 사실상 동일하게 만듭니다.

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'web_server_ip';

WordPress를 구성한 후 이 문제로 다시 돌아오겠습니다. 실제로 WordPress를 설치하지 않고 데이터베이스에서 웹 서버를 분리하는 방법을 배우기 위해 이 가이드를 사용하는 경우 지금 더 제한적인 설정을 사용할 수 있습니다. 웹 애플리케이션에 따라 다르므로 애플리케이션에 필요한 최소 데이터베이스 권한을 찾으십시오.

디스크에 쓸 수 있는 권한을 플러시하고 사용을 시작합니다.

FLUSH PRIVILEGES;

이제 다음을 입력하여 MySQL 프롬프트를 종료할 수 있습니다.

exit

원격 및 로컬 연결 테스트

계속하기 전에 wordpressuser 계정을 사용하여 로컬 컴퓨터와 웹 서버 모두에서 데이터베이스에 연결할 수 있는지 확인하는 것이 가장 좋습니다.

먼저 새 계정으로 로그인을 시도하여 데이터베이스 시스템에서 연결을 테스트합니다.

mysql -u wordpressuser -p

프롬프트가 표시되면 이 계정에 대해 설정한 비밀번호를 입력하십시오.

MySQL 프롬프트가 표시되면 로컬 연결이 성공한 것입니다. 다음을 입력하여 다시 종료할 수 있습니다.

exit

원격 연결을 테스트하려면 웹 서버에 로그인하십시오.

웹 서버에서 원격 데이터베이스에 액세스하려면 MySQL용 클라이언트 도구를 설치해야 합니다. 로컬 패키지 캐시를 업데이트한 다음 클라이언트 유틸리티를 설치합니다.

sudo apt-get update
sudo apt-get install mysql-client

이제 다음 구문을 사용하여 데이터베이스 서버에 연결할 수 있습니다.

mysql -u wordpressuser -h database_server_IP -p

다시 말하지만 데이터베이스 서버에 올바른 IP 주소를 사용하고 있는지 확인해야 합니다. 사설 네트워크에서 수신 대기하도록 MySQL을 구성한 경우 데이터베이스의 사설 네트워크 IP를 입력하고 그렇지 않으면 데이터베이스 서버의 공용 IP 주소를 입력합니다.

wordpressuser 계정의 암호를 묻는 메시지가 표시되고 모든 작업이 제대로 수행되면 MySQL 프롬프트가 표시됩니다.

이것이 성공하면 이제 원격으로 연결할 수 있음을 확인했으므로 계속 진행하고 프롬프트를 종료할 수 있습니다.

추가 확인을 위해 세 번째 서버에서 동일한 작업을 수행하여 다른 서버에 액세스 권한이 부여되지 않았는지 확인할 수 있습니다. 로컬 액세스 및 웹 서버에서의 액세스를 확인했지만 다른 연결이 거부되는지 확인하지 않았습니다.

계속해서 특정 사용자 계정을 구성하지 않은 서버에서 동일한 절차를 시도하십시오. 위와 같이 클라이언트 유틸리티를 설치해야 할 수도 있습니다.

mysql -u wordpressuser -h database_server_IP -p

이것은 성공적으로 완료되지 않아야 합니다. 다음과 같은 오류가 발생합니다.

ERROR 1130 (HY000): Host '11.111.111.111' is not allowed to connect to this MySQL server

이것이 우리가 기대하고 원하는 것입니다.

웹 서버 설정

이제 웹 서버가 WordPress 데이터베이스에 액세스할 수 있음을 확인했으므로 Nginx, PHP 및 필요한 구성 요소를 구성하여 실제로 이를 웹 서버로 만들어야 합니다.

위의 테스트를 실행하기 위해 패키지 색인을 업데이트했으므로 다시 수행할 필요가 없습니다. 필요한 모든 패키지를 설치해 보겠습니다.

sudo apt-get install nginx php5-fpm php5-mysql

모든 것이 설치되면 소프트웨어 구성을 시작할 수 있습니다.

PHP 구성

매우 쉽기 때문에 PHP부터 시작하겠습니다.

동적 콘텐츠를 처리할 php-fpm용 PHP 구성 파일을 엽니다. 다음에서 하나의 값만 수정하면 됩니다.

sudo nano /etc/php5/fpm/php.ini

cgi.fix_pathinfo 매개변수를 검색하십시오. \;”로 주석 처리될 가능성이 높습니다. 문자를 입력하고 "1\로 설정합니다. 행의 주석 처리를 제거하고 "0\으로 설정하여 이 두 조건을 모두 반대로 해야 합니다.

cgi.fix_pathinfo=0

이것은 보안 조치입니다. 이 옵션을 설정하면 정확히 일치하는 항목이 없는 경우 사용자가 액세스하려고 시도한 파일을 추측하지 않도록 PHP에 지시합니다. 이를 설정하지 않으면 악의적인 사용자가 이 기회를 이용하여 서버에서 원하지 않는 코드를 실행하도록 할 수 있습니다.

완료되면 파일을 저장하고 닫습니다.

다음으로 PHP 프로세서와 웹 서버가 통신하는 방식을 수정하기 위해 다른 파일을 열어야 합니다.

sudo nano /etc/php5/fpm/pool.d/www.conf

127.0.0.1:9000으로 설정되어야 하는 listen 지시문을 찾습니다. 포트를 사용하는 대신 이것을 유닉스 도메인 소켓으로 설정하겠습니다.

listen = /var/run/php5-fpm.sock

완료되면 파일을 저장하고 닫습니다.

이제 값이 있으므로 PHP 프로세서를 다시 시작합니다.

sudo service php5-fpm restart

Nginx 구성

이제 Nginx를 구성할 준비가 되었습니다. 작업할 수 있는 새 파일에 기본 가상 호스트 파일을 복사하여 시작할 수 있습니다. 우리 사이트의 도메인 이름을 따서 이름을 지정합니다. 자리 표시자 "example.com\을 사용하겠습니다.

sudo cp /etc/nginx/sites-available/default /etc/nginx/sites-available/example.com

이제 방금 복사한 파일을 엽니다.

sudo nano /etc/nginx/sites-available/example.com

내부에서 서버 블록(server 괄호 안에 포함된 섹션)을 수정합니다. 포트 80을 수신하도록 지시문의 주석을 제거하여 시작합니다. 또한 루트 디렉터리를 변경하고 Nginx가 기본적으로 PHP 인덱스 파일을 제공하도록 합니다.

server {
    listen 80;
    root /var/www/example.com;
    index index.php index.hmtl index.htm;

다음으로 도메인 이름을 사용하도록 server_name 지시문을 수정하고 try_files가 올바르게 설정되었는지 확인하고(파일이 없는 경우 PHP에 요청 전달) 오류 페이지는 다음과 같이 구성됩니다.

server {
    listen 80;
    root /var/www/example.com;
    index index.php index.hmtl index.htm;
    server_name example.com;
    location / {
        try_files $uri $uri/ /index.php?q=$uri&$args;
    }
    error_page 404 /404.html;
    error_page 500 502 503 504 /50x.html;
    location = /50x.html {
        root /usr/share/nginx/www;
    }

마지막으로 모든 PHP 요청과 일치하는 위치 블록을 사용하여 실제 PHP 처리를 설정해야 합니다. 정확히 일치하는 항목이 없으면 즉시 404를 반환합니다. 또한 PHP용으로 구성한 소켓을 사용합니다.

server {
    listen 80;
    root /var/www/example.com;
    index index.php index.hmtl index.htm;
    server_name example.com;
    location / {
        try_files $uri $uri/ /index.php?q=$uri&$args;
    }
    error_page 404 /404.html;
    error_page 500 502 503 504 /50x.html;
    location = /50x.html {
        root /usr/share/nginx/www;
    }
    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_pass unix:/var/run/php5-fpm.sock;
        fastcgi_index index.php;
        include fastcgi_params;
    }
}

이것이 서버 블록 구성의 끝입니다. 파일을 저장하고 닫습니다.

이제 이것을 "enabled\ 디렉토리에 연결하고 기본 서버 블록 파일에 대한 링크를 제거합니다.

sudo rm /etc/nginx/sites-enabled/default
sudo ln -s /etc/nginx/sites-available/example.com /etc/nginx/sites-enabled/

다음 변경 사항을 적용하려면 Nginx를 다시 시작하세요.

sudo service nginx restart

워드프레스 설치

이제 웹 서버가 PHP 처리로 설정되었고 데이터베이스 서버가 있으므로 이를 활용하고 데이터베이스에 연결하는 애플리케이션을 설치해야 합니다. 지금까지 아시다시피 이 자습서에서는 WordPress를 사용하여 이 기능을 시연합니다.

최신 WordPress tarball을 홈 디렉토리에 다운로드합니다.

cd ~
wget http://wordpress.org/latest.tar.gz

파일을 추출하면 홈 디렉토리에 "wordpress\라는 디렉토리가 생성됩니다.

tar xzvf latest.tar.gz

WordPress에는 샘플 구성 파일이 포함되어 있지만 기본적으로 제공되지는 않습니다. 이 파일을 올바르게 읽을 수 있도록 이름을 바꾼 다음 텍스트 편집기에서 열어 변경 사항을 적용할 수 있습니다.

cp ~/wordpress/wp-config-sample.php ~/wordpress/wp-config.php
nano ~/wordpress/wp-config.php

내부에는 원격 데이터베이스에 대한 올바른 값을 입력해야 합니다. 이전에 원격 데이터베이스 테스트에서 사용한 것과 동일한 IP 주소를 사용해야 합니다.

/** The name of the database for WordPress */
define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'wordpressuser');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', 'database_server_ip');

완료되면 파일을 닫습니다. 이것은 실제로 웹 서버와 데이터베이스 서버를 명시적으로 연결하는 전체 구성의 유일한 부분입니다.

다음으로 Nginx 서버 블록 구성에서 설정한 디렉토리 구조를 생성해야 합니다. 기억하시겠지만 저는 데모에 "example.com\을 사용하고 있지만 Nginx 구성에서 지정한 대로 사용해야 합니다.

sudo mkdir -p /var/www/example.com

그런 다음 ~/wordpress 디렉토리에 있는 파일과 디렉토리를 방금 만든 새 문서 루트에 복사합니다.

sudo cp -r ~/wordpress/* /var/www/example.com

이제 모든 파일이 제자리에 있습니다. 남은 일은 권한과 파일 소유권을 약간 수정하는 것입니다. 서버의 문서 루트로 이동하여 시작해야 합니다.

cd /var/www/example.com

이 디렉토리에 있는 모든 파일을 www-data라는 웹 서버 사용자에게 제공할 것입니다.

sudo chown -R www-data:www-data *

우리는 여전히 이러한 파일을 루트가 아닌 일반 사용자로 편집할 수 있기를 원하므로 웹 서버의 루트가 아닌 일반 계정을 웹 서버 그룹에 추가할 수 있습니다. 그런 다음 해당 그룹에 이 디렉터리의 파일을 수정할 수 있는 권한을 부여할 수 있습니다.

sudo usermod -a -G www-data your_user
sudo chmod -R g+rw /var/www/example.com

웹 인터페이스를 통해 사이트 설정

이제 웹 인터페이스를 통해 설치를 완료하기만 하면 됩니다.

웹 서버와 연결된 도메인 이름(또는 공용 IP 주소)으로 이동합니다.

http://example.com

관련 정보를 입력해야 하는 WordPress 설치 화면이 표시됩니다.

구성을 설정했으면 방금 만든 계정을 사용하여 애플리케이션에 로그인해야 합니다.

사이트 구성을 시작할 수 있는 관리 대시보드로 이동합니다.

원격 데이터베이스 권한 제한

WordPress 구성을 마쳤으면 돌아가서 원격 데이터베이스 사용자에 대한 일부 권한을 취소해야 합니다.

대부분의 데이터베이스 권한은 일상적인 작업에 필요하지 않으며 업데이트 또는 플러그인 설치 중에만 활성화하면 됩니다. 이러한 단계를 수행한 후 관리 작업을 수행할 때 오류가 발생하는 경우 이를 염두에 두십시오.

특정 플러그인에는 추가 권한이 필요할 수도 있습니다. 각 플러그인을 조사하여 필요한 항목을 확인하고 최소한의 추가 액세스가 필요한 플러그인을 선택하는 것이 좋습니다.

데이터베이스 서버에 로그인하십시오. 그런 다음 MySQL 루트 계정을 사용하여 MySQL에 로그인합니다.

mysql -u root -p

프롬프트에 액세스하려면 암호를 입력하십시오.

다음을 입력하여 원격 사용자의 현재 권한을 볼 수 있습니다.

show grants for 'wordpressuser'@'web_server_IP';

+---------------------------------------------------------------------------------------------------------------------------+
| Grants for wordpressuser@xx.xxx.xxx.xxx                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpressuser'@'xx.xxx.xxx.xxx' IDENTIFIED BY PASSWORD '*5FD2B7524254B7F81B32873B1EA6D681503A5CA9' |
| GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wordpressuser'@'xx.xxx.xxx.xxx'                                                 |
+---------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

"usage\ 권한은 실제로 실제 권한이 없음을 의미하므로 이에 대해 걱정할 필요가 없습니다. 두 번째 권한 라인은 처음에 wordpress 데이터베이스에 대한 모든 권한을 허용하도록 설정한 것입니다.

현재 권한보다 더 제한적인 새로운 권한을 적용하는 절차는 실제로 두 단계입니다.

먼저 현재 권한을 모두 취소해야 합니다. 다음과 같이 입력하면 됩니다.

REVOKE ALL PRIVILEGES on wordpress.* FROM 'wordpressuser'@'web_server_IP';

지금 현재 보조금을 요청하면 두 번째 줄이 사라진 것을 볼 수 있습니다.

show grants for 'wordpressuser'@'web_server_IP';

+---------------------------------------------------------------------------------------------------------------------------+
| Grants for wordpressuser@10.128.213.175                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpressuser'@'xx.xxx.xxx.xxx' IDENTIFIED BY PASSWORD '*5FD2B7524254B7F81B32873B1EA6D681503A5CA9' |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

이제 원하는 권한을 계정에 다시 추가할 수 있습니다. 매일 사용하려면 UPDATE, INSERT, SELECT 및 DELETE 권한이 필요합니다.

GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip';

다시 확인하면 세분화된 권한이 설정되었음을 알 수 있습니다.

변경 사항을 구현하기 위해 권한 테이블을 다시 읽도록 MySQL에 지시하려면 다음을 입력할 수 있습니다.

FLUSH PRIVILEGES;

그런 다음 MySQL을 다시 종료합니다.

exit

결론

따라하고 있었다면 이제 애플리케이션과 통신하는 원격 데이터베이스를 얻는 방법을 잘 이해했을 것입니다. 몇 가지 WordPress 관련 단계를 살펴보았지만 일반적인 아이디어, 특히 MySQL 구성 및 사용자 권한과 관련된 아이디어는 대부분의 원격 MySQL 상황에 적용할 수 있습니다.