.::: Test Insert, select Data using Maxscale include maxscale router to slave/ master :::.

 

1. Create table;
CREATE TABLE `hostname_tbl` (
   `host` varchar(100) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `dbinfo` varchar(8000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

2. check maxscale list servers

[root@ha01 ~]#  maxctrl list servers
┌─────────────────┬─────────────┬──────┬─────────────┬─────────────────┬─────────┬─────────────────────────────┐
│ Server          │ Address     │ Port │ Connections │ State           │ GTID    │ Monitor                     │
├─────────────────┼─────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────────────────┤
├ gtid01-v31      │ 10.10.10.31 │ 3306 │ 0           │ Master, Running │ 0-1-121 │ MariaDB-Monitor-gtid        │
├─────────────────┼─────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────────────────┤
│ gtid02-v32      │ 10.10.10.32 │ 3306 │ 0           │ Slave, Running  │ 0-1-121 │ MariaDB-Monitor-gtid        │
├─────────────────┼─────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────────────────┤
│ gtid03-v33      │ 10.10.10.33 │ 3306 │ 0           │ Slave, Running  │ 0-1-121 │ MariaDB-Monitor-gtid        │
└─────────────────┴─────────────┴──────┴─────────────┴─────────────────┴─────────┴─────────────────────────────┘
[root@ha01 ~]#
 

3. insert from maxscale

mysql -h 10.10.10.15 -P4036 -uadmin -padmin -A

insert into `teguhth`.`hostname_tbl` (`host`,`created_date`,`dbinfo`) value (@@hostname,now(),'testing');

mysql -h 10.10.10.15 -P4036 -uadmin -padmin -A -e "INSERT INTO \`teguhth\`.\`hostname_tbl\` (\`host\`, \`created_date\`, \`dbinfo\`) VALUES (@@hostname, NOW(), 'testing');"

[root@ha01 ~]# mysql -h 10.10.10.15 -P4036 -uadmin -padmin -A
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
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.

MariaDB [(none)]> insert into `teguhth`.`hostname_tbl` (`host`,`created_date`,`dbinfo`) value (@@hostname,now(),'testing');
Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> exit
Bye
[root@ha01 ~]# mysql -h 10.10.10.15 -P4036 -uadmin -padmin -A -e "INSERT INTO \`teguhth\`.\`hostname_tbl\` (\`host\`, \`created_date\`, \`dbinfo\`) VALUES (@@hostname, NOW(), 'testing');"
[root@ha01 ~]#
[root@ha01 ~]# mysql -h 10.10.10.15 -P4036 -uadmin -padmin -A -e 'select @@hostname as Hostname,host,created_date,dbinfo from teguhth.hostname_tbl;'
+-----------+-----------+---------------------+---------+
| Hostname  | host      | created_date        | dbinfo  |
+-----------+-----------+---------------------+---------+
| teguhth02 | teguhth01 | 2025-01-13 13:47:54 | testing |
| teguhth02 | teguhth01 | 2025-01-13 13:50:07 | testing |
| teguhth02 | teguhth01 | 2025-01-13 13:53:56 | testing |
| teguhth02 | teguhth01 | 2025-01-13 13:54:58 | testing |
| teguhth02 | teguhth01 | 2025-01-13 13:55:55 | testing |
| teguhth02 | teguhth01 | 2025-01-13 13:56:08 | testing |
+-----------+-----------+---------------------+---------+
[root@ha01 ~]#
 

4. check

mysql -h 10.10.10.15 -P4036 -uadmin -padmin -A -e 'select @@hostname as Hostname,host,created_date,dbinfo from teguhth.hostname_tbl;'
mysql -h 10.10.10.31 -P3306 -uadmin -padmin -A -e 'select @@hostname as Hostname,host,created_date,dbinfo from teguhth.hostname_tbl;'
mysql -h 10.10.10.32 -P3306 -uadmin -padmin -A -e 'select @@hostname as Hostname,host,created_date,dbinfo from teguhth.hostname_tbl;'
mysql -h 10.10.10.33 -P3306 -uadmin -padmin -A -e 'select @@hostname as Hostname,host,created_date,dbinfo from teguhth.hostname_tbl;'

[root@ha01 ~]# mysql -h 10.10.10.15 -P4036 -uadmin -padmin -A -e 'select @@hostname as Hostname,host,created_date,dbinfo from teguhth.hostname_tbl;'
+-----------+-----------+---------------------+---------+
| Hostname  | host      | created_date        | dbinfo  |
+-----------+-----------+---------------------+---------+
| teguhth02 | teguhth01 | 2025-01-13 09:27:33 | testing |
| teguhth02 | teguhth01 | 2025-01-13 09:28:09 | testing |
+-----------+-----------+---------------------+---------+
[root@ha01 ~]#
[root@ha01 ~]# mysql -h 10.10.10.31 -P3306 -uadmin -padmin -A -e 'select @@hostname as Hostname,host,created_date,dbinfo from teguhth.hostname_tbl;'
+-----------+-----------+---------------------+---------+
| Hostname  | host      | created_date        | dbinfo  |
+-----------+-----------+---------------------+---------+
| teguhth01 | teguhth01 | 2025-01-13 09:27:33 | testing |
| teguhth01 | teguhth01 | 2025-01-13 09:28:09 | testing |
+-----------+-----------+---------------------+---------+
[root@ha01 ~]#
[root@ha01 ~]# mysql -h 10.10.10.32 -P3306 -uadmin -padmin -A -e 'select @@hostname as Hostname,host,created_date,dbinfo from teguhth.hostname_tbl;'
+-----------+-----------+---------------------+---------+
| Hostname  | host      | created_date        | dbinfo  |
+-----------+-----------+---------------------+---------+
| teguhth02 | teguhth01 | 2025-01-13 09:27:33 | testing |
| teguhth02 | teguhth01 | 2025-01-13 09:28:09 | testing |
+-----------+-----------+---------------------+---------+
[root@ha01 ~]#
[root@ha01 ~]# mysql -h 10.10.10.33 -P3306 -uadmin -padmin -A -e 'select @@hostname as Hostname,host,created_date,dbinfo from teguhth.hostname_tbl;'
+------------+-----------+---------------------+---------+
| Hostname   | host      | created_date        | dbinfo  |
+------------+-----------+---------------------+---------+
| teguhth003 | teguhth01 | 2025-01-13 09:27:33 | testing |
| teguhth003 | teguhth01 | 2025-01-13 09:28:09 | testing |
+------------+-----------+---------------------+---------+
[root@ha01 ~]#
 

5. try using router maxscale

select *,@@hostname from teguhth.barang; -- maxscale route to slave
select *,@@hostname from teguhth.barang; -- maxscale route to master