Perl script to connect two databases at the same time

Question

I am trying to connect to 2 databases in the same MySQL instance with 1 Perl script.

I use this in a migration script, where I grab data from the source database and paste it into a new one.

Connecting to 1 database, and then trying to initiate a second connection with the same user, simply changes the current database to a new one.

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "mysql";
my $database1 = "db1";
my $dsn1 = "DBI:$driver:database=$database1";
my $userid = "userhead";
my $password = "pwdhead";
my $database2 = "db2";
my $dsn2 = "DBI:$driver:database=$database2";

my $dbh1 = DBI->connect($dsn1, $userid, $password ) or die $DBI::errstr;
my $dbh2 = DBI->connect($dsn2, $userid, $password ) or die $DBI::errstr;

my $sth = $dbh2->prepare("INSERT INTO Persons") $dbh1->prepare("SELECT *FROM Persons");
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;

In the above example, I am trying to copy from one database table to another datbase table. but I get an error when running the script. Please help me

Answer 1

, . ,

data_sources MySQL , @dbh. ,

my $stmt = $dbh[0]->prepare('SELECT * FROM table)

, @databases , ,

use strict;
use warnings 'all';

use DBI;

my $user = 'username';
my $pass = 'password';

my @databases = DBI->data_sources('mysql');

my @dbh = map { DBI->connect($_, $user, $pass) } @databases;

---

Update

,

, , VALUES INSERT

, , . , , , , .

#!/usr/bin/perl

use strict;
use warnings 'all';

use DBI;

my $userid   = "userhead";
my $password = "pwdhead";

my ($dbase1, $dbase2) = qw/ db1 db2 /;

my $dsn1 = "DBI:mysql:database=$dbase1";
my $dsn2 = "DBI:mysql:database=$dbase2";

my $dbh1 = DBI->connect($dsn1, $userid, $password ) or die $DBI::errstr;
my $dbh2 = DBI->connect($dsn2, $userid, $password ) or die $DBI::errstr;

my $select = $dbh1->prepare("SELECT * FROM Persons");
my $insert = $dbh2->prepare("INSERT INTO Persons VALUES (?, ?, ?, ?, ?)");

$select->execute;
while ( my @row = $select->fetchrow_array ) {
    $insert->execute(@row);
}

, @row.

while ( my ($id, $name) = $select->fetchrow_array ) {
    my $lastname = '';
    $insert->execute($id, $name, $lastname);
}

Answer 2

A ( ):

mysqldump ; mysql . . Unix, exec() Perl ( ).

B ( ):

" ", SELECT, Perl. INSERT . ( ), INSERT CSV LOAD DATA INSERT.