#!/bin/bash########### #功能说明部分 ####################################### #脚本功能是按照每天对mysql数据库中所有表进行备份, the backup file is: Database name-table name. sql# instance: Backup/ .- ,- -/uic-test.sql# test Data Restore instance: MySQL-uroot-pxxx uic< backup/ .- ,- -/uic-test.sql############## #脚本变量定义 ################################# #MYSQL_HOME=/home/work/Mysqlmysql_dump=/home/work/mysql/bin/Mysqldumpbackup_home=/home/work/mysql/Backupsave_time_threshold=7#备份天数BACKUP_DATA_PATH= $BACKUP _home/$ (Date+%F) #RM_DATA_PATH= $BACKUP _home/$ (Date-D"-30 Days"+%F) PASS=$(greppassword/home/work/mysql/etc/root.cnf |sedS/[[:space:]]//g|awk-f ' = ' {' Print $ '})User='Root'HOST='127.0.0.1'############## #函数部分 #######################################functionBackupmysql () {Local DB=$1Local Table=$2Local Dbdir=$3Local Backfile="${dbdir}/${db}-${table}.sql"$MYSQL _dump--skip-lock-tables-h${host}-u${user}-p${pass} $db $table |gzip>${backfile}.gz}######### #脚本逻辑部分 #########################################ls$BACKUP _home &>/dev/NULL||mkdir$BACKUP _homeChownwork.work $BACKUP _homels$BACKUP _data_path &>/dev/NULL||mkdir$BACKUP _data_pathChownwork.work $BACKUP _data_pathFind$BACKUP _home/-maxdepth1-type D-mtime +7-execRM-RF {} \;#if[-D $RM _data_path];then# RM-RF $RM _data_path #fiCD $BACKUP _data_path||ExitEcho "begin backup MySQL"cmd="Show Databases"CNT=$ (Mysql-h${host}-u${user}-p${pass}-s-e"${cmd}") forDbinch$cnt Docmd="Use $db; show Tables"tablelist=$ (Mysql-h${host}-u${user}-p${pass}-s-e"${cmd}") forTableinch$tablelist Do if["$table"=="Project_files"] ; Thencmd1="Use table; SELECT project_id,version,chunk,size from $table"MySQL-h${host}-u${user}-p${pass}-s-e"${CMD1}"|gzip> ${backup_data_path}/${db}-${table}_txt.gzElseBackupmysql"$db" "$table" "$BACKUP _data_path" fi Done Done
MySQL backup script