1 ################################### 2 ## SQLgrey support for DBCluster ## 3 ################################### 4 5Database clustering behaviour is enabled by the 'db_cluster' 6configuration variable in /etc/sqlgrey/sqlgrey.conf 7 8## Default 9 10By default db_cluster is set to 'off'. 11 12## DBClustering 13 14Theese functions allow you to work with a cluster of databases (and 15mailservers). 16This is very usefull for places where the mailservers are already being 17clustered through eg. lvs. 18It is also usefull if you simply want all your mailservers to use the same 19tables (eg. the Auto-whitelists). And lastly, it is usefull for distributing high db load. 20 21NOTE: THIS HAS BEEN TESTED WITH MySQL ONLY. 22 23## DBCluster - what does it do 24 25Basically, dbclustering uses normal DBI through an override module that allows 26it to have connections to several database servers instead of just one. 27(Module used, is DBIx::DBCluster is made by Alex Rak, slightly modified) 28 29What this means to you, is easy access to distributing sqlgrey's queries among 30several database servers. This is probably best explained with an example: 31 32 [internet] 33 | 34 [Load balancer] 35 / | \ 36[mail1] [mail2] [mail3] [ect.] 37 38In this case, you have 3 mailserver that semi random gets connections from internet. 39Let each mailserver have its own SQL-server on localhost, since using 1 common for all servers can 40be to heavy for some setups. 41 42If [mail1] gets a new request, it'll greylist, respond "450" and stick client into the 43"connect" table. Now heres the problem. Client backs off, and comes back later to try 44again, but there is no garantee it'll get [mail1] again. Infact, if it doesnt, the 45greylisting will happen all over. 46 47The solution is to use DBClustering. Each mailserver STILL has its own SQL-server, but 48we add a master-sql and let the local sql-servers be replication slaves: 49 50 [internet] 51 | 52 [Load balancer] 53 / | \ 54[mail1] [mail2] [mail3] [ect.] 55 \ | / 56 [DB-Master] 57 58 59We enable DBCluster in sqlgrey, set [DB-Master] as "db_host" and set read_hosts=localhost 60 61Now, all write operations will be directed to [DB-Master]. Using normal SQL-replication, the 62local DB's will get all new changes and thus, be an excact copy of the master. 63All read operations are done to localhost, removing load from the master, and speeding up the 64read time, since the request doesnt have to travel over the network. 65 66Sounds complicated? It isnt.. Read on.. 67 68## Setting up DBClustering 69 70Step 1. Set up a replication db-cluster. This is not covered here. 71 Check your manual for whatever DB your using. MySQL's replication howto can be found 72 here: http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html 73 74Step 2. in sqlgrey.conf, set db_cluster = on 75 76Step 3. Change db_host to point to your MASTER-SQL. db_host becomes your "write host" 77 78Step 4. Add one or more sqlservers to read_hosts. (eg. read_hosts=localhost) 79 80Step 5. Choose one sqlgrey server to do db-cleanup (read more below). At a prompt on that host 81 type "hostname". Add the resulting hostname to db_cleanup_hostname. (eg. db_cleanup_hostname=mail1) 82 83And youre done. 84 85## Configuration directive: db_cleanup_hostname 86 87Its probably not desirable to have every sqlgrey in the cluster issuing db-cleanup every 30 minutes to the 88master-sql. 89 90To get around this problem, you can choose one server that does the cleanup. Every sqlgrey will query its own 91hostname upon startup and the hostname that matches the db_cleanup_hostname will do the cleanup. 92Since it usually isnt desirable to have differing configuration files on every host in a cluster, using hostname 93seems the best solution. This way, sqlgrey.conf can be identical on every node in the cluster. 94 95The hostname corrosponds to the output of the "hostname" command on linux. 96$ hostname 97mailhost-1 98 99In sqlgrey.conf set: 100db_cleanup_hostname=mailhost-1 101 102 103## Configuration directive: read_hosts 104 105read_hosts must be set to the hostnames/IP's of the "read_only" database hosts. 106It can contain 1 or more hosts, seperated by comma. 107Read requests will be distributed equally among "read_hosts". 108 109Remember that "db_host" becomes your "write-only" host upon enabling clustering. Should you wish to read from 110the "db_host" as well as say, localhost, simply add it to read_hosts. 111 112Examples: 113--------- 114Write to master, read from localhost: 115 db_host=my-master.example.com 116 read_hosts=localhost 117 118Write to master, read from localhost and master: 119 db_host=my-master.example.com 120 read_hosts=localhost,my-master.example.com 121 122Write to master, read from 3 slaves: 123 db_host=my-master.example.com 124 read_hosts=slave-1.example.com,slave-2.example.com,slave-3.example.com 125 126 127 128 129 130 131