How to backup MySQL regularly and upload it to Qiniu

In most application scenarios, we need to back up important data and place it in a safe place in case of emergency.

Common MySQL data backup methods include directly packaging and copying the corresponding database or table files (physical backup), mysqldump full logical backup, xtrabackup incremental logical backup, etc.

Common data storage methods include local storage, FTP upload to a remote server, cloud storage (such as Alibaba Cloud OSS, Qiniu Cloud Storage, etc.), and even local storage.

We may not want to back up manually every time, nor do we want to spend so much time downloading every time, nor do we want to lose it on the server, because we need off-site backup. Then we can try to write a script to regularly back up the database and then automatically upload it to a designated server or cloud storage.

Here, we will talk about how to back up MySQL on a Linux server and upload it to Qiniu Cloud Storage.

Preparation

• Linux

•crontab service

You need to ensure that the crond service is in the started & auto-started state.

•gzip command

The system must be able to execute the gzip command normally to compress files.

•mysqldump command

The system must be able to execute the mysqldump command normally for logical data backup. The data backed up by mysqldump consists of executable SQL and there is no version incompatibility issue.

•qshell tool

qshell is a command line tool that Qiniu Cloud officially implements using the public API in Qiniu documents to facilitate developers to test and use Qiniu API services.

Specific documents and download address: https://developer.qiniu.com/kodo/tools/1302/qshell

• Qiniu Cloud Account

The prerequisite for storing data is of course to have a Qiniu account. Qiniu provides individuals with 10G of free storage space for personal use. Registered Address:
https://portal.qiniu.com/signup?code=3looatwobaxci

• Qiniu storage space

After you have a Qiniu Cloud account, you need to manually create a space (bucket) in the console to store data.

qshell configuration

The qshell we downloaded from the official address is a compressed package that supports multiple system platforms. Select the binary file of our corresponding system and grant it executable permissions. It can also be placed in a directory such as /usr/local/bin/ to facilitate direct calling of the qshell command.

Configure Qiniu account, ak and sk are in Qiniu Cloud Console > Personal Center > Key Management.
qshell account ak sk

This command will write the ak/sk account to ~/.qshell/account.json, and no further configuration is required.

The qshell command we use here is rput, which is to upload a file in multi-part upload mode. Use the document:

https://github.com/qiniu/qshell/blob/master/docs/rput.md

qshell rput <Bucket> <Key> <LocalFile> true

For other detailed functions of qshell, please refer to its documentation.

Script content

#!/bin/sh
#mysql data backup script
#
# use mysqldump --help,get more detail.
dbname=your_dbname
user=your_db_username
password=your_db_password
bakDir=/opt/backup/sql
logFile=/opt/backup/mysqlbak.log
datetime=`date +%Y%m%d%H%M%S`
keepDay=7
echo "-------------------------------------------" >> $logFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $logFile
echo "--------------------------" >> $logFile
cd $bakDir
bakFile=$dbname.$datetime.sql.gz
mysqldump -u $user -p $password $dbname | gzip > $bakFile
echo "Database [$dbname] backup completed" >> $logFile
echo "$bakDir/$bakFile" >> $logFile
echo "Start uploading backup files to Qiniu Cloud Storage" >> $logFile
/usr/local/bin/qshell rput <Bucket> database/$bakFile $bakFile true | sed -r "s/\x1B\[([0-9]{1,2}(;[0-9]{1,2})?)?[m|K]//g" >> $logFile 2>&1
echo "Delete the backup file ${keepDay} days ago" >> $logFile
find $bakDir -ctime +$keepDay >> $logFile
find $bakDir -ctime +$keepDay -exec rm -rf {} \;
echo " " >> $logFile
echo " " >> $logFile

The database configuration, log files, storage path, <Bucket>, etc. in the script need to be modified by yourself and exist. database/$bakFile represents <Key>, which is the path and file name in Qiniu storage and can be customized.

The script file needs to have executable permissions, and then the script can be executed for testing.

Scheduled tasks

# Execute the backup script at 2 a.m. every day * 2 * * * /opt/backup/baksql.sh

If the scheduled task is not executed, you can check the log /var/log/cron to troubleshoot the problem, or check whether crond is running.

Summarize

The above is the method I introduced to you to regularly back up Mysql and upload it to Qiniu. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!