Deploy a Primary Server with MariaDB Community Server 10.4 on Ubuntu 20.04 LTS

These instructions detail the deployment of MariaDB Community Server 10.4 on Ubuntu 20.04 LTS in a Primary Server configuration.

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

Dependencies

These instructions assume that one or more replica MariaDB Server instances will also be deployed.

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

MariaDB Platform Components

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

Component

Description

MariaDB Community Server 10.4

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

MariaDB Community Server Components

These instructions detail the deployment of the following MariaDB Community 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 APT package repository for Ubuntu 20.04 LTS.

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

Install via APT (Debian/Ubuntu)

  1. Configure the APT package repository.

    To configure APT package repositories:

    $ sudo apt install wget
    
    $ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
    
    $ echo "2de6253842f230bc554d3f5ab0c0dbf717caffbf45ae6893740707961c8407b7 mariadb_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_repo_setup
    
    $ sudo ./mariadb_repo_setup \
       --mariadb-server-version="mariadb-10.4"
    
    $ 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.

Configuration

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

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 options and system variables for a Primary Server include:

    System Variable/Option

    Description

    log_bin

    Set this option to enable the Binary Log.

    server_id

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

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

    System Variable/Option

    Description

    binlog_format

    Sets Binary Log events as row-based, statement-based, or a mix of rows and statements.

    gtid_domain_id

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

    gtid_strict_mode

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

  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
    
    # Replication Configuration (Primary Server)
    log_bin          = mariadb-bin
    server_id        = 1
    binlog_format    = ROW
    

Group Commit

Group commit can help performance by reducing I/O.

If you would like to configure parallel replication on the Replica, then it is also required to configure group commit on the Primary.

System Variable/Option

Description

binlog_commit_wait_count

Sets the number of transactions that the server commits as a group to the binary log.

binlog_commit_wait_usec

Sets the number of microseconds that the server waits for transactions to group commit before it commits the current group.

Semi-Synchronous Replication

MariaDB Replication deployments that use MariaDB Community Server 10.2 have the option of enabling semi-synchronous replication for Primary Servers through the semisync_master 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_master in CS10.2 to load the semisync_master plugin. This is not needed in CS10.3 and later.

rpl_semi_sync_master_enabled

Set to enable semi-synchronous replication.

Useful system variables for Semi-Synchronous Replication:

System Variable/Option

Description

rpl_semi_sync_master_timeout

Sets the number of milliseconds which the Primary Server waits for commit acknowledgments from the Replica Server. If this threshold is crossed, the Primary Server reverts to asynchronous replication.

rpl_semi_sync_master_wait_point

Sets whether MariaDB Community Server waits for semi-sync acknowledgment after synchronizing the Binary Log or after committing in the storage engine.

Starting the Primary Server

The Primary Server is a MariaDB Community Server that provides binary logs to Replica Servers in a MariaDB Replication deployment. Once MariaDB Community Server has been configured, start the Primary Server, then start replication on the Replica Servers.

Ubuntu 20.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

Configure the Replication User

Replica Servers replicate from the Primary Server through client connections. In order for them to access the Primary Server, the Replica Servers require a user account with sufficient privileges.

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

    $ sudo mariadb
    
  2. Create the user account with the CREATE USER statement:

    CREATE USER repl@'%' IDENTIFIED BY 'repl_passwd';
    
  3. Grant the user account the REPLICATION SLAVE privilege with the GRANT statement:

    GRANT REPLICATION SLAVE ON *.* TO repl@'%';
    
  4. After you deploy the Replica Server, provide these account credentials to the CHANGE MASTER TO statement as the MASTER_USER and MASTER_PASSWORD options.

Testing

When you have the Primary Server up and running, you should test that it is working and that there weren't any issues during startup. You should also ensure that the Server is correctly configured before starting replication on the Replica Servers.

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

    $ sudo mariadb
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 38
    Server version: 10.4.13-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. Execute the SHOW MASTER STATUS statement to ensure that binary logging is enabled:

    SHOW MASTER STATUS;
    
    +--------------------+----------+--------------+------------------+
    | File               | Position | binlog_Do_DB | Binlog_Ignore_DB |
    +--------------------+----------+--------------+------------------+
    | mariadb-bin.000003 |     4919 |              |                  |
    +--------------------+----------+--------------+------------------+
    

    The statement returns a result-set similar to the above when Binary Logging is enabled and you can move on to setting up the Replica Servers. An empty set means that Binary Logging is disabled and must be enabled before the Server can operate as a Primary Server.

  3. Deploy one or more replicas.

  4. Test your Replica Servers.

Next steps: