3

I have about 120 databases in my SQL. I want to migrate so I want to know which databases are not used in last 30 days.

1 Answer 1

3

Unfortunately, the INFORMATION_SCHEMA and SHOW STATUS are of no value for InnoDB tables.

I am also sure you don't have time to gore through and interpret servers logs.

Here is the fastest way: Check the timestamp of the most recently accessed file inside every database.

All you need is the most recent file written in each of the 120 databases. This is true for both MyISAM and InnoDB.

I wrote a crazy post back on Dec 21, 2011 ( Fastest way to check if InnoDB table has changed) to check the timestamp of any individual InnoDB table.

You should write a bash script to cd into every folder under /var/lib/mysql, find the most recent file, and check the timestamp. Just subtract the most recent timestamp from UNIX_TIMESTAMP(NOW()).

  • If the difference is less than 2592000 (That's the number of seconds in 30 days), that database has been updated in the last 30 days.
  • If the difference is greater than 2592000, that database is over 30 days old.

Here is that script

#!/bin/bash

DATADIR=/var/lib/mysql

AGE_DAYS=30
(( AGE_SECS = AGE_DAYS * 86400 ))

RIGHTNOW=`date +%s`

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

SQL="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DB_CSV=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
DBLIST=`echo "${DB_CSV}" | sed 's/,/ /g'`

OLDDBLIST=""
SPC=""
for DB in `echo "${DBLIST}"`
do
    DBFOLDER=${DATADIR}/${DB}
    cd ${DBFOLDER}
    FILECOUNT=`ls -lt *.[fiM][rbY][mdDI] | wc -l`
    if [ ${FILECOUNT} -gt 0 ]
    then
        FILENAME=`ls -lt *.[fiM][rbY][mdDI] | head -1 | awk '{print $9}'`
        FILETIME=`ls -l --time-style="+%s" ${FILENAME} | awk '{print $6}'`
        (( TIMEDIFF = RIGHTNOW - FILETIME ))
        if [ ${AGE_SECS} -gt ${TIMEDIFF} ]
        then
            OLDDBLIST="${OLDDBLIST}${SPC}${DB}"
            SPC=" "
        fi
    fi
done

echo ${OLDDBLIST}

CAVEAT : @Jynus wrote a post about using the audit_plugin to determine that info.

If you are using Percona Server, check this forum.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.