MySQL database regularly backs up Shell scripts and back them up to other machines

This is a simple MySQL database backup shell script, suitable for backing up mysql databases on a single Linux server or VPS. The working principle is to use mysql mysqldum

This is a simple MySQL database backup shell script, suitable for backing up mysql databases on a single Linux server or VPS. The working principle is to use mysql mysqldum

This is a simple MySQL database backup shell script, suitable for backing up mysql databases on a single Linux server or VPS. The working principle is to use mysql's mysqldump tool to export the database. SQL file and archive all exported files.

Then we use the scp command in the shell script to copy the backup file to another backup machine. Because scp requires a password for each file transfer, we need to use the keep CT plug-in or use the key. This section describes how to generate a key pair.

1. Generate a key pair. I use an rsa key. When the command "ssh-keygen-t rsa" is generated, the system prompts you to enter the key pair storage location and password. Press enter to accept the default value.

The Public Key is stored in ~ /. Ssh/id_rsa.pub, and save the private key in ~ /. Ssh/id_rsa.

2. Then modify the. ssh directory permission, chmod 755 ~ /. Ssh. Finally, copy the public key to the machine you want to access and save it :~ /. Ssh/authorized_keys.

Scp-P 1110 ~ /. Ssh/id_rsa.pub daniel @*. *. *. *:/home/daniel /. ssh/authorized_keys (note that-P is capitalized, indicating that the ssh port of the target machine daniel is 1110, in lower case, p indicates the source ssh port. Modify the ssh port in the file/etc/ssh/sshd_config .) Here we will introduce the scp script key-free method.

Finally, use crontab to regularly execute the script!

The script is as follows:

#! /Bin/bash
# Description: MySQL buckup shell script
# Author: Daniel
# Web site :~ Danewang/blog/

St = $ (date + % s)
USER = "root"
PASSWORD = "*****" # User Name
DATABASE = "myblogdb" # DATABASE User Password
MAIL = "abcd@gmail.com" # mail
BACKUP_DIR =/home/daniel/data_backup/# backup file storage path
LOGFILE =/home/daniel/data_backup/data_backup.log # Log File Path

DATE = 'date + % Y % m % d-% H % m' # Use the date format as the file name
DUMPFILE = $ DATE. SQL
Archive1_date. SQL .tar.gz
OPTIONS = "-u $ USER-p $ PASSWORD $ DATABASE"

# Determine whether the backup file storage directory exists; otherwise, the directory is created.
If [! -D $ BACKUP_DIR]
Then
Mkdir-p "$ BACKUP_DIR"
Fi

# Before starting backup, write the backup information header to the log file
Echo ""> $ LOGFILE
Echo "--------------------"> $ LOGFILE
Echo "backup date:" $ (date + "% y-% m-% d % H: % M: % S") >>$ LOGFILE
Echo "-------------------"> $ LOGFILE

# Switch to the backup directory
Cd $ BACKUP_DIR
Mysqldump $ OPTIONS> $ DUMPFILE
# Determine whether the database backup is successful
If [[$? = 0]
Then
Tar czvf $ ARCHIVE $ DUMPFILE >>$ LOGFILE 2> & 1
Echo "[$ ARCHIVE] Backup Successful! ">>> $ LOGFILE
Rm-f $ DUMPFILE # Delete the original backup file. You only need to keep the backup package.
# Back up the compressed package file to another machine.
Scp-P $ ARCHIVE daniel @ *. *:/home/daniel/data_backup/
Else
Echo "Database Backup Fail! ">>> $ LOGFILE
# Send an email notification to the Administrator after the backup fails
Mail-s "database: $ DATABASE Daily Backup Fail! "$ MAIL
Fi
Echo "Backup Process Done"
# Delete a backup file for more than three days
# Cleaning
Find $ BACKUP_DIR-type f-mtime + 2-name "* .tar.gz"-exec rm-f {}\;

Modify/etc/crontab

# Vi/etc/crontab
Add the following content:
00 03 *** root/data/backup/dump. sh Note:/data/backup/dump. sh indicates the script storage location.

Indicates that the backup is performed at three o'clock every day.

Restart crond
#/Etc/rc. d/init. d/crond restart

Recommended reading:

Automatic MySQL database backup in CentOS