CODEWITHSUNDEEP
mysql
user3521737
user3521737

Reputation: 281

Replicate a table from one phpMyAdmin server to another

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.

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

Upvotes: 3

Views: 990

Answers (2)

Karolis
Karolis

Reputation: 2618

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.

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):

    $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.

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 replication docs: 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.

Upvotes: 2

ddalu5
ddalu5

Reputation: 401

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

Upvotes: 2

Related Questions