Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Our production environments typically consists in 4-8 Apache web servers and 2 (My)SQL servers :

Each web server is affiliated to one SQL server SQL servers have a circular replication setup All web servers are load balanced, by Pound for example. Every night a job backups one of the SQL servers, locking the affiliated web servers for about 10-15 minutes.

Is there a way to configure the balancing to avoid reaching those locked servers for a short time?

Is there another way to handle this lock, other than backuping a non-production third server?

PS: We envisage to reload the Pound configuration, just before and after the backup, with an appropriate configuration file, but it feels a bit odd...


The question has already been asked on StackOverflow but closed as 'not development centric' purposed. As a matter of fact, it's more a production and run discussion.

share|improve this question

3 Answers 3

up vote 4 down vote accepted

Taking load off a server to do a backup is not unheard of. How you do that depends entirely on your load balancer. For example, Kemp load balancers have a web API you can call to disable a particular server and haproxy has a pipe based management interface that you can script from a linux shell. I'm sure that pound has an equivalent way off disabling a server but I don't know what that is.

Another popular way to disable a server with haproxy is to have the check action look for a specific file. When you want to disable the server just delete or rename that file. You should be able to easily do this with pound, and it satisfies your requirement of keeping the scripting in our web tier and not calling to the load balancer.

share|improve this answer
    
In deed poundctl -b n m r would to the job, but the point is that no direct communication is possible between the balancer and the SQL server. It has to be relayed by the web back-end. I envisage a cron table with an empiric sufficient time to perform the backup, lacking of a better solution. –  Tom Desp Mar 31 '13 at 16:13
    
what about my suggestion of making the health check depend on the existence of a file on your web server? –  longneck Mar 31 '13 at 23:16
    
Synchronizing the activity across the cluster is trivial - and the least of your worries. Simplest way to do this would be a script using ssh connections. –  symcbean Apr 2 '13 at 21:58

If you stop apache on the host where you are doing backups pound should detect in 30 seconds that the backend is offline and direct visitors to other hosts.

share|improve this answer
    
again, backup is performed on SQL server, not Web server. Stop SQL during backup would fail the web server which would be relayed to the LB but it'd also produce an unwanted huge apache error log. –  Tom Desp Mar 31 '13 at 16:22
    
You can specify a different port/address for the ha checking by using HAport sqlserver:port so maybe that would help you. Or use a different daemon (netcat) on a different port just for the haport directve. –  Jure1873 Mar 31 '13 at 19:37
    
Sorry, I didn't see above that there is no direct communication between the sql server and the backend. –  Jure1873 Mar 31 '13 at 19:38

If you a slave running off either master, then you can do the backups on the slave without the complications of more-than-2-node master-master replication. That would be my preferred choice.

But you could equally well mark one of the current 2 master as 'fenced' forcing all the web traffic onto the still active server. How you do this depends on what's talking to the database. If it's simply PHP (without persistent connections - which would be silly in this setup) then just swap in a different include file, wait a couple of seconds (minutes?) for the current connections to complete then run your backup. When the backup completes, switch the include files back again.

Alternatively, if you've got the webserving capacity, split your wbservers into 2 pools - one for each DBMS and tell pound to fence one bank while you backup / run 2 instances of pound with RRDNS and add an iptables tables to reject new connections on the backup bank when you want to backup.

If you've got persistent connections then the bank switching is probably the only practical solution.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.