MariaDB 11.8 : Replication2026/04/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 # add follows in [mariadb] section
[mariadb]
binlog_format=row
log-bin=mysql-bin
# set server ID (unique one)
server-id=101
# set GTID (domain ID used for grouping)
gtid_domain_id=1
[root@www ~]#
[root@www ~]# systemctl restart mariadb mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 11.8.5-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)]> exit Bye |
| [3] | Change settings on MariaDB Replica Hosts. |
|
[root@node01 ~]#
vi /etc/my.cnf.d/mariadb-server.cnf # add follows in [mariadb] section
[mariadb]
binlog_format=row
log-bin=mysql-bin
# set server ID (unique one)
server-id=102
# set GTID (like domain ID used for grouping)
gtid_domain_id=1
# read only yes
read_only=1
# set report-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] 2026-04-17 11:20:37 MySQL binlog position: filename 'mysql-bin.000001', position '521', GTID of the last change '1-101-1' [00] 2026-04-17 11:20:37 Writing backup-my.cnf [00] 2026-04-17 11:20:37 ...done [00] 2026-04-17 11:20:37 Writing mariadb_backup_info [00] 2026-04-17 11:20:37 ...done [00] 2026-04-17 11:20:37 Redo log (from LSN 48857 to 48873) was copied. [00] 2026-04-17 11:20:37 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 cent cent 729510 Apr 17 11:21 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 ..... ..... 2026-04-17 11:22:33 0 [Note] InnoDB: innodb_buffer_pool_size_max=96m, innodb_buffer_pool_size=96m 2026-04-17 11:22:33 0 [Note] InnoDB: Completed initialization of buffer pool 2026-04-17 11:22:33 0 [Note] InnoDB: Buffered log writes (block size=512 bytes) 2026-04-17 11:22:33 0 [Note] InnoDB: End of log at LSN=48873 [00] 2026-04-17 11:22:33 Last binlog file , position 0 recovered pages: 0% 75% 100% (0.0 seconds); tables to flush: 1 0 (0.0 seconds); [00] 2026-04-17 11:22:33 completed OK! # run restore [root@node01 ~]# mariabackup --copy-back --target-dir /root/mariadb_backup ..... ..... [01] 2026-04-17 11:23:08 Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt [01] 2026-04-17 11:23:08 ...done [01] 2026-04-17 11:23:08 Copying ./mariadb_backup_info to /var/lib/mysql/mariadb_backup_info [01] 2026-04-17 11:23:08 ...done [01] 2026-04-17 11:23:08 Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb [01] 2026-04-17 11:23:08 ...done [00] 2026-04-17 11:23:08 completed OK! # set replication [root@node01 ~]# mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 11.8.5-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) MariaDB [(none)]> change master to master_host='10.0.0.31', master_user='repl_user', master_password='password', master_use_gtid=slave_pos; Query OK, 0 rows affected (0.017 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: 521 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 820 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: 521 Relay_Log_Space: 1131 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes 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: Yes 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: Slave_Pos Gtid_IO_Pos: 1-101-1 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: 1 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 Replicate_Rewrite_DB: 1 row in set (0.002 sec) |
| Sponsored Link |
|
|