MySQL Master-Master

Основная дока этой заметки:
Опыт эксплуатации MySQL Master-Master — как пережить аварию датацентра

Плюс о репликации у меня есть
http://dragonflybsd.blogspot.ru/search/label/mysql



Нужно понимать, что настоящий master-master в MySQL не сделать; то, что есть, будет с рядом оговорок.
Нет настоящей синхронной реплиации, полусинхронная появилась только в 5.6
Нет встроенных методов проверки целостности
В чистом виде в режиме мастер-мастер можно связать только 2 сервера; если надо больше, делается кольцо 1 -> 2 -> 3 -> 1, которое может по разным причинам порваться
Нет штатных средств уведомления о проблемах с репликацией
Нет даже нужной команды load data from master, которая сняла бы много головной боли. Точнее, раньше такая команда была, но применима была только для MyISAM и уже удалена из современных версий.
Чтобы при внезапной перезагрузке не ломалась репликация, необходимо включать такие опции как flush_log_at_trx_commit=1, sync_binlog=1, sync_relay_log=1, sync_relay_log_info=1, sync_master_info=1, что существенно скажется на производительности.

Поэтому и тут хорошо применима формула, которая не помню где была взята: есть 3 параметра: скорость, консистентность (в том числе среза), устойчивость к сбоям отдельного узла (устойчивость к разделению). Выберите любые 2.
Например, гарантия того, что с любого узла в любой момент времени всегда снимаются одни и те же данные, даже когда часть узлов выпадало, означает очень низкую скорость и отзывчивость всей системы. Потому что такая система требует наличия синхронной записи на все узлы, и пока ВСЕ узлы не подтвердили получение информации, система не может продолжить работу.
А в системе, где важны скорость и устойчивость к сбоям, выпадение одного узла на скорости не скажется, но с разных узлом можно получить данные разной степени "свежести". Для каких-то систем это вполне допустимо, но например для банковской сферы это абсолютно неприемлемо.

Есть ещё подобные системы: ACID (atomicity, consistency, isolation, durability — «атомарность, непротиворечивость, изолированность, долговечность»), CAP, но они не отражают скорости работы всей системы в целом, ведь если у нас производительность 10 записей в секунду и не масштабируется, кому она нужна?


Ручная проверка целостности:
можно взять из набора percona-toolkit

• pt-table-checksum
• pt-table-sync

Есть вариант через сравнение дампов, но проблема с локом базы на время бэкапа, большой сложностью поймать одинаковый master_log_pos без перевода баз в режим read lock и временным отказом в обслуживании, зато потом можно сверять дампы, формировать различные строки, сделать набор исправлений под каждый сервер.

Можно воспользоваться скриптиком (тиснуто с первой ссылки)

#!/bin/bash

DATABASES=`mysql -u root -p${MYSQL_ROOT_PASSWORD} -h $SHARD_L -B -N -e"SHOW DATABASES" | grep -vE '(^binlogs$)|(^performance_schema$)|(^test.*$)|(^information_schema$)'`

for DB in $DATABASES; do

TABLES=`mysql -u root -p${MYSQL_ROOT_PASSWORD} -h $SHARD_L -B -N -D $DB -e"SHOW TABLES"

    for TABLE in $TABLES; do

        CS_L=`mysql -u root -p${MYSQL_ROOT_PASSWORD} -h $SHARD_L -D $DB -B -N -e"CHECKSUM TABLE $TABLE" | awk '{print $2}'`
        CS_R=`mysql -u root -p${MYSQL_ROOT_PASSWORD} -h $SHARD_R -D $DB -B -N -e"CHECKSUM TABLE $TABLE" | awk '{print $2}'`

        if [ "$CS_L" != "$CS_R" ]; then
            echo "${DB}-${TABLE} : DIFF"

            mysql -u root -p${MYSQL_ROOT_PASSWORD} -h $SHARD_L -D $DB -B -N -e"SELECT * FROM $TABLE" > /var/tmp_data/table_diff_${SHARD_L}.tmp
            mysql -u root -p${MYSQL_ROOT_PASSWORD} -h $SHARD_R -D $DB -B -N -e"SELECT * FROM $TABLE" > /var/tmp_data/table_diff_${SHARD_R}.tmp
            diff -u /var/tmp_data/table_diff_${SHARD_L}.tmp /var/tmp_data/table_diff_${SHARD_R}.tmp
            rm -f /var/tmp_data/table_diff_${SHARD_L}.tmp /var/tmp_data/table_diff_${SHARD_R}.tmp

        else
            echo "${DB}-${TABLE} : OK"
        fi

    done

done

Выполняться может долго, например на таблице в 90 млн строк на достаточно сильном сервере checksum table выполнялся около 7 минут, полностью забивая сервер вычислениями, в том числе диски. Значит, скорее всего остальные запросы будут выполняться гораздо медленнее и очень легко поймать отказ в обслуживании.

Проверку особенно больших таблиц надо начинать обязательно с show table status like '%BASE%' и смотреть Rows - если не совпадает количество записей, о какой схожести может идти речь?

Ещё замечание о master_log_pos

It is very important to understand the difference between IO Thread position in Master logs (Master_Log_File:Read_Master_Log_Pos) and SQL Thread position in Master logs (Relay_Master_Log_File:Exec_Master_Log_Pos).
When you’re using CHANGE MASTER TO to set start position for the slave you’re specifying position for SQL thread and so you should use Relay_Master_Log_File:Exec_Master_Log_Pos. Otherwise you’re going to ruin your replication.
It does not help SHOW MASTER STATUS has Master_Log_File value and CHANGE MASTER TO accepts Master_Log_File parameter, while you actually want to use Relay_Master_Log_File instead. This naming was left from pre MySQL 4.0 era and it still causes confusion so many years later.

http://www.mysqlperformanceblog.com/2008/07/07/how-show-slave-status-relates-to-change-master-to/