MYSQL BAKUP USING SHELLS SCRIPTS

Kindly find the MYSQL shells scripts in given below.

-------------------------------------------------------------------------------------------------------

#! /bin/bash

# You are free to modify and distribute this code,
# so long as you keep my name and URL in it.

# your MySQL server's name
SERVER=10.0.0.125

# directory to backup to
BACKDIR=/root/mysqlbackup/

# date format that is appended to filename
DATE=`date +'%m-%d-%Y'`

#----------------------MySQL Settings--------------------#

# your MySQL server's location (IP address is best)
HOST="10.0.0.125"

# MySQL username
USER="root"

# MySQL password
PASS="xxxxx"

# List all of the MySQL databases that you want to backup in here, 
# each separated by a space
DBS="XXXX"

# set to 'y' if you want to backup all your databases. this will override
# the database selection above.
DUMPALL="n"


#----------------------Mail Settings--------------------#

# set to 'y' if you'd like to be emailed the backup (requires mutt)
MAIL=y

# email addresses to send backups to, separated by a space
EMAILS="suppport@magicalbinary.com"

SUBJECT="MySQL backup on $SERVER ($DATE)"

#----------------------FTP Settings--------------------#

# set "FTP=y" if you want to enable FTP backups
FTP=n

# FTP server settings; should be self-explanatory
FTPHOST="10.0.0.127"
FTPUSER="XXX"
FTPPASS="XXX"

# directory to backup to. if it doesn't exist, file will be uploaded to 
# first logged-in directory
FTPDIR="/home/value"

#-------------------Deletion Settings-------------------#

# delete old files?
DELETE=y

# how many days of backups do you want to keep?
DAYS=6

#----------------------End of Settings------------------#
# check of the backup directory exists
# if not, create it
if  [ -e $BACKDIR ]
then
    echo Backups directory already exists
else
    mkdir $BACKDIR
fi

if  [ $DUMPALL = "y" ]
then
    echo "Creating list of all your databases..."

    mysql -h$HOST -u$USER -p$PASS "dlr" "dlr"  > dbs_on_$SERVER.txt

    # redefine list of databases to be backed up
    DBS=`sed -e ':a;N;$!ba;s/\n/ /g' -e 's/Database //g' dbs_on_$SERVER.txt`
fi

echo "Backing up MySQL databases...$DBS"
read p1
for database in $DBS
do
    mysqldump -h$HOST -u$USER -p$PASS $database dlr > "$BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql"
    gzip -f -9 "$BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql"

# if you have the mail program 'mutt' installed on
done
# your server, this script will have mutt attach the backup
# and send it to the email addresses in $EMAILS

if  [ $MAIL = "y" ]
then
BODY="Your backup is ready, please check"
ATTACH=`for file in $BACKDIR/*$DATE.sql.gz; do echo -n "-a ${file} ";  done`

    echo "$BODY" | mutt -s "$SUBJECT" $ATTACH $EMAILS
        
    echo -e "Your backup has been emailed to you! \n"
fi

if  [ $FTP = "y" ]
then
echo "Initiating FTP connection..."
cd $BACKDIR
ATTACH=`for file in *$DATE.sql.gz; do echo -n -e "put ${file}\n"; done`

    ftp -nv <<EOF
    open $FTPHOST
    user $FTPUSER $FTPPASS
    cd $FTPDIR
    $ATTACH
    quit
EOF
echo -e  "FTP transfer complete! \n"
fi

if  [ $DELETE = "y" ]
then
    find $BACKDIR -name "*.sql.gz" -mtime $DAYS -exec rm {} \;

    if  [ $DAYS = "1" ]
    then
        echo "Yesterday's backup has been deleted."
    else
        echo "The backup from $DAYS days ago has been deleted."
    fi
fi

echo Your backup is complete!

using shell script to automate install VPS linux

Vì tính chất công việc, nên đôi khi mình phải setup khá nhiều VPS để dùng. Đặc biệt là các VPS linux, tuy nhiên chỉ cần setup Apache + PHP + mysql + phpMyAdmin là đủ cho 1 cuộc tình 
Vì phải lặp đi lặp lại 1 mớ lệnh, mà mình thì cũng ko ưa terminal cho lắm  nên đành viết cái bash để install. Tuy nhiên đời nó chả như mơ!

Chuẩn bị

VPS linux, mình toàn dùng CentOS 6 64bit
Bitvise tunnelier hoặc putty gì đấy tùy bạn

Viết bash

Đầu tiên cần liệt kê ra các câu lệnh sẽ phải dùng

yum update
echo y | yum install mysql mysql-devel mysql-server -y
/etc/init.d/mysqld start
/usr/bin/mysql_secure_installation
/etc/init.d/mysqld restart
echo y | yum install php-mbstring php-gd httpd-devel apxs php-mcrypt php-mysql pdo-mysql php-sqlite php-mcrypt php-soap
echo y | yum install php
service httpd restart
wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm -ivh epel-release-6-8.noarch.rpm
echo y | yum install php-mcrypt

cd //var/www/html
wget https://files.phpmyadmin.net/phpMyAdmin/4.0.10.15/phpMyAdmin-4.0.10.15-all-languages.zip
echo y | yum install unzip
unzip phpMyAdmin-4.0.10.15-all-languages.zip
rm -rf phpMyAdmin-4.0.10.15-all-languages.zip

Để viết 1 file bash cực kì đơn giản, save mớ lệnh đó vào notepad rồi lưu dưới đuôi .sh là xong.

Khoan đã

Nếu bạn dùng trên linux thì có một chương trình/lệnh giúp convert line-ending từ Windows -> Linux đó là dos2unix, bạn chỉ cần dos2unix bash_script.sh là được, tuy nhiên mình dùng Windows. Vấn đề gặp phải đó là kí tự cuối mỗi dòng.

Với windows, với mỗi cuối dòng thì windows sẽ lưu thêm /r, vô tình làm cho các câu lệnh có cái thực hiện được, có cái không được!

Vì thế mình phải chuyển cách lưu lại, ở đây mình dùng Sublime Text, bạn chỉnh như sau:

Sau đó chịu khó xóa xuống dòng, và enter lại hoặc dùng cách nào đó để chuyển lại cái Line Endings thành Unix là xong 
Để kiểm tra đã thành công chưa, khi chạy file bash bạn thêm -x vào là được.

dùng sublime mình set "default_line_ending" là "unix" thì không phải lo lắng cái ngắt dòng nữa.Ngoài ra mình ưa thích dùng editorconfig cho tất cả các project

Cách dùng

Chắc ai cũng biết nhỉ  nhưng cũng nói sơ qua
B1: Upload file bash này lên vps
B2: Dùng SSH Shell cd đến nơi chứa file
B3: nhập lệnh

bash setup.sh

hoặc để kiểm tra xem có còn /r không thì gõ

bash -x setup.sh

Xong!
Mình note lại để sau này có quên thì có cái mà dùng 

nguồn: http://kipalog.com/posts/Viet-bash-install-VPS

Scripts for backup website

#######################################################################
## AURELIEN HUSSON ##
## Script backup website and Database ##
## Info : ##
## Exemple : website find in /home/www/website/public_html/ ##
## backup in /home/www/website/backup/ ##
## The script in /home/www/website/backup-site.sh ##
#######################################################################
#######################################################################
## AUTOMATIC CRON ##
## For automatiq backup ex : eveyday’s 00:01 AM ##
## 01 00 * * * /home/linuxtricks/backup-script.sh ##
#######################################################################

#! /bin/bash

echo We define the system variables

path=`dirname $0`
pathsav=”$path/backup”

site=”www.website.com”
wwwpath=”/home/website/”
wwwfolder=”public_html”

dbhost=”localhost”
dbuser=”user”
dbpasswd=”password”
dbname=”site”

dbrestohost=”$dbhost”
dbrestouser=”$dbuser”
dbrestopasswd=”$dbpasswd”
dbrestoname=”siterestore”

maxjourssauvegarde=”30″

echo Defined variables.

echo “”

echo Checking Programs
progtar=$(which tar)
progtar=$(echo $?)
progbzip2=$(which bzip2)
progbzip2=$(echo $?)
progbzcat=$(which bzcat)
progbzcat=$(echo $?)
progmysqdump=$(which mysqldump)
progmysqdump=$(echo $?)
progmysql=$(which mysql)
progmysql=$(echo $?)

if [ $progtar -eq 0 ] && [ $progbzip2 -eq 0 ] && [ $progbzcat -eq 0 ] && [ $progmysqdump -eq 0 ] && [ $progmysql -eq 0 ]
then
echo All programs are available on the server
else
echo It lacks a program to run the backup script. Check tar, bzip2, bzcat, mysqldump and mysql
exit 1
fi

echo Checking Completed programs

echo “”

echo Installing the script
cd “$path”
mkdir “$pathsav” 2>/dev/null
echo Script initialising

echo “”

echo The backup date we define
dte=$(date +%Y-%m-%d–%H-%M-%S)

echo Backup date defined on $dte

echo “”

echo We launch Backup
echo “”
echo We launch archive website $site
cd “$wwwpath”
tar cf “$pathsav/$site-$dte.tar” “$wwwfolder”
cd “$path”

if [ $? -eq 0 ]
then
echo Archiving website ends : “$pathsav/$site-$dte.tar”
else
echo Failed archive website
exit 2
fi

echo “”

echo It will launch the backup database

mysqldump -u “$dbuser” -p”$dbpasswd” -h “$dbhost” “$dbname” > “$pathsav/$site-$dte.sql”

if [ $? -eq 0 ]
then
echo Backed up database.
else
echo Failed to backup the database
exit 3
fi

echo “”

echo We compress the archive website $site

bzip2 -z “$pathsav/$site-$dte.tar”

if [ $? -eq 0 ]
then
echo Compression archive of the finished website : “$pathsav/$site-$dte.tar.bz2”
else
echo Failed compression archive website
exit 4
fi

echo “”

echo We compress the database.

bzip2 -z “$pathsav/$site-$dte.sql”

if [ $? -eq 0 ]
then
echo Database compressed .
else
echo Failed compressing the database
exit 5
fi

echo “”

echo BACKUP ENDED

echo ———————

echo PURGE OLD BACKUP

find “$pathsav” -name “$site*” -mtime +”$maxjourssauvegarde” -exec echo ‘{}’ \;
find “$pathsav” -name “$site*” -mtime +”$maxjourssauvegarde” -exec rm ‘{}’ \;

if [ $? -eq 0 ]
then
echo Old backups deleted.
else
echo Failed to delete old backups.
exit 6
fi

echo ——————–

echo CHECKING THE BACKUP

echo We check archive “$pathsav/$site-$dte.tar.bz2”
tar tf “$pathsav/$site-$dte.tar.bz2” 2>/dev/null 1>/dev/null

if [ $? -eq 0 ]
then
echo Backup Site Files : OK
else
echo An error occurred during restore. The backup is probably damaged.
fi

echo “”

echo We check the backup database : “$pathsav/$site-$dte.sql.bz2”

bzcat “$pathsav/$site-$dte.sql.bz2” | mysql -u “$dbrestouser” -p”$dbrestopasswd” -h “$dbrestohost” $dbrestoname

if [ $? -eq 0 ]
then
echo Backing up the database : OK
else
echo An error occurred during restore. The backup is probably damaged.
fi

echo TEST COMPLETED RESTORATION