Saturday, June 24, 2023

.::: Enabling GTIDs Replication in MariaDB Server for Master - Slave or Mirroring :::.

 1. enable /etc/hosts <optional>

[root@teguhth01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.31 teguhth01
10.10.10.32 teguhth02
10.10.10.33 teguhth03
10.10.10.34 teguhth04
10.10.10.35 teguhth05
[root@teguhth01 ~]#

[root@teguhth02 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.31 teguhth01
10.10.10.32 teguhth02
10.10.10.33 teguhth03
10.10.10.34 teguhth04
10.10.10.35 teguhth05
[root@teguhth02 ~]#

2. add server.cnf configuration in server 1 <master> & server 2<slave>

In Master:

server_id=1
log-bin=mysql-bin
gtid_strict_mode=on
#bind-address=10.10.10.31
# only mastet gtid-domain-id=1
gtid-domain-id=1

In Replica:

server_id=2
log-bin=mysql-bin
#bind-address=10.10.10.32
gtid_strict_mode=on

or advanced

In Master:

[root@teguhth01 my.cnf.d]#

#.............
log_warnings=1
server_id=1
gtid_strict_mode=on
gtid-domain-id=1
log-bin=mysql-bin
#gtid_strict_mode=1
#bind-address=10.10.10.33
#contoh lain tidak menggunakan log-bin=mysql-bin

gtid_ignore_duplicates=ON
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_slave_enabled=ON
rpl_semi_sync_master_wait_point=AFTER_SYNC
slave-skip-errors=1062,1032
#............

[root@teguhth01 my.cnf.d]#

In Replica:

[root@teguhth02 my.cnf.d]#
#....
[mariadb]
log_warnings=1
server_id=2
gtid_strict_mode=on
log-bin=mysql-bin
#gtid_strict_mode=1
#bind-address=10.10.10.34
#contoh lain tidak menggunakan log-bin=mysql-bin

gtid_ignore_duplicates=ON
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_slave_enabled=ON
rpl_semi_sync_master_wait_point=AFTER_SYNC
slave-skip-errors=1062,1032
##....

[root@teguhth02 my.cnf.d]#

3. restart mariadb

[root@teguhth01 ~]#
[root@teguhth01 ~]# systemctl restart mariadb
[root@teguhth01 ~]#

[root@teguhth02 ~]# systemctl restart mariadb
[root@teguhth02 ~]#
 


4. create database teguhth in master & slave

show databases;
create database teguhth;
show databases;

MariaDB [(none)]> select @@hostname,@@version;
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth01  | 10.5.21-MariaDB-log |
+------------+---------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.004 sec)

MariaDB [(none)]> create database teguhth;
Query OK, 1 row affected (10.003 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| teguhth            |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]>

MariaDB [(none)]> select @@hostname,@@version;
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth02  | 10.5.21-MariaDB-log |
+------------+---------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.002 sec)

MariaDB [(none)]> create database teguhth;
Query OK, 1 row affected (10.005 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| teguhth            |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]>

5. create user replica  in master or slave<optional>

create user 'replica'@'%' identified by 'password';

create user 'replica' identified by 'password';
grant replication slave, reload,super,replication client on *.* to replica@'%' identified by 'password';
flush privileges;
SHOW GRANTS FOR replica@'%';

MariaDB [(none)]> create user 'replica' identified by 'password';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant replication slave, reload,super,replication client on *.* to replica@'%' identified by 'password';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW GRANTS FOR replica@'%';
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for replica@%                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, SUPER, REPLICATION SLAVE, BINLOG MONITOR ON *.* TO `replica`@`%` IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> select @@hostname,@@version;
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth01  | 10.5.21-MariaDB-log |
+------------+---------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

MariaDB [(none)]> create user 'replica' identified by 'password';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> grant replication slave, reload,super,replication client on *.* to replica@'%' identified by 'password';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> SHOW GRANTS FOR replica@'%';
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for replica@%                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, SUPER, REPLICATION SLAVE, BINLOG MONITOR ON *.* TO `replica`@`%` IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> select @@hostname,@@version;
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth02  | 10.5.21-MariaDB-log |
+------------+---------------------+
1 row in set (0.001 sec)

MariaDB [(none)]>

6. backup a database from Master & transfer to Slave <maybe optional>
mysqldump -u root -p teguhth --skip-lock-tables  > db_teguh.sql ;

[root@teguhth01 data]# ls
[root@teguhth01 data]# mysqldump -u root -p teguhth --skip-lock-tables  > db_teguh.sql ;
Enter password:
[root@teguhth01 data]# ls
db_teguh.sql
[root@teguhth01 data]#
or

