Deploy an Enterprise Cluster Node with MariaDB Enterprise Server 10.4 on Ubuntu 16.04 LTS

These instructions detail the deployment of MariaDB Enterprise Server 10.4 on Ubuntu 16.04 LTS in an Enterprise Cluster Node configuration.

These instructions detail how to deploy a Galera Cluster node, which is suited for a transactional or OLTP workload that requires high availability (HA).

MariaDB Platform Components

These instructions detail the deployment of the following MariaDB Platform components:

Component

Description

MariaDB Enterprise Server 10.4

  • It is a 100% Open Source modern SQL database.

MariaDB Enterprise Cluster

MariaDB Enterprise Server Components

These instructions detail the deployment of the following MariaDB Enterprise Server components:

Component

Description

InnoDB

Galera Cluster

  • It provides virtually synchronous multi-primary replication for multiple MariaDB Enterprise Servers.

  • It allows any node to handle reads and writes.

  • It replicates writes to every node in the cluster.

Term Definitions

Term

Definition

row database

  • A database where all columns of each row are stored together.

  • Best suited for transactional and OLTP workloads.

  • Also known as a "row-oriented database".

Installation

MariaDB Corporation provides a APT package repository for Ubuntu 16.04 LTS.

MariaDB Enterprise Cluster requires that you install an additional package for Galera Cluster to use the Server as a Cluster Node. The enterprise build of Galera Cluster 4 is available for MariaDB Enterprise Server 10.5 and 10.4. Galera Cluster 3 is available for MariaDB Enterprise Server 10.3 and 10.2.

Install via APT (Debian/Ubuntu)

  1. Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for customer_download_token in the following directions.

  2. Configure the APT package repository.

    To configure APT package repositories:

    $ sudo apt install wget
    
    $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
    
    $ echo "957bc29576e8fd320fa18e35fa49b5733f3c8eeb4ca06792fb1f05e089c810ff  mariadb_es_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_es_repo_setup
    
    $ sudo ./mariadb_es_repo_setup --token="customer_download_token" --apply \
       --mariadb-server-version="10.4"
    
    $ sudo apt update
    
  3. Install MariaDB Enterprise Server and package dependencies:

    $ sudo apt install mariadb-server mariadb-backup
    
  4. Install Galera.

    To install MariaDB Enterprise Cluster for MariaDB Enterprise Server 10.4, install the enterprise-build of Galera Cluster 4:

    $ sudo apt install galera-enterprise-4
    
  5. Configure MariaDB.

    Installation only loads MariaDB Enterprise Server to the system. MariaDB Enterprise Server requires configuration before the database server is ready for use.

Configuration

MariaDB Enterprise Server can be configured in the following ways:

  • System variables and options can be set in a configuration file (such as /etc/my.cnf). MariaDB Enterprise Server must be restarted to apply changes made to the configuration file.

  • System variables and options can be set on the command-line.

  • If a system variable supports dynamic changes, then it can be set on-the-fly using the SET statement.

Configuration Files

MariaDB's packages include several bundled configuration files. It is also possible to create custom configuration files.

On Debian and Ubuntu, MariaDB's packages bundle the following configuration files:

  • /etc/mysql/my.cnf

  • /etc/mysql/mariadb.cnf

  • /etc/mysql/mariadb.conf.d/50-client.cnf

  • /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

  • /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf

  • /etc/mysql/mariadb.conf.d/50-server.cnf

  • /etc/mysql/mariadb.conf.d/60-galera.cnf

  • /etc/mysql/mariadb.conf.d/mariadb-enterprise.cnf

And on Debian and Ubuntu, custom configuration files from the following directories are read by default:

  • /etc/mysql/conf.d/

  • /etc/mysql/mariadb.conf.d/

