Настройка репликации в MySQL + создание бекапов у нагруженного сервера MySQL с помощью репликации

Мы имеем два физических сервера, неважно какая ОС установлена, FreeBSD / Linux. На обоих серверах стоит Mysql (я лично тестировал на связках Ubuntu 9.04/9.10 FreeBSD 8.0 и Mysql5.1) На одном сервере стоит рабочий MySQL сервер (IP = 10.0.0.1). На втором свежеустановленный MySQL (IP = 10.0.0.2).

1.Открываем конфигурационный файл mysql на основном сервере

Для Linux: /etc/mysql
Для FreeBSD: /var/db/mysql

Так же просим учесть, что у ОС FreeBSD его по умолчанию нету, взять дефолтный конфигурационный файл можно в папке /usr/local/share/mysql
(my-small.cnf, my-medium.cnf, my-large.cnf или my-huge.cnf - в зависимости от конфигурации сервера и нагрузки на него.
Не забудьте что при копировании дефолтного конфигурационного файла в папку /var/db/mysql ему необходимо задать имя my.cnf).

и редактируем следующие поля:
для Linux:

 server-id               = 1
 log_bin                 = /var/log/mysql/mysql-bin.log
 expire_logs_days        = 10
 max_binlog_size         = 1024M

для FreeBSD:

 server-id=1
 log-bin=mysql-bin
 expire_logs_days= 10
 max_binlog_size=1024M

Параметры означают следующее:
server-id - Уникальный server_id
log-bin - указывает путь для ведения логов и формат имени файлов для Linux, или формат файла для FreeBSD
expire_logs_days - сколько по времени днях хранить логи, учтите что на нагруженном сервере за 10 дней объем может достигать 50-100Gb
max_binlog_size - какой размер файла логов делать

И теперь самый неприятный момент - Нужно перезапустить основной сервер MySQL
Для Linux
/etc/init.d/mysql restart
Для FreeBSD
/usr/local/etc/rc.d/mysql-server restart

2. Заходим в консоль MySQL сервер. (на основном сервере)

Если у пользователя root нету пароля - то просто вводим mysql [Enter]
Если пароль есть - то вводим mysql -u root -p [Enter] и вводим пароль от пользователя root [Enter]

root@mysql:~# mysql
Welcome to the MySQL monitor.  Commands end with; or \g.
Your MySQL connection id is 4057381
Server version: 5.1.37-1ubuntu5.1-log (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

3. Вводим одну команду

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username'@'10.0.0.2' IDENTIFIED BY 'password';
где
username - имя пользователя
password - пароль
10.0.0.2 - ip сервера репликации (поменяйте на свой IP)

4. Открываем конфигурационный файл MySQL на резервном сервере.

и редактируем следующие параметры

server-id = 2
master-host = 10.0.0.1
master-port = 3306
master-user = username
master-password = password

Параметры означают следующее:
server-id - уникальный id сервера, значение должно отличаться от значения первого сервера
master-host = IP адрес на котором работает мастер-сервер
master-port = Порт на котором работает мастер-сервер
master-user = имя пользователя, которое мы задали в пункте 3
master-password = пароль, который мы задали в пункте 3

5. Перезапускаем MySQL на резервном сервере, ОБЯЗАТЕЛЬНО

Для Linux
/etc/init.d/mysql restart
Для FreeBSD
/usr/local/etc/rc.d/mysql-server restart
Заходим в консоль mysql
Если у пользователя root нету пароля - то просто вводим mysql [Enter]
Если пароль есть - то вводим mysql -u root -p [Enter] и вводим пароль от пользователя root [Enter]
и после вводим 1 команду: STOP SLAVE; [Enter]

root@rep:~# mysql
Welcome to the MySQL monitor.  Commands end with; or \g.
Your MySQL connection id is 229333
Server version: 5.1.37-1ubuntu5.1 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> STOP SLAVE;
Query OK, 0 rows affected (0,02 sec)

6. Переходим к созданию бекапа с рабочего сервера

На основном сервере вводим команду:

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

Параметры означают следующее:
--user=root - имя пользователя, от чьего имени будем делать бекап
--password=root_password - пароль пользователя root
--extended-insert - Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).
--all-databases - Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.
--lock-tables - Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.
Внимание, бекап лучше всего делать в самое не загруженное время работы MySQL (например в 6 утра)

7. Установка бекапа на сервер репликации

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

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

После чего заходим в консоль mysql и вводим команду START SLAVE; [Enter]
Проверить работоспособность сервера репликаций можно с помощью команды SHOW SLAVE STATUS; в консоле управления mysql

8. Создание бекапа с помощью сервера репликации

В ручном режиме:
Сначала заходим в консоль MySQL сервера: mysql [Enter] и вводим команду STOP SLAVE;
Выходим из консоли mysql и запускаем mysqldump:

mysqldump --user=root --password=root_password --all-databases --lock-all-tables > /tmp/backup.sql

Параметры означают следующее:
--user=root - имя пользователя, от чьего имени будем делать бекап
--password=root_password - пароль пользователя root
--all-databases - Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.
--lock-all-tables - Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.
После чего не забываем запустить репликацию снова, для этого заходим в консоль MySQL: mysql [Enter] и вводим команду START SLAVE;

В автоматическом режиме:

#!/bin/sh
 
DATE=`date +%Y-%m-%d_%H-%M`
USER=root
PASS=root_password
 
mysqladmin --user=${USER} --password=${PASS} stop-slave
mysqldump --user=${USER} --password=${PASS} --all-databases --lock-all-tables > /backups/mysql/backup-${DATE}.sql
mysqladmin --user=${USER} --password=${PASS} start-slave
tar jcvf /backups/mysql/backup-${DATE}.tar.bz2 /backups/mysql/backup-${DATE}.sql
rm /backups/mysql/backup-${DATE}.sql

Если вы захотите добавить этот скрипт в crontab - то добавте в скрипте полные пути до всех запускаемых файлов
Найти полный путь до запускаемого файла можно с помощью:

root@rep:~# whereis mysqladmin
mysqladmin: /usr/bin/mysqladmin /usr/share/man/man1/mysqladmin.1.gz

У FreeBSD другие пути, например:

www# whereis mysqladmin
mysqladmin: /usr/local/bin/mysqladmin /usr/local/man/man1/mysqladmin.1.gz