MySQL Check Auto Increment Script - without 'S

问题:

I've been working on this script for a couple of days now (i'm recently started writing in bash)
The script checks when a column is near to reach its auto_incement limit. I could of just get the auto_increment value from information schema. But, because we have hundreds of tables and about 4TB of data that would of taken awhile. So I decided to get the auto_increment value from 'SHOW CREATE TABLE' which hopefully will be faster(still have to run this in staging). I'm sure there are multiple ways to solve this problem and like I said I'm not an bash script expert but I would like your opinion and If there is anything I could be doing better. Hopefully this also helps others.
Thank you! -Gio

#!/bin/bash
#
# This is bash script checks when auto_increment column is reaching its limit
# To run Script $ ./auto_increment_check.sh [username] [password] [host]

MYSQL_USER="$1"
MYSQL_PASSWD="$2"
MYSQL_HOST="$3"

MYSQL=$(which mysql)
if [ $? != 0 ]
then
  echo -e "\nMYSQL CLIENT NOT PRESENT!\n"
  exit 1
fi

MYSQLCONNECT="$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWD -h $MYSQL_HOST"

QUERY="
SELECT table_schema,
       table_name,
       data_type,
       ( CASE data_type
           WHEN 'tinyint' THEN 255
           WHEN 'smallint' THEN 65535
           WHEN 'mediumint' THEN 16777215
           WHEN 'int' THEN 4294967295
           WHEN 'bigint' THEN 18446744073709551615
         end >> IF(Locate('unsigned', column_type) > 0, 0, 1) ) AS MAX_VALUE
FROM   information_schema.columns
       INNER JOIN information_schema.tables USING (table_schema, table_name)
WHERE  table_schema NOT IN ( 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA'
                           )
       AND extra = 'auto_increment'"


$MYSQLCONNECT --batch -N -e "$QUERY" | while read DATABASE TABLE DATA_TYPE MAX_VALUE; 
do
  NEXT_AUTO_INCREMENT=`mysql -uroot -pgio --batch -N -e "SHOW CREATE TABLE $DATABASE.$TABLE" |  awk     -F'AUTO_INCREMENT=' 'NF==1{print "0";next}{sub(/ .*/,"",$2);print $2}'`
  AUTO_INCREMENT_RATIO=$(awk 'BEGIN {printf "%2.2f\n", '$NEXT_AUTO_INCREMENT' / '$MAX_VALUE'}')
  [[ $(awk 'BEGIN{print ('$AUTO_INCREMENT_RATIO'>=0.9)}') -eq 1 ]] && echo "Auto Increment limit almost reached! $DATABASE.$TABLE - NEXT_AUTO_INCREMENT= $NEXT_AUTO_INCREMENT, MAX= $MAX_VALUE, RATIO= $AUTO_INCREMENT_RATIO"
done

回答1:

If information_schema is slow for you, I recommend setting innodb_stats_on_metadata=0.

As for monitoring auto increment capacity, you should do this with common_schema.

Once common_schema is installed you can run a query like this:

select * 
from common_schema.auto_increment_columns 
order by auto_increment_ratio desc 
limit 10;

For further information please read the blog post I wrote on this subject last year.