LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 08-04-2008, 03:17 PM   #1
ganesh_k
LQ Newbie
 
Registered: Dec 2003
Location: Chennai,India
Distribution: Ubuntu
Posts: 11

Rep: Reputation: 0
How to find whether file transfer has completed on scp or mysqldump.


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

Last edited by ganesh_k; 08-04-2008 at 03:44 PM.
 
Old 08-04-2008, 03:42 PM   #2
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 14,198

Rep: Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470
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.
 
Old 08-04-2008, 03:53 PM   #3
ganesh_k
LQ Newbie
 
Registered: Dec 2003
Location: Chennai,India
Distribution: Ubuntu
Posts: 11

Original Poster
Rep: Reputation: 0
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
 
Old 08-04-2008, 08:36 PM   #4
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,226

Rep: Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023
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.
 
Old 08-05-2008, 10:22 AM   #5
ganesh_k
LQ Newbie
 
Registered: Dec 2003
Location: Chennai,India
Distribution: Ubuntu
Posts: 11

Original Poster
Rep: Reputation: 0
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
 
Old 08-05-2008, 10:27 AM   #6
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 14,198

Rep: Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470
Quote:
Originally Posted by ganesh_k View Post
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
If you don't have the rights to set up replication, should you even be doing manual dumps/reloads???
 
Old 08-05-2008, 07:43 PM   #7
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,226

Rep: Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023
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.
 
Old 08-06-2008, 09:00 AM   #8
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 14,198

Rep: Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470Reputation: 2470
Quote:
Originally Posted by chrism01 View Post
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
File transfer stalled after round 40MB (scp, ftp, http, cp) eXor Debian 3 03-26-2008 01:10 PM
script to find out time taken to transfer a file aeby Linux - Networking 1 05-09-2007 05:29 AM
mysqldump : Can I split the file up to 2GB max per file? Swakoo Linux - General 10 10-17-2005 04:13 AM
cannot transfer (FTP) 10GB file, also scp dont work freebies Linux - Software 2 02-25-2005 12:38 AM
Console hanging and scp transfer low when using ssh XStorm Linux - Networking 1 12-08-2003 09:34 PM


All times are GMT -5. The time now is 01:36 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration