我有一个维护 cron 作业脚本，每 5 分钟运行一次，它会截断一个表，用维护数据重新填充它，然后在最后截断它。我还每天运行一个 mysqldump 脚本来备份我的所有表。我最近注意到备份中缺少一些表。经过进一步调查，我发现 mysqldump 抛出了这个错误：
Error 1412 "Table Definition Has Changed"
进一步研究之后，显然是因为我的 cron 作业脚本在 mysqldump 备份中间使用 TRUNCATE 清空表造成的。
我正在使用 --single-transaction 选项，但没有什么区别。
是否有某种方法可以让 mysqldump 继续运行，即使表在 mysqldump 运行时被截断（并且仍然备份该表）？这需要在没有 --ignore-table 选项的情况下完成。我知道如何做到这一点，但宁愿将所有维护表都包含在备份中，以防数据库必须从备份中完全恢复。
我能想到的唯一其他事情是使用“DELETE FROM table”而不是“TRUNCATE table”，但我必须检查所有网站上的所有脚本才能改变这一点。
我最近将我的数据库升级到 MySQL 8.0，我正在使用 phpmyadmin 来获取状态信息。我还将我的虚拟服务器升级到 4GB RAM 和 2 个 vCPU，旨在成为我网站的 MySQL 专用服务器。MySQL 在服务器上自行运行，我在单独的服务器上拥有 php 和其他所有内容。
问题：我的服务器上的内存使用量似乎随着时间的推移而逐渐增加。它通常运行良好，但由于 OOM 杀手，几天后会崩溃。这些可能很难看，有时 MySQL 不会重新启动几个小时（锁定/冻结/等），即使我的 cronjob 脚本每 5 分钟检查一次 MySQL 是否正在运行并重新启动它没有运行。我的网站有时会在我醒来之前整个晚上/早上都关闭，并且我将被迫重新启动操作系统几次，然后才能重新开始工作。
网站上的速度变慢也存在问题，这些问题似乎在没有警告的情况下发生，没有任何明显的原因，慢查询日志中没有任何内容，网站流量缓慢，可用内存充足。这些发生大约一个小时，问题自行消失。发生这种情况时，由于 MySQL 的问题，加载网页可能需要 20-30 秒。
我调查了不使用索引运行的慢查询日志和查询。经过调查，事实证明其中许多涉及国家/州的 200 行小表格，我们在其中选择大部分表格并将其显示在网站上，如设计的那样（这就是为什么其中许多出现在“查询不使用索引") 列表。否则，除了从小表中选择大量内容之外，日志的该部分没有其他内容。
以下是来自 phpmyadmin 的一些数据（5 月 21 日更新）：
Network traffic since startup: 165.1 GiB
This MySQL server has been running for 3 days, 15 hours, 58 minutes and 2 seconds. It started up on May 18, 2021 at 05:43 AM.
Traffic # ø per hour
Received 5.3 GiB 61.4 MiB
Sent 159.8 GiB 1.8 GiB
Total 165.1 GiB 1.9 GiB
Connections # ø per hour %
Max. concurrent connections 32 --- ---
Failed attempts 25 0.28 <0.01%
Aborted 0 0 0%
Total 2,494 k 28.35 k 100.00%
警报状态变量（由 phpmyadmin 标记为红色） - 5 月 21 日更新：
Aborted connectsDocumentation 25 The number of failed attempts to connect to the MySQL server.
Binlog cache disk useDocumentation 19.5 k The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.
Handler read rndDocumentation 70.2 M The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler read rnd nextDocumentation 5.9 G The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Innodb buffer pool pages dirtyDocumentation 20 The number of pages currently dirty.
Innodb buffer pool readsDocumentation 6.8 M The number of logical reads
that InnoDB could not satisfy from buffer pool and had to do a single-page read.
Innodb buffer pool wait freeDocumentation 3 Normally, writes to the InnoDB buffer pool happen in the background. However, if it's necessary to read or create a page and no clean pages are available, it's necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size was set properly, this value should be small.
Innodb row lock time avgDocumentation 911 The average time to acquire a row lock, in milliseconds.
Innodb row lock time maxDocumentation 31.9 k The maximum time to acquire a row lock, in milliseconds.
Innodb row lock waitsDocumentation 228 The number of times a row lock had to be waited for.
Opened tablesDocumentation 7.9 k The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
Select full joinDocumentation 203 k The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
Slow queriesDocumentation 43 The number of queries that have taken more than long_query_time seconds.Documentation
Sort merge passesDocumentation 4.3 k The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
Table locks waitedDocumentation 1.5 k The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
查询统计信息（5 月 21 日更新）：
Questions since startup: 35,646,301 Documentation
ø per hour: 405,017
ø per minute: 6,750
ø per second: 113
Statements # ø per hour %
select 33,914 k 385.3 k 95.14
update 568 k 6,448.2 1.59
insert 349 k 3,968.7 0.98
change db 337 k 3,826.6 0.94
set option 303 k 3,447.3 0.85
replace 136 k 1,545.2 0.38
delete 14,064 159.8 0.04
update multi 4,827 54.8 0.01
show fields 2,940 33.4 0.01
truncate 2,163 24.6 0.01
show status 2,092 23.8 0.01
show replica status 2,092 23.8 0.01
show slave status 2,092 23.8 0.01
show master status 2,091 23.8 0.01
show processlist 2,047 23.3 0.01
show create table 1,059 12 <0.01
show table status 979 11.1 <0.01
rollback to savepoint 957 10.9 <0.01
show triggers 957 10.9 <0.01
show keys 335 3.8 <0.01
show variables 272 3.1 <0.01
show tables 119 1.4 <0.01
create table 64 0.7 <0.01
show warnings 61 0.7 <0.01
insert select 37 0.4 <0.01
drop table 30 0.3 <0.01
delete multi 26 0.3 <0.01
unlock tables 15 0.2 <0.01
begin 15 0.2 <0.01
show create db 15 0.2 <0.01
savepoint 15 0.2 <0.01
show create trigger 12 0.1 <0.01
release savepoint 12 0.1 <0.01
show grants 8 0.1 <0.01
show binlogs 8 0.1 <0.01
show databases 5 0.1 <0.01
kill 4 <0.1 <0.01
show storage engines 2 <0.1 <0.01
show slave hosts 1 <0.1 <0.01
show replicas 1 <0.1 <0.01
flush 1 <0.1 <0.01
create db 1 <0.1 <0.01
MySQL 配置文件 my.cnf。几年前，一位专业的 DBA 告诉我设置这些变量来调整 1GB 服务器的 mysql - 以处理 Out Of Memory 崩溃。我最近更改的唯一变量是 innodb_buffer_pool_size 从 512MB 到 2G（5 月 21 日更新，添加了“skip-name-resolve”以修复我在 mysqltuner 上发现的错误）：
[mysqld]
skip-name-resolve
default_authentication_plugin = mysql_native_password
character_set_server=latin1
collation_server=latin1_swedish_ci
port = 3306
sql_mode = "NO_ENGINE_SUBSTITUTION"
innodb_buffer_pool_size = 2000M
innodb_strict_mode = OFF
join_buffer_size = 1M
key_buffer_size = 64M
max_connect_errors = 10000
myisam_recover_options = "BACKUP,FORCE"
performance_schema = 0
read_buffer_size = 1M
slow_query_log = ON
sort_buffer_size = 1M
sync_binlog = 0
thread_stack = 262144
wait_timeout = 14400
table_open_cache = 10000
table_definition_cache = 2500
open_files_limit = 30000
max_connections = 100
read_rnd_buffer_size = 128K
innodb_change_buffer_max_size = 15
innodb_log_buffer_size = 12M
innodb_log_file_size = 120M
innodb_buffer_pool_instances = 8
innodb_lru_scan_depth = 128
innodb_page_cleaners = 64
thread_cache_size = 50
max_heap_table_size=24M
tmp_table_size=24M
thread_cache_size=100
innodb_io_capacity=800
read_buffer_size=128K
read_rnd_buffer_size=64K
eq_range_index_dive_limit=32
symbolic-links=0
key_cache_age_threshold=64800
key_cache_division_limit=50
key_cache_block_size=32K
innodb_buffer_pool_dump_pct=90
innodb_print_all_deadlocks=ON
innodb_read_ahead_threshold=8
innodb_read_io_threads=64
innodb_write_io_threads=64
max_allowed_packet=32M
max_seeks_for_key=32
max_write_lock_count=16
myisam_repair_threads=4
open_files_limit=30000
query_alloc_block_size=32K
query_prealloc_size=32K
sort_buffer_size=2M
updatable_views_with_limit=NO
general_log_file=/var/log/mysql/general.log
slow_query_log_file=/var/log/mysql/slow-query.log
更新
我决定在我的所有数据库中将所有 MyISAM 表分阶段到 Innodb。不再有 MyISAM 表。希望这可以简化调整工作。
更新 2：Pastebins
3天前，我逐步淘汰了所有MyISAM表并制作了InnoDB表，在my.cnf中添加了“skip-name-resolve”并重新启动了服务器。我已经从 phpmyadmin 更新了上面的其余信息，截至 5 月 21 日，今天添加了这些 pastebins 以及在服务器运行 2-3 天后的新数据。
显示全球状态：https ://pastebin.com/r3t84pvZ
显示全局变量：https ://pastebin.com/kQpevtdx
显示完整的处理程序：https ://pastebin.com/fR6b7Tdg
状态：https ://pastebin.com/vyWyhZSf
MySQL 调谐器：https ://pastebin.com/ETLCa48V
顶部：https ://pastebin.com/cU8RvgpT
ulimit -a：https ://pastebin.com/BhNVgEXH
iostat -xm 5 3：https ://pastebin.com/MxymEXyq
/proc/meminfo：https://pastebin.com/PKKeumyt _