High availability with a MariaDB Galera MySQL cluster with ProxySQL Tutorial

Databases MySQL/MariaDB
, , , ,
1

How to get high availability with a MariaDB Galera MySQL cluster and ProxySQL

Installing and configuring MariaDB Galera

MariaDB Galera allows you to create a cluster of multiple MySQL servers for a high availability setup. This is a tutorial on how to get MariaDB installed and configured with ProxySQL on CentOS Linux servers.

Ideally it’s best to have an odd number of MySQL servers in this arrangement to prevent a split-brain condition if connectivity loss occurs among some of the servers.

To reduce the expense of having a 3rd MySQL database server, in this tutorial we will use 2 database servers and Galera Arbitrator to act as a 3rd node. Galera Arbitrator is a light-weight member of the cluster that participates in voting, but not in the actual database data replication. Galera Arbitrator could be installed on an existing server such as a web server, or a standalone VPS with minimal resources.

In this example, this is what the layout will look like:

MariaDB%20Galera

On MySQL1 and MySQL2 servers we’ll install MariaDB (a MySQL drop-in replacement):

  1. Create /etc/yum.repos.d/MariaDB.repo file with the contents of:

  2. [mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

  3. Install MariaDB by running: yum install MariaDB-server MariaDB-client

  4. To configure the Galera cluster, open the file: /etc/mysql/conf.d/galera.cnf and the following:

  5. [mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster_name"
wsrep_cluster_address="gcomm://mysql1_ip,mysql2_ip,arbitrator_ip"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="this_server_ip"
wsrep_node_name="this_server_name"

    Replace the galera_cluster_name with a name you choose for your cluster. Replace mysql1_ip , mysql2_ip and arbitrator_ip with the actual IPs of those servers. Replace this_server_ip and this_server_name with the IP and name of the server you are configuring.

  6. Galera uses these network ports:

    • 3306 for MySQL

    • 4567 for Galera cluster replication traffic (both UDP transport and TCP on this port)

    • 4568 for incremental state transfer

    • 4444 for all other state snapshot transfer

      To allow these ports in CentOS run the following commands:

      firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4567/udp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --reload

  7. Make sure rsync is installed by running yum install rsync

  8. Now, we will bring up the first node in our cluster, to do so, make sure mysql is not running and run this command: galera_new_cluster

  9. You should now be able to run this MySQL query which shows the number of nodes present in the cluster: mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

    The output should look something like this:

    +--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

  10. To bring up the 2nd node, run this command on the 2nd MySQL server: systemctl start mysql

You should now see 2 nodes present in the cluster:

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

Output:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

  1. Now that we verified that our cluster is running, enable MySQL to start up automatically by running this command on both MySQL servers: systemctl enable mysql

  2. To configure the Galera Arbitrator, on the server you wish to install the Arbitrator, make sure the same ports are enabled that we enabled on the MySQL servers.

  3. Install MariaDB like we did in steps 1 and 2, but do not edit the configuration file.

  4. Edit the file /etc/sysconfig/garb with the following contents

    # A comma-separated list of node addresses (address[:port]) in the cluster
GALERA_NODES="mysql1_ip:4567,mysql2_ip:4567,arbitrator_server_ip:4567"

# Galera cluster name, should be the same as on the rest of the nodes.
GALERA_GROUP="galera_cluster_name"

    Replace mysql1_ip, mysql2_ip and arbitrator_server_ip with the actual IPs, but be sure to leave the port numbers ( :4567 )

  5. Enable and start the Galera Arbitrator:

    systemctl enable garb
systemctl start garb

  6. Now on one of the MySQL servers check to make sure we have 3 nodes present in the cluster:

    mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

    Output should be:

    +--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

  7. You may test if the cluster is fully functioning by create a database one of the MySQL servers and check if it exists on the 2nd server.

Installing and configuring ProxySQL

Now that we have a functioning Galera cluster, we are going to install and configure ProxySQL to load balance between the two MySQL servers and automatically fail over if one of them fails.

  1. To install ProxySQL on the web server run:

    cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

    Then run:

    yum install proxysql

  2. Start ProxySQL: service proxysql start

  3. We will configure ProxySQL through the ProxySQL Admin Interface:

    mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

  4. In the Admin interface we are going to add MySQL1 and MySQL2 servers:

    INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'mysql1_ip',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'mysql2_ip',3306);

    Replace mysql1_ip and mysql2_ip with actual IPs.

    Then run these commands to save these servers to ProxySQL:

    LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

  5. Next, we will need a monitor SQL user that ProxySQL will use to monitor the uptime of the MySQL servers. To create this MySQL user, on one of the MySQL servers run this query:

    GRANT SELECT ON `information_schema`.`GLOBAL_STATUS` TO 'cluster_monitor'@'%' IDENTIFIED BY 'monitor_user_password';

    replace monitor_user_password with the desired password for this user. Save this password, we will use in the next step.

  6. Back to the ProxySQL Admin interface on the Web server, we will need to configure the monitor user and password that ProxySQL will use by running these queries:

    UPDATE global_variables SET variable_value='cluster_monitor' WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='monitor_user_password' WHERE variable_name='mysql-monitor_password';

    Use the actual user and password for the monitor user password you created.

    Save and load this configuration by running:

    LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

  7. Any SQL users that will connecting to the MySQL servers through the Proxy, will need to be added to ProxySQL. For example, we will add the root user:

    INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','root_user_password',1);

    Save users to ProxySQL by running these queries:

    LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

  8. To check the status of the database servers used by ProxySQL, you could run these queries from the Admin interface:

    This will show you the servers in ProxySQL:

    SELECT * FROM mysql_servers;

    To check the MySQL connection and ping log:

    SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

  9. From the web server, to use ProxySQL, establish MySQL connection to 127.0.0.1 on port 6033.

Important Notes:

  • By default, to use ProxySQL establish MySQL connections to 127.0.0.1 on port 6033.
  • Galera cluster only works with the Innodb storage engine. Databases using the MyISAM storage engine will not automatically replicate.
  • Any database users connecting through the Proxy will need to be added to ProxySQL.
  • Additional documentation on ProxySQL: https://github.com/sysown/proxysql/wiki