MariaDB 10.5 : Replication2022/03/17

	Configure MariaDB Replication settings. This configuration is general Primary-Replica settings.
[1]	On all Hosts, Install and Start MariaDB Server, refer to here.
[2]	Change settings and create a user for replication on MariaDB Primary Host.

[root@www ~]#

vi /etc/my.cnf.d/mariadb-server.cnf

[mysqld]

# add follows in [mysqld] section : get binary logs

log-bin=mysql-bin

# define server ID (uniq one)

server-id=101

[root@www ~]#

systemctl restart mariadb

[root@www ~]#

mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.13-MariaDB-log 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.

# create user : set any password for 'password' section
MariaDB [(none)]> grant replication slave on *.* to repl_user@'%' identified by 'password'; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye

[3]	Change settings on MariaDB Replica Hosts.

[root@node01 ~]#

vi /etc/my.cnf.d/mariadb-server.cnf

[mysqld]

# add follows in [mysqld] section : get binary logs

log-bin=mysql-bin

# define server ID (uniq one)

server-id=102

# read only yes

read_only=1

# define own hostname

report-host=node01.srv.world

[root@node01 ~]#

systemctl restart mariadb

[4]	Get Dump-Data on MariaDB Primary Host. After getting Data, transfer it to Replica Hosts with [sftp] or [rsync] and so on.

# create a directory and get Backup Data

[root@www ~]#

mkdir /home/mariadb_backup

[root@www ~]#

mariabackup --backup --target-dir /home/mariadb_backup -u root

[00] 2022-03-17 13:56:11 Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
[00] 2022-03-17 13:56:11 Using server version 10.5.13-MariaDB
mariabackup based on MariaDB server 10.5.13-MariaDB Linux (x86_64)
[00] 2022-03-17 13:56:11 uses posix_fadvise().
[00] 2022-03-17 13:56:11 cd to /var/lib/mysql/
[00] 2022-03-17 13:56:11 open files limit requested 0, set to 1024
[00] 2022-03-17 13:56:11 mariabackup: using the following InnoDB configuration:
[00] 2022-03-17 13:56:11 innodb_data_home_dir =
.....
.....
[00] 2022-03-17 13:56:13         ...done
[00] 2022-03-17 13:56:13 Writing xtrabackup_info
[00] 2022-03-17 13:56:13         ...done
[00] 2022-03-17 13:56:13 Redo log (from LSN 47586 to 47598) was copied.
[00] 2022-03-17 13:56:13 completed OK!

[5]	On Replica Host, Copy back Backup Data of Primary Host and Configure replication settings. After starting replication, confirm the replication works normally to create test database or insert test data and so on.

# stop MariaDB and remove existing data

[root@node01 ~]#

systemctl stop mariadb

[root@node01 ~]#

rm -rf /var/lib/mysql/*

# transferred backup data

[root@node01 ~]#

ll mariadb_backup.tar.gz

-rw-r--r--. 1 root root 408867 Mar 17 13:58 mariadb_backup.tar.gz
[root@node01 ~]#

tar zxvf mariadb_backup.tar.gz

# run prepare task before restore task : OK if [completed OK]

[root@node01 ~]#

mariabackup --prepare --target-dir /root/mariadb_backup

mariabackup based on MariaDB server 10.5.13-MariaDB Linux (x86_64)
[00] 2022-03-17 14:04:21 cd to /root/mariadb_backup/
[00] 2022-03-17 14:04:21 open files limit requested 0, set to 1024
[00] 2022-03-17 14:04:21 This target seems to be not prepared yet.
.....
.....
2022-03-17 14:04:21 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=47586,47586
[00] 2022-03-17 14:04:22 Last binlog file , position 0
[00] 2022-03-17 14:04:22 completed OK!

# run restore

[root@node01 ~]#

mariabackup --copy-back --target-dir /root/mariadb_backup

mariabackup based on MariaDB server 10.5.13-MariaDB Linux (x86_64)
[01] 2022-03-17 14:04:35 Copying ibdata1 to /var/lib/mysql/ibdata1
[01] 2022-03-17 14:04:35         ...done
[01] 2022-03-17 14:04:35 Copying ./mysql/innodb_table_stats.ibd to /var/lib/mysql/mysql/innodb_table_stats.ibd
.....
.....
[01] 2022-03-17 14:04:35 Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
[01] 2022-03-17 14:04:35         ...done
[00] 2022-03-17 14:04:35 completed OK!

[root@node01 ~]#

chown -R mysql. /var/lib/mysql

[root@node01 ~]#

systemctl start mariadb

# confirm [File] and [Position] value of master log

[root@node01 ~]#

cat /root/mariadb_backup/xtrabackup_binlog_info

mysql-bin.000001        642     0-101-2

# set replication

[root@node01 ~]#

mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.13-MariaDB-log 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.

# master_host=(Master Host IP address)
# master_user=(replication user)
# master_password=(replication user password)
# master_log_file=([File] value confirmed above)
# master_log_pos=([Position] value confirmed above)
MariaDB [(none)]> change master to 
master_host='10.0.0.31',
master_user='repl_user',
master_password='password',
master_log_file='mysql-bin.000001',
master_log_pos=642;
Query OK, 0 rows affected (0.191 sec)

# start replication
MariaDB [(none)]> start slave; 
Query OK, 0 rows affected (0.00 sec)

# show status
MariaDB [(none)]> show slave status\G 
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.31
                   Master_User: repl_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 642
                Relay_Log_File: mariadb-relay-bin.000003
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000001
              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: 642
               Relay_Log_Space: 866
               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: 101
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

Matched Content