Deploy a Replica Server with MariaDB Enterprise Server 10.2 on CentOS 7

These instructions detail the deployment of MariaDB Enterprise Server 10.2 on CentOS Linux 7 in a Replica Server configuration.

These instructions detail how to deploy a replica server in a MariaDB Replication deployment, which is suited for a transactional or OLTP workload that requires high availability (HA).

Prerequisites

These instructions assume that the primary MariaDB Server instance has already been deployed.

See the following resources for how to deploy a primary server:

MariaDB Platform Components

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

Component

Description

MariaDB Enterprise Server 10.2

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

MariaDB Enterprise Server Components

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

Component

Description

InnoDB

MariaDB Replication

  • It allows data to be replicated from a primary server to one or more replica servers.

  • It supports asynchronous and semi-synchronous replication.

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".

primary server

  • The server that is designated to receive write queries in a MariaDB Replication deployment.

  • Formerly known as a "master".

replica server

  • A server that replicates from a primary server using MariaDB Replication.

  • Formerly known as a "slave".

Installation

MariaDB Corporation provides a YUM package repository for CentOS Linux 7.

MariaDB Enterprise Server does not require any additional software to operate as a node in MariaDB Replication.

Install via YUM (RHEL/CentOS)

  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 YUM package repository.

    To configure YUM package repositories:

    $ sudo yum install wget
    
    $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
    
    $ echo "eeebe9e08dffb8a4e820cc0f673afe437621060129169ea3db0790eb649dbe9b  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.2"
    
  3. Install MariaDB Enterprise Server and package dependencies:

    $ sudo yum install MariaDB-server MariaDB-backup
    
  4. 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 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/mariadb-enterprise.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/

Configuring MariaDB

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

    Mandatory system variables and options for a Replica Server include:

    System Variable/Option

    Description

    server_id

    Sets the numeric Server ID for this MariaDB Enterprise Server. Must be unique in the deployment.

    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 a Replica Server include:

    System Variable/Option

    Description

    log_bin

    Set this option to enable the Binary Log, allowing the Replica Server to replace the Primary Server if it goes down.

    relay_log

    Sets the location for the Relay Log.

    log_slave_updates

    Sets the Replica Server to log updates from the slave thread to the Binary Log. Use this if you want to daisy chain Replica Servers.

    gtid_domain_id

    Sets the numeric domain identifier to use in Global Transaction ID's.

    gtid_ignore_duplicates

    Set when using multi-source replication to ignore Binary Log events with duplicate Global Transaction ID's.

    gtid_strict_mode

    Set to ensure that GTIDs are monotonically increasing..

    report_host

    Sets the IP address or hostname the Replica Server sends to the Primary Server during its registration.

    report_port

    Sets the port number the Replica Server sends to the Primary Server during its registration.

  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 RHEL, CentOS, and SLES, a good custom configuration file would be: /etc/my.cnf.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 mysqld, such as [mariadb] or [server].

    For example:

    [mariadb]
    
    # Server Configuration
    log_error                 = mariadbd.err
    innodb_buffer_pool_size   = 1G
    
    # Replication Configuration (Replica Server)
    log_bin                = mariadb-bin
    server_id              = 2
    log_slave_updates      = ON
    binlog_format          = ROW
    

Parallel Replication

Writes to the Primary Server that are group committed or logged with a Global Transaction ID in different replication domains can be applied on the Replica Server using parallel threads to improve performance.

System Variable/Option

Description

slave_parallel_threads

Sets the number of threads the Replica Server uses to apply replication events in parallel. Use a non-zero value to enable Parallel Replication.

slave_parallel_mode

Sets how the Replica Server applies replicated transactions.

Semi-Synchronous Replication

MariaDB Replication deployments that use MariaDB Enterprise Server 10.2 have the option of enabling semi-synchronous replication for Replica Servers through the semisync_slave plugin.

Mandatory additional options and system variables for Semi-Synchronous Replication:

System Variable/Option

Description

plugin_load_add

Set this option to plugin_load_add=semisync_slave in ES10.2 to load the semisync_slave plugin. This is not needed in ES10.3 and ES10.4.

rpl_semi_sync_slave_enabled

Set to enable semi-synchronous replication for a Replica Server.

Starting the Replica Server

Replica Servers are MariaDB Enterprise Servers that replicate binary logs from the Primary Server in a MariaDB Replication deployment. In order to start the Replica Server, we need to go through the following process:

  1. Take a backup of the Primary Server.

  2. Restore the backup to the Replica Server.

  3. Start MariaDB Enterprise Server on the Replica Server.

  4. Start MariaDB Replication on the Replica Server.

Backing Up the Primary Server

Before the Replica Server can be started, its data needs to be consistent with the Primary Server. To ensure that it is consistent, take a backup of the Primary Server:

The instructions below show how to perform a backup using MariaDB Backup. For more information about backing up and restoring the database, please see the Recovery Guide.

  1. On the Primary Server, take a full backup:

    $ sudo mariabackup --backup \
          --user=mariabackup_user \
          --password=mariabackup_passwd \
          --target-dir=/data/backup/replica_backup
    

    Confirm successful completion of the backup operation.

  2. On the Primary Server, prepare the backup:

    $ sudo mariabackup --prepare \
          --target-dir=/data/backup/replica_backup
    

    Confirm successful completion of the prepare operation.

