MySQL scheduled database backup operation example

This article describes the example of MySQL scheduled database backup operation. Share with you for your reference, the details are as follows:

1. View mysqldump

root@laowang:/# which mysqldump
/usr/bin/mysqldump

2. Write a script

Edit the my.cnf file, specify the account and password, and then reference it in the script

root@laowang:/# vim /etc/my.cnf

[mysqldump]
user=root
password=root

Script Files

root@laowang:/var/backups# vim mysql_backup.sh
#!/bin/sh
#################################################
# Back up the database###################################################
#mysqldump backup program execution path DUMP=/usr/bin/mysqldump
#Backup file storage path OUT_DIR=/var/database
#Backup file permissions LINUX_USER=root
#The name of the database to be backed up DB_NAME=laowang
#Backup days, delete previous DAYS=1

#Enter the backup storage directory cd $OUT_DIR
#Get the current system time DATE=`date +%Y_%m_%d`
#Backup database file name OUT_SQL=$DB_NAME"_$DATE.sql"
#The final saved database backup file name TAR_SQL=$DB_NAME"_$DATE.tar.gz"
#Start backing up the database $DUMP --defaults-extra-file=/etc/my.cnf --default-character-set=utf8 $DB_NAME > $OUT_SQL

#Compress to .tar.gz format tar -czf $TAR_SQL ./$OUT_SQL
#Delete the backup file in .sql format rm $OUT_SQL
#Change the owner of the backup database file chown $LINUX_USER:$LINUX_USER $OUT_DIR/$TAR_SQL
#Delete the backup files from 30 days ago (note: there is a space between {} \;)
find $OUT_DIR -name "*.tar.gz" -type f -mtime +$DAYS -exec rm -f {} \;

3. Schedule

root@laowang:/# crontab -e
# mh dom monitor command
10 10 * * * /var/backups/mysql_backup.sh

ctrl+X to exit

y Save changes

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

The best way to automatically backup the mysql database (windows server)
MySQL scheduled backup using crontab scheduled backup example under Linux
A simple method to implement scheduled backup of MySQL database in Linux
Linux regularly backs up the MySQL database and deletes previous backup files (recommended)
How to back up mysql regularly and cut nginx access log regularly
Implementation of MySQL scheduled database backup (full database backup)

<<: Vue implements start time and end time range query

>>: Briefly understand the two common methods of creating files in Linux terminal