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

MySQL database regularly backs up Shell scripts and backs up them to other machines. This is a simple MySQL database backup shell script, applicable to mysql database backup on a single Linux server or VPS, the working principle is to use mysql mysqldump 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. The private key is stored 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: [plain] #! /Bin/bash # description: MySQL buckup shell script # author: Daniel st =$ (date + % s) USER = "root" PASSWORD = "*****" # username 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' # date used format: DUMPFILE = $ DATE. SQL archiveappsdate. SQL .tar.gz OPTIONS = "-u $ USER-p $ PASSWORD $ DATABASE" # judge backup Whether the file storage directory exists. Otherwise, create the directory if [! -D $ BACKUP_DIR] then mkdir-p "$ BACKUP_DIR" fi # before starting the backup, write the BACKUP information header to the log file echo "" >>loglogfile 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 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 package file to another machine. Scp-P $ ARCHIVE daniel @ *. *:/home/daniel/data_backup/else echo "Database Backup Fail! ">>$ LOGFILE # send an email to the Administrator after the Backup fails to remind you of mail-s" database: $ DATABASE Daily Backup Fail! "$ MAIL fi echo" Backup Process Done "# Delete the 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 and add: 00 03 *** root/data/backup/dump. sh note/data/backup/dump. sh is 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