Restoring the Backup on the Replica Server

To make the Replica Server consistent with the Primary Server, we restore the new backup on the Replica:

  1. On the Primary Server, copy the backup to the Replica Server:

    $ sudo rsync -av /data/backup/replica_backup replicaserver:/data/backup/
    
  2. On the Replica Server, restore the backup using MariaDB Backup.

    On MariaDB Enterprise Server 10.2:

    $ sudo mariabackup --copy-back \
       --target-dir=/data/backup/replica_backup
    
  3. On the Replica Server, fix the file permissions of the restored backup:

    $ sudo chown -R mysql:mysql /var/lib/mysql
    
  4. After the backup has been restored on the Replica Server, we can start MariaDB Enterprise Server, and confirm that the backup is valid by ensuring that the server starts properly and that all data was properly imported.

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.

CentOS Linux 7 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

Starting MariaDB Replication

The last step is to set up MariaDB Replication.

  1. Get the GTID position that corresponds to the restored backup.

    If the backup was taken with MariaDB Backup, this position will be located in xtrabackup_binlog_info:

    $ cat xtrabackup_binlog_info
    mariadb-bin.000096 568 0-1-2001,1-2-5139
    

    The GTID position from the above output is 0-1-2001,1-2-5139.

  2. Connect to the Replica Server using MariaDB Client using the root@localhost user account:

    $ sudo mysql
    
  3. Set the gtid_slave_pos system variable to the GTID position:

    SET GLOBAL gtid_slave_pos='0-1-2001,1-2-5139';
    
  4. Execute the CHANGE MASTER TO statement to configure the Replica Server to connect to the Primary Server at this position:

    CHANGE MASTER TO
       MASTER_USER = "repl",
       MASTER_HOST = "192.0.2.1",
       MASTER_PASSWORD = "repl_passwd",
       MASTER_USE_GTID=slave_pos;
    

    The above statement configures the Replica Server to connect to a Primary Server located at 192.0.2.1 using the repl user account. These account credentials must first be configured on the primary server.

  5. Start replication using the START SLAVE command:

    START SLAVE;
    

    The above statement configures the Replica Server to connects to the Primary Server to retrieve new binary log events and replicate them into the local database.

Testing

When you have the Replica Servers up and running, you should test each to ensure that they are working and that there weren't any issues during startup. You should also ensure that the Server is replicating data from the Primary Server.

Checking Replication Status

  1. Connect to the Replica Server using MariaDB Client using the root@localhost user account:

    $ sudo mysql
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 38
    Server version: 10.2.33-8-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. Execute the SHOW SLAVE STATUS statement.

    This statement returns a lot of columns, but the important ones are Slave_IO_Running and Slave_SQL_Running. If both of those say Yes, then replication is running correctly.

    SHOW SLAVE STATUS\G
    
    *************************** 1. row ***************************
                    Slave_IO_State: Waiting for master to send event
                       Master_Host: 178.128.150.186
                       Master_User: repl
                       Master_Port: 3306
                     Connect_Retry: 60
                   Master_Log_File: mariadb-bin.000003
               Read_Master_Log_Pos: 4919
                    Relay_Log_File: mysqld-relay-bin.000003
                     Relay_Log_Pos: 3913
             Relay_Master_Log_File: mariadb-bin.000003
                  Slave_IO_Running: Yes
                 Slave_SQL_Running: Yes
                   Replicate_Do_DB:
               Replicate_Ignore_DB:
                Replicate_Do_Table:
            Replicate_Ignore_Table:
           Replicate_Wild_Do_Table:
       Replicate_Wild_Ignore_Table:
                        Last_Errno: 0
                        Last_Error:
                      Skip_Counter: 0
               Exec_Master_Log_Pos: 4919
                   Relay_Log_Space: 5138
                   Until_Condition: None
                    Until_Log_File:
                     Until_Log_Pos: 0
                Master_SSL_Allowed: No
                Master_SSL_CA_File:
                Master_SSL_CA_Path:
                   Master_SSL_Cert:
                 Master_SSL_Cipher:
                    Master_SSL_Key:
             Seconds_Behind_Master: 0
     Master_SSL_Verify_Server_Cert: No
                     Last_IO_Errno: 0
                     Last_IO_Error:
                    Last_SQL_Errno: 0
                    Last_SQL_Error:
       Replicate_Ignore_Server_Ids:
                  Master_Server_Id: 1
                    Master_SSL_Crl:
                Master_SSL_Crlpath:
                        Using_Gtid: No
                       Gtid_IO_Pos:
           Replicate_Do_Domain_Ids:
       Replicate_Ignore_Domain_Ids:
                     Parallel_Mode: conservative
                         SQL_Delay: 0
               SQL_Remaining_Delay: NULL
           Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
                  Slave_DDL_Groups: 17
    Slave_Non_Transactional_Groups: 0
        Slave_Transactional_Groups: 7
    

Testing Replication

  1. Connect to the Primary Server using MariaDB Client using the root@localhost user account:

    user@primary-server$ sudo mysql
    
  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 the Replica Server and start MariaDB Client:

    user@cluster-node2$ sudo mysql
    
  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 Replica Server, allowing both Servers to return the same data.

Next steps: