pt-query-digest는 percona 에서 만든 유용한 Toolkit 으로
slow query 가 많을 경우, 쿼리를 parsing하고 결과를 summary 하여 보여줍니다.
쿼리의 유형, 빈도, 비중 등을 확인할 수 있습니다.

 

SlowQuery 보기

슬로우쿼리를 요약해서 보여줍니다.

$ pt-query-digest slow.log

 

Binlog 보기

type 을 binlog 라고 따로 지정해줘야 합니다. (슬로우쿼리는 따로 지정하지 않아도 됩니다.)
binlog 에 남은 쿼리를 요약해서 보여줍니다.

$ mysqlbinlog bin-log.000002 > bin002.tmp
$ ./pt-query-digest --type binlog bin002.tmp

# 1.4s user time, 40ms system time, 22.74M rss, 173.82M vsz
# Current date: Tue Feb 21 13:16:08 2017
# Hostname: mytestdb
# Files: bin002.tmp
# Overall: 7.39k total, 26 unique, 7.39k QPS, 0x concurrency _____________
# Time range: 2016-11-17 08:43:34 to 08:43:35
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time              0       0       0       0       0       0       0
# Query size       788.45k       5  14.59k  109.27  511.45  490.66    5.75
# @@session.au           1       1       1       1       1       0       1
# @@session.au           1       1       1       1       1       0       1
# @@session.au           1       1       1       1       1       0       1
# @@session.ch          33      33      33      33      33       0      33
# @@session.co          33      33      33      33      33       0      33
# @@session.co          33      33      33      33      33       0      33
# @@session.fo           1       1       1       1       1       0       1
# @@session.lc           0       0       0       0       0       0       0
# @@session.ps           1       1       1       1       1       0       1
# @@session.sq           0       0       0       0       0       0       0
# @@session.sq       1.00G   1.00G   1.00G   1.00G   1.00G       0   1.00G
# @@session.un           1       1       1       1       1       0       1
# error code             0       0       0       0       0       0       0

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x9669C3CA8D682CF3  0.0000  0.0%   569 0.0000  0.00 INSERT SELECT help_topic
#    2 0x0CBEE2D590974E43  0.0000  0.0%     3 0.0000  0.00 INSERT SELECT help_topic
#    3 0x3111CFAC56B7F868  0.0000  0.0%     1 0.0000  0.00 INSERT UPDATE INSERT UPDATE INSERT UPDATE help_topic
#    4 0xBA1D332B93598A57  0.0000  0.0%     1 0.0000  0.00 INSERT help_topic
#    5 0x32AB55431077349C  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT UPDATE INSERT DELETE SELECT UPDATE REPLACE INSERT UPDATE INSERT SELECT DELETE SELECT help_topic
#    6 0x3D8FE5F7A4F35B39  0.0000  0.0%     1 0.0000  0.00 TRUNCATE TABLE help_topic
#    7 0xBB7C6C3AEDD369AB  0.0000  0.0%     1 0.0000  0.00 INSERT UPDATE SELECT UPDATE INSERT SELECT INSERT SELECT INSERT SELECT INSERT help_topic
#    8 0x7DF5027649B2002A  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#    9 0xBCBC01709CA2A938  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#   10 0x753EC8E016B3A117  0.0000  0.0%     1 0.0000  0.00 INSERT REPLACE SELECT INSERT REPLACE INSERT REPLACE INSERT DELETE REPLACE INSERT REPLACE help_topic
#   11 0x757E6F2238CC152D  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#   12 0xDAB48334253B1919  0.0000  0.0%     1 0.0000  0.00 INSERT UNION help_topic
#   13 0x5352E4133E954777  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#   14 0x1EFC2DE6CC335EDE  0.0000  0.0%     1 0.0000  0.00 INSERT UNION SELECT help_topic
#   15 0xFFDCE34F4BC1CA0F  0.0000  0.0%    40 0.0000  0.00 INSERT help_category
#   16 0x813031B8BBC3B329  0.0000  0.0%  2461 0.0000  0.00 COMMIT
#   17 0x06EBCB4968733E9A  0.0000  0.0%  1223 0.0000  0.00 INSERT help_relation
#   18 0x85FFF5AA78E5FF6A  0.0000  0.0%  2461 0.0000  0.00 BEGIN
#   19 0xC2BA4B26B17449FF  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#   20 0x2CF24E4EF7DA9A35  0.0000  0.0%     1 0.0000  0.00 TRUNCATE TABLE help_relation
# MISC 0xMISC              0.0000  0.0%   616 0.0000   0.0 <4 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0x9669C3CA8D682CF3 at byte 968549 ___
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-11-17 08:43:34
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          7     569
# Exec time      0       0       0       0       0       0       0       0
# Query size    76 606.70k     208  14.59k   1.07k   3.19k   1.40k  621.67
# error code     0       0       0       0       0       0       0       0
# String:
# Databases    mysql
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mysql` LIKE 'help_topic'\G
#    SHOW CREATE TABLE `mysql`.`help_topic`\G
INSERT INTO help_topic (help_topic_id,help_category_id,name,description,example,url) VALUES (584,17,'VERSION','Synt....

 

tcpdump 결과 보기

tcpdump 결과를 summary 해서 보여줍니다.

$ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

$ pt-query-digest --type tcpdump mysql.tcp.txt

 

'MySQL' 카테고리의 다른 글

MySQL sorted index 생성  (0) 2021.08.06
innodb_open_files과 open_files_limit  (0) 2021.08.04
MySQL 정적 및 동적 권한 2  (0) 2021.07.30
MySQL 정적 및 동적 권한  (0) 2021.07.29
MySQL 8.0 strage engine  (0) 2021.07.21

전에 DBA 계정에서 SUPER 권한을 제거하는 방법을 해보았습니다.

그러나 실생활에서는 반대로 계정이 올바르게 작동하는 데 필요한 것만 추가해야 합니다.

각 사용자에게 권한을 하나씩 추가하는 것은 그들이 가질 수 있는 상호 작용 수준을 고려할 때 문제가 있고 실수하기 쉽습니다. 

대신 ROLES를 사용하여 훨씬 더 쉬운 방법으로 올바른 권한을 그룹화, 할당 및 취소할 수 있습니다.

ROLES를 올바르게 사용하려면

첫 번째 단계는 ROLES를 식별하는 것입니다. 기본 원칙을 유지하고 단순하게 만들어야 합니다. 따라서 ROLES이 너무 많거나 교차 기능 권한이 너무 많은 ROLE을 사용하지 않도록 해야 합니다.

  • DBA(모든 것을 할 수 있음)
  • MaintenanceAdmin(서버에서 일부 작업만 수행할 수 있음)
  • UserAdmin(사용자가 권한 부여 등을 할당하도록 생성할 수 있음)
  • MonitorUser(모든 프로세스 보기 및 performance_schema에서 읽기)
  • DBManager(스키마/테이블/트리거/보기/루틴 등 추가/삭제/수정 가능)
  • DBDesigner (스키마/테이블에 의한 명확한 식별로 대부분 특정 객체 수정 가능)
  • ReplicationAdmin(GR도 복제 시작/중지 추가/변경/제거 가능)
  • BackupAdmin(백업 가능, 복원 불가)

8개의 관리 역할로 나누었습니다.

롤을 생성해 보겠습니다.

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16		 CREATE ROLE 'DBA', 'MaintenanceAdmin', 'UserAdmin', 'MonitorUser', 'DBManager', 'DBDesigner', 'ReplicationAdmin', 'BackupAdmin'


(root@localhost) [mysql]>Select user,host from mysql.user where account_locked ='Y' and password_expired='Y' order by 1;
+------------------+------+
| user             | host |
+------------------+------+
| BackupAdmin      | %    |
| DBA              | %    |
| DBDesigner       | %    |
| DBManager        | %    |
| MaintenanceAdmin | %    |
| MonitorUser      | %    |
| ReplicationAdmin | %    |
| UserAdmin        | %    |
+------------------+------+
8 rows in set (0.00 sec)

롤을 하나씩 확인하고 어떤 권한을 할당해야 하는지 알아보겠습니다.

MySQL

1
2
3		 (secure_test@localhost) [(none)]>show grants for current_user()\G
*************************** 1. row ***************************
Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost`

 

