Назад | Перейти на главную страницу

Как синхронизировать две таблицы MySQL (по запросу или через cron)

Я видел Cron синхронизация таблиц mysql . Но я не могу использовать репликацию. Я использовал percona-toolkit и это сработало отлично. Я мог запустить команду синхронизации по запросу или просто запустить ее через cron. Он будет сравнивать контрольные суммы в двух таблицах и выполнять вставки, обновления, удаления и т. Д. Однако Perl (DBD :: mysql) и MySQL имеют некоторые несовместимости на новом сервере, на котором я собираюсь запустить это, и я не могу использовать pt-table- синхронизировать. Есть ли подобное решение, использующее что-то другое, кроме Perl / DBD?

Изменить: (подробнее, ясность)

И исходная, и целевая таблицы являются действующими таблицами и постоянно используются. Следовательно, решение с несуществующей таблицей (например, выполняется DROP TABLE) не будет приемлемым.
Невозможно использовать репликацию или любые подобные модификации самого сервера. Решение должно работать на стороне клиента.
В этом конкретном сценарии обе таблицы находятся на одном сервере, но в разных БД (например, db1.tbl db2.tbl). Однако решение, которое не полагается на этот факт, определенно будет бонусом.
Сетевая задержка вряд ли будет проблемой. В этом случае сценарий запускается на сервере в том же центре обработки данных.
Невозможно использовать Perl (несовместимость между Perl и MySQL - 64 бит против 32 бит)

использовать mysqldump --opt <database> <tablename> чтобы создать дамп вашей таблицы и передать его на новый сервер. Поскольку у вас, по-видимому, есть доступ к удаленной базе данных через TCP / IP, вы просто можете использовать

mysqldump --opt --user=<youruser> --password=<yourpassword> -host <yourhost> \
<yourDB> <yourtable> | mysql -u <newserveruser> -p<password>

чтобы подключиться к удаленной базе данных, сбросьте ее и загрузите вывод на новый сервер.

Если у вас не было прямого доступа TCP / IP к удаленной базе данных, вы все равно могли бы сделать то же самое, туннелируя данные через SSH после настройки. аутентификация с открытым ключом:

ssh -C -l <remoteuser> <remoteserver> \
'mysqldump --opt --user=<youruser> --password=<yourpassword> <yourDB> <yourtable>' \
| mysql -u <newserveruser> -p<password>

Увидеть документация к mysqldump и страница руководства для SSH Больше подробностей.

Если вам нужна более эффективная пропускная способность, рассмотрите возможность создания дампа с помощью mysqldump, сохраняя его на исходном сервере и используя rsync для копирования / обновления копии на целевом сервере перед импортом. Так как rsync будет создавать скользящие контрольные суммы в исходном и конечном файлах, вероятно, не потребуется передавать большую часть содержимого дампа при последующих запусках.

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

Если вы просто не можете отбросить таблицу в место назначения по каким-либо причинам, вы можете вставить выгруженные данные в новую таблицу (быстрый и грязный, но несколько небезопасный подход будет перенаправлять mysqldump вывод на sed -e 's/mytable/newtable/g' перед дальнейшим подключением к mysql), а затем запустите цикл UPDATE / DELETE / INSERT с парой таких JOIN (непроверенный, сделайте проверку работоспособности):

/* set write lock on the table so it cannot be read while updating */
LOCK TABLES mytable WRITE;

/* update all rows which are present in mytable and newtable */
UPDATE mytable AS M LEFT JOIN newtable AS N ON M.primarykey = N.primarykey 
SET M.column1=N.column1, M.column2=N.column2 [...]
WHERE N.primarykey Is Not NULL;

/* delete all rows from mytable which are no longer present in newtable */
DELETE M FROM mytable AS M LEFT JOIN newtable AS N on M.primarykey = N.primarykey 
WHERE N.primarykey Is NULL;

/* insert new rows from newtable */
INSERT INTO mytable (primarykey, column1, column2, [...]) 
SELECT (N.primarykey, N.column1, N.column2, [...]) FROM mytable AS M 
RIGHT JOIN newtable AS N ON M.primarykey=N.primarykey WHERE M.primarykey Is NULL

/* release lock */
UNLOCK TABLES;

Примечание: Конечно, данные вашей базы данных будут несовместимы, пока вы вставляете / обновляете ее данные, но пока вы не используете транзакции (недоступно для таблиц MyISAM), это будет иметь место независимо от того, что вы делаете - удаление и воссоздание table создаст временные несоответствия, как и цикл обновления / удаления / вставки. Это связано с самой природой неатомарного дизайна MyISAM без транзакций.

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

Другой вариант - использовать REPLACE INTO вместо удаления таблицы, как показано на http://codeinthehole.com/writing/how-to-sync-a-mysql-table-between-two-remote-databases/

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

Вы пробовали использовать триггеры?

DELIMITER $$
CREATE TRIGGER sync_table1_insert
AFTER INSERT ON `table1` FOR EACH ROW
BEGIN
    INSERT INTO table2 (id, value) VALUES (NEW.id, NEW.value);
END;
$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER sync_table1_update
AFTER UPDATE ON `table1` FOR EACH ROW
BEGIN
    UPDATE table2 SET value = NEW.value WHERE id = NEW.id;
END;
$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER sync_table1_delete
AFTER DELETE ON `table1` FOR EACH ROW
BEGIN
    DELETE FROM table2 WHERE id = OLD.id;
END;
$$
DELIMITER ;