mysql -h teguhth01  -uroot -p -e "SHOW MASTER STATUS;"

mysql -h teguhth01 -uroot -p -e "SELECT BINLOG_GTID_POS('start_binlog<file>', 'start_pos<position>');"

 

mysqldump -h teguhth01 -uroot -p -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false --all-databases| gzip -c > all_db_.sql.gz



[root@teguhth01 data]# scp db_teguh.sql root@teguhth02:/data
The authenticity of host 'teguhth02 (10.10.10.32)' can't be established.
ECDSA key fingerprint is SHA256:kee0V03Z60s3pYAUCFL8BnOGOt30Kg2G1YAUh/jlozE.
ECDSA key fingerprint is MD5:76:3f:d2:f0:5a:66:d6:ae:28:f9:16:f1:dd:5a:93:2d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'teguhth02,10.10.10.32' (ECDSA) to the list of known hosts.
root@teguhth02's password:
db_teguh.sql                                                                                                                100% 1275   790.8KB/s   00:00
[root@teguhth01 data]#

7. Restore to standby

mysql -u root -p teguhth < db_teguh.sql

[root@teguhth02 data]# mysql -u root -p teguhth < db_teguh.sql
Enter password:
[root@teguhth02 data]#

8. check configuration gtid
select @@hostname,@@version;
show global variables like 'log_bin';
show global variables like '%gtid_strict_mode%';

MariaDB [(none)]> select @@hostname,@@version;
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth02  | 10.5.21-MariaDB-log |
+------------+---------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.004 sec)

MariaDB [(none)]> show global variables like '%gtid_strict_mode%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| gtid_strict_mode | ON    |
+------------------+-------+
1 row in set (0.002 sec)

MariaDB [(none)]>

MariaDB [(none)]> select @@hostname,@@version;
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth01  | 10.5.21-MariaDB-log |
+------------+---------------------+
1 row in set (0.016 sec)

MariaDB [(none)]> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.042 sec)

MariaDB [(none)]> show global variables like '%gtid_strict_mode%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| gtid_strict_mode | ON    |
+------------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]>

9. set slave in secondary

stop slave ; reset slave; reset master;
SET GLOBAL gtid_slave_pos = ' ';
CHANGE MASTER TO MASTER_HOST ='10.10.10.31', MASTER_USER ='replica',MASTER_PASSWORD ='password', MASTER_USE_GTID=slave_pos;
start slave;
SHOW SLAVE STATUS\G

MariaDB [(none)]> stop slave ; reset slave; reset master;
Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST ='10.10.10.31', MASTER_USER ='replica',MASTER_PASSWORD ='password', MASTER_USE_GTID=slave_pos;
Query OK, 0 rows affected (0.010 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.011 sec)

MariaDB [(none)]>

10. cek slave secondary
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.10.10.31
                   Master_User: replica
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 328
                Relay_Log_File: teguhth02-relay-bin.000002
                 Relay_Log_Pos: 627
         Relay_Master_Log_File: mysql-bin.000002
              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: 328
               Relay_Log_Space: 940
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Slave_Pos
                   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)

MariaDB [(none)]>

10. check before insert data

check in slave & master
MariaDB [teguhth]> select @@hostname;
+------------+
| @@hostname |
+------------+
| teguhth01  |
+------------+
1 row in set (0.000 sec)

MariaDB [teguhth]> show tables;
Empty set (0.000 sec)

MariaDB [teguhth]> select @@hostname;
+------------+
| @@hostname |
+------------+
| teguhth02  |
+------------+
1 row in set (0.000 sec)

MariaDB [teguhth]> show tables;
Empty set (0.000 sec)

MariaDB [teguhth]>

11. create table in master & check in slave

create table barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));

MariaDB [teguhth]> create table barang(
    -> KODE_BARANG char(6) not null ,
    -> NAMA_BARANG varchar(25),
    -> SATUAN_BARANG varchar(20),
    -> STOK_BARANG decimal(4),
    -> primary key (KODE_BARANG));
Query OK, 0 rows affected (0.015 sec)

MariaDB [teguhth]> show tables;
+-------------------+
| Tables_in_teguhth |
+-------------------+
| barang            |
+-------------------+
1 row in set (0.001 sec)

MariaDB [teguhth]> select @@hostname;
+------------+
| @@hostname |
+------------+
| teguhth01  |
+------------+
1 row in set (0.000 sec)

MariaDB [teguhth]>

