Preface: I realize that these questions are not exactly unique to Xojo, but I have to commend Xojo Inc and the community they have set up. If I were to go over to stack exchange and ask this question I would be ridiculed and down voted. I do have thick skin, but i would at best have to sort thru lots of garbage and perhaps not even get an answer.
My question is what is the best war to protect you mysql database when running in the xojo cloud. When I run it on my own CPU I allow mySql only to take connections from 127.0.0.1. In the cloud however its a comply different IP address. This open up hole for a ne’er-do-well to enter.
Also: what is there a simple way to simply replicate the database to a copy here in my office for backup? (I wish this to happen automagically)
There are a couple of basic things you can do to try and deter someone from trying to hack your remote MySQL instance.
Change the port it uses to something other than the default 3306.
Make sure all database users have a password.
Only ever connect to the database via an application specific database user that has restricted permissions.
If you know the IP address of the server(s) that your application will connect from you can restrict the database user to only connect from those IP addresses.
Use SSL (the Xojo MySQL driver apparently supports this).
For replication, you could use the built in replication mechanisms, I’ve done this across separate fire-walled networks in a data centre but not across the internet, so I’m not sure how reliable that would be.
If you’re really just looking for a daily backup to be pulled across to your office, then I would recommend automysqlbackup as a simple mechanism to dump the data to rotated date-time stamped files, followed by an ftp script either to run from the office and pull the data or from the remote host to push it.
Once you have the data you could rename the current version of the database and then import the data as a new version of the database.
Re connecting
I agree with Ian. Plus keep your critical fields encrypted in case someone does manage to hack in and steal your data, then all is not lost. You need encryption not only on the login, but also the MySQL data transfer itself. I use the following to encrypt the data transfer:
'turn on encryption of the MySQL traffic
tempMySQLCommunityServer.SSLMode = True
tempMySQLCommunityServer.SSLCipher = "DHE-RSA-AES256-SHA"