Detailed explanation of how to use the mysql backup script mysqldump

#!/bin/bash
#Full backup mode, usually executed on the slave machine, suitable for small and medium-sized MySQL databases #Delete backups older than 15 days #Author: fafu_li
#Date: 2015.08.10

source /etc/profile #Load system environment variablessource ~/.bash_profile #Load user environment variablesset -o nounset #Exit when referencing uninitialized variables#set -o errexit #Exit when an error occurs when executing a shell commanduser="root"
password="123456"
host="localhost"
port="3306"
#Database to be backed up, array db=("test")
#Lock mode during backup,
#MyISAM is locked table --lock-all-tables,
#InnoDB locks rows --single-transaction
lock="--single-transaction"
mysql_path="/usr/local/mysql"
backup_path="${mysql_path}/backup"
date=$(date +%Y-%m-%d_%H-%M-%S)
day=15
backup_log="${mysql_path}/backup.log"

#Create a backup directory if [ ! -e $backup_path ]; then
  mkdir -p $backup_path
fi

#Delete the previous backup find $backup_path -type f -mtime +$day -exec rm -rf {} \; > /dev/null 2>&1

echo "Start backing up database: ${db[*]}"

#Backup and compress backup_sql(){
  dbname=$1
  backup_name="${dbname}_${date}.sql"
  #-R backup stored procedures, functions, triggers mysqldump -h $host -P $port -u $user -p$password $lock --default-character-set=utf8 --flush-logs -R $dbname > $backup_path/$backup_name  
  if [[ $? == 0 ]];then
    cd $backup_path
    tar zcpvf $backup_name.tar.gz $backup_name
    size=$(du $backup_name.tar.gz -sh | awk '{print $1}')
    rm -rf $backup_name
    echo "$date backup $dbname($size) successful"
  else
    cd $backup_path
    rm -rf $backup_name
    echo "$date backup $dbname failed"
  fi
}

#Loop backup length=${#db[@]}
for (( i = 0; i < $length; i++ )); do
    backup_sql ${db[$i]} >> $backup_log 2>&1
done

echo "Backup completed, see the result in $backup_log"
du $backup_path/*$date* -sh | awk '{print "file:" $2 ", size:" $1}'