[vchkpw] vpopmail and mysql replication
Greetings, I've got a working mysql and vpopmail setup going, with replication support. For those that don't know, replication support in vpopmail means that all updates go to a single server, where lookups can be sent to another server. I have a local copy of the replicated database on each machine to spread the load out and to be more robust in the face of other machine failure. Unfortunately, this makes each machine dependant on the mysql update server, since each login triggers an update to the lastauth table. How have other people solved this, without just disabling mysql logging? Has anyone used any of the more rare multiple-master ring topologys in mysql to let each server update? I really don't want to setup a dedicated mysql server just to handle updates like this. I'm also planning to start using spamassassin mysql preferences, so I'd like any server to be able to update the database with user preferences, and the change replicate around. Does anyone have any input on this? Anyone use squirrelmail with their preferences in the database as well? Thanks! --Doug
Re: [vchkpw] vpopmail and mysql replication
Doug Clements wrote: Greetings, I've got a working mysql and vpopmail setup going, with replication support. For those that don't know, replication support in vpopmail means that all updates go to a single server, where lookups can be sent to another server. I have a local copy of the replicated database on each machine to spread the load out and to be more robust in the face of other machine failure. Unfortunately, this makes each machine dependant on the mysql update server, since each login triggers an update to the lastauth table. How have other people solved this, without just disabling mysql logging? Has anyone used any of the more rare multiple-master ring topologys in mysql to let each server update? I really don't want to setup a dedicated mysql server just to handle updates like this. I'm also planning to start using spamassassin mysql preferences, so I'd like any server to be able to update the database with user preferences, and the change replicate around. Does anyone have any input on this? Anyone use squirrelmail with their preferences in the database as well? Thanks! --Doug I'm about to tackle this same problem myself, since I'm about to ditch a pair of Solaris boxes for my NFS mounted mail spools and instead move to filers. My plan is to instead use Linux HA to have two machines as a failover cluster mounting the database via NFS. I think this'll be a lot cleaner, and will also integrate in a nicer fashion with the concept vpopmail has of mysql replication. Your other option, if you've got money to spend on the problem, is to get a database server which is both supported by vpopmail and supports more flexible replication. I'd recommend Sybase, since they support real two way replication, and their replication speed is amazing. Hope that helps, Nick Harring System Administrator Webley Systems, Inc.
Re: [vchkpw] vpopmail and mysql replication
You could potentially code in a multiple call update from vpopmail to some IFDEF'd in mysql servers. I'm thinking that you could make certain update calls to as many MySQL servers as you wanted, sending the same SQL statement to each. I imagine some critical thinking in the process could lead to judgement on what would be the essential updates to multi-home and which updates could be written to the master for later replication. My $0.02USD, before taxes. Doug Clements wrote: Greetings, I've got a working mysql and vpopmail setup going, with replication support. For those that don't know, replication support in vpopmail means that all updates go to a single server, where lookups can be sent to another server. I have a local copy of the replicated database on each machine to spread the load out and to be more robust in the face of other machine failure. Unfortunately, this makes each machine dependant on the mysql update server, since each login triggers an update to the lastauth table. How have other people solved this, without just disabling mysql logging? Has anyone used any of the more rare multiple-master ring topologys in mysql to let each server update? I really don't want to setup a dedicated mysql server just to handle updates like this. I'm also planning to start using spamassassin mysql preferences, so I'd like any server to be able to update the database with user preferences, and the change replicate around. Does anyone have any input on this? Anyone use squirrelmail with their preferences in the database as well? Thanks! --Doug
Re: [vchkpw] vpopmail and mysql replication
On Fri, Sep 05, 2003 at 02:31:39PM -0500, Nick Harring wrote: I'm about to tackle this same problem myself, since I'm about to ditch a pair of Solaris boxes for my NFS mounted mail spools and instead move to filers. My plan is to instead use Linux HA to have two machines as a failover cluster mounting the database via NFS. I think this'll be a lot cleaner, and will also integrate in a nicer fashion with the concept vpopmail has of mysql replication. Your other option, if you've got money to spend on the problem, is to get a database server which is both supported by vpopmail and supports more flexible replication. I'd recommend Sybase, since they support real two way replication, and their replication speed is amazing. Thanks for the input. I would assume Sybase also supports concurrent access to the DB files over NFS, since you're planning to use that. I know mysql specifically doesn't allow that (at least with MyISAM tables). Unfortunately, Sybase pricing is probably greater than the cost of labor in setting up a dedicated mysql server. What is a typical Sybase price for a setup like yours? --Doug
Re: [vchkpw] vpopmail and mysql replication
Doug Clements wrote: On Fri, Sep 05, 2003 at 02:31:39PM -0500, Nick Harring wrote: I'm about to tackle this same problem myself, since I'm about to ditch a pair of Solaris boxes for my NFS mounted mail spools and instead move to filers. My plan is to instead use Linux HA to have two machines as a failover cluster mounting the database via NFS. I think this'll be a lot cleaner, and will also integrate in a nicer fashion with the concept vpopmail has of mysql replication. Your other option, if you've got money to spend on the problem, is to get a database server which is both supported by vpopmail and supports more flexible replication. I'd recommend Sybase, since they support real two way replication, and their replication speed is amazing. Thanks for the input. I would assume Sybase also supports concurrent access to the DB files over NFS, since you're planning to use that. I know mysql specifically doesn't allow that (at least with MyISAM tables). Unfortunately, Sybase pricing is probably greater than the cost of labor in setting up a dedicated mysql server. What is a typical Sybase price for a setup like yours? --Doug I've not yet actually priced out Sybase for this, and probably wouldn't do concurrent NFS access, since with two way replication it'd be easier to just have two servers with a virtual IP for failover (makes it totally transparent to the clients). Sybase licensing is typically on a per CPU basis though, so if you have low load the pricing shouldn't be too bad. Also, with MySQL I'm not planning on doing concurrent access, but active/passive failover. The scenario plays out roughly like this: Server A is primary, and thus active. Server B is secondary, and thus passive. Servers A and B mount the nfs share, but only Server A starts MySQL and takes ownership of the virtual IP. Server A chugs along happily for an arbitrary period of time, serving requests and keeping the table files up to date. Server A fails, server B fails to receive a heartbeat and immediately assumes the virtual IP, forcibly arps to notify the switch, and then starts MySQL, loading the tables via NFS. When Server A resumes it sees that B is primary, and stays passive, waiting for a heartbeat failure in order to fail back over. The other option, one that I'm also considering, is instead of using shared storage is to have two configuration files on each server, one with it being the master, and the other with it being the slave. You still use a virtual IP, but both servers run all the time. Which ever is the slave just connects to the master as a slave and pulls updates from it, only restarting in master mode in order to server updates properly to the rest of the cluster. In the first scenario, if you're leery of NFS (which is a reasonable thing for databases), the other option is to hang one storage array with two scsi host channels off of two boxes and have them mount/umount based on their active or passive role. In either scenario you need to be 100% positive that you'll completely fail rather than have both sides become active, as split brain syndrome is incredibly difficult to recover from. You might want to look at Veritas Cluster Services for this sort of setup, as they're pretty reasonable to manage and they offer the flexibility to do a lot of fail over scenarios. I'd definitely advise looking at the Linux HA tools and see what kind of reliability and flexibility you need. I have had very, very positive experiences with both LVS and Pirahna in the past, including doing it with MySQL. The upfront effort is a little on the high side, especially for dynamically reconfiguring MySQL servers to flip from master to slave and back, but its well worth it when you get an essentially bulletproof cluster. Nick
