同步 2 个不同数据库的表 - MySQL

2022-01-22 00:00:00 sync mysql replication

我在数据库表中有一个包含某些医疗信息的表.我爬行&每天解析它们并将其存储在我本地数据库的那个表中.

I've a table with certain medical information in a database table. I crawl & parse them daily and store it in that table of my local database.

假设最初有 1500 条记录，今天在我的本地机器上又添加了 100 条记录.

Suppose there were 1500 records initially, today 100 more records are added on my local machine.

现在，我有一个服务器，我需要在其中推送这些记录(因此数据库不同).我昨天复制了数据库.因此，服务器数据库表现在有 1500 个条目.如何将新的 100 个条目同步到实时应用程序?

Now, I've a server in which I need to push those records (so the database is different). I copied the database yesterday. So, the server database table has 1500 entries now. How can I sync the new 100 entries to the live application?

请注意，我无法从本地计算机中删除旧条目，否则它将被一次又一次地抓取.

Please note that I cannot delete old entries from my local machine else it will be crawled again and again.

推荐答案

您可能需要使用 'SELECT ... INTO OUTFILE' 和 'LOAD DATA INFILE INTO TABLE' 命令.

You may want to use 'SELECT ... INTO OUTFILE' and 'LOAD DATA INFILE INTO TABLE' commands.

详细说明...

给定表结构:

CREATE TABLE my_local_table (
    id int NOT NULL auto_increment PRIMARY KEY,
    data varchar(20),
    created_on datetime);

CREATE TABLE server_table (
    id int NOT NULL auto_increment PRIMARY KEY,
    data varchar(20),
    created_on datetime,
    local_id int);

还有一些虚假数据:

INSERT INTO my_local_table (data, created_on) VALUES ('test', now()), ('test2', now());

您将使用以下命令:

SELECT id, data, created_on 
    FROM my_local_table
    WHERE created_on >= '2011-08-18'
    INTO OUTFILE '/tmp/t.txt';

-- (and on the server)
LOAD DATA LOCAL INFILE '/tmp/t.txt'
    INTO TABLE server_table
    (local_id, data, created_on);

要自动执行这两个操作，您可以使用 bash 脚本/批处理文件调用 mysql，首先使用第一条语句连接到本地服务器，然后执行第二条语句连接到远程服务器.

To automate the two, you can use a bash script / batch file calling mysql connecting first to the local server using the first statement, then to the remote server executing the second.

mysql -e 'SELECT....';
mysql -h remote_server -e 'LOAD DATA...';

相关文章