MySQL scheduled backup solution (using Linux crontab)

Preface

Although some love in this world has a price, data is priceless. Data backup is particularly important so that if you accidentally delete the database one day in the future, you don’t have to run away in a hurry.

The solution introduced in this article is to use Linux's own crontab scheduled task function to regularly execute the database backup script.

Technical points:

• Database backup dump command
• Shell Script
• Linux scheduled tasks crontab
  

Data backup dump

The database has a command to export the data and structure within the database, which is the backup.

Restoring the backed-up data will delete and rebuild the table in the original data, and then insert the data in the backup. This is recovery.

It is important to note that if the data before recovery is more than the backup, the extra data will be lost after recovery.

List the backup and recovery commands of the two databases I often use

postgresql:

Backup pg_dump -h [ip] -U [user name] [database name] > [exported .sql file]

Restore psql -s [database name] -f [export .sql file]

mysql:

Backup mysqldump -h -u [user name] -p [database name] > [exported .sql file]

Restore mysql -u [user name] -p [database name] < [exported .sql file]

Shell Script

To complete a fully functional backup solution, you need shell scripts.

We want this script to back up to a specified path and store them in a compressed format, up to 30 files. If there are more than 30 files, the oldest one will be deleted, and the operation log will be recorded.

Nothing more to say, it’s all in the script, let’s do it!

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

#The maximum number of backup files to save is count=30
#Backup save path backup_path=/app/mysql_backup
#Date date_time=`date +%Y-%m-%d-%H-%M`

#If the folder does not exist, create it if [ ! -d $backup_path ]; 
then  
 mkdir -p $backup_path; 
fi
#Start backupmysqldump -u $username -p$password $database_name > $backup_path/$database_name-$date_time.sql
#Start compressing 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 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`

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

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

Give the script a nice name that makes sense, dump_mysql.sh

Give the script executable permissions chmod +x dump_mysql.sh. After execution, the script turns green and is an executable file.

Execution method: ./ plus script name

The meaning of chmod command parameters -
+ represents adding certain permissions
x represents executable permission

Scheduled tasks crontab

Crontab is a scheduled task function that comes with Linux. We can use it to execute the dump_mysql.sh script once every morning.

Crontab usage:

• crontab -l View the scheduled task list
• crontab -e Edit (add/delete) scheduled tasks
  

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

The addition is completed when the capital is protected and the withdrawal is completed.

Content explanation:

00 01 * * * /app/dump_mysql.sh is divided into two parts.

The first part 00 01 * * * is the period of the scheduled task, and the second part /app/dump_mysql.sh is what to do at the specified time.

The period expression is five placeholders, representing: minutes, hours, days, months, and weeks.

The placeholder * represents every. The first place is every minute, the second place is every hour, and so on.

Placeholders use specific numbers to represent specific times. 10 in the first position means 10 minutes, 10 in the third position means the 10th, and so on.

The placeholder - indicates an interval. 5-7 in the first place means 5 minutes to 7 minutes, in the fifth place means Friday to Sunday, and so on.

The placeholder / indicates the interval. 5-10/2 is used in the first position to indicate the interval from 5 to 10 minutes, and in the second position to indicate the interval from 5 to 10 o'clock to 2 hours, and so on.

Placeholders use , to indicate a list. 5,10 in the first place means 5 points and 10 points, in the fourth place means May and October, and so on.

Summarize

This is the end of this article about MySQL scheduled backup solution. For more relevant MySQL scheduled backup content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!