[rt-users] Splitting queries across mysql servers

Parish, Brent bparish at workscape.com
Wed Sep 6 15:44:42 EDT 2006


I'm using circular replication in MySQL and LOVE it!
The idea is that each MySQL host points at the other as a slave, so all
updates go to both hosts.  The problem with that in the past is that
tables using auto-increment columns could get whacked if both servers
added a row at the exact same time and contended for that next
incremented value.  
MySQL now has values you can set in my.cnf that tell one server to use
odd numbers (e.g.) and the other evens.
Works like a charm, though there is no automatic failover.  I run the DB
attached to the base IP for the server, as well as a virtual IP that can
swing over (by hand) to the other.  Lastly, they share a crossover cable
(direct cable) interface to do the replication over GigE.
You can check out the Heartbeat project for IP failover, but I've not
had time to dig deep into that yet (one dragon to slay at a time,
please!).
HTH
- Brent Parish




-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Drew
Taylor
Sent: Tuesday, September 05, 2006 5:24 PM
To: rt-users at lists.bestpractical.com
Subject: [rt-users] Splitting queries across mysql servers

Hi,
(Now subscribed from my personal address)

We're looking to scale our RT instance at $work, and make it more fault
tolerant at the same time. The obvious first step is to split the DB out
from the Mason servers (currently all on one box). At the same time, we
want to use mysql replication to get the data onto a second DB server.
In a perfect world we would have RT transparently be able to do SELECTs
on both DB boxes while sending UPDATE/INSERT statements to the master
DB.

I know I'm not the first person to attempt this. :-) Any pointers to DBI
multiplexers, tips, etc would be most appreciated.

Thanks,
Drew
--
----------------------------------------------------------------
 Drew Taylor                 *  Web development & consulting
 Email: drew at drewtaylor.com  *  Site implementation & hosting
 Web  : www.drewtaylor.com   *  perl/mod_perl/DBI/mysql/postgres
 ----------------------------------------------------------------
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com Commercial support:
sales at bestpractical.com


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com
**********************************************************************************************
IMPORTANT: The contents are intended for the named recipient(s) only and may contain information that is 
PRIVILEGED and/or CONFIDENTIAL.
If you have received this email in error, please destroy all copies of this message and it's attachments and 
then notify the system manager or the sender immediately. Do not disclose the contents to anyone or make 
copies thereof.
*** eSafe scanned this email for viruses, vandals, and malicious content. ***
**********************************************************************************************




More information about the rt-users mailing list