Backup remote mysql database to local machine

I told you here a way to backup remote MySQL databases using a mix of AppleScript and shell script (ssh).

But that method won’t work with certain table types, which don’t store the data in files, such as InnoDB. So, here is a one-liner using mysqldump to backup that kind of databases…

mysqldump --opt --compress --host='website.com' --user='USER' --password='PWD' --all-databases | mysql -uUSER -pPWD -h127.0.0.1

Just substitute where needed USER and PWD. This shell script requires MySQL running in both machines and privileges to access the data in both sides.

IMHO, it runs much slower than the other method (making a copy of the remote db files) so, if you can use it, go for it! Otherwise, this method is safe for your data (safe if you don’t lose power or internet connection in the process 😉 ).

See “man mysqldump” for more options (such as extracting only certain databases or tables, as this script will backup everything!).

Advertisement

Backup remote mysql databases to local MAMP via AppleScript/SSH

Requirements: access to the remote machine (usually a server) via SSH.

I backup my databases to the local installation of MAMP (stands for Mac-Apache-MySQL-PHP), but you can specify any directory you wish.

First step: do some stuff, so you can login automatically (no password prompt) to the remote machine. I followed the steps at http://ubuntuforums.org/showthread.php?t=30709 and it worked flawlessly. In the first step exposed there, just type what the folk says, then “enter” all the time long (no need to enter data).

After you successfully copy the public key to the remote server, just copy-paste the following to your script editor, modify what you need to match your settings and hit “Run”:

--> this is the folder where the remote "mysql" dir will be copied
--> I choose MAMP's one, but you can choose any for backup purposes
set localDir to "User:Library:Application Support:appsolute:MAMP PRO:db:"

--> enter your server, the dir where your "mysql" dir is located (usually something as /var/lib)
--> and move it to whatever location is available via HTTP
--> if you don't have such service, you can use "scp" to copy the file to your machine
--> but then you must setup your machine to accept SSH connections
with timeout of 3600 seconds --> 1 hour
do shell script "ssh -l root server.com 'cd /var/lib; tar -zcf mysql.tar.gz mysql;mv mysql.tar.gz /var/www'"
end timeout

--> download tgz file and remove pre-existing "mysql" folder in local machine (not sure yours, mine yes)
do shell script "cd " & quoted form of POSIX path of localDir & "; curl -O http://www.server.com/mysql.tar.gz; rm -rf mysql;"

--> unpack
tell application "Finder" to open (localDir & "mysql.tar.gz")

--> remove tgz file from remote and local servers (I'll keep the uncompressed dir)
do shell script "ssh -l root qilania.com 'rm /var/www/mysql.tar.gz'; rm " & quoted form of POSIX path of (localDir & "mysql.tar.gz")

Cheers!

EDIT: See this post for a different option (supports other db engines, such as InnoDB).

FileMaker to MySQL via AppleScript

This is the AppleScript code we use to pass data from FileMaker to a remote MySQL database. It requires FileMaker, the MySQL binary installed in your machine and a connection to a remote MySQL-powered server.

The “code” tag is crap, so I’ll post an image of the code and a link to the code itself. You can download it here.

AppleScript --> FileMaker to MySQL (click to enlarge)

This way we feel comfortable working “at home” with a full-blown interface in FileMaker and we can populate changes to the server when needed.

Hello world!

Welcome to my personal blog about Qilania, the educational MMORPG for kids which will be born in 2010, where I act as product manager.

This will be a very interesting project, not only for its nature, but for the great amount of technologies used in it: SmartFox Pro Server and OpenSpace, PHP, MySQL, Flash and Flex, Java and AppleScript (in order to automate lots of tasks).

Stay tuned!