Синхронизация MySQL баз после ошибки репликации

В процессе длительной работы с двумя и более MySQL баз данных, между которым происходит репликация, можно столкнуться с массой мелких ошибок, вызванных, например, конфликтами первичных ключей, повреждениями журналов и т.п. Особенно, если репликация настроена, как мастер-мастер, конфликты гарантированы. Естественно, из-за мелких ошибок никто не станет полностью заново синхронизировать две базы данных, а скорее всего, пропустит ошибку через установку SQL_SLAVE_SKIP_COUNTER или slave-skip-errors. Со временем, две БД накопят некий запас неконсистентности, благодаря таким пропущенным конфликтам. Итак, что делать, если нужно восстановить полную консистентность, или в случае, когда ошибка в репликации совсем критичная и не решается вышеописанными способами? Единственный выход — полная синхронизация двух БД «с нуля» и сброс состояния репликации.

Порядок действий таков - на мастере:

mysql-master> STOP SLAVE;
mysql-master> RESET MASTER;
mysql-master> FLUSH TABLES WITH READ LOCK;
mysql-master> SHOW MASTER STATUS;
+------------+----------+--------------------+------------------------+---------------+--------+--------------+---------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB  |  bin.000002 |     654 |                    | mysql            |
+------------+----------+--------------------+------------------------+---------------+--------+--------------+----------------+

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

Далее, не закрывая соединения с мастер базой (т.к. это снимет режим только чтения), делаем дамп всех необходимых данных:

 [user@master ~]$ mysqldump -uroot -p --all-database > ./mysqldump.sql 

Теперь можно снять режим чтения командой

mysql-master> UNLOCK TABLES;

Следующий шаг — скопировать дамп на слейв сервер и выполнить там:

mysql-slave> STOP SLAVE; 

Импортируем загруженный с мастер сервера дамп:

[user@slave1 ~]$ mysql -uroot -p < mysqldump.sql span="">

Далее на слейве удаляем все журналы от мастера и начинаем репликацию с момента, когда на мастере был сделан дамп:

mysql-slave> RESET SLAVE;
mysql-slave> CHANGE MASTER TO MASTER_LOG_FILE = [записанный ранее File], MASTER_LOG_POS =[записанный ранее Position];
mysql-slave> START SLAVE;
mysql-slave> SHOW SLAVE STATUS;

Убеждаемся в том, что параметры Slave_IO_Running и Slave_SQL_Running установлены в Yes. В случае, если у вас мастер-мастер репликация, продолжаем на слейв сервере:

mysql-slave> STOP SLAVE;
mysql-slave> FLUSH TABLES WITH READ LOCK;
mysql-slave> RESET MASTER;
mysql-slave> SHOW MASTER STATUS;+------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000006 |       12 |              | mysql            |
+------------+----------+--------------+------------------+

Снова запоминаем File и Position и на мастер сервере делаем:

mysql-master> CHANGE MASTER TO MASTER_LOG_FILE=[записанный File], MASTER_LOG_POS=[записанный Position];
mysql-master> START SLAVE;
mysql-master> SHOW SLAVE STATUS G;

Убеждаемся в том, что параметры Slave_IO_Running и Slave_SQL_Running установлены в Yes.

На слейве снимаем лок и запускаем репликацию:

mysql-slave> UNLOCK TABLES;
mysql-slave> START SLAVE;

Подробнее о настройке мастер-мастер репликации с нуля можно прочитать

— See more at: http://www.sover.pro/blogs/54-sinhronizaciya-mysql-baz-posle-oshibki-replikacii.html#sthash.uf9cRY0w.dpu

 
Ошибка «Error 1292: Incorrect date / datetime value» при синхронизации
Суть проблемы: чаще всего такая ошибка возникает при попытке синхронизации с таблицей, в которой есть запись со значением '0000-00-00' или '0000-00-00 00:00:00' в полях типа DATE или DATETIME соответственно. В некоторых случаях настройки MySQL позволяют создавать такие записи, но не позволяют редактировать схему таблицы.

Решение: вообще, при синхронизации или экспорте данных MySQL Workbench добавляет специальные запросы, как бы оборачивая основной SQL код:

Вчитавшись в этот код, начинаешь думать, что программа пытается решить эту проблему самостоятельно. Парадокс в том, что для решения проблемы эти строки нужно удалить:

# Удалить эту строку из начала SQL кода
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
...

# Удалить эту строку из конца SQL кода

SET SQL_MODE=@OLD_SQL_MODE;

Ошибка «Error 1005: Can't create table '...' (errno: 150)» при синхронизации
Суть проблемы: обычно эта ошибка касается неправильной настройки внешних ключей (вкладка «Foreign Keys» в настройках таблиц). У меня она возникала в том случае, если я делал ключом поле с меткой NOT NULL, а в поведении внешнего ключа указывал SET NULL — это абсурд, ведь InnoDB не сможет установить значение NULL в поле, где такое значение запрещено.

Решение: внимательно следим за настройкой поведения внешних ключей. Если необходимо поведение SET NULL, у поля-ключа в дочерней таблице не должен стоять флаг NOT NULL.
Ошибка «Field ... can not be null» при выгрузке стартовых данных
Суть проблемы: независимо от настроек таблицы, все поля в «Inserts» имеют по умолчанию значение NULL, даже если такое значение не разрешено для данного поля. Соответственно, при выгрузке на сервер может возникнуть ошибка.

Решение: при добавлении стартовых данных следим за тем, чтобы значение NULL оставалось лишь в тех полях, где это разрешено. Если нужно сделать поле пустой строкой, делаем финт ушами: ставим в него курсор, нажимаем пробел, затем стираем его (во всяком случае, я не придумал ничего получше на такой случай :)).
Ошибка Сan't connect to MySQL server on ... (10061) при подключении
Суть проблемы: говорят, что может быть несколько причин. Я встечал такую ошибку в случае, если в файле my.cnf была установлена настройка «skip-networking» — по сути она не даёт MySQL работать с сетью.

Решение: закомментировать данную опцию:

# skip-networking