Jump to content
Sign in to follow this  
gec100

backup and dl sql over ssh

Recommended Posts

gec100

hi all.

i'm very new to AutoIt so please bare with me :)

here what i'd like to do:

- connect to db server using ssh

- dump the database to a .sql file

- ftp to the server and dl the .sql file

do some work on it locally

- ftp to the server and upload the edited .sql file

- connect to db server using ssh

- import the .sql to the database

- exit

here is how i did it:

 

$user = ...
$pass = ...
$db = ...
$connect = "e:\plink -ssh -pw " & $pass & " user@server.net"
$dl = "mysqldump -p -u " & $user & " " & $db & "> " & @MDAY & @MON & ".sql {ENTER}"

; db backup
Run(@ComSpec & " /C " & $connect)
Sleep(5000)
Send ($dl)
Sleep (3000)
Send($pass & "{ENTER}")
Sleep (5000)
Send ("exit{ENTER}")

;ftp dl
Run(@ComSpec & " /c " & "ftp server.net", "C:\Users\Administrator\Desktop\Moj")
Sleep (3000)
Send($user & "{ENTER}")
Sleep (3000)
Send($pass & "{ENTER}")
Sleep (3000)
Send("get " & @MDAY & @MON & ".sql{ENTER}")
Sleep(20000)
Send("bye{ENTER}")

that's only the receiving part. sending part is very similar.

now, although this works i'm sure this can look much nicer. also it would be better if i could read output and use instead of sleep() for better speed and error handling that is non existent right now.

thoughts please.

Edited by gec100

Share this post


Link to post
Share on other sites
gec100

no one?

Share this post


Link to post
Share on other sites
MouseSpotter

Google: autoit connect to sql database

Share this post


Link to post
Share on other sites
gec100

of course i've googled it. that's why i have working program. plus i have to use ssh to connect to my database. and if i google what u suggested i'll get everything except what i need.

so what i need now is to learn how to make my working program better

Share this post


Link to post
Share on other sites
gec100

Hi all again
 
here is what i've done so far:
 

#include <Debug.au3>
#include <GUIConstantsEx.au3>

$server = 
$user = 
$pass = 
$db = 

Opt("GUIOnEventMode", 1)  ; Change to OnEvent mode
$Form1 = GUICreate("Izaberi", 351, 168, 339, 222)
$Button1 = GUICtrlCreateButton("Download baze", 16, 40, 147, 81)
$Button2 = GUICtrlCreateButton("Upload baze", 184, 40, 147, 81)

GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked")
GUICtrlSetOnEvent($Button1, "_download")
GUICtrlSetOnEvent($Button2, "_upload")
GUISetState(@SW_SHOW)

While 1
  Sleep(1000)  ; Idle around
WEnd

Func CLOSEClicked()
  MsgBox(0, "GUI Event", "Zatvaram ... Bok!", 2)
  Exit
EndFunc

Func _upload()
    $file = FileOpenDialog("Select A File...", @DesktopDir, "db Files (*.sql)", 1 + 2)
    $split = StringSplit($file, "\")
    $filename = $split[$split[0]]
    
    ftpcon()
    ftpu()
    Sleep(1000)
    sshcon()
    sshd()
    sshu()
EndFunc

Func _download()
    sshcon()
    sshd()
    Sleep(1000)
    ftpcon()
    ftpd()
EndFunc
 
;#########################################
 
Func sshcon()
   Run(@ComSpec & " /c " & "c:\plink -ssh " & $user & "@" & $server & " -pw " & $pass)
   Sleep (5000)
EndFunc

Func ftpcon()
   Run(@ComSpec & " /c " & "ftp " & $server, "C:\Users\Administrator\Desktop\Moj")
   Sleep (3000)
   Send($user & "{ENTER}")
   Sleep (3000)
   Send ($pass & "{ENTER}")
   Sleep (3000)
EndFunc

Func sshd ()
   Send ("mysqldump -p -u " & $user & " " & $db & " {ASC 0062} " & @MDAY & @MON & ".sql {ENTER}")
   Sleep (3000)
    Send ($pass & "{ENTER}")
   Sleep (5000)
   Send ("exit{ENTER}")
 EndFunc

Func sshu ()
   Send ("mysql -p -u " & $user & " " & $db & " < " & $filename & ".sql")
    Sleep (3000)
   Send ($pass & "{ENTER}")
   Sleep (5000)
   Send ("exit{ENTER}")
EndFunc

Func ftpd()
   Send("get " & @MDAY & @MON & ".sql{ENTER}")
   Sleep(20000)
   Send("bye{ENTER}")
EndFunc

Func ftpu()
   Send("put " & $filename & ".sql{ENTER}")
   Sleep(20000)
   Send("bye{ENTER}")
EndFunc

The sending part i'm not sure it is working cause i did not test it yet.

The rest works. But.

it is a terrible work. it has no error handling whatsoever.

i tried working with StdinWrite and StdoutRead but it is hard for me to understand them cause i do not see what's happening.

any help and directions would be great.

maybe ssh and ftp are not good ways to access remote server?

thx

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×