[MySQL - Tunner 설치]

MySQL Tunner란

MySQL현재 설정값과 서버의 사양, 사용상태들을 비교하여 MySQL설정을 하는 방법으로, perl 스크립트로 동작하며 현재 설정과 서버의 사양 , 사용 상태들을 비교하여 mysql 의 설정을 어떻게 바꾸면 좋을지 몇 가지 기본 성능 제안과 함께 간단한 형식으로 알려주는 툴입니다.

MySQLTuner 는 MySQL / MariaDB / Percona Server에 대해 300 여개의 지표를 지원합니다.

MySQL, MariaDB 에서 동작하며, 리눅스에서 동작하나, 윈도우에서는 지원하지 않습니다.

저와같은 초보 DB관리자가 성능개선을 위해 이것저것 알아볼때 빠뜨리는 부분을 확인 할 수 있어 좋습니다.

■ 호환성 목록

[MySQL]

MySQL 8 (partial support, password checks don't work)

MySQL 5.7 (full support)

MySQL 5.6 (full support)

MySQL 5.5 (full support, no more MySQL support)

[MariaDB]

MariaDB 10.5 (partial support)

MariaDB 10.4 (full support)

MariaDB 10.3 (full support)

MariaDB 10.2 (full support)

MariaDB 10.1 (full support)

MariaDB 10.0 (full support, 6 last month support)

MariaDB 5.5 (full support, no more MariaDB support)

[Percona]

Percona Server 8.0 (partial support, password checks don't work)

Percona Server 5.7 (full support)

Percona Server 5.6 (full support)

Percona XtraDB cluster (partial support, no test environment)

Mysql Replications (partial support, no test environment)

Galera replication (partial support, no test environment)

MySQL 3.23, 4.0, 4.1, 5.0, 5.1, 5.5 (partial support - deprecated version)

Perl은 최소 5.6 이상 필요 하며 perl-doc 패키지도 같이 필요 합니다

Unix/Linux based 환경만 지원 합니다

테스트 환경

Hostname	OS version	IP	DB version
mysql	CentOS Linux release 7.3.1611	192.168.100.84	MySQL 8.0.28

※ 사전 확인

MySQL 5.7.7 버전부터 기본포함이며 5.6 버전 부터 별도로 설치가 가능 합니다. 5.6 버전을 사용한다면 별도로 SYS Schema 를 설치 해야 합니다.

■ MySQL Tunner Download

[root@mysql ~]# wget http://mysqltuner.pl/ -O mysqltuner.pl
--2022-11-04 13:37:00--  http://mysqltuner.pl/
Resolving mysqltuner.pl (mysqltuner.pl)... 217.70.184.38
Connecting to mysqltuner.pl (mysqltuner.pl)|217.70.184.38|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl [following]
--2022-11-04 13:37:01--  https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Resolving raw.github.com (raw.github.com)... 185.199.111.133, 185.199.108.133, 185.199.110.133, ...
Connecting to raw.github.com (raw.github.com)|185.199.111.133|:443... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl [following]
--2022-11-04 13:37:02--  https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.111.133, 185.199.110.133, 185.199.109.133, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.111.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 245210 (239K) [text/plain]
Saving to: ‘mysqltuner.pl’

100%[===================================================================================================================================================>] 245,210     --.-K/s   in 0.003s  

2022-11-04 13:37:02 (69.7 MB/s) - ‘mysqltuner.pl’ saved [245210/245210]




[root@mysql ~]# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
--2022-11-04 13:37:07--  https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.108.133, 185.199.110.133, 185.199.111.133, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.108.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 3988 (3.9K) [text/plain]
Saving to: ‘basic_passwords.txt’

100%[===================================================================================================================================================>] 3,988       --.-K/s   in 0s      

2022-11-04 13:37:07 (63.6 MB/s) - ‘basic_passwords.txt’ saved [3988/3988]




[root@mysql ~]# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
--2022-11-04 13:37:14--  https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.110.133, 185.199.111.133, 185.199.109.133, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.110.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2150147 (2.0M) [text/plain]
Saving to: ‘vulnerabilities.csv’

100%[===================================================================================================================================================>] 2,150,147   --.-K/s   in 0.01s   

2022-11-04 13:37:14 (178 MB/s) - ‘vulnerabilities.csv’ saved [2150147/2150147]

■ Minimal 점검 (최소한의 정보 점검 실행)

[root@mysql ~]# perl mysqltuner.pl
>>  MySQLTuner 2.0.9
     * Jean-Marie Renouard <jmrenouard@gmail.com>
     * Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: <DB Adminuser>
Please enter your MySQL administrative password: <DB Adminuser password>
[OK] Currently running supported MySQL version 8.0.28
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /log/mariadb.log exists
[--] Log file: /log/mariadb.log (59K)
[OK] Log file /log/mariadb.log is not empty
[OK] Log file /log/mariadb.log is smaller than 32 Mb
[OK] Log file /log/mariadb.log is readable.
[!!] /log/mariadb.log contains 242 warning(s).
[!!] /log/mariadb.log contains 111 error(s).
[--] 14 start(s) detected in /log/mariadb.log
[--] 1) 2022-11-02T05:12:42.369015Z 0 [System] [MY-010931] [Server] /mysql/bin/mysqld: ready for connections. Version: '8.0.28'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 2) 2022-11-02T05:12:42.368342Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
[--] 3) 2022-10-03T23:52:43.999076Z 0 [System] [MY-010931] [Server] /mysql/bin/mysqld: ready for connections. Version: '8.0.28'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 4) 2022-10-03T23:52:43.999032Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
[--] 5) 2022-09-28T23:27:05.654719Z 0 [System] [MY-010931] [Server] /mysql/bin/mysqld: ready for connections. Version: '8.0.28'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 6) 2022-09-28T23:27:05.654086Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
[--] 7) 2022-09-22T22:36:24.828347Z 0 [System] [MY-010931] [Server] /mysql/bin/mysqld: ready for connections. Version: '8.0.28'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 8) 2022-09-22T22:36:24.828304Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
[--] 9) 2022-08-19T05:17:53.431102Z 0 [System] [MY-010931] [Server] /mysql/bin/mysqld: ready for connections. Version: '8.0.28'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 10) 2022-08-19T05:17:53.431031Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
[--] 8 shutdown(s) detected in /log/mariadb.log
[--] 1) 2022-10-06T21:56:36.438689Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.
[--] 2) 2022-10-03T23:52:36.559439Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.
[--] 3) 2022-09-23T07:26:22.894996Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.
[--] 4) 2022-09-22T22:38:22.594730Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.
[--] 5) 2022-08-26T22:19:28.523595Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.
[--] 6) 2022-08-19T05:17:46.781341Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.
[--] 7) 2022-08-19T05:12:23.298416Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.
[--] 8) 2022-08-19T05:11:48.659089Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 32.0K (Tables: 2)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8
-------- CVE Security Recommendations --------------------------------------------------------------
[!!] CVE-2022-21412(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21413(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: DML). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21414(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21415(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Replication). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21417(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: InnoDB). Supported versions that are affected are 5.7.37 and prior and 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21418(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: InnoDB). Supported versions that are affected are 8.0.28 and prior. Difficult to exploit vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server as well as unauthorized update
[!!] CVE-2022-21423(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: InnoDB). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a partial denial of service (partial DOS) of MySQL Server. CVSS 3.1 Base Score 2.7 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:L)."
[!!] CVE-2022-21425(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: DDL). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server as well as unauthorized update
[!!] CVE-2022-21427(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: FTS). Supported versions that are affected are 5.7.37 and prior and 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21435(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21436(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21437(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21438(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21440(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server as well as unauthorized update
[!!] CVE-2022-21444(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: DDL). Supported versions that are affected are 5.7.37 and prior and 8.0.28 and prior. Difficult to exploit vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.4 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:H/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21451(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: InnoDB). Supported versions that are affected are 5.7.37 and prior and 8.0.28 and prior. Difficult to exploit vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.4 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:H/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21452(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21454(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Group Replication Plugin). Supported versions that are affected are 5.7.37 and prior and 8.0.28 and prior. Easily exploitable vulnerability allows low privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 6.5 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:L/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21457(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: PAM Auth Plugin). Supported versions that are affected are 8.0.28 and prior. Difficult to exploit vulnerability allows unauthenticated attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized access to critical data or complete access to all MySQL Server accessible data. CVSS 3.1 Base Score 5.9 (Confidentiality impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:N/A:N)."
[!!] CVE-2022-21459(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server as well as unauthorized update
[!!] CVE-2022-21460(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Logging). Supported versions that are affected are 5.7.37 and prior and 8.0.28 and prior. Difficult to exploit vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized access to critical data or complete access to all MySQL Server accessible data. CVSS 3.1 Base Score 4.4 (Confidentiality impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:H/PR:H/UI:N/S:U/C:H/I:N/A:N)."
[!!] CVE-2022-21462(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.1 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
[!!] CVE-2022-21478(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server as well as unauthorized update
[!!] CVE-2022-21479(<= 8.0.28) : "Vulnerability in the MySQL Server product of Oracle MySQL (component: Server: Optimizer). Supported versions that are affected are 8.0.28 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server and unauthorized read access to a subset of MySQL Server accessible data. CVSS 3.1 Base Score 5.5 (Confidentiality and Availability impacts). CVSS Vector: (CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:L/I:N/A:H)."
[!!] 24 CVE(s) found for your MySQL release.
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 23h 34m 55s (787 q [0.005 qps], 232 conn, TX: 725K, RX: 74K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 3.5G
[--] Max MySQL memory    : 10.1G
[--] Other process memory: 0B
[--] Total buffers: 168.0M global + 65.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 223M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 523.4M (14.81% of installed RAM)
[!!] Maximum possible memory usage: 10.1G (292.57% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/787)
[OK] Highest usage of available connections: 1% (2/151)
[!!] Aborted connections: 12.93% (30/232)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 2% (5 temp sorts / 211 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 2% (8 on disk / 275 total)
[OK] Thread cache hit rate: 98% (3 created / 232 connections)
[OK] Table cache hit rate: 90% (9K hits / 10K requests)
[OK] table_definition_cache (2000) is greater than number of tables (328)
[OK] Open file limit used: 0% (6/8K)
[OK] Table locks acquired immediately: 100% (186 immediate / 186 locks)
[OK] Binlog cache memory access: 100.00% (4 Memory / 4 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 223.6M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled since MySQL 8.0.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M / 32.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 48.0M * 2 / 128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 98.04% (55111 hits / 56215 total)
[!!] InnoDB Write Log efficiency: 89.33% (904 hits / 1012 total)
[OK] InnoDB log waits: 0.00% (0 waits / 108 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Check warning line(s) in /log/mariadb.log file
    Check error line(s) in /log/mariadb.log file
    24 CVE(s) found for your MySQL release. Consider upgrading your version !
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Buffer Key MyISAM set to 0, no MyISAM table detected
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    skip-name-resolve=1
    key_buffer_size=0
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals 25% of buffer pool size.

■ Maximum 점검 (디버깅 없이 모든 환경 설정 점검)

[root@mysql ~]# perl mysqltuner.pl --buffers --dbstat --idxstat --sysstat --pfstat --tbstat --outputfile /root/mysqltuner.txt
>>  MySQLTuner 2.0.9
     * Jean-Marie Renouard <jmrenouard@gmail.com>
     * Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: <DB Adminuser>
Please enter your MySQL administrative password: <DB Adminuser password>
[OK] Currently running supported MySQL version 8.0.28
[OK] Operating on 64-bit architecture
...
생략
...

결과값이 길기때문에 파일로 생성하여 보는것이 좋습니다.

문제가 되는 부분은 결과에서 [!!]로 표시해주기 때문에 정정할 방안을 모색해야합니다.

MySQLTunner.pl에서 제안하는 'Recommendations'를 적극 참고합니다.

운영중인 DB 서버에서 Maximum 점검을 하게 되면 많은 결과값이 나오므로 Minimal로 점검하여 참고 합니다.

참고

https://hoing.io/archives/7154

MySQLTuner - MySQL 성능(튜닝) 진단 툴 - 성능 및 구성 최적화

hoing.io

https://yunhyeonglee.tistory.com/5

MySQLTunner.pl - MySQL 성능 진단 툴

MySQLTunner 는 MySQL 설치를 빠르게 검토하고 성능과 안전성을 높이기 위해 조정할 수 있는 Perl로 작성된 스크립트이다. 현재 구성 변수 및 상태 데이터가 검색되어 몇가지 기본 성능 제안 함께 간단

yunhyeonglee.tistory.com

저작자표시

'DataBase > MySQL & MariaDB' 카테고리의 다른 글

[MySQL - Orchestrator 구축] part 1 (2)	2023.02.05
[MySQL - Read/Write Split 부하분산 구성] use. ProxySQL (0)	2023.01.27
[MySQL - mysql-proxy] part 2. Read/Write Split & 부하테스트 (0)	2022.09.27
[MySQL - mysql-proxy 설치] part 1. (0)	2022.09.27
[MySQL - phpMyAdmin 설치] (0)	2022.09.27

'DataBase > MySQL & MariaDB' 카테고리의 다른 글

티스토리툴바