Configuring MariaDB

  1. Determine which system variables and options you need to configure.

    Mandatory system variables and options for MariaDB Enterprise Cluster include:

    System Variable/Option

    Description

    wsrep_provider

    Sets the path to the wsrep Provider. For ES10.5 and ES10.4, this is the path to the libgalera_enterprise_smm.so file. For ES10.3 and ES10.2 this is the path to the libgalera_smm.so file.

    wsrep_cluster_address

    Sets the Group Communications back-end (usually gcomm://), followed by a comma-separated list of IP addresses or domain names for each Cluster Node. It is best practice to include all Cluster Nodes in this list.

    wsrep_cluster_name

    Sets the logical name for the cluster. Must be the same on all Cluster Nodes.

    binlog_format

    Set to ROW, MariaDB Enterprise Cluster does not support other Binary Log formats.

    innodb_autoinc_lock_mode

    Set to 2, MariaDB Enterprise Cluster does not support other auto-increment lock modes.

    wsrep_on

    Set to ON to enable MariaDB Enterprise Cluster.

    Useful system variables and options for MariaDB Enterprise Server include:

    System Variable/Option

    Description

    datadir

    Sets the path to the data directory. MariaDB Enterprise Server writes data files to this directory, including tablespaces, logs, and schemas. Change it to use a non-standard location or to start the Server on a different data directory for testing.

    bind_address

    Sets the local TCP/IP address on which MariaDB Enterprise Server listens for incoming connections. Bind to 0.0.0.0 to make the Server accessible through any network interface.

    port

    Sets the port MariaDB Enterprise Server listens on. Use this system variable to use a non-standard port or when running multiple Servers on the same host for testing.

    max_connections

    Sets the maximum number of simultaneous connections MariaDB Enterprise Server allows.

    thread_handling

    Sets how MariaDB Enterprise Server handles threads for client connections.

    log_error

    Sets the file name for the error log.

    innodb_buffer_pool_size

    Sets the amount of memory InnoDB reserves for the Buffer Pool.

    innodb_log_file_size

    Sets the size for each Redo Log file and innodb_log_files_in_group sets the number of Redo Log files used by InnoDB.

    innodb_io_capacity

    Sets the maximum number of I/O operations per second that InnoDB can use.

    Useful system variables and options for MariaDB Enterprise Cluster include:

    System Variable/Option

    Description

    wsrep_provider_options

    Use to set wsrep Provider Options, which are passed to the Galera Replication plugin, allowing you to fine tune replication performance.

    wsrep_slave_threads

    Sets the number of threads the Cluster Node uses to apply replication events.

    wsrep_sst_method

    Sets the script the Cluster Node uses to perform State Snapshot Transfers (SST). Set to mariabackup to use MariaDB Backup.

    wsrep_sst_auth

    Sets the user name and password the Cluster Node uses to authenticate itself when connecting to a donor node for a State Snapshot Transfer (SST).

  2. Choose a configuration file in which to configure your system variables and options.

    It not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, then it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as z-.

    • On Debian and Ubuntu, a good custom configuration file would be: /etc/mysql/mariadb.conf.d/z-custom-my.cnf

  3. Set your system variables and options in the configuration file.

    They need to be set in a group that will be read by mariadbd, such as [mariadb] or [server].

    For example:

    [mariadb]
    
    # Server Configuration
    log_error                 = mariadbd.err
    innodb_buffer_pool_size   = 1G
    
    # Cluster Configuration
    wsrep_provider           = /usr/lib64/galera-enterprise-4/libgalera_enterprise_smm.so
    wsrep_cluster_address    = gcomm://192.0.2.1,192.0.2.2,192.0.2.3
    wsrep_cluster_name       = TestCluster
    wsrep_on                 = ON
    
    binlog_format            = ROW
    innodb_autoinc_lock_mode = 2
    

MariaDB Replication Configuration

MariaDB Replication can be used along with MariaDB Enterprise Cluster. For example, you may want to replicate between two MariaDB Enterprise Clusters in different data centers.

Options and system variables to consider when using MariaDB Replication with MariaDB Enterprise Cluster:

System Variable/Option

Description

log_bin

Set this option to enable the Binary Log, allowing the Cluster Node to operate as a Primary Server.

log_slave_updates

Sets a Cluster node to write all replicated transactions to the Binary Log, so that they can be replicated by any Replica Servers.

server_id

Sets a numeric identifier for the Server, must be unique across all Servers being used in MariaDB Replication.

wsrep_gtid_mode

Sets the Cluster Node to automatically update the joiner node's wsrep_gtid_domain_id value as the gtid_domain_id.

wsrep_gtid_domain_id

Sets the domain identifier to use in Galera transactions.

gtid_domain_id

Sets the domain identifier to use in non-Galera transactions.

gtid_strict_mode

Sets MariaDB Enterprise Server to enforce global ordering for Global Transaction ID's.

Starting the Cluster

MariaDB Enterprise Cluster is composed of a series of MariaDB Enterprise Servers configured to connect to one another and form a Primary Component.

If the cluster is not running, bootstrap the Primary Component on a single Cluster Node, then start the other MariaDB Enterprise Servers normally. If the cluster is already running, start the Cluster Node as you would a normal MariaDB Enterprise Server.

Bootstrapping the Primary Component

Cluster Nodes start as non-operational components. When they start, they attempt to connect to other MariaDB Enterprise Servers listed in the wsrep_cluster_address system variable. When the Node finds the Primary Component, it requests a state transfer to update its local database and then becomes operational.

If the Cluster Node never finds the Primary Component, it remains non-operational and fails.

If the cluster is not running, you need to bootstrap the Primary Component on the first node you start.

Note, this is only done on the first node you start. Use the normal process management system to start all other nodes in the cluster.

When using systemd (most supported OSes), bootstrap the Primary Component using the sudo galera_new_cluster script:

$ sudo galera_new_cluster

When using Upstart (RHEL6/CentOS 6), bootstrap the Primary Component by starting the server process with the --wsrep-new-cluster option:

$ sudo service mysql start --wsrep-new-cluster

Starting the Server

MariaDB Enterprise Server includes configuration to start, stop, restart, enable/disable on boot, and check the status of the Server using the operating system default process management system.

Note that when the cluster is not running, you need to bootstrap the first Server.

Ubuntu 16.04 LTS uses systemd. You can manage the Server process using the systemctl command:

Operation

Command

Start

sudo systemctl start mariadb

Stop

sudo systemctl stop mariadb

Restart

sudo systemctl restart mariadb

Enable during startup

sudo systemctl enable mariadb

Disable during startup

sudo systemctl disable mariadb

Status

sudo systemctl status mariadb

Bootstrap a cluster node

sudo galera_new_cluster

Recover a cluster node's position

sudo galera_recovery

Testing

When you have the Primary Component bootstrapped on the first Cluster and all the other Cluster Nodes started, you should test to ensure that they are working correctly and that there aren't any issues with the cluster.

Checking Cluster

  1. Connect to any Cluster Node using MariaDB Client:

    $ sudo mariadb
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 38
    Server version: 10.4.13-7-MariaDB-Enterprise MariaDB Enterprise Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]>
    
  2. Check the wsrep_cluster_size status variable using the SHOW GLOBAL STATUS statement:

    SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';
    
    +--------------------+-------+
    | Variable_name      | Value |
    +--------------------+-------+
    | wsrep_cluster_size |     3 |
    +--------------------+-------+
    

    The size of the cluster should equal the current number of Cluster Nodes. If it is lower than expected, one or more of the Cluster Nodes either did not start correctly or are unable to connect to the Primary Component.

Testing Replication

  1. Connect to a Cluster Node and start MariaDB Client:

    user@cluster-node1$ sudo mariadb
    
  2. Create a database with the CREATE DATABASE statement:

    CREATE DATABASE IF NOT EXISTS test;
    
  3. Create a table with the CREATE TABLE statement:

    CREATE TABLE test.names (
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(255));
    
  4. Insert some data with the INSERT statement:

    INSERT INTO test.names(name) VALUES
       ("Walker Percy"), ("Kate Chopin"), ("William Faulkner"), ("Jane Austen");
    
  5. Confirm that the data was inserted properly with the SELECT statement:

    SELECT * FROM test.names;
    
    +----+------------------+
    | id | name             |
    +----+------------------+
    |  1 | Walker Percy     |
    |  2 | Kate Chopin      |
    |  3 | William Faulkner |
    |  4 | Jane Austen      |
    +----+------------------+
    
  6. Connect to different Cluster Node and start MariaDB Client:

    user@cluster-node2$ sudo mariadb
    
  7. Confirm that the data was replicated properly with the SELECT statement:

    SELECT * FROM test.names;
    
    +----+------------------+
    | id | name             |
    +----+------------------+
    |  1 | Walker Percy     |
    |  2 | Kate Chopin      |
    |  3 | William Faulkner |
    |  4 | Jane Austen      |
    +----+------------------+
    

    We can confirm that the DDL and DML were replicated from the other Cluster Node, allowing all Servers in the cluster to return the same data.

Next steps: