Downtimeless MySQL backups on a budget

If you use innodb tables, you can use

http://www.percona.com/docs/wiki/percona-xtrabackup:start

That will take a dump of your database that can be imported by their tools also without locking. I believe if you have myisam tables it locks those.

If you are using innodb or another backend that's fully transactional, you can use mysqldump --single-transaction .... I have used this on fairly large (~100GB) databases with good results; if the database is under heavy load it can take hours but it does work without locking your tables. Replication is generally better but sometimes you want a nice solid dump file. Keep in mind that you can dump a mysql replication slave as well.

From the mysqldump page (note the caveates about operations that will leak into the transaction):

 ·   --single-transaction

   This option sends a START TRANSACTION SQL statement to the server
   before dumping data. It is useful only with transactional tables
   such as InnoDB, because then it dumps the consistent state of the
   database at the time when BEGIN was issued without blocking any
   applications.

   When using this option, you should keep in mind that only InnoDB
   tables are dumped in a consistent state. For example, any MyISAM or
   MEMORY tables dumped while using this option may still change
   state.

   While a --single-transaction dump is in process, to ensure a valid
   dump file (correct table contents and binary log coordinates), no
   other connection should use the following statements: ALTER TABLE,
   CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
   consistent read is not isolated from those statements, so use of
   them on a table to be dumped can cause the SELECT that is performed
   by mysqldump to retrieve the table contents to obtain incorrect
   contents or fail.

I don't see much of a problem replicating over a high latency connection to a cheap VPS in the U.S. The high latency should not really be that much of a problem. Replication is designed to be able to catch up quickly even when a slave falls hours behind, ie it can operate asynchronously.

As long as you can stand that much outgoing bandwidth on your Australian hosting plan.

Here is a much more detailed response to whether the high latency would matter