1. Disable selinux & firewalld
[root@proxysql ~]# sestatus
SELinux status: disabled
[root@proxysql ~]#
[root@proxysql ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@proxysql ~]#
2. additional hosts
[root@proxysql ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.51 mariadb01
10.10.10.52 mariadb02
10.10.10.100 severnines
10.10.10.140 proxysql
[root@proxysql ~]#
3. install client mariadb mysql
[root@proxysql data]# yum install mariadb -y
4. download & install proxysql & enable booting
[root@proxysql data]# yum install wget -y
[root@proxysql data]# wget https://github.com/sysown/proxysql/releases/download/v2.4.1/proxysql-2.4.1-1-centos7.x86_64.rpm
[root@proxysql data]# yum install -y proxysql-2.4.1-1-centos7.x86_64.rpm
[root@proxysql data]# systemctl enable proxysql
[root@proxysql data]# systemctl start proxysql
[root@proxysql data]# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2022-06-19 14:40:59 WIB; 4s ago
Process: 1470 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 1472 (proxysql)
CGroup: /system.slice/proxysql.service
├─1472 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
└─1473 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
Jun 19 14:40:59 proxysql systemd[1]: Starting High Performance Advanced Proxy for MySQL...
Jun 19 14:40:59 proxysql proxysql[1470]: 2022-06-19 14:40:59 [INFO] Using config file /etc/proxysql.cnf
Jun 19 14:40:59 proxysql proxysql[1470]: 2022-06-19 14:40:59 [INFO] Current RLIMIT_NOFILE: 102400
Jun 19 14:40:59 proxysql proxysql[1470]: 2022-06-19 14:40:59 [INFO] Using OpenSSL version: OpenSSL 3.0.2 15 Mar 2022
Jun 19 14:40:59 proxysql proxysql[1470]: 2022-06-19 14:40:59 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
Jun 19 14:40:59 proxysql systemd[1]: Started High Performance Advanced Proxy for MySQL.
[root@proxysql data]#
5. Configure proxysql (change admin admin to admin 123)
mysql -u admin -p admin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> ';
UPDATE global_variables SET variable_value='admin:123' WHERE variable_name='admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;
[root@proxysql data]# mysql -u admin -p admin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> ';
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
ProxySQL> UPDATE global_variables SET variable_value='admin:123' WHERE variable_name='admin-admin_credentials';
Query OK, 1 row affected (0.01 sec)
ProxySQL> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL> SAVE ADMIN VARIABLES TO DISK;
Query OK, 46 rows affected (0.00 sec)
ProxySQL>
6. add backend
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.10.10.51',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.10.10.52',3306);
SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.10.10.51',3306);
Query OK, 1 row affected (0.00 sec)
ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.10.10.52',3306);
Query OK, 1 row affected (0.00 sec)
ProxySQL> SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
+--------------+-------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-------------+------+--------+
| 1 | 10.10.10.51 | 3306 | ONLINE |
| 1 | 10.10.10.52 | 3306 | ONLINE |
+--------------+-------------+------+--------+
2 rows in set (0.00 sec)
ProxySQL>
7. additional access user
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('testuser','123',1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
ProxySQL> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('testuser','123',1);
Query OK, 1 row affected (0.00 sec)
ProxySQL> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
ProxySQL>
8. Configure ProxySQL users for MySQL cluster:
CREATE USER 'testuser'@'%' IDENTIFIED BY '123';
GRANT ALL ON RECIPES.* TO 'testuser'@'%';
FLUSH PRIVILEGES;
[root@mariadb01 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.48-MariaDB 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)]> CREATE USER 'testuser'@'%' IDENTIFIED BY '123';
Query OK, 1 rows affected (0.02 sec)
MariaDB [(none)]> GRANT ALL ON RECIPES.* TO 'testuser'@'%';
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
9. create user monitoring
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT SELECT ON sys.* TO 'monitor'@'%';
FLUSH PRIVILEGES;
MariaDB [(none)]> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT ON sys.* TO 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
10. Configure ProxySQL Nodes Monitoring:
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
-- Load the changes into Runtime to apply the changes.
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;
ProxySQL> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
ProxySQL> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.01 sec)
ProxySQL> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)
ProxySQL> -- Load the changes into Runtime to apply the changes.
ProxySQL> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
ProxySQL> SAVE MYSQL VARIABLES TO DISK;
Query OK, 150 rows affected (0.00 sec)
ProxySQL> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
ProxySQL>
ProxySQL> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+-------------+------+------------------+-------------------------+-------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-------------+------+------------------+-------------------------+-------------------------------------------------------------------+
| 10.10.10.51 | 3306 | 1655625453822616 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625453787228 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.51 | 3306 | 1655625451824441 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625451786460 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625449819109 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.51 | 3306 | 1655625449782201 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625447815742 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.51 | 3306 | 1655625447781084 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.51 | 3306 | 1655625445805779 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625445778138 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
+-------------+------+------------------+-------------------------+-------------------------------------------------------------------+
10 rows in set (0.00 sec)
ProxySQL>
ProxySQL> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+-------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-------------+------+------------------+-------------------------+---------------+
| 10.10.10.51 | 3306 | 1655625910569489 | 2835 | NULL |
| 10.10.10.52 | 3306 | 1655625910541406 | 1802 | NULL |
| 10.10.10.51 | 3306 | 1655625908572559 | 1788 | NULL |
| 10.10.10.52 | 3306 | 1655625908538709 | 1203 | NULL |
| 10.10.10.52 | 3306 | 1655625906568950 | 1725 | NULL |
| 10.10.10.51 | 3306 | 1655625906537286 | 992 | NULL |
| 10.10.10.51 | 3306 | 1655625904578006 | 6623 | NULL |
| 10.10.10.52 | 3306 | 1655625904533563 | 9820 | NULL |
| 10.10.10.51 | 3306 | 1655625902562132 | 1603 | NULL |
| 10.10.10.52 | 3306 | 1655625902526537 | 2136 | NULL |
+-------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)
ProxySQL>
11. login mariadb from proxysql
mysql -u testuser -p 123 -h 127.0.0.1 -P 6033
[root@proxysql ~]# sestatus
SELinux status: disabled
[root@proxysql ~]#
[root@proxysql ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@proxysql ~]#
2. additional hosts
[root@proxysql ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.51 mariadb01
10.10.10.52 mariadb02
10.10.10.100 severnines
10.10.10.140 proxysql
[root@proxysql ~]#
3. install client mariadb mysql
[root@proxysql data]# yum install mariadb -y
4. download & install proxysql & enable booting
[root@proxysql data]# yum install wget -y
[root@proxysql data]# wget https://github.com/sysown/proxysql/releases/download/v2.4.1/proxysql-2.4.1-1-centos7.x86_64.rpm
[root@proxysql data]# yum install -y proxysql-2.4.1-1-centos7.x86_64.rpm
[root@proxysql data]# systemctl enable proxysql
[root@proxysql data]# systemctl start proxysql
[root@proxysql data]# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2022-06-19 14:40:59 WIB; 4s ago
Process: 1470 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 1472 (proxysql)
CGroup: /system.slice/proxysql.service
├─1472 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
└─1473 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
Jun 19 14:40:59 proxysql systemd[1]: Starting High Performance Advanced Proxy for MySQL...
Jun 19 14:40:59 proxysql proxysql[1470]: 2022-06-19 14:40:59 [INFO] Using config file /etc/proxysql.cnf
Jun 19 14:40:59 proxysql proxysql[1470]: 2022-06-19 14:40:59 [INFO] Current RLIMIT_NOFILE: 102400
Jun 19 14:40:59 proxysql proxysql[1470]: 2022-06-19 14:40:59 [INFO] Using OpenSSL version: OpenSSL 3.0.2 15 Mar 2022
Jun 19 14:40:59 proxysql proxysql[1470]: 2022-06-19 14:40:59 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
Jun 19 14:40:59 proxysql systemd[1]: Started High Performance Advanced Proxy for MySQL.
[root@proxysql data]#
5. Configure proxysql (change admin admin to admin 123)
mysql -u admin -p admin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> ';
UPDATE global_variables SET variable_value='admin:123' WHERE variable_name='admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;
[root@proxysql data]# mysql -u admin -p admin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> ';
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
ProxySQL> UPDATE global_variables SET variable_value='admin:123' WHERE variable_name='admin-admin_credentials';
Query OK, 1 row affected (0.01 sec)
ProxySQL> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL> SAVE ADMIN VARIABLES TO DISK;
Query OK, 46 rows affected (0.00 sec)
ProxySQL>
6. add backend
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.10.10.51',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.10.10.52',3306);
SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.10.10.51',3306);
Query OK, 1 row affected (0.00 sec)
ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.10.10.52',3306);
Query OK, 1 row affected (0.00 sec)
ProxySQL> SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
+--------------+-------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-------------+------+--------+
| 1 | 10.10.10.51 | 3306 | ONLINE |
| 1 | 10.10.10.52 | 3306 | ONLINE |
+--------------+-------------+------+--------+
2 rows in set (0.00 sec)
ProxySQL>
7. additional access user
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('testuser','123',1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
ProxySQL> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('testuser','123',1);
Query OK, 1 row affected (0.00 sec)
ProxySQL> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
ProxySQL>
8. Configure ProxySQL users for MySQL cluster:
CREATE USER 'testuser'@'%' IDENTIFIED BY '123';
GRANT ALL ON RECIPES.* TO 'testuser'@'%';
FLUSH PRIVILEGES;
[root@mariadb01 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.48-MariaDB 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)]> CREATE USER 'testuser'@'%' IDENTIFIED BY '123';
Query OK, 1 rows affected (0.02 sec)
MariaDB [(none)]> GRANT ALL ON RECIPES.* TO 'testuser'@'%';
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
9. create user monitoring
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT SELECT ON sys.* TO 'monitor'@'%';
FLUSH PRIVILEGES;
MariaDB [(none)]> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT ON sys.* TO 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
10. Configure ProxySQL Nodes Monitoring:
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
-- Load the changes into Runtime to apply the changes.
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;
ProxySQL> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
ProxySQL> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.01 sec)
ProxySQL> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)
ProxySQL> -- Load the changes into Runtime to apply the changes.
ProxySQL> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
ProxySQL> SAVE MYSQL VARIABLES TO DISK;
Query OK, 150 rows affected (0.00 sec)
ProxySQL> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
ProxySQL>
ProxySQL> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+-------------+------+------------------+-------------------------+-------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-------------+------+------------------+-------------------------+-------------------------------------------------------------------+
| 10.10.10.51 | 3306 | 1655625453822616 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625453787228 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.51 | 3306 | 1655625451824441 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625451786460 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625449819109 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.51 | 3306 | 1655625449782201 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625447815742 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.51 | 3306 | 1655625447781084 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.51 | 3306 | 1655625445805779 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
| 10.10.10.52 | 3306 | 1655625445778138 | 0 | Access denied for user 'monitor'@'proxysql' (using password: YES) |
+-------------+------+------------------+-------------------------+-------------------------------------------------------------------+
10 rows in set (0.00 sec)
ProxySQL>
ProxySQL> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+-------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-------------+------+------------------+-------------------------+---------------+
| 10.10.10.51 | 3306 | 1655625910569489 | 2835 | NULL |
| 10.10.10.52 | 3306 | 1655625910541406 | 1802 | NULL |
| 10.10.10.51 | 3306 | 1655625908572559 | 1788 | NULL |
| 10.10.10.52 | 3306 | 1655625908538709 | 1203 | NULL |
| 10.10.10.52 | 3306 | 1655625906568950 | 1725 | NULL |
| 10.10.10.51 | 3306 | 1655625906537286 | 992 | NULL |
| 10.10.10.51 | 3306 | 1655625904578006 | 6623 | NULL |
| 10.10.10.52 | 3306 | 1655625904533563 | 9820 | NULL |
| 10.10.10.51 | 3306 | 1655625902562132 | 1603 | NULL |
| 10.10.10.52 | 3306 | 1655625902526537 | 2136 | NULL |
+-------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)
ProxySQL>
11. login mariadb from proxysql
mysql -u testuser -p 123 -h 127.0.0.1 -P 6033
mysql -u testuser -p123 -h 127.0.0.1 -P 6033 -e 'select @@hostname as Hostname, @@version as Version'
mysql -u testuser -p123 -h 10.10.10.140 -P 6033 -e 'select @@hostname as Hostname, @@version as Version'
mysql -u testuser -p123 -h 10.10.10.140 -P 6033 -e 'select @@hostname as Hostname, @@version as Version'
[root@proxysql data]# mysql -u testuser -p -h 127.0.0.1 -P 6033
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ls |
| lst |
| lsti |
| mysql |
| performance_schema |
| sys |
| teguhth |
| tka |
+--------------------+
9 rows in set (0.02 sec)
MySQL [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| mariadb01 |
+------------+
1 row in set (0.00 sec)
MySQL [(none)]>
12. stop mariadb01 & check status
MySQL [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| mariadb01 |
+------------+
1 row in set (0.00 sec)
MySQL [(none)]> select @@hostname;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
+------------+
| @@hostname |
+------------+
| mariadb02 |
+------------+
1 row in set (0.01 sec)
MySQL [(none)]>
No comments:
Post a Comment