and in slave
MariaDB [teguhth]> select @@hostname;
+------------+
| @@hostname |
+------------+
| teguhth02  |
+------------+
1 row in set (0.000 sec)

MariaDB [teguhth]> show tables;
Empty set (0.000 sec)

MariaDB [teguhth]>
MariaDB [teguhth]> show tables;
+-------------------+
| Tables_in_teguhth |
+-------------------+
| barang            |
+-------------------+
1 row in set (0.000 sec)

MariaDB [teguhth]>

12. check data before insert data in master/teguhth01
check in master & slave;

select *,@@hostname from barang;

MariaDB [teguhth]> select *,@@hostname from barang;
Empty set (0.008 sec)

MariaDB [teguhth]>

MariaDB [teguhth]>  select *,@@hostname from barang;
Empty set (0.009 sec)

MariaDB [teguhth]>

13. insert in master/teguhth01
 
use teguhth;
select * from barang;
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-01','RICE COOKER','BUAH',20);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-02','LEMARI ES','UNIT',8);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-03','TELEVISI','UNIT',30);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-04','RADIO/TAPE','BUAH',35);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-05','KOMPUTER','UNIT',28);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-06','KIPAS ANGIN','BUAH',38);
select * from barang;

MariaDB [teguhth]> select * from barang;
Empty set (0.000 sec)

MariaDB [teguhth]> insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-01','RICE COOKER','BUAH',20);
select * from barang;Query OK, 1 row affected (0.003 sec)

MariaDB [teguhth]> insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-02','LEMARI ES','UNIT',8);
Query OK, 1 row affected (0.003 sec)

MariaDB [teguhth]> insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-03','TELEVISI','UNIT',30);
Query OK, 1 row affected (0.002 sec)

MariaDB [teguhth]> insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-04','RADIO/TAPE','BUAH',35);
Query OK, 1 row affected (0.002 sec)

MariaDB [teguhth]> insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-05','KOMPUTER','UNIT',28);
Query OK, 1 row affected (0.001 sec)

MariaDB [teguhth]> insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-06','KIPAS ANGIN','BUAH',38);
Query OK, 1 row affected (0.001 sec)

MariaDB [teguhth]> select * from barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01      | RICE COOKER | BUAH          |          20 |
| ELK-02      | LEMARI ES   | UNIT          |           8 |
| ELK-03      | TELEVISI    | UNIT          |          30 |
| ELK-04      | RADIO/TAPE  | BUAH          |          35 |
| ELK-05      | KOMPUTER    | UNIT          |          28 |
| ELK-06      | KIPAS ANGIN | BUAH          |          38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.001 sec)

MariaDB [teguhth]> select *,@@hostname from barang;
+-------------+-------------+---------------+-------------+------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG | @@hostname |
+-------------+-------------+---------------+-------------+------------+
| ELK-01      | RICE COOKER | BUAH          |          20 | teguhth01  |
| ELK-02      | LEMARI ES   | UNIT          |           8 | teguhth01  |
| ELK-03      | TELEVISI    | UNIT          |          30 | teguhth01  |
| ELK-04      | RADIO/TAPE  | BUAH          |          35 | teguhth01  |
| ELK-05      | KOMPUTER    | UNIT          |          28 | teguhth01  |
| ELK-06      | KIPAS ANGIN | BUAH          |          38 | teguhth01  |
+-------------+-------------+---------------+-------------+------------+
6 rows in set (0.001 sec)

MariaDB [teguhth]>
 

14. check in master & slave after insert

MariaDB [teguhth]>  select *,@@hostname from barang;
+-------------+-------------+---------------+-------------+------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG | @@hostname |
+-------------+-------------+---------------+-------------+------------+
| ELK-01      | RICE COOKER | BUAH          |          20 | teguhth02  |
| ELK-02      | LEMARI ES   | UNIT          |           8 | teguhth02  |
| ELK-03      | TELEVISI    | UNIT          |          30 | teguhth02  |
| ELK-04      | RADIO/TAPE  | BUAH          |          35 | teguhth02  |
| ELK-05      | KOMPUTER    | UNIT          |          28 | teguhth02  |
| ELK-06      | KIPAS ANGIN | BUAH          |          38 | teguhth02  |
+-------------+-------------+---------------+-------------+------------+
6 rows in set (0.001 sec)

MariaDB [teguhth]>
 

15. check slave status

MariaDB [teguhth]> select @@hostname,@@version;
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth02  | 10.5.21-MariaDB-log |
+------------+---------------------+
1 row in set (0.000 sec)

