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

Использование 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 с парой соединений (например, без проверки, выполните проверку работоспособности):

/* 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), это будет иметь место независимо от того, что вы делаете - удаление и воссоздание таблицы приведет к временным несоответствиям, так же как и цикл обновления / удаления / вставки. Это связано с самой природой неатомарного дизайна транзакций MyISAM.

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

Другим вариантом было бы использовать 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 ;