backup and dl sql over ssh

[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 May 23, 2013 by gec100

[gec100]

no one?

[MouseSpotter]

Google: autoit connect to sql database

[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

[gec100]

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

expandcollapse popup

#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