MariaDB [teguhth]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.10.10.31
                   Master_User: replica
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 2102
                Relay_Log_File: teguhth02-relay-bin.000002
                 Relay_Log_Pos: 2401
         Relay_Master_Log_File: mysql-bin.000002
              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: 2102
               Relay_Log_Space: 2714
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 1-1-7
       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: 6
1 row in set (0.000 sec)

MariaDB [teguhth]>

16. check log
[root@teguhth02 ~]# cat /var/lib/mysql/mysql
mysql/              mysql-bin.000002    mysql-bin.index     mysql.sock
mysql-bin.000001    mysql-bin.000003    mysql_error.log     mysql_upgrade_info
[root@teguhth02 ~]# cat /var/lib/mysql/mysql_error.log
2023-06-24 16:46:41 0 [Note] Starting MariaDB 10.5.21-MariaDB-log source revision bed70468ea08c2820647f5e3ac006a9ff88144ac as process 9788
2023-06-24 16:46:41 0 [Note] InnoDB: Uses event mutexes
2023-06-24 16:46:41 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2023-06-24 16:46:41 0 [Note] InnoDB: Number of pools: 1
2023-06-24 16:46:41 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2023-06-24 16:46:41 0 [Note] InnoDB: Using Linux native AIO
2023-06-24 16:46:41 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2023-06-24 16:46:41 0 [Note] InnoDB: Completed initialization of buffer pool
2023-06-24 16:46:41 0 [Note] InnoDB: 128 rollback segments are active.
2023-06-24 16:46:41 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-06-24 16:46:41 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-06-24 16:46:41 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2023-06-24 16:46:41 0 [Note] InnoDB: 10.5.21 started; log sequence number 58659; transaction id 67
2023-06-24 16:46:41 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-06-24 16:46:41 0 [Note] Semi-sync replication initialized for transactions.
2023-06-24 16:46:41 0 [Note] Semi-sync replication enabled on the master.
2023-06-24 16:46:41 0 [Note] Starting ack receiver thread
2023-06-24 16:46:41 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2023-06-24 16:46:41 0 [Note] InnoDB: Buffer pool(s) load completed at 230624 16:46:41
2023-06-24 16:46:41 0 [Note] Server socket created on IP: '::'.
2023-06-24 16:46:41 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MariaDB server acts as a replica and has its hostname changed. Please use '--log-basename=#' or '--relay-log=teguhth02-relay-bin' to avoid this problem.
2023-06-24 16:46:41 6 [Note] Slave I/O thread: Start semi-sync replication to master 'replica@10.10.10.31:3306' in log 'mysql-bin.000002' at position 2102
2023-06-24 16:46:41 7 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000002' at position 2102, relay log './teguhth02-relay-bin.000001' position: 4; GTID position '1-1-7'
2023-06-24 16:46:41 0 [Note] /usr/sbin/mariadbd: ready for connections.
Version: '10.5.21-MariaDB-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
2023-06-24 16:46:41 6 [Note] Slave I/O thread: connected to master 'replica@10.10.10.31:3306',replication starts at GTID position '1-1-7'
[root@teguhth02 ~]#

17. check simpe slave

mysql -uroot -p -e "select @@hostname,@@version";
mysql -uroot -p -e "show slave status\G" | grep -i -E "Slave_IO_State:| Master_Host:| Master_User:| Master_Port:| Relay_Master_Log_File:| Exec_Master_Log_Pos:| Seconds_Behind_Master:| Master_Server_Id:| Using_Gtid:| Gtid_IO_Pos:| Slave_SQL_Running_State:";

 

[root@teguhth02 ~]# mysql -uroot -p -e "select @@hostname,@@version";
Enter password:
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth02  | 10.5.22-MariaDB-log |
+------------+---------------------+
[root@teguhth02 ~]# mysql -uroot -p -e "show slave status\G" | grep -i -E "Slave_IO_State:| Master_Host:| Master_User:| Master_Port:| Relay_Master_Log_File:| Exec_Master_Log_Pos:| Seconds_Behind_Master:| Master_Server_Id:| Using_Gtid:| Gtid_IO_Pos:| Slave_SQL_Running_State:";
Enter password:
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.10.10.31
                   Master_User: replica
                   Master_Port: 3306
         Relay_Master_Log_File: mysql-bin.000028
           Exec_Master_Log_Pos: 342
         Seconds_Behind_Master: 0
              Master_Server_Id: 1
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 1-1-148
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
[root@teguhth02 ~]#
 



Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts