Periodically back up MySQL in the production environment

Every database has a command to export the data and structure in the database, that is, backup. Restoring the backup data will delete and rebuild the tables in the original data, and then insert the data in the backup. This is restore. Note that if there is more data before restoration than backup, the data after restoration will be lost.

Mysqldump -h -u [username] -p [database name] > mysqldump -h -u [username] -p [database name] > mysqldump -h -u [username] -p [database name] > mysqldump

To complete a full-featured backup solution, shell scripts are required. We want this script to be backed up to the specified path and compressed, save up to 30, more than 30 delete the earliest, and log the operation. Don’t say anything. It’s all in the script. Go!

# username
username=root
# your password
password=nicai
The database to be backed up
database_name=l_love_you

# Maximum number of backup files to be saved
count=30
Backup save path
backup_path=/app/mysql_backup
# date
date_time=`date +%Y-%m-%d-%H-%M`

Create a folder if it does not exist
if [ ! -d $backup_path ]; 
then     
    mkdir -p $backup_path; 
fi
# start backup
mysqldump -u $username -p$password $database_name > $backup_path/$database_name-$date_time.sql
# start compression
cd $backup_path
tar -zcvf $database_name-$date_time.tar.gz $database_name-$date_time.sql
Delete the source file
rm -rf $backup_path/$database_name-$date_time.sql
Update the backup log
echo "create $backup_path/$database_name-$date_time.tar.gz" >> $backup_path/dump.log

Find the backup that needs to be deleted
delfile=`ls -l -crt  $backup_path/*.tar.gz | awk '{print $9 }' | head -1`

Check whether the current number of backups exceeds the threshold
number=`ls -l -crt  $backup_path/*.tar.gz | awk '{print $9 }' | wc -l`

if [ $number -gt $count ]
then
  # delete the earliest backup and keep only the count backup
  rm $delfile
  Update delete file log
  echo "delete $delfile" >> $backup_path/dump.log
fi
Copy the code

Chmod +x dump_mysql.sh: chmod +x dump_mysql.sh: chmod +x dump_mysql.sh: chmod +x dump_mysql.sh: chmod +x dump_mysql.sh: chmod +x dump_mysql.sh: chmod +x dump_mysql.sh: chmod +x dump_mysql.sh: chmod +x dump_mysql.sh

Parameter Meaning of the chmod command -- + indicates that some permissions are added. X indicates that the permissions are executedCopy the code

• Crontab -l Displays the scheduled task list
• Crontab -e Edit (add/delete) scheduled tasks

Run the crontab -e command to open an editable text, enter 00 01 * * * /app/dump_mysql.sh to save and exit.