Batch backup of remote VPS (CentOS) data on Windows host on a regular basis _ MySQL

Source: Internet
Author: User
Tags pscp
Back up remote VPS (CentOS) data in batches on Windows host

BitsCN.com does not only need to back up MySQL after VPS, but also some independent site data needs to be backed up.
If there is a reliable third-party Linux host or cloud storage like Amazon, it is much simpler, and Linux-Linux backup is very convenient.
However, I want to run a backup every day or every week in my Windows 7 environment.

The general idea is as follows:
Connect to the VPS using plink.exe provided by Putty, run the backup, and log out;
Use pscp.exe, which comes with Putty, to copy the backup to the local machine;
Add a scheduled task and run it once every day/week to implement scheduled backup.


Index. bat that triggers backup in Windows
@ Echo off & setlocal ENABLEEXTENSIONS

: Only the SSH connection and backup path configuration information is provided here.
: Modify the path and MySQL database to be backed up in linux files in the same directory at the same time.

: SSH-related information
Set SERVER = 192.168.1.100
Set PORT = 22
Set USER = root
Set PASSWORD = password

: Corresponds to the path configured in the linux file in the same directory.
Set BACKUP_PATH =/root/backup

: The location of the local backup file to be downloaded (automatically created)
Set SAVE_PATH = D:/Backup/

: ---------- Configuration is complete. do not modify the following ----------

: Runtime variable
Set EXE_SSH = % ~ Dp0/exe/plink.exe
Set EXE_SCP = % ~ Dp0/exe/pscp.exe
Set LINUX = % ~ Dp0/linux

Set YEAR = % date :~ 0, 4%
Set MONTH = % date :~ 5, 2%
Set DAY = % date :~ 8, 2%
Set TRUE_PATH = % SAVE_PATH % YEAR %-% MONTH %-% DAY %/

: Create backup path
Echo Creating Backup Dir...
If not exist % TRUE_PATH % (
Mkdir % TRUE_PATH % 2> nul
)
If not exist % TRUE_PATH % (
Echo Backup path: % TRUE_PATH % not exists, create dir failed.
Goto exit
)
Echo Creating Backup Dir... Done.

: Generate backup directories and files
Echo Connecting to remote server and creating backups...
% EXE_SSH %-C % USER % @ % SERVER %-P % PORT %-pw % PASSWORD %-m % LINUX % 2> nul
Echo Connecting to remote server and creating backups... Done.

: Download backup
Echo Downloading backups...
% EXE_SCP %-pw % PASSWORD %-P % PORT % USER % @ % SERVER %: % BACKUP_PATH %/*. * % TRUE_PATH %
Echo Downloading backups... Done.

: Exit
Echo Exit

Linux commands called by index. bat

#! /Bin/bash

PATH =/usr/local/sbin:/usr/bin:/bin

# ---------- Configuration item, do not final /----------

# The path of the backup file storage must be consistent with the BACKUP_PATH in index. bat.
BACKUP_PATH =/root/backup

# Files or directories to be backed up, separated by spaces
FILES = "/www/cmstop/www/dbpma"

# Full path of mysqldump executable files (if you can directly execute mysqldump, you do not need full path)
MYSQLDUMP = mysqldump

# The MySQL database to be backed up. multiple MySQL databases are separated by spaces.
DATABASES = "mysql test"

# MySQL User name
USER = root

# MySQL password
PASSWD =

# ---------- The configuration item ends, and the following does not need to be modified ----------

# Runtime variables
DATETIME = $ (date-d now + % Y-% m-% d)
CURPATH = $ (cd "$ (dirname" $0 ")"; pwd)

# Creating a backup directory
If [! -D $ BACKUP_PATH]; then
Mkdir-p $ BACKUP_PATH
Fi

Rm-fR $ BACKUP_PATH /*

# Backing up a MySQL database
Cd $ BACKUP_PATH
For database in $ DATABASES
Do
If ["$ PASSWD" = ""]; then
$ MYSQLDUMP-u $ USER $ database> $ database. $ DATETIME. dump. SQL
Else
$ MYSQLDUMP-u $ USER-p $ PASSWD $ database> $ database. $ DATETIME. dump. SQL
Fi
Tar czf returns database.time.dump. SQL .tar.gz $ database. $ DATETIME. dump. SQL
Rm-f $ database. $ DATETIME. dump. SQL
Done

# Backup file or directory
For file in $ FILES
Do
Tar czf restart file.time.tar.gz $ file
Mv audio file.time.tar.gz $ BACKUP_PATH
Done

# Complete
Cd $ CURPATH
Exit

After the download is complete, the backup files generated on the server are not deleted, because the files are automatically cleared before the next backup.
Download and use
Download the file to your local machine, decompress it to a certain place, and modify the configuration items according to the preceding instructions;
Because the Key needs to be added to the local machine for the first SSH login, this step cannot be simply ignored. The feasible method is to load the Putty Session, but it is quite troublesome. it is simpler to run it once, we need to perform an SSH logon:

: Switch to the directory you extracted, such as D:/VPS /:
Cd/d D:/VPS/

: Modify the following parameters as needed.
Exe/plink.exe-C root@192.168.1.100-P 22-pw password

: Wait, and you will be prompted whether to add the Key to the list of hosts trusted by the local machine. enter yes.
Yes

: Now, enter some common commands to play with it and enter exit to exit.
Exit

Add a scheduled task according to the introduction in this article;
Manually execute the previously added scheduled task or observe an execution cycle to check whether the backup is valid.
To be improved
Like the previous script, it seems that there is still a lack of a function to automatically delete the backup several days ago. BitsCN.com

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.