☰

如何同步两个 MySQL 表（按需或通过 cron）

tag-icon • tag-icon mysql mysql-replication percona
如何同步两个 MySQL 表（按需或通过 cron）

我见过Cron 同步 mysql 表。但我不能使用复制。我曾经使用percona 工具包并且运行完美。我可以按需运行同步命令，也可以通过 cron 运行它。它会比较两个表的校验和并执行插入、更新、删除等操作。但是，在我将运行它的新服务器中，Perl(DBD::mysql) 和 MySQL 存在一些不兼容性，我无法使用 pt-table-sync。是否有使用 Perl/DBD 以外的其他程序的类似解决方案？

编辑：（更多细节，更清晰）

  1. 源表和目标表都是实时表，并且经常使用。因此，不存在表（例如执行了 DROP TABLE）的解决方案是不可接受的。
  2. 不能对服务器本身使用复制或任何此类修改。解决方案必须在客户端上工作。
  3. 在这个特定场景中，两个表都位于同一台服务器中，但位于不同的数据库中（例如 db1.tbl db2.tbl）。然而，不依赖这一事实的解决方案绝对是一个优势
  4. 网络延迟不太可能成为问题。在这种情况下，脚本在同一数据中心的服务器上运行。
  5. 无法使用 Perl（Perl 与 MySQL 不兼容 - 64 位与 32 位）

答案1

用来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 表不可用），无论您做什么都会出现这种情况 - 删除并重新创建表会造成暂时的不一致，就像执行更新/删除/插入循环一样。这是由于 MyISAM 的非原子无事务设计的本质。

答案2

听起来你想要类似的东西rubyrep它可以向左或向右同步，并且可以配置您想要同步的内容类型。但是我认为它是数据库级别而不是表级别。这可能是修改为基于表的同步的一个很好的起点。

另一个选择是使用 REPLACE INTO 而不是删除表，如下所示http://codeinthehole.com/writing/how-to-sync-a-mysql-table-between-two-remote-databases/

听起来您可能无法访问日志，或者我建议从二进制日志中获取命令。

答案3

您尝试过使用触发器吗？

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 ;

相关内容