SUGGESTION #1
I have a suggestion if you are willing to get your hands a little dirty and the tables are reasonably small enough.
You should convert all tables with auto_increment from INT to BIGINT. This should suppress autoincrement overflow issues for our lifetime. This will result in bigger tables (1 extra byte per row).
SUGGESTION #2
If you still prefer to monitor all tables with auto increment, here is a poor man's approach:
Start by running this query
SELECT table_schema,table_name,auto_increment FROM information_schema.tables
WHERE auto_increment IS NOT NULL;
This will give you every table that has auto_increment defined in the table and the next value that will used. If you want to see how many values are left, you can compute it.
For example, if all you auto_increments were defined as INT, the max signed value would be 2147483647. You could run this query
SELECT table_schema,table_name,auto_increment
(2147483647 - auto_increment) values_left
FROM information_schema.tables
WHERE auto_increment IS NOT NULL;
The values_left is what you can monitor. If you want to be warned of tables who have less than million values left, you can then run this:
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;
If you are able to make Nagios plugins or if you want to create a cronjob that calls for the tables you wish to monitor, you can use this last query as your base.
Give it a Try !!!
Actually 4 extra bytes, not 1 extra. Bigint is 8 bytes (64 bits), int is 4. But don't just blindly do this - if you have an app that records the ID values, if it can't handle 64 bit integers you will have a very bad time. Plus increasing storage space like this can slow down data read. – Ariel – 2014-06-29T08:30:26.410
Thanks; it'll need to be a bit more involved, to incorporate the data type in the max value calculation, but it's a very good approach. – moritz – 2014-03-14T19:31:15.827