Настройка репликации баз данных MySQL

Итак, для разнесения нагрузки на базу данных MySQL нам нужно настроить репликацию. В данном случае master - сервер, который будет работать как обычно и данные в него будут как записываться, так и читаться, а slave - это своеобразное зеркало, откуда можно только читать, но не писать. Его можно будет оптимизировать для чтения для быстрого выполнения бэкапов или для быстрого заполнения сайтов. Итак:

Настраиваем master сервер

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

Включаем сеть. Для этого в настройках смотрим на эти 2 строчки

skip-networking
bind-address = 127.0.0.1

Если такие есть, закомментируйте их. Нам нужна возможность обращаться к этому серверу по сети другим сервером.

Теперь включаем логирование запросов. Если возможность есть, записывайте их на виртуальный диск в памяти. И скорость выше и меньше дергать жёсткие диски (если база сильно нагружена, значит дискам тоже, вполне вероятно, не до отдыха). Смотрим в конфиг и добавляем это:

log-bin = /var/log/mysql/mysql-bin.log #куда логируем
binlog-dopdb=exampledb #какую базу
server-id=1 #указываем, что это master сервер, с которого будет идти репликация

Если добавили, перегружаем базу

/etc/init.d/mysql restart

И проверяем, в сети ли наша база

netstat -antpu | grep mysql

Должны увидеть что-то типа

0.0.0.0:3306

Теперь зайдём в базу и создадим пользователя для репликации. Вполне правильно, не рутом же реплицировать данные, руту вообще удалённый доступ нужно запретить. Заходим:

mysql -u root -p

вводим пароль root для MySQL (не системный root)

Enter password:

Итак, у нас появляется что-то типа

mysql>

что значит, что мы в консоли базы данных. Создаём пользователя для репликации

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; #some_password заменяем своим, которых нравится
FLUSH PRIVILEGES; #коммитим привилегии

Теперь проверяем, идёт ли логирование:

USE exampledb;
FLUSH TABLES WITH READ LOCK; 
SHOW MASTER STATUS;

Должны увидеть что-то такое

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 130      | exampledb    |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

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

mysqldump -u root -p exampledb > /tmp/dump.sql

Копируем файл по SSH на второй сервер

scp -v /tmp/dump.sql slave:/tmp/

Всё, теперь мы переходим на второй сервер…

Настройка второго сервера MySQL для репликации данных (slave)

Заходим

mysql -u root -p 
Enter password: #вводим пароль
CREATE DATABASE exampledb; #создаём базу, которая будет копией той, что есть на сервере master
quit; #выходим

Теперь заливаем тот дамп, который мы копировали недавно по SSH

mysql -u root -p exampledb < /tmp/dump.sql

Разумеется, система запросит пароль и подлив пройдёт. Далее нам нужно в настройках сервера MySQL (slave), где вообще есть master и как к нему обратится. Редактируем настройки:

nano /etc/my.cnf:
server-id=2 #указываем, что это второй сервер
master-host=192.168.0.1 #указываем IP master сервера
master-user=slave_user #тот самый пользователь только для репликации
master-password=secret #и его пароль
master-connect-retry=60 #как часто обращаемся
replicate-do-db=exampledb #какую базу реплицировать

Перегружаем сервер:

/etc/init.d/mysql restart

Теперь мы можем зайти и попробовать подлить данные с основного сайта, заодно проверив, пройдёт ли это и, если да, появятся ли новые данные. Заходим в консоль MySQL

mysql -u root -p 
Enter password: #вводим пароль
LOAD DATA FROM MASTER; #отдаём команду запроса данных с master
quit; #выходим

Проверяя данные через консоль или с помощью phpmyadmin, workbench и т.д., мы должны будем увидеть новые данные по мере поступления их на master сервер. Теперь, например, мы можем делать резервные копии баз данных без нагрузки основной. К тому же slave легко переписать в master, если вдруг с основным сервером что-то случилось.