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

По мере роста сайта или приложения трафик будет увеличиваться и, вероятно, исходных ресурсов станет не хватать. Если веб-сервер и база данных обслуживаются одним сервером, вы сможете увеличить производительность, переместив БД на отдельный сервер.

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

Требования

  • Два сервера Ubuntu 16.04 (сервер 1 и сервер 2), настроенные согласно этому руководству.
  • Сервер 1 будет обслуживать веб-сервер. Установите на него стек LEMP (инструкции найдете здесь, раздел по установке MySQL можно пропустить).
  • Опционально: защитите стек LEMP SSL-сертификатом. Чтобы создать сертификат Let’s Encrypt, вам понадобится домен.

1: Установка MySQL на сервер базы данных

Переместив обработку БД на отдельный сервер, вы увеличите производительность сайта. Кроме того, так вы получите базовую структуру для настройки балансировки нагрузки.

Для начала нужно установить MySQL на сервер 2, который будет выступать в качестве сервера БД.

Чтобы сделать это, введите:

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

Создайте и подтвердите уникальный root-пароль MySQL.

Чтобы убедиться, что установка MySQL прошла успешно и программа запустилась, используйте systemctl:

systemctl status mysqlmysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2017-05-23 14:54:04 UTC; 12s ago
Main PID: 27179 (mysqld)
CGroup: /system.slice/mysql.service
└─27179 /usr/sbin/mysqld

Строка Active: active (running) сообщает, что установка и запуск MySQL прошли успешно.

Теперь нужно запустить сценарий безопасности MySQL.

mysql_secure_installation

Введите root-пароль MySQL и нажмите Enter.

Сценарий предложит включить плагин валидации паролей, который автоматически будет проверять сложность паролей пользователей MySQL. Если вы хотите повысить безопасность, включите этот плагин. Для этого введите y и Enter. Чтобы пропустить эту настройку, просто нажмите Enter.

Если вы включили плагин, сценарий предложит выбрать уровень защиты от 0 до 2. Введите нужный уровень и нажмите Enter.

Затем будет предложено изменить root-пароль MySQL. Поскольку пароль был создан только что, можно пропустить этот этап. Просто нажмите Enter.

На остальные запросы можно ответить yes. Вам будет предложено удалить анонимных пользователей MySQL, запретить удаленный вход для root, удалить тестовую базу данных и перезагрузить таблицы привилегий, чтобы предыдущие изменения вступили в силу. Чтобы принять все эти предложения, введите y и нажмите Enter.

Сценарий завершит работу после того, как получит ответы на все запросы. Теперь установка MySQL достаточно защищена. На следующем этапе нужно настроить удаленный доступ к MySQL.

2: Настройка MySQL для поддержки удаленных соединений

Откройте конфигурационный файл mysqld.

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

Этот файл состоит из разделов, заголовки которых берутся в квадратные скобки. Найдите раздел mysqld:

. . .
[mysqld]

В нем найдите параметр bind-address, который определяет, какие сетевые адреса нужно прослушивать.

На данный момент MySQL прослушивает только локальные соединения. Это нужно изменить.

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

[mysqld]
. . .
bind-address = db_server_ip

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

. . .
require_secure_transport = on

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

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

sudo mysql_ssl_rsa_setup --uid=mysql

Команда создаст необходимые файлы и сделает их доступными для чтения сервером MySQL (—uid = mysql).

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

sudo systemctl restart mysql

Чтобы убедиться, что сервер прослушивает удаленные соединения, используйте netstat:

sudo netstat -plunt | grep mysqld
tcp        0      0 db_server_ip:3306     0.0.0.0:*               LISTEN      27328/mysqld

Эта команда выведет сетевую статистику сервера. Как видите, процесс mysqld привязан к адресу db_server_ip по стандартному порту MySQL 3306.

Откройте этот порт в брандмауэре:

sudo ufw allow mysql

3: Настройка БД для WordPress и создание учетных данных

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

Подключитесь к MySQL как root:

mysql -u root -p

Введите root-пароль MySQL, после чего командная строка изменится:

mysql>

Создайте БД для WordPress, например:

CREATE DATABASE wordpress;

Примечание: Все операторы SQL должны заканчиваться точкой с запятой (;). Если вы, нажав Enter в команде MySQL, видите только строку ->, вы, вероятно, забыли про точку с запятой. Просто введите символ в новой строке и снова нажмите Enter, чтобы продолжить.

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

Чтобы создать локального пользователя БД, введите:

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

Передайте этому пользователю все права на БД.

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

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

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

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

CREATE USER 'wordpressuser'@'web-server_ip' IDENTIFIED BY 'password';

Теперь у вас есть пользователь для удаленного доступа к БД. Передайте ему права на БД:

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

