Ubuntu 24.04
Sponsored Link

MariaDB 10.11 : Replication2024/05/17

 
Configure MariaDB Replication. This is the general Primary/Replica setting.
[1]
[2] Change settings and create a user for replication on MariaDB Primary Host.
root@www:~#
vi /etc/mysql/mariadb.conf.d/50-server.cnf
# line 27 : change to IP of this host

bind-address =
10.0.0.31
# line 73 : uncomment and change to any another ID

server-id =
101
# line 74 : uncomment

log_bin = /var/log/mysql/mysql-bin.log
root@www:~#
mkdir /var/log/mysql

root@www:~#
chown mysql:mysql /var/log/mysql

root@www:~#
systemctl restart mariadb
root@www:~#
mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.11.7-MariaDB-2ubuntu2 Ubuntu 24.04

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 replica@'%' 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 Replica Host.
root@node01:~#
vi /etc/mysql/mariadb.conf.d/50-server.cnf
# line 27 : change to IP of this host
bind-address            = 10.0.0.51

# line 73 : uncomment and change to another ID (different one from Primary Host)
server-id               = 102

# line 74 : uncomment
log_bin                 = /var/log/mysql/mysql-bin.log

# line 77 : add
# read only
read_only=1
# specify this hostname
report-host=node01.srv.world

root@node01:~#
mkdir /var/log/mysql

root@node01:~#
chown mysql:mysql /var/log/mysql

root@node01:~#
systemctl restart mariadb
[4] Get Dump-Data on Primary 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] 2024-05-17 01:05:29 Writing backup-my.cnf
[00] 2024-05-17 01:05:29         ...done
[00] 2024-05-17 01:05:29 Writing xtrabackup_info
[00] 2024-05-17 01:05:29         ...done
[00] 2024-05-17 01:05:29 Redo log (from LSN 49855 to 49871) was copied.
[00] 2024-05-17 01:05:29 completed OK!
[5] On Replica Host, Copy back Backup Data of Primary Host and Configure replication settings.
After starting replication, verify 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 700492 May 17 01:06 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

.....
.....
2024-05-17  1:07:56 0 [Note] InnoDB: Initializing buffer pool, total size = 100.000MiB, chunk size = 100.000MiB
2024-05-17  1:07:56 0 [Note] InnoDB: Completed initialization of buffer pool
2024-05-17  1:07:56 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
2024-05-17  1:07:56 0 [Note] InnoDB: End of log at LSN=49871
[00] 2024-05-17 01:07:56 Last binlog file , position 0
[00] 2024-05-17 01:07:56 completed OK!

# run restore

root@node01:~#
mariabackup --copy-back --target-dir /root/mariadb_backup

.....
.....
[01] 2024-05-17 01:09:08 Copying ./mysql/proc.MAI to /var/lib/mysql/mysql/proc.MAI
[01] 2024-05-17 01:09:08         ...done
[01] 2024-05-17 01:09:08 Copying ./mysql/table_stats.frm to /var/lib/mysql/mysql/table_stats.frm
[01] 2024-05-17 01:09:08         ...done
[00] 2024-05-17 01:09:08 completed OK!

root@node01:~#
chown -R mysql:mysql /var/lib/mysql

root@node01:~#
systemctl start mariadb

# confirm [File] and [Position] value of logs of primary host

root@node01:~#
cat /root/mariadb_backup/xtrabackup_binlog_info

mysql-bin.000001        328

# set replication

root@node01:~#
mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.11.7-MariaDB-2ubuntu2-log Ubuntu 24.04

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 = Primary Host IP address
# master_user = replication user ID
# master_password = replication user ID 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='replica',
master_password='password',
master_log_file='mysql-bin.000001',
master_log_pos=328;
Query OK, 0 rows affected (0.295 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: replica
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 328
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
          Replicate_Rewrite_DB:
               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: 328
               Relay_Log_Space: 865
               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