Обзор средств синхронизации баз данных MySQL

[DarkByte]

Через SSH можно пробросить порт, например, используя PuTTY.

[WolandV]

Спасибо, отредактировал пост. Поставил бы плюсик, если бы мог.

[Archy]

Посмотрите SymmetricDS, для многих он стал стандартом де-факто. А для ручной синхронизации удалённых БД стоит смотреть в сторону SQLyog, там и туннели и визуальное сопоставление и копирование в пару кликов.

[zoxa]

Хотелось бы добавить немного по MAATKIT. Как вы правильно заметили MAATKIT теперь полностью мигрировал под percona и теперь percona занимается его развитием, вот ссылка на список www.percona.com/doc/percona-toolkit/

Нужно добавть одну особенность по mk-table-sync/ pt-table-sync: данная функия работает в комплекте с mt-table-checksum / pt-table-checksum:
1. скрипт *-table-checksum находит разницу в таблицах,
2. скрипт *-table-sync синхронизирует данные наденные *-table-checksum

Вот пример скрипта который я использовал для себя (это только отрывок, без всех всех опций) для проверки MASTER-MASTER сихронизации

# Проверка таблиц базе $DB_TO_CHECK и записать результат в таблицу $CHECKSUM
# $CHECKSUM=replicate_db.checksum (обазятельно должна участовать в репликации)
pt-table-checksum -q \
    --replicate $CHECKSUM --create-replicate-table --databases $DB_TO_CHECK \
    h=$MASTER_HOST,u=$USR,p=$PWD 2>&1

# используя флаг -q мы переключаем скрипт в тихий режим и тогда скрирт веренте return code 0 если все ок и 
# больше нуля если pt-table-checksum нашел разницу в таблицах
if [ $? -gt 0 ]; then
    # если pt-table-checksum вернул не нулевой результать запускаем pt-table-sycn чтобы синхронизировать 
    # chunks с ошибками
    pt-table-sync --execute --sync-to-master --verbose \
        --replicate $CHECKSUM \
        --databases $DB_TO_CHECK \        
        h=$SLAVE_HOST,u=$USR,p=$PWD 2>&1
fi  

Запустив вот этот запрос на сервере 2 можно можно получить список кусочков (chunk) которые содержат ошибки \ не синхронизированны

SELECT 
    db, tbl, count(chunk) AS chunks
FROM replicate_db.checksum 
WHERE this_crc <> master_crc
GROUP BY db,tbl
ORDER BY db,tbl;

[zoxa]

добавление. Percona Toolkit обновился \ исправили ошибки на выходных: www.mysqlperformanceblog.com/2013/05/06/percona-toolkit-2-2-2-has-been-released/

[truezemez]

Простой способ сравнить структуры 2-х бд:

mysqldump --no-data --skip-comments -u dbuser -p dbname > db1.sql
mysqldump --no-data --skip-comments -u dbuser -p dbname > db2.sql
diff db1.sql db2.sql

[baltazorbest]

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

[truezemez]

По идее, не должен — мы же используем ключ --no-data.

[script88]

Ну сравнить структуру мы сравнили, а patch и revert делать в ручную?!

[truezemez]

Что посоветуете?

[script88]

schemasync, автор про него писал маленькая и быстрая утилита написанная на python

[truezemez]

Спасибо, ознакомимся.

[svetasmirnova]

Ещё такая тулза есть: dev.mysql.com/doc/workbench/en/mysqldiff.html

[ekho]

У неё есть одна проблема — она показывает только ALTER'ы. Если добавилась/убавилась таблица/вьюшка, то тулза выкинет warning и на этом посчитает свою работу выполненной.
Кроме того, предлагает переименовать одну из двух сравниваемых БД.

Вот пример:

mysqldbcompare --skip-data-check --skip-row-count --run-all-tests --server1=root:***@192.168.1.133 --changes-for=server2 --difftype=sql compare1:compare2

выхлоп:

# server1 on 192.168.1.133: ... connected.
# Checking databases compare1 on server1 and compare2 on server2
#

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `compare2`
+++ `compare1`
@@ -1 +1 @@
-CREATE DATABASE `compare2` /*!40100 DEFAULT CHARACTER SET cp1251 */
+CREATE DATABASE `compare1` /*!40100 DEFAULT CHARACTER SET cp1251 */

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `compare2`
+++ `compare1`
@@ -1 +1 @@
-CREATE DATABASE `compare2` /*!40100 DEFAULT CHARACTER SET cp1251 */
+CREATE DATABASE `compare1` /*!40100 DEFAULT CHARACTER SET cp1251 */

