Change ispconfig db name in multiserver setup

Hi,
I'm not sure if this is a strictly developer issue, but probably I'll get a more technical answer here.

What is the best way to change the server address and the database name for each/most servers in a multiserver ispconfig setup? Is it enough to change the config file and reconfigure services? What it I have custom config files for most of the services?​

This thing seems quite risky. Why would I want to do that? It's easy to explain.
I've been running ispconfig for the last 10 (+?) years. So now I'm running a quite involved multiserver setup over multiple DC. Despite a shrinking sale price and customer numbers, they (the customers) have grown much more afraid and sensitive to any sort of down time oin the service.
Unfortunately, I've been experiencing an increased number of incidents due to mysql issues over the last years. It generally works but when it does not, a lot of things break, all together. To contrast this trend, I am currently using multiple master-master mysql replicated servers. This works most of the time, except when it does not and then it's harder to fix issues in a classic master-master mysql cluster.

Things have got quite complicated with time, specially on the mailserver(s) side of the business so it's now long overdue to simplify the setup.

I'm looking into consolidating all the "dbispconfig" databases in a "single multiserver setup". This would be a galera cluster, geo-replicated over multiple DC and separated from the rest of the hosting db servers (they continue to have a localhost based not replicated database as localhost) .

I hope this way to reduce the number of errors and increase stability and reduce customer complaints and staff time spent on fixing servers (that would be only my time

Apart from the technical question above? Do you think this is a good idea? I can't find any other options, really.

Thanks for your help in advance,
ispcomm.

 

Th0m, thanks for the reply, however I am not looking into moving all servers into one. I'm looking to moving all config databases into one highly available galera cluster.

I already have HA setup for the mail and dns servers using the server mirror functionality in ispconfig and some other magic like multi-site replication and load balancing the mail servers, separate smtp and imap servers etc.

What I'm targeting is specifically the mysql server on "localhost" for all the boxes which is not highly available to the level that I'd like (I have some master-master redundancy on some servers, which requires extra attention and which I'd like to make more resilient).

The solution I'd like to implement is a "separate" galera cluster with 3+ database servers (in fact at least 5), geo-distributed across multiple DC. HAproxy will take care for the connections from "localhost" to the galera cluster. Unfortunately, all my servers connect to the "dbispconfig" datatabase on "localhost" and I want to rename that to something else on each server before moving it to the galera cluster (for name-collision avoidance). That would be something like dbispconfig-mail1, dbispconfig-mail2 (etc).

Now, a few years ago I did some development for my ispconfig needs and I seem to remember that most stuff is placed in the main dbispconfig db on the master server and that the other servers connect to this database to pull updates for their own local databases. Updates are/were tagged with the server id.

However the name of the local database is "hardcoded" in a whole bunch of config files on each server (ftp, postfix, etc etc) plus at least two config files in ispconfig itself (the server and the interface parts).

My question is, what is the "safest" way to rename the database on each of the servers, before migrating it to the galera cluster? Would something like a forced update + reconfiguration of local services suffice ? What do I need to be careful about ?

 

so basically a central db cluster for the dbispconfig databases themselves.
i did this when i first started using ispconfig, and whilst everyone's requirements and use cases are different, i'd find it difficult to recommend anyone actually goes with this now.
again it's great when everything is working, but it's even more of a nightmare when it isn't. if your ispconfig servers don't have a local copy of their dbispconfig database then if anything happens to their link to that central db server, or that db cluster goes down, nothing will work properly. no-one will be able to send or access mail, as they won't be able to authenticate their email account, same for ftp and ssh users, and the control panel will be inaccessible.
at least with localhost databases, if anything happens, you might not be able to apply new changes straight away, but existing web/mail/db services should carry on as normal whilst you fix whatever's wrong with the master.

 

@nhybgtvfr : I see where you come from regarding the cluster usage. It's a single point of failure, but it's supposed to be way more available than a single server. That's a given and you should be able to achieve this. Connectivity to the cluster shall be the same as connectivity to the "internet". There should not be an instance where you only have one. You could even run a cluster node on the localhost as an option, in small installs and call it a day.

You're still talking about a cluster of mysql db here. This is what I'm trying to achieve by renaming my dbispconfig databases to something else. So I can put them in a "single" cluster.
However, only using localhost as db is not good. If anything happens to the localhost database, nothing works on that server. The local services (postfix, ftp etc) source their users directly from the DB. Stuff if cached in postfix but subject to mysql availability. This is there a local load balancer with primary and backup connections to alternative servers comes into play. That is also a single point of failure, however orders of magnitute simpler and more reliable than a mysql install. It has no disk activity, extremely small memory footprint etc etc.
If the localhost db (or master db) is lost on the ispconfig server, nothing can be updated on the whole multiserver setup. The ispconfig interface does not work either.

I agree with you, conditionally. Single server, on localhost is much easier, faster and probably has a higher availability per server. When things to bad, they do only on a subset of the accounts, which provides for a "plausible deniability" with the customer. Been there, done that.
The localhost setup works well for web servers. It does not provide sufficient assurances for mailservers. I'm targeting mostly the mailservers.
There are disadvantages to having a localhost setup, specially for a mail server. It requires constant 24/7 availability of support staff and very quick reaction time. The time to recover might be relevant after any "major" issue that is not fixed by a simple restart (for which I have monitors on the servers anyway).
I prefer a 5 node galera cluster to handling 20 localhost mysql instances. I think this will be an option that deserves some investigation. It comes with some bonus features like replicating common data among similar servers on the cluster. For example the mail-usage policy counters, the fail2ban blocked ip database and others. Call it a personal preference if you wish.
So, back to the original question: How do I rename the default "dbispconfig" database to something else on each server?

 

Reading back my last post above, it comes a little harsh. I'm sorry about that. It was not meant.

Yes, the two config.inc.php were easily found with grep.
But I guess I need to launch the update script on all involved servers and ask for permission fix in the master db and for reconfiguration of the local services.
Am I missing something else?

 

Hello ispcomm,
just stumbled upon your post and hope most of your problems are resolved or reduced by now.
If not, you may look into ProxySQL for the db-cluster. I'm using it on each app-server now for (really simple) R/W Splitting and LB on segregated so-called host groups on master-master XtraDB (= Galera+) Cluster(s).
Basically, all issues gone. Plus, more HA "built-in" (hg0 (r/w), hg1 (read-only), primary exists in both with different weights etc.). It's just fantastic.
All the best,
mb