MySQL Full script upgrade, support multiple hosts, multiple databases

Source: Internet
Author: User
Tags mysql backup

This foot is an upgrade to the script written in the morning (previous address http://www.sql8.net/a/course/shujuku/Mysql/2014/0815/30340.html)

For colleagues multiple databases and data accounts, passwords, the host may not be the same situation overwrite

After rewriting, two scripts are required to complete the collaboration.

One for batch execution script batch.sh

Two for data backup script databak.sh


Batch execution Script batch.sh**************************************


SH databak.sh 192.168.1.1 database1 bak1 12322
SH databak.sh localhost database2,database3,database4 bak 123333


*********************************************************************************************


Data backup Script databak.sh**************************************

#!/bin/bash
#mysql全备脚本, this script can back up multiple databases,
#为每个数据建立单独的文件夹进行备份
#对于不同帐号, different libraries, not hosts
#调用参数格式 host Database account password
#调用方法
#同一帐号多个数据库可以用, separating
#sh databak.sh host Database account password
#sh databak.sh localhost youoil bak 123




#author: Yxy
#website: Www.sql8.net
#mail: [Email protected]
#created: 2014-8-14


#Mysql dabase Information
#主机
Db_host=$1
#databases
#需要备份的数据库, if there are multiple databases, use, separate
Databaselist=$2
#数据库帐号
Db_user=$3
#数据库密码
Db_passwd=$4
#mysql安装目录
Mysql= "/opt/lampp/bin/mysql"
#mysql命令目录
mysqldump= "/opt/lampp/bin/mysqldump"


If ["$db _host" = = ""] | | ["$databaseList" = = ""] | | ["$db _user" = = ""] | | ["$db _passwd" = = ""]; Then
echo "Insufficient arguments"
Exit 1
Fi
echo "$db _host" "$databaseList" "$db _user" "$db _passwd"




#Path Information
#存放备份文件的路径
backup_db= "/opt/backup/database"
logfile= $BACKUP _db "/bak.log"


#Time Information
#时间
Time= ' date + '%y-%m-%d-%h-%m-%s '
day= ' date + '%d '
Month= ' date + '%y-%m '
weekday= ' date + '%u '






#Path Enable Write
if [!-W "$BACKUP _db"]; Then
Chmod-r $BACKUP _db
Fi


#echo "**********************************host info****************************************"
#echo "Db_host:" $db _host
#echo "Db_user:" $db _user
#echo "DB_PASSWD:" $db _passwd
#echo "MYSQL:" $MYSQL
#echo "MYSQLDUMP:" $MYSQLDUMP
#echo "DatabaseName:" $databaseName
#echo "backup_db:" $BACKUP _db
#echo "**********************************************************************************"




#Mysql Backup
Date= ' Date +%y%m%d '
begin= ' date + '%y-%m-%d%h:%m:%s '
#echo "Start Backup database:" $databaseName "" $Begin


For databaseName in ' Echo ' $databaseList | Sed ' s/,/\n/g '
Do
Echo $databaseName


#fileName
#生成备份文件名
Filename= $databaseName "-" $time ". sql"
#生成备份文件完整路径
Backup_dbpath= $BACKUP _db "/" $databaseName




#echo "***********************************database info***********************************************"
#echo "Backup_dbpath:" $BACKUP _dbpath
#echo "FileName:" $fileName
#echo "**********************************************************************************"





if [!-d "$BACKUP _dbpath"]; Then
mkdir "$BACKUP _dbpath"
Fi
#备份mysql
$MYSQLDUMP-u $db _user-p$db_passwd-h $db _host $databaseName > $BACKUP _dbpath/$fileName
#打包. sql file
CD $BACKUP _dbpath && tar-czf $fileName. tar.gz $fileName && rm-rf $fileName && chmod go-rwx $file Name.tar.gz


last= ' date + '%y-%m-%d%h:%m:%s '
#echo "End Backup Database:" $databaseName "$Last
#输出日志
echo Start: $Begin end: $Last $fileName succ >> $LogFile


Done

Crontab setting **********************************************

Set the relevant backup rules as needed, some of the rules here


#每天1点备份

* 1 * * * cd/opt/backup/database &&/1.sh



#每周一1点备份

* 1 * * 1 cd/opt/backup/database &&/1.sh



#每2小时备份

* */2 * * * cd/opt/backup/database &&/1.sh



#每2天备份

* * */2 * * cd/opt/backup/database &&/1.sh


#每个月5号1点20分备份

1 5 * * Cd/opt/backup/database &&/1.sh



For more Contrab rules please see

Http://www.sql8.net/a/course/caozuoxitong/liunx/2014/0815/30328.html


This article is from "Black Hawk" blog, please make sure to keep this source http://1657030.blog.51cto.com/1647030/1540680

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.