ProxySQL (3)

1. server 등록

ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.18.0.4',3306);
ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.18.0.5',3306);

ProxySQL> SELECT * FROM mysql_servers;

# RUNTIME에 반영
ProxySQL> LOAD MYSQL SERVERS TO RUNTIME;

2. replication group 등록

ProxySQL> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1, 2, 'cluster1');

# RUNTIME에 반영
ProxySQL> LOAD MYSQL SERVERS TO RUNTIME;

1: Write node hostgroup_id
2: Read node hostgroup_id
'cluster1': 그룹이름
서버 등록시 해당 1,2중에 결정해서 등록하여야 함.

runtime에 반영시 해당 노드의 read_only값을 기준으로 노드의 hostgroup_id가 변경됨.

3. 노드의 Readonly 로그 확인

ProxySQL> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 2;

mysql_replication_hostgroups 번호(writer_hostgroup,reader_hostgroup)에 맞는 노드가 mysql_servers에 존재하여야 로그가 남음.

4. mysql user 추가

ProxySQL> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','',1);

ProxySQL> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('user1','userpw',1);

ProxySQL> LOAD MYSQL USERS TO RUNTIME;
ProxySQL> SAVE MYSQL USERS TO DISK;