Remote backup and recovery of MySQL database

Install MySQL client

We use cent os7 so use the yum command to install

yum install mysql

Back up files from a remote repository

Use the mysqldump command to remotely export backup files in sql format

Command format:

mysqldump -h[address] -P[port] -u[user name] -p[password] [database name] >[storage path]

Example:

mysqldump -hrm-2ez4k2hwt0pa8ie96.mysql.rds.aliyuncs.com -P3306 -uprod_root -p888888 mfqy-production >./pmall_prod_v5.3.0.sql

Compress file

The sql file that is directly backed up takes up a lot of space, we can compress it to save space

tar cvzf pmall_prod_v5.3.0.tar.gz pmall_prod_v5.3.0.sql  rm -rf pmall_prod_v5.3.0.sql

Write backup script

We can write a shell script to perform backup and compression

#!/bin/bashDate=`date +%Y%m%d%H%M%S`cd /home/maintenance/Backup/usr/bin/mysqldump -hrm-2ze4k2hch0pa4ie75.mysql.rds.aliyuncs.com -P3306 -uprod_root -pMfqy_root_87654321 mfqy-production>./pmall_prod_$Date.sql

Add executable permissions for scripts

chmod a+x backup.sh

Write delete script

We can write a shell script to perform deletion

/usr/bin/find /home/maintenance/Backup -mtime +30 -name "*.tar.gz" -exec rm -rf {} ;

Delete the file with the extension tar.gz created 30 days ago in the /home/maintenance/Backup directory.

{} The brace represent the file name found by the previous find

Add executable permissions for scripts

chmod a+x delete.sh

Create a timer

Use crontab -e command to enter the timer edit page

crontab -e

The meaning of the crontab file: In the crontab file created by the user, each line represents a task, and each field of each line represents a setting. Its format is divided into six fields. The first five sections are time setting sections. , The sixth paragraph is the command paragraph to be executed, the format is as follows:

minute   hour   day   month   week   command

Add timer for executing backup script

0 0 * * * /home/maintenance/Backup/backup.sh

Execute the backup script at 0:00 every day

Add timer to execute delete script

0 8 * * * /home/maintenance/Backup/delete.sh

Execute the delete script at 8:00 every day

View the timer list

crontab -l

Unzip the file

tar xvzf pmall_prod_v5.3.0.tar.gzrm -rf pmall_prod_v5.3.0.tar.gz

Restore backup

Restore the sql file backup to the database

Command format:

mysql -h[address] -P[port] -u[user name] -p[password] [database name] <[backup file]

Example:

mysql -hrdso30006c33s57oufvf.mysql.rds.aliyuncs.com -P3306 -uadm -p888888 pltx-production <./pmall_prod_v5.3.0.sql