Как восстановить репликацию MySQL или MariaDB
Если кластер работает в режиме Master — Master, сначала найдем ноду, на которой произошел сбой репликации. Для этого заходим на каждом сервере в оболочку mysql следующей командой:
* в данном примере заходим от имени пользователя root.
И выводим состояние ноды в режиме Slave:
mysql> SHOW SLAVE STATUS\G
В случае проблем с репликацией мы увидим значения Slave_IO_Running и/или Slave_SQL_Running в состоянии No, а также описание ошибки:
На рабочей Master-ноде
Блокируем все таблицы всех баз для чтения и записи:
mysql> FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON;
И выводим состояние работы СУРБД:
mysql> show master status\G
Результат будет, примерно, таким:
File: mysql-bin.000015
Position: 6315
Binlog_Do_DB:
Binlog_Ignore_DB: information_schema,mysql
Запомним или запишем значения для File и Position. Они понадобятся при восстановлении вторичной ноды кластера.
Теперь выходим из командной оболочки базы:
и создаем дамп рабочих баз:
# mysqldump -uroot -p -v —databases db1 db2 > /tmp/mydb_dump.sql
* данная команда сделает дамп баз db1, db2 и сохранит его в файл /tmp/mydb_dump.sql.
Теперь снова подключаемся к MySQL:
и снимаем ранее установленные блокировки:
mysql> SET GLOBAL read_only = OFF;
Полученный ранее файл с резервной копией переносим на второй сервер при помощи такой команды:
# scp /tmp/mydb_dump.sql dmosk@192.168.166.156:/tmp
* в данном примере, мы скопируем созданный нами дамп /tmp/mydb_dump.sql в каталог /tmp сервера 192.168.166.156 подключившись под учетной записью dmosk.
На нерабочей ноде
Создаем дамп баз:
# mysqldump -uroot -p -v —databases db1 db2 > /tmp/mydb_dump_slave.sql
Заходим в оболочку управления MySQL:
mysql> stop slave;
Удаляем старые базы:
mysql> drop database db1;
mysql> drop database db2;
* в данном примере удаляются базы, для которых мы сделали резервные копии.
И создаем их заново:
mysql> CREATE DATABASE db1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
mysql> CREATE DATABASE db2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Выходим из оболочки:
Теперь восстанавливаем базы из ранее созданного дампа:
# mysql -v -uroot -p change master to master_host = «192.168.166.155», master_user = «replmy», master_password = «password», master_log_file = «mysql-bin.000015», master_log_pos = 6315;
* 192.168.166.155: IP-адрес моего первого сервера. replmy: учетная запись для репликации, которая была создана при создании кластера. password: пароль для учетной записи replmy. mysql-bin.000015: имя файла, которое мы должны были записать или запомнить (у вас может быть другим). 6315: номер позиции, с которой необходимо начать репликацию (также должны были записать или запомнить ранее).
Запускаем репликацию следующей командой:
mysql> start slave;
И проверяем состояние репликации:
mysql> SHOW SLAVE STATUS\G
Состояние Slave_IO_Running и Slave_SQL_Running должно быть Yes, а ошибки должны исчезнуть:
Источник
Поиск неисправностей репликации
Если вы следовали инструкциям, но установленный механизм репликации не работает, прежде всего следует искать пользовательские ошибки. Выполните следующие проверки:
Производит ли головной сервер записи в двоичный журнал? Проверьте это при помощи команды SHOW MASTER STATUS . Если да, значение Position будет отличным от нуля. Если нет, проверьте, запущен ли головной сервер с опцией log-bin и установлен ли server-id .
Запущен ли подчиненный сервер? Проверьте это при помощи команды SHOW SLAVE STATUS . Ответ находится в столбце Slave_running . Если нет, проверьте опции подчиненного сервера и просмотрите сообщения в журнале ошибок.
Если подчиненный сервер запущен, установил ли он соединение с головным сервером? Выполните команду SHOW PROCESSLIST , найдите поток, которому соответствует значение system user в столбце User и none в столбце Host , и проверьте столбец State . Если в нем находится значение connecting to master , проверьте привилегии для пользователя репликации на головном сервере, имя хоста головного сервера, установку DNS, посмотрите, запущен ли головной сервер в текущее время, доступен ли он для подчиненного сервера. После этого, если все окажется в порядке, просмотрите журналы ошибок.
Если подчиненный сервер был запущен, но затем остановился, посмотрите на вывод команды SHOW SLAVE STATUS и проверьте журналы ошибок. Такое обычно случается, когда некоторый запрос, успешно выполняющийся на головном сервере, не выполняется на подчиненном. Если создан корректный образ головного сервера и данные на подчиненном сервере обновлялись только через поток подчиненного сервера, этого происходить не должно. Но если все же такое случилось — значит, имеет место ошибка; как сообщить о ней, читайте ниже.
Если запрос, успешно выполняемый на головном сервере, не выполняется на подчиненном, и нельзя выполнить полную ресинхронизацию базы данных (ее стоит выполнить), попробуйте сделать следующее:
Сначала проверьте: возможно где-либо случайно оказалась ненужная запись. Разберитесь, как она оказалась там, затем удалите ее, и выполните команду SLAVE START .
Если вы проделали все, о чем написано выше, и ничего не помогло или этого сделать нельзя, попытайтесь понять, будет ли безопасно выполнить обновления вручную (если необходимо) и после этого игнорировать следующий запрос от головного сервера.
Если вы решили пропустить следующий запрос, выполните команды SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; чтобы пропустить запрос, не использующий функции AUTO_INCREMENT или LAST_INSERT_ID() . В противном случае выполните команды SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; SLAVE START . Причина того, что запросы, использующие функции AUTO_INCREMENT или LAST_INSERT_ID() , обрабатываются по-другому, заключается в том, что они создают два события в двоичном журнале головного сервера.
Если вы уверены, что подчиненный сервер был успешно запущен и синхронизирован с головным сервером, а также что обновления таблиц не производились вне потока подчиненного сервера, пришлите нам отчет об ошибке, и вам не потребуется опять повторять описанные выше уловки.
Удостоверьтесь, что вы не внесли старой ошибки при апгрейде MySQL до более новой версии.
Если ничего не помогает, просмотрите журналы ошибок. Если журналы большие, выполните команду grep -i slave /path/to/your-log.err на подчиненном сервере. Искать ошибку на головном сервере — не лучшая идея, поскольку в его журналах находятся лишь системные ошибки общего характера; если это возможно, он посылает ошибку на подчиненный сервер, когда что-либо происходит не так, как надо.
Если вы убедились, что пользовательская ошибка здесь ни при чем, однако механизм репликации по-прежнему не работает или работает нестабильно, пришло время начать работу над отчетом об ошибке. Вы должны предоставить нам столько информации, сколько нужно, чтобы отследить ошибку. Пожалуйста, уделите отчету об ошибке нужное количество времени и усилий, чтобы сделать его хорошо. В идеале мы хотели бы иметь контрольный пример в формате, который находится в каталоге mysql-test/t/rpl* исходного дерева. Отослав такой контрольный пример, в большинстве случаев можно рассчитывать на получение патча в течение одного-двух дней, хотя, конечно, это время может варьироваться в зависимости от множества факторов.
Еще один хороший способ проинформировать нас об ошибке — написать простую программу с легко конфигурируемыми параметрами соединения для головного и подчиненного серверов, в которой будет продемонстрирована проблема наших систем. Программа может быть написана на Perl или на C, в зависимости от того, какой язык вы знаете лучше.
Подготовив информацию об ошибке одним из двух способов, используйте утилиту mysqlbug , чтобы создать отчет об ошибке, и пошлите его по адресу . Если же вы имеете дело с фантомом — проблемой, которая имеет место, но вы по какой-либо причине не можете ее воспроизвести по желанию:
Убедитесь, что эта проблема не вызвана пользовательской ошибкой. Например, при обновлении подчиненного сервера вне потока подчиненного сервера данные будут не синхронизированы, и могут быть нарушения уникальных ключей при обновлениях. В этом случае поток подчиненного сервера остановится и будет ждать, пока таблицы не будут очищены вручную, для приведения их в синхронизированный режим.
Запустите подчиненный сервер с опциями log-slave-updates и log-bin — при этом в журнал будет заноситься информация обо всех обновлениях, происходящих на подчиненном сервере.
Сохраните все доказательства наличия ошибки перед сбросом репликации. Если у нас нет информации о проблеме, или имеется только отрывочная информация, потребуется время, чтобы найти источник проблемы. Вы должны собрать следующие «свидетельства»:
Все двоичные журналы головного сервера
Весь двоичный журнал подчиненного сервера
Вывод команды SHOW MASTER STATUS на головном сервере во время обнаружения проблемы
Вывод команды SHOW SLAVE STATUS на головном сервере во время обнаружения проблемы
Журналы ошибок головного сервера и подчиненного сервера
Для изучения двоичных журналов используйте утилиту mysqlbinlog . Таким образом можно находить проблемные запросы, например:
Собрав «свидетельства» о проблеме-фантоме, попробуйте сначала организовать их в отдельный контрольный пример. После этого сообщите о проблеме по адресу , описав эту проблему во всех подробностях.
Источник
Как идентифицировать и устранить подчиненную задержку репликации MySQL
Этот пост был изначально написан Мухаммедом Ирфаном
Здесь, в группе поддержки Percona MySQL , мы часто сталкиваемся с проблемами, когда клиент жалуется на задержки репликации, и во многих случаях проблема заканчивается привязкой к ведомой репликации MySQL. Это, конечно, не является чем-то новым для пользователей MySQL, и у нас было несколько постов в блоге по производительности MySQL на эту тему за эти годы (в прошлом два особенно популярных поста были: « Причины задержки репликации MySQL » и « Управление подчиненным устройством»). Отставание с MySQL Replication .
Однако в сегодняшнем сообщении я расскажу о некоторых новых способах выявления задержек в репликации, включая возможные причины отстающих рабов, и о том, как решить эту проблему.
Как определить задержку репликации
Репликация MySQL работает с двумя потоками, IO_THREAD и SQL_THREAD. IO_THREAD подключается к мастеру, читает двоичные события журнала от мастера по мере их поступления и просто копирует их в локальный файл журнала, называемый relaylog . С другой стороны, SQL_THREAD считывает события из журнала ретрансляции, хранящегося локально на ведомом устройстве репликации (файл, который был записан потоком ввода-вывода), а затем применяет их как можно быстрее. Всякий раз, когда репликация задерживается, важно сначала выяснить, задерживается ли она на ведомом IO_THREAD или ведомом SQL_THREAD.
Обычно поток ввода-вывода не вызывает большой задержки репликации, поскольку он просто читает двоичные журналы с главного устройства. Тем не менее, это зависит от подключения к сети, задержки в сети … как быстро это между серверами. Подчиненный поток ввода-вывода может быть медленным из-за высокой пропускной способности. Обычно, когда ведомый IO_THREAD способен читать двоичные журналы достаточно быстро, он копирует и накапливает журналы ретрансляции на ведомом устройстве, что является одним из признаков того, что ведомый IO_THREAD не является виновником задержки ведомого устройства.
С другой стороны, когда ведомый SQL_THREAD является источником задержек репликации, это, вероятно, связано с тем, что запросы, поступающие из потока репликации, слишком долго выполняются на ведомом устройстве. Иногда это происходит из-за разного оборудования между главным и подчиненным, разных индексов схемы, рабочей нагрузки. Более того, рабочая нагрузка подчиненного OLTP иногда вызывает задержки репликации из-за блокировки. Например, если длительное чтение таблицы MyISAM блокирует поток SQL, или любая транзакция таблицы InnoDB создает блокировку IX и блокирует DDL в потоке SQL. Кроме того, примите во внимание, что подчиненный однопоточный до MySQL 5.6, который был бы другой причиной задержек на ведомом SQL_THREAD.
Позвольте мне показать вам через главный статус / статус ведомого, чтобы определить, что подчиненное устройство отстает от ведомого IO_THREAD или ведомого SQL_THREAD.
Это ясно указывает на то, что ведомый IO_THREAD отстает, и, очевидно, из-за этого ведомый SQL_THREAD также отстает, что приводит к задержкам репликации. Как вы можете видеть, главный файл журнала — это mysql-bin.018196 (параметр файла из основного состояния), а ведомый IO_THREAD находится в mysql-bin.018192 ( главный_лог_файл из подчиненного состояния ), который указывает, что ведомый IO_THREAD читает из этого файла, в то время как на главном он пишет на mysql-bin.018196 , поэтому ведомый IO_THREAD отстает на 4 бинлога. Между тем, ведомый SQL_THREAD читает из того же файла, то есть mysql-bin.01819 2 (Relay_Master_Log_File из статуса ведомого) Это указывает на то, что ведомый SQL_THREAD применяет события достаточно быстро, но он также запаздывает, что можно наблюдать по разнице между Read_Master_Log_Pos & Exec_Master_Log_Pos из выходных данных показа ведомого состояния.
Вы можете рассчитать задержку ведомого SQL_THREAD из Read_Master_Log_Pos — Exec_Master_Log_Pos в целом, при условии, что выходные данные параметра Master_Log_File из статуса show slave и параметр Relay_Master_Log_File из выходных данных статуса show slave одинаковы. Это даст вам приблизительное представление о том, как быстро ведомый SQL_THREAD применяет события. Как я уже упоминал выше, ведомый IO_THREAD отстает, как в этом примере, тогда отстает и ведомый SQL_THREAD. Вы можете прочитать подробное описание полей вывода статуса show slave здесь.
Кроме того, параметр Seconds_Behind_Master показывает огромную задержку в секундах. Однако это может вводить в заблуждение, поскольку оно измеряет только разницу между временными метками журнала ретрансляции, который был выполнен последним, по сравнению с записью журнала ретрансляции, которая была недавно загружена IO_THREAD. Если на ведущем блоке больше блоков, ведомый не учитывает их в расчете Seconds_behind_master. Вы можете получить более точную оценку задержки ведомого устройства, используя pt-heartbeat из Percona Toolkit. Итак, мы научились проверять задержки репликации — либо ведомый IO_THREAD, либо ведомый SQL_THREAD. Теперь позвольте мне дать несколько советов и предложений о том, чем именно вызвана эта задержка.
Советы и предложения Что вызывает задержку репликации и возможные исправления
Обычно ведомый IO_THREAD отстает из-за медленной сети между ведущим / ведомым. В большинстве случаев включение slave_compressed_protocol помогает уменьшить задержку ведомого IO_THREAD. Еще одно предложение — отключить бинарное ведение журнала на ведомом устройстве, поскольку оно также требует интенсивного ввода-вывода, если только вы не потребовали его для восстановления на определенный момент времени.
Чтобы минимизировать ведомую задержку SQL_THREAD, сконцентрируйтесь на оптимизации запросов. Я рекомендую включить опцию конфигурации log_slow_slave_statements, чтобы запросы, выполняемые ведомым устройством, которые занимают больше времени long_query_time , регистрировались в медленном журнале. Чтобы собрать больше информации о производительности запросов, я бы также рекомендовал установить для параметра конфигурации log_slow_verbosity значение «full».
Таким образом, мы можем видеть, есть ли запросы, выполняемые ведомым SQL_thread, для выполнения которых требуется много времени. Вы можете следить за моим предыдущим постом о том, как включить медленный журнал запросов для определенного периода времени с упомянутыми опциями здесь . И как напоминание, log_slow_slave_statements как переменная были впервые введены в Percona Server 5.1, который теперь является частью vanilla MySQL начиная с версии 5.6.11. В вышестоящей версии MySQL Server log_slow_slave_statements были введены как опция командной строки. Подробности можно найти здесь, в то время как log_slow_verbosity является специфической функцией Percona Server.
Еще одна причина задержки для ведомого SQL_THREAD, если вы используете формат binlog на основе строк, состоит в том, что если в любой таблице вашей базы данных отсутствует первичный ключ или уникальный ключ, то он будет сканировать все строки таблицы на наличие DML на ведомом устройстве и вызывает задержки репликации, поэтому убедитесь, что все ваши таблицы должны иметь первичный ключ или уникальный ключ. Подробности см. В этом отчете об ошибке http://bugs.mysql.com/bug.php?id=53375. Вы можете использовать приведенный ниже запрос на ведомом устройстве, чтобы определить, в какой из таблиц базы данных отсутствует первичный или уникальный ключ.
Одно из улучшений сделано для этого случая в MySQL 5.6, где в хеше памяти выручает slave_rows_search_algorithms .
Обратите внимание, что Seconds_Behind_Master не обновляется, пока мы читаем огромное событие RBR, поэтому «отставание» может быть связано именно с этим — мы не завершили чтение события. Например, при репликации на основе строк огромные транзакции могут вызвать задержку на ведомой стороне, например, если у вас есть таблица с 10 миллионами строк, и вы делаете «УДАЛИТЬ ИЗ таблицы, ГДЕ id 5 миллионов строк будут отправлены на подчиненное устройство, каждая строка отдельно, что будет болезненно медленный. Таким образом, если вам нужно время от времени удалять самые старые строки из огромной таблицы, используя Partitioning, это может быть хорошей альтернативой для этого для некоторых видов рабочих нагрузок, где вместо использования DELETE используйте DROP, старый раздел может быть хорошим, и реплицируется только оператор, потому что это будет операция DDL. ,
Чтобы объяснить это лучше, предположим, что у вас есть partition1, содержащий строки идентификаторов от 1 до 1000000 , partition2 — идентификаторы от 1000001 до 2000000 и т. Д., Поэтому вместо удаления с помощью оператора «DELETE FROM table WHERE ID Обратитесь к руководству по изменению работы с разделами — посмотрите этот замечательный пост от моего коллеги Романа, объясняющего возможные причины задержек репликации здесь.
pt-stalk — это один из лучших инструментов Percona Toolkit, который собирает диагностические данные при возникновении проблем. Вы можете настроить pt-stalk следующим образом, чтобы при возникновении задержки ведомого устройства он мог регистрировать диагностическую информацию, которую мы можем позже проанализировать, чтобы выяснить, что именно вызывает задержку.
Вот как вы можете настроить pt-stalk таким образом, чтобы он собирал диагностические данные при наличии ведомого лага:
Вы можете отрегулировать порог, в настоящее время равный 300 секундам, комбинируя его с параметром –cycles, это означает, что если значение seconds_behind_master равно> = 300 в течение 60 секунд или более, тогда pt-stalk начнет сбор данных. Добавление опции –notify-by-email будет уведомлять по электронной почте, когда pt- stalk собирает данные. Вы можете соответствующим образом настроить пороги pt-stalk таким образом, чтобы он запускал сбор диагностических данных во время проблемы.
Вывод
A lagging slave is a tricky problem but a common issue in MySQL replication. I’ve tried to cover most aspects of replication delays in this post. Please share in the comments section if you know of any other reasons for replication delay.
Источник