Hi All,

I want to speed up a process that goes in our organization. Basically it consists of 3 steps
1. from box1 do a mysqldump of a database(in mysql)
2. scp the file mysqldump file to another remote machine say box2
3. import the mysqldump in mysql server on box2.

The database size is really huge in terms of a couple of GBs. Also the network speed is only in couple of Kbps. So the overall process takes very long time. I want to speed this up by doing parellelizing the process. This is my idea.

1. Change the mysqldumping process so that it creates one file with all create table statements and then a data file for each table.

2. A process should continously monitor the status of all files being dumped from mysql. And as soon as it sees a file being completely dumpe d it should scp the file from box1 to box2.

3. On box2 a similar process should run as in step 2, which should keep checking for files which are completely scped. And as soon it sees some file being completed transferred should load the file into mysql server corresponding database.

So to be able to do this I need a mechanism to find whether a file has been completely transferred or completely dumped by mysql server. Also I want this process to be bit flexible. As in the mysql server on box2 may not be always running. In which case I just want to do just first 2 steps and do 3rd step whenever possible i.e mysql server on box2 is avaiable again.

Can anyone please tell me how this can be done?

Thanks,
Ganesh

I'd suggest looking in to MySQL Replication:

http://dev.mysql.com/doc/refman/5.1/en/replication.html

Which would be a much better solution. What you're talking about is easily done through some clever scripting, but has lots more moving parts, and is more prone to failure.

Hi,

Thanks TB0ne. But unfortunately I don't have the rights to setup replication from box1. Can you please tell how it can be done through scripting?

Thanks,
Ganesh

Basically, you write a script that creates the backup(s) and then creates a file eg via touch cmd that acts as a signal after a file has completed.
Send the backup file followed by the associated signal file. The receiver waits till it sees the signal file before it attempts to load the dump file.
So,
Sender:
1. mysqldump blah to file1.dmp
2. touch file1.sig
3. send file1.dmp
4. send file1.sig
repeat for each dump/sig file pair



Receiver:
wait for sig file, load associated dmp file, remove/archive dmp+sig file
repeat ...

note; to reduce network load, gzip dump file before transmission, gunzip at receiver.

Hi,

Thanks very much for the answer chrism01. I think it was an excellent idea. I got into another problem while trying to create data files for tables in the database. So I am going to try generate sql files with insert statements instead of data files.

Thanks,
Ganesh

If you don't have the rights to set up replication, should you even be doing manual dumps/reloads???

ganesh_k: the mysqldump prog produces what you want, just read up on the optons: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

TBOne: could be he's only got schema owner rights, not mysql root. iirc, you need mysql root (equiv dba) to do replication.

Quite true...but I still would question why he's trying to perform what's essentially a DBA function, without DBA privs.