Automatically back up mysql and delete backups from 7 days ago.

1.Remote Connection to Server

First refer to https://www.west.cn/faq/list.asp?unid=417 tutorial to remotely log in to the server

2.Download Backup Script File

windows: Download http://downinfo.myhostadmin.net/vps/bkupmysql.bat and save to the root directory of drive d

linux:  wget  http://downinfo.myhostadmin.net/vps/bkupmysql.sh save to /root directory

3.Edit Script File

windows:

Open bkupmysql.bat with Notepad     

set dbuser=root                        #Set database user, generally no need to modify

set dbpass=passwd                      #Set database super admin password, need to modify to the actual password

set DaysAgo=7                          #Set retention days

set mysqlpath=d:\mysqlbak              #Set backup file save path, generally no need to modify

set mysql=D:\SOFT_PHP_PACKAGE\mysql\bin\mysql.exe   #Set backup file save path, version 5.6 requires modifying the path

set mysqldump=D:\SOFT_PHP_PACKAGE\mysql\bin\mysqldump.exe #Set mysql executable file path, version 5.6 requires modifying the path

set logfile=d:\bkinfo.log              #Set backup success or failure log file, generally no need to modify

set logfail=d:\bkfail.log              #Set detailed failure process log file, generally no need to modify

Note: If the created database contains Database,test,information_schema,performance_schema,mysql characters,

please delete

findstr -v "^Database$ ^Database$ ^test$ ^information_schema$ ^performance_schema$ ^mysql$"

the corresponding names in it

linux:

vi bkupmysql.sh

Press i to enter edit mode

dbuser='root'                         #Set database user, generally no need to modify

dbpass='passwd'                       #Set database super admin password, need to modify to the actual password

DaysAgo='7'                           #Set retention days

mysqlpath='/home/backup/mysql'        #Set backup file save path, generally no need to modify

mysql='/www/wdlinux/mysql/bin/mysql'  #Set mysql executable file path, generally no need to modify

mysqldump='/www/wdlinux/mysql/bin/mysqldump'  #Set mysqldump executable file path, generally no need to modify

exclude_db="^Database$\|^mysql$\|^information_schema$\|^performance_schema$\|^test$" 

#Set database names forbidden from backup, generally no need to modify

logfile='/root/bkinfo.log'            #Set backup success or failure log file, generally no need to modify

#logfail='/root/bkfail.log'           #Set detailed failure process log file, generally no need to modify

Mainly modify the database password; others can remain default

After modification, press ESC key to return, press :wq to save and exit

4.Add Scheduled Task

Execute at 4:00 AM every day

windows:

Execute under cmd

schtasks /create /tn "bkupmysql" /ru system /tr d:\bkupmysql.bat /sc daily /st 04:00

linux:

crontab -e

Press i to enter edit mode, add a new line at the end, copy the following content, where /root/bkfail.log is the save path for the detailed failure log

0 4 * * * /bin/sh /root/bkupmysql.sh >>/root/bkfail.log 2>&1

Press ESC key to return, press :wq to save and exit

Note: The above script is for reference only. Actual application may cause failure to back up or other abnormalities due to differences in environment, permissions, configuration, etc. Our company is not responsible for this. Please adjust it according to actual conditions.