Deploy a Galera Cluster Node with MariaDB Community Server 10.6

Install via YUM (CentOS, RHEL)

1. Configure the YUM package repository.

   Installable versions of MariaDB Community Server are 10.6, 10.5, and 10.4. Prefix the version with mariadb- and pass the version string to the --mariadb-server-version flag to mariadb_repo_setup. The following directions reference 10.6.

   To configure YUM package repositories:

   $ sudo yum install wget
   

   $ wget https://r.mariadb.com/downloads/mariadb_repo_setup
   

   $ echo "30d2a05509d1c129dd7dd8430507e6a7729a4854ea10c9dcf6be88964f3fdc25 mariadb_repo_setup" \
       | sha256sum -c -
   

   $ chmod +x mariadb_repo_setup
   

   $ sudo ./mariadb_repo_setup \
      --mariadb-server-version="mariadb-10.6"
   

2. Install MariaDB Community Server and package dependencies:

   $ sudo yum install MariaDB-server MariaDB-backup
   

3. Configure MariaDB.

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

   See Configuration.

Install via APT (Debian, Ubuntu)

1. Configure the APT package repository.

   Installable versions of MariaDB Community Server are 10.6, 10.5, and 10.4. Prefix the version with mariadb- and pass the version string to the --mariadb-server-version flag to mariadb_repo_setup. The following directions reference 10.6.

   To configure APT package repositories:

   $ sudo apt install wget
   

   $ wget https://r.mariadb.com/downloads/mariadb_repo_setup
   

   $ echo "30d2a05509d1c129dd7dd8430507e6a7729a4854ea10c9dcf6be88964f3fdc25 mariadb_repo_setup" \
       | sha256sum -c -
   

   $ chmod +x mariadb_repo_setup
   

   $ sudo ./mariadb_repo_setup \
      --mariadb-server-version="mariadb-10.6"
   

   $ sudo apt update
   

2. Install MariaDB Community Server and package dependencies:

   $ sudo apt install mariadb-server mariadb-backup
   

3. Configure MariaDB.

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

   See Configuration.

Install via ZYpp (SLES)

1. Configure the ZYpp package repository.

   Installable versions of MariaDB Community Server are 10.6, 10.5, and 10.4. Prefix the version with mariadb- and pass the version string to the --mariadb-server-version flag to mariadb_repo_setup. The following directions reference 10.6.

   To configure ZYpp package repositories:

   $ sudo zypper install wget
   

   $ wget https://r.mariadb.com/downloads/mariadb_repo_setup
   

   $ echo "30d2a05509d1c129dd7dd8430507e6a7729a4854ea10c9dcf6be88964f3fdc25 mariadb_repo_setup" \
       | sha256sum -c -
   

   $ chmod +x mariadb_repo_setup
   

   $ sudo ./mariadb_repo_setup \
      --mariadb-server-version="mariadb-10.6"
   

2. Install MariaDB Community Server and package dependencies:

   $ sudo zypper install MariaDB-server MariaDB-backup
   

3. Configure MariaDB.

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

   See Configuration.

Configuration Files

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

On RHEL, CentOS, and SLES, MariaDB's packages bundle the following configuration files:

• /etc/my.cnf

• /etc/my.cnf.d/client.cnf

• /etc/my.cnf.d/mysql-clients.cnf

• /etc/my.cnf.d/server.cnf

And on RHEL, CentOS, and SLES, custom configuration files from the following directories are read by default:

• /etc/my.cnf.d/

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

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 Galera Cluster include:

   System Variable/Option	Description
   wsrep_provider	Sets the path to the wsrep Provider. 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 Galera Cluster does not support other Binary Log formats.
   innodb_autoinc_lock_mode	Set to 2, MariaDB Galera Cluster does not support other auto-increment lock modes.
   wsrep_on	Set to ON to enable MariaDB Galera Cluster.

   Useful system variables and options for MariaDB Community Server include:

   System Variable/Option	Description
   datadir	Sets the path to the data directory. MariaDB Community 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 Community 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 Community 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 Community Server allows.
   thread_handling	Sets how MariaDB Community 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 Galera 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 is 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 RHEL, CentOS, and SLES, a good custom configuration file would be: /etc/my.cnf.d/z-custom-my.cnf

   • 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-4/libgalera_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 Galera Cluster. For example, you may want to replicate between two MariaDB Galera Clusters in different data centers.

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

Bootstrapping the Primary Component

Cluster Nodes start as non-operational components. When they start, they attempt to connect to other MariaDB Community 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

Starting the Server

MariaDB Community 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.

For distributions that use systemd (most supported OSes), you can manage the Server process using the systemctl command:

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.6.17-MariaDB MariaDB 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
      ("Kai Devi"),
      ("Lee Wang"),
      ("Dani Smith"),
      ("Hao Nguyen");
   

5. Confirm that the data was inserted properly with the SELECT statement:

   SELECT * FROM test.names;
   

   +----+------------+
   | id | name       |
   +----+------------+
   | 1  | Kai Devi   |
   | 2  | Lee Wang   |
   | 3  | Dani Smith |
   | 4  | Hao Nguyen |
   +----+------------+
   

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  | Kai Devi   |
   | 2  | Lee Wang   |
   | 3  | Dani Smith |
   | 4  | Hao Nguyen |
   +----+------------+
   

   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:

• Deploy MaxScale with Galera Monitor and Read/Write Split Router

• Deploy MaxScale with Galera Monitor and Read Connection Router