Deploy a Replica Server with MariaDB Community Server 10.6
This page is part of MariaDB's Enterprise Documentation.
The parent of this page is: MariaDB Deployment
Topics on this page:
Overview
This procedure provides instructions detailing the deployment of MariaDB Community Server 10.6 in a Primary/Replica Topology as a replica server. If you are using MariaDB Enterprise Server 10.6, see "Deploy Primary/Replica Topology".
The Primary/Replica Topology is suited for transactional or OLTP workloads that require 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 Community Server Components
These instructions detail the deployment of the following MariaDB Community Server components:
Component | Description |
---|---|
| |
|
Term Definitions
Term | Definition |
---|---|
row database |
|
primary server |
|
replica server |
|
Installation
MariaDB Corporation provides package repositories for YUM (RHEL, CentOS), APT (Debian, Ubuntu), and ZYpp (SLES).
MariaDB Community Server does not require any additional software to operate as a node in MariaDB Replication.
Install via YUM (CentOS, RHEL)
Configure the YUM package repository.
Installable versions of MariaDB Community Server are
10.6
,10.5
,10.4
,10.3
, and10.2
. Prefix the version withmariadb-
and pass the version string to the--mariadb-server-version
flag to mariadb_repo_setup. The following directions reference10.6
.To configure YUM package repositories:
$ sudo yum install wget
$ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
$ echo "d4e4635eeb79b0e96483bd70703209c63da55a236eadd7397f769ee434d92ca8 mariadb_repo_setup" \ | sha256sum -c -
$ chmod +x mariadb_repo_setup
$ sudo ./mariadb_repo_setup \ --mariadb-server-version="mariadb-10.6"
Install MariaDB Community Server and package dependencies:
$ sudo yum install MariaDB-server MariaDB-backup
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)
Configure the APT package repository.
Installable versions of MariaDB Community Server are
10.6
,10.5
,10.4
,10.3
, and10.2
. Prefix the version withmariadb-
and pass the version string to the--mariadb-server-version
flag to mariadb_repo_setup. The following directions reference10.6
.To configure APT package repositories:
$ sudo apt install wget
$ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
$ echo "d4e4635eeb79b0e96483bd70703209c63da55a236eadd7397f769ee434d92ca8 mariadb_repo_setup" \ | sha256sum -c -
$ chmod +x mariadb_repo_setup
$ sudo ./mariadb_repo_setup \ --mariadb-server-version="mariadb-10.6"
$ sudo apt update
Install MariaDB Community Server and package dependencies:
$ sudo apt install mariadb-server mariadb-backup
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)
Configure the ZYpp package repository.
Installable versions of MariaDB Community Server are
10.6
,10.5
,10.4
,10.3
, and10.2
. Prefix the version withmariadb-
and pass the version string to the--mariadb-server-version
flag to mariadb_repo_setup. The following directions reference10.6
.To configure ZYpp package repositories:
$ sudo zypper install wget
$ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
$ echo "d4e4635eeb79b0e96483bd70703209c63da55a236eadd7397f769ee434d92ca8 mariadb_repo_setup" \ | sha256sum -c -
$ chmod +x mariadb_repo_setup
$ sudo ./mariadb_repo_setup \ --mariadb-server-version="mariadb-10.6"
Install MariaDB Community Server and package dependencies:
$ sudo zypper install MariaDB-server MariaDB-backup
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
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 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
Determine which system variables and options you need to configure.
Mandatory system variables and options for a Replica Server include:
System Variable/Option
Description
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
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.
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.
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.
Sets the maximum number of simultaneous connections MariaDB Community Server allows.
Sets how MariaDB Community Server handles threads for client connections.
Sets the file name for the error log.
Sets the amount of memory InnoDB reserves for the Buffer Pool.
Sets the size for each Redo Log file and innodb_log_files_in_group sets the number of Redo Log files used by InnoDB.
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
Set this option to enable the Binary Log, allowing the Replica Server to replace the Primary Server if it goes down.
Sets the location for the Relay Log.
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.
Sets the numeric domain identifier to use in Global Transaction ID's.
Set when using multi-source replication to ignore Binary Log events with duplicate Global Transaction ID's.
Set to ensure that GTIDs are monotonically increasing..
Sets the IP address or hostname the Replica Server sends to the Primary Server during its registration.
Sets the port number the Replica Server sends to the Primary Server during its registration.
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
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 (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 |
Sets the number of threads the Replica Server uses to apply replication events in parallel. Use a non-zero value to enable Parallel Replication. | |
Sets how the Replica Server applies replicated transactions. |
Semi-Synchronous Replication
MariaDB Replication deployments that use MariaDB Community Server 10.2 have the option of enabling semi-synchronous replication for Replica Servers through the semisync_slave plugin.
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.
On the Primary Server, take a full backup.
On MariaDB Community Server 10.4 and later:
$ sudo mariadb-backup --backup \ --user=mariabackup_user \ --password=mariabackup_passwd \ --target-dir=/data/backup/replica_backup
On MariaDB Community Server 10.3 and earlier:
$ sudo mariabackup --backup \ --user=mariabackup_user \ --password=mariabackup_passwd \ --target-dir=/data/backup/replica_backup
Confirm successful completion of the backup operation.
On the Primary Server, prepare the backup.
On MariaDB Community Server 10.4 and later:
$ sudo mariadb-backup --prepare \ --target-dir=/data/backup/replica_backup
On MariaDB Community Server 10.3 and earlier:
$ 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:
On the Primary Server, copy the backup to the Replica Server:
$ sudo rsync -av /data/backup/replica_backup replicaserver:/data/backup/
On the Replica Server, restore the backup using MariaDB Backup.
On MariaDB Community Server 10.4 and later:
$ sudo mariadb-backup --copy-back \ --target-dir=/data/backup/replica_backup
On MariaDB Community Server 10.3 and earlier:
$ sudo mariabackup --copy-back --target-dir=/data/backup/replica_backup
On the Replica Server, fix the file permissions of the restored backup:
$ sudo chown -R mysql:mysql /var/lib/mysql
After the backup has been restored on the Replica Server, we can start MariaDB Community 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 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.
For distributions that use systemd (most supported OSes), you can manage the Server process using the systemctl
command:
Operation | Command |
Start |
|
Stop |
|
Restart |
|
Enable during startup |
|
Disable during startup |
|
Status |
|
Starting MariaDB Replication
The last step is to set up MariaDB Replication.
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
.Connect to the Replica Server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Set the gtid_slave_pos system variable to the GTID position:
SET GLOBAL gtid_slave_pos='0-1-2001,1-2-5139';
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 therepl
user account. These account credentials must first be configured on the primary server.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
Connect to the Replica Server using 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.6.8-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)]>
Execute the SHOW SLAVE STATUS statement.
This statement returns a lot of columns, but the important ones are
Slave_IO_Running
andSlave_SQL_Running
. If both of those sayYes
, 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
Connect to the Primary Server using MariaDB Client using the
root@localhost
user account:user@primary-server$ sudo mariadb
Create a database with the CREATE DATABASE statement:
CREATE DATABASE IF NOT EXISTS test;
Create a table with the CREATE TABLE statement:
CREATE TABLE test.names ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255));
Insert some data with the INSERT statement:
INSERT INTO test.names(name) VALUES ("Walker Percy"), ("Kate Chopin"), ("William Faulkner"), ("Jane Austen");
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 | +----+------------------+
Connect to the Replica Server and start MariaDB Client:
user@cluster-node2$ sudo mariadb
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: