最近,我们的 mysql 表中的 auto_increment 列发生溢出。现在我想使用 nagios 监控我们的数据库,这样只要 auto_increment 列接近其最大值,我们就会收到警报。
是否已经有了检查?如果有,我在哪里可以找到它?如果没有,有什么好的提示可以教你如何写检查吗?
(我只发现了一小部分有用的 Google 搜索结果,而且这些搜索结果使用了 Percona 扩展;遗憾的是我们这里仅限于“纯”mysql)。
答案1
建议 1
如果你愿意弄脏你的手并且桌子足够小的话,我有一个建议。
您应该将所有带有 auto_increment 的表从 INT 转换为 BIGINT。这应该可以抑制我们一生中自动增量溢出问题。这将导致更大的表(每行多 1 个字节)。
建议 #2
如果您仍然喜欢监控所有具有自动增量的表,这里有一个穷人的方法:
首先运行此查询
SELECT table_schema,table_name,auto_increment FROM information_schema.tables
WHERE auto_increment IS NOT NULL;
这将为您提供表中定义了 auto_increment 的每个表以及将使用的下一个值。如果您想查看剩余的值数,可以计算出来。
例如,如果所有 auto_increments 都定义为INT
,则最大有符号值将为2147483647
。您可以运行此查询
SELECT table_schema,table_name,auto_increment
(2147483647 - auto_increment) values_left
FROM information_schema.tables
WHERE auto_increment IS NOT NULL;
这values_left
就是您可以监控的内容。如果您希望对剩余值少于百万的表发出警告,则可以运行以下命令:
SELECT * FROM
(SELECT table_schema,table_name,auto_increment
(2147483647 - auto_increment) values_left
FROM information_schema.tables
WHERE auto_increment IS NOT NULL) A
WHERE values_left < 1000000;
如果您能够制作 Nagios 插件,或者想要创建一个调用您想要监控的表的 cronjob,则可以使用这最后一个查询作为基础。
试一试 !!!
答案2
你可以看看https://vettabase.com/blog/monitor-auto-increment-usage
给定的查询报告 auto_increment 列的状态按填充排序:
SELECT
t.TABLE_SCHEMA AS `schema`,
t.TABLE_NAME AS `table`,
t.AUTO_INCREMENT AS `auto_increment`,
c.DATA_TYPE AS `pk_type`,
t.AUTO_INCREMENT / (
CASE DATA_TYPE
WHEN 'tinyint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 255, 127)
WHEN 'smallint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 65535, 32767)
WHEN 'mediumint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 16777215, 8388607)
WHEN 'int' THEN IF(COLUMN_TYPE LIKE '%unsigned', 4294967295, 2147483647)
WHEN 'bigint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 18446744073709551615, 9223372036854775807)
END / 100) AS `max_value`
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.AUTO_INCREMENT IS NOT NULL
AND c.COLUMN_KEY = 'PRI'
AND c.DATA_TYPE LIKE '%int'
ORDER BY `max_value` DESC
干杯
答案3
受到 RonaldMySQLDBA 的回答的启发,我现在写了https://github.com/moritz/mysql-check-autoincrement