We've got another storage server in our cluster. The storage servers are also the DB servers (to keep all the data together for backups). Now that we've got two DB servers, I thought that I could go with MariaDB Galera Cluster to have an active-active DB cluster. But before I actually did anything, I remembered that ISPConfig itself uses a MySQL database for its own local configuration. So the problem is that if I were to set up such a DB cluster, then how would I maintain the ISPConfig configuration correctly? If I just have the new storage server ("storage1") blindly copy the old storage server ("storage0"), then wouldn't this also copy over the "dbispconfig" database too and basically nuke the ISPConfig configuration? Has anyone dealt with this sort of thing and knows what to do?
First: I haven't tried such a setup yet. Just a few thoughts how this might work: If both servers run ispconfig, then each of them has probably a database named 'dbispconfig' and these must not be merged or overwritten when you setup a cluster. One option might be to rename one of the databases to e.g. dbispconfig1, then change the name in the ISPConfig config.inc.php file as well and then run an ispconfigupdate with reconfigure services = yes to get the changed database name into all config files. And when you add more servers in future, you can simply use different database names for each new server like dbispconfig2 and so on.
Yes, I realised that I can't just replicate blindly, or the "dbispconfig" databases on each node would get nuked by the replication - that's why I'm posting this question. As I came home from work, I did also think of the idea of renaming each node's database differently, so that there are no conflicts. And, I guess, as a bonus, you get every node's database replicated on every other node, as a kind of "backup" - though this could, in time, prove to be an issue should the number of nodes in your cluster becomes much larger (e.g. a hundred nodes would have a hundred ISPConfig databases on every single node). So I did a bit more digging with Google search, and there is, apparently, a "binlog-ignore-db" configuration parameter which allows you to tell a master to ignore a database - it won't log changes to it, so it won't get replicated - and a similar "replicate-ignore-db" parameter, which does the ignoring on the client's side (so the master might send out a log of changes to that database, but the client will ignore it). Thus, if I'm understanding this correctly, then adding "binlog-ignore-db = dbispconfig" should have the multi-masters all ignore changes to "dbispconfig" and not log these changes with other nodes (and, for good measure, one could also use "replicate-ignore-db = dbispconfig" too - though if the first option is working on all masters, this shouldn't be necessary but it won't hurt to double-up the protection and make very sure that it won't get overwritten). I'll have to experiment. Using Galera has great potential - for example, my earlier problem with the APS installer using "localhost" wouldn't matter anymore, as the databases will be on "localhost" (and the improved performance for that).
Just thinking on this some more. I've never run my cluster with multiple DB servers yet. How does ISPConfig normally process that? I see that when you create a new database, there is a "server" field. So does this mean that the database is only created on that single specified node? Also, I've noticed, in general use, that every node seems to have a dummy database when one is created. I presume this has something to do with ensuring that all nodes have the necessary database users and privileges. I'm just trying to work out how ISPConfig's normal operation would work with Galera. Because, with it, all the data is synchronised between nodes automatically. So a write to any node ends up replicated on all the nodes. So, to be more specific with my question, if I create a new database and database user on one node and Galera automatically syncs up all the other nodes, does ISPConfig detect such replication - that the databases and users already exist, thanks to replication - and not bother to create any dummy databases and database users? Because what won't work, of course, is if I create a new database and database user on one node and it gets replicated to all the other nodes... and then ISPConfig attempts to do its own syncing of databases / users and starts overwriting (or at least needlessly repeating the creation of) the newly created database / user. I presume I'm not the first person to have ever used database replication with ISPConfig and you've already thought of this. But before I proceed, I need to know how ISPConfig normally deals with these things, to work out whether ISPConfig and Galera are, in fact, compatible with each other.
Okay, reading some more, I see in the HowToForge tutorial for database clustering that the configuration " slave_skip_errors = 1007,1008,1050, 1396" is used, to simply skip any "database already created" / "can't drop non-existent database" errors on the slave. Would this do it? So ISPConfig will try to create those dummy databases and users, but it'll fail because they already exist (or, for DROPs, they already don't exist) and we just skip these errors?
