Как настроить удаленную базу данных для оптимизации производительности сайта с помощью MySQL на Ubuntu 20.04

Введение

По мере роста вашего приложения или веб-сайта может возникнуть момент, когда вы выросли из вашей текущей настройки сервера. Если вы разместили свой веб-сервер и базу данных на одной машине, может быть хорошей идеей разделить эти две функции, чтобы каждая могла работать на своем собственном оборудовании и разделять нагрузку по ответу на запросы ваших посетителей.

В этом руководстве вы настроите удаленный сервер базы данных MySQL, к которому может подключаться ваше веб-приложение. WordPress используется в качестве примера, чтобы иметь что-то, с чем работать, но техника широко применима к любому приложению, поддерживаемому MySQL.

Предварительные требования

Прежде чем приступить к этому руководству, вам понадобится:

• Два сервера Ubuntu 20.04. На каждом из них должен быть создан пользователь без прав root с привилегиями sudo и включенным брандмауэром UFW, как описано в нашем Руководстве по начальной настройке сервера с Ubuntu 20.04. Один из этих серверов будет использоваться для размещения вашей MySQL базы данных и будет называться сервером баз данных на протяжении этого руководства. Другой сервер будет подключаться к вашему серверу баз данных удаленно и выступать в качестве вашего веб-сервера; соответственно, он будет называться веб-сервером на протяжении этого руководства.
• На вашем сервере баз данных: установлен MySQL. Следуйте Инструкции по установке MySQL на Ubuntu 20.04, чтобы настроить его.
• На вашем веб-сервере: Установлены Nginx и PHP. Наше руководство Как установить Linux, Nginx, MySQL, PHP (стек LEMP) в Ubuntu 20.04 проведет вас через процесс, но имейте в виду, что вы должны пропустить Шаг 2 этого руководства, который фокусируется на установке MySQL, так как вы установите MySQL на своем базовом сервере данных.
  • По желанию (но настоятельно рекомендуется), установлены сертификаты TLS/SSL от Let’s Encrypt на вашем веб-сервере. Вам потребуется приобрести доменное имя и настроить DNS-записи для вашего сервера, но сами сертификаты бесплатны. Наше руководство Как обеспечить безопасность Nginx с помощью Let’s Encrypt в Ubuntu 20.04 покажет вам, как получить эти сертификаты.

Шаг 1 — Настройка MySQL для прослушивания удаленных подключений на сервере баз данных

Хранение данных на отдельном сервере – хороший способ плавного расширения после достижения предела производительности конфигурации на одной машине. Это также предоставляет базовую структуру, необходимую для балансировки нагрузки и дальнейшего расширения вашей инфраструктуры еще больше впоследствии. После установки MySQL, следуя рекомендациям предварительного учебника, вам потребуется изменить некоторые значения конфигурации, чтобы разрешить подключения с других компьютеров.

Большинство изменений конфигурации сервера MySQL можно внести в файл mysqld.cnf, который по умолчанию хранится в каталоге /etc/mysql/mysql.conf.d/. Откройте этот файл на вашем сервере баз данных с привилегиями root в вашем предпочтительном редакторе. В этом примере используется nano:

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

Этот файл разделен на разделы, обозначенные метками в квадратных скобках ([ и ]). Найдите раздел, помеченный mysqld:

. . .
[mysqld]
. . .

Внутри этого раздела найдите параметр с именем bind-address. Это указывает программному обеспечению базы данных, на каком сетевом адресе слушать подключения.

По умолчанию это установлено на 127.0.0.1, что означает, что MySQL настроен на поиск только локальных подключений. Вам нужно изменить это, чтобы ссылаться на внешний IP-адрес, по которому можно подключиться к вашему серверу.

Если оба ваших сервера находятся в дата-центре с возможностями частной сети, используйте частный сетевой IP-адрес вашего сервера базы данных. В противном случае вы можете использовать его публичный IP-адрес:

[mysqld]
. . .
bind-address = db_server_ip

