MariaDB master-slave репликация не работает

[MaksimsE]

Всем привет,
Пытаюсь настроить master-slave репликацию, но не работает.

Есть NODE1 с базой 130G. Я сделал raw copy всей директории с помощью rsync и отправил на NODE2. Затем поднял NODE2, настроил его как slave, но вижу ошибку:

Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event_recovery`, CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE)' on query. Default database: 'zabbix'. Query: 'insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (29184882,29184909,null,null,null)'

С чем связано? Как сделать так, чтобы реплика работала?

Собственно, show slave status:

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.16.1
Master_User: zabbix-db-node2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000093
Read_Master_Log_Pos: 89914115
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 88772890
Relay_Master_Log_File: mysql-bin.000091
Slave_IO_Running: Yes
Slave_SQL_Running: No

Конфиг slave:

server_id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2

Конфиг master:

server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1

create table на master:

MariaDB [zabbix]> show create table events \G;
*************************** 1. row ***************************
Table: events
Create Table: CREATE TABLE `events` (
`eventid` bigint(20) unsigned NOT NULL,
`source` int(11) NOT NULL DEFAULT '0',
`object` int(11) NOT NULL DEFAULT '0',
`objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
`clock` int(11) NOT NULL DEFAULT '0',
`value` int(11) NOT NULL DEFAULT '0',
`acknowledged` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `events_1` (`source`,`object`,`objectid`,`clock`),
KEY `events_2` (`source`,`object`,`clock`),
KEY `events_0` (`eventid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [zabbix]> show create table event_recovery \G;
*************************** 1. row ***************************
Table: event_recovery
Create Table: CREATE TABLE `event_recovery` (
`eventid` bigint(20) unsigned NOT NULL,
`r_eventid` bigint(20) unsigned NOT NULL,
`c_eventid` bigint(20) unsigned DEFAULT NULL,
`correlationid` bigint(20) unsigned DEFAULT NULL,
`userid` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`eventid`),
KEY `event_recovery_1` (`r_eventid`),
KEY `event_recovery_2` (`c_eventid`),
CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE,
CONSTRAINT `c_event_recovery_2` FOREIGN KEY (`r_eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE,
CONSTRAINT `c_event_recovery_3` FOREIGN KEY (`c_eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

create table на slave:

MariaDB [zabbix]> show create table events \G;
*************************** 1. row ***************************
Table: events
Create Table: CREATE TABLE `events` (
`eventid` bigint(20) unsigned NOT NULL,
`source` int(11) NOT NULL DEFAULT '0',
`object` int(11) NOT NULL DEFAULT '0',
`objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
`clock` int(11) NOT NULL DEFAULT '0',
`value` int(11) NOT NULL DEFAULT '0',
`acknowledged` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `events_1` (`source`,`object`,`objectid`,`clock`),
KEY `events_2` (`source`,`object`,`clock`),
KEY `events_0` (`eventid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [zabbix]> show create table event_recovery \G;
*************************** 1. row ***************************
Table: event_recovery
Create Table: CREATE TABLE `event_recovery` (
`eventid` bigint(20) unsigned NOT NULL,
`r_eventid` bigint(20) unsigned NOT NULL,
`c_eventid` bigint(20) unsigned DEFAULT NULL,
`correlationid` bigint(20) unsigned DEFAULT NULL,
`userid` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`eventid`),
KEY `event_recovery_1` (`r_eventid`),
KEY `event_recovery_2` (`c_eventid`),
CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE,
CONSTRAINT `c_event_recovery_2` FOREIGN KEY (`r_eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE,
CONSTRAINT `c_event_recovery_3` FOREIGN KEY (`c_eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

[yukra]

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.16.1
Master_User: zabbix-db-node2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000093
Read_Master_Log_Pos: 89914115
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 88772890
Relay_Master_Log_File: mysql-bin.000091
Slave_IO_Running: Yes
Slave_SQL_Running: No

1) У меня (правда на перконе) SHOW SLAVE STATUS выводит больше инфы. Не знаю как там у марии, но вроде и то и то форк класического mysql и не должны были в этом плане сильно отличаться:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.10
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.003634
          Read_Master_Log_Pos: 373838900
               Relay_Log_File: mysql-relay-bin.001360
                Relay_Log_Pos: 373839063
        Relay_Master_Log_File: mysql-bin.003634
             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: 373838900
              Relay_Log_Space: 373839283
              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: 314
                  Master_UUID: 96737380-468e-11e5-999d-e89a8f740262
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>

2) "Slave_SQL_Running: No" - запустить пробовали? Команда типа mysql -e 'start slave sql_thread' (или просто "start slave sql_thread" в консоли mysql)
3) Версии? Не знаю как сейчас, да еще и в форках, но олдовые dba мне рассказывали что дабы обойти всякие "непонятки" крайне желательно на слейве иметь версию "не ниже, чем на мастере", ибо формат репликации изредка может "подправляться" и этим вы гарантируете что слейв поймет и "старый" и "новый" формат репликации, хотя не думаю что это сильно актуально в наши дни, когда формату уже не один год.
4) "show create table events" - это вы что хотели показать? Я честно говоря не понял.
4.5) "ERROR: No query specified" - это артефакты копипасты или откуда берется?
5) "raw copy всей директории с помощью rsync" - это зачем? Опять же в моей перконе "mysqldump --single-transaction" отлично справляется с задачей "сделать новый слейв без остановки мастера".
6) Тип репликации (binlog_format) лучше указывать явно, чем не указывать.
7) А че там в логах?
8) Да и причем тут вообще заббикс?

[MaksimsE]

1. Я написал только часть чтобы не упереться в количество символов.
2. Запускаю, ничего не меняется.
3. На Slave 10.1.31, на Master - 10.1.30. То есть Slave свежее Master-а
4. Хотел показать, что show create для таблиц как на master, так и на slave одинаковые.
4.5 Это отсюда, если быть точнее:

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.10.16.1
Master_User: zabbix-db-node2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000129
Read_Master_Log_Pos: 8425286
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 5394307
Relay_Master_Log_File: mysql-bin.000127
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: 1452
Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event_recovery`, CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE)' on query. Default database: 'zabbix'. Query: 'insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (29186090,29186093,null,null,null)'
Skip_Counter: 0
Exec_Master_Log_Pos: 5394019
Relay_Log_Space: 218176956
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: 1452
Last_SQL_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event_recovery`, CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE)' on query. Default database: 'zabbix'. Query: 'insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (29186090,29186093,null,null,null)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)

ERROR: No query specified



5. Mysqldump в моем случае делался дольше и Rsync упоминается как один из вариантов на dev.mysql
6.


Мне вот это не нравится когда slave пытается выполнить insert

insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (29186090,29186093,null,null,null)

MariaDB [(none)]> select * from zabbix.events where eventid = 29186090;
Empty set (0.00 sec)

MariaDB [(none)]> select * from zabbix.events where eventid = 29186093;
Empty set (0.00 sec)

7. в mysql-error.log последняя запись недельной давности.

[yukra]

1) Как по мне - так одно из самых интересных вещей вырезали. То, что таблицы одинаковые - так вы их rsync'ом копировали, а тут у нас должна быть ошибка описана.
2) Нужно или уровень логов поднять, или смотрите не туда или что-то около того. Любая команда должна быть или выполнена, или должна вернуться ошибка. Не выполнить и не вернуть ошибку - не верю что описанное вами поведение это именно то, что хотели получить разработчики. Баг, глюк, и т.п. - может быть, но на штатное поведение это не похоже.
3) Ок
4) Тык. Вы же их копировали rsyncом. С чего им разными то быть? Тут я видимо недопонимаю что вы хотели показать этим.
4.5 + 6) Воо, "Last_SQL_Error" - уже предметный разговор.
У меня на первый взгляд такое ощущение, что вы репликацию не с того места начала.

По идеи перед копированием "мастера" вы должны сделать что-то типа (на мастере):

SET GLOBAL read_only = ON;
show master status;

И на вторую команду увидеть ответ типа такого:

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.003638 | 566931139 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

После этого не снимая с мастера readonly (и будучи на 100% уверенным что никто не может выполнять инсерты\апдейты от пользователей с привелегиями SUPER, ибо "When the read_only system variable is enabled, the server permits no client updates except from users who have the SUPER privilege." https://dev.mysql.com/doc/refman/5.6...svar_read_only ) вы выключили mysql, скопировали БД на второй сервер, включили обе БД, не делая никаких запросов в slave (за исключением SELECT если хотите) вы выполнили команды

CHANGE MASTER TO MASTER_HOST = "192.168.X.Y", MASTER_USER = "replication_user", MASTER_PASSWORD = "replication_password", MASTER_LOG_FILE = "mysql-bin.003638", MASTER_LOG_POS = 566931139;
start slave;

Причем параметры MASTER_LOG_FILE и MASTER_LOG_POS именно те, которые были в выводе "show master status" на мастере?
Мне кажется где-то примерно в этом у вас что-то пошло не так, и репликация банально не с того места пытается стартовать.

5) Как я уже сказал я вместо остановки мастера предпочитаю делать "mysqldump --single-transaction --master-data ..." и автоматически получаю и позицию мастера и согласованность дампа и отсутствие даунтайма на мастере. Плюс уверенность в том, что данные скопированы самым правильным образом и не нужно париться на тему оптимизации таблиц.
То, что это занимает больше времени суммарно - ну наверное да, но совершенно точно это занимает меньше моего личного времени (mysqldump можно запустить и забыть до завтра, завтра с утра начать импортировать дамп, а под вечер запустить репликацию, условно же конечно говоря, все от производительности железа зависит). У вас же получилось быстрей, но не работает.

[MaksimsE]

В общем, проблема решена.

Последовательность была такая:

1. залочил master-а в read only
2. опустил mariadb сервис на slave
2. догнал слейвом мастера, с помощью rsync
3. включил mariadb на slave, но без slave, натравил на мастера
4. start slave, догнал мастера
5. разлочил таблицы на мастере (обратное пункту номер 1)

С этого момента master-slave работает штатно.
Плюс ко всему настроил master-master, чтобы при отвале базы заббикс продолжал писать во вторую. Для этого использую keepalived с virtual ip. На деле работает отлично.

[yukra]

Главное их замониторить, а то mysql любит разваливать репликацию на ровном месте типа "с соседа прилете апдейт в таблицу типа MEMORY, а у нас такой записи нет, потому что мы только что ребутались что бы ядро обновить". Только не спрашивайте зачем MEMORY вообще использовать и реплицировать.