MySQL local backup and dual-machine mutual backup scripts

Modify the script to make the necessary configuration, and then execute it as the root user.

1. First when performing a remote backup.

2. Local Backup

3. Remote backup without parameters < attention 1>

Note: You need to add users to a MySQL user on another host ...

Where to configure:

# define host and mysql password
　　 REMOTE_HOST="" #远程主机名或IP
　　 REMOTE_PASSWORD="" #远程主机的mysql中的root@本地主机 密码.
　　 # define database path
　　 DB_PATH="/var/lib/mysql" #本地mysql数据库路径
　　 LOCAL_PASSWORD="" #本地mysql root用户密码
　　 DB_BACKUP_PATH="/root/db_bk" #本地备份时数据保存的路径
　　 NO_BACKUP_DB="" 设置本地数据库中不想备份的数据库名,用空格隔开

Direct download

#!/bin/sh
# mysqldump_all.sh
# This scripts are used to backup MySQL database bettwen tow MySQL server!
# define host and MySQL password
Remote_host= ""
Remote_password= ""
# define database path
Db_path= "/var/lib/mysql"
Local_password= ""
Db_backup_path= "/ROOT/DB_BK"
# define which database no back up, write DB anme ESCAP whit space!
# example;
# no_backup_db= "Yns test Ps_note"
# This said the If DB name is ' Yns ' or ' test ' or ' ps_note ', then not backup them!
No_backup_db= ""
# Get the database name and process one by one
echo "Start to Backup ...";
#ls/var/lib/mysql >/tmp/mydb.all
If ["$"!= "help"]; Then
For DB in ' LS $DB _path '; Todo
if_backup= "1"
If [-D $DB _path/$db]; Then
For nobackdb in $NO _backup_db; Todo
if (test "$db" = "$noBackDB"); Then
#echo-N "...";
If_backup= "";
Fi
Done
If [$if _backup]; Then
Case "$" in
First
Echo-n "Crate DB [$db] on $REMOTE _host first!"
result= ' mysqladmin-h $REMOTE _host--password= $REMOTE _password Create $db '
if [! $result]; Then
echo "... Done! ";
Else
echo "... fialed! Check your configure plz! ";
echo "Remote host said: $result";
Exit
Fi
;;
Local
# Back up to localhost dir.
if [!-D $DB _backup_path]; Then
mkdir $DB _backup_path
Fi
Echo-n "[$db] .... Backup to Local$db_backup_path with gzip;
If [e "$DB _backup_path/$db. gz"]; Then
MV $DB _backup_path/$db. GZ $DB _backup_path/$db 1.gz;
Fi
Mysqldump--password=walte#ming $db | gzip > $DB _backup_path/$db. gz
echo "... [OK] "
;;
*)
# do common backup!
Echo-n "[$db] ... Do backup! ====>> $REMOTE _host/$db ";
Mysqldump--add-drop-table $db--password= $LOCAL _password | Mysql-h $REMOTE _host--password= $REMOTE _password $db
echo ".... [OK] ";
Esac
Else
echo "[$db]";
echo "***this database set to not backup!... Operation canceled! ";
Fi
Else
echo "[$db]";
echo "***warning! Not a databbase. Backup canceled*** ";
Fi
Done
Else
echo "Usage:mysqldupm_all.sh [Firs|local|help]";
echo "Some question ask for href=" mailto:walteyh@sohu.com "; >walteyh@sohu.com ";
Fi
# Ended All backup opretion!
echo "";
echo "Note:if There is come wrong message white MySQL, you should check yourconfigure first!";
Exit