MariaDB 11.8 : Replication2026/05/28 |
|
Configure MariaDB Replication. This is the general Primary/Replica setting. |
|
| [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/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 # line 77 : add binlog-format = row 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 32 Server version: 11.8.6-MariaDB-5 from Ubuntu-log -- Please help get to 10k stars at https://github.com/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 replica@'%' identified by 'password'; 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 binlog-format = row gtid_domain_id = 1 # specify this hostname report-host=node01.srv.worldroot@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] 2026-05-28 00:43:58 Backup created in directory '/home/mariadb_backup/' [00] 2026-05-28 00:43:58 MySQL binlog position: filename 'mysql-bin.000001', position '519', GTID of the last change '1-1-1' [00] 2026-05-28 00:43:58 Writing backup-my.cnf [00] 2026-05-28 00:43:58 ...done [00] 2026-05-28 00:43:58 Writing mariadb_backup_info [00] 2026-05-28 00:43:58 ...done [00] 2026-05-28 00:43:58 Redo log (from LSN 45600 to 45616) was copied. [00] 2026-05-28 00:43:58 completed OK! # confirm GTID (Global Transaction ID) root@www:~# mariadb -e 'select @@gtid_binlog_pos;' +-------------------+ | @@gtid_binlog_pos | +-------------------+ | 1-1-1 | +-------------------+ |
| [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/mariadb/*
# transferred backup data root@node01:~# ll mariadb_backup.tar.gz -rw-r--r-- 1 ubuntu ubuntu 744738 May 28 00:46 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-05-28 0:47:18 0 [Note] InnoDB: Using AVX512 instructions 2026-05-28 0:47:18 0 [Note] InnoDB: Using io_uring 2026-05-28 0:47:18 0 [Note] InnoDB: innodb_buffer_pool_size_max=96m, innodb_buffer_pool_size=96m 2026-05-28 0:47:18 0 [Note] InnoDB: Completed initialization of buffer pool 2026-05-28 0:47:18 0 [Note] InnoDB: Buffered log writes (block size=512 bytes) 2026-05-28 0:47:18 0 [Note] InnoDB: End of log at LSN=45616 [00] 2026-05-28 00:47:18 Last binlog file , position 0 recovered pages: 0% 76% 100% (0.0 seconds); tables to flush: 1 0 (0.1 seconds); [00] 2026-05-28 00:47:18 completed OK! # run restore root@node01:~# mariabackup --copy-back --target-dir /root/mariadb_backup ..... ..... [01] 2026-05-28 00:47:36 ...done [01] 2026-05-28 00:47:36 Copying ./sys/io_global_by_file_by_bytes.frm to /var/lib/mariadb/sys/io_global_by_file_by_bytes.frm [01] 2026-05-28 00:47:36 ...done [01] 2026-05-28 00:47:36 Copying ./sys/x@0024innodb_buffer_stats_by_table.frm to /var/lib/mariadb/sys/x@0024innodb_buffer_stats_by_table.frm [01] 2026-05-28 00:47:36 ...done [01] 2026-05-28 00:47:36 Copying ./performance_schema/db.opt to /var/lib/mariadb/performance_schema/db.opt [01] 2026-05-28 00:47:36 ...done [00] 2026-05-28 00:47:36 completed OK! # set replication root@node01:~# mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 44 Server version: 11.8.6-MariaDB-5 from Ubuntu-log -- Please help get to 10k stars at https://github.com/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. # gtid_slave_pos = GTID you confirmed on primary host # master_host = Primary Host IP address # master_user = replication user ID # master_password = replication user ID password MariaDB [(none)]> set global gtid_slave_pos = "1-1-1"; Query OK, 0 rows affected (0.012 sec) MariaDB [(none)]> change master to master_host='10.0.0.31', master_user='replica', master_password='password', master_use_gtid=slave_pos; Query OK, 0 rows affected (0.032 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: 519 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 641 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: 519 Relay_Log_Space: 951 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: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 1-1-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: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 Replicate_Rewrite_DB: 1 row in set (0.001 sec) |
| Sponsored Link |
|
|