Downtimeless MySQL backups on a budget

Question

My current MySQL backup scenario is to replicate our db to a second server and run mysqldump on that server to remove any downtime from table or row locking. This is working well but costs $150 per month for the second server (Australian hosting is a lot more expensive than US.)

I read a lot of questions on here about this, most people need help with the scheduled backups and whatnot which is not what I need. I need to mysqldump (preferably every 4hrs) with no downtime. The db is ~7GB uncompressed, so the mysqldump can take some time depending on the server.

I have considered replicating to the same machine, but I didnt want the slave to eat into much needed memory. I'm not sure I can constrain memory usage on a per db basis? Either way, this will put load on the server while its dumping the db.

I just read this http://www.zmanda.com/quick-mysql-backup.html and it looks good, $300 per year is ok, that saves me a lot.

Unfortunately I can't replicate to Amazon's RDS but I could replicate to a micro RC2 instance but the replication would take place over-net and the ping is ~220ms.

I saw a few people on here talking about LVM snapshots which might be a good option. I dont know a great deal about this option tho.

Opinions would be greatly appreciated.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

Realistically, only the time it takes to actually export the database will be downtime. Do it during a slow enough time period and there shouldn't be ANY problem. What is an IT department on that budget really expecting?

You should be able to mysqldump a 7GB database in 5-10 minutes MAX, take off the read/write lock and the downtime will be over. You can then find the most bandwidth effective way to the 7GB file to the new server (read: HIGH COMPRESSION). You have plenty of time to get the file transferred and imported into MySQL on the new server. Then, enter the masterlog information and start replication. Should be a piece of cake!

MySQL documentation is fantastic: http://dev.mysql.com/doc/refman/5.0/en/replication.html

Answer 5

I'm not sure I can constrain memory usage on a per db basis

Of course you can - you just need run the slave with a different /etc/my.cnf

You can even do stuff to manipulate the scheduling priority / CPU affinity on the master and slave using nice/renice and taskset (assuming it's a Linux server).

but the replication would take place over-net and the ping is ~220ms

Latency is pretty much irrelevant - the important thing is bandwidth - and the database bandwidth (assuming you're not replicating session data) is several orders of magnitude less than the HTTP bandwidth.

I need to [create a consistent backup of the database] (preferably every 4hrs) with no downtime

But the strategies you discuss don't allow for recovery at anything like that time.

I think the cheapest option would be a slave on the same machine - and if it's adversely affecting performance beyond what you can reconfigure then upgrade the current hosting package.

You might also consider running a disconnected slave: enable bin logs on the current server. Get a backup, restore the backup on a local machine then copy down the bin logs as they are rotated and roll them forward on the local DBMS.

Answer 6

My suggestion:

1 - keep your second account/server and implement replication to a database in your original account/server.

2 - stop replication to the second account/server.

3 - monitor performance for a few days. Make sure you monitor it long enough to include your busiest periods.

4 - be ready to switch over to your old setup if there is a major performance problem. This is the reason why you kept the second account.

5 - buy more capacity/upgrade server in your original account. This should be cheaper than paying for two servers I believe.

6 - cancel second account.

Good luck!