Data transferring

[Michael Widenius]

>>>>> "Danny" == Danny Arseneau <danny@stripped> writes:

Danny> Hi guys, I currently have a need to transfer data from one mysql server
Danny> to another on a regular basis. It's  not a whole lot of data (< 5000
Danny> rows) and only the data from one table has to be transferred.

Danny> I had first implemented a solution where a cronjob on the source server
Danny> used a 'select .... into outfile' query to generate a datafile, which
Danny> was then transferred to the destination server and subsequently loaded
Danny> into the destination mysql server by using 'load data infile ...'.
Danny> Because we are using the SSH tools, we avoid the need to have seperate
Danny> cronjobs on each server. The source server invokes a script on the
Danny> destination server using ssh (Secure version of rsh). This works quite
Danny> well and is quite speedy. There are some potential problems, because we
Danny> haven't figured out a way to confirm that the data was properly
Danny> transferred.

Danny> Another solution was presented to us, which we thought was quite
Danny> elegant. Basically we use a perl script with the DBI modules. We then
Danny> establish a connection to the source server and the destination server
Danny> at the same time. We do a select on the source server, then do an
Danny> 'insert into ....' on the destination for each row of data. This also
Danny> works and it allows us to verify that the data transfer was succesful,
Danny> but it is extremely slow. It takes over 6 minutes to transfer all the
Danny> data between the two servers. I guess the problem is with the 'insert
Danny> into...'.

Danny> My question is, does anybody on this list have any suggestions or
Danny> recommendations on how to handle this transfer of data. Is there another
Danny> way to transfer data between two mysql servers when you have a
Danny> connection to each one in the same program?

Danny> Thank you all very much. I read messages on this list all the time and I
Danny> find it very helpful and informative. Keep up all of the good work guys.
Danny> -- 

Danny> Danny Arseneau
Danny> Chief Technology Officer
Danny> Mamma Systems Inc.

Hi!

Have you tried to do:

mysqldump --opt --lock-tables database | mysql -h remote-server database

This should be quite fast!

Note that you can get inserts much faster by grouping many inserts
into the same insert command:

INSERT INTO TABLE values (first row),(second row),(....)

(mysqldump --opt uses this)

Regards,
Monty