我想在自己的 Windows7 下每天/周运行一次备份,就有了这个小工具
不过折腾 VPS 之后,就不只是要备份 MySQL 了,一些独立的站点数据也是需要备份的。如果有靠谱的第三方 Linux 主机或类似 Amazon 的云存储,那就简单多了,Linux - Linux 的备份很方便。
不过我想在自己的 Windows7 下每天/周运行一次备份,就有了这个小工具。
大概的想法如下:
使用 Putty 自带的 plink.exe 连接到 VPS 上,运行备份并退出登录;
接着使用 Putty 自带的 pscp.exe 将备份复制到本机;
添加一个计划任务,每天/周自动运行一次,实现定时备份。
代码如下:
Windows 下负责触发备份的 index.bat
@echo off & setlocal ENABLEEXTENSIONS
:: 这里只是 SSH 连接和备份路径配置信息
:: 请同时修改同目录下 linux 文件中要备份的路径和 MySQL 数据库
:: SSH 相关信息
set SERVER=192.168.1.100
set PORT=22
set USER=root
set PASSWORD=password
:: 和同目录下 linux 文件中配置的路径对应
set BACKUP_PATH=/root/backup
:: 备份要下载到的本机位置(会自动创建)
set SAVE_PATH=D:\Backup\
:: ---------- 配置结束,以下请勿修改 ----------
:: 运行时变量
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%\
:: 创建备份路径
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.
:: 生成备份目录和文件
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.
:: 下载备份
echo Downloading backups...
%EXE_SCP% -pw %PASSWORD% -P %PORT% %USER%@%SERVER%:%BACKUP_PATH%/*.* %TRUE_PATH%
echo Downloading backups... Done.
:exit
echo Exit
由 index.bat 调用的 Linux 端命令
代码如下:
#!/bin/bash
PATH=/usr/local/sbin:/usr/bin:/bin
# ---------- 配置项,不要最后的 / ----------
# 备份文件存储的路径,请与 index.bat 中的 BACKUP_PATH 保持一致
BACKUP_PATH=/root/backup
# 要备份的文件或目录,多个以空格分隔
FILES="/www/cmstop /www/dbpma"
# mysqldump 可执行文件的全路径(如果可以直接执行 mysqldump 可不用全路径)
MYSQLDUMP=mysqldump
# 要备份的 MySQL 数据库,多个以空格分隔
DATABASES="mysql test"
# MySQL 用户名
USER=root
# MySQL 密码
PASSWD=
# ---------- 配置项结束,下面的不用修改 ----------
# 运行时变量
DATETIME=$(date -d now +%Y-%m-%d)
CURPATH=$(cd "$(dirname "$0")"; pwd)
# 创建备份目录
if [ ! -d $BACKUP_PATH ]; then
mkdir -p $BACKUP_PATH
fi
rm -fR $BACKUP_PATH/*
# 备份 MySQL 数据库
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 $database.$DATETIME.dump.sql.tar.gz $database.$DATETIME.dump.sql
rm -f $database.$DATETIME.dump.sql
done
# 备份文件或目录
for file in $FILES
do
tar czf $file.$DATETIME.tar.gz $file
mv $file.$DATETIME.tar.gz $BACKUP_PATH
done
# 完成
cd $CURPATH
exit
下载完成之后不删除服务器上产生的备份文件,因为下一次备份前会自动清除。
下载和使用
下载文件到你本机,解压到某个地方,然后根据上文的说明修改配置项;
由于 SSH 第一次登录需要添加 Key 到本机,这一步没法简单忽略,可行的办法是载入 Putty 的 Session,不过挺麻烦的,还是运行一次简单些,我们要执行一次 SSH 登录:
代码如下:
:: 切换到你解压到的目录,如 D:\VPS\:
cd /d D:\VPS\
:: 根据情况修改下面的参数
exe\plink.exe -C root@192.168.1.100 -P 22 -pw password
:: 等待一下,会提示是否将 Key 加入本机信任主机列表里面,输入 yes 就可以了。
yes
:: 这就进去了,输入点常用命令玩玩,输入 exit 退出。
exit
根据的介绍,添加一个定时执行的计划任务;
手动执行一下上面添加的计划任务或观察一个执行周期,看备份是否有效。
待改进
同之前的脚本一样,似乎还缺少一个自动删除多少天前备份的功能。

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

Scenarios where PostgreSQL is chosen instead of MySQL include: 1) complex queries and advanced SQL functions, 2) strict data integrity and ACID compliance, 3) advanced spatial functions are required, and 4) high performance is required when processing large data sets. PostgreSQL performs well in these aspects and is suitable for projects that require complex data processing and high data integrity.

The security of MySQL database can be achieved through the following measures: 1. User permission management: Strictly control access rights through CREATEUSER and GRANT commands. 2. Encrypted transmission: Configure SSL/TLS to ensure data transmission security. 3. Database backup and recovery: Use mysqldump or mysqlpump to regularly backup data. 4. Advanced security policy: Use a firewall to restrict access and enable audit logging operations. 5. Performance optimization and best practices: Take into account both safety and performance through indexing and query optimization and regular maintenance.

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Atom editor mac version download
The most popular open source editor

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

SublimeText3 Chinese version
Chinese version, very easy to use

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
