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
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.