LinuxShare

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

---

Автор: Виктор Вислобоков
Лицензия: CC-BY-NC-ND
Написано по мотивам статьи: Live Backups of MySQL Using Replication

---

Предисловие

У многих читателей вызовет недоумение такой непонятный способ резервного копирования. Они скажут, что для создания резервной копии "на лету" можно воспользоваться утилитой mysqldump. Безусловно, в чём-то они правы, использование данной утилиты вполне разумный и правильный способ, но к сожалению он подходит далеко не всегда.

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

Другим недостатком использования mysqldump опять же при достаточно большой базе, может оказаться логическая нецелостность таблиц в БД. Как известно mysqldump может не блокировать таблицы при осуществлении дампа, но тогда во время выполнения дампа, которое занимает продолжительное время, многие таблицы могут быть изменены работающими пользователями, что приведёт к логической нецелостности данных, либо mysqldump блокирует таблицы, но в этом случае пользователи не смогут работать с БД, что во многих случаях недопустимо!

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

Настройка репликации

Пусть у нас есть два сервера: основной сервер с IP-адресом 10.1.1.1 и резервный с IP-адресом 10.1.1.2. Убедитесь, что межсетевой экран не мешает доступу одного сервера к другому и что настройки обоих серверов позволяют сетевые подключения к mysql по TCP/IP. На основном сервере вы должны создать специального пользователя для целей репликации и дать ему необходимые права:

GRANT REPLICATION SLAVE, REPLICATION CLIENT
    ON *.*
    TO 'replicant'@'10.1.1.2'
    IDENTIFIED BY 'password';

где соответственно replicant вы можете заменить на любое другое имя пользователя, а password на придуманый вами пароль

Далее в ваш /etc/my.cnf, в секцию [mysqld] вы должны добавить строчки:

server-id = 1
log-bin = /var/log/mysql/bin.log

Перед перезапуском MySQL-сервера с этими строчками в /etc/my.cnf озаботьтесь, чтобы каталог /var/log/mysql существовал, чтобы владелец и группа у него были "mysql" и чтобы у пользователя "mysql" были полные права на этот каталог.

На дополнительном сервере настройка несколько сложней. В /etc/my.cnf в секцию [mysqld] вы должны добавить строки:

server-id = 2

master-host = 10.1.1.1
master-port = 3306
master-user = replicant
master-password = password

log-bin = /var/log/mysql/bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index

Как и на основном сервере, не забудьте про каталог /var/log/mysql! Также не забудьте подставить вместо replicant то самое имя пользователя и вместо password тот самый пароль, как было сказано выше.

Перезапустите основной и дополнительный серверы

Теперь на основном сервере вы делаете дамп БД с помощью mysqldump:

mysqldump --user=root --password=root_password \
      --extended-insert --all-databases \
      --master-data  > /tmp/backup.sql  

Не забудьте, что вместо root_password вы должны написать реальный пароль пользвателя root для MySQL. Специальный ключ --master-data добавит в файл дампа строки вида:

--
-- Position to start replication from
--

CHANGE MASTER TO MASTER_LOG_FILE='bin.000846' ;
CHANGE MASTER TO MASTER_LOG_POS=427 ;

которые впоследствие укажут резервному серверу какие данные с момента выполнения резервной копии на нём отсутствуют.

Далее, скопируйте файл /tmp/backup.sql на резервный севрер и загрузите его командой:

mysql --user=root --password=root_password < /tmp/backup.sql

А теперь, выполните на резервном сервере команду:

START SLAVE;

Если всё было настроено корректно, то резервный сервер подключится к основному и начнёт реплицировать с него данные, которые были изменены с момента загруженной резервной копии. Репликация настроена!

Резервное копирование

Теперь, всё что нужно для выполнения резервного копирования, это на резервном сервере дать команду:

STOP SLAVE;

которая остановит репликацию с основным сервером. Затем выполнить на резервном сервере резервное копирование БД и снова запустить репликацию, командой:

START SLAVE;

Вы можете автоматизировать весь процесс, с помощью вот такого скрипта:

#!/bin/sh

date = `date +%Y%m%d`

mysqladmin --user=root --password=root_password stop-slave
mysqldump --user=root --password=my_pwd --lock-all-tables \
          --all-databases > /backups/mysql/backup-${date}.sql
mysqladmin --user=root --password=root_password start-slave

Заключение

Вот собственно и всё. Остаётся лишь поблагодарить Russell Dyer за оригинальную и очень полезную статью!