Затем нужно сбросить привилегии, чтобы обновить настройки:

FLUSH PRIVILEGES;

Закройте командную строку MySQL:

exit

4: Тестирование удаленных и локальных подключений

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

Сначала проверьте локальное соединение с сервером 2:

mysql -u wordpressuser -p

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

exit

Теперь перейдите на сервер 1.

Для начала установите клиентские инструменты MySQL, чтобы получить доступ к удаленной БД. Обновите индекс пакетов и установите все необходимое:

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

Подключитесь к серверу 2:

mysql -u wordpressuser -h db_server_ip -p

Примечание: Помните о том, что IP-адреса должны находиться в одной сети. Если вы используете внутренний адрес на сервере MySQL, используйте такой же адрес на сервере 1.

Будет запрошен пароль wordpressuser. Если соединение было успешно создано, вы увидите командную строку MySQL. Убедитесь, что соединение использует SSL:

status
--------------
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper
Connection id:      52
Current database:
Current user:       wordpressuser@203.0.113.111
SSL:         Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:   10
Connection:     203.0.113.111 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         3 hours 43 min 40 sec
Threads: 1  Questions: 1858  Slow queries: 0  Opens: 276  Flush tables: 1  Open tables: 184  Queries per second avg: 0.138
--------------

Строка SSL: говорит о том, что соединение использует шифрование SSL. Закройте командную строку.

exit

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

Перейдите на сервер 3, у которого нет доступа к БД.

Примечание: Возможно, вам понадобится установить утилиты, как на сервер 1.

Введите:

mysql -u wordpressuser -h db_server_ip -p

Команда должна выдать ошибку:

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

5: Установка WordPress

Чтобы проверить возможности новой удаленной БД MySQL, установите на сервер 1 WordPress – популярную платформу для размещения сайтов и блогов. Для этого нужно загрузить ПО, настроить подключение и запустить установку WordPress в браузере.

Перейдите на сервер 1 и загрузите последний релиз WordPress в домашний каталог:

cd
curl -O https://wordpress.org/latest.tar.gz

Распакуйте архив, после чего в домашнем каталоге появится каталог wordpress:

tar xzvf latest.tar.gz

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

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

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

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

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

В терминале появятся ключи, которые нужно скопировать и вставить в файл wp-config.php.

Внимание! Не копируйте приведенные ниже ключи! Используйте свои уникальные значения.

define('AUTH_KEY',         '1jl/vqfs<XhdXoAPz9 DO NOT COPY THESE VALUES c_j{iwqD^<+c9.k<J@4H');
define('SECURE_AUTH_KEY',  'E2N-h2]Dcvp+aS/p7X DO NOT COPY THESE VALUES {Ka(f;rv?Pxf})CgLi-3');
define('LOGGED_IN_KEY',    'W(50,{W^,OPB%PB<JF DO NOT COPY THESE VALUES 2;y&,2m%3]R6DUth[;88');
define('NONCE_KEY',        'll,4UC)7ua+8<!4VM+ DO NOT COPY THESE VALUES #`DXF+[$atzM7 o^-C7g');
define('AUTH_SALT',        'koMrurzOA+|L_lG}kf DO NOT COPY THESE VALUES  07VC*Lj*lD&?3w!BT#-');
define('SECURE_AUTH_SALT', 'p32*p,]z%LZ+pAu:VY DO NOT COPY THESE VALUES C-?y+K0DK_+F|0h{!_xY');
define('LOGGED_IN_SALT',   'i^/G2W7!-1H2OQ+t$3 DO NOT COPY THESE VALUES t6**bRVFSD[Hi])-qS`|');
define('NONCE_SALT',       'Q6]U:K?j4L%Z]}h^q7 DO NOT COPY THESE VALUES 1% ^qUswWgn+6&xqHN&%');

Скопируйте ключи и откройте конфигурационный файл:

nano ~/wordpress/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');
. . .

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

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

. . .
/** 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', 'db_server_ip');
. . .

Следующая строка настроит WordPress для поддержки SSL при соединении с MySQL. Добавьте ее в любую точку файла.

define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

Скопируйте файлы и каталоги из ~/wordpress в корневой каталог Nginx. Флаг –а сохранит все права файлов.

sudo cp -a ~/wordpress/* /var/www/html

Теперь все файлы на месте. Остается только изменитьправа собственности на них. Передайте права на корневой каталог пользователю веб-сервера, www-data:

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

6: Установка WordPress в браузере

Процедура настройки WordPress в браузере задаст несколько вопросов и установит необходимые таблицы. Запустите ее.

Откройте в браузере:

http://example.com

Сначала будет предложено выбрать язык установки. Выберите язык, после чего вы перейдете к главной странице:

Welcome!
Welcome to the famous five-minute WordPress installation process! …

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

Tags: , , ,