fbpx
27.8 C
Jakarta
Selasa, 25 Juni 2024

Cara Memperbaiki Pesan MySQLTUNER [!!] Attempted to use login credentials from Debian maintenance account, but they failed.

MySQLTuner adalah skrip yang ditulis dalam Perl yang membantu kita menganalisis dan mengkonfigurasi server database MySQL/MariaDB untuk mendapatkan peningkatan kinerja, stabilitas, dan lingkungan server yang seimbang.

Namun pada kondisi tertentu kita tidak bisa mengeksekusi perintah mysqltuner. Error tersebut paling sering terjadi pada sistem Debian/Ubuntu, dimana kita benar-benar tidak dapat menjalankan perintah mysqltuner.

Pesan error yang paling umum saat kita menjalankan mysqltuner adalah sebagai berikut:

root@dev:~# mysqltuner
 >>  MySQLTuner 1.7.17 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[!!] Attempted to use login credentials from Debian maintenance account, but they failed.

Bagaimana memperbaikinya?

1. Nah, untuk memperbaiki kesalahan ini pertama-tama kita perlu mendownload mysqltuner.pl dengan perintah sederhana.

$ wget http://mysqltuner.pl/ -O mysqltuner.pl --no-check-certificate

2. Kemudian silahkan atur izin/permission pada file mysqltuner.pl yang baru saja kita download.

$ chmod +x mysqltuner.pl

3. Terakhir, untuk memperbaiki script mysqltuner yang error, Anda bisa menjalankan mysqltuner dengan perintah berikut.

$ perl mysqltuner.pl --user root --pass 'passwordroot'

NOTE: Ubah ‘passwordroot’ dengan kata sandi root MySQL/MariaDB Anda

Anda dapat menggunakan perintah di atas setiap kali Anda ingin menjalankan mysqltuner, daripada hanya menjalankan mysqltuner atau perintah ./mysqltuner.pl.

root@dev:~# perl mysqltuner.pl --user root --pass 'passwordroot'
 >>  MySQLTuner 2.5.3
         * Jean-Marie Renouard 
         * Major Hayden 
 >>  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
[OK] Logged in using credentials passed on the command line
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 27.0G (Tables: 282)
[--] Data in MEMORY tables: 0B (Tables: 1)
[OK] Total fragmented tables: 0
 
