Backup MySQL tabel per tabel dengan rotasi otomasi

Berikut ini adalah MySQL backup script yang dapat mengambil tabel dengan backup tabel (file cadangan individu masing-masing tabel database masing-masing) dalam format terkompresi. Hal ini juga memberikan rotasi otomatis dari file backup yang lama. Script cadangan menangani tabel InnoDB dan MyISAM secara terpisah.

Anda harus mengatur variabel-variabel berikut sebelum menjalankan skrip backup.

DB_User

Database user yang memiliki akses ke semua database dan tabel. Saya menggunakan “root” untuk penyebaran saya.

DB_PASS

Password dari pengguna di atas, diawali dengan “-p”. Misalnya jika password yang Rahasia, maka Anda harus menulis password sebagai “-pSecret”.

BAKUP_LOG

File untuk yang log cadangan akan ditulis. Ini harus dapat ditulis oleh user yang menjalankan script.

BASE_BAK_FLDR

Folder backup. Ini harus dapat ditulis oleh user yang menjalankan script.

RM_FLDR_DAYS

Contoh

Rotasi backup +30 artinya backup tiap 30 hari sekali. Script ditulis oleh Mohammed Salih/HowToForge.com

#!/bin/bash
# Database Backup script.
# Created By:    Mohammed Salih
#                 Senior System Administrator
#                Date: 21/06/2007
#
# Database credentials
DB_USER=root
#Please append password in the xxxxx section below, note that there is
# no space between -p and xxxxx
DB_PASS="-pxxxxxxx"
# Get list of Databases except the pid file
DBS_LIST=$(echo "show databases;"|mysql -u $DB_USER $DB_PASS -N)
# Log file
BAKUP_LOG=/backup/log/db-backup.log
# Backup Base directory
BASE_BAK_FLDR=/backup/db
# Backup rotation period.
RM_FLDR_DAYS="+30"
# From here, only edit if you know what you are doing.
index=0
# Check if we can connect to the mysql server; otherwise die
if [ ! "$(id -u -n)" = "mysql" ]; then
        echo -e "Error:: $0 : Only user 'mysql' can run this script"
        exit 100
fi
PING=$(mysqladmin ping -u $DB_USER $DB_PASS 2>/dev/null)
if [ "$PING" != "mysqld is alive" ]; then
        echo "Error:: Unable to connected to MySQL Server, exiting !!"
        exit 101
fi
# Backup process starts here.
# Flush logs prior to the backup.
mysql -u $DB_USER $DB_PASS -e "FLUSH LOGS"
# Loop through the DB list and create table level backup,
# applying appropriate option for MyISAM and InnoDB tables.
for DB in $DBS_LIST; do
    DB_BKP_FLDR=$BASE_BAK_FLDR/$(date +%d-%m-%Y)/$DB
    [ ! -d $DB_BKP_FLDR ]  && mkdir -p $DB_BKP_FLDR
    # Get the schema of database with the stored procedures.
    # This will be the first file in the database backup folder
    mysqldump -u $DB_USER $DB_PASS -R -d --single-transaction $DB | \
            gzip -c > $DB_BKP_FLDR/000-DB_SCHEMA.sql.gz
    index=0
    #Get the tables and its type. Store it in an array.
    table_types=($(mysql -u $DB_USER $DB_PASS -e "show table status from $DB" | \
            awk '{ if ($2 == "MyISAM" || $2 == "InnoDB") print $1,$2}'))
    table_type_count=${#table_types[@]}
    # Loop through the tables and apply the mysqldump option according to the table type
    # The table specific SQL files will not contain any create info for the table schema.
    # It will be available in SCHEMA file
    while [ "$index" -lt "$table_type_count" ]; do
        START=$(date +%s)
        TYPE=${table_types[$index + 1]}
        table=${table_types[$index]}
        echo -en "$(date) : backup $DB : $table : $TYPE "
        if [ "$TYPE" = "MyISAM" ]; then
            DUMP_OPT="-u $DB_USER $DB_PASS $DB --no-create-info --tables "
        else
            DUMP_OPT="-u $DB_USER $DB_PASS $DB --no-create-info --single-transaction --tables"
        fi
        mysqldump  $DUMP_OPT $table |gzip -c > $DB_BKP_FLDR/$table.sql.gz
        index=$(($index + 2))
        echo -e " - Total time : $(($(date +%s) - $START))\n"
    done
done
# Rotating old backup. according to the 'RM_FLDR_DAYS'
if [ ! -z "$RM_FLDR_DAYS" ]; then
    echo -en "$(date) : removing folder : "
    find $BASE_BAK_FLDR/ -maxdepth 1 -mtime $RM_FLDR_DAYS -type d -exec rm -rf {} \;
    echo
fi

Lokasi backup di atur pada: $BKP_BASE_FLDR/01-01-2007/bigdb

Belajar Bash: for Loop

The purpose of this exercise is to write a number of for loops to be able to understand how to implement them effectively.

1. Create a script called defense.sh in /opt/scripts
The purpose of the script is to configure the firewall to drop known zombie networks.

#!/bin/bash
IP=/opt/scripts/banned

for i in $(awk '{print}' < "$IP" )
do
echo $i
iptables -A INPUT -p tcp -s $i -j DROP
done
exit 0

The script takes a list of IP Addresses in a file and uses awk to print each IP from the file into the variable $i. Then an iptables command employs the “$i” variable to drop each of the IP Addresses on the INPUT chain. The “-p tcp” limits the drop to TCP protocol and the “-s” indicates the source. The IP is dropped with the jump “-j” to DROP. Continue reading Belajar Bash: for Loop

Belajar Bash: Until Loop

The until loop will continue until a specific requirement is met or  as long as the exit status is NOT zero.  This is the opposite of the while loop.

In the example below you can see that the variable “i” starts with 0 and will loop until it equals 100.  It is incremented by one each loop.  When it reaches 100 it will deliver the zero as an indication of success and the loop will stop. Continue reading Belajar Bash: Until Loop