Простая репликация БД MySQL

Инструкция по настройки простой репликации с мастер сервера на слэйв сервер.

Данная инстракция описывает работу в связке FreeBSD 8.2 + MySQL 5.1

Настройка мастера
Нам необходимо внести изменения в /usr/local/etc/my.cnf (если его нет, копируем с /usr/local/share/mysql/my-medium.cnf)
Обязательно укажем уникальный ID сервера, путь для бинарных логов и имя БД для репликации в секции [mysqld]:

<

server-id = 1
 log-bin = /var/lib/mysql/mysql-bin
 replicate-do-db = testdb

Убедитесь, что у вас достаточно места на диске для бинарных логов.

Добавим пользователя replication, под правами которого будет производится репликация. Будет достаточно привилегии «replication slave «. В сети часто встречается инструкция такого вида:

mysql@master> GRANT replication slave ON "testdb".* TO "replication"@"192.168.1.1" IDENTIFIED BY "password";

Когда я выполнял у себя эту реализацию — закончилось неудачей. Права на репликацию — административная опция и если у вас не получилось — пробуйте этот вариант:

mysql@master> GRANT replication slave ON *.* TO "replication"@"192.168.1.1" IDENTIFIED BY "password";

Перезагрузим MySQL, чтобы изменения в конфиге вступили в силу:

<

root@master# service mysql-server restart

Если все прошло успешно, команда «show master status» должна показать примерно следующее:

<

mysql@master> SHOW MASTER STATUS\G
 File: mysql-bin.000003
 Position: 98
 Binlog_Do_DB:
 Binlog_Ignore_DB:

Значение position должно увеличиваться по мере того, как вносятся изменения в БД на мастере.

Настройка реплики
Укажем ID сервера, имя БД для репликации и путь к relay-бинлогам в секции [mysqld] конфига, затем перезагрузим MySQL:

<

server-id = 2
 relay-log = /var/lib/mysql/mysql-relay-bin
 relay-log-index = /var/lib/mysql/mysql-relay-bin.index
 replicate-do-db = testdb

<

root@replica# service mysql-server restart

Переносим данные
Здесь нам придется заблокировать БД для записи. Для этого можно либо остановить работу приложений, либо воспользоваться установкой флажка read_only на мастере (внимание: на пользователей с привилегией SUPER этот флаг не действует). Если у нас есть таблицы MyISAM, сделаем также «flush tables»:

<

mysql@master> FLUSH TABLES WITH READ LOCK;
 mysql@master> SET GLOBAL read_only = ON;

Посмотрим состояние мастера командой «show master status» и запомним значения File и Position (после успешной блокировки мастера они не должны изменятся):

 File: mysql-bin.000003
 Position: 98

Делаем дамп БД, и после завершения операции снимаем блокировку мастера:

<

mysql@master> SET GLOBAL read_only = OFF;
 mysql@master> UNLOCK TABLES;

Переносим дамп на реплику и восстанавливаем из него данные.
Наконец, запускаем репликацию командами «change master to» и «start slave» и посмотрим, все ли прошло хорошо:

<

mysql@replica> CHANGE MASTER TO MASTER_HOST = «192.168.1.101», MASTER_USER = «replication», MASTER_PASSWORD = «password», MASTER_LOG_FILE = «mysql-bin.000003», MASTER_LOG_POS = 98;

<

mysql@replica> start slave;

Значения MASTER_LOG_FILE и MASTER_LOG_POS мы берем с мастера.

Посмотрим, как идет репликация командой «show slave status»:

<

mysql@replica> SHOW SLAVE STATUS\G
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.1.101
 Master_User: replication
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000003
 Read_Master_Log_Pos: 98
 Relay_Log_File: mysql-relay-bin.001152
 Relay_Log_Pos: 235
 Relay_Master_Log_File: mysql-bin.000003
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: testdb,testdb
 Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 98
 Relay_Log_Space: 235
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: 5

Slave_IO_Running: Yes, Slave_SQL_Running: Yes — говорит о том, что репликация идет успешно.

Update #1

При появлении ошибки:

<

ERROR 1201 (HY000) at line 1: Could not initialize master info
 structure; more error messages can be found in the MySQL error log

На slave’е необходимо выполнить следующее:

<

stop slave;
 reset slave;
 change master to…. ;
 start slave;

Update #2

Если бинарные логи переполнили раздел, необходимо воспользоваться следующими командами:

<

show binary logs;
 purge binary logs before ‘2012-07-11 00:00:01’

Источник http://semenushkin.ru/2011/11/11/%D0%BF%D1%80%D0%BE%D1%81%D1%82%D0%B0%D1%8F-%D1%80%D0%B5%D0%BF%D0%BB%D0%B8%D0%BA%D0%B0%D1%86%D0%B8%D1%8F-%D0%B1%D0%B4-mysql/