proxysql mysql_server 데이터 입력 문의

Question

proxysql mysql_servers 테이블에 입력은 4줄인데 LOAD MYSQL SERVERS TO RUNTIME을 실행하면 hostgroup_id가 10인게 db001,db002,db003 세개로 생겨버립니다. 그래서 write를 하게 되면 db001에만 들어가는게 아니라 다른 서버에도 데이터가 들어가지고 있는데 왜 이런지 아실까요?

김영헌 · Answer

제가 수행했던 로그입니다. # mysql -h127.0.0.1 -P16032 -uradmin -pradmin --prompt "ProxySQL Admin>" mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ProxySQL Admin>select * from mysql_servers; Empty set (0.00 sec) ProxySQL Admin>load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec) ProxySQL Admin>select * from mysql_servers; Empty set (0.00 sec) ProxySQL Admin>save mysql servers to disk; Query OK, 0 rows affected (0.02 sec) ProxySQL Admin>select * from mysql_servers; Empty set (0.01 sec) ProxySQL Admin>INSERT INTO mysql_servers(hostgroup_id, hostname, port) -> VALUES (10, 'db001', 3306); Query OK, 1 row affected (0.00 sec) ProxySQL Admin>INSERT INTO mysql_servers(hostgroup_id, hostname, port) -> VALUES (20, 'db001', 3306); Query OK, 1 row affected (0.00 sec) ProxySQL Admin> ProxySQL Admin>INSERT INTO mysql_servers(hostgroup_id, hostname, port) -> VALUES (20, 'db002', 3306); Query OK, 1 row affected (0.00 sec) ProxySQL Admin>INSERT INTO mysql_servers(hostgroup_id, hostname, port) -> VALUES (20, 'db003', 3306); Query OK, 1 row affected (0.00 sec) ProxySQL Admin>INSERT INTO mysql_replication_hostgroups -> VALUES (10,20,'read_only',''); Query OK, 1 row affected (0.00 sec) ProxySQL Admin> ProxySQL Admin>select * from mysql_servers; +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | db001 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | db001 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | db002 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | db003 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.01 sec) ProxySQL Admin>LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) ProxySQL Admin>select * from mysql_servers; +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | db001 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | db001 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | db002 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | db003 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.01 sec) ProxySQL Admin>SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.03 sec) ProxySQL Admin>select * from mysql_servers; +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | db001 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | db001 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | db002 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | db003 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec)

김영헌 · Answer

안녕하세요. 일단 질문 주신 내용에 대해서 제가 직접 수행해 봤는데요. 말씀하신 현상은 재현되지 않았습니다. proxysql docker를 삭제하고 /db/proxysql/data 디렉토리의 파일들도 모두 삭제한 후에 다시 생성해서 수행해도 동일한 현상이 나타나나요?

김영헌 · Answer

안녕하세요. 4건을 입력했는데 데이터가 저렇게 나온다는 얘기신거죠?? hostgroup_id가 10인 db002, db003 을 delete로 삭제해도 load mysql servers to runtime; 을 하면 다시 위와 같은 상태로 돌아가나요?

더스와이스 · Answer

입력은 4줄 했는데 LOAD MYSQL SERVERS TO RUNTIME; 하면 아래와 같이 나와요