Поскольку вы будете подключаться к своей базе данных через интернет, рекомендуется требовать зашифрованные подключения, чтобы обеспечить безопасность ваших данных. Если вы не шифруете подключение к MySQL, любой пользователь в сети может перехватывать чувствительную информацию между вашим веб-сервером и сервером базы данных. Чтобы зашифровать подключения к MySQL, добавьте следующую строку после строки bind-address, которую вы только что обновили:

[mysqld]
. . .
require_secure_transport = on
. . .

Сохраните и закройте файл, когда закончите. Если вы используете nano, сделайте это, нажав CTRL + X, Y, а затем ENTER.

Чтобы SSL-подключения работали, вам нужно создать некоторые ключи и сертификаты. MySQL поставляется с командой, которая автоматически настраивает их. Запустите следующую команду, которая создаст необходимые файлы. Она также делает их доступными для чтения серверу MySQL, указав UID пользователя mysql:

sudo mysql_ssl_rsa_setup --uid=mysql

Чтобы заставить MySQL обновить свою конфигурацию и прочитать новую информацию о SSL, перезагрузите базу данных:

sudo systemctl restart mysql

Чтобы подтвердить, что сервер теперь прослушивает внешний интерфейс, выполните следующую команду ss:

sudo ss -plunt | grep mysqld

Output
tcp     LISTEN   0        70             127.0.0.1:33060          0.0.0.0:*      users:(("mysqld",pid=4053,fd=22))                                              
tcp     LISTEN   0        151        db_server_ip:3306           0.0.0.0:*      users:(("mysqld",pid=4053,fd=24))

ss выводит статистику о сетевой системе вашего сервера. Этот вывод показывает, что процесс с именем mysqld прикреплен к db_server_ip на порту 3306, стандартном порте MySQL, подтверждая, что сервер прослушивает соответствующий интерфейс.

Затем откройте этот порт в брандмауэре, чтобы разрешить прохождение трафика:

sudo ufw allow mysql

Это все конфигурационные изменения, которые вам нужно внести в MySQL. Далее вы настроите базу данных и некоторые пользовательские профили, один из которых вы будете использовать для удаленного доступа к серверу.

Шаг 2 — Настройка базы данных WordPress и удаленных учетных данных на сервере базы данных

Несмотря на то что MySQL теперь прослушивает внешний IP-адрес, в настоящее время не настроены пользователи или базы данных с разрешенным удаленным доступом. Создайте базу данных для WordPress и пару пользователей, которые могут к ней обращаться.

Начните с подключения к MySQL в качестве пользователя root MySQL:

sudo mysql

mysql -u root -p

Из приглашения MySQL создайте базу данных, которую будет использовать WordPress. Может быть полезно дать этой базе данных узнаваемое имя, чтобы вы могли легко идентифицировать ее позже. Здесь она названа wordpress:

CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Теперь, когда вы создали свою базу данных, вам нужно создать пару пользователей. Создайте локального пользователя, а также удаленного пользователя, привязанного к IP-адресу веб-сервера.

Сначала создайте локального пользователя local_db_user и сделайте эту учетную запись доступной только для локальных попыток подключения, используя localhost в объявлении:

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

Затем предоставьте этой учетной записи полный доступ к базе данных wordpress:

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

Теперь этот пользователь может выполнять любую операцию в базе данных WordPress, но эта учетная запись не может быть использована удаленно, так как она соответствует только подключениям с локальной машины. Имея это в виду, создайте сопутствующую учетную запись, которая будет соответствовать подключениям исключительно с вашего веб-сервера. Для этого вам понадобится IP-адрес вашего веб-сервера.

Пожалуйста, обратите внимание, что вы должны использовать IP-адрес, который использует ту же сеть, которую вы настроили в файле mysqld.cnf. Это означает, что если вы указали частный IP-адрес в файле mysqld.cnf, вам нужно будет включить частный IP вашего веб-сервера в следующие две команды. Если вы настроили MySQL для использования публичного интернета, вы должны сопоставить это с публичным IP-адресом вашего веб-сервера.

