Репликация MySQL Master-Master и проблема с столбцом Auto-Increment

ПРИМЕЧАНИЕ. Я нашел ответ и поместил его вверху. Ниже ответа приведены некоторые другие разглагольствования (мой первоначальный ответ), которые все еще могут иметь некоторую ценность для объяснения этого.

Поскольку ваш запрос удваивает количество строк, ваш оператор INSERT INTO t1(c1,d1) SELECT LPAD('', 50, MD5( RAND() ) ), DATE_ADD( CURDATE(), INTERVAL FLOOR( RAND() * 365 ) DAY ) FROM t1; может вставлять разное количество строк на сервер 1 и сервер 2. Все операторы, использующие столбец с автоматическим приращением, отправляют свой INSERT_ID вместе с репликацией, и это значение не будет истинным на сервере. 2, если там также был запущен оператор a.

Давайте посмотрим на пример. Я сделаю stop slave, чтобы имитировать длительный запрос или плохую сеть.

1. Создайте две базы данных и настройте репликацию master-master
2. Создайте таблицу и вставьте исходную строку
3. Остановить репликацию на сервере 2
4. Запустите оператор, который удваивает количество строк пару раз на сервере 1. 2 достаточно, но я сделал 3.

5. Отметьте show binlog events (предупреждение, не делайте этого со старой базой данных, это займет вечность). Это то, что я вижу.

   Запрос | НАЧАЛО
   Intvar | INSERT_ID=3
   Запрос | используйте test; ВСТАВИТЬ В t1(c1,d1) SELECT ...
   Запрос | COMMIT
   Запрос | НАЧАЛО
   Intvar | INSERT_ID=5
   Запрос | использовать test; ВСТАВИТЬ В t1(c1,d1) SELECT ...
   Запрос | COMMIT
   Запрос | НАЧАЛО
   Intvar | INSERT_ID=9
   Запрос | использовать test; ВСТАВИТЬ В t1(c1,d1) SELECT ... Запрос | СОВЕРШИТЬ

6. Обратите внимание, что каждый раз, когда я запускаю дублирование, INSERT_ID изменяется соответствующим образом. Во второй вставке это 5 означает, что первая вставка вставила 1 строку (помните, приращение равно 2). В третьей вставке INSERT_ID равен 9, что означает, что вторая вставка вставила 2 строки. Все это имеет смысл. Давай продолжим

7. На сервере 2 сделайте дублирование один раз, пока не запускайте репликацию. Выполнение select * from t1 теперь правильно показывает две строки с идентификаторами 1 и 2.

8. Теперь снова запустите подчиненное устройство и запустите SHOW SLAVE STATUS \G. Он остановился с повторяющимся идентификатором 5. При повторном выборе всех значений из t1 отображаются четыре строки. Первый был начальным. Второе — это то, что мы сделали на сервере 2, а два последних раза с идентификаторами 3 и 5 были из того первого оператора на сервере 1, который добавил только 1 строку.

9. Следующая часть репликации это

   Запрос | НАЧАЛО
   Intvar | INSERT_ID=5
   Запрос | использовать test; ВСТАВИТЬ В t1(c1,d1) SELECT ...
   Запрос | СОВЕРШИТЬ

10. На сервере 1 INSERT_ID был равен 5, когда это произошло, и это то, что будет использовать репликация, однако на сервере 2 у нас уже есть идентификатор 5, потому что мы продублировали строки еще раз, прежде чем получить это. Таким образом, репликация прерывается.

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

Тем не менее, для этого конкретного случая есть простое решение, если вам нужно делать такие вещи.

1. Добавьте server_id к данным и создайте подобную таблицу

   CREATE TABLE t1 ( id int(11) NOT NULL AUTO_INCREMENT, server_id int(1) DEFAULT NULL, c1 varchar(50) DEFAULT NULL, d1 date DEFAULT '1970-01-01', PRIMARY KEY (id)) ENGINE=MyISAM AUTO_INCREMENT =4 НАБОР СИМВОЛ ПО УМОЛЧАНИЮ=latin1;

