Репликация в MySQL

Срочно понадобилось иметь копию двух таблиц (test_db.wiz_main и test_db.wiz_data) на втором mysql-сервере. Причем, чтобы все новые изменения с master-таблиц, сразу же появлялись на втором сервере. Как вариант решения была выбрана репликация.

Для того, чтобы организовать репликацию между двумя MySQL-серверами необходимо:

• На master-сервере
  

1) Включить binarylog, если он еще не включён и назначить master-серверу идентификатор (server-id). Для этого в my.cnf добавить и перегрузить:

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

2) Создать пользователя с правами:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;

3) Заблокировать требуемые таблицы на время dump'а. Во время блокировки также необходимо посмотреть текущее состояние в binary логе

mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+-------------------+----------+--------------+------------------+
File               Position  Binlog_Do_DB  Binlog_Ignore_DB
+-------------------+----------+--------------+------------------+
srv011-bin.000813   1156293                            
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

4) Сделать dump требуемых таблиц и снять блокировку

mysqldump -u root -p test_db > /root/test_db.db
mysql> UNLOCK TABLES;

• На slave-сервере
  

1) Указать, где находится master-сервер и какие конкретно базы (таблицы) следует реплицировать. Для этого в my.cnf добавить:

max-user-connections=50
master-host=212.122.238.32
master-user=slave_user
master-password=v9X8Ds4
server-id= 2
replicate-do-db=test_db
replicate-do-table=test_db.wiz_data
replicate-do-table=test_db.wiz_main

2) Указать "отправную точку", с которой следует начать репликацию данных.

mysql> CHANGE MASTER TO MASTER_LOG_FILE='srv011-bin.000813';
Query OK, 0 rows affected (0.05 sec)

mysql> CHANGE MASTER TO MASTER_LOG_POS=1156293;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Slave_IO_State:
         Master_Host: 212.122.238.32
         Master_User: slave_user
         Master_Port: 3306
       Connect_Retry: 60
     Master_Log_File: srv011-bin.000813
 Read_Master_Log_Pos: 1156293
      Relay_Log_File: relay.000001
       Relay_Log_Pos: 4
Relay_Master_Log_File: srv011-bin.000813
    Slave_IO_Running: No
   Slave_SQL_Running: No
     Replicate_Do_DB: test_db
 Replicate_Ignore_DB:
  Replicate_Do_Table: test_db.wiz_main,test_db.wiz_data
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
          Last_Errno: 0
          Last_Error:
        Skip_Counter: 0
 Exec_Master_Log_Pos: 1156293
     Relay_Log_Space: 4
     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: NULL
1 row in set (0.00 sec)

3) Инициировать репликацию

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Slave_IO_State: Waiting for master to send event
         Master_Host: 212.122.238.32
         Master_User: slave_user
         Master_Port: 3306
       Connect_Retry: 60
     Master_Log_File: srv011-bin.000813
 Read_Master_Log_Pos: 1174592
      Relay_Log_File: relay.000001
       Relay_Log_Pos: 18347
Relay_Master_Log_File: srv011-bin.000813
    Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
     Replicate_Do_DB: test_db
 Replicate_Ignore_DB:
  Replicate_Do_Table: test_db.wiz_main,test_db.wiz_data
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
          Last_Errno: 0
          Last_Error:
        Skip_Counter: 0
 Exec_Master_Log_Pos: 1174592
     Relay_Log_Space: 18347
     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: 0
1 row in set (0.00 sec)

Ссылки:

1. Live Backups of MySQL Using Replication
2. How To Set Up Database Replication In MySQL
3. Репликация Master-Slave в MySQL
4. Репликация mySQL — параметры и команды