JJON's StorY

MySQL 구성 변수의 설정은 데이터베이스 시스템의 성능에 근본적인 영향을 미칩니다. 때로는 한 변수를 변경하는 것이 다른 변수에 어떤 영향을 미칠 수 있는지 예측하는 것이 약간 까다로울 수 있습니다. 특히 결과가 매우 직관적이지 않은 경우를 처리할 때 그렇습니다. 따라서 여기에서는 open_files_limit이 innodb_open_files보다 높게 설정하면 어떻게 되는지 살펴보겠습니다.

다음을 사용하여 MySQL 구성 파일에서 열린 파일의 최대 수를 설정할 수 있습니다.

open_files_limit=10000

이 값이 설정되지 않은 경우 기본값(MySQL 5.7에서는 5,000)을 사용해야 합니다.

open_files_limit 값이 설정되면 infinity로 설정되지 않는 한 SystemD의 LIMIT_NOFILES를 사용합니다(CentOS 7에서는 65536을 사용하지만 수동으로 지정하면 훨씬 더 높은 값이 가능함).

[root@centos7-pxc57-3 ~]# grep open_files_limit /etc/my.cnf
open_files_limit=10000
[root@centos7-pxc57-3 ~]# grep LimitNOFILE /lib/systemd/system/mysqld.service.d/limit_nofile.conf
LimitNOFILE=infinity
[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit”
+--------------------+
| @@open_files_limit |
+--------------------+
| 65536              |
+--------------------+
[root@centos7-pxc57-3 ~]# perl -pi -e ’s/LimitNOFILE=infinity/LimitNOFILE=20000/‘ /lib/systemd/system/mysqld.service.d/limit_nofile.conf && systemctl daemon-reload && systemctl restart mysqld
[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit”
+--------------------+
| @@open_files_limit |
+--------------------+
| 20000              |
+--------------------+
[root@centos7-pxc57-3 ~]# perl -pi -e ’s/LimitNOFILE=20000/LimitNOFILE=5000/‘ /lib/systemd/system/mysqld.service.d/limit_nofile.conf && systemctl daemon-reload && systemctl restart mysqld
[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit”
+--------------------+
| @@open_files_limit |
+--------------------+
| 5000               |
+--------------------+

위에서 볼 수 있듯이 MySQL은 open_files_limit을 시스템이 허용하도록 구성된 값 보다 높은 값을 설정할 수 없으며 open_files_limit너무 높게 설정되면 기본값으로 최대값으로 다시 설정됩니다.

그것은 매우 간단해 보이지만, MySQL이 동시에 얼마나 많은 .ibd 파일을 열수 있는지 innodb_open_files에 따라 결정됩니다.

파일을 열기 위해서는 open_files_limit 보다 낮아야 합니다. 만약 더 높게 설정하려고 하면 MySQL은 로그 파일에 warning을 출력합니다.

[root@centos7-pxc57-3 ~]# grep innodb_open_files /var/log/mysqld.log 
2018-09-21T08:31:06.002120Z 0 [Warning] InnoDB: innodb_open_files should not be greater than the open_files_limit.

warning을 출력되지 않도록 값을 낮추어야 합니다.

[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@innodb_open_files”
+---------------------+
| @@innodb_open_files |
+---------------------+
| 2000                |
+---------------------+

왜 2000 으로 설정 되었을까요?

innodb_open_files 너무 높게 설정 하면 문서에 따라 다음과 같이 기본값으로 설정이 되기 때문입니다.

innodb_file_per_table이 활성화되어 있지 않으면 300이고, 300과 table_open_cache 중 더 높은 값으로 설정 됩니다. 
5.6.6 이전에는 기본값이 300입니다.

그리고 table_open_cache는 MySQL 5.6.7 이하 버전의 경우 기본값은 400이고 5.6.8 이후 버전은 2000입니다.

참고로 table_open_cache 완전히 다른 설정입니다. innodb_open_files은 서버가 한 번에 열어둘 수 있는 InnoDB 파일( .ibd)의 수를 제어합니다 . table_open_cache은 서버가 한 번에 열 수 있는 테이블 정의 파일( .frm)의 수 를 제어합니다 .

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

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

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

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

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

ROLES를 올바르게 사용하려면

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

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

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

롤을 생성해 보겠습니다.

MySQL

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

MySQL

DBA

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

필요한 것만 추가합니다.

MySQL

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

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

MySQL

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

MySQL

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

MySQL

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

MySQL

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

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

MySQL

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

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

활성된 롤을 제거하려면:

MySQL

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

MaintenanceAdmin

MySQL

UserAdmin 

MySQL

MonitorUser 

MySQL

DBManager

MySQL

DBDesigner

MySQL

ReplicationAdmin

MySQL

BackupAdmin 

MySQL

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

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

MySQL

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

MySQL

다시 접속 합니다.

MySQL

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

MySQL

결론

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

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

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

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

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

MySQL

MySQL

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

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

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

MySQL

MySQL

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

MySQL

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

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

SYSTEM_VARIABLES_ADMIN 권한을 회수 합니다.

MySQL

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

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

MySQL

결론

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