CentOS 7 MySQL5.6 Sub-database-Fully prepared-shell script

#Change the amount you can use, the output information is automatically generated Mysql_backup.log logs to see when the script was backed up,

CentOS 7 MySQL Automatic backup shell script

System environment:

CentOS7 64-bit

MySQL-5.6

Principles and tools:

Shell Script

Mysqldump Program

crontab command

1. New Shell Script

vi/script/mysql_backup.sh

#!/bin/bash

#

# MySQL Backup Script

# Author:Rui.Xu

# time:07/2018

#

Clear

Mkdir-p/script

echo "" >>/script/mysql_backup.log

echo "------------------------------------------------------------" >>/script/mysql_backup.log

Echo Date: $ (date) >>/script/mysql_backup.log

echo User: $ (whoami) >>/script/mysql_backup.log

#Record script start time

shi= ' Date +%h:%m:%s:%n | Awk-f ': ' {print '} '

fen= ' Date +%h:%m:%s:%n | Awk-f ': ' {print $} '

miao= ' Date +%h:%m:%s:%n | Awk-f ': ' {print $} '

haomiao= ' Date +%h:%m:%s:%n | Awk-f ': ' {print $4} '

# Please modify the following configuration information yourself

mysql_user= "Root" #MySQL backup user

Mysql_password= "Amunlinux" #MySQL backup user password

mysql_host= "localhost"

Mysql_port= "3306"

Mysql_charset= "UTF8" #MySQL encoding





Backup_db_arr= ("Rui_xu" "Amun" "Linux" "Amunlinux") #The name of the database to be backed up, multiple separated by spaces such as ("DB1" "DB2" "DB3")





Backup_location=/home/mysql_backup/mysql #备份数据数据,, do not bring "/" at the end, this item can be kept by default, the program will automatically create the folder





Expire_backup_delete= ' on ' # Whether to enable expired backups to delete on for off





Expire_days=3 #Expiration time days Default is three days, this item is only valid when Expire_backup_delete is turned on

# We do not need to change the following

Backup_time= ' Date +%y-%m-%d-%h:%m ' #Define backup time





Backup_ymd= ' Date +%y-%m-%d ' #Define the date, day, and day in the backup directory





Backup_3ago= ' date-d ' 3 days ago ' +%y-%m-%d ' #3 days before date





Backup_dir= $backup _location/$backup _ymd #backup folder full path





Welcome_msg= "Welcome to use MySQL backup tools!"# Welcome

# to determine if MySQL starts, MySQL does not start then backup exits

mysql_ps= ' ps-ef |grep mysql |wc-l '

Mysql_listen= ' Netstat-an |grep listen |grep $mysql _port|wc-l '

if [[$mysql _ps = = 0]-o [$mysql _listen = = 0]]; Then

echo "Error:mysql is not running!  Backup stop! " >>/script/mysql_backup.log

Exit

Else

echo $welcome _msg >>/script/mysql_backup.log

Fi

# connected to MySQL database, unable to connect then backup exits

Mysql-h$mysql_host-p$mysql_port-u$mysql_user-p$mysql_password <<end

Use MySQL;

Select Host,user from user where user= ' root ' and host= ' localhost ';

Exit

End

Flag= ' echo $? '

if [$flag! = "0"]; Then

echo "Error:can" t connect MySQL server!  Backup stop! " >>/script/mysql_backup.log

Exit

Else

echo "MySQL Connect ok! Please wait ... ">>/script/mysql_backup.log

# Determine if there is a database that defines the backup, and if it is defined, start the backup or exit the backup

If ["$backup _db_arr"! = ""];then

#dbnames =$ (cut-d ', '-f1-5 $backup _database)

#echo "Arr is (${backup_db_arr[@]})"

For dbname in ${backup_db_arr[@]}

Do

echo "Database $dbname backup start ..." >>/script/mysql_backup.log

' Mkdir-p $backup _dir '

' Mysqldump-h$mysql_host-p$mysql_port-u$mysql_user-p$mysql_password $dbname--default-character-set= $mysql _ CharSet | gzip > $backup _dir/$dbname-$backup _time.sql.gz '

Flag= ' echo $? '

if [$flag = = "0"];then