CREATE USER 'remote_user'@'web_server_ip' IDENTIFIED BY 'password';

После создания удаленной учетной записи дайте ей те же привилегии, что и вашему локальному пользователю:

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

Наконец, сбросьте привилегии, чтобы MySQL знал, что начать их использовать:

FLUSH PRIVILEGES;

Затем выйдите из приглашения MySQL, набрав:

exit

Теперь, когда вы настроили новую базу данных и пользователя с возможностью удаленного подключения, вы можете перейти к проверке, сможете ли вы подключиться к базе данных с веб-сервера:

Шаг 3 — Тестирование удаленных и локальных подключений

Прежде чем продолжить, лучше всего убедиться, что вы можете подключиться к вашей базе данных как с локальной машины — вашего сервера баз данных — так и с вашего веб-сервера:

Сначала протестируйте локальное подключение с вашего сервера баз данных, попытавшись войти с использованием вашей новой учетной записи:

mysql -u local_db_user -p

Когда будет запрошен пароль, введите пароль, который вы установили для этой учетной записи.

Если вы получите приглашение MySQL, значит, локальное подключение прошло успешно. Вы можете выйти снова, набрав:

exit

Затем войдите на свой веб-сервер, чтобы проверить удаленные подключения:

ssh remote_user@web_server_ip

Вам понадобится установить некоторые клиентские инструменты для MySQL на вашем веб-сервере, чтобы получить доступ к удаленной базе данных. Сначала обновите кэш локальных пакетов, если вы этого не делали недавно:

sudo apt update

Затем установите утилиты клиента MySQL:

sudo apt install mysql-client

После этого подключитесь к серверу баз данных, используя следующий синтаксис:

mysql -u remote_user -h db_server_ip -p

Снова убедитесь, что вы используете правильный IP-адрес сервера базы данных. Если вы настроили MySQL на прослушивание частной сети, введите частный IP-адрес вашей базы данных. В противном случае введите публичный IP-адрес сервера базы данных.

Вас попросят ввести пароль для вашей учетной записи remote_user. После ввода пароля, если все работает ожидаемо, вы попадете на приглашение MySQL. Проверьте, что соединение использует SSL с помощью следующей команды:

status

Если соединение действительно использует SSL, строка SSL: указывает на это, как показано здесь:

Output
--------------
mysql  Ver 8.0.29-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Connection id:          14
Current database:
Current user:           remote_user@web_server_ip
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.29-0ubuntu0.20.04.2 (Ubuntu)
Protocol version:       10
Connection:             db_server_ip via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 1 hour 8 min 28 sec

Threads: 2  Questions: 69  Slow queries: 0  Opens: 206  Flush tables: 3  Open tables: 125  Queries per second avg: 0.016
--------------

После проверки удаленного подключения выйдите из приглашения:

exit

Вы проверили локальный доступ и доступ с веб-сервера, но не проверили, что другие подключения будут отклонены. Для дополнительной проверки попробуйте выполнить то же самое с третьего сервера, для которого вы не настраивали конкретную учетную запись пользователя, чтобы убедиться, что этот другой сервер не получит доступ.не

Обратите внимание, что перед выполнением следующей команды для попытки подключения вам может потребоваться установить утилиты клиента MySQL, как вы делали ранее:

mysql -u wordpressuser -h db_server_ip -p

Это не должно завершиться успешно и должно выдать ошибку, подобную этой:

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

Это ожидаемо, поскольку вы не создали пользователя MySQL, который имеет право подключаться с этого сервера, и это желательно, так как вы хотите убедиться, что ваш сервер баз данных будет отклонять доступ неавторизованных пользователей к вашему серверу MySQL.

После успешного тестирования удаленного подключения вы можете приступить к установке WordPress на вашем веб-сервере.

Шаг 4 — Подготовка веб-сервера для установки WordPress

