I would really like to hear how some of you are handling backups on
high-availability servers. The DBA in my company is skeptical about
switching from MSSQL Server to MySQL, this is one of his reasons
(backups). If someone is making MySQL work in a high-availabity
environment, let's hear about it!
Thanks for any input,
-Ryan
So... where to begin. We're truly managing our databases for high
availability... so much so that we actually have hardware load
balancers between our database servers and our application servers...
although that's a little extreme. Ignoring the load balancers here's
what we do:
Production database pair replicates between each other, ie each
server in a pair has the other one as it's replication master. We
deploy several pairs of database servers this way, each pair has it's
own data and the other pairs don't need to know about it.
This addresses several issues... firstly by running in pairs we can
deploy our apps servers in pairs also (or fours or sixes etc...), so
apps1 speaks to data1 and apps2 speaks to data2... if something
happens to data1, it takes out apps1 (except in our case where the
hardware load balancer reconnects apps1 to data2)... but whatever you
are doing with your apps servers in a high availability situation
should be able to detect apps1 is in trouble and fail it out of
production anyway. Also because replication is so fast (not
instantaneous though, but close) it means we have current data on two
servers all the time... if something happened to a disk array or
something, we'd still have the data.
From there we have what I call our mysql-admin server... this
handles the non production tasks... let me explain. We are doing both
High availability and high volume... this means we need the
production servers to be fast. Because the volume is high that means
things like reports can cause quite a drag on the database servers...
however the reports are only seen by us and our clients, not by all
the users. Running reports usually puts a dent in performance on the
database side. For that reason we have a separate server that handles
this.
So our mysql-admin server connects to each database pair and
replicates the data from all of them... so we have several instances
of MySQL running on mysql-admin. This machine also has a large
storage array attached to it. It is here we do things like run
reports and do backups... it can be done without affecting production
servers at all. I have a series of scripts which run in Cron that
stop the replication process, and simply copy the data files to the
array. Replication is started again and the scripts go on to do
things like compress the data files and so on... It's very fast and
totally painless on the production servers.
I realize that not everyone is in a position where they can throw
hardware load balancers between their apps servers and their database
servers, nor can they dedicate an extra server to handle the admin
tasks for the databases. That said we also rely exclusively on
InnoDB... If you're needs are truly High Availability you probably
will too. You don;t need a separate server to handle backup with
InnoDB and you don't need to take a live Server out of the loop.
InnoDB offers what they call InnoDB Hot Backup... which is a program
you can buy that will handle the backup while the server is live and
store all the changes during the time of the backup in it's logs...
it's very effective and works very well. We have used InnoDB Hot
backup and like it very much. The only reason we switched away from
using it is because we could... we changed the way our Admin server
runs now and it is always deliberately not reading replication
changes from the production servers for 1:50 of every 2:00 hours...
so it's easy for us to just do a file system copy of the database
files... it's a LITTLE easier for us to recover from that without
using InnoDB Hot Backup... so we do it that way... but InnoDB Hot
Backup works great.
Not using InnoDB, or can't use InnoDB.. then your choices are a
little harder. I don't know about other solutions.. but the
production server/admin server setup works great regardless of what
your database engine is... if you have a server you can do it on :-)
Best Regards, Bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
