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

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

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

I’m using this new script to select certain records from sourceDB.table and copy them to targetDB.table if their record number doesn’t already exist. What has me stumped is the separate connections.

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

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 separated databases. You need to do a select on source dB, fetch the data and the do an insert in the other dB.

1 Like

So retrieve it from source, and then iterate over that to do the inserts into target.

Thallius:

No this is not possible with two physically separated databases

Actually it is possible. Although I am concerned there is a bigger problem at play as to why this is being done.

OP, can you please expound on what you have going on. Are you just trying to sync the two DB’s? If so, how did you get to such a place?

Is this a one time exercise?

1 Like

benanamen:

Actually it is possible. Although

I always suspicious to learn something new. So please tell me how this works…

1 Like

We have a production database with millions of records. I am building a development/training database with a subset of those records. I’ll be pulling data that start with one record and then eventually collect and copy over all of the associated records in other tables for that starting record. (Imagine selecting on one building on a college campus and then needing to get all the classes held in that building and then all the students enrolled in those classes and then the attendance records for those students along with their grades.)

It isn’t a one-time thing, the specs for this task have me pulling records to populate the new database as requested. But it will never be all the records from production, just the selected ones as a sample.

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

Disclaimer: There is a better way to do this with Prepared Statements and Transactions.

How to run 1000s insert queries with mysqli?

The key to have your multiple inserts fast and reliable is to use **transactions** and a **prepared statement**.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$host1 = 'localhost';
$db1 = 'productionDB';
$user1 = 'xx';
$pass1 = 'xx';

$host2 = 'localhost';
$db2 = 'trainingDB';
$user2 = 'xx';
$pass2 = 'xx';

$conn1 = mysqli_connect($host1, $user1, $pass1);
mysqli_select_db($conn1, $db1);

$conn2 = mysqli_connect($host2, $user2, $pass2);
mysqli_select_db($conn2, $db2, );

$query = mysqli_query($conn1, "SELECT username, email from users WHERE username = 'someUsername'");

while ($row = mysqli_fetch_row($query)) {
mysqli_query($conn2, "INSERT IGNORE INTO users (username, email) VALUES ('{$row[0]}', '{$row[1]}')");
}

1 Like

You are just doing what I explained with two queries. One to fetch and one to insert.

The OP asked for a solution by using only one query and this is not possible…

1 Like

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

Still, It is possible to use a single query using FEDERATED TABLES.

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables.

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

Short Tutorial on Setup

How to Link Tables with MySQL (FEDERATED Tables) – Ideas that came from space

Linking Tables is a normal Feature in Oracle DB or MS-SQL. It is also (similar) possible with MySQL! The solution is a Plugin called “FEDERATED”.

Est. reading time: 2 minutes

1 Like

OP, since you are going to do this regularly and are dealing with millions of records on the production server I would recommend you set up the FEDERATED tables to copy your data from the remote production server to the Training DB. It would be the cleanest setup and you can use a single query to get the job done.

IMPORTANT: Do not edit the data in the FEDERATED tables in your training DB or you will be making those changes in the production DB. The FED tables are just remote shortcuts to the production DB.

Copy the data locally like so…

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

Here is sample SQL I used to test the code. (FED table CREATE must match the remote table structure)

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. Even not for read only…

1 Like

The op is not doing testing. They are doing training from a separate database other than production, using a copy of a subset of the production data. I was initially concerned with this process as I posted until I talked to the op in private.

2 Likes

system

closed

August 17, 2022, 8:44pm
#13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Source: https://www.sitepoint.com/community/t/using-php-how-do-i-copy-selected-records-from-one-mysql-database-to-another-on-different-hosts-thus-different-connections/387127