DBA

GRANT ALL 은 MySQL 8.0.x에서 더 이상 사용되지 않는 SUPER도 할당됩니다. 

필요한 것만 추가합니다.

MySQL

1
2
3		 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `DBA`@`%` WITH GRANT OPTION;
  
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `DBA`@`%` WITH GRANT OPTION;

GRANT ALL과 정확히 동일해야 하지만 SUPER는 제외됩니다. 

테스트 사용자에게 ROLE을 할당하려면:

MySQL

1 GRANT `DBA`@`%` TO `secure_test`@`localhost`

이제 사용자는 다음을 수행합니다.

MySQL

1
2
3
4
5		 (secure_test@localhost) [(none)]>show grants for current_user()\G
*************************** 1. row ***************************
Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost`
*************************** 2. row ***************************
Grants for secure_test@localhost: GRANT `DBA`@`%` TO `secure_test`@`localhost`

이제 DBA가 권한 부여로 표시되지만 활성 상태가 아닙니다.

 

MySQL

1
2		 (secure_test@localhost) [(none)]>show grants for DBA@'%'\G
ERROR 1142 (42000): SELECT command denied to user 'secure_test'@'localhost' for table 'user'

롤을 활성화하려면 명시적으로 수행해야 합니다.

 

MySQL

1  SET DEFAULT ROLE DBA TO  secure_test@'localhost';

그리고 사용자가 다시 연결하도록 합니다.

롤이 활성화되면 다음을 사용할 수도 있습니다.

 

MySQL

1 show grants for current_user()\G

현재 특정 사용자에 대해 어떤 권한이 활성화되어 있는지 확인합니다.

또한 mysql.default_roles 테이블을 쿼리하는 사용자에 대해 활성 롤을 제어할 수 있습니다. 

 

활성된 롤을 제거하려면:

MySQL

1 SET DEFAULT ROLE NONE TO  secure_test@'localhost';

어쨌든, 이제 우리는 모든 DBA가 사용할 수 있는 DBA 역할을 갖게 되었으며, 무언가를 변경해야 하는 경우 모든 단일 사용자가 아니라 ROLE에서 수행할 수 있습니다. 

 

MaintenanceAdmin

MySQL

1
2
3		 GRANT EVENT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, RELOAD, SHUTDOWN ON *.* TO `MaintenanceAdmin`@`%`;
GRANT BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN ON *.* TO `MaintenanceAdmin`@`%`;
GRANT `MaintenanceAdmin`@`%` TO `secure_test`@`localhost` ;

 

UserAdmin 

MySQL

1
2
3		 GRANT CREATE USER, GRANT OPTION, RELOAD, SHOW DATABASES ON *.* TO `UserAdmin`@`%`;
GRANT ROLE_ADMIN  ON *.* TO `UserAdmin`@`%`;
GRANT `UserAdmin`@`%` TO `secure_test`@`localhost` ;

 

MonitorUser 

MySQL

1
2
3		 GRANT PROCESS, REPLICATION CLIENT ON *.* TO `MonitorUser`@`%`;
GRANT SELECT ON performance_schema.* TO `MonitorUser`@`%`;
GRANT `MonitorUser`@`%` TO `secure_test`@`localhost` ;

 

DBManager

MySQL

1
2
3		 GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, INDEX, INSERT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE  ON *.* TO `DBManager`@`%`;
GRANT SET_USER_ID, SHOW_ROUTINE ON *.* TO `DBManager`@`%`;
GRANT `DBManager`@`%` TO `secure_test`@`localhost` ;

 

DBDesigner

MySQL

1
2		 GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE VIEW, INDEX, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO `DBDesigner`@`%`;
GRANT `DBDesigner`@`%` TO `secure_test`@`localhost` ;

 

ReplicationAdmin

MySQL

1
2
3
4
5		 GRANT REPLICATION CLIENT ON *.* TO `ReplicationAdmin`@`%`;
GRANT REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, SERVICE_CONNECTION_ADMIN ON *.* TO `ReplicationAdmin`@`%`;
GRANT SELECT on performance_schema.* TO `ReplicationAdmin`@`%`;
GRANT SELECT on mysql.* TO `ReplicationAdmin`@`%`;
GRANT `ReplicationAdmin`@`%` TO `secure_test`@`localhost` ;

 

BackupAdmin 

MySQL

1
2
3		 GRANT EVENT, LOCK TABLES, SELECT, SHOW DATABASES ON *.* TO `BackupAdmin`@`%`;
GRANT BACKUP_ADMIN ON *.* TO `BackupAdmin`@`%`;
GRANT `BackupAdmin`@`%` TO `secure_test`@`localhost` ;

 

모든 롤이 지정되면 테스트할 수 있습니다. 

예를 들어 ReplicationAdmin 에서 바이너리 로그를 확인하고 replication 중지/시작하는 것을 확인할 수 있습니다 .

 

MySQL

1
2
3
4
5		 (secure_test@localhost) [(none)]>show binary logs;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation


(secure_test@localhost) [(none)]>stop group_replication;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or GROUP_REPLICATION_ADMIN privilege(s) for this operation

롤은 아직 활성 상태가 아닙니다. 이제 사용자의 롤을 활성화하겠습니다.

MySQL

1 SET DEFAULT ROLE ReplicationAdmin  TO  secure_test@'localhost';

 

다시 접속 합니다.

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32		 (secure_test@localhost) [(none)]>show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000011 | 113802321 | No        |
| binlog.000012 |     19278 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)


(secure_test@localhost) [(none)]>stop group_replication;
Query OK, 0 rows affected (5.25 sec)


DC2-2(secure_test@localhost) [(none)]>select * from  performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5         |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)


(secure_test@localhost) [(none)]>start group_replication;
Query OK, 0 rows affected (3.70 sec)


(secure_test@localhost) [(none)]>select * from  performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 79ede65d-9797-11eb-9963-08002734ed50 | gr4         |        3306 | ONLINE       | PRIMARY     | 8.0.23         |
| group_replication_applier | 7e214802-9797-11eb-a0cf-08002734ed50 | gr6         |        3306 | ONLINE       | SECONDARY   | 8.0.23         |
| group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5         |        3306 | ONLINE       | SECONDARY   | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)

 

활성화된 권한은 다음과 같습니다.

MySQL

1
2
3
4
5
6
7
8
9
10
11
12		 DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G
*************************** 1. row ***************************
Grants for secure_test@localhost: GRANT REPLICATION CLIENT ON *.* TO `secure_test`@`localhost`
*************************** 2. row ***************************
Grants for secure_test@localhost: GRANT GROUP_REPLICATION_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,SERVICE_CONNECTION_ADMIN ON *.* TO `secure_test`@`localhost`
*************************** 3. row ***************************
Grants for secure_test@localhost: GRANT SELECT ON `mysql`.* TO `secure_test`@`localhost`
*************************** 4. row ***************************
Grants for secure_test@localhost: GRANT SELECT ON `performance_schema`.* TO `secure_test`@`localhost`
*************************** 5. row ***************************
Grants for secure_test@localhost: GRANT `BackupAdmin`@`%`,`DBA`@`%`,`DBDesigner`@`%`,`DBManager`@`%`,`MaintenanceAdmin`@`%`,`MonitorUser`@`%`,`ReplicationAdmin`@`%`,`UserAdmin`@`%` TO `secure_test`@`localhost`
5 rows in set (0.00 sec)

 

결론

ROLES를 사용하면 한 곳에서 필요한 권한을 수정할 수 있으며 동시에 구성 요소 또는 플러그인 사용으로 인한 동적 권한의 확산 가능성을 제어할 수 있어 여러 권한을 갖는 복잡성을 크게 줄일 수 있습니다. .

롤은 일반적으로 가장 일반적인 데이터베이스에서 사용되며 MySQL은 이를 상당히 늦게 구현했습니다. 그러나 롤을 사용하는 것은 소규모 플랫폼에서 중형/대형 플랫폼으로 이동할 때 사용자 권한을 처리하는 자연스러운 방법입니다. 

 

'MySQL' 카테고리의 다른 글

innodb_open_files과 open_files_limit  (0) 2021.08.04
pt-query-digest  (0) 2021.08.03
MySQL 정적 및 동적 권한  (0) 2021.07.29
MySQL 8.0 strage engine  (0) 2021.07.21
binlog2sql: Binlog를 이용한 Point In Time Recovery  (0) 2020.08.04

MySQL 8에서 정적 권한과 동적 권한 간의 상호 작용에 대해 더 자세히 알아야 할 때 Security Threat Tool 스크립트를 작성하고 있었습니다 .

동적 권한은 권한 정의를 쉽게 확장하는 동시에 더 세분화된 기능을 제공하기 위해 MySQL 8에 추가된 "새로운" 기능입니다. 예를 들어 FLUSH 작업에는 이제 범위별로 전용 권한이 있습니다. 

동적 권한은 런타임에 할당됩니다. 대부분은 서버가 시작될 때 활성화됩니다. 그러나 활성화되면 구성 요소 또는 플러그인과 관련하여 변경할 수도 있습니다. ( https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-dynamic )

정적 권한은 MySQL에서 사용할 수 있는 고전적인 권한입니다. ( https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-static )

그것들은 서버에 내장되어 있으며 변경할 수 없습니다.

 

먼저 사용자를 생성하겠습니다.

MySQL

1
2
3
4
5
6
7		 create user secure_test@'localhost' identified by 'secret';
(secure_test@localhost) [(none)]>show grants for current_user();
+-------------------------------------------------+
| Grants for secure_test@localhost                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `secure_test`@`localhost` |
+-------------------------------------------------+

보시다시피 연결할 수 있지만 권한이 없습니다.

관리 계정이 있는 다른 터미널에서 DBA를 생성하는 고전적인 작업을 수행해 보겠습니다.

 

MySQL

1 GRANT ALL on *.* to secure_test@'localhost' WITH GRANT OPTION;

 

MySQL

1
2
3
4
5		 (secure_test@localhost) [(none)]>show grants for current_user()\G
*************************** 1. row ***************************
Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION

보시다시피 저에게는 많은 권한이 할당되어 있습니다. 

각 권한이 수행하는 작업과 다른 권한과 상호 작용하는 방식을 정확히 식별하는 것은 어렵습니다.

super_read_only 변수를 수정을 통해 SUPER 권한을 제거하면 어떻게 동작하는지 확인 해 봅니다.

 

MySQL

1 revoke SUPER on *.* from secure_test@'localhost';

 

MySQL

1
2
3
4
5		 (secure_test@localhost) [(none)]>show grants for current_user()\G
*************************** 1. row ***************************
Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION

보시다시피 SUPER는 사라졌습니다. 

 

MySQL

1
2		 (secure_test@localhost) [(none)]>set global super_read_only=0;
Query OK, 0 rows affected (0.00 sec)

그리고 여전히 전역 변수를 수정할 수 있습니다.

매뉴얼에 따르면 동적 권한의 SYSTEM_VARIABLES_ADMIN 을 통해 " 런타임 시 시스템 변수 변경 활성화 "를 수정할 수 있습니다 .

SYSTEM_VARIABLES_ADMIN 권한을 회수 합니다.

 

MySQL

1
2
3
4		 revoke SYSTEM_VARIABLES_ADMIN on *.* from  secure_test@'localhost';


(secure_test@localhost) [(none)]>set global super_read_only=0;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

super를 실제로 제거/제한하려면 SYSTEM_VARIABLES_ADMIN 도 제거해야 합니다 .

 

메뉴얼을 확인하면 SUPER가 이 모든 것에 영향을 미치고 있음을 알 수 있습니다.

  • BINLOG_ADMIN,
  • CONNECTION_ADMIN,
  • ENCRYPTION_KEY_ADMIN,
  • GROUP_REPLICATION_ADMIN,
  • REPLICATION_SLAVE_ADMIN,
  • SESSION_VARIABLES_ADMIN,
  • SET_USER_ID,
  • SYSTEM_VARIABLES_ADMIN

그리고 활성 플러그인에 따라 다른 플러그인을 사용할 수도 있습니다. 

따라서 모든 SUPER 관련 권한을 제거하려면 다음을 수행해야 합니다.

 

MySQL

1 REVOKE SUPER, BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN on *.* from secure_test@'localhost';

이렇게 하면 SUPER가 없는 사용자와 비슷한 권한만 남게 됩니다.

 

MySQL

1
2
3
4
5		 DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G
*************************** 1. row ***************************
Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SHOW_ROUTINE,SYSTEM_USER,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION

 

결론

SUPER 권한을 제거 했지만, 위의 목록은 여전히 SHUTDOWN, RELOAD 또는 FILE이 있으며 모두 안전하지 않으므로 신중하게 할당해야 합니다. 

'MySQL' 카테고리의 다른 글

pt-query-digest  (0) 2021.08.03
MySQL 정적 및 동적 권한 2  (0) 2021.07.30
MySQL 8.0 strage engine  (0) 2021.07.21
binlog2sql: Binlog를 이용한 Point In Time Recovery  (0) 2020.08.04
mysql 벤치 마크 툴 sysbench  (0) 2020.04.16

Alert Manager

모니터링 시스템이니, 문제가 발생하면 이를 알람으로 보내주는 역할도 있어야한다. Alertmanager는 Prometheus에서 문제가 발생했다고 생각되는 시점에 slack, hipchat 등을 통해 알람을 보내준다.

알람을 거는 기준은 Rule을 작성해서 load시키는 방식으로 동작하는데

expr: job:request_latency_seconds:mean5m{job="myjob"} > 0.5

와 같이 expression을 작성하는 것으로 알람을 전송할 수 있다. 특정 메트릭의 값이 어느정도 선(threshold)을 넘는다거나, 낮아진다거나 하는 메트릭을 보고 판단을 할 수 있다.

다만, 이 또한 Grafana를 사용하게 된다면 Grafana에서도 동일하게 알람매니저를 제공을 하고 있는데, 아무래도 Grafana쪽이 사용하기 더 쉽고 직관적이기 때문에 이걸 직접 사용할까는 싶지만, 그래도 expression을 이용해서 더 복잡한 조건을 걸어서 알람을 노티해주는 방식이 있다는건 좋다.

 

대시보드에서 무언가를 보기 위해서는 일단 prometheus.yml 의 내용을 보도록 하자

prometheus.yml

# my global config
global:
  scrape_interval:     15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
  # scrape_timeout is set to the global default (10s).

# Alertmanager configuration
alerting:
  alertmanagers:
  - static_configs:
    - targets:
      # - alertmanager:9093

# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
  # - "first_rules.yml"
  # - "second_rules.yml"

# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: 'prometheus'

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
    - targets: ['localhost:9090']

설정이 다른 모니터링 서비스들에 비해서는 매우 간단한 편이다. (모든 설정이 간편한건 아니다. default로 설정 되어있는 config가 간단해서 그렇지, 실제로 모든 config의 field를 보면 상당히 양이 많다. 자세한건 https://prometheus.io/docs/prometheus/latest/configuration/configuration/)

config 설명

  • global.scrape_interval : 몇 초 단위로 메트릭을 수집할 지를 결정(15초로 기본으로 되어 있는데, 이를 수정하여 1초단위로 수집을 할 지.. 10초 단위로 수집할지.. 1분 단위로 수집할지를 결정할 수 있다)
  • global.evaluation_interval : 이건 alerting rule을 어느 주기로 evaluate를 할 것인지에 대한 설정이다. evaluate를 하게되면 그 결과를 바탕으로 (inactive, pending, firing) 의 세 state로 구분이 되고 그 상태를 기반으로 alertmanager에서 알람을 발송할 지 안할지를 결정할 수 있다
    • alertmanager의 rule은 위에서 언급한대로 expression을 사용해서 설정을 할 수 있다
  • alerting : alertmanager의 target port를 지정할 수 있다. prometheus를 실행하면 alertmanager가 자동으로 같이 뜨는게 아니라, alertmanager를 download해서 같이 구동을 시켜줘야 한다
  • rule_files : expression으로 구성된 rule file의 path를 지정해준다. 복수개의 파일을 지정할 수 있다
  • scrape_config : 이부분이 중요하다.
    • job_name : 메트릭을 수집해서 구분을 할 네이밍을 지정
    • static_configs.targets : 실제 메트릭을 수집할 서버의 주소를 지정한다. 리스트로 구성이 되어 있으며 여러개의 호스트를 지정할 수 있다

 

alter manager 실행

> setproxy wget https://github.com/prometheus/alertmanager/releases/download/v0.21.0/alertmanager-0.21.0.linux-amd64.tar.gz

> tar -xzf alertmanager-0.21.0.linux-amd64.tar.gz

> cd alertmanager-0.21.0.linux-amd64

> ./alertmanager

웹 브라우저에서 http://prometheus-main.ay1.krane.9rum.cc:9093 로 접속하면 다음과 같은 화면을 볼 수 있다.

 

프로메테우스가 alter manager를 모니터링 하려면 prometheus.yml 파일에 Alertmanager configuration 항목을 추가 해야 한다.

> cd prometheus-2.2.1.linux-amd64/

> vi prometheus.yml

# Alertmanager configuration
alerting:
alertmanagers:
- static_configs:
- targets:
- alertmanager:9093

'ETC DB' 카테고리의 다른 글

prometheus 실행  (0) 2021.07.22
prometheus TSDB format  (0) 2021.07.21
prometheus TSDB 관리 API  (0) 2021.07.21
prometheus disk storage  (0) 2021.07.21
prometheus 데이터 구조  (2) 2021.07.21

+ Recent posts

티스토리툴바