Ubuntu 20.04
Sponsored Link

MariaDB : Replication2020/05/09

 
Configure MariaDB Replication. This is general Master-Slave setting.
[1]
[2] Change settings and create a user for replication on MariaDB Matser Host.
root@www:~#
vi /etc/mysql/mariadb.conf.d/50-server.cnf
# line 28: 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:~#
systemctl restart mariadb
root@www:~#
mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.3.22-MariaDB-1ubuntu1-log Ubuntu 20.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 Slave Host.
root@node01:~#
vi /etc/mysql/mariadb.conf.d/50-server.cnf
# line 28: change to IP of this host
bind-address            = 10.0.0.51

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

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

# line 79: add
# read only
read_only=1
# define own hostname
report-host=node01.srv.world

root@node01:~#
systemctl restart mariadb
[4] Get Dump-Data on MariaDB Master Host.
After getting Data, transfer it to Slave 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] 2020-05-09 18:03:19 MySQL binlog position: filename 'mysql-bin.000001', position '640', GTID of the last change '0-101-2'
[00] 2020-05-09 18:03:19 Writing backup-my.cnf
[00] 2020-05-09 18:03:19         ...done
[00] 2020-05-09 18:03:19 Writing xtrabackup_info
[00] 2020-05-09 18:03:19         ...done
[00] 2020-05-09 18:03:19 Redo log (from LSN 1631392 to 1631401) was copied.
[00] 2020-05-09 18:03:19 completed OK!
[5] On Slave Host, Copy back Backup Data of Master Host and Configure replication settings. After starting replication, make sure 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/*
# transfered backup data

root@node01:~#
ll mariadb_backup.tar.gz

-rw-r--r-- 1 root root 214909 May 9 18:05 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.3.22-MariaDB debian-linux-gnu (x86_64)
[00] 2020-05-09 18:08:45 cd to /root/mariadb_backup/
[00] 2020-05-09 18:08:45 This target seems to be not prepared yet.
[00] 2020-05-09 18:08:45 mariabackup: using the following InnoDB configuration for recovery:
[00] 2020-05-09 18:08:45 innodb_data_home_dir = .
.....
.....
2020-05-09 18:08:45 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2020-05-09 18:08:45 0 [Note] InnoDB: Completed initialization of buffer pool
2020-05-09 18:08:45 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2020-05-09 18:08:45 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1631392
[00] 2020-05-09 18:08:45 Last binlog file , position 0
[00] 2020-05-09 18:08:46 completed OK!

# run restore

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

mariabackup based on MariaDB server 10.3.22-MariaDB debian-linux-gnu (x86_64)
[01] 2020-05-09 18:09:25 Copying ibdata1 to /var/lib/mysql/ibdata1
[01] 2020-05-09 18:09:25         ...done
[01] 2020-05-09 18:09:25 Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt
[01] 2020-05-09 18:09:25         ...done
.....
.....
[01] 2020-05-09 18:09:25         ...done
[01] 2020-05-09 18:09:25 Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
[01] 2020-05-09 18:09:25         ...done
[01] 2020-05-09 18:09:25 Copying ./aria_log.00000001 to /var/lib/mysql/aria_log.00000001
[01] 2020-05-09 18:09:25         ...done
[00] 2020-05-09 18:09:25 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        640     0-101-2

# set replication

root@node01:~#
mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 48
Server version: 10.3.22-MariaDB-1ubuntu1-log Ubuntu 20.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.

MariaDB [(none)]> change master to 
    -> master_host='10.0.0.31',             # Master Host IP address
    -> master_user='replica',             # replication user
    -> master_password='password',          # replication user password
    -> master_log_file='mysql-bin.000001',  # [File] value confirmed above
    -> master_log_pos=640;              # [Position] value confirmed above
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: 640
                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_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: 640
               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: 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: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
Matched Content