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 :
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.
Using 2 commands in a batch file like this :
mysqldump --add-drop-database -u username -psecret -h 126.96.36.199 -B db1 db2 > temp.sql mysql -u root -psecret < temp.sqlwill 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:email@example.com 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 exitthen run the winscp commands like this
winscp /script=my_dumpThe script above does the following
troubleshootingWhite 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 yestests 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
option echo on option confirm off open guy:firstname.lastname@example.org 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.