Vicidial mysql replication

[doublepositive]

Good Afternoon,

We are attempting to setup a 2 node vicidial database cluster with active - passive mysql replication. Our configuration is as follows:

Corosync and Pacemaker for clustering
Cluster resource: Shared virtual IP address
mysql replication (mixed mode) for active - passive cluster

Is row based replication (binlog_format=ROW) the best option for vicidial mysql replication? What recommendations does the vicidial community have to implement this setup? We would also like to know how to recover quickly when the primary node fails.

Thank you

[mflorell]

The only option for MySQL replication with VICIdial is Master/slave using MyISAM tables. Row-level replication WILL NOT WORK. You have to have all dialers pointed to the Master server. You also can point reports to use a MySQL slave server, but not any production resources.

VICIdial is a Real-time application, and it requires a query cache that MyISAM uses. VICIdial is NOT a transactional system, so the other forms of MySQL replication will not work with VICIdial.

[doublepositive]

Thank mflorell.

What ideas/recommendations on proven database HA techniques for vicidial?

[mflorell]

We have several clients that have set up MySQL slaves to switch to master when the master becomes unavailable. Usually this is a "warm" transfer that is done manually in a matter of seconds. But we also have a couple clients that set up "hot" failover that was automated with heartbeat processes.

It's important to remember with MySQL Master/slave replication that you can have multiple slave servers to one master. One of our clients had 3 slave servers, with one for reporting, one for running backups on, and one for failover.

[doublepositive]

Can you point me to some documentation/best practices to setup a 2 node cluster using mysql MyISAM tables?

[doublepositive]

Row level will not work. Why? What replication format works best with Vicidial? Row based, Statement based or mixed?

Thank you for your assistance.

[mflorell]

Master/Slave replication is the ONLY method that will work with VICIdial.

As I stated before, VICIdial is NOT a transactional application, it is a Real-Time application, and it REQUIRES queries to be executed in order. Because of this, transactional methods of replication WILL NOT WORK for a VICIdial database back-end.

[doublepositive]

We want to accomplish the following with our master/slave setup:
1. Slave(s) is used for failover.
2. Slave takes over role as master in the event the master fails.


What is the optimal way to setup replication to accomplish this goal?

Thank you.

[mflorell]

We don't have any specific set of instrucctions for failover, it really depends on the customer equipment and requirements.

As for setting up master/slave replication, there are literally dozens of tutorials online for how to do that.

[williamconley]

We want to accomplish the following with our master/slave setup:
1. Slave(s) is used for failover.
2. Slave takes over role as master in the event the master fails.


What is the optimal way to setup replication to accomplish this goal?

Thank you.

As stated earlier: You must set up a heartbeat-based system to modify the roles and then modify the system so all resources now use the new master after failover (hot-swap the IP of the master/slaves so everyone now points at the NEW master without a change *or* modify all the clients to point to the new master's IP without changing it). There are plenty of schemes available to do this, but your level of expertise is a deciding factor.

*BUT* your best bet (and return on investment) is to bulletproof the primary DB server to the best of your ability first. RAID10. Extra drives. Redundant power supply in the case and UPS on each power supply outside the case.

Then a plain-jane slave server for reporting with the possibility of a manual failover method is generally more than enough. Honestly: If your DB server is going to die, you already have problems that may require shutting down the system. A good server board will kill memory if it offends, for instance. Redundant PS, UPS? If you lost the DB server with all this in place, something *really* bad must have happened. LOL

[doublepositive]

@williamconley

We are going to build our database server based on your recommendations. We are going to use master/slave replication. What replication format works best with Vicidial? Row based, Statement based or mixed?

[williamconley]

The only option for MySQL replication with VICIdial is Master/slave using MyISAM tables. Row-level replication WILL NOT WORK. You have to have all dialers pointed to the Master server. You also can point reports to use a MySQL slave server, but not any production resources.

VICIdial is a Real-time application, and it requires a query cache that MyISAM uses. VICIdial is NOT a transactional system, so the other forms of MySQL replication will not work with VICIdial.

I realize the bold inside a quote is not as "stand-outish" as it could be so:
"Row-level replication WILL NOT WORK."

Note that while we have a yuuuge amount of experience with Vicidial: Mflorell built this system from the ground up over (?) 10+ years. If he answers a questions, it's not something to take lightly.

[jkalin1]

I believe the question is which binary logging format is most compatible with Vicidial. Is that correct doublepositive? It sounds like mflorell has indicated row-based replication will not work correctly with Vicidial. That leaves you with statement-based or mixed. I would think you would want statement-based replication as with mixed-format logging a statement based log is used in general, but the log may be switched to row-based in some cases which may put you back into a bad situation with row based/level (as per mflorell).

Hopefully mflorell/williamconley can confirm that statement-based (or mixed?) is the preferred logging format.

[mflorell]

yes, a statement-based bin-log will work.

[jkalin1]

mflorell, is statement-based logging preferred over mixed due to the fact that that mixed may resort to row-based which has some incompatibilities with Vicidial?

Appreciate your clarification!