MySQL database whole database backup script
Author: old farmer (Liu Qihua)
QQ: 46715422
Email: 46715422@qq.com
Wechat: 46715422

Test operating system environment: CentOS 6/7, Debian 8/9, FreeBSD 11/12
Test MySQL version: 5.7.23
User name and password: mysql
The database we need to back up this time: test
Script required for backup: back u database
Script storage path / home/mysql
Generate backup storage path / home/mysql

Create a backup script: back u database

#!/bin/sh

export MYSQLHOME=/usr/local/mysql/
export PATH=$MYSQLHOME/bin:$PATH
export MYSQLDATA=/usr/local/mysql/data/
export BACKUP_DIR=/home/mysql/
export USER=root
export PASSWORD=root
export DATABASE=test
export DATE=`date +"%Y-%m-%d"`
export OLDDATE=`date -v -7d +"%Y-%m-%d"`

######################################################
##
##  purpose:backup total database everyday logically
##  
##  file: back_database
##
##  author :EricLiu
##   
##  email: 46715422@qq.com
##
##  created:2018-08-25
##
##  restore_script:mysql -h127.0.0.1 -P3306 -u$USER -p$PASSWORD $DATABASE < /home/mysql/file_name
##  
#####################################################

test -f $BACKUP_DIR/$DATABASE.$OLDDATE.dump

if [ $? -eq 0 ] ; then

  rm -f $BACKUP_DIR/$DATABASE.$OLDDATE.dump

  echo " `date +"%Y-%m-%d %H:%M:%S"` the file $BACKUP_DIR/$DATABASE.$OLDDATE.dump has been moved!"

fi

test -f $BACKUP_DIR/$DATABASE.$DATE.dump

if [ $? -eq 0 ] ; then

  echo "Today's(`date +"%Y-%m-%d %H:%M:%S"`) BACKUP job has done!"

else

  mysqldump -h127.0.0.1 -P3306 -u$USER -p$PASSWORD $DATABASE > $BACKUP_DIR/$DATABASE.$DATE.dump


######check the dump file whether produced successfully

  test -f $BACKUP_DIR/$DATABASE.$DATE.dump

  if [ $? -eq 0 ]; then
   
     echo " `date +"%Y-%m-%d %H:%M:%S"` the database $DATABASE has been backuped to $BACKUP_DIR/$DATABASE.$DATE.dump successfully!"

  else
    
     echo " `date +"%Y-%m-%d %H:%M:%S"` the mysqldump failed!"

  fi

fi

# Back database script authorization
chmod 700 back_database

All we have to do is add the back database file to the scheduled task.

The author usually sells special products and second-hand computers in Xinjiang. If you want to communicate, you can scan the QR code below and add the author!