MariaDB 10.3 : Replication2020/01/23 |
|
Configure MariaDB Replication settings. This configuration is general Master-Slave settings.
|
|
| [1] | |
| [2] | Change settings and create a user for replication on MariaDB Matser Host. |
|
[root@www ~]#
vi /etc/opt/rh/rh-mariadb103/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 rh-mariadb103-mariadb mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.13-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)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> exit Bye |
| [3] | Change settings on MariaDB Slave Hosts. |
|
[root@node01 ~]#
vi /etc/opt/rh/rh-mariadb103/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 rh-mariadb103-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 -p password [00] 2020-01-22 22:47:06 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set [00] 2020-01-22 22:47:06 Using server version 10.3.13-MariaDB-log [00] 2020-01-22 22:47:06 mariabackup based on MariaDB server 10.3.13-MariaDB Linux (x86_64) ..... ..... [00] 2020-01-22 22:47:33 Redo log (from LSN 1639826 to 1639835) was copied. [00] 2020-01-22 22:47:33 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 rh-mariadb103-mariadb [root@node01 ~]# rm -rf /var/opt/rh/rh-mariadb103/lib/mysql/*
# transferred backup data [root@node01 ~]# ll mariadb_backup.tar.gz -rw-r--r--. 1 root root 212896 Jan 22 22:48 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 [00] 2020-01-22 22:50:17 mariabackup based on MariaDB server 10.3.13-MariaDB Linux (x86_64) [00] 2020-01-22 22:50:17 cd to /root/mariadb_backup/ ..... ..... [00] 2020-01-22 22:50:17 Last binlog file , position 0 [00] 2020-01-22 22:50:17 completed OK! # run restore [root@node01 ~]# mariabackup --copy-back --target-dir /root/mariadb_backup [00] 2020-01-22 22:51:41 mariabackup based on MariaDB server 10.3.13-MariaDB Linux (x86_64) [01] 2020-01-22 22:51:41 Copying ibdata1 to /var/opt/rh/rh-mariadb103/lib/mysql/ibdata1 [01] 2020-01-22 22:51:41 ...done ..... ..... [01] 2020-01-22 22:51:41 Copying ./xtrabackup_binlog_pos_innodb to /var/opt/rh/rh-mariadb103/lib/mysql/xtrabackup_binlog_pos_innodb [01] 2020-01-22 22:51:41 ...done [00] 2020-01-22 22:51:41 completed OK!
[root@node01 ~]#
chown -R mysql. /var/opt/rh/rh-mariadb103/lib/mysql [root@node01 ~]# systemctl start rh-mariadb103-mariadb
# confirm [File] and [Position] value of master log [root@node01 ~]# cat /root/mariadb_backup/xtrabackup_binlog_info mysql-bin.000001 642 0-101-2 # set replication [root@node01 ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.13-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. MariaDB [(none)]> change master to -> master_host='10.0.0.31', # Master Host IP address -> master_user='repl_user', # replication user -> master_password='password', # replication user password -> master_log_file='mysql-bin.000001', # [File] value confirmed above -> master_log_pos=642; # [Position] value confirmed above Query OK, 0 rows affected (0.58 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: 642 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: 642 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: 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 |