[OK] Currently running supported MySQL version 10.5.23-MariaDB-0+deb11u1
 
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file  doesn't exist
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3m 39s (1K q [8.388 qps], 30 conn, TX: 149M, RX: 3M)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory     : 259.6G
[--] Max MySQL memory    : 861.2M
[--] Other process memory: 0B
[--] Total buffers: 417.0M global + 2.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 422.9M (0.16% of installed RAM)
[OK] Maximum possible memory usage: 861.2M (0.32% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 1% (2/151)
[!!] Aborted connections: 10.00% (3/30)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 348 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 21% (50 on disk / 237 total)
[OK] Thread cache hit rate: 93% (2 created / 30 connections)
[OK] Table cache hit rate: 98% (1K hits / 2K requests)
[!!] table_definition_cache (400) is less than number of tables (476) 
[OK] Open file limit used: 0% (28/32K)
[OK] Table locks acquired immediately: 100% (53 immediate / 53 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :128.0M
[--]  +-- KB Used Size :23.3M
[--]  +-- KB used :18.2%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 128.0M / 27.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128.0M should be equal to 25%
[--] 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: 99.06% (19493727 hits / 19678771 total)
[!!] InnoDB Write Log efficiency: 84.59% (439 hits / 519 total)
[OK] InnoDB log waits: 0.00% (0 waits / 80 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[!!] Aria pagecache hit rate: 84.6% (408 cached / 63 reads)
 
-------- 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: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
    table_open_cache (> 2000)
    table_definition_cache (400) > 476 or -1 (autosizing if supported)
    innodb_log_buffer_size (> 16M)

Untuk Variable-variable setting yang bisa ditingkatkan, MySQLTuner akan memberikan Suggestion/adjustment untuk setting performance MySQL/MariaDB anda






Reporter: Nyoman Artawa Wiguna

Baca Juga:  Menjalankan MySQL Preference Pane Monitor Mac OS X dengan MariaDB

MySQLTuner adalah skrip yang ditulis dalam Perl yang membantu kita menganalisis dan mengkonfigurasi server database MySQL/MariaDB untuk mendapatkan peningkatan kinerja, stabilitas, dan lingkungan server yang seimbang.

Namun pada kondisi tertentu kita tidak bisa mengeksekusi perintah mysqltuner. Error tersebut paling sering terjadi pada sistem Debian/Ubuntu, dimana kita benar-benar tidak dapat menjalankan perintah mysqltuner.

Pesan error yang paling umum saat kita menjalankan mysqltuner adalah sebagai berikut:

root@dev:~# mysqltuner
 >>  MySQLTuner 1.7.17 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[!!] Attempted to use login credentials from Debian maintenance account, but they failed.

Bagaimana memperbaikinya?

1. Nah, untuk memperbaiki kesalahan ini pertama-tama kita perlu mendownload mysqltuner.pl dengan perintah sederhana.

$ wget http://mysqltuner.pl/ -O mysqltuner.pl --no-check-certificate

2. Kemudian silahkan atur izin/permission pada file mysqltuner.pl yang baru saja kita download.

$ chmod +x mysqltuner.pl

3. Terakhir, untuk memperbaiki script mysqltuner yang error, Anda bisa menjalankan mysqltuner dengan perintah berikut.

$ perl mysqltuner.pl --user root --pass 'passwordroot'

NOTE: Ubah ‘passwordroot’ dengan kata sandi root MySQL/MariaDB Anda

Anda dapat menggunakan perintah di atas setiap kali Anda ingin menjalankan mysqltuner, daripada hanya menjalankan mysqltuner atau perintah ./mysqltuner.pl.

root@dev:~# perl mysqltuner.pl --user root --pass 'passwordroot'
 >>  MySQLTuner 2.5.3
         * Jean-Marie Renouard 
         * Major Hayden 
 >>  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
[OK] Logged in using credentials passed on the command line
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 27.0G (Tables: 282)
[--] Data in MEMORY tables: 0B (Tables: 1)
[OK] Total fragmented tables: 0
 
[OK] Currently running supported MySQL version 10.5.23-MariaDB-0+deb11u1
 
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file  doesn't exist
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3m 39s (1K q [8.388 qps], 30 conn, TX: 149M, RX: 3M)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory     : 259.6G
[--] Max MySQL memory    : 861.2M
[--] Other process memory: 0B
[--] Total buffers: 417.0M global + 2.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 422.9M (0.16% of installed RAM)
[OK] Maximum possible memory usage: 861.2M (0.32% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 1% (2/151)
[!!] Aborted connections: 10.00% (3/30)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 348 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 21% (50 on disk / 237 total)
[OK] Thread cache hit rate: 93% (2 created / 30 connections)
[OK] Table cache hit rate: 98% (1K hits / 2K requests)
[!!] table_definition_cache (400) is less than number of tables (476) 
[OK] Open file limit used: 0% (28/32K)
[OK] Table locks acquired immediately: 100% (53 immediate / 53 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :128.0M
[--]  +-- KB Used Size :23.3M
[--]  +-- KB used :18.2%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 128.0M / 27.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128.0M should be equal to 25%
[--] 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: 99.06% (19493727 hits / 19678771 total)
[!!] InnoDB Write Log efficiency: 84.59% (439 hits / 519 total)
[OK] InnoDB log waits: 0.00% (0 waits / 80 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[!!] Aria pagecache hit rate: 84.6% (408 cached / 63 reads)
 
-------- 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: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
    table_open_cache (> 2000)
    table_definition_cache (400) > 476 or -1 (autosizing if supported)
    innodb_log_buffer_size (> 16M)

Untuk Variable-variable setting yang bisa ditingkatkan, MySQLTuner akan memberikan Suggestion/adjustment untuk setting performance MySQL/MariaDB anda






Reporter: Nyoman Artawa Wiguna

Baca Juga:  Baris perintah MySQL SHOW DATABASES, Daftar Semua List Database di MySQL
Untuk mendapatkan Berita & Review menarik Saksenengku Network
Google News

Artikel Terkait

Populer

Artikel Terbaru