php PDO transfer resultset to database

Question

I try to transfer a huge amount of data (around 200k records every few minutes) from one to another database (also on two different servers). The tableschema on both tables is both dbs is the same.

So whats the best way to transfer a huge resultset into a db without causing an memory limit error.

my current solution looks like this. But this means I run about 200k Insert querys in writeToDB2() and that seems to be not very effective to me.

$stmt = $this->db_1->query("SELECT foo from bar");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    writeToDB2($row);
}

Does anyone knew a better solution to bulk transfer the data?

Replication? Although has its own drawback – frz3993 Jan 13 '17 at 13:15 — frz3993, Jan 13 '17 at 13:15

score 1 · Answer 1 · answered Jan 13 '17 at 13:14

1

Fortunately mysql supports INSERT SELECT spanning databases.

$stmt = $this->db_1->query("INSERT INTO db2.bar(foo) SELECT foo from db1.bar");
$stmt->execute();

answered Jan 13 '17 at 13:14

e4c5

52,766
11
101
134

Would be a neat solution but unfortunately those thow databases dont run on the same server. So I have two db-handels – rockZ Jan 13 '17 at 13:21
then your only option is replication. – e4c5 Jan 13 '17 at 13:22

score 1 · Accepted Answer · answered Jan 13 '17 at 13:24

The other answer works only if the same user has access to both databases.

A general purpose solution is to forget about PHP and PDO at all and use console mysql shell and mysqldump like

mysqldump -uuser1 -ppassword1 db1 tablename | mysql -uuser2 -ppassword2 db2

php PDO transfer resultset to database

2 Answers2