-
질문 & 답변
카테고리
-
세부 분야
데이터베이스
-
해결 여부
미해결
proxysql mysql_server 데이터 입력 문의
22.07.05 16:45 작성 조회수 288
0
proxysql mysql_servers 테이블에 입력은 4줄인데
LOAD MYSQL SERVERS TO RUNTIME을 실행하면
hostgroup_id가 10인게 db001,db002,db003 세개로 생겨버립니다. 그래서 write를 하게 되면 db001에만 들어가는게 아니라 다른 서버에도 데이터가 들어가지고 있는데 왜 이런지 아실까요?
답변을 작성해보세요.
0
김영헌
지식공유자2022.07.12
제가 수행했던 로그입니다.
# 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)
0
김영헌
지식공유자2022.07.12
안녕하세요.
일단 질문 주신 내용에 대해서 제가 직접 수행해 봤는데요.
말씀하신 현상은 재현되지 않았습니다.
proxysql docker를 삭제하고 /db/proxysql/data 디렉토리의 파일들도 모두 삭제한 후에
다시 생성해서 수행해도 동일한 현상이 나타나나요?
0
답변 4