MySQL local backup and dual-machine mutual backup scripts

Source: Internet
Author: User
Tags mysql database backup

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

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.