Если вы следовали предварительным руководствам, у вас должны быть установлены Nginx и PHP. WordPress, а также многие его плагины, используют множество дополнительных расширений PHP. Перед установкой WordPress на ваш веб-сервер подготовьте сервер, установив некоторые из этих расширений PHP для использования с WordPress:

sudo apt install php-curl php-gd php-intl php-mbstring php-soap php-xml php-xmlrpc php-zip

После установки расширений перезапустите процесс PHP-FPM, чтобы запущенный процессор PHP мог использовать новые установленные функции:

sudo systemctl restart php7.4-fpm

Затем внесите несколько изменений в файлы блоков сервера Nginx. Исходя из предварительных руководств, у вас должен быть файл конфигурации для вашего сайта в каталоге /etc/nginx/sites-available/. Он также должен быть настроен на ответ на доменное имя или IP-адрес вашего сервера и защищен сертификатом TLS/SSL. /etc/nginx/sites-available/your_domain используется здесь в качестве примера каталога. Замените путь к вашему файлу конфигурации при необходимости.

Кроме того, /var/www/your_domain будет использоваться в качестве корневого каталога в этом примере установки WordPress. Снова используйте указанный в вашей собственной конфигурации корневой каталог.

Откройте файл блока сервера вашего сайта в выбранном вами редакторе. В этом примере используется nano:

sudo nano /etc/nginx/sites-available/your_domain

Внутри этого блока сервера добавьте несколько блоков местоположения. Начните с создания точных соответствующих блоков местоположения для запросов к /favicon.ico и /robots.txt, которые вы не хотите регистрировать.

Вы сделаете это, используя регулярное выражение для сопоставления с любыми запросами на статические файлы, отключите регистрацию этих запросов и пометьте их как высоко кэшируемые, поскольку это обычно дорогостоящие ресурсы для обслуживания. Вы можете настроить этот список статических файлов, чтобы он содержал любые другие расширения файлов, используемые на вашем сайте:

server {
    server_name your_domain www.your_domain;
    root /var/www/your_domain;

    index index.html index.htm index.php;

    location = /favicon.ico { log_not_found off; access_log off; }
    location = /robots.txt { log_not_found off; access_log off; allow all; }
    location ~* \.(css|gif|ico|jpeg|jpg|js|png)$ {
        expires max;
        log_not_found off;
    }
    . . .
}

