MySQL database automatic backup and scheduled backup

MySQL database automatic backup and scheduled backup

Backup is the basis of DISASTER recovery (Dr). It is a process of copying all or part of data sets from disks or arrays of application hosts to other storage media to prevent data loss caused by system errors or system faults. And for some sites, systems, the database is everything, so do a good database backup is crucial!

1. Viewing Disk Space

# df -h
Copy the code

This command is commonly used to check the occupied disk space and remaining disk space.

Commonly used parameters

2. Creating a Backup Directory

cd /home
mkdir backupcd backup
Copy the code

3. Create a backup Shell script

Suppose the database we need to back up periodically is Test

#! /bin/bash# create script
vim test.sh 

#! /bin/bash# Paste the content
mysqldump -uroot -proot test > /home/backup/t_user$(date +%Y%m%d_%H%M%S).sql

#! /bin/bashCompress the backup
mysqldump -uroot -proot test | gzip > /home/backup/test$(date +%Y%m%d_%H%M%S).sql.gz
Copy the code

4. Add executable permissions

chmod u+x test.sh
Copy the code

5. Adding a Scheduled Task

Install the crontab

If command not found is displayed, the crontab command is not installed

yum -y install vixie-cron
Copy the code

Check the crontab status

service crond start
service crond stop
service crond restart
service crond reload
service crond status
Copy the code

Adding a Scheduled Task

crontab -e
#Enter the following
#Execute a shell script every minute0, 0, 0 * *? /home/backup/test.shCopy the code

View the crontab task

#Example Create a scheduled task command
crontab -e
#View scheduled task commands
crontab -l
#None example Delete all scheduled task commands
crontab -r
Copy the code

Docker MySQL periodic backup

#Create it in the /home directory
vim test.sh
Copy the code

Regular script

#! /bin/bash
#Set the user name and password for logging in to mysql
mysql_user="root"
mysql_password="root"
mysql_host="localhost"
mysql_port="3306"
 
#Address for storing backup files
backup_location=/home/backup
 
#Whether to delete expired data
expire_backup_delete="ON"
expire_days=7
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"
#The backuptestData in the database
docker exec -i mysql mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B test > $backup_dir/test-$backup_time.sql
echo $welcome_msg
#Deleting Expired Dataif [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ]; then `find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf` echo "Expired backup data delete complete!" fiCopy the code

Setting Script Permissions

chmod 755 test.sh
Copy the code

You can run the sh test.sh command to check whether the command can be executed properly

Welcome to use MySQL backup tools!
Expired backup data delete complete!
Copy the code

After the script is correct, you can add scheduled tasks

#Example Add a scheduled crontab scheduled task0, 0, 12 * *? cd /home; sh test.sh >> log.txt 2>>log.txtCopy the code