MySQL – Periodic backup, retain the latest 31 days of data

My personal blog website, welcome to visit: learn to teach government

The background,

Overview: For our production databases, we need to make regular backups to avoid data loss. Such as daily backups, but also need to clean up the previous ones. Here I show only the latest 31 days of data.

2. Use backup commands

1. The mysqldump command is directly used by mysql to back up the database. The usage is as follows:

   #grammar
   mysqldump -u<username> -p<password> dbname tablename1 tablename2... > /path/to/***.sql
   
   #The sample
   mysqldump -u root -p '123456' mytest student > /home/back/student_bak.sql
   Copy the code

2. In some versions, the user may fail to enter the password in plain text. In this case, you need to add the password in the mysql configuration file (CNF configuration).

   #Edit the file
   vim /etc/mysql/mysql.conf.d/mysqld.cnf
   Copy the code

   The configuration is as follows:

   #The backup
   [mysqlddump]
   user=root
   password=123456
   Copy the code

3. Save and exit, and restart mysql

   systemctl restart mysql
   Copy the code

3. Write backup scripts

1. Create a folder for storing backup data. Mine is:

   /home/blog/back
   Copy the code

2. Add backup script mysql_blog_backup.sh (only the latest 31 days of data are retained) :

   #! /bin/bash
   
   #Save the number of backups and back up data for 31 days
   number=31
   #Backup Save Path
   backup_dir=/home/blog/back/sql
   #The date of
   dd=`date +%Y-%m-%d-%H-%M-%S`
   #Backup tool
   tool=mysqldump
   #The user name
   username=root
   #passwordPassword = Your password#The database to be backed up
   database_name=blog
   
   #Create a folder if it does not exist
   if [ ! -d $backup_dir ];
   then
       mkdir -p $backup_dir;
   fi
   
   #Mysqldump -u root -p123456 users > /root/mysqlbackup/users-$filename.sql
   $tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql
   
   #Write create backup log
   echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt
   
   #Find the backup that needs to be deleted
   delfile=`ls -l -crt  $backup_dir/*.sql | awk '{print $9 }' | head -1`
   
   #Check whether the current number of backups is greater than$number
   count=`ls -l -crt  $backup_dir/*.sql | awk '{print $9 }' | wc -l`
   
   if [ $count -gt $number ]
   then
     #Delete the earliest backup and retain only the number backup
     rm $delfile
     #Write delete file log
     echo "delete $delfile" >> $backup_dir/log.txt
   fi
   Copy the code

3. Assign execute permission to the script

   chmod u+x  mysql_blog_backup.sh
   Copy the code

4. In this case, you can directly run the sh script to verify the execution

4. Scheduled tasks

1. Linux corntab command, accurate to the minute. The two commands used here

   Crontab -e # Edit the crontab scheduled task crontab -l # Query the crontab taskCopy the code

2. The following commands are commonly used to service scheduled tasks

   Service cron start // Start the service service cron stop // Stop the service service cron restart // Restart the service service cron reload // Reload the service cron Status // View the service statusCopy the code

3. Add a scheduled task to execute our backup script

   #The scheduled task editing page is displayed
   crontab -e
   
   #Add, execute the script once a day at 3am
   00 03 * * * /home/blog/back/mysql_blog_backup.sh
   Copy the code

4. Reload the configuration at this point.

Five, the results

1. If you see the following figure, the task has been added successfully
   

   

Blog www.cnblogs.com/letcafe/p/m for reference…