MySQL scheduled backup scheme

Time:2020-9-29

Although some love in this world is valuable, but the data is priceless, and data backup is particularly important. You can delete the database one day in the future, so you don’t have to rush to run.

MySQL scheduled backup scheme

The solution introduced in this article is to use the crontab timing task function of Linux to execute the script of backup database regularly.

Technical points:

  • Database backup dump command
  • Shell script
  • Crontab of Linux timing task

Data backup dump

The database has a command to export the data and structure in the database, which 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 recovery.
It should be noted that if there is more data before recovery than that of backup, there will be no more data after recovery.

List two kinds of database backup and restore commands that I use frequently

postgresql:

backups Pg_ Dump - H [IP] - u [user name] [library name] > [exported. SQL file]
recovery PSQL - s [library name] - f [export. SQL file]

mysql:

backups Mysqldump - H - u [user name] - P [library name] > [exported. SQL file]
recovery MySQL - u [user name] - P [library name] < [exported. SQL file]

Shell script

To complete a fully functional backup solution, you need shell script.
We need to make this script backup to the specified path, and compress the storage, up to 30, more than 30, delete the earliest, and record the operation log.
Don’t say anything. It’s all in the script!

#User name
username=root
#Password
password=nicai
#Database to be backed up
database_name=l_love_you

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

#Create if folder 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 compressing
cd $backup_path
tar -zcvf $database_name-$date_time.tar.gz $database_name-$date_time.sql
#Delete 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

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

#Determine 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 the count number of backups
  rm $delfile
  #Update delete file log
  echo "delete $delfile" >> $backup_path/dump.log
fi

Give the script a beautiful name as the name suggestsdump_mysql.sh
Giving scripts executable permissionschmod +x dump_mysql.shAfter execution, the script turns green, which is the executable file
Execution method:. / add script name

Chmod command parameter meaning--
+Delegate to add some permissions
X stands for executable rights

Crontab

Crontab is a regular task function of Linux. We can use it to execute it once every morningdump_mysql.shscript.

Crontab usage:
  • Crontab – L view scheduled task list
  • Crontab – e edit (add / delete) scheduled tasks

Run the crontab – e command, open a text that can be edited, and enter00 01 * * * /app/dump_mysql.sh
Break even and exit is added.

Content explanation:

00 01 * * * /app/dump_mysql.shLook at it in two parts,
Part one00 01 * * * Is the period of the timed task, part two/app/dump_mysql.shIt’s time to do things.
Period expressions are five place holders that represent:Minutes, hours, days, months, weeks

Place holder with*expresseachThe first one is every minute, the second is every hour, and so on
Place holder withSpecific figuresexpressspecific timeIt is used in the 10th place and so on
Place holder with-expresssection5-7 in the first place is 5 to 7 points, in the fifth place is from 5 to Sunday, and so on
Place holder with/expressinterval5-10 / 2 used in the first place is 5 minutes to 10 minutes interval of 2 minutes, used in the second place to indicate 5:00 to 10:00 interval of 2 hours, and so on
Place holder with,expresslist5,10 in the first place are 5 points and 10 points; in the fourth place, it means may and October, and so on


Updated July 20, 2020

Password security in scripts

Thank you for your reminding. It is not safe to put database password directly in the script.
After our script is executed, MSYQL will also print a line of warning:
mysqldump: [Warning] Using a password on the command line interface can be insecure
Passwords may not be secure on the command line interface

On this issue, the online processing method is more consistent, mysqldump command to remove the – U user name – P password parameter, use the--defaults-fileParameter to read the configured user name and password.

It is also the solution given by the official website:
MySQL scheduled backup scheme

Specifying a password on the command line should be considered unsafe. To avoid giving passwords on the command line, use the options file. See section 6.1.2.1: user password security guide.

MySQL scheduled backup scheme

edit my.cnf

Edit database home directory my.cnf , my path is / etc/ my.cnf 。 Add a password under [Client]. If there is no [Client], write one, otherwise the password below will be invalid.

[client]
password="nicai"

By default, the account currently logged in to the Linux server is used as the account to connect to the database. I happen to be root, so the user name can not be matched. If it is inconsistent, you need to add one

user=xxx

You’d better give it after editing my.cnf Set the permissions of 600. 600 means that only the owner has read and write permissions.

shell> chmod 600 my.cnf

If the password contains special characters, be sure to use double quotation marks, or you will report an error if you run the mysqldump command
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

modify parameters

When you run the mysqldump command, use the--defaults-file=/etc/my.cnfParameter substitution-uroot -ppasswordThe parameters of the.
Remove the user name and password from the script, and modify the mysqldump command as follows:

mysqldump --defaults-file=/etc/my.cnf $database_name >$backup_path/$database_name-$date_time.sql

Although the above method is provided by the government, it still writes the plaintext in the document. It is just adding the read and write permission. It is still not very convincing. You can consider the following method

Use SHC to encrypt the script:

In Linux, the script encryption tool SHC encrypts the shell script. SHC can convert the shell script into an executable binary file, which is also a way.

The backup files also need to be encrypted:

Tar cooperates with OpenSSL to complete encryption and compression
Tar - czvf - source file | OpenSSL DES3 - Salt - K password - out encryption file tar.gz
decrypt:
OpenSSL DES3 - D - K password - Salt - in encrypt file tar.gz | tar xvf -