Внутри существующего блока location / вы можете настроить список try_files. Сначала закомментируйте стандартную настройку, добавив символ решетки (#) перед первой строкой. Затем добавьте следующие строки:

server {
    . . .
    location / {
        #try_files $uri $uri/ =404;
        try_files $uri $uri/ /index.php$is_args$args;
    }
    . . .
}

С этой новой строкой в вашей конфигурации вместо возврата ошибки 404 в качестве значения по умолчанию управление передается файлу index.php с аргументами запроса.

В общем, верхняя часть вашего файла должна быть похожа на это:

server {
    server_name your_domain www.your_domain;
    root /var/www/your_domain;

    index index.html index.htm index.php;

    location = /favicon.ico { log_not_found off; access_log off; }
    location = /robots.txt { log_not_found off; access_log off; allow all; }
    location ~* \.(css|gif|ico|jpeg|jpg|js|png)$ {
        expires max;
        log_not_found off;
    }

    location / {
       # try_files $uri $uri/ =404;
        try_files $uri $uri/ /index.php$is_args$args;
    }

  ...
}

Когда закончите, сохраните и закройте файл.

Проверьте вашу конфигурацию на наличие синтаксических ошибок, набрав:

sudo nginx -t

Если ошибок не сообщается, перезагрузите Nginx, набрав:

sudo systemctl reload nginx

После подготовки вашего веб-сервера вы готовы установить WordPress.

Шаг 5 – Установка WordPress на веб-сервер

Чтобы продемонстрировать возможности вашего нового сервера MySQL с поддержкой удаленного доступа, вы пройдете процесс установки и настройки WordPress – популярной системы управления контентом – на вашем веб-сервере. После настройки программного обеспечения сервера вы можете загрузить WordPress. По соображениям безопасности всегда рекомендуется получать последнюю версию WordPress непосредственно с веб-сайта проекта.

Сначала перейдите в каталог с возможностью записи (рекомендуется временный каталог, например /tmp):

cd /tmp

Это изменит ваш рабочий каталог на временную папку. Затем введите следующую команду, чтобы загрузить последнюю версию WordPress в виде сжатого файла:

curl -LO https://wordpress.org/latest.tar.gz

Разархивируйте сжатый файл, чтобы создать структуру каталогов WordPress:

tar xzvf latest.tar.gz

Вы собираетесь переместить эти файлы в ваш корневой документ в ближайшее время, но перед этим скопируйте образец файла конфигурации под именем файла, который читает WordPress:

cp /tmp/wordpress/wp-config-sample.php /tmp/wordpress/wp-config.php

Скопируйте все содержимое каталога в корневой каталог вашего документа. Флаг -a гарантирует сохранение ваших разрешений, и точка в конце исходного каталога указывает, что все внутри каталога должно быть скопировано (включая скрытые файлы):

sudo cp -a /tmp/wordpress/. /var/www/your_domain

После размещения ваших файлов, назначьте владельца пользователю и группе www-data. Это пользователь и группа, от имени которых работает Nginx, и Nginx должен иметь возможность читать и записывать файлы WordPress для обслуживания веб-сайта и выполнения автоматических обновлений:

sudo chown -R www-data:www-data /var/www/your_domain

Ваши файлы WordPress теперь находятся в корневом каталоге сервера и имеют правильное владение. Теперь вы готовы настроить WordPress.

Шаг 6 — Настройка файла конфигурации WordPress

При открытии файла конфигурации WordPress начните с настройки некоторых секретных ключей для обеспечения безопасности вашей установки. WordPress предоставляет безопасный генератор для этих значений, чтобы вам не приходилось придумывать их сами. Они используются только внутренне, поэтому сложные, безопасные значения здесь не повредят удобству.

Чтобы получить безопасные значения из генератора секретных ключей WordPress, введите:

curl -s https://api.wordpress.org/secret-key/1.1/salt/

Вы получите уникальные значения, похожие на следующий вывод:

Output
define('AUTH_KEY',         'L4|2Yh(giOtMLHg3#] DO NOT COPY THESE VALUES %G00o|te^5YG@)');
define('SECURE_AUTH_KEY',  'DCs-k+MwB90/-E(=!/ DO NOT COPY THESE VALUES +WBzDq:7U[#Wn9');
define('LOGGED_IN_KEY',    '*0kP!|VS.K=;#fPMlO DO NOT COPY THESE VALUES +&[%8xF*,18c @');
define('NONCE_KEY',        'fmFPF?UJi&(j-{8=$- DO NOT COPY THESE VALUES CCZ?Q+_~1ZU~;G');
define('AUTH_SALT',        '@qA7f}2utTEFNdnbEa DO NOT COPY THESE VALUES t}Vw+8=K%20s=a');
define('SECURE_AUTH_SALT', '%BW6s+d:7K?-`C%zw4 DO NOT COPY THESE VALUES 70U}PO1ejW+7|8');
define('LOGGED_IN_SALT',   '-l>F:-dbcWof%4kKmj DO NOT COPY THESE VALUES 8Ypslin3~d|wLD');
define('NONCE_SALT',       '4J(<`4&&F (WiK9K#] DO NOT COPY THESE VALUES ^ZikS`es#Fo:V6');

Это строки конфигурации, которые вы можете вставить непосредственно в свой файл конфигурации, чтобы установить безопасные ключи. Скопируйте полученный вывод сейчас.

Откройте файл конфигурации WordPress:

sudo nano /var/www/your_domain/wp-config.php

Найдите раздел, который содержит примерные значения для этих настроек:

. . .
define('AUTH_KEY',         'put your unique phrase here');
define('SECURE_AUTH_KEY',  'put your unique phrase here');
define('LOGGED_IN_KEY',    'put your unique phrase here');
define('NONCE_KEY',        'put your unique phrase here');
define('AUTH_SALT',        'put your unique phrase here');
define('SECURE_AUTH_SALT', 'put your unique phrase here');
define('LOGGED_IN_SALT',   'put your unique phrase here');
define('NONCE_SALT',       'put your unique phrase here');
. . .

Удалите эти строки и вставьте значения, скопированные из командной строки:

. . .
define('AUTH_KEY',         'VALUES COPIED FROM THE COMMAND LINE');
define('SECURE_AUTH_KEY',  'VALUES COPIED FROM THE COMMAND LINE');
define('LOGGED_IN_KEY',    'VALUES COPIED FROM THE COMMAND LINE');
define('NONCE_KEY',        'VALUES COPIED FROM THE COMMAND LINE');
define('AUTH_SALT',        'VALUES COPIED FROM THE COMMAND LINE');
define('SECURE_AUTH_SALT', 'VALUES COPIED FROM THE COMMAND LINE');
define('LOGGED_IN_SALT',   'VALUES COPIED FROM THE COMMAND LINE');
define('NONCE_SALT',       'VALUES COPIED FROM THE COMMAND LINE');
. . .

Затем измените некоторые настройки подключения к базе данных в начале файла. Вам придется настроить имя базы данных, пользователя базы данных и связанный с ним пароль, который был настроен в MySQL.

Введите информацию о подключении к вашей удаленной базе данных. Помните использовать тот же IP-адрес и учетные данные, которые вы использовали в тесте удаленной базы данных ранее:

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

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

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

/** MySQL hostname */
define('DB_HOST', 'db_server_ip');
. . .

И, наконец, после строки define( 'DB_COLLATE', '' );, добавьте эту выделенную строку, которая сообщает WordPress использовать SSL-соединение с вашей базой данных MySQL:

...
define( 'DB_COLLATE', '' );

define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);
...