# WARNING: Objects in server1.compare1 but not in server1.compare2:
#        TABLE: table2
#         VIEW: view1
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     table1                                  FAIL    SKIP    SKIP    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE `compare2`.`table1` 
  CHANGE COLUMN data data varchar(46) NULL;



Databases are consistent given skip options specified.
#
# ...done

[svetasmirnova]

О, спасибо!

> Кроме того, предлагает переименовать одну из двух сравниваемых БД.

Это как посмотреть: вдруг вы её хотите переименовать? =)

А что хочется вместо:

# WARNING: Objects in server1.compare1 but not in server1.compare2:
# TABLE: table2
# VIEW: view1

увидеть?

[ekho]

Ну вообще при сравнении двух БД вместо ворнингов об отсутствующих таблицах/вьюшках хочется увидеть CREATE/DROP TABLE/VIEW. По-моему вполне ожидаемо, не?

[svetasmirnova]

Ну вот иногда глаз замыливается и очевидного-то и не замечаешь =)

Можете открыть feature request на bugs.mysql.com?

[zoxa]

А как вы решаете вопросы в которой будет следующее:

db1.table1
— index key1
— index key2

db2.table1
— index key2
— index key1

т.е. ключи идут в разном порядке?

[truezemez]

Никак. Будут в разном порядке.

Я в комментарии указал "простой способ сравнить структуры 2-х бд". Не супер-мега-всех-заткнул-за-пояс-скрипт, а именно простой способ. Свалились на Вас с неба две базы, а Вам стало интересно есть ли между ними разница в структуре. Не регулярно сверять базы и синхронизировать, а просто сравнить. Раз в полгода.

[zoxa]

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

[svetasmirnova]

Обратите ещё внимание на недавно появившиеся MySQL Workbench utilities.

Например, mysqlserverclone (http://dev.mysql.com/doc/workbench/en/mysqlserverclone.html) клонирует базу, mysqldiff (http://dev.mysql.com/doc/workbench/en/mysqldiff.html) сравнивает структуру двух баз, mysqldbcompare (http://dev.mysql.com/doc/workbench/en/mysqldbcompare.html) — помимо структуры, сравнивает и данные, mysqlreplicate (http://dev.mysql.com/doc/workbench/en/mysqlreplicate.html) — разворачивает репликацию.

[script88]

На самом деле, GUI приложения, с большими объемами не справляются. Из всех утилит которые я попробовал (без gui) на средних объемах БД 3-4 ГБ sсhemasync и maatkit справились достаточно быстро.
MySQL Workbench очень тормознутый инструмент который нуждается в большом напильнике

[svetasmirnova]

MySQL Workbench Utilities, несмотря на название, — это набор command line tools, написаных на Python, имеющий к MySQL Workbench только то отношение, что они поставляются вместе с ним и могут быть вызваны непосредственно из него. А могут использоваться и самостоятельно. Вот здесь отдельно их можно загрузить: dev.mysql.com/downloads/tools/utilities/

[script88]

Спасибо, на досуге попробую.

[svetasmirnova]

> К сожалению, почти все упомянутые мной средства обладают существенным недостатком (и я буду рад, если ошибаюсь, но опровержений моим словам я не нашел) — все они требуют удаленного доступа к БД по tpc/ip. Учитывая, что нормальной практикой является разрешение доступа к БД только с локального хоста и запрет удаленного доступа, добиться синхронизации лишь средствами упомянутых утилит не удастся.

Я не думаю, что это недостаток, потому что иначе же нельзя к удалённой машине присоединиться.

Помимо предложенной переброски портов можно сделать ещё аккаунт, предназначенный специально для процедуры синхронизации и жёстко прописать хост, с которого он может соединяться плюс добавить опцию REQUIRE SSL.

[synergy]

Немного не по теме, но для ручного сравнения баз, лучше DBForge for MySQL еще ничего не придумали. И умеет работать через SSH- и HTTP-туннели. Если разработчики DBForge читают Хабр, то низкий им поклон!