Shulou(Shulou.com)06/01 Report--

In most application scenarios, we need to back up important data and place it in a safe place for emergencies.

The common MySQL data backup methods are directly packaging and copying the corresponding database or table files (physical backup), mysqldump full logical backup, xtrabackup incremental logical backup and so on.

Common data storage methods include local storage, FTP uploading to remote servers, cloud storage (such as Ali Cloud OSS, Qiniu Cloud Storage, etc.), or even local storage.

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

Here, let's talk about how to back up MySQL under the Linux server and upload it to Qiniu Cloud Storage.

Preparatory work

Linux system

Crontab service

You need to make sure that the crond service is started & self-starting.

Gzip command

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

Mysqldump command

The system needs to be able to execute mysqldump commands normally to logically back up data. The data backed up by mysqldump consists of executable SQL, and there is no problem of version incompatibility.

Qshell tool

Qshell is a command-line tool for developers to test and use Qiniu API service, which is officially implemented by Qiniuyun using API disclosed in Qiniu documentation.

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

Qiniuyun account

Of course, the premise of storing data is that there is an account of Qiniu. Qiniu provides 10 gigabytes of free storage space for personal use. Registered address:

Https://portal.qiniu.com/signup?code=3looatwobaxci

Qiniu storage space

After you have Qiniuyun's account, you need to manually create a bucket on the console to store the data.

Qshell configuration

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

Configure Qiniu account. Ak and sk are located in Qiniu Cloud console-> personal Center-> key Management.

Qshell account ak sk

This command writes the ak/sk account to ~ / .qshell / account.json, after which there is no need to configure it.

The qshell command we use here is rput, that is, upload a file by multipart upload and use the document:

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

Qshell rput true

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

Script content

#! / bin/sh# mysql data backup script## use mysqldump-- help Get more detail.dbname=your_dbnameuser=your_db_usernamepassword=your_db_passwordbakDir=/opt/backup/sqllogFile=/opt/backup/mysqlbak.logdatetime= `date +% Y% m% d% H% M% S `keepDay = 7echo "- -" > > $logFileecho $(date + "% y-%m-%d% Day") > > $logFileecho "- -"> > $logFilecd $bakDirbakFile=$dbname.$datetime.sql.gzmysqldump-u$user-p$password $dbname | gzip > $bakFileecho" Database [$dbname] backup completed "> > $logFileecho" $bakDir/$bakFile "> > $logFileecho" start uploading backup files to Qiniuyun Storage "> > $logFile/usr/local/bin/qshell rput database/$bakFile $bakFile true | sed-r" s /\ x1B\ [([0-9] {1jue 2}) [0-9] {1 exec rm 2})? [m | K] / / g "> > $logFile 2 > & 1echo" Delete backup files from ${keepDay} days ago "> > $logFilefind $bakDir-ctime + $keepDay > > $logFilefind $bakDir-ctime + $keepDay-exec rm-rf {}\; echo" > > $logFileecho "> > $logFile

The database configuration, log files, storage path, etc. in the script need to be modified and exist. Database/$bakFile, that is, the path in Qiniu storage & file name, can be customized.

The script file requires executable permissions, and then you can execute the script for testing.

Scheduled task

# execute backup script * 2 * / opt/backup/baksql.sh at 2 am every day

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

Summary

The above is the method of regularly backing up Mysql and uploading it to Qiniu, which is introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.