Setup method of automatic backup of Linux MySQL database

MySQL database table automatic backup shell script

Call Example:

The code is as follows	Copy Code
Mysql_data_backup.bash Www_cas Mysql_data_backup.bash 112 Www_qiche Mysql_data_backup.bash 112 Www_health

Include Features:

1. Parameter detection

2. Generate Backup logs

3. Automatically detects the tables in the specified database, backs up each table separately, and finally packages the tar file

4. Backup each table separately for easy recovery

5 support for multiple data host judgments

The following are the contents of the file

The code is as follows

Copy Code

#!/bin/bash # Check Host If [! $];then Echo ' Usage:mysql_data_backup [HostID] dbname ' Exit Else If [$1-eq 110]; Then db_host=192.168.1.110 Db_user=xxxx Db_passwd=xxx elif [$1-eq 112]; Then db_host=192.168.1.112 Db_user=xxx Db_passwd=xxx Else Echo ' Invalid Host ID ' Exit Fi Fi # Check Database name If [! $];then Echo ' Usage:mysql_data_backup HostID [dbname] ' Exit Else Echo ' Backup begin ' Fi Begin_time= ' Date ' +%s ' Db_name=$2 # Get Table Info Tables_content= ' mysqlshow-u $db _user-p$db_passwd-h $db _host $db _name | Sed ' s/|//g ' | Sed ' s///g ' | Sed ' 1,4d ' | Sed ' $d ' # Check Content if emptyed tables_content_check= ' echo-n $tables _content >/tmp/mysql_backup_check.tmp ' if [!-s/tmp/mysql_backup_check.tmp];then Echo ' Backup stop ' Exit Fi # Plan Backup of SQL tables Tables= (${tables_content}) # Get tables Length tables_len=${#tables [*]} # backup file (TAR) number Backupfilenum=3 # Backup of directory do not bring/ Backupdir= "/backup1/sqldata/$db _name" # Backup of directory for date datestamp=$ (date "+%y%m%d") # log file Logfile= '/backup1/sqldata/backup.log ' # Final Backup Directroy Filedir= "$backupDir/$datestamp" # Auto Create target directory if [!-D $backupDir];then mkdir $backupDir Fi if [!-D $fileDir];then mkdir $fileDir Fi if [!-D $logfile];then Touch $logfile Fi echo "Total $tables _len tables." For ((i=0;i< $tables _len;i++)) Todo Tablename= "${tables[$i]}" Filename= "${tables[$i]}.sql" Filepath= "$fileDir/$fileName" echo "Table [${tables[$i]}] Backup ..." Mysqldump-e-H $db _host-u $db _user-p$db_passwd $db _name $tableName > $filePath Done # tar Files CD $backupDir Tar cvf ${datestamp}.tar./${datestamp} # Delete Source Archive Rm-rf./${datestamp} End_time= ' Date ' +%s ' Total_time=$[end_time-begin_time] Echo ' Backup done ' echo "Total time: ${total_time} second" Now_date= ' Date ' +%y-%m-%d%k:%m:%s ' # Save Log echo "${now_date}: Backup Database [${db_name}]: Total time [${total_time}s]" >> $logfile # Begin clean excess of backup file # count Backup dir name of length; Backupdir_length= ' expr length ' $backupDir ' # Get tar file list Backupfile_list= ' Find $backupDir-name "*.tar" | Sort-n-r-k 1. $backupDir _length ' # set Array Backupfiles= (${backupfile_list}) backupfile_length=${#backupFiles [*]} If [$backupFile _length-gt $backupFileNum];then         for (i=$ backupfile_length;i> $backupFileNum i--))         do                  filename= "${backupfiles[$i-1]}"                 RM-RF $fileName                 #save Log                 echo "File: $fileName deleted "                 echo" File: $fileName deleted ">> $logfile         done Else          echo "backup file number normal." Fi

Data recovery can use batch processing to recover data tables

g:database20110324 This directory to place the table SQL files that need to be recovered

Main.bat Restore.bat are placed in this directory

Click Main.bat to start the backup, each execution of a file will be paused, press any key can continue
Green background Blue text part needs to be modified as needed
Total 2 places
The first is the SQL file path
The second place is the database name

Main.bat content is as follows

The code is as follows	Copy Code
@echo off For%%b in (./*.sql) do @restore. bat g:database20110324%%b

Restore.bat content is as follows

:

code is as follows	copy code
@echo off Pause Echo file%1 begins restoring mysql-h localhost-u root-t database1--default-character-set=utf8-e "source%1" echo file%1 completes restore echo. Echo. Echo.