MySQL Master-Slave репликация

Master-Slave репликация в MySQL часто используется для обеспечения отказоустойчивости или распределения нагрузки на сервер баз данных. Например, можно запросы на изменение данных направлять на Master-сервера, а запросы на чтение выполнять на Slave-сервере. Пример о применении репликации.

Есть 2 сервера:
1. 192.168.1.10 - Debian squeeze Mysql 5.5 - Master
2. 192.168.1.15 - Centos 6 Mysql 5.5 - Slave

Необходимо настроить репликацию с Master сервера на Slave сервер базы данных repl.

0. Подготовительные работы

Создадим на мастере базу данных, которую будем реплицировать:
mysql> CREATE DATABASE repl;

Создадим таблицу и немного данных добавим:
mysql> use repl;
mysql> CREATE table users (id bigint auto_increment primary key, email varchar(255) not null);
mysql> DESCRIBE users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| email | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into users (email) values ('one@example.com'), ('two@example.com'), ('three@example.com');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM users;
+----+-------------------+
| id | email             |
+----+-------------------+
|  1 | one@example.com   |
|  2 | two@example.com   |
|  3 | three@example.com |
+----+-------------------+
3 rows in set (0.00 sec)

1. Настрйока Master-сервера

Отредактируем конфигурационный файл сервера баз данных MySQL:
master# vim /etc/mysql/my.cnf
server-id = 1  - id-сервера
log_bin = /var/log/mysql/mysql-bin.log - расположение бин лога MySQL
binlog_do_db            = repl - какую базу данных необходимо реплицировать
bind-address = 0.0.0.0 - По умолчанию MySQL висит на localhost. Если указать 0.0.0.0, то MySQL будет слушать 3306 порт на всех сетевых интерфейсах

Перезапустим MySQL:
master# /etc/init.d/mysql restart

2. Создадим пользователя для репликации

master# mysql -uroot -p

Создадим пользователя slave_user с паролем password:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

Создадим дамп базы данных, которую будем реплицировать:
mysql> use repl;
mysql> FLUSH TABLES WITH READ LOCK;

Проверим статус Master'а:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      332 | repl         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Тут нам надо запомнить поля: File и Position, так как позднее они нам нужны будут для настройки Slave'а.

3. Создадим дамп базы repl

master# mysqldump -uroot -p repl > repl.sql
master# mysql -uroot -p

Разблокируем таблицу, после создания дампа:
mysql> use repl;
mysql> UNLOCK TABLES;

4. Создание базы  на слейве

Скопируем дамп базы на Slave:
master# scp  repl.sql root@192.168.1.15:/root/repl.sql
root@192.168.1.15's password: 
bash: scp: команда не найдена 
lost connection
Если получим такую ошибку, то на Slave'е установим нужный пакет:
# yum -y install openssh-clients

slave# mysq -uroot -p
mysql> CREATE DATABASE repl;

Загрузим дамп в базу:
slave# mysql -uroot -p repl < /root/repl.sql 

5. Настройка слейва

slave# vim /etc/my.cnf
server-id = 2 - id Slave'а
relay-log = /var/log/mysql/mysql-relay-bin.log - путь к relay логу
log_bin = /var/log/mysql/mysql-bin.log - путь к бинарному логу на Master
binlog_do_db = repl - база данных для репликации

Создадим папку для наших логов:
slave# mkdir /var/log/mysql
slave# chown mysql:mysql /var/log/mysql

Перезапустим MySQL:
slave# /etc/init.d/mysql restart

6. Запуск Slave'а

Осталось запустить репликацию, для этого укажем параметры подключения к Master'у:
slave# mysql -uroot -p

Тут нам и пригодятся File и Position из SHOW MASTER STATUS:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.10', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS = 332;

Запускаем Slave:
mysql> START SLAVE;

Статус репликации

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.10
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-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: 107
              Relay_Log_Space: 555
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR: 
No query specified

Проверим работу

Давайте на Slave'е посмотрим, какие данные есть в нашей таблице:
mysql> SELECT * FROM users;
+----+-------------------+
| id | email             |
+----+-------------------+
|  1 | one@example.com   |
|  2 | two@example.com   |
|  3 | three@example.com |
+----+-------------------+
3 rows in set (0.00 sec)

На Master'е добавим еще значение в таблицу:
mysql> insert into users (email) values ('five@example.com');

И сразу убедимся, что данные записались:
mysql> SELECT * FROM users;
+----+-------------------+
| id | email             |
+----+-------------------+
|  1 | one@example.com   |
|  2 | two@example.com   |
|  3 | three@example.com |
|  4 | five@example.com  |
+----+-------------------+
4 rows in set (0.00 sec)


А теперь посмотрим, какие данные у нас на Slave'е.
mysql> SELECT * FROM users;
+----+-------------------+
| id | email             |
+----+-------------------+
|  1 | one@example.com   |
|  2 | two@example.com   |
|  3 | three@example.com |
|  4 | five@example.com  |
+----+-------------------+
4 rows in set (0.00 sec)

Как видим, данные успешно среплицировались.

Использованные ссылки

http://ruhighload.com/post/%D0%9A%D0%B0%D0%BA+%D0%BD%D0%B0%D1%81%D1%82%D1%80%D0%BE%D0%B8%D1%82%D1%8C+MySQL+Master-Slave+%D1%80%D0%B5%D0%BF%D0%BB%D0%B8%D0%BA%D0%B0%D1%86%D0%B8%D1%8E
http://www.mysql.ru/docs/man/Replication_FAQ.html