max_connections        = 500

key_buffer_size = 2G
innodb_buffer_pool_size = 4G

key_buffer_size=8G
innodb_buffer_pool_size=24G

service mysql restart

Параметры key_buffer_size и innodb_buffer_pool_size могут быть изменены в любое время на основании показателей производительности машины. Смена значений влияет на скорость обработки данных во время работы службы MySQL и не может привести к потере уже сохраненных данных или смене структуры хранения их в файловой системе.

CREATE USER 'rsyslog'@'HOST' identified by 'root';
GRANT ALL on Syslog.* TO 'rsyslog'@'HOST' identified by 'root';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'HOST' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

Для корректного анализа и сбора статистики сервер MySQL должен проработать некоторое время без смены параметров конфигурации и без перезагрузок, по рекомендации самого MySQLTuner не менее 24 часов.

/usr/lib/eltex-ems-db/tools/mysqltuner.pl --user root --pass root

 >>  MySQLTuner 1.6.14 - Major Hayden <major@mhtx.net>
 >>  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
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.7.32-0ubuntu0.16.04.1
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 31M (Tables: 79)
[--] Data in MyISAM tables: 1M (Tables: 6)
[OK] Total fragmented tables: 0
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'root@localhost' has user name as password.
[!!] User 'javauser@%' hasn't specific host restriction.
[!!] User 'radius@%' hasn't specific host restriction.
[!!] There is no basic password file list!
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 19h 3m 44s (62K q [0.404 qps], 2K conn, TX: 29M, RX: 6M)
[--] Reads / Writes: 86% / 14%
[--] Binary logging is disabled
[--] Physical Memory     : 7.8G
[--] Max MySQL memory    : 480.4M
[--] Other process memory: 2.3G
[--] Total buffers: 320.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 371.0M (4.65% of installed RAM)
[OK] Maximum possible memory usage: 480.4M (6.02% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/62K)
[OK] Highest usage of available connections: 31% (48/151)
[OK] Aborted connections: 0.04%  (1/2596)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Joins performed without indexes: 2591
[OK] Temporary tables created on disk: 17% (333 on disk / 1K total)
[OK] Table cache hit rate: 84% (659 open / 776 opened)
[OK] Open file limit used: 1% (285/16K)
[OK] Table locks acquired immediately: 100% (84K immediate / 84K locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.5% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.1M
[OK] Read Key buffer hit rate: 99.0% (7K cached / 71 reads)
[!!] Write Key buffer hit rate: 11.9% (538 cached / 474 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 256.0M/32.0M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 13.20% (2162 used/ 16382 total)
[OK] InnoDB Read buffer efficiency: 99.22% (268998 hits/ 271102 total)
[!!] InnoDB Write Log efficiency: 154.55% (17 hits/ 11 total)
[OK] InnoDB log waits: 0.00% (0 waits / 28 writes)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)

Особое внимание стоит уделить строкам, помеченным символами [!!]