Сохраните и закройте файл.

После настройки конфигурации WordPress установлен и вы готовы пройти через его веб-интерфейсную установочную программу.

Шаг 7 — Настройка WordPress через веб-интерфейс

WordPress имеет веб-интерфейс для настройки. Пройдя его, вам будут заданы несколько вопросов, и будут установлены все необходимые таблицы в вашей базе данных. Здесь вы выполните начальные шаги настройки WordPress, которые вы можете использовать в качестве отправной точки для создания собственного веб-сайта с удаленной базой данных.

Перейдите на доменное имя (или публичный IP-адрес), связанное с вашим веб-сервером, вместе с дополнительной ссылкой /wp-admin:

http://your_domain.com/wp-admin

Вас попросят выбрать язык, который вы хотели бы использовать:

Далее вы попадете на основную страницу настройки.

Выберите название для вашего сайта на WordPress и выберите имя пользователя. Рекомендуется выбрать что-то уникальное и избегать общих имен пользователей, таких как “admin” из соображений безопасности. Сильный пароль генерируется автоматически. Сохраните этот пароль или выберите альтернативный сильный пароль.

Введите свой адрес электронной почты и выберите, хотите ли вы отключить индексацию вашего сайта поисковыми системами:

После того как вы введете свои данные, вам потребуется войти в интерфейс администратора WordPress, используя только что созданную учетную запись. После этого вы попадете на панель управления, где сможете настроить свой новый сайт на WordPress.

Заключение

Следуя этому руководству, вы настроили базу данных MySQL для приема защищенных SSL-соединений от удаленной установки WordPress. Команды и методы, использованные в этом руководстве, применимы к любому веб-приложению, написанному на любом языке программирования, но конкретные детали реализации будут отличаться. Обратитесь к документации по базе данных вашего приложения или языка для получения дополнительной информации.