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

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

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

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

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

1
2

 skip-networking
 bind-address = 127.0.0.1

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

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

1
2
3

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

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

1

 /etc/init.d/mysql restart

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

1

 netstat -antpu | grep mysql

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

1

 0.0.0.0:3306

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

1

 mysql -u root -p

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

1

 Enter password:

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

1

 mysql>

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

1
2

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

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

1
2
3

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

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

1
2
3
4
5
6

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

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

1

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

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

1

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

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

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

Заходим

1
2
3
4

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

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

1

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

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

1
2
3
4
5
6
7

 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 #какую базу реплицировать

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

1

 /etc/init.d/mysql restart

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

1
2
3
4

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

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