В процессе длительной работы с двумя и более 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 код:
Вчитавшись в этот код, начинаешь думать, что программа пытается решить эту проблему самостоятельно. Парадокс в том, что для решения проблемы эти строки нужно удалить:
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
...
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 работать с сетью.
Решение: закомментировать данную опцию:
