Step :
I. Make sure you can SSH without password on your server, see this
II. Test Rsync
If first step done, you can test copy data from server with rsync.
$ rsync -avz -e ssh remoteuser@remotehost:/remote/dir /this/dir/
III. Backup Mysql Server
1. Create New Directoery backup Mysql on Server, etc : /home/user/backupmysql seting permision to 777
2. Use this script my_sql.sh on Server for backup mysql. create on /home/user/
Sample Script :
#!/bin/bash
# Purpose: Backup mysql
# Author: Xinix-Technology-Jafar/Habib
DEST="/home/user/backupmysql/mysql_backup" #address for temporary folder mysql
# set mysql login info
MUSER="root" # Username
MPASS='PASSWORD' # Password
MHOST="127.0.0.1" # Server Name
# PATH VARIABLES
MK=/bin/mkdir; # Location of the mk bin
RM=/bin/rm; # Location of the rm bin
# guess binary names
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
# CREATE MYSQL BACKUP
# Remove existing backup dir - because we backup the files before onto our backup server, this is safe to do!
$RM -Rf $DEST
# Create new backup dir
$MK $DEST
# get all db names
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
FILE=${DEST}/${db}.sql
$MYSQLDUMP --single-transaction -u $MUSER -h $MHOST -p$MPASS $db > $FILE
done
3. Run Script
$ sh my_sql.sh
4. If work, script will :
- Backup all database on your MYSQL
- Copy backup data to temporary folder ( /mysql_backup )
*Note :* This script will delete temporary folder and create new temporary folder ( /mysql_backup )
IV. Run Script my_sql.sh & copy backup via SSH
1. Creat new backup folder on host & set permision 777, etc:
$ mkdir /home/user/mysqlserver
$ mkdir /home/user/mysqlserver/current
$ mkdir /home/user/mysqlserver/old
$ chmod 777 -R /home/user/mysqlserver
2. Create Script mysqlserver.sh on /home/user/mysqlserver. Script:
#!/bin/bash unset PATH # USER VARIABLES BACKUPDIR=/home/user/mysqlserver # Host folder to save backup file KEY=/home/user/.ssh/id_dsa # SSH key MYSQL_BACKUPSCRIPT=/home/user/my_sql.sh # Path to the remote mysql backup script PRODUCTION_USER=Userserver@Addressserver # The user and the address of the production server CPDATA=Userserver@Addressserver:/home/user/backup/mysql_backup/ # Folder on the backup server where the backups shall be located DAYS=60 # The number of days after which old backups will be deleted # PATH VARIABLES SH=/bin/sh # Location of the bash bin in the production server!!!! CP=/bin/cp; # Location of the cp bin FIND=/usr/bin/find; # Location of the find bin ECHO=/bin/echo; # Location of the echo bin MK=/bin/mkdir; # Location of the mk bin SSH=/usr/bin/ssh; # Location of the ssh bin DATE=/bin/date; # Location of the date bin RM=/bin/rm; # Location of the rm bin GREP=/bin/grep; # Location of the grep bin RSYNC=/usr/bin/rsync; # Location of the rsync bin TOUCH=/bin/touch; # Location of the touch bin CURRENT=$BACKUPDIR/current # Current File Backup OLD=$BACKUPDIR/old # Old File Backup # CREATING CURRENT DATE / TIME NOW=`$DATE '+%Y-%m'-%d_%H:%M` NOW=$OLD/$NOW $MK $NOW # CREATE REMOTE MYSQL BACKUP BY RUNNING THE REMOTE BACKUP SCRIPT $SSH -i $KEY $PRODUCTION_USER "$SH $MYSQL_BACKUPSCRIPT" # RUN RSYNC INTO CURRENT $RSYNC -avz -e $SSH $CPDATA $CURRENT #UPDATE THE MTIME TO REFELCT THE SNAPSHOT TIME $TOUCH $BACKUPDIR/currentmysqlD # MAKE HARDLINK COPY $CP -R $CURRENT/* $NOW # REMOVE OLD BACKUPS # untuk menghapus secara otomatis file backup yang lama for FILE in "$( $FIND $OLD -maxdepth 1 -type d -mtime +$DAYS )" do # $RM -Rf $FILE # $ECHO $FILE done
4. Run Script
$ sh mysqlserver.sh
5. If work, Script will execute :
- Order server to run script backup mysql ( my_sql.sh )
- Copy data on temporary folder @ server ( /mysql_backup ) using rsync to curent folder on host ( /home/user/mysqlserver/current )
- Create new folder with date format on old folder wich inside is all data on current folder
thx to : - Stephan Jau
- troy.jdmz
No comments:
Post a Comment