In the grand tradition of my publishing little building-block shell scripts of interest, here goes another one. This is a simple cron job that I run daily on a number of hosts to generate storage usage growth. (This is in addition to Cacti and Nagios which poll some of this data already but for different reasons and with different granularity).
The FILES variable should be populated with a whitespace separated list of files, directories, and block devices to track.
The DB_ABCD variables should be populated with appropriate credentials to talk to a mysql server.
The actual script looks something like this:
#!/bin/bash
FILES='/var/lib/mysql/ibdata1 /var/lib/mysql/db/table.ibd /dev/sda1 /var/log/mysql'
LOCAL=`hostname -s`
DB_HOST='aaa'
DB_USER='bbb'
DB_PASS='ccc'
function insert {
FILE=$1
SIZE=$2
QUERY="replace into metrics.storage_usage values( now(), '$LOCAL', '$FILE', $SIZE )"
mysql --host=${DB_HOST} --user=${DB_USER} --password="${DB_PASS}" -e "${QUERY}"
}
for FILE in $FILES
do
if [ -d $FILE ]; then
BASE=$FILE
SIZE=`du -ks $FILE/ | awk '{print $1}'`
elif [ -b $FILE ]; then
TMP=`df -k -P $FILE | tail -n1 | awk '{print $3 " " $6}'`
SIZE=`echo $TMP | awk '{print $1}'`
BASE=`echo $TMP | awk '{print $2}'`
else
BASE=`basename $FILE`
SIZE=`du -k $FILE | awk '{print $1}'`
fi
echo "$BASE = $SIZE"
insert $BASE $SIZE
done
I am putting my data into a table called “storage_usage” in a database called “metrics”:
CREATE TABLE `storage_usage` (
`ts` date NOT NULL,
`host` varchar(25) NOT NULL,
`file` varchar(64) NOT NULL,
`size` int(10) unsigned NOT NULL COMMENT 'in kbytes',
PRIMARY KEY (`ts`,`host`,`file`)
)
Obviously, this could be tweaked in any different number of ways, based on your needs. One tweak you might want to consider if you’re running it in a daily cron is to remove the echo so you don’t get an email report of every run. Also, if you might want to record more than one snapshot per file per host per day – in the which case you probably need to change the type of the timestamp column to a datetime. Or there might be cases where you want to change the replace to an insert or… whatever 
Several months ago, I’d switched to using Percona’s xtrabackup & innobackupex for all of my mysql backup needs. I had successfully used these backups to restore and replicate databases across several systems. It is good stuff.
Last week, I needed to set up new replication of an 80gb database. This should have been routine by now, but when I attempted to prepare the backup this time, it whined and complained and failed. I was kind of frazzled by the time I gave up on the issue and declared it a fluke of one sort or another.
Last night, I tried again from Sunday’s full backup, and it happened again:
ammon@amy:/var/lib/2009-08-16_04-02-17$ sudo xtrabackup --prepare --target-dir=.
xtrabackup Ver 0.8.1rc Rev 78 for 5.0.83 unknown-linux-gnu (x86_64)
xtrabackup: cd to .
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=75546624, start_lsn=(86 1293090752)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:512M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 75546624
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Log scan progressed past the checkpoint lsn 86 1293090752
090818 2:54:34 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
090818 2:54:34 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: File name .//tmp/#sql6e1e_8cce1_0.ibd
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
I gave up after poking a few things.
This morning’s fresh look turned up this bug report.
ammon@amy:/var/lib/2009-08-16_04-02-17$ sudo mkdir tmp
ammon@amy:/var/lib/2009-08-16_04-02-17$ sudo xtrabackup --prepare --target-dir=.
xtrabackup Ver 0.8.1rc Rev 78 for 5.0.83 unknown-linux-gnu (x86_64)
xtrabackup: cd to .
xtrabackup: This target seems to be not prepared yet.
090818 12:27:41 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
xtrabackup: 'ib_logfile0' seems to be 'xtrabackup_logfile'. will retry.
xtrabackup: xtrabackup_logfile detected: size=84983808, start_lsn=(86 1293090752)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:512M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 84983808
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Log scan progressed past the checkpoint lsn 86 1293090752
090818 12:27:41 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Doing recovery: scanned up to log sequence number 86 1298333184 (6 %)
InnoDB: Doing recovery: scanned up to log sequence number 86 1303576064 (13 %)
InnoDB: Doing recovery: scanned up to log sequence number 86 1308818944 (20 %)
InnoDB: Doing recovery: scanned up to log sequence number 86 1314061824 (27 %)
InnoDB: Doing recovery: scanned up to log sequence number 86 1319304704 (34 %)
090818 12:27:44 InnoDB: Starting an apply batch of log records to the database...
... snip ...
That’s right. There’s a bug in innodb restoration that interprets location of /tmp (configurable in my.cnf) to be relative in stead of absolute.
So, if you have problems while trying to restore from an xtrabackup/ibbackup snapshot (or if you’re trying to recover innodb after a crash), just creating the offending tmp directory appears to work.