MySQL: репликация баз данных

mysqllogo Репликация – есть ни что иное как “синхронизация” двух и/или более баз данных на различных серверах MySQL.

Сам процесс репликации происходит с помощью бинарных логов, ведущихся на MASTER-сервере.

Для их просмотра – используйтеся утилита mysqlbinlog.

Конфигурация сервера MASTER:

# uname -v
FreeBSD 9.0-RELEASE-p3

# pkg_info | grep mysql-server
mysql-server-5.5.30 Multithreaded SQL database (server)

В файл конфигурации сервера MySQL /etc/my.cnf в блок [mysqld] на сервере MASTER добавляем:

#Replication
server-id = 1
log-bin = mysql-bin
replicate-do-db = testdb1

Обратите внимание, что некоторые из этих строк уже могут в нём присутствовать. В таком случае – закоментируйте или удалите их дубликаты.

Добавим пользователя replication, и назначим ему привелегию REPLICATION SLAVE для базы testdb1:

mysql> GRANT replication slave ON ‘testdb1’.* TO ‘replication’@’%’ identified by ‘password’;

Если доступ нужен ко всем базам – указываем:

mysql> GRANT replication slave ON *.* TO ‘replication’@’%’ identified by ‘password’;

Перезапускаем сервер MySQL:

# service mysql-server restart
Stopping mysql.
Waiting for PIDS: 28563.
Starting mysql.

Проверим – запустился ли он:

# service mysql-server status
mysql is running as pid 14744.

В случае ошибки:

# service mysql-server status
mysql is not running.

Проверяем, что бы указали в /etc/my.cnf или смотрим файл лога. О настройке логгирования MySQL рассказано в статье MySQL: включаем логи.

Проверяем состояние бинарного лога:

mysql> SHOW master STATUSG
*************************** 1. row ***************************
File: mysql-bin.000093
Position: 76302
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Конфигурация SLAVE-сервера:

Операционная система

# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 12.10
Release:        12.10
Codename:       quantal

Сервер MySQL:

# aptitude show mysql-server
Пакет: mysql-server
Состояние: установлен
Автоматически установлен: нет
Версия: 5.5.29-0ubuntu0.12.10.1

В файл конфигурации /etc/mysql/my.cnf в блок [mysqld] добавляем:

#Replication slave
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
replicate-do-db = testdb1

Перезапускаем сервер:

# service mysql restart
mysql stop/waiting
mysql start/running, process 9524

Останавливаем запись в базы данных на MASTER-сервере:

mysql> SET GLOBAL read_only = ON;

Создаём дамп базы данных:

# mysqldump -u root -p testdb1 > testdb_bak.sql
Enter password:

Снимаем блокировку таблиц:

mysql> SET GLOBAL read_only = OFF;

Создаём базу на SLAVE-сервере:

mysql> create database testdb2;
Query OK, 1 row affected (0.00 sec)

Создаём пользователя userdb1 и назаначаем ему доступ к базе testdb2:

grant all on testdb2.* to ‘userdb1’@’localhost’ identified by ‘password’;
Query OK, 0 rows affected (0.00 sec)

Загружаем дамп в новую базу:

# mysql -u root -p userdb2 < testdb_bak.sql

Устанавливаем параметры репликации на SLAVE-сервере:

mysql> CHANGE MASTER TO MASTER_HOST = “77.120.***.40”, MASTER_USER = “replication”, MASTER_PASSWORD = “password “, MASTER_LOG_FILE = “mysql-bin.000093 “, MASTER_LOG_POS = 1309;
Query OK, 0 rows affected (0.59 sec)

Значения для MASTER_LOG_FILE и MASTER_LOG_POS – берём из команды:

mysql> SHOW SLAVE STATUSG

выполненной на MASTER-сервере.

Запускаем процесс:

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

Если требуется внести какие-либо изменения в настройки репликации на SLAVE-сервере – сначала надо остановить процесс командой:

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

Посмотреть процесс можно командой:

mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 77.120.106.40
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000093
Read_Master_Log_Pos: 4619
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 3563
Relay_Master_Log_File: mysql-bin.000093
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb1
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: 4619
Relay_Log_Space: 3719
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)