14

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.

Christian
  • 779
  • 1
  • 13
  • 31
  • What is the website? Give a description of what it does – jamespo Sep 13 '11 at 12:27
  • You can buy servers for a lot cheaper than $150 a month. 7GB does not sound like that much data. You can buy [disposable 128MB servers](http://lowendbox.com) for as little as $1.50 a month and more impressive 1GB ones for about $20. Since there is no need for a query cache you can easily handle plenty of writes with a GB of RAM and a server with an SSD. – Xeoncross Sep 13 '11 at 14:50
  • LVM snapshots will not give a consistent image unless you shutdown the server first. You can do hot snapshots - and try to rebuild the files - but it's risky. – symcbean Sep 14 '11 at 09:04

6 Answers6

10

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.

Mike
  • 21,910
  • 7
  • 55
  • 79
5

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.
Joshua Hoblitt
  • 665
  • 4
  • 11
  • Joshua, I notice your typo of 'myself' and note that I find it *so* hard to type 'myself' because I just naturally type mysql. I currently do a mysqldump 4 hourly on the slave machine. single-transaction looks like a good option, thanks! – Christian Sep 13 '11 at 05:19
  • Doh. Nice catch. :) – Joshua Hoblitt Sep 13 '11 at 06:25
  • I do not think that mysqldump is a good option on such a large database. If it takes hours to dump, it can take weeks to restore. Test your restore time and the resources needed to complete it! –  Sep 13 '11 at 15:21
  • Thanks Baron, it does take a little while to restore - not weeks, but still a considerable time. I will see how long it takes when I get my new server. Maybe a copy of the files will work out to be much more effective. – Christian Sep 14 '11 at 04:07
2

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

thomasrutter
  • 2,437
  • 1
  • 25
  • 34
  • 1
    I would have no idea how much bandwidth it would even use. Maybe I should monitor the traffic between the boxes I have now to see how much is used. – Christian Sep 13 '11 at 04:04
  • 1
    You may be "disappointed" with trying to run mysql on top of EBS. I highly suggest you test the performance before trying to use it for replication. – Joshua Hoblitt Sep 13 '11 at 06:26
  • Thanks for that, will definitely get a feel for it before I start relying on it - if this is the approach I take. – Christian Sep 14 '11 at 04:06
1

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

Luke
  • 103
  • 7
  • And I meant to add, replication does not use much bandwidth. It is no doubt a better call than mysqldump-ing every four hours!!! – Luke Sep 13 '11 at 04:14
  • Who mentioned IT department? This is just my website. :) And I am currently replicating for backups but not sure its the best approach at $150 p/m. As stated the option of an EC2 micro instance is there. – Christian Sep 13 '11 at 04:24
  • @Christian what's p/m? I don't know what it is, but 150$ for a single p per m seems expensive 8-| – TehShrike Sep 13 '11 at 07:00
  • @TehShrike, p/m = per month. Australian hosting is a lot more pricey than US hosting. Also, I was trying to keep the second server on the same network for speed and transfers not counted against bandwidth allowance. – Christian Sep 14 '11 at 04:02
1

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.

symcbean
  • 19,931
  • 1
  • 29
  • 49
  • Nice response, thanks for that. The new server I am looking at getting would have enough memory to allow for a slave on the same machine, but I really like the idea of the binlogs being copied / rolled forward. Thanks again! – Christian Sep 14 '11 at 04:04
1

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!

jdias
  • 111
  • 2