2. Подготовьте две строки, по одной для каждого идентификатора сервера.

   INSERT INTO t1(server_id, c1,d1) SELECT 1, LPAD('', 50, MD5(RAND())), DATE_ADD(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY) ; ВСТАВИТЬ В t1(server_id, c1,d1) SELECT 2, LPAD('', 50, MD5(RAND())), DATE_ADD(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY);

3. Для каждого дублирования просто учитывайте строки, созданные на вашем сервере.

   ВСТАВИТЬ В t1(server_id, c1,d1) SELECT server_id, LPAD('', 50, MD5(RAND())), DATE_ADD(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY) FROM t1 где server_id = 1;

НИЖЕ ИСХОДНЫЙ ОТВЕТ

Прежде всего, вы ошибаетесь, когда предполагаете, что у вас будет два набора идентификаторов в диапазоне 1, 3, 5, .. и 2, 4, 6 ... Независимо от того, на каком сервере выполняется оператор для значения, если Auto_increment всегда макс(идентификатор)+1. Таким образом, если вы сделаете две вставки на сервере 1, он получит нечетные значения 1 и 3. Если вы затем сделаете одну вставку на сервере 2, он получит четное значение 4 (4 — это следующее число больше 3, которое удовлетворяет auto_increment_offset + N × auto_increment_increment).

Вы можете увидеть значение Auto_increment, запустив show table status;

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

Тем не менее, вот как я это проверил (и получил такие же удивительные результаты).

1. Я создал новую пустую установку с двумя серверами и главным мастером make_replication_sandbox --master_master mysql-5.5.17-osx10.6-x86_64.tar.gz. Они оба запущены, и поэтому есть рабы. Они настраиваются автоматически, как и вы.
2. Затем я создал таблицу и вставил первую строку в соответствии с вашим вопросом. Auto_increment теперь равен 2 на обоих серверах и в таблице есть одна строка
3. Затем я запускаю while (true) do ./n1 test -e "INSERT INTO t1(c1,d1) SELECT LPAD('', 50, MD5( RAND() ) ), DATE_ADD( CURDATE(), INTERVAL FLOOR( RAND() * 365 ) DAY ) FROM t1;"; done; на обоих серверах одновременно (./n2 на другом).

И у меня есть теория.

Допустим, у вас есть 1000 строк в таблице, и вы инициируете одно и то же дублирование на обоих серверах одновременно. Другими словами, вы получите 4000 строк на обоих серверах, и все они будут одинаковыми.

Но что происходит, так это то, что вы дублируете строки в каждой базе данных, так что сервер 1 видит 2000 строк и сервер 2000 строк, но только первые 1000 одинаковы, остальные 1000 были сгенерированы по-разному на двух серверах.

Затем запускается репликация. Это репликация на основе операторов, поэтому выполняется один и тот же оператор, что означает, что на обоих серверах строки снова дублируются до 4000, и это правильный счет, но все же только 1000 из них одинаковы, остальные 3000 будут отличаться.

Пока на каждом сервере выполняется одинаковое количество запросов, это может сработать (дубликатов нет, но данные различаются), но если одному серверу удается выполнить два запроса до кэширования репликации, вы получаете утверждение в репликации, что на сервере 2 добавлено 1000 строк ( если раньше было 1000 строк), но на сервере 1 добавляется 4000 строк (потому что сервер 1 уже дважды удвоил 1000). Если следующий оператор добавил еще 2000 строк на сервер 2, а двоичный журнал содержит что-то вроде «первое автоматическое увеличение, используемое на сервере», вы получите коллизию.

Я знаю, что это абстрактно и странно, и даже сложнее написать, чем подумать об этом :)

Я надеюсь, что это поможет, и я надеюсь, что проблема в этом... Мастер-мастер - это сложно, и это определенно одна из вещей, которые я бы не стал делать в мастер-мастере.