echo "Database $dbname success Backup to $backup _dir/$dbname-$backup _time.sql.gz" >>/script/mysql_backup.log

Else

echo "Database $dbname backup fail!" >>/script/mysql_backup.log

Fi

Done

Else

echo "Error:no database to backup! Backup Stop ">>/script/mysql_backup.log

Exit

Fi

# If delete expired backup is turned on, delete operation

If ["$expire _backup_delete" = = "On"-a "$backup _location"! = ""];then

# ' Find $backup _location/-type d-o-type f-ctime + $expire _days-exec rm-rf {} \; '

' Find $backup _location/-type d-mtime + $expire _days | Xargs RM-RF '

echo "Expired backup data Delete complete!" >>/script/mysql_backup.log

Fi

echo "All database backup success!  Thank you! " >>/script/mysql_backup.log

Tar-zcvp-f/mnt/mysql-all-$backup _ymd.tar.gz $backup _dir >>/script/mysql_backup.log

scp/mnt/mysql-all-$backup _ymd.tar.gz [email protected]:/home/mysql_full_backup/>>/script/mysql_backup.log

rm-fr/mnt/mysql-all-$backup _ymd.tar.gz

#记录结束时间

shi1= ' Date +%h:%m:%s:%n | Awk-f ': ' {print '} '

Fen2= ' Date +%h:%m:%s:%n | Awk-f ': ' {print $} '

miao3= ' Date +%h:%m:%s:%n | Awk-f ': ' {print $} '

haomiao4= ' Date +%h:%m:%s:%n | Awk-f ': ' {print $4} '

H= ' expr $shi 1-$shi '

f= ' Expr $fen 2-$fen '

m= ' expr $miao 3-$miao '

hm= ' expr $haomiao 4-$haomiao '

echo "" >>/script/mysql_backup.log

echo "Elapsed time:" $H "H" $F "M" $M "s" $HM "MS" >>/script/mysql_backup.log

echo "" >>/script/mysql_backup.log

Exit

Fi

Note: If this sh file is edited under win, it needs to be converted to UNIX format by editor, otherwise SH will perform unsuccessfully,

2. Modify shell script properties to give execute permission

chmod 600/opt/mysqlbackup.sh

chmod +x/opt/mysqlbackup.sh

3. Timed Script execution

Way One:

Execute CRONTAB-E command

Crontab-e

Enter the following to set a scheduled automatic backup daily 3:00

* * * * */root/mysqlbackup.sh

Way two:

Open the Auto-execute file

Vi/etc/crontab

Add the following to the ETC to automatically perform the task every 3:00.

* * * * */root/mysqlbackup.sh

Crontab File Summary:

In the crontab file created by the user, each line represents a task, each field of each row represents a setting, its format is divided into six fields, the first five is the time setting segment, and the sixth paragraph is the command segment to execute, in the following format:

Minute hour day Month Week command

Time-sharing Weekly command

which

Minute: Represents minutes, which can be any integer from 0 to 59. (Available per minute * or */1)

Hour: Represents the hour, which can be any integer from 0 to 23. (0 means 0 points)

Day: Represents a date, which can be any integer from 1 to 31.

Month: Represents the month, which can be any integer from 1 to 12.

Week: Represents the day of the week, which can be any integer from 0 to 7, where 0 or 7 represents Sunday.

Command: The commands to execute can be either system commands or script files that you write yourself.

In each of these fields, you can also use the following special characters:

Asterisk (*): represents all possible values, such as the month field if it is an asterisk, the command action is executed monthly after the constraints of other fields are met.

Comma (,): You can specify a list range with a comma-separated value, for example, "1,2,5,7,8,9"

Middle Bar (-): An integer range can be represented by a middle bar between integers, such as "2-6" for "2,3,4,5,6"

Forward slash (/): You can specify the interval frequency of the time with a forward slash, such as "0-23/2", which is performed every two hours. A forward slash can be used with asterisks, such as */10, if used in the minute field, which means that it executes every 10 minutes.

Crontab timed execution of log records in/var/spool/mail/root to view logging

Vi/var/spool/mail/root

4. mysql Recovery

Gunzip < amun-2018-07-25-17:06.sql.gz | Mysql-uroot-pamunlinux Amun

CentOS 7 MySQL5.6 Sub-database-Fully prepared-shell script