Репликация баз данных MySQL 5

Есть необходимость хранить online-копии баз данных на другом сервере.  Решено использовать master-slave репликацию MySQL.

Предполагается, что первый и второй MySQL серверы уже установлены и работают.

Для начала на первом сервере нам необходимо включить бинарный лог и назначить идентификатор этому серверу. 

Прописываем в my.cnf

log-bin=/var/db/mysql/srv-01-bin.log

server-id=1

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

[root@db-srv-01 ~]# mysql -u root -h db-srv-01.local -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_db-srv-02'@'db-srv-02.local' IDENTIFIED BY 'Pa$$w0rd';

mysql> FLUSH PRIVILEGES;

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

mysql> FLUSH TABLES WITH READ LOCK;

Также нам будут необходимы эти данные: имя файла бинарного лога master-сервера и позиция данных в бинарном логе.

mysql> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| srv-01-bin.000002 |    29599 |              |                  |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

Делаем дамп всех БД сервера.

[root@db-srv-01 ~]# mysqldump -u root -h db-srv-01.local -p -A > /tmp/dump_mysql_all.sql

Переходим к настройке второго сервера. В это время все БД первого сервера остаются заблокированными.

Разворачиваем БД из бэкапа на второй сервер.

[root@db-srv-02 ~]# mysql -u root -h db-srv-02.local -p < /tmp/dump_mysql_all.sql

Далее нам необходимо добавить в my.cnf следующие строки:

master-host = db-srv-01.local

master-user = slave_db-srv-02

master-password = Pa$$w0rd

master-port = 3306

server-id = 2

log-bin = /var/db/mysql/srv-02-bin.log

log-bin-index = /var/db/mysql/srv-02-bin-index.log

log-error = /var/db/mysql/srv-02-error.log

relay-log = /var/db/mysql/srv-02-relay.log

relay-log-info-file = /var/db/mysql/srv-02-relay-log.info

relay-log-index = /var/db/mysql/srv-02-relay-log.index

Необходимо указать стартовую позицию, с которой начинать репликацию.

[root@db-srv-02 ~]# mysql -u root -h db-srv-02 -p

mysql> CHANGE MASTER TO MASTER_LOG_FILE='srv-01-bin.000002';

mysql> CHANGE MASTER TO MASTER_LOG_POS=29599;

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

               Slave_IO_State:

                  Master_Host: db-srv-01.local

                  Master_User: slave_db-srv-02

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: srv-01-bin.000002

          Read_Master_Log_Pos: 29599

               Relay_Log_File: srv-02-relay.000002

                Relay_Log_Pos: 28264

        Relay_Master_Log_File: srv-01-bin.000002

             Slave_IO_Running: No

            Slave_SQL_Running: No

              Replicate_Do_DB:

          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: 29599

              Relay_Log_Space: 28416

              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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)

Запускаем репликацию.

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: db-srv-01.local

                  Master_User: slave_db-srv-02

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: srv-01-bin.000002

          Read_Master_Log_Pos: 30360

               Relay_Log_File: srv-02-relay.000003

                Relay_Log_Pos: 252

        Relay_Master_Log_File: srv-01-bin.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          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: 30360

              Relay_Log_Space: 28562

              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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)

Теперь необходимо разблокировать БД.

[root@db-srv-01 ~]# mysql -u root -h db-srv-01 -p

mysql> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

Все.

Новые записи в таблицах автоматически появятся в на втором сервере.

При подготовке статьи я использовал следующие материалы:

1. "Статья Live Backups of MySQL Using Replication" http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html

2.  "How to Set Up Replication" из документации по MySQL http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html