Wednesday, August 25, 2010

Automating with Winscp

If you find yourself working on windows in a mixed unix/windows development environment, you probably need to copy files from a unix computer.
You might even need to do this periodically.

In these cases, winscp is your friend - while using the winscp commands

Today I decided to dump a remote Mysql DB and upload it locally.
There are several ways to attack this issue :

  • remotely create the dump


  • create the dump locally and copy it
  • create the dump locally, and upload it to your computer remotely. 
  • This option is quite nice since you can always control the access to your DB and configure it for remote access. 

  • Even if both DBs have remote access, you might still want to dump and load the DB locally and that's due to the fact that these operations lock the DB, in large quantities of data you might want to shorten them as much as possible, thus settling on local operations.
    The lock will minimize, but the overall time will remain the same as you still need to copy the file.

    Dumping remotely


    Using 2 commands in a batch file like this :

    mysqldump --add-drop-database -u username -psecret -h 123.123.123.123 -B db1 db2 > temp.sql
    mysql -u root -psecret < temp.sql
    
    will generate a temp dump file and inject it locally. however, you can only use this in case mysql is configured with remote connect. In other cases, you will need to dump the file locally, copy it and then inject it.


    Automating local dumps with WinScp
    write a file called "my_dump.winscp" and write something like
    option echo on
    option confirm off
    open guy:secret@123.123.123.123
    pwd
    cd /tmp
    lcd %TMP%
    ! mysqldump  --add-drop-database \-u username \-psecret \-B db1 1> temp_dump.sql
    ! test \-e temp_dump.sql && echo yes 
    get temp_dump.sql
    rm temp_dump.sql
    exit
    
    then run the winscp commands like this
    winscp /script=my_dump
    
    The script above does the following
  • login
  • go to temp dirs remotely and locally
  • dump db1 to file
  • copy output file locally
  • exit

  • troubleshooting

    White writing the script I encountered several problems. The most important one is while writing the mysqldump command I got "Unknown switch 'u'.". I later discovered this is due to the fact the winscp needs switches to be escaped. NOTE : this is very strange, since it is not documented anywhere. See Official Winscp Mysqldump example . Another thing to note is the exclamation point in the mysql command. This is a shorthand of "call" - a command in winscp that basically allows you to invoke any command you might want to. Last but not least, the command
    ! test \-e temp_dump.sql && echo yes
    
    tests the existence of the output file. You will see "yes" printed if it is there, otherwise blank. This is similar to "dir filename" behavior in DOS.


    Other WinScp automation script

    In this case, I wanted to intercept a buffer in the form of a file. 
    So I needed a script that goes to a directory and copies files. The Winscp script looks like this :
    option echo on
    option confirm off
    open guy:secret@123.123.123.123
    pwd
    cd /tmp
    lcd %TMP%
    get filename1*[0-9]  
    get filename2*[0-9]  
    get filename3*[0-9]  
    exit
    

    this goes to tmp folder locally, and tmp folder remotely, and copies all files with template
    filename1*[0-9], filename2*[0-9], filename3*[0-9].

    this is a very simple yet useful case.