使用shell脚本操作和备份Mysql数据库表

●在sh中执行MySQL脚本

mysql -uroot -pvendingihr < /mnt/soft/app_bak/create_orderbackDB.sql

●在sh中执行MySQL命令

mysql -uroot -pvendingihr -e 'use vendingdb;select count(1) from tb_orders;'

●sh中执行MySQL命令，并将结果返回给sh变量

OrderCount=mysql -uroot -pvendingihr -e 'use vendingdb;select count(1) from tb_orders;'|sed '1d'

///sed是非交互式的编辑器。它不会修改文件除非使用shell重定向来保存结果。
///因为sed把每一行都存在临时缓冲区中对这个副本进行编辑所以不会修改原文件。 sed'1d'是删除结果中第一行。


●以下是一个备份Mysql数据库指定表，在订单表数据达到100万行时进行备份并重新初始化订单表的一个实例：

#ql -uroot -pvendingihr < /mnt/soft/app_bak/initorder.sql
#!/bin/bash
BACKUP_DATE=`date +%Y%m%d`
BACKUP_TIME=`date +%F\ %T`
WEEK=`date +%w`
IP=`/sbin/ifconfig eth0|/bin/grep "inet addr"|/bin/cut -d : -f 2|/bin/awk '{print $1}'`
BACKUP_PATH="/mnt/mysqlbak"
LOG_FILE="${BACKUP_PATH}/mysql_${BACKUP_DATE}.log"
MYSQL_USER='root'
MYSQL_PASSWORD='vendingihr'
MYSQL_PATH="/usr/bin"
MYSQL="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD}"
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASSWORD}"
USEDBNAME="vendingdb"
BACK2DBNAME="vending_orderbakdb"
TB_ORDER="tb_orders"
TB_ORDERDT="tb_orderdetail"
BACKUP_FULL_PATH=${BACKUP_PATH}/${BACKUP_DATE}/${BACK2DBNAME}
echo backupDate:${BACKUP_DATE}
echo backupTime:${BACKUP_TIME}
echo backupPath:${BACKUP_PATH}
echo ${WEEK}
echo IP:${IP}
echo mysql:${MYSQL}

# if BACKUP_PATH doesn't exists,mkdir
[ ! -d "${BACKUP_PATH}" ] && mkdir -p "${BACKUP_PATH}"
#========================Backup start==========================
echo "--------------backup start ${BACKUP_TIME}-----------------">>${BACKUP_TIME}
# stop slave SQL_THREAD and flush logs and read from master status to know binlog file name and positon
${MYSQL} -e "stop slave SQL_THREAD;flush logs;"
${MYSQL} -e "show master status \G">>${LOG_FILE}
echo >>${LOG_FILE}

#start backup every table
DBNAME="`${MYSQL} -e 'show databases;'|sed '1d'`"
echo "DATABASE LIST:" >>${LOG_FILE}
echo "------Database List:------"
echo ${DBNAME}>>${LOG_FILE}
echo >>${LOG_FILE}
for dbname in ${DBNAME}	
do
	echo ${dbname}
done
echo "------use "${USEDBNAME}"------"
echo "Create backup path...->"${BACKUP_FULL_PATH}
mkdir -p "${BACKUP_FULL_PATH}"
#cd /backup/${dbname}_${WEEK}

echo -e "/////////${USEDBNAME} backup start ${BACKUP_TIME}">>"${LOG_FILE}"
echo "//////////${USEDBNAME} backup start ........."
TABNAME=`${MYSQL} ${USEDBNAME} -e 'show tables;'|sed '1d'`

echo "TABLE LIST:" >>${LOG_FILE}
echo "Table List:"
echo ${TABNAME}>>${LOG_FILE}
echo >>${LOG_FILE}
for tabname in ${TABNAME}
do
echo ${tabname}
done
echo -e "${TB_ORDER} backup start ${BACKUP_TIME}">>"${LOG_FILE}"
echo "${TB_ORDER} backup start ${BACKUP_TIME}"

OrderCount=${MYSQL} ${USEDBNAME} -e 'select count(1) from tb_orders;'|sed '1d'
if [OrderCount>1000000 ]; then
  ${MYSQL_DUMP} ${USEDBNAME} ${TB_ORDER}>${BACKUP_PATH}/${USEDBNAME}_${WEEK}/${TB_ORDER}.sql
  ${MYSQL_DUMP} ${USEDBNAME} ${TB_ORDERDT}>${BACKUP_PATH}/${USEDBNAME}_${WEEK}/${TB_ORDERDT}.sql
  ${MYSQL} ${USEDBNAME} -e ' truncate table tb_orders;'
  ${MYSQL} ${USEDBNAME} -e ' truncate table tb_orders;'
fi

echo "/////${TB_ORDER} backup complete..."