Using PHP, how to copy selected records from one mysql database to another on different hosts (thus different connections) – PHP – SitePoint Forums

using PHP, how to copy selected records from one mysql database to another on different hosts (so different connections)?

  • I coded separate mysqli connections
  • The databases both have the same structure

I use this new script to select some records from sourceDB.table and copy them to targetDB.table if their record number does not already exist. What puzzled me were the separate connections.

Something like this (or whatever you suggest), but with separate mysqli connection handling:

INSERT INTO targetDB.table
SELECT sourceDB.table.*
FROM
sourceDB.table
LEFT JOIN targetDB.table USING(id)
WHERE
targetDB.table.id IS NULL


No, this is not possible with two physically separate databases. You have to make a selection on the source dB, retrieve the data and do an insert into the other dB.



1 like

So get it from the source and then iterate through it to perform the inserts into the target.

In fact it is possible. While I’m concerned, there’s a bigger issue at play as to why this is being done.

OP, can you please explain what you have going on. Are you just trying to synchronize the two databases? If so, how did you get to such a place?

Is this a one-time exercise?



1 like

I’m always wary of learning something new. So please tell me how it works…



1 like

We have a production database with millions of records. I’m building a dev/training database with a subset of these records. I’m going to extract data that starts with a record, then optionally collect and copy all related records to other tables for that starting record. (Imagine selecting a building on a college campus and then needing to get all the classes in that building, then all the students enrolled in those classes, then the attendance records for those students and their grades.)

This is not a one-time thing, the specs for this task require me to fetch records to populate the new database as requested. But it will never be all the discs of the production, just those selected as a sample.

I’m “Jumping the Shark” and showing a MySQLI solution.

Disclaimer: There is a better way to go with prepared statements and transactions.



1 like

You just do what I explained with two queries. One to retrieve and one to insert.

The OP asked for a solution using a single query and it’s not possible…



1 like

@Thallius, I misread your answer. While OP didn’t ask for a single query, his example is a single query. :smile:

However, it is possible to use a single query using FEDERATED TABLES.

The FEDERATED The storage engine allows you to access data from a remote MySQL database without using replication or cluster technology. Ask a local FEDERATED table automatically extracts data from remote (federated) tables.

https://dev.mysql.com/doc/refman/8.0/en/federated-storage-engine.html

Little tutorial on the configuration



1 like

OP, since you’re going to be doing this on a regular basis and you’re dealing with millions of records on the production server, I recommend setting up the FEDERATED tables to copy your data from the remote production server to the training database. This would be the cleanest setup and you can use a single query to do the job.

IMPORTANT: Do not modify the data in the FEDERATED tables in your training database, otherwise you will make these changes in the production database. FED tables are just remote shortcuts to the production database.

Copy the data locally like this…

INSERT IGNORE INTO trainingDB.usersTable (username, email)
(SELECT username, email FROM `federated_table` WHERE username="xx");

Here is an example of SQL that I used to test the code. (the FED CREATE table must match the structure of the remote table)


CREATE SERVER federatedTablelink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'remoteUser', HOST 'remoteDbIP', DATABASE 'yourRemoteDbNAme', Password 'remoteDbPassword');


CREATE TABLE federated_table (
    username   VARCHAR(32) NOT NULL DEFAULT '',
    email  VARCHAR(20) NOT NULL DEFAULT '',
    INDEX username (username)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='federatedTablelink/users';



1 like

I would never suggest using production data for testing purposes. Not even read-only…



1 like

The op does not do tests. They train from a separate non-production database, using a copy of a subset of the production data. I was initially concerned about this process when I posted until I spoke to the op privately.



2 likes

Maria H. Underwood