Backup MySQL Connection

[apexpro]

We were able to create multiple redundant copies of the backend MySQL database for WHMCS that are in perfect sync for redundancy purposes.

 

Now comes the second part. What would be the best approach to having the core WHMCS scripting load 1 DB, if that's down, then load another and possibly a third?

 

I assume all MySQL calls are centralized into one component so just doing a MySQL call followed by connection errors then backups would work?

 

I tried to find an add on but no luck.

 

Haven't looked at the code yet but figured would give this a try.

[twhiting9275]

The problem you're going to have here is when one is down, the other will take data . Then you have to figure out which is newer, and which is not.

A pretty simplistic way to do what you're after here would be as follows

#1: Create three identical configuration.php files. One as the main (configuration.php), one as the secondary (configuration-bak.php), one as the backup main (configuration-main.php)

#2: Create a php script which checks to see if main is up

#3: If main is up, do nothing

#4: If main is not up, use php script to check values in configuration-bak.php.

#5: If configuration-bak.php is up, use fopen to grab the contents of configuration-bak.php , then rewrite configuration.php with those contents (fwrite)

#6: Check configuration-main.php repeatedly until it's back, then sync and reverse the steps of #5

 

You'd need to run this every 60 seconds via cron.

 

 

There's really no default way inside of WHMCS to replicate the db like you're after, right now

[apexpro]

Appreciate the reply but that wont work. First off, means a minimum of 60 seconds of downtime but has many other draw backs.

 

The data isn't an issue. We are handling two way replication now across multiple servers ensuring that the most current updates get distributed across multiple databases in real time. Should one fail, it will be updated once it comes live again but that's only the backend data, now the challenge is the front end interface.

 

I gather that the MySQL commands result from a single point, I'll have to identify that.

 

Seems the best approach would be

 

- Connect to DB

- If DB is down, default to backup

- If backup is down, default to third

 

- Commit changes / request

- If fails, reload secondary database

- If secondary fails, revert to third

 

Since it's all on the php side, php makes it easy to recognize DBI failed connections and errors in the request / sneers / updates.

 

This will provide realtime uptime, just have to ensure that there is only one point in which MySQL activity happens.

 

With that, we can create multiple instances of WHMCS across several front end php web servers, load balanced with sticky connections, so have the front end and back end covered. Just have to get past this one challenged and wanted others feedback.