我已经阅读了很多关于此内容的文章，但没有找到任何解决方案，这就是我创建这个主题的原因。
首先我要给你我的专用服务器规格
[email protected]
OS: CloudLinux 7.6 How2Kill.pl
##### Uptime: 3d 17h 0m
####### Packages: Unknown
##O#O## Shell: bash 4.2.46
####### CPU: Intel Core i7-4790K @ 8x 4.4GHz [43.0°C]
########### RAM: 6994MiB / 15846MiB
#############
###############
################
#################
#####################
#####################
#################
我想请 mysql 专家帮我解决这个问题
我有 Centos 7 系统，带有 cPanel 和 cloudlinux。我使用 invision 社区软件的数据库，它运行完美
我也将我的数据库用于 SourceBans ++，它是 CSGO 服务器的高级禁令系统应用程序。
因此 csgo 服务器连接到 mysql，如果有人在服务器上禁止某人，它只会转到 mysql，然后显示在网站上。
因此，有时连接到我的专用服务器 mysql 的远程服务器会松动连接，我不知道为什么，这就是我寻求帮助的原因
L 08/01/2019 - 00:49:37: [sbpp_main.smx] Verify Ban Query Failed: Lost connection to MySQL server during query
L 08/03/2019 - 00:57:02: [sbpp_main.smx] Verify Ban Query Failed: Can't connect to MySQL server on 'how2kill.pl' (4)
L 08/03/2019 - 00:58:02: [sbpp_main.smx] Verify Ban Query Failed: Can't connect to MySQL server on 'how2kill.pl' (4)
L 08/03/2019 - 01:18:14: [sbpp_main.smx] Verify Ban Query Failed: Can't connect to MySQL server on 'how2kill.pl' (4)
L 08/03/2019 - 01:45:36: [sbpp_main.smx] Query Failed: Lost connection to MySQL server during query
L 08/10/2019 - 06:53:13: [sbpp_main.smx] Query Failed: Lost connection to MySQL server during query
L 08/10/2019 - 08:09:33: [sbpp_main.smx] Query Failed: Lost connection to MySQL server during query
我甚至尝试将主机更改为专用服务器 IP，但仍然无法解决问题，一段时间后它就失去连接了。
我想说的是，本地主机上的一切都运行正常，唯一的问题是与其他服务器远程与数据库通信。
但是如果我在其他托管上使用其他数据库，例如 blazingfast.io 或 hekko.pl，他们的数据库运行良好，没有任何问题，根本没有丢失连接。
这就是我从 mysql 错误文件中获取的日志类型
2019-08-27 0:34:19 470238 [Warning] Aborted connection 470238 to db: 'csowicze_sourcebans' user: 'csowicze_sourcebans' host: 'node52.pukawka.pl' (Got timeout reading communication packets)
2019-08-27 0:34:19 470241 [Warning] Aborted connection 470241 to db: 'csowicze_sourcebans' user: 'csowicze_sourcebans' host: 'node52.pukawka.pl' (Got timeout reading communication packets)
2019-08-27 0:34:19 470239 [Warning] Aborted connection 470239 to db: 'csowicze_sourcebans' user: 'csowicze_sourcebans' host: 'node52.pukawka.pl' (Got timeout reading communication packets)
2019-08-27 0:34:20 470242 [Warning] Aborted connection 470242 to db: 'csowicze_sourcebans' user: 'csowicze_sourcebans' host: 'node52.pukawka.pl' (Got timeout reading communication packets)
反正
这是my.cnf文件
[mysqld]
long_query_time = 1
performance_schema = on
log_output = FILE
slow_query_log = ON
log_slow_admin_statements = ON
log_queries_not_using_indexes = OFF
log-error = /var/lib/mysql/h2k.how2kill.pl.err
wait_timeout = 1000
max_connections = 500
max_allowed_packet=256M
interactive_timeout = 30
tmp_disk_table_size = 1G
max_heap_table_size = 128M
tmp_table_size = 128M
join_buffer_size = 128M
innodb_buffer_pool_size = 3GB
innodb_log_file_size = 4G
innodb_buffer_pool_instances = 3
innodb_lru_scan_depth = 100
innodb_log_buffer_size = 1G
innodb_io_capacity = 1000
innodb_change_buffer_max_size = 10
innodb_file_per_table = 1
table_open_cache = 128
query_cache_type = 0
query_cache_limit = 128M
query_cache_min_res_unit = 2K
query_cache_size = 0
thread_cache_size = 100
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 1M
default-storage-engine = MyISAM
local-infile = 0
log_warnings = 2
max_write_lock_count = 16
这是 mysqltuner 日志
[root@h2k ~]# ./mysqltuner.pl
>> MySQLTuner 1.7.15 - Major Hayden
>> Bug reports, feature requests, and downloads at
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.3.17-MariaDB-log-cll-lve
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/h2k.how2kill.pl.err(7M)
[OK] Log file /var/lib/mysql/h2k.how2kill.pl.err exists
[OK] Log file /var/lib/mysql/h2k.how2kill.pl.err is readable.
[OK] Log file /var/lib/mysql/h2k.how2kill.pl.err is not empty
[OK] Log file /var/lib/mysql/h2k.how2kill.pl.err is smaller than 32 Mb
[!!] /var/lib/mysql/h2k.how2kill.pl.err contains 32113 warning(s).
[!!] /var/lib/mysql/h2k.how2kill.pl.err contains 26397 error(s).
[--] 17 start(s) detected in /var/lib/mysql/h2k.how2kill.pl.err
[--] 1) 2019-08-25 2:28:38 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2019-08-25 1:57:28 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2019-08-25 1:40:27 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2019-08-25 1:37:24 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2019-08-25 1:30:14 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2019-08-25 1:28:23 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2019-08-25 1:26:14 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2019-08-25 1:24:23 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2019-08-25 1:22:23 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2019-08-25 1:20:22 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7 shutdown(s) detected in /var/lib/mysql/h2k.how2kill.pl.err
[--] 1) 2019-08-25 1:34:22 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2019-08-25 1:24:46 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2019-08-25 1:12:40 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2019-08-20 22:11:17 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2019-08-13 4:36:42 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2019-08-12 0:23:38 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2019-08-12 0:23:36 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 2.3G (Tables: 610)
[--] Data in InnoDB tables: 1.4G (Tables: 586)
[!!] Total fragmented tables: 1
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'csowicze@%' does not specify hostname restrictions.
[!!] User 'csowicze_amxbans@%' does not specify hostname restrictions.
[!!] User 'csowicze_forum@%' does not specify hostname restrictions.
[!!] User 'csowicze_gagdd22@%' does not specify hostname restrictions.
[!!] User 'csowicze_gomodklany@%' does not specify hostname restrictions.
[!!] User 'csowicze_sourcebans@%' does not specify hostname restrictions.
[!!] User 'csowicze_usersmsgo@%' does not specify hostname restrictions.
[!!] User 'gocode@%' does not specify hostname restrictions.
[!!] User 'gocode_dev@%' does not specify hostname restrictions.
[!!] User 'gocode_doc@%' does not specify hostname restrictions.
[!!] User 'gocode_forum@%' does not specify hostname restrictions.
[!!] User 'how2kill@%' does not specify hostname restrictions.
[!!] User 'how2kill_amxx@%' does not specify hostname restrictions.
[!!] User 'how2kill_amxxbans@%' does not specify hostname restrictions.
[!!] User 'how2kill_arena@%' does not specify hostname restrictions.
[!!] User 'how2kill_csgoshop@%' does not specify hostname restrictions.
[!!] User 'how2kill_csowiczesb@%' does not specify hostname restrictions.
[!!] User 'how2kill_h2k@%' does not specify hostname restrictions.
[!!] User 'how2kill_ignuser@%' does not specify hostname restrictions.
[!!] User 'how2kill_ignwebuser@%' does not specify hostname restrictions.
[!!] User 'how2kill_sklepsms@%' does not specify hostname restrictions.
[!!] User 'how2kill_sourcebans@%' does not specify hostname restrictions.
[!!] User 'how2kill_testips@%' does not specify hostname restrictions.
[!!] User 'naxe@%' does not specify hostname restrictions.
[!!] User 'naxe_ips@%' does not specify hostname restrictions.
[!!] User 'shooted@%' does not specify hostname restrictions.
[--] There are 618 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 16h 34m 9s (66M q [209.267 qps], 868K conn, TX: 485G, RX: 33G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 69.1G
[--] Other process memory: 0B
[--] Total buffers: 4.4G global + 131.5M per thread (500 max threads)
[--] P_S Max memory usage: 501M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 35.1G (226.70% of installed RAM)
[!!] Maximum possible memory usage: 69.1G (446.66% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (48/66M)
[OK] Highest usage of available connections: 47% (235/500)
[OK] Aborted connections: 0.73% (6380/868903)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 9M sorts)
[!!] Joins performed without indexes: 23335
[!!] Temporary tables created on disk: 67% (735K on disk / 1M total)
[OK] Thread cache hit rate: 99% (772 created / 868K connections)
[!!] Table cache hit rate: 0% (128 open / 435K opened)
[OK] Open file limit used: 5% (147/2K)
[OK] Table locks acquired immediately: 99% (53M immediate / 53M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 501.7M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.3.17-MariaDB-log-cll-lve)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 25.0% (67M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/606.1M
[OK] Read Key buffer hit rate: 99.8% (3B cached / 5M reads)
[!!] Write Key buffer hit rate: 55.2% (3M cached / 2M writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 3.0G/1.4G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (266.666666666667 %): 4.0G * 2/3.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 3
[--] Number of InnoDB Buffer Pool Chunk : 24 for 3 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: 100.00% (1393348378 hits/ 1393405704 total)
[!!] InnoDB Write Log efficiency: 51.46% (1870504 hits/ 3634621 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1764117 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.8% (396M cached / 733K 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:
Control warning line(s) into /var/lib/mysql/h2k.how2kill.pl.err file
Control error line(s) into /var/lib/mysql/h2k.how2kill.pl.err file
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `csowicze_forum`.`core_output_cache`; -- can free 192.630504608154 MB
Total freed space after theses OPTIMIZE TABLE : 192.630504608154 Mb
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Read this before increasing for MariaDB
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here:
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (2505) variable
should be greater than table_open_cache (128)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 128.0M, or always use indexes with JOINs)
tmp_table_size (> 200M)
max_heap_table_size (> 32M)
table_open_cache (> 128)
innodb_log_file_size should be (=384M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
也许问题出在配置上，我不是 mysql 专家，所以我不知道什么对我的服务器来说应该是最好和最稳定的。
感谢您的任何建议和帮助。