Содержание
- MySQL Replication skip error
- MySQL Replication skip error
- Top Reviewed Books regarding MySQL at amazon.com
- admin
- 7 thoughts on “ MySQL Replication skip error ”
- Основы репликации в MySQL
- Небольшое введение
- Настройка репликации
- Добавляем реплики
- Объединяем реплики
- Рокировка мастера и реплики
- Заключение
MySQL Replication skip error
MySQL Replication skip error
There are two ways you can configure MySQL replication : normal and GTID based replication. From MySQL 5.6 GTID based replication is started. However, you can again explore how to setup MySQL Replication on the the following links
Sometimes, replication on Slave server is stopped for many a reasons and you need to apply “MySQL Replication skip error” technique to start quickly Slave SQL process. In the following output you will see Replication process is stopped due to Slave_SQL_Running is stopped:
You see “Slave_IO_Running: Yes” but “Slave_SQL_Running: No” that means your Slave IO process is running and retrieving data from Master but couldn’t execute due to Slave_SQL_Running process is stopped.
Apply the following commands:
Step-1: Stop the slave server
Step-2 : Set counter so that slave skip the error sql
Step-3: Start slave
For GTID based replication see the following procedure
You see “Slave_IO_Running: Yes” but “Slave_SQL_Running: No” that means your Slave IO process is running and retrieving data from Master but couldn’t execute due to Slave_SQL_Running process is stopped. You will find the reasons at line
You now need to know which exact GTID needs to skip to start the replication again. You see at the output
It means Slave retrieved the last GTID 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:241806257.
The next line of the output
It means Slave could execute GTIDs upto 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830504. So GTIDs 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505 to 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:241806257 are retrieved but pending to be executed.
That means it stopped during execution of GTID 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505(one greater than last executed). So we need to skip this GTID to be executed and this is called “MySQL Replication skip error” tricks. Follow the step by step procedure to apply “MySQL Replication skip error” technique
- Stop The slave
- Set global GTID_NEXT variable to intended GTID to be skipped
- Execute an empty transaction
- Set global GTID_NEXT variable to AUTOMATIC again
- Start Slave again
- Check Slave status again
After issuing “start slave” command , you may again face the same problem. You may need to repeat the procedure until replication process is started without error. Want to do it automatically, read the article “fixing mysql replication error automatically” .
Top Reviewed Books regarding MySQL at amazon.com
admin
7 thoughts on “ MySQL Replication skip error ”
I tried to run the command manually and this automated command, however I noticed that the fields: Retrieved_Gtid_Set: and Executed_Gtid_Set: are in the bank, why can not correct the error and run the script.
Follow my error screen:
SET GTID_NEXT = ‘46620f04-9913-11e6-9b69-000c29f31b4f: 161024’;
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: x.x.x.x
Master_User: replicacao
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 1697992
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 149077
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table cyber.radacct; Can’t find record in ‘radacct’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000009, end_log_pos 276553
Skip_Counter: 0
Exec_Master_Log_Pos: 275864
Relay_Log_Space: 1571785
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: 1032
Last_SQL_Error: Could not execute Update_rows event on table cyber.radacct; Can’t find record in ‘radacct’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000009, end_log_pos 276553
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 46620f04-9913-11e6-9b69-000c29f31b4f
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 161024 16:02:45
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Can you help me?
Does anybody have the solution? I have exactly the same problem
Источник
Основы репликации в MySQL
Небольшое введение
Репликация (от лат. replico -повторяю) — это тиражирование изменений данных с главного сервера БД на одном или нескольких зависимых серверах. Главный сервер будем называть мастером, а зависимые — репликами.
Изменения данных, происходящие на мастере, повторяются на репликах (но не наоборот). Поэтому запросы на изменение данных (INSERT, UPDATE, DELETE и т. д.) выполняются только на мастере, а запросы на чтение данных (проще говоря, SELECT) могут выполняться как на репликах, так и на мастере. Процесс репликации на одной из реплик не влияет на работу других реплик, и практически не влияет на работу мастера.
Репликация производится при помощи бинарных логов, ведущихся на мастере. В них сохраняются все запросы, приводящие (или потенциально приводящие) к изменениям в БД (запросы сохраняются не в явном виде, поэтому если захочется их посмотреть, придется воспользоваться утилитой mysqlbinlog). Бинлоги передаются на реплики (бинлог, скачанный с мастера, называется «relay binlog «) и сохраненные запросы выполняются, начиная с определенной позиции. Важно понимать, что при репликации передаются не сами измененные данные, а только запросы, вызывающие изменения.
При репликации содержимое БД дублируется на нескольких серверах. Зачем необходимо прибегать к дублированию? Есть несколько причин:
- производительность и масштабируемость. Один сервер может не справляться с нагрузкой, вызываемой одновременными операциями чтения и записи в БД. Выгода от создания реплик будет тем больше, чем больше операций чтения приходится на одну операцию записи в вашей системе.
- отказоустойчивость. В случае отказа реплики, все запросы чтения можно безопасно перевести на мастера. Если откажет мастер, запросы записи можно перевести на реплику (после того, как мастер будет восстановлен, он может принять на себя роль реплики).
- резервирование данных. Реплику можно «тормознуть » на время, чтобы выполнить mysqldump, а мастер — нет.
- отложенные вычисления. Тяжелые и медленные SQL-запросы можно выполнять на отдельной реплике, не боясь помешать нормальной работе всей системы.
Кроме того, есть некоторые другие интересные возможности. Поскольку на реплики передаются не сами данные, а запросы, вызывающие их изменения, мы можем использовать различную структуру таблиц на мастере и репликах. В частности, может отличаться тип таблицы (engine) или набор индексов. Например, для осуществления полнотекстового поиска мы можем на реплике использовать тип таблицы MyISAM, несмотря на то, что мастер будет использовать InnoDB.
Настройка репликации
Настройки мастера
Обязательно укажем уникальный ID сервера, путь для бинарных логов и имя БД для репликации в секции [mysqld]:
= 1
log-bin = /var/lib/mysql/mysql-bin
replicate-do-db = testdb
Убедитесь, что у вас достаточно места на диске для бинарных логов.
Добавим пользователя replication, под правами которого будет производится репликация. Будет достаточно привилегии «replication slave «:
mysql@master> GRANT replication slave ON «testdb».* TO «replication»@»192.168.1.102» IDENTIFIED BY «password»;
Перезагрузим MySQL, чтобы изменения в конфиге вступили в силу:
root@master# service mysqld restart
Если все прошло успешно, команда «show master status » должна показать примерно следующее:
mysql@master> SHOW MASTER STATUSG
File: mysql-bin.000003
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
Значение position должно увеличиваться по мере того, как вносятся изменения в БД на мастере.
Настройки реплики
Укажем ID сервера, имя БД для репликации и путь к relay-бинлогам в секции [mysqld] конфига, затем перезагрузим MySQL:
= 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
replicate-do-db = testdb
root@replica# service mysqld restart
Переносим данные
Здесь нам придется заблокировать БД для записи. Для этого можно либо остановить работу приложений, либо воспользоваться установкой флажка read_only на мастере (внимание: на пользователей с привилегией SUPER этот флаг не действует). Если у нас есть таблицы MyISAM, сделаем также «flush tables»:
mysql@master> FLUSH TABLES WITH READ LOCK;
mysql@master> SET GLOBAL read_only = ON;
Посмотрим состояние мастера командой «show master status» и запомним значения File и Position (после успешной блокировки мастера они не должны изменятся):
File: mysql-bin.000003
Position: 98
Делаем дамп БД, и после завершения операции снимаем блокировку мастера:
mysql@master> SET GLOBAL read_only = OFF;
Переносим дамп на реплику и восстанавливаем из него данные.
Наконец, запускаем репликацию командами «change master to» и «start slave» и посмотрим, все ли прошло хорошо:
mysql@replica> CHANGE MASTER TO MASTER_HOST = «192.168.1.101 «, MASTER_USER = «replication «, MASTER_PASSWORD = «password «, MASTER_LOG_FILE = «mysql-bin.000003 «, MASTER_LOG_POS = 98;
mysql@replica> start slave;
Значения MASTER_LOG_FILE и MASTER_LOG_POS мы берем с мастера.
Посмотрим, как идет репликация командой «show slave status «:
mysql@replica> SHOW SLAVE STATUSG
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 98
Relay_Log_File: mysql-relay-bin.001152
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb,testdb
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: 98
Relay_Log_Space: 235
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: 5
Наиболее интересные сейчас значения я выделил. При успешном начале репликации их значения должны быть примерно такими, как в листинге (см. описание команды «show slave status » в документации). Значение Seconds_Behind_Master может быть любым целым числом.
Если репликация идет нормально, реплика будет следовать за мастером (номер лога в Master_Log_File и позиция Exec_Master_Log_Pos будут расти). Время отставания реплики от мастера (Seconds_Behind_Master), в идеале, должно быть равно нулю. Если оно не сокращается или растет, возможно, что нагрузка на реплику слишком высока — она просто не успевает повторять изменения, происходящие на мастере.
Если же значение Slave_IO_State пусто, а Seconds_Behind_Master равно NULL, репликация не началась. Смотрите лог MySQL для выяснения причины, устраняйте её и заново запускайте репликацию:
mysql@replica> start slave;
Путем этих нехитрых действий мы получаем реплику, данные которой идентичны данным на мастере.
Кстати, время блокировки мастера — это время создания дампа. Если он создается недопустимо долго, можно попробовать поступить так:
- заблокировать запись в мастер флагом read_only, запомнить позицию и остановить MySQL.
- после этого скопировать файлы БД на реплику и включить мастер.
- начать репликацию обычным способом.
Существует несколько способов создать реплику без остановки мастера вообще, но они срабатывают не всегда.
Добавляем реплики
Пусть у нас уже есть работающие мастер и реплика, и нам нужно добавить к ним еще одну. Сделать это даже проще, чем добавить первую реплику к мастеру. И гораздо приятнее то, что нет необходимости останавливать для этого мастер.
Для начала настроим MySQL на второй реплике и убедимся, что мы внесли нужные параметры в конфиг:
= 3
replicate-do-db = testdb
Теперь остановим репликацию на первой реплике:
stop slave;
Реплика продолжит работать нормально, однако данные на ней уже не будут актуальными. Посмотрим статус и запомним позицию мастера, до которой реплика дошла перед остановкой репликации:
SHOW SLAVE STATUSG
Нам нужные будет значения Master_Log_File и Exec_Master_Log_Pos:
Master_Log_File: mysql-bin.000004
Exec_Master_Log_Pos: 155
Создадим дамп БД и продолжим репликацию на первой реплике:
START SLAVE;
Восстановим данные из дампа на второй реплике. Затем включим репликацию:
CHANGE MASTER TO MASTER_HOST = «192.168.1.101 «, MASTER_USER = «replication «, MASTER_PASSWORD = «password «, MASTER_LOG_FILE = «mysql-bin.000004 «, MASTER_LOG_POS = 155;
START SLAVE;
Значения MASTER_LOG_FILE и MASTER_LOG_POS — это соответственно значения Master_Log_File и Exec_Master_Log_Pos из результата команды «show slave status » на первой реплике.
Репликация должна начаться с той позиции, на которой была остановлена первая реплика (и соответственно, создан дамп). Таким образом, у нас будет две реплики с идентичными данными.
Объединяем реплики
Иногда возникает такая ситуация: на мастере существует две БД, одна из которых реплицируется на одной реплике, а вторая — на другой. Как настроить репликацию двух БД на обеих репликах, не делая их дампы на мастере и не выключая его из работы? Достаточно просто, с использованием команды «start slave until «.
Итак, у нас имеется master с базами данных testdb1 и testdb2, которые реплицируются соответственно на репликах Настроим репликацию обеих БД на replica-1 без остановки мастера.
Остановим репликацию на replica-2 командой и запомним позицию мастера:
STOP SLAVE;
SHOW SLAVE STATUSG
Master_Log_File: mysql-bin.000015
Exec_Master_Log_Pos: 231
Создадим дамп БД testdb2 и возобновим репликацию (на этом манипуляции с replica-2 закончились). Дамп восстановим
Ситуация на replica-1 такая: БД testdb1 находится на одной позиции мастера и продолжает реплицироваться, БД testdb2 восстановлена из дампа с другой позиции. Синхронизируем их.
Остановим репликацию и запомним позицию мастера:
STOP SLAVE;
SHOW SLAVE STATUSG
Master_Log_File: mysql-bin.000016
Exec_Master_Log_Pos: 501
Убедимся, что в конфиге на секции [mysqld] указано имя второй БД:
replicate-do-db = testdb2
Перезагрузим MySQL, чтобы изменения в конфиге вступили в силу. Кстати, можно было просто перезагрузить MySQL, не останавливая репликацию — из лога мы бы узнали, на какой позиции мастера репликация остановилась.
Теперь проведем репликацию с позиции, на которой была приостановлена позиции, на которой мы только что приостановили репликацию:
CHANGE MASTER TO MASTER_HOST = «192.168.1.101 «, MASTER_USER = «replication «, MASTER_PASSWORD = «password «, MASTER_LOG_FILE = «mysql-bin.000015 «, MASTER_LOG_POS = 231;
start slave until MASTER_LOG_FILE = «mysql-bin.000016 «, MASTER_LOG_POS = 501;
Репликация закончится, как только реплика дойдет до указанной позиции в секции until, после чего обе наши БД будут соответствовать одной и той же позиции мастера (на которой мы остановили репликацию Убедимся в этом:
SHOW SLAVE STATUSG
START SLAVE;
Master_Log_File: mysql-bin.000016
Exec_Master_Log_Pos: 501
Добавим в конфиг на секции [mysqld] имена обеих БД:
replicate-do-db = testdb1
replicate-do-db = testdb2
Важно: каждая БД должна быть указана на отдельной строке.
Перезагрузим MySQL и продолжим репликацию:
CHANGE MASTER TO MASTER_HOST = «192.168.1.101 «, MASTER_USER = «replication «, MASTER_PASSWORD = «password «, MASTER_LOG_FILE = «mysql-bin.000016 «, MASTER_LOG_POS = 501;
После того, как replica-1 догонит мастер, содержание их БД будет идентично. Объединить БД на replica-2 можно или подобным образом, или сделав полный дамп
Рокировка мастера и реплики
Переключить реплику в режим мастера бывает необходимо, например, в случае отказа мастера или при проведении на нем технических работ. Для возможности такого переключения необходимо настроить реплику подобно мастеру, или сделать её пассивным мастером.
Включим ведение бинарных логов (дополнительно к relay-бинлогам) в конфиге в секции [mysqld]:
log-bin = /var/lib/mysql/mysql-bin
И добавим пользователя для ведения репликации:
mysql@master> GRANT replication slave ON ’testdb’.* TO ’replication’@’192.168.1.101′ IDENTIFIED BY «password «;
Пассивный мастер ведет репликацию как и обычная реплика, но кроме этого создает бинарные логии — то есть, мы можем начать репликацию с него. Убедимся в этом командой «show master status «:
mysql@replica> SHOW MASTER STATUSG
File: mysql-bin.000001
Position: 61
Binlog_Do_DB:
Binlog_Ignore_DB:
Теперь, чтобы перевести пассивный мастер в активный режим, необходимо остановить репликацию на нем и включить репликацию на бывшем активном мастере. Чтобы в момент переключения данные не были утеряны, активный мастер необходимо заблокировать на запись.
mysql@master> FLUSH TABLES WITH READ LOCK
mysql@master> SET GLOBAL read_only = ON;
mysql@replica> STOP SLAVE;
mysql@replica> SHOW MASTER STATUS;
File: mysql-bin.000001
Position: 61
mysql@master> CHANGE MASTER TO MASTER_HOST = «192.168.1.102 «, MASTER_USER = «replication «, MASTER_PASSWORD = «password «, MASTER_LOG_FILE = «mysql-bin.000001 «, MASTER_LOG_POS = 61;
mysql@master> start slave;
Все, так мы поменяли активный мастер. Можно снять с бывшего мастера блокировку.
Заключение
Мы немного разобрались в том, как настраивать репликацию в MySQL и выполнять некоторые основные операции. К сожалению, за рамками статьи остались следующие важные вопросы:
- устранение единичных точек отказа (SPF, Single Points of Failure). При использовании единственного сервера MySQL, его отказ приводил к отказу всей системы. При использовании нескольких серверов, отказ любого из них приведет к отказу системы, если только мы специально не позаботимся об этом. Нам нужно предусмотреть обработку ситуации с отказом мастера и реплики. Одно из существующих средств — MMM, однако, требует доработки напильником.
- балансировка нагрузки. При использовании нескольких реплик нам было бы удобно использовать прозрачный механизм балансировки, особенно если производительность реплик неодинакова. Под Linux возможно использовать стандартное решение — LVS.
- изменение логики работы приложения. В идеальной ситуации, запросы на чтение данных надо направлять на реплики, а на изменение — на мастер. Однако, отставания реплик, такая схема часто неработоспособна и необходимо выявлять такие запросы на чтение, которые все же должны выполнятся на мастере.
Надеюсь осветить эти вопросы в дальнейших статьях.
Спасибо за внимание!
Источник
mysql master-slave replication, often encounter errors and lead to slave end replication interruption, this time generally requires manual intervention, skip errors to continue
There are two ways to skip errors:
1.1 Skip a specified number of transactions:
mysql>stop slave; mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; #Skip a transaction mysql>start slave;
1.2 Modify mysql configuration file to skip all errors or specified types of errors through the slave_skip_errors parameter
vi /etc/my.cnf [mysqld] #slave-skip-errors=1062,1053,1146 #Skip the error of the specified error no type #slave-skip-errors=all #Skip all errors
2 cases
Let’s simulate an error scenario
Environment (a configured master-slave replication environment)
master database IP: 192.168.247.128
Slve database IP: 192.168.247.130
mysql version: 5.6.14
binlog-do-db = mydb
Execute the following statement on master:
mysql>use mysql; mysql>create table t1 (id int); mysql>use mydb; mysql>insert into mysql.t1 select 1;
View replication status on slave
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.247.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000017
Read_Master_Log_Pos: 2341
Relay_Log_File: DBtest1-relay-bin.000011
Relay_Log_Pos: 494
Relay_Master_Log_File: mysql-bin.000017
Slave_IO_Running: Yes
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: 1146
Last_Error: Error 'Table 'mysql.t1' doesn't exist' on query. Default database: 'mydb'. Query: 'insert into mysql.t1 select 1'
Skip_Counter: 0
Exec_Master_Log_Pos: 1919
Relay_Log_Space: 1254
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: 1146
Last_SQL_Error: Error 'Table 'mysql.t1' doesn't exist' on query. Default database: 'mydb'. Query: 'insert into mysql.t1 select 1'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f0f7faf6-51a8-11e3-9759-000c29eed3ea
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 131210 21:37:19
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
As can be seen from the results, Read_Master_Log_Pos: 2341, Exec_Master_Log_Pos: 1919 error Last_SQL_Error: Error’Table’mysql.t1’does’t exist’ on query.
Because only binlog is recorded for mydb, errors occur when tables in other databases are operated on the mydb library but do not exist on slave.
Let’s look at the transaction content in the binlog, where a row represents a transaction.
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000017' from 1919G
*************************** 1. row ***************************
Log_name: mysql-bin.000017
Pos: 1919
Event_type: Query
Server_id: 1
End_log_pos: 1999
Info: BEGIN
*************************** 2. row ***************************
Log_name: mysql-bin.000017
Pos: 1999
Event_type: Query
Server_id: 1
End_log_pos: 2103
Info: use `mydb`; insert into mysql.t1 select 1
*************************** 3. row ***************************
Log_name: mysql-bin.000017
Pos: 2103
Event_type: Xid
Server_id: 1
End_log_pos: 2134
Info: COMMIT /* xid=106 */
*************************** 4. row ***************************
Log_name: mysql-bin.000017
Pos: 2134
Event_type: Query
Server_id: 1
End_log_pos: 2213
Info: BEGIN
*************************** 5. row ***************************
Log_name: mysql-bin.000017
Pos: 2213
Event_type: Query
Server_id: 1
End_log_pos: 2310
Info: use `mydb`; insert into t1 select 9
*************************** 6. row ***************************
Log_name: mysql-bin.000017
Pos: 2310
Event_type: Xid
Server_id: 1
End_log_pos: 2341
Info: COMMIT /* xid=107 */
6 rows in set (0.00 sec)
From the above results, we need to skip two transactions (Pos: 1999 insert, Pos: 2103 commit)
Skip operation:
mysql>stop slave;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; Skip a transaction
mysql>start slave;
mysql> show slave statusG
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: mydb
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.247.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000017
Read_Master_Log_Pos: 3613
Relay_Log_File: DBtest1-relay-bin.000018
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000017
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: 3613
Relay_Log_Space: 458
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
Master_UUID: f0f7faf6-51a8-11e3-9759-000c29eed3ea
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.01 sec)
The replication status is normal.
MySQL GTID replication error can happen unexpectedly for various reasons and as often as the classic master slave, circular or multi-master replication but in this short tutorial we will learn, step by step, how to skip MySQL GTID transaction error in order to keep in sync our MySQL slave server. Before we begin we need to say that all below steps were tested in a master-slave setup using MySQL 5.6 and MySQL 5.7, we think that this MySQL skipping transaction method detailed below can be easily applied even for newer MySQL versions like 8+. Please note that all below operations were taken on the slave server only, no master queries or changes were needed.
Table of Contents
MySQL GTID Replication Error
This tutorial about MySQL GTID Replication Error contains the next sections:
Show MySQL Slave Status via CLI
Stop MySQL Slave
Fix MySQL GTID Replication Error
Skip MySQL GTID Replication Error
Start MySQL Slave
Check MySQL Slave Replication Status
Show MySQL Slave Status via CLI
Let’s start by checking our MySQL slave server status via CLI by executing show slave status G query like shown in the example below:
mysql> SHOW SLAVE STATUS G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.1.1.1
Master_User: dbrepuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000706
Read_Master_Log_Pos: 374830771
Relay_Log_File: dbm-relay-bin.000713
Relay_Log_Pos: 959223458
Relay_Master_Log_File: bin.000691
Slave_IO_Running: Yes
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: 1396
Last_Error: Error 'Operation ALTER USER failed for 'dummyuser'@'10.1.%.%'' on query. Default database: 'mysql'. Query: 'ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C''
Skip_Counter: 0
Exec_Master_Log_Pos: 959223257
Relay_Log_Space: 16698942535
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: 1396
Last_SQL_Error: Error 'Operation ALTER USER failed for 'dummyuser'@'10.1.%.%'' on query. Default database: 'mysql'. Query: 'ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: bdf3bf63-0cc9-11e8-89e8-000d3a365fa6
Master_Info_File: /mnt/mysql-data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180628 06:29:51
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:58869595-59928201
Executed_Gtid_Set: b2c84f0c-5dcb-11e8-a550-005056847b4c:1-13, bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:52044900-59506053
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
We can clearly see on the row called Slave_SQL_Running that our slave isn’t executing any new SQL queries received from the master due a SQL query error like shown on the row Last_SQL_Error.
Stop MySQL Slave
Our next step is to stop MySQL slave by executing a SQL query in order to fix the replication error, we won’t stop mysql/mysqld service (daemon) for this we just need to stop the process that handles the replication. Let’s run the SQL query that will stop the slave process for us:
mysql> STOP SLAVE;
By executing once again the SQL query show slave statusG we should be able now to see that our slave is stopped and no replication is happening between master and slave:
mysql> SHOW SLAVE STATUS G
...
Slave_IO_Running: No
Slave_SQL_Running: No
...
Fix MySQL GTID Replication Error
Knowing now that our MySQL replication is stopped let’s take a closer look at the error message.
Last_SQL_Error: Error 'Operation ALTER USER failed for 'dummyuser'@'10.1.%.%'' on query. Default database: 'mysql'. Query: 'ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C''
The error message says that the ALTER USER query failed for the user named dummyuser. Let’s check our mysql.user table to identify the issue with this query.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user, host, authentication_string from user where user like '%dummyuser%';
+----------------+------+-------------------------------------------+
| user | host | authentication_string |
+----------------+------+-------------------------------------------+
| dummyuser | 10.% | *1B2319A2556FC59343FE08BC9027EFF385AD7A1D |
+----------------+------+-------------------------------------------+
1 row in set (0.00 sec)
We can now see the differences between the SQL query that’s failing and the actual data that’s in our table. First issue that we notice is present in the host column, the error shows users host as being 10.1.%.% but in our table we have 10.%. The second issue is with authentication_string column, we can see that the password has been updated and it doesn’t match anymore. The conclusion here is that the password for our dummyuser has been updated on the master. On the slave server we didn’t had that record to match the criteria, meaning that someone else updated the record on the slave manually by replacing the host entry with 10.%.
To avoid such situations make sure that you never perform any changes on slave, all SQL changes must be carried out on the master server as this is the data source for our slave.
Let’s fix this error by updating our record on the slave server.
mysql> UPDATE user SET host='10.1.%.%' WHERE host='10.%' AND user='dummyuser';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Using the queries above we’ve updated our host and authentication_string (hashed password) records now to match with the records from our master server. Now we have the same data on the slave as on the master, no data was lost.
Skip MySQL GTID Replication Error
The classic method for skipping master-slave errors won’t work in this particular case as our replication is GTID.
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
The error above is expected as we’re running a MySQL GTID replication setup.
Let’s have a look on the last transaction executed on the slave that failed:
Executed_Gtid_Set: b2c84f0c-5dcb-11e8-a550-005056847b4c:1-13, bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:52044900-59506053
We know now that the transaction number 59506053 coming from our master server having the ID bdf3bf63-0cc9-11e8-89e8-000d3a365fa6 failed.
Having now the record fixed manually on the slave we can carry on and insert an empty transaction in order to bypass the error. We’ll need to increment the transaction via a SQL query like shown below in order to bypass the error.
mysql> SET GTID_NEXT='bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:59506054';
Now we’ll have to commit our change:
mysql> BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC';
Start MySQL Slave
Having everything in place like transaction number incremented and change committed we can try to start our GTID slave replication back with the next SQL query:
mysql> START SLAVE;
A successful show slave statusG will look like this:
mysql> SHOW SLAVE STATUS G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Check MySQL Slave Replication Status
We know that our slave is running now, receiving data from the master and we can check how far the slave is behind the master like this:
mysql> SHOW SLAVE STATUS G
...
Seconds_Behind_Master: 168356
...
Once the Seconds_Behind_Master value goes down to 0 seconds then we can say that our slave is fully synced.
mysql> SHOW SLAVE STATUS G
...
Seconds_Behind_Master: 0
...
There are several ways to resolve errors in master-slave replication
1. If a primary key conflict occurs during master-slave replication, which prevents master-slave replication, you can use the sql_slave_skip_counter variable to ignore the error and exclude it
2. If a large error occurs, consider reconfiguring from the server to recover the error using the reset slave method
The following demonstrates how to use these two methods to resolve errors, with detailed instructions for related operations
How to use reset slave
Environment ready to set up master-slave synchronization
Primary Node Configuration
1. Modify the configuration file
[root@Master ~]# vim /etc/my.cnf [mysqld] log-bin=/data/bin/mysql-bin binlog-format=row server-id=1
2. Create a binary log directory
[root@Master ~]# mkdir /data/bin [root@Master ~]# chown -R mysql.mysql /data/bin
3. Start the mysqld service
[root@Master ~]# systemctl start mariadb
4. View the primary server log location
[root@Master ~]# mysql -e "SHOW MASTER LOGS;" +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 26753 | | mysql-bin.000002 | 921736 | | mysql-bin.000003 | 245 | +------------------+-----------+
5. Create an account to copy data
[root@Master ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.73.%' IDENTIFIED BY 'centos';"
Configure from Node
1. Modify the configuration file
[root@Slave ~]# vim /etc/my.cnf [mysqld] read-only server-id=2
2. Start the service
[root@Slave ~]# systemctl start mariadb
Start building incorrect configurations here
All of the following CHANGE MASTER TO Configuration is wrong
3.To configure CHANGE MASTER TOMariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='master2.mycompany.com', -> MASTER_USER='replication', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master2-bin.001', -> MASTER_LOG_POS=4, -> MASTER_CONNECT_RETRY=10; Query OK, 0 rows affected (0.00 sec)
4. View SLAVE STATUS
MariaDB [(none)]> SHOW SLAVE STATUSG;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: master2.mycompany.com
Master_User: replication
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master2-bin.001
Read_Master_Log_Pos: 4
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master2-bin.001
Slave_IO_Running: No
Slave_SQL_Running: No
...The following omissions...
5. Start the replication thread
MariaDB [(none)]> START SLAVE;
6. Check SLAVE STATUS again
MariaDB [(none)]> SHOW SLAVE STATUSG;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: master2.mycompany.com
Master_User: replication
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master2-bin.001
Read_Master_Log_Pos: 4
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master2-bin.001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
...The following omissions...
Thread has started normally
Primary Server Import Data Test
[root@Master ~]# mysql < hellodb_innodb.sql [root@Master ~]# mysql -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+
See if synchronization is possible from the server (CHANGE MASTER TO information is not properly synchronized)
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
The following are error resolution methods
Since the error occurred in CHANGE MASTER TO, partial correction of CHANG MASTER TO is OK here
1. First stop the replication thread from the server
MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected (17.48 sec)
2. Reset the SLAVE information from the server
MariaDB [(none)]> RESET SLAVE; Query OK, 0 rows affected (0.01 sec)
3. Re-enter the correct CHANGE MASTER TO information
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.73.110',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=245; Query OK, 0 rows affected (0.01 sec)
4. View SLAVE STATUS;
MariaDB [(none)]> SHOW SLAVE STATUSG;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
#The information here has been corrected to correct
5. Restart Threads
MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)
6. Check SLAVE STATUS again;
MariaDB [(none)]> SHOW SLAVE STATUSG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 7384 #Data has already been copied
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 7668
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#IO and SQL threads started
7. See if the libraries from within the node are synchronized
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | #The hellodb library has been copied from the primary node | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec)
Other instructions:
In production, if data deviation between master and slave nodes is large and cannot be synchronized at a later time, you may consider purging all from the server from the new configuration slave server.You can refer to https://blog.51cto.com/11886307/2390636 for specific configuration methods
About the usage of sql_slave_skip_counter
This option can be used to ignore errors when a primary key conflict occurs and the slave server will no longer be serviced in the wrong location. This error usually occurs when the master replicate or the slave server already occupies a record.
Build Error
Continue to use the master-slave replication environment just now
1. Create a record from the server
MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,'Li Xiaolong',30,'M'); Query OK, 1 row affected (0.00 sec)
2. Create a record on the primary server with the same primary key
MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,'Xiao Yan',20,'M'); Query OK, 1 row affected (0.00 sec)
3. Return to view SLAVE STATUS from node
MariaDB [(none)]> SHOW SLAVE STATUSG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 7576
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 7668
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
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: 1062
Last_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000003, end_log_pos 7549
Skip_Counter: 0
Exec_Master_Log_Pos: 7384
Relay_Log_Space: 8156
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: 1062
Last_SQL_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000003, end_log_pos 7549
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
4. Continue adding records from the primary node after an error has occurred
MariaDB [(none)]> INSERT hellodb.teachers VALUE (6,'Xiao Xuner',20,'M'); Query OK, 1 row affected (0.00 sec)
5. At this point the secondary node will no longer replicate information from the primary node
MariaDB [(none)]> SELECT * FROM hellodb.teachers WHERE tid>4; +-----+-------------+-----+--------+ | TID | Name | Age |Gender | +-----+-------------+-----+--------+ | 5 | Li Xiaolong | 30 | M | #This is the record you just added from the node +-----+-------------+-----+--------+ 1 row in set (0.00 sec)
Troubleshooting
1. Ignore errors using sql_slave_skip_counter variable
MariaDB [(none)]> SET GLOBAL sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec)
2. Stop the thread and restart
MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)
3. Check the slave status and there is no error message
MariaDB [(none)]> SHOW SLAVE STATUSG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 7770
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000003
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: 7770
Relay_Log_Space: 8634
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)
4. View the teachers table from the server
MariaDB [(none)]> SELECT * FROM hellodb.teachers WHERE tid>4; +-----+-------------+-----+--------+ | TID | Name | Age | Gender | +-----+-------------+-----+--------+ | 5 | Li Xiaolong | 30 | M | | 6 | Xiao Xuner | 20 | M | #At this point, the record No. 6 just inserted in the primary node has been copied over +-----+-------------+-----+--------+ 2 rows in set (0.00 sec)
The above are some related fixes for errors in master-slave replication