Shell ：导出 Mysql 数据

#!/bin/bash
# Author: wjxie
# Created at: 2014-12-19
# Feature: dump and clear table ad.ad_hit

set -e
USERNAME="******"
PASSWORD="******"
DATABASE="ad"
MYSQL=`which mysql`
TODAY=`date +%Y-%m-%d`
YESTERDAY=`date -d yesterday +%Y-%m-%d`
DUMP_FILE="/root/workspaces/sql.dump/ad_hit.sql."$TODAY
LOG_FILE="/root/workspaces/sql.dump/shell.log"

echo "==================== "$TODAY" ====================" >> $LOG_FILE
count=`$MYSQL $DATABASE -u$USERNAME -p$PASSWORD <<EOF | tail -n +2
  select count(id) from ad_hit where created_at < '$YESTERDAY';
  exit
EOF`
echo "ad_hit created before yesterday has "$count" records." >> $LOG_FILE

if [[ $count -gt 10000000 ]];then
  # dump ad_hit before yesterday
  # -t : only dump data, not to dump schema
  # --single-transaction : not to prevent read and write, but keeps repeatable r                                                                                        ead. this will only works for innodb
  # --quick : not to cache query result in memory, dump directly
  start_time=`date +%s`
  mysqldump -u$USERNAME -p$PASSWORD -t --single-transaction --quick $DATABASE ad                                                                                        _hit --where="created_at < '$YESTERDAY'" > $DUMP_FILE
  end_time=`date +%s`
  cost_time=$[ end_time - start_time ]
  echo "dump ad_hit finished, cost "$cost_time" s." >> $LOG_FILE

  # clear ad_hit before yesterday, optimize table
  start_time=`date +%s`
  $MYSQL $DATABASE -u$USERNAME -p$PASSWORD <<EOF
    delete from ad_hit where created_at < '$YESTERDAY';
    optimize table ad_hit;
    exit
EOF
  end_time=`date +%s`
  cost_time=$[ end_time - start_time ]
  echo "clear and optimize ad_hit finished, cost "$cost_time" s." >> $LOG_FILE
fi