It takes about 5 minutes to read this article

Full backup of MySQL periodically (1)

MySQL > Alter database backup (2)

Upload MySQL backup to private cloud (3)

The profile

  • The introduction

  • Full amount of backup

  • Restore full backup

  • Regular backup

The introduction

After the launch of the product, our data is very important, not the slightest mistake should be made, we should be fully prepared, even if one day it will be hacked or malicious deletion, then GG. So we need to do full backup and incremental backup to our online database regularly. For example, perform incremental backup once a day and full backup once a week. The following operating system is centos7.

Making address:

https://github.com/zonezoen/MySQL_backup
Copy the code

Full amount of backup

/usr/bin/mysqldump -uroot -p123456  --lock-all-tables --flush-logs test > /home/backup.sql
Copy the code

As shown in the previous code, the function is to make a full backup of the Test database. The MySQL user name is root. The password is 123456. The backup file path is /home. SQL parameter – flush-logs: a new log file is used to record the following log parameters – lock-all-tables: locks all databases

The following is the database backup script file I used:

Script file function is not very complicated, first of all, variable assignment. Then back up the database, then go to the directory where the backup files reside, and then compress the backup files. The penultimate third line is to use nodeJS to upload the backup database file to the seven cow cloud, here is not too much description, and the topic is not consistent with this article, want to see the specific implementation can view GitHub source code. The corresponding variable can be changed into its own value can be taken over to use.

#! /bin/bash
Create the following directories before using them
Get the current time
date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/home/db/backup/mysql
username="root"
password="123456"
db_name="zone"
Define the backup file name
fileName="${db_name}_${date_now}.sql"
Define the backup file directory
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."
/usr/bin/mysqldump -u${username} -p${password}  --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}
Go to the backup file directory
cd ${backUpFolder}
# compress backup files
tar zcvf ${fileName}.tar.gz ${fileName}

# use nodejs to upload backup file other place
#NODE_ENV=$backUpFolder@$backUpFileName node /home/tasks/upload.js
date_end=$(date "+%Y%m%d-%H%M%S")
echo "finish backup mysql database ${db_name} at ${date_end}."
Copy the code

Restore full backup

mysql -h localhost -uroot -p123456 < bakdup.sql
Copy the code

or

mysql> source /path/backup/bakdup.sql
Copy the code

Well, restore full backup is only two sentences, it seems that there is no need to say anything more. By the way, after restoring the full backup, the incremental backup after the full backup is also restored to the database.

Regular backup

Run the following command to go to the page for editing scheduled tasks:

crontab -e
Copy the code

Add the following command to execute the backup script every minute:

* * * * * sh /usr/your/path/mysqlBackup.sh
Copy the code

Every five minutes:

*/5 * * * * sh /usr/your/path/mysqlBackup.sh
Copy the code

Hourly execution:

0 * * * * sh /usr/your/path/mysqlBackup.sh
Copy the code

Daily execution:

0 0 * * * sh /usr/your/path/mysqlBackup.sh
Copy the code

Weekly execution:

0 0 * * 0 sh /usr/your/path/mysqlBackup.sh
Copy the code

Monthly execution:

0 0 1 * * sh /usr/your/path/mysqlBackup.sh
Copy the code

Annual implementation:

0 0 1 1 * sh /usr/your/path/mysqlBackup.sh
Copy the code

The format of the crontab command is as follows:

There will be a dedicated article on the scheduled task Crontab for Linux.

Ok, today I will share these knowledge, next I will share the knowledge of MySQL incremental update, please look forward to!

Follow the wechat public account, reply to [mysql Resources], get the advanced video tutorial on mysql performance optimization