MariaDB/MySQL

Previously we wrote about MYSQL, to give our customers a better understanding of what MYSQL is.
Now – partnering with Jelastic – we offer many new features including MariaDB/MySQL database clusterization which is an obligatory requirement for highly loaded production applications. Togglebox introduced out-of-the-box clustering for MariaDB/MySQL to make applications highly available by default. This database clusterization ensures not only data availability but also high performance.

MariaDB/MySQL Clusters provides many benefits:

  • High availability with pre-configured replication options – Master-Slave, Master-Master, Galera
  • Efficient load balancing – Two ProxySQL nodes for load balancing with automatic splitting of read/write requests
  • Automated failover – The database nodes that are temporarily unavailable or have high latency are automatically excluded then re-added once connection is restored
  • Scalability and Auto-Discovery – New nodes are added during horizontal scaling, and connected with all required adjustments being applied automatically

Enable Automatic Clustering for Databases

The easy-to-follow steps below will help you learn how to activate auto-clustering for your MariaDB and MySQL databases.

1) Click on NEW ENVIRONMENT at the dashboard then choose the MariaDB or MySQL database.

2) Activate the Auto-Clustering button.

The dropdown with different replication schemes will appear:

  • MariaDB – Master-Slave, Master-Master, and Galera
  • MySQL – Master-Slave and Master-Master
3) Choose Auto-Clustering Options

Choose the replication type then add the required number of nodes.

4) Highly Available ProxySQL Load Balancer

MariaDB/MySQL has two ProxySQL nodes enabled by default. You can exclude them from the cluster topology before installation with the respective switch.
Keep in mind that you won’t be able to do that afterward.

5) Database Cluster Access Credentials

If you wish to override the automatically generated database access credentials, do so in these three simple steps:
5.1) Click on the Variables button.

5.2) Then press Add to provide custom user’s database credentials via two variables DB_USER and DB_PASS as follows:

5.3) Finally, Press Apply and your custom credentials will be mailed to you once cluster installation is successful. This process is the same for access to the PHP MyAdmin at Master Node and database cluster Entry Point.

Cluster Horizontal Scaling

Scaling the master-slave/master-master topologies with an extra database node is created via cloning an existing slave node. Once this cloning procedure is completed the database grabs data via binlog replay. This algorithm guarantees the binlog will never expire and horizontal scaling is quick.

Cluster Layers Isolation

You can define what external application layers you will expose, either all or the entry point proxy layer only. Turn the SLB access switch for each layer and click Create.
MYSQL
That’s all! No configurations required, the cluster is ready.

Database Cluster Access Information

You will be notified by email when your installation is successful. The following updates will occur:

    • PHP MyAdmin at Master Node web administration interface with credentials to access the database server for interactive management.
    • Entry Point for Connections to MySQL Cluster – hostname and credentials for connecting an application to the database cluster. Nodes form a proxy layer referred to as the entry point for the database cluster with hostname as follows:proxy.${envName}.${platformDomain}.
  • Cluster Orchestrator Panel – Use the received credentials to access admin panel of cluster Orchestrator installed on ProxySQL node, that provides a possibility to review the cluster topology information: slick visualization of topologies, replication problems if there are any, read/write distribution, state of health check-ups and autodiscovery of newly added DB nodes, etc.

Master-Slave MariaDB/MySQL Replication

Master-slave provides good read performance but not good redundancy. You increase read performance with each slave you add. The write latency is low because the write is recorded locally. Here is a list of the benefits of choosing Master-slave:

  • High performance for read requests
  • Database backup does not impact master instance
  • Reading requests do not affect the master’s performance

Master-Master MariaDB/MySQL Replication

Master-master operates with two master nodes simultaneously. Master-master provides automatic failover, increased and much improved read performance for every slave.

MariaDB Galera

Galera cluster offers good redundancy, increases read and write performance, but cluster size is limited. It is plug and play, flexible, and user-friendly.
Adding nodes to a cluster is fully automated. You can removed failed or unneeded nodes from the cluster.

The Master-Slave and the Master-Master would require application support. While, Galera clusters act similar to a a stand alone database requiring no code changes.