将一个表从一个phpMyAdmin服务器复制到另一个

My boss is having me implement a system for our live site that copies a table from one of our databases to its own. It's supposed to do this once a day; the database it's supposed to copy from updates every thirty minutes. What I'm confused about is how to make this work. I intend to use PHP for this and the initial suggestion was to use AJAX, but I'm not sure I see the point of the latter when the page we're using isn't going to be linked to by any of our other pages and it's not going to display anything.

我的老板让我为我们的live站点实现一个系统，该系统将我们的一个数据库中的一个表复制到它自己的数据库中。它应该每天这么做一次;它应该每30分钟从更新中复制一个数据库。我搞不懂的是如何使它工作。我打算用PHP来实现这一点，最初的建议是使用AJAX，但我不确定我是否理解后者的意义，因为我们使用的页面不会被其他页面链接，也不会显示任何内容。

How can I copy this table, much less get the two databases to communicate?

如何复制这个表，更不用说让两个数据库进行通信?

2 个解决方案

#1

Why don't you use MySQL replication no need for PHP or any programming only configuration.

为什么不使用MySQL复制不需要PHP或任何编程配置。

#2

You are not going to use AJAX for this, because AJAX is a client <-> server communication technology, and you need the data copied between 2 servers.

您不会为此使用AJAX，因为AJAX是一个客户机<->服务器通信技术，您需要在两个服务器之间复制数据。

Depending on your database setup, you could do it in PHP, even though this would be more of a hack than a real solution. In PHP you could do something like this (treat it as pseudo code):

根据您的数据库设置，您可以在PHP中进行此操作，尽管这更像是一种技巧，而不是真正的解决方案。在PHP中，您可以这样做(将其视为伪代码):

    $srcDb  = new mysqli('localhost', 'user', 'pass', 'source_database_name');
    $destDb = new mysqli('localhost', 'user', 'pass', 'dest_database_name');


    $result = $srcDb->query("SELECT * FROM TABLE");

    $destDb->begin_transaction();
    $destDb->query('DELETE FROM TABLE');

    while ($row = $result->fetch_assoc()) {
        // format insert query from $row...
        $destDb->query("INSERT INTO TABLE (...) VALUES (...)");
    }

    $destDb->commit();

Depending on the size of the table, you will have a time when the table on DEST server will be empty or have incomplete rows. To avoid this, you might use mysql transactions, but with a large number of rows, committing such transaction would take a long time. This solution is generally very inefficient.

根据表的大小，您将有一段时间，在DEST服务器上的表将是空的或有不完整的行。为了避免这种情况，您可以使用mysql事务，但是要使用大量的行，提交这样的事务需要很长时间。这个解决方案通常效率很低。

A proper way to do this would be to setup source server as MySQL master, and dest server as MySQL slave. This way the updates on the slave server would be almost instant.

正确的方法是将源服务器设置为MySQL主服务器，将dest服务器设置为MySQL从服务器。这样，从服务器上的更新几乎是即时的。

Mysql replication docs: https://dev.mysql.com/doc/refman/5.7/en/replication.html

Mysql复制文档:https://dev.mysql.com/doc/refman/5.7/en/replication.html

To only replicate one table instead of the whole database, take a look at --replicate-do-table=name option.

要复制一个表而不是整个数据库，请查看——replicate-do-table=name选项。

#1