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:~#
root@www:~# systemctl restart mariadb
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.worldroot@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! # 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) |
| Sponsored Link |