Detailed explanation of mysql backup script mysqldump usage

In this article, we share the mysql backup script for your reference. The specific contents are as follows

#!/bin/bash
# Full standby mode, 1 It is executed on the slave machine, suitable for small and medium-sized mysql Database 
# Delete 15 Back up days ago 
# By: fafu_li
# Time: 2015.08.10

source /etc/profile    # Load system environment variables 
source ~/.bash_profile  # Load user environment variables 
set -o nounset       # Exit when referencing an uninitialized variable 
#set -o errexit      # Execute shell Exit when the command encounters an error 

user="root"
password="123456"
host="localhost"
port="3306"
# Database to be backed up, array 
db=("test")
# Locking mode during backup, 
#MyISAM Is a lock table --lock-all-tables , 
#InnoDB Lock line --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 previous backups 
find $backup_path -type f -mtime +$day -exec rm -rf {} \; > /dev/null 2>&1

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

# Back up 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)  Success  "
  else
    cd $backup_path
    rm -rf $backup_name
    echo "$date  Backup  $dbname  Failure  "
  fi
}

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

echo " Backup End , Results View  $backup_log"
du $backup_path/*$date* -sh | awk '{print " Documents :" $2 ", Size :" $1}'