I am experimenting with mysql replication, and have done some research on key collisions in the case of a 'load balancing' situation (live sql servers running on each amavisd server), using either same mx weight, or VRRP/CARP, heartbeat, virtual ip type setups. 'random' smtp connections could hit each server, and each server has a local mysql DB, in a dual master/slave replication setup. (updates to either db propagate to the other, works fine, creates lots of traffic, so maybe use a second nic and an xover cable..)
My concern is over use of SERIAL keys in amavisd-new tables, vs AUTO_INCREMENT keys. (are SERIAL keys an alias for AUTO_INCREMENT? Are SERIAL keys safe in replication situations?) I have seen documentation saying that 'auto_increment' works as expected in replication situations, but can't find any information on SERIAL keys. http://www.weberdev.com/Manuals/MySQL3.X_4.X/replication.html#replicatio n-features Another issue may be AWL files, (I suppose a spamassassin question also?). Every 'new' ip/email incoming will create a new PRIMARY KEY (username,email,ip). If two connections, one on each box, first one wins, replication stops and you need to manually issue a bunch of commands to skip (two?) transactions and restart slave. --slave-skip-errors=[err_code1,err_code2,... | all] Normally, replication stops when an error occurs, which gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value. Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of sync with the master, with you having no idea why this has occurred I am using Innodb DB type on Freebsd5, and mysql 4.1.20ish. -- Michael Scheidell, CTO 561-999-5000, ext 1131 SECNAP Network Security Corporation Keep up to date with latest information on IT security: Real time security alerts: http://www.secnap.com/news
