MariaDB : Replication2023/11/20 |
|
Configure MariaDB Replication settings. This configuration is general Primary-Replica settings.
|
|
| [1] | |
| [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 ~]#
[root@www ~]# systemctl restart mariadb mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 10.5.22-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
[mysqld]
[root@node01 ~]# # 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 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] 2023-11-20 09:45:07 Connecting to server host: localhost, user: root, password: not set, port: not set, socket: not set [00] 2023-11-20 09:45:07 Using server version 10.5.22-MariaDB mariabackup based on MariaDB server 10.5.22-MariaDB Linux (x86_64) [00] 2023-11-20 09:45:07 uses posix_fadvise(). [00] 2023-11-20 09:45:07 cd to /var/lib/mysql/ [00] 2023-11-20 09:45:07 open files limit requested 0, set to 1024 [00] 2023-11-20 09:45:07 mariabackup: using the following InnoDB configuration: [00] 2023-11-20 09:45:07 innodb_data_home_dir = [00] 2023-11-20 09:45:07 innodb_data_file_path = ibdata1:12M:autoextend [00] 2023-11-20 09:45:07 innodb_log_group_home_dir = ./ [00] 2023-11-20 09:45:07 InnoDB: Using Linux native AIO ..... ..... [00] 2023-11-20 09:45:09 Writing backup-my.cnf [00] 2023-11-20 09:45:09 ...done [00] 2023-11-20 09:45:09 Writing xtrabackup_info [00] 2023-11-20 09:45:09 ...done [00] 2023-11-20 09:45:09 Redo log (from LSN 47577 to 47589) was copied. [00] 2023-11-20 09:45:09 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 546815 Nov 20 09:47 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.22-MariaDB Linux (x86_64) [00] 2023-11-20 09:51:51 cd to /root/mariadb_backup/ [00] 2023-11-20 09:51:51 open files limit requested 0, set to 1024 [00] 2023-11-20 09:51:51 This target seems to be not prepared yet. [00] 2023-11-20 09:51:51 mariabackup: using the following InnoDB configuration for recovery: [00] 2023-11-20 09:51:51 innodb_data_home_dir = . [00] 2023-11-20 09:51:51 innodb_data_file_path = ibdata1:12M:autoextend [00] 2023-11-20 09:51:51 innodb_log_group_home_dir = . [00] 2023-11-20 09:51:51 InnoDB: Using Linux native AIO [00] 2023-11-20 09:51:51 Starting InnoDB instance for recovery. [00] 2023-11-20 09:51:51 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 2023-11-20 9:51:51 0 [Note] InnoDB: Uses event mutexes 2023-11-20 9:51:51 0 [Note] InnoDB: Compressed tables use zlib 1.2.13 2023-11-20 9:51:51 0 [Note] InnoDB: Number of pools: 1 2023-11-20 9:51:51 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions 2023-11-20 9:51:51 0 [Note] InnoDB: Using Linux native AIO 2023-11-20 9:51:51 0 [Note] InnoDB: Initializing buffer pool, total size = 104857600, chunk size = 104857600 2023-11-20 9:51:51 0 [Note] InnoDB: Completed initialization of buffer pool 2023-11-20 9:51:51 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=47577,47577 [00] 2023-11-20 09:51:51 Last binlog file , position 0 [00] 2023-11-20 09:51:51 completed OK! # run restore [root@node01 ~]# mariabackup --copy-back --target-dir /root/mariadb_backup mariabackup based on MariaDB server 10.5.22-MariaDB Linux (x86_64) [01] 2023-11-20 09:52:17 Copying ./aria_log_control to /var/lib/mysql/aria_log_control [01] 2023-11-20 09:52:17 ...done [01] 2023-11-20 09:52:17 Copying ./aria_log.00000001 to /var/lib/mysql/aria_log.00000001 [01] 2023-11-20 09:52:17 ...done [01] 2023-11-20 09:52:17 Copying ibdata1 to /var/lib/mysql/ibdata1 [01] 2023-11-20 09:52:17 ...done [01] 2023-11-20 09:52:17 Copying ./mysql/innodb_table_stats.ibd to /var/lib/mysql/mysql/innodb_table_stats.ibd [01] 2023-11-20 09:52:17 ...done [01] 2023-11-20 09:52:17 Copying ./mysql/innodb_index_stats.ibd to /var/lib/mysql/mysql/innodb_index_stats.ibd [01] 2023-11-20 09:52:17 ...done ..... ..... [01] 2023-11-20 09:52:17 Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt [01] 2023-11-20 09:52:17 ...done [01] 2023-11-20 09:52:17 Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info [01] 2023-11-20 09:52:17 ...done [00] 2023-11-20 09:52:17 completed OK! # confirm [File] and [Position] value of master log [root@node01 ~]# cat /root/mariadb_backup/xtrabackup_binlog_info mysql-bin.000001 521 0-101-1 # 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.22-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=521; 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: 521 Relay_Log_File: mariadb-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: 521 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) |
| Sponsored Link |
|
|