Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Script of MySQL automatic backup and remote timing FTP

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

This article mainly introduces "MySQL automatic backup script and remote timing FTP". In daily operation, I believe many people have doubts about MySQL automatic backup script and remote timing FTP. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MySQL automatic backup script and remote timing FTP". Next, please follow the editor to study!

Share a MySQL automatic backup script, scheduled execution settings and Windows automatic FTP.

Prerequisite environment: the MySQL database server opens vsftpd and configures the appropriate account so that it can be FTP by the intranet storage server

Step 1: write MySQL auto-execution scripts

#! / bin/bash

# mysql_db_backup.sh: backup mysql databases.

#

# Last updated: Wed Nov 9 07:01:01 CST 2011

#-

# This is a free shell script under GNU GPL version 2.0 or above

# Copyright (C) 2011 Andy Yao

# Blog: http://t.qq.com/andy_microblog

#-

# your mysql login information

# db_user is mysql username

# db_passwd is mysql password

# db_host is mysql host

#--

Db_user= "root"

Db_passwd= "123456"

Db_host= "192.168.1.11"

# the directory for story your backup file.

Backup_dir= "/ mnt/sdb1/mysql_db_backup"

# date format for backup file (dd-mm-yyyy)

Time= "$(date +"% Y-%m-%d_%H-%M-%S ")"

File_time= "$(date +"% Y-%m-%d_%H-%M-%S ")"

Mysql_backup_path= "$backup_dir/$file_time"

Mkdir $backup_dir/$file_time

Log_path= "$backup_dir/$file_time.log.txt"

#-this log is for monitor ssh status

Ssh_log_path= "$backup_dir/log.txt"

Echo "-" > > $ssh_log_path

Date > > $ssh_log_path

Echo "-" > > $log_path

Echo "-" > > $log_path

Echo "-" > > $log_path

Echo "backup mysql db start" > > $log_path

Date > > $log_path

Echo "-" > > $log_path

#! / bin/bash

Cat / dev/null > $backup_dir/mysqlback.txt

Connmsg= `mysql-h$db_host-u$db_user-p$db_passwd $db / share/ "$line" .sql

Echo "-" > > $log_path

Date > > $log_path

Echo "$line" > > $log_path

Mysqldump-h$db_host-u$db_user-p$db_passwd "$line"-- lock-tables=false | gzip-9 > "$mysql_backup_path/$line.$time.sql.gz"

Date > > $log_path

Echo "-" > > $log_path

Fi

Done

< $backup_dir/mysqlback.txt echo "---------------------" >

> $log_path

Echo "backup mysql db stop" > > $log_path

Date > > $log_path

Echo "-" > > $log_path

Echo "-" > > $log_path

Echo "-" > > $log_path

#-this log is for monitor ssh status

Date > > $ssh_log_path

Echo "-" > > $ssh_log_path

Ls-l $mysql_backup_path > > $log_path

Echo "-" > > $log_path

Cd $backup_dir

Du-s > > $log_path

Du-sm > > $log_path

Du-sh > > $log_path

Echo "-" > > $log_path

Du-h | sort-rk2 > > $log_path

Exit 0

Step 2: regularly execute mysql backup scripts and set up crontab, which should not be explained, right?

[root@localhost /] # cat / etc/crontab

SHELL=/bin/bash

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

MAILTO=root

HOME=/

# run-parts

01 * root run-parts / etc/cron.hourly

02 4 * root run-parts / etc/cron.daily

22 4 * 0 root run-parts / etc/cron.weekly

42 4 1 * * root run-parts / etc/cron.monthly

01 7 * root / mysql_db_backup/mysql_db_backup_auto_db_filelist.sh

* * / 1 * root ntpdate 203.129.68.14 & & hwclock-w

Step 3: automatically schedule FTP on the Windows side, save the following code as bat, and set the scheduled task

@ echo off & color 1f & title automatic FTPMYSQL backup files

Mode con: cols=60 lines=10

Echo =

Echo--

Echo--

Echo-automatic FTPMYSQL backup files # #-# #--

Echo--

Echo--

Echo-Please do not close the program window manually while processing

Echo--

Echo-when finished, the program will close automatically.

Set xtime=%time::=%

Set xdate=%date%

Set copy_path=%xdate:~0,4%-%xdate:~5,2%-%xdate:~8,2%_07-01-01

Rem specifies the LOG storage path

Set log_path=c:\ bat\ log\ ftp_mysql_copy.log.txt

Echo-- > > log_path%

Echo-> > log_path%

Date / t > > log_path% & time / t > > log_path%

Echo-start-> > log_path%

Cd E:\ MYSQL_BACKUP_12

E:

Md copy_path%

Cd copy_path%

Echo open 192.168.1.11 > ftp.src

Echo username > > ftp.src

Echo password > > ftp.src

Echo cd / software/mysql_db_backup/%copy_path%/ > > ftp.src

Echo pwd > > ftp.src

Echo ls > > ftp.src

Echo prompt > > ftp.src

Echo bin > > ftp.src

Echo mget * > ftp.src

Echo bye > > ftp.src

Ftp-s:ftp.src

Del ftp.src

Echo-- end-> > log_path%

Date / t > > log_path% & time / t > > log_path%

Echo-> > log_path%

Echo-- > > log_path%

When you're done with the above, you can start testing.

At this point, the study of "MySQL automatic backup script and remote timing FTP" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report