Backup ข้อมูลพวก .sql มันมีแบบ อัตโนมัติไหมครับ

[kke]

@icez

ผมใช้ mysqldump ไม่เคยเจอปัญหานี้นะครับ db เพี้ยน เละแค่ไหนมันก็ dump ได้ ใส่กลับได้ถูกตลอด

เจอกับไฟล์ backup ของ DA ครับ backup/restore ด้วย DA

ไฟล์ sql ที่ backup ไว้ภาษาไทยมันเพี้ยนไปบางตัว 95% ไม่มีปัญหา แต่บางตัวอักษรกลายเป็น ?? ไปตั้งแต่ตอน backup ก็ยังงงอยู่ว่าทำไม

ค่าที่เก็บใน sql มันประมาณนี้

“?สินค้ารายการที่ 1 กล่อมถนอมอาหาร?? ?
…”

คือตัวอักษรบางส่วนกลายเป็น ? ไปซะงั้น โดยส่วนใหญ่ก็เป็นภาษาไทยถูกต้อง

ที่เคยเจอมาคือ charset ผิด dump ออกมาแล้วเละไปเลย แบบนี้ยังเอามา convert ให้ charset ถูกแล้วใช้งานได้

[icez]

function backup ของ da มันไม่ได้ใช้ mysqldump น่ะสิครับ

[kke]

@icez

function backup ของ da มันไม่ได้ใช้ mysqldump น่ะสิครับ

แล้วมันใช้อะไรเหรอไอซ์

ก็เห็นมันมีไฟล์ .sql ออกมา

[icez]

gen เองไงครับ -*-’ เหมือนกะ phpmyadmin ก็ gen เอง ไม่ได้ใช้ mysqldump ก็ออกมาเป็น .sql ได้เหมือนกันแหละ

[kke]

@icez

gen เองไงครับ -*-’ เหมือนกะ phpmyadmin ก็ gen เอง ไม่ได้ใช้ mysqldump ก็ออกมาเป็น .sql ได้เหมือนกันแหละ

ยืนยันว่า admin_backup ของ DirectAdmin เรียก mysqldump 100% ครับ

เวลา backup อยู่ถ้า top ดูจะมี process mysqldump สลับกับ gzip ไล่ไปทีละ user

โดยเฉพาะข้อมูลจากเว็บ DA น่าจะยืนยันได้

Version 1.36.1 | Directadmin Docs

Version 1.33.6 | Directadmin Docs

[spinner_j]

ความรู้แน่เอี้ยดครับ ขอบคุณมากครับ ถกกันได้มันส์ดี กระทู้เดียวเล่นเอามึนไปเลย

ผมไม่ได้เทพแต่ขอ Share ความรู้จากประสบการณ์ที่เคยใช้ล่ะกันนะครับ เผื่อเพื่อนๆ พี่ๆ น้องๆ ท่านไหนสนใจวิธีนี้น่าจะเอาไปประยุกต์ได้มากอยู่ครับ

ผมใช้ตัวนี้ครับ ใช้มานานแล้ว 2-3 ปีได้ ไม่เคยมีปัญหา restore ไม่ได้ และไม่เคยมีปัญหากับภาษาไทยครับ

ทุกอย่าง Ok สำหรับผม แต่แนะนำให้ผู้ที่จะนำไปใช้ควรทดสอบจนตัวเองมั่นใจอีกครั้งนะครับ

AutoMySQLBackup

จะเข้าไป Load หรือว่าเอา script ไปเลยก็ได้ครับ

ใช้ cron ตามเวลาที่อยาก Backup ครับ มันจะ Backup ให้เราเองทุกวันตามเวลาที่กำหนด แยก folder เป็น

daily weekly monthly เลยครับ ทุกครั้งที่ Backup มันจัด File เข้า Folder ให้ไม่ทับ File เดิม

แถมแยก Folder ใน daily weekly monthly เป็นชื่อ DB ของเราด้วยครับ

ตัวอย่างการ Backup ทุกเวลา ตี 2 กับอีก 19 นาที ชื่อ File จะเป็นชื่อ DB_วันที่_เวลา_วัน.sql.gz เลยครับ

ใน Daily (จะไม่มี Monday เพราะว่าผมตั้งวันแรกของอาทิตย์เป็นวันจันทร์ครับ)

-rw-r–r-- 1 root wheel 107698436 Aug 21 02:23 thspin_forum_v3_2010-08-21_02h19m.Saturday.sql.gz

-rw-r–r-- 1 root wheel 26196805 Aug 22 02:21 thspin_forum_v3_2010-08-22_02h19m.Sunday.sql.gz

-rw-r–r-- 1 root wheel 29660807 Aug 24 02:21 thspin_forum_v3_2010-08-24_02h19m.Tuesday.sql.gz

-rw-r–r-- 1 root wheel 31316449 Aug 25 02:21 thspin_forum_v3_2010-08-25_02h19m.Wednesday.sql.gz

-rw-r–r-- 1 root wheel 32691484 Aug 26 02:21 thspin_forum_v3_2010-08-26_02h19m.Thursday.sql.gz

-rw-r–r-- 1 root wheel 34003713 Aug 27 02:21 thspin_forum_v3_2010-08-27_02h19m.Friday.sql.gz

ใน Weekly ก็จะเก็บ thspin_forum_v3_2010-08-23_02h19m.Monday.sql.gz ครับ เก็บทุกๆ Monday ไม่ทับกันและรู้สึกว่าไม่ลบทิ้งนะครับ หรือครบเดือนแล้วลบผมจำไม่ได้

ใน Monthly อันนี้จำไม่ได้ น่าจะเก็บทุกวันสุดท้ายของแต่ละเดือนชื่อ File ก็คล้ายๆ กันครับ อันนี้น่าจะเก็บทุกๆ สิ้นเดือนแบบไม่ลบทิ้งเช่นกันครับ

ทดสอบแล้วถูกใจหรือไม่ก็แล้วแต่ความชอบหรืองานของแต่ละคนนะครับ

ความเสี่ยงในการนำไปใช้ขึ้นอยู่กับการทดสอบจนมั่นใจแล้วของคุณเองเท่านั้นครับ ^^

แค่สนุกในการทำเครื่องและอยากแลกเปลี่ยนประสบการณ์ครับ ไม่ได้ทำเป็นอาชีพ ^^

ผิดพลาดอย่างไร พี่ๆ ชี้แนะได้เต็มที่ครับ น้อมรับเป็นความรู้ต่อไปครับ

[hr]

ปรับแต่งคร่าวๆ

Username to access the MySQL server e.g. dbuser

User mysql ที่สามารถเข้าถึง DB ที่เราจะ Backup ได้ ถ้าต้องการ Backup ทุก Database ก็ต้องเป็น User ที่เข้าได้ทุก DB

USERNAME=dbuser

Username to access the MySQL server e.g. password

Password ของ User ด้านบน

PASSWORD=password

Host name (or IP address) of MySQL server e.g localhost

Host name ถ้า Backup เครื่องตัวเองโดยใส่ localhost ถ้า Backup เครื่องอื่นก็ใส่ IP ครับ

DBHOST=localhost

List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3”

เลือก DB ที่จะ Backup ได้ด้วยครับ ไม่ต้องเลือกหมดก็ได้

ถ้าอยากให้ Backup ทั้งหมดใส่ =“all” ครับ ด้านท้าย Script สามารถเลือก DB ที่ไม่ต้องการ Backup ได้ด้วยครับ เหมาะสำหรับอยาก Backup เกือบหมดเว้นแค่ไม่กี่ DB

DBNAMES=“DB1 DB2 DB3”

Backup directory location e.g /backups

ตั้งตามที่เราอยากให้ Backup ของเราไปเก็บที่ไหนครับ

BACKUPDIR="/backups"

#!/bin/bash

#

# MySQL Backup Script

# VER. 2.5 - http://sourceforge.net/projects/automysqlbackup/

# Copyright (c) 2002-2003 [email]wipe_out@lycos.co.uk[/email]

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

# GNU General Public License for more details.

# 

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

#

#=====================================================================

#=====================================================================

# Set the following variables to your system needs

# (Detailed instructions below variables)

#=====================================================================



# Username to access the MySQL server e.g. dbuser

USERNAME=dbuser



# Username to access the MySQL server e.g. password

PASSWORD=password



# Host name (or IP address) of MySQL server e.g localhost

DBHOST=localhost



# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"

DBNAMES="DB1 DB2 DB3"



# Backup directory location e.g /backups

BACKUPDIR="/backups"



# Mail setup

# What would you like to be mailed to you?

# - log   : send only log file

# - files : send log file and sql files as attachments (see docs)

# - stdout : will simply output the log to the screen if run manually.

# - quiet : Only send logs if an error occurs to the MAILADDR.

MAILCONTENT="stdout"



# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])

MAXATTSIZE="4000"



# Email Address to send mail to? (user@domain.com)

MAILADDR="user@domain.com"





# ============================================================

# === ADVANCED OPTIONS ( Read the doc's below for details )===

#=============================================================



# List of DBBNAMES for Monthly Backups.

MDBNAMES="mysql $DBNAMES"



# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)

DBEXCLUDE=""



# Include CREATE DATABASE in backup?

CREATE_DATABASE=yes



# Separate backup directory and file for each DB? (yes or no)

SEPDIR=yes



# Which day do you want weekly backups? (1 to 7 where 1 is Monday)

DOWEEKLY=6



# Choose Compression type. (gzip or bzip2)

COMP=gzip



# Compress communications between backup server and MySQL server?

COMMCOMP=no



# Additionally keep a copy of the most recent backup in a seperate directory.

LATEST=no



#  The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)

MAX_ALLOWED_PACKET=



#  For connections to localhost. Sometimes the Unix socket file must be specified.

SOCKET=



# Command to run before backups (uncomment to use)

#PREBACKUP="/etc/mysql-backup-pre"



# Command run after backups (uncomment to use)

#POSTBACKUP="/etc/mysql-backup-post"



#=====================================================================

# Options documantation

#=====================================================================

# Set USERNAME and PASSWORD of a user that has at least SELECT permission

# to ALL databases.

#

# Set the DBHOST option to the server you wish to backup, leave the

# default to backup "this server".(to backup multiple servers make

# copies of this file and set the options for that server)

#

# Put in the list of DBNAMES(Databases)to be backed up. If you would like

# to backup ALL DBs on the server set DBNAMES="all".(if set to "all" then

# any new DBs will automatically be backed up without needing to modify

# this backup script when a new DB is created).

#

# If the DB you want to backup has a space in the name replace the space

# with a % e.g. "data base" will become "data%base"

# NOTE: Spaces in DB names may not work correctly when SEPDIR=no.

#

# You can change the backup storage location from /backups to anything

# you like by using the BACKUPDIR setting..

#

# The MAILCONTENT and MAILADDR options and pretty self explanitory, use

# these to have the backup log mailed to you at any email address or multiple

# email addresses in a space seperated list.

# (If you set mail content to "log" you will require access to the "mail" program

# on your server. If you set this to "files" you will have to have mutt installed

# on your server. If you set it to "stdout" it will log to the screen if run from 

# the console or to the cron job owner if run through cron. If you set it to "quiet"

# logs will only be mailed if there are errors reported. )

#

# MAXATTSIZE sets the largest allowed email attachments total (all backup files) you

# want the script to send. This is the size before it is encoded to be sent as an email

# so if your mail server will allow a maximum mail size of 5MB I would suggest setting

# MAXATTSIZE to be 25% smaller than that so a setting of 4000 would probably be fine.

#

# Finally copy automysqlbackup.sh to anywhere on your server and make sure

# to set executable permission. You can also copy the script to

# /etc/cron.daily to have it execute automatically every night or simply

# place a symlink in /etc/cron.daily to the file if you wish to keep it 

# somwhere else.

# NOTE:On Debian copy the file with no extention for it to be run

# by cron e.g just name the file "automysqlbackup"

#

# Thats it..

#

#

# === Advanced options doc's ===

#

# The list of MDBNAMES is the DB's to be backed up only monthly. You should

# always include "mysql" in this list to backup your user/password

# information along with any other DBs that you only feel need to

# be backed up monthly. (if using a hosted server then you should

# probably remove "mysql" as your provider will be backing this up)

# NOTE: If DBNAMES="all" then MDBNAMES has no effect as all DBs will be backed

# up anyway.

#

# If you set DBNAMES="all" you can configure the option DBEXCLUDE. Other

# wise this option will not be used.

# This option can be used if you want to backup all dbs, but you want 

# exclude some of them. (eg. a db is to big).

#

# Set CREATE_DATABASE to "yes" (the default) if you want your SQL-Dump to create

# a database with the same name as the original database when restoring.

# Saying "no" here will allow your to specify the database name you want to

# restore your dump into, making a copy of the database by using the dump

# created with automysqlbackup.

# NOTE: Not used if SEPDIR=no

#

# The SEPDIR option allows you to choose to have all DBs backed up to

# a single file (fast restore of entire server in case of crash) or to

# seperate directories for each DB (each DB can be restored seperately

# in case of single DB corruption or loss).

#

# To set the day of the week that you would like the weekly backup to happen

# set the DOWEEKLY setting, this can be a value from 1 to 7 where 1 is Monday,

# The default is 6 which means that weekly backups are done on a Saturday.

#

# COMP is used to choose the copmression used, options are gzip or bzip2.

# bzip2 will produce slightly smaller files but is more processor intensive so

# may take longer to complete.

#

# COMMCOMP is used to enable or diable mysql client to server compression, so

# it is useful to save bandwidth when backing up a remote MySQL server over

# the network. 

#

# LATEST is to store an additional copy of the latest backup to a standard

# location so it can be downloaded bt thrid party scripts.

#

# If the DB's being backed up make use of large BLOB fields then you may need

# to increase the MAX_ALLOWED_PACKET setting, for example 16MB..

#

# When connecting to localhost as the DB server (DBHOST=localhost) sometimes

# the system can have issues locating the socket file.. This can now be set

# using the SOCKET parameter.. An example may be SOCKET=/private/tmp/mysql.sock

#

# Use PREBACKUP and POSTBACKUP to specify Per and Post backup commands

# or scripts to perform tasks either before or after the backup process.

#

#

#=====================================================================

# Backup Rotation..

#=====================================================================

#

# Daily Backups are rotated weekly..

# Weekly Backups are run by default on Saturday Morning when

# cron.daily scripts are run...Can be changed with DOWEEKLY setting..

# Weekly Backups are rotated on a 5 week cycle..

# Monthly Backups are run on the 1st of the month..

# Monthly Backups are NOT rotated automatically...

# It may be a good idea to copy Monthly backups offline or to another

# server..

#

#=====================================================================

# Please Note!!

#=====================================================================

#

# I take no resposibility for any data loss or corruption when using

# this script..

# This script will not help in the event of a hard drive crash. If a 

# copy of the backup has not be stored offline or on another PC..

# You should copy your backups offline regularly for best protection.

#

# Happy backing up...

#

#=====================================================================

# Restoring

#=====================================================================

# Firstly you will need to uncompress the backup file.

# eg.

# gunzip file.gz (or bunzip2 file.bz2)

#

# Next you will need to use the mysql client to restore the DB from the

# sql file.

# eg.

# mysql --user=username --pass=password --host=dbserver database < /path/file.sql

# or

# mysql --user=username --pass=password --host=dbserver -e "source /path/file.sql" database

#

# NOTE: Make sure you use "<" and not ">" in the above command because

# you are piping the file.sql to mysql and not the other way around.

#

# Lets hope you never have to use this.. [img]http://www.thaihosttalk.com/public/style_emoticons/<#EMO_DIR#>/smile.gif[/img]

#

#=====================================================================

# Change Log

#=====================================================================

#

# VER 2.5 - (2006-01-15)

#		Added support for setting MAXIMUM_PACKET_SIZE and SOCKET parameters (suggested by Yvo van Doorn)

# VER 2.4 - (2006-01-23)

#    Fixed bug where weekly backups were not being rotated. (Fix by wolf02)

#    Added hour an min to backup filename for the case where backups are taken multiple

#    times in a day. NOTE This is not complete support for mutiple executions of the script

#    in a single day.

#    Added MAILCONTENT="quiet" option, see docs for details. (requested by snowsam)

#    Updated path statment for compatibility with OSX.

#    Added "LATEST" to additionally store the last backup to a standard location. (request by Grant29)

# VER 2.3 - (2005-11-07)

#    Better error handling and notification of errors (a long time coming)

#    Compression on Backup server to MySQL server communications. 

# VER 2.2 - (2004-12-05)

#    Changed from using depricated "-N" to "--skip-column-names".

#    Added ability to have compressed backup's emailed out. (code from Thomas Heiserowski)

#    Added maximum attachment size setting.

# VER 2.1 - (2004-11-04)

#    Fixed a bug in daily rotation when not using gzip compression. (Fix by Rob Rosenfeld)

# VER 2.0 - (2004-07-28)

#    Switched to using IO redirection instead of pipeing the output to the logfile.

#    Added choice of compression of backups being gzip of bzip2.

#    Switched to using functions to facilitate more functionality.

#    Added option of either gzip or bzip2 compression. 

# VER 1.10 - (2004-07-17)

#    Another fix for spaces in the paths (fix by Thomas von Eyben)

#    Fixed bug when using PREBACKUP and POSTBACKUP commands containing many arguments.

# VER 1.9 - (2004-05-25)

#    Small bug fix to handle spaces in LOGFILE path which contains spaces (reported by Thomas von Eyben)

#    Updated docs to mention that Log email can be sent to multiple email addresses.

# VER 1.8 - (2004-05-01)

#    Added option to make backups restorable to alternate database names

#    meaning that a copy of the database can be created (Based on patch by Rene Hoffmann)

#    Seperated options into standard and advanced.

#    Removed " from single file dump DBMANES because it caused an error but

#    this means that if DB's have spaces in the name they will not dump when SEPDIR=no.

#    Added -p option to mkdir commands to create multiple subdirs without error.

#    Added disk usage and location to the bottom of the backup report.

# VER 1.7 - (2004-04-22)

#    Fixed an issue where weelky backups would only work correctly if server

#    locale was set to English (issue reported by Tom Ingberg)

#    used "eval" for "rm" commands to try and resolve rotation issues.

#    Changed name of status log so multiple scripts can be run at the same time.

# VER 1.6 - (2004-03-14)

#   Added PREBACKUP and POSTBACKUP command functions. (patch by markpustjens)

#   Added support for backing up DB's with Spaces in the name.

#   (patch by markpustjens)

# VER 1.5 - (2004-02-24)

#   Added the ability to exclude DB's when the "all" option is used.

#   (Patch by kampftitan)

# VER 1.4 - (2004-02-02)

#   Project moved to Sourceforge.net

# VER 1.3 - (2003-09-25)

#   Added support for backing up "all" databases on the server without

#    having to list each one seperately in the configuration.

#   Added DB restore instructions.

# VER 1.2 - (2003-03-16)

#   Added server name to the backup log so logs from multiple servers

#   can be easily identified.

# VER 1.1 - (2003-03-13)

#   Small Bug fix in monthly report. (Thanks Stoyanski)

#   Added option to email log to any email address. (Inspired by Stoyanski)

#   Changed Standard file name to .sh extention.

#   Option are set using yes and no rather than 1 or 0.

# VER 1.0 - (2003-01-30)

#   Added the ability to have all databases backup to a single dump

#   file or seperate directory and file for each database.

#   Output is better for log keeping.

# VER 0.6 - (2003-01-22)

#   Bug fix for daily directory (Added in VER 0.5) rotation.

# VER 0.5 - (2003-01-20)

#   Added "daily" directory for daily backups for neatness (suggestion by Jason)

#   Added DBHOST option to allow backing up a remote server (Suggestion by Jason)

#   Added "--quote-names" option to mysqldump command.

#   Bug fix for handling the last and first of the year week rotation.

# VER 0.4 - (2002-11-06)

#   Added the abaility for the script to create its own directory structure.

# VER 0.3 - (2002-10-01)

#   Changed Naming of Weekly backups so they will show in order.

# VER 0.2 - (2002-09-27)

#   Corrected weekly rotation logic to handle weeks 0 - 10 

# VER 0.1 - (2002-09-21)

#   Initial Release

#

#=====================================================================

#=====================================================================

#=====================================================================

#

# Should not need to be modified from here down!!

#

#=====================================================================

#=====================================================================

#=====================================================================

PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin 

DATE=`date +%Y-%m-%d_%Hh%Mm`				# Datestamp e.g 2002-09-21

DOW=`date +%A`							# Day of the week e.g. Monday

DNOW=`date +%u`						# Day number of the week 1 to 7 where 1 represents Monday

DOM=`date +%d`							# Date of the Month e.g. 27

M=`date +%B`							# Month e.g January

W=`date +%V`							# Week Number e.g 37

VER=2.5									# Version Number

LOGFILE=$BACKUPDIR/$DBHOST-`date +%N`.log		# Logfile Name

LOGERR=$BACKUPDIR/ERRORS_$DBHOST-`date +%N`.log		# Logfile Name

BACKUPFILES=""

OPT="--quote-names --opt"			# OPT string for use with mysqldump ( see man mysqldump )



# Add --compress mysqldump option to $OPT

if [ "$COMMCOMP" = "yes" ];

	then

		OPT="$OPT --compress"

	fi



# Add --compress mysqldump option to $OPT

if [ "$MAX_ALLOWED_PACKET" ];

	then

		OPT="$OPT --max_allowed_packet=$MAX_ALLOWED_PACKET"

	fi



# Create required directories

if [ ! -e "$BACKUPDIR" ]		# Check Backup Directory exists.

	then

	mkdir -p "$BACKUPDIR"

fi



if [ ! -e "$BACKUPDIR/daily" ]		# Check Daily Directory exists.

	then

	mkdir -p "$BACKUPDIR/daily"

fi



if [ ! -e "$BACKUPDIR/weekly" ]		# Check Weekly Directory exists.

	then

	mkdir -p "$BACKUPDIR/weekly"

fi



if [ ! -e "$BACKUPDIR/monthly" ]	# Check Monthly Directory exists.

	then

	mkdir -p "$BACKUPDIR/monthly"

fi



if [ "$LATEST" = "yes" ]

then

	if [ ! -e "$BACKUPDIR/latest" ]	# Check Latest Directory exists.

	then

		mkdir -p "$BACKUPDIR/latest"

	fi

eval rm -fv "$BACKUPDIR/latest/*"

fi



# IO redirection for logging.

touch $LOGFILE

exec 6>&1           # Link file descriptor #6 with stdout.

                    # Saves stdout.

exec > $LOGFILE     # stdout replaced with file $LOGFILE.

touch $LOGERR

exec 7>&2           # Link file descriptor #7 with stderr.

                    # Saves stderr.

exec 2> $LOGERR     # stderr replaced with file $LOGERR.





# Functions



# Database dump function

dbdump () {

mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 > $2

return 0

}



# Compression function plus latest copy

SUFFIX=""

compression () {

if [ "$COMP" = "gzip" ]; then

	gzip -f "$1"

	echo

	echo Backup Information for "$1"

	gzip -l "$1.gz"

	SUFFIX=".gz"

elif [ "$COMP" = "bzip2" ]; then

	echo Compression information for "$1.bz2"

	bzip2 -f -v $1 2>&1

	SUFFIX=".bz2"

else

	echo "No compression option set, check advanced settings"

fi

if [ "$LATEST" = "yes" ]; then

	cp $1$SUFFIX "$BACKUPDIR/latest/"

fi	

return 0

}





# Run command before we begin

if [ "$PREBACKUP" ]

	then

	echo ======================================================================

	echo "Prebackup command output."

	echo

	eval $PREBACKUP

	echo

	echo ======================================================================

	echo

fi





if [ "$SEPDIR" = "yes" ]; then # Check if CREATE DATABSE should be included in Dump

	if [ "$CREATE_DATABASE" = "no" ]; then

		OPT="$OPT --no-create-db"

	else

		OPT="$OPT --databases"

	fi

else

	OPT="$OPT --databases"

fi



# Hostname for LOG information

if [ "$DBHOST" = "localhost" ]; then

	HOST=`hostname`

	if [ "$SOCKET" ]; then

		OPT="$OPT --socket=$SOCKET"

	fi

else

	HOST=$DBHOST

fi



# If backing up all DBs on the server

if [ "$DBNAMES" = "all" ]; then

        DBNAMES="`mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST --batch --skip-column-names -e "show databases"| sed 's/ /%/g'`"



	# If DBs are excluded

	for exclude in $DBEXCLUDE

	do

		DBNAMES=`echo $DBNAMES | sed "s/\b$exclude\b//g"`

	done



        MDBNAMES=$DBNAMES

fi

	

echo ======================================================================

echo AutoMySQLBackup VER $VER

echo http://sourceforge.net/projects/automysqlbackup/

echo 

echo Backup of Database Server - $HOST

echo ======================================================================



# Test is seperate DB backups are required

if [ "$SEPDIR" = "yes" ]; then

echo Backup Start Time `date`

echo ======================================================================

	# Monthly Full Backup of all Databases

	if [ $DOM = "01" ]; then

		for MDB in $MDBNAMES

		do

 

			 # Prepare $DB for using

		        MDB="`echo $MDB | sed 's/%/ /g'`"



			if [ ! -e "$BACKUPDIR/monthly/$MDB" ]		# Check Monthly DB Directory exists.

			then

				mkdir -p "$BACKUPDIR/monthly/$MDB"

			fi

			echo Monthly Backup of $MDB...

				dbdump "$MDB" "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql"

				compression "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql"

				BACKUPFILES="$BACKUPFILES $BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql$SUFFIX"

			echo ----------------------------------------------------------------------

		done

	fi



	for DB in $DBNAMES

	do

	# Prepare $DB for using

	DB="`echo $DB | sed 's/%/ /g'`"

	

	# Create Seperate directory for each DB

	if [ ! -e "$BACKUPDIR/daily/$DB" ]		# Check Daily DB Directory exists.

		then

		mkdir -p "$BACKUPDIR/daily/$DB"

	fi

	

	if [ ! -e "$BACKUPDIR/weekly/$DB" ]		# Check Weekly DB Directory exists.

		then

		mkdir -p "$BACKUPDIR/weekly/$DB"

	fi

	

	# Weekly Backup

	if [ $DNOW = $DOWEEKLY ]; then

		echo Weekly Backup of Database \( $DB \)

		echo Rotating 5 weeks Backups...

			if [ "$W" -le 05 ];then

				REMW=`expr 48 + $W`

			elif [ "$W" -lt 15 ];then

				REMW=0`expr $W - 5`

			else

				REMW=`expr $W - 5`

			fi

		eval rm -fv "$BACKUPDIR/weekly/$DB_week.$REMW.*" 

		echo

			dbdump "$DB" "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql"

			compression "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql$SUFFIX"

		echo ----------------------------------------------------------------------

	

	# Daily Backup

	else

		echo Daily Backup of Database \( $DB \)

		echo Rotating last weeks Backup...

		eval rm -fv "$BACKUPDIR/daily/$DB/*.$DOW.sql.*" 

		echo

			dbdump "$DB" "$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql"

			compression "$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql$SUFFIX"

		echo ----------------------------------------------------------------------

	fi

	done

echo Backup End `date`

echo ======================================================================





else # One backup file for all DBs

echo Backup Start `date`

echo ======================================================================

	# Monthly Full Backup of all Databases

	if [ $DOM = "01" ]; then

		echo Monthly full Backup of \( $MDBNAMES \)...

			dbdump "$MDBNAMES" "$BACKUPDIR/monthly/$DATE.$M.all-databases.sql"

			compression "$BACKUPDIR/monthly/$DATE.$M.all-databases.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/monthly/$DATE.$M.all-databases.sql$SUFFIX"

		echo ----------------------------------------------------------------------

	fi



	# Weekly Backup

	if [ $DNOW = $DOWEEKLY ]; then

		echo Weekly Backup of Databases \( $DBNAMES \)

		echo

		echo Rotating 5 weeks Backups...

			if [ "$W" -le 05 ];then

				REMW=`expr 48 + $W`

			elif [ "$W" -lt 15 ];then

				REMW=0`expr $W - 5`

			else

				REMW=`expr $W - 5`

			fi

		eval rm -fv "$BACKUPDIR/weekly/week.$REMW.*" 

		echo

			dbdump "$DBNAMES" "$BACKUPDIR/weekly/week.$W.$DATE.sql"

			compression "$BACKUPDIR/weekly/week.$W.$DATE.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/weekly/week.$W.$DATE.sql$SUFFIX"

		echo ----------------------------------------------------------------------

		

	# Daily Backup

	else

		echo Daily Backup of Databases \( $DBNAMES \)

		echo

		echo Rotating last weeks Backup...

		eval rm -fv "$BACKUPDIR/daily/*.$DOW.sql.*" 

		echo

			dbdump "$DBNAMES" "$BACKUPDIR/daily/$DATE.$DOW.sql"

			compression "$BACKUPDIR/daily/$DATE.$DOW.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/daily/$DATE.$DOW.sql$SUFFIX"

		echo ----------------------------------------------------------------------

	fi

echo Backup End Time `date`

echo ======================================================================

fi

echo Total disk space used for backup storage..

echo Size - Location

echo `du -hs "$BACKUPDIR"`

echo

echo ======================================================================

echo If you find AutoMySQLBackup valuable please make a donation at

echo http://sourceforge.net/project/project_donations.php?group_id=101066

echo ======================================================================



# Run command when we're done

if [ "$POSTBACKUP" ]

	then

	echo ======================================================================

	echo "Postbackup command output."

	echo

	eval $POSTBACKUP

	echo

	echo ======================================================================

fi



#Clean up IO redirection

exec 1>&6 6>&-      # Restore stdout and close file descriptor #6.

exec 1>&7 7>&-      # Restore stdout and close file descriptor #7.



if [ "$MAILCONTENT" = "files" ]

then

	if [ -s "$LOGERR" ]

	then

		# Include error log if is larger than zero.

		BACKUPFILES="$BACKUPFILES $LOGERR"

		ERRORNOTE="WARNING: Error Reported - "

	fi

	#Get backup size

	ATTSIZE=`du -c $BACKUPFILES | grep "[[:digit:][:space:]]total$" |sed s/\s*total//`

	if [ $MAXATTSIZE -ge $ATTSIZE ]

	then

		BACKUPFILES=`echo "$BACKUPFILES" | sed -e "s# # -a #g"`	#enable multiple attachments

		mutt -s "$ERRORNOTE MySQL Backup Log and SQL Files for $HOST - $DATE" $BACKUPFILES $MAILADDR < $LOGFILE		#send via mutt

	else

		cat "$LOGFILE" | mail -s "WARNING! - MySQL Backup exceeds set maximum attachment size on $HOST - $DATE" $MAILADDR

	fi

elif [ "$MAILCONTENT" = "log" ]

then

	cat "$LOGFILE" | mail -s "MySQL Backup Log for $HOST - $DATE" $MAILADDR

	if [ -s "$LOGERR" ]

		then

			cat "$LOGERR" | mail -s "ERRORS REPORTED: MySQL Backup error Log for $HOST - $DATE" $MAILADDR

	fi	

elif [ "$MAILCONTENT" = "quiet" ]

then

	if [ -s "$LOGERR" ]

		then

			cat "$LOGERR" | mail -s "ERRORS REPORTED: MySQL Backup error Log for $HOST - $DATE" $MAILADDR

			cat "$LOGFILE" | mail -s "MySQL Backup Log for $HOST - $DATE" $MAILADDR

	fi

else

	if [ -s "$LOGERR" ]

		then

			cat "$LOGFILE"

			echo

			echo "###### WARNING ######"

			echo "Errors reported during AutoMySQLBackup execution.. Backup failed"

			echo "Error log below.."

			cat "$LOGERR"

	else

		cat "$LOGFILE"

	fi	

fi



if [ -s "$LOGERR" ]

	then

		STATUS=1

	else

		STATUS=0

fi



# Clean up Logfile

eval rm -f "$LOGFILE"

eval rm -f "$LOGERR"



exit $STATUS

[ThaiTumweb]

@KKE

เจอกับไฟล์ backup ของ DA ครับ backup/restore ด้วย DA

ไฟล์ sql ที่ backup ไว้ภาษาไทยมันเพี้ยนไปบางตัว 95% ไม่มีปัญหา แต่บางตัวอักษรกลายเป็น ?? ไปตั้งแต่ตอน backup ก็ยังงงอยู่ว่าทำไม

ค่าที่เก็บใน sql มันประมาณนี้

“?สินค้ารายการที่ 1 กล่อมถนอมอาหาร?? ?
…”

คือตัวอักษรบางส่วนกลายเป็น ? ไปซะงั้น โดยส่วนใหญ่ก็เป็นภาษาไทยถูกต้อง

ที่เคยเจอมาคือ charset ผิด dump ออกมาแล้วเละไปเลย แบบนี้ยังเอามา convert ให้ charset ถูกแล้วใช้งานได้

ผมไม่เคยเจอเลยสักครั้งนะครับ

แล้วได้ดูปล่าวครับว่า ที่เป็นเพราะว่าอะไร มีอะไรที่ผิดปรกติไหมครับ

[pizzaman911]

ความรู้ไม่มี แวะมาแจกแต้มครับ

เคยเจอปัญหาตอน restore กลับ แต่เป็นเพราะคนละ version ครับ นอกจากนั้นไม่เคยเจอปัญหา mysqldump ครับ

[kke]

@Thaitumweb

ผมไม่เคยเจอเลยสักครั้งนะครับ

แล้วได้ดูปล่าวครับว่า ที่เป็นเพราะว่าอะไร มีอะไรที่ผิดปรกติไหมครับ

ผมก็เพิ่งเคยเจอครั้งเดียวครับ เป็น case ที่แปลก แต่ไม่ได้ไปไล่หาว่าเพราะอะไร พอดีไม่ใช่้เครื่องผม

แล้วก็ backup มีแต่ที่ back ไว้ด้วย DA ทั้งหมด มีย้อนหลังหลายวัน เป็นเหมือนกันหมด

เอาไฟล์ backup มาแกะดูไฟล์ sql ก็เจอว่ามันเป็น ? ปนอยู่กับภาษาไทยปกติแล้ว

เว็บที่ restore กลับมาก็เป็นภาษาไทยปน ? ไปแล้ว ก็ไม่รู้ว่าจะไปไล่หาอะไรตรงใหน

เดาว่าที่กลายเป็น ? น่าจะเป็นตัวอักษรพิเศษ ที่เก็บเป็น utf8 แล้วตอน backup bit มันหายไป

อาจเพราะ db เป็น utf8 แต่ใน my.cnf ใช้ default tis620 และตั้งค่า init tis620 ไว้ด้วย

ก็ได้แค่ให้เจ้าของเว็บไปไล่แก้ไขข้อความแต่ละหน้าเอาใหม่ เพราะเนื้อหา 90% ไม่มีปัญหา มันกลายเป็น ? เฉพาะแค่บางส่วนในข้อความ

[icez]

มันใส่ option อะไรเพิ่มรึเปล่าหว่า ผมไม่เคยเจอปัญหาเลยนะ

[toey]

@Dino

ถ้า DB ก้อนใหญ่จัด ผมชอบเจอ Syntax error ตอน import กลับเข้าไปคืนนะ หาวิธีแก้อยู่เหมือนกัน ถึงได้เตือนไว้ก่อนว่าให้ test ล่วงหน้า

เพิ่ม -c -f เข้าไปด้วยครับ ถ้า error ก็จะ error แค่แถวเดียวครับ แล้วมันจะทำงานต่อครับ

[toey]

@spinner_j

ความรู้แน่เอี้ยดครับ ขอบคุณมากครับ ถกกันได้มันส์ดี กระทู้เดียวเล่นเอามึนไปเลย

ผมไม่ได้เทพแต่ขอ Share ความรู้จากประสบการณ์ที่เคยใช้ล่ะกันนะครับ เผื่อเพื่อนๆ พี่ๆ น้องๆ ท่านไหนสนใจวิธีนี้น่าจะเอาไปประยุกต์ได้มากอยู่ครับ

ผมใช้ตัวนี้ครับ ใช้มานานแล้ว 2-3 ปีได้ ไม่เคยมีปัญหา restore ไม่ได้ และไม่เคยมีปัญหากับภาษาไทยครับ

ทุกอย่าง Ok สำหรับผม แต่แนะนำให้ผู้ที่จะนำไปใช้ควรทดสอบจนตัวเองมั่นใจอีกครั้งนะครับ

AutoMySQLBackup download | SourceForge.net

จะเข้าไป Load หรือว่าเอา script ไปเลยก็ได้ครับ

ใช้ cron ตามเวลาที่อยาก Backup ครับ มันจะ Backup ให้เราเองทุกวันตามเวลาที่กำหนด แยก folder เป็น

daily weekly monthly เลยครับ ทุกครั้งที่ Backup มันจัด File เข้า Folder ให้ไม่ทับ File เดิม

แถมแยก Folder ใน daily weekly monthly เป็นชื่อ DB ของเราด้วยครับ

ตัวอย่างการ Backup ทุกเวลา ตี 2 กับอีก 19 นาที ชื่อ File จะเป็นชื่อ DB_วันที่_เวลา_วัน.sql.gz เลยครับ

ใน Daily (จะไม่มี Monday เพราะว่าผมตั้งวันแรกของอาทิตย์เป็นวันจันทร์ครับ)

-rw-r–r-- 1 root wheel 107698436 Aug 21 02:23 thspin_forum_v3_2010-08-21_02h19m.Saturday.sql.gz

-rw-r–r-- 1 root wheel 26196805 Aug 22 02:21 thspin_forum_v3_2010-08-22_02h19m.Sunday.sql.gz

-rw-r–r-- 1 root wheel 29660807 Aug 24 02:21 thspin_forum_v3_2010-08-24_02h19m.Tuesday.sql.gz

-rw-r–r-- 1 root wheel 31316449 Aug 25 02:21 thspin_forum_v3_2010-08-25_02h19m.Wednesday.sql.gz

-rw-r–r-- 1 root wheel 32691484 Aug 26 02:21 thspin_forum_v3_2010-08-26_02h19m.Thursday.sql.gz

-rw-r–r-- 1 root wheel 34003713 Aug 27 02:21 thspin_forum_v3_2010-08-27_02h19m.Friday.sql.gz

ใน Weekly ก็จะเก็บ thspin_forum_v3_2010-08-23_02h19m.Monday.sql.gz ครับ เก็บทุกๆ Monday ไม่ทับกันและรู้สึกว่าไม่ลบทิ้งนะครับ หรือครบเดือนแล้วลบผมจำไม่ได้

ใน Monthly อันนี้จำไม่ได้ น่าจะเก็บทุกวันสุดท้ายของแต่ละเดือนชื่อ File ก็คล้ายๆ กันครับ อันนี้น่าจะเก็บทุกๆ สิ้นเดือนแบบไม่ลบทิ้งเช่นกันครับ

ทดสอบแล้วถูกใจหรือไม่ก็แล้วแต่ความชอบหรืองานของแต่ละคนนะครับ

ความเสี่ยงในการนำไปใช้ขึ้นอยู่กับการทดสอบจนมั่นใจแล้วของคุณเองเท่านั้นครับ ^^

แค่สนุกในการทำเครื่องและอยากแลกเปลี่ยนประสบการณ์ครับ ไม่ได้ทำเป็นอาชีพ ^^

ผิดพลาดอย่างไร พี่ๆ ชี้แนะได้เต็มที่ครับ น้อมรับเป็นความรู้ต่อไปครับ

[hr]

ปรับแต่งคร่าวๆ

Username to access the MySQL server e.g. dbuser

User mysql ที่สามารถเข้าถึง DB ที่เราจะ Backup ได้ ถ้าต้องการ Backup ทุก Database ก็ต้องเป็น User ที่เข้าได้ทุก DB

USERNAME=dbuser

Username to access the MySQL server e.g. password

Password ของ User ด้านบน

PASSWORD=password

Host name (or IP address) of MySQL server e.g localhost

Host name ถ้า Backup เครื่องตัวเองโดยใส่ localhost ถ้า Backup เครื่องอื่นก็ใส่ IP ครับ

DBHOST=localhost

List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3”

เลือก DB ที่จะ Backup ได้ด้วยครับ ไม่ต้องเลือกหมดก็ได้

ถ้าอยากให้ Backup ทั้งหมดใส่ =“all” ครับ ด้านท้าย Script สามารถเลือก DB ที่ไม่ต้องการ Backup ได้ด้วยครับ เหมาะสำหรับอยาก Backup เกือบหมดเว้นแค่ไม่กี่ DB

DBNAMES=“DB1 DB2 DB3”

Backup directory location e.g /backups

ตั้งตามที่เราอยากให้ Backup ของเราไปเก็บที่ไหนครับ

BACKUPDIR=“/backups”

#!/bin/bash

#

# MySQL Backup Script

# VER. 2.5 - http://sourceforge.net/projects/automysqlbackup/

# Copyright (c) 2002-2003 [email]wipe_out@lycos.co.uk[/email]

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

# GNU General Public License for more details.

# 

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

#

#=====================================================================

#=====================================================================

# Set the following variables to your system needs

# (Detailed instructions below variables)

#=====================================================================



# Username to access the MySQL server e.g. dbuser

USERNAME=dbuser



# Username to access the MySQL server e.g. password

PASSWORD=password



# Host name (or IP address) of MySQL server e.g localhost

DBHOST=localhost



# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"

DBNAMES="DB1 DB2 DB3"



# Backup directory location e.g /backups

BACKUPDIR="/backups"



# Mail setup

# What would you like to be mailed to you?

# - log   : send only log file

# - files : send log file and sql files as attachments (see docs)

# - stdout : will simply output the log to the screen if run manually.

# - quiet : Only send logs if an error occurs to the MAILADDR.

MAILCONTENT="stdout"



# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])

MAXATTSIZE="4000"



# Email Address to send mail to? (user@domain.com)

MAILADDR="user@domain.com"





# ============================================================

# === ADVANCED OPTIONS ( Read the doc's below for details )===

#=============================================================



# List of DBBNAMES for Monthly Backups.

MDBNAMES="mysql $DBNAMES"



# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)

DBEXCLUDE=""



# Include CREATE DATABASE in backup?

CREATE_DATABASE=yes



# Separate backup directory and file for each DB? (yes or no)

SEPDIR=yes



# Which day do you want weekly backups? (1 to 7 where 1 is Monday)

DOWEEKLY=6



# Choose Compression type. (gzip or bzip2)

COMP=gzip



# Compress communications between backup server and MySQL server?

COMMCOMP=no



# Additionally keep a copy of the most recent backup in a seperate directory.

LATEST=no



#  The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)

MAX_ALLOWED_PACKET=



#  For connections to localhost. Sometimes the Unix socket file must be specified.

SOCKET=



# Command to run before backups (uncomment to use)

#PREBACKUP="/etc/mysql-backup-pre"



# Command run after backups (uncomment to use)

#POSTBACKUP="/etc/mysql-backup-post"



#=====================================================================

# Options documantation

#=====================================================================

# Set USERNAME and PASSWORD of a user that has at least SELECT permission

# to ALL databases.

#

# Set the DBHOST option to the server you wish to backup, leave the

# default to backup "this server".(to backup multiple servers make

# copies of this file and set the options for that server)

#

# Put in the list of DBNAMES(Databases)to be backed up. If you would like

# to backup ALL DBs on the server set DBNAMES="all".(if set to "all" then

# any new DBs will automatically be backed up without needing to modify

# this backup script when a new DB is created).

#

# If the DB you want to backup has a space in the name replace the space

# with a % e.g. "data base" will become "data%base"

# NOTE: Spaces in DB names may not work correctly when SEPDIR=no.

#

# You can change the backup storage location from /backups to anything

# you like by using the BACKUPDIR setting..

#

# The MAILCONTENT and MAILADDR options and pretty self explanitory, use

# these to have the backup log mailed to you at any email address or multiple

# email addresses in a space seperated list.

# (If you set mail content to "log" you will require access to the "mail" program

# on your server. If you set this to "files" you will have to have mutt installed

# on your server. If you set it to "stdout" it will log to the screen if run from 

# the console or to the cron job owner if run through cron. If you set it to "quiet"

# logs will only be mailed if there are errors reported. )

#

# MAXATTSIZE sets the largest allowed email attachments total (all backup files) you

# want the script to send. This is the size before it is encoded to be sent as an email

# so if your mail server will allow a maximum mail size of 5MB I would suggest setting

# MAXATTSIZE to be 25% smaller than that so a setting of 4000 would probably be fine.

#

# Finally copy automysqlbackup.sh to anywhere on your server and make sure

# to set executable permission. You can also copy the script to

# /etc/cron.daily to have it execute automatically every night or simply

# place a symlink in /etc/cron.daily to the file if you wish to keep it 

# somwhere else.

# NOTE:On Debian copy the file with no extention for it to be run

# by cron e.g just name the file "automysqlbackup"

#

# Thats it..

#

#

# === Advanced options doc's ===

#

# The list of MDBNAMES is the DB's to be backed up only monthly. You should

# always include "mysql" in this list to backup your user/password

# information along with any other DBs that you only feel need to

# be backed up monthly. (if using a hosted server then you should

# probably remove "mysql" as your provider will be backing this up)

# NOTE: If DBNAMES="all" then MDBNAMES has no effect as all DBs will be backed

# up anyway.

#

# If you set DBNAMES="all" you can configure the option DBEXCLUDE. Other

# wise this option will not be used.

# This option can be used if you want to backup all dbs, but you want 

# exclude some of them. (eg. a db is to big).

#

# Set CREATE_DATABASE to "yes" (the default) if you want your SQL-Dump to create

# a database with the same name as the original database when restoring.

# Saying "no" here will allow your to specify the database name you want to

# restore your dump into, making a copy of the database by using the dump

# created with automysqlbackup.

# NOTE: Not used if SEPDIR=no

#

# The SEPDIR option allows you to choose to have all DBs backed up to

# a single file (fast restore of entire server in case of crash) or to

# seperate directories for each DB (each DB can be restored seperately

# in case of single DB corruption or loss).

#

# To set the day of the week that you would like the weekly backup to happen

# set the DOWEEKLY setting, this can be a value from 1 to 7 where 1 is Monday,

# The default is 6 which means that weekly backups are done on a Saturday.

#

# COMP is used to choose the copmression used, options are gzip or bzip2.

# bzip2 will produce slightly smaller files but is more processor intensive so

# may take longer to complete.

#

# COMMCOMP is used to enable or diable mysql client to server compression, so

# it is useful to save bandwidth when backing up a remote MySQL server over

# the network. 

#

# LATEST is to store an additional copy of the latest backup to a standard

# location so it can be downloaded bt thrid party scripts.

#

# If the DB's being backed up make use of large BLOB fields then you may need

# to increase the MAX_ALLOWED_PACKET setting, for example 16MB..

#

# When connecting to localhost as the DB server (DBHOST=localhost) sometimes

# the system can have issues locating the socket file.. This can now be set

# using the SOCKET parameter.. An example may be SOCKET=/private/tmp/mysql.sock

#

# Use PREBACKUP and POSTBACKUP to specify Per and Post backup commands

# or scripts to perform tasks either before or after the backup process.

#

#

#=====================================================================

# Backup Rotation..

#=====================================================================

#

# Daily Backups are rotated weekly..

# Weekly Backups are run by default on Saturday Morning when

# cron.daily scripts are run...Can be changed with DOWEEKLY setting..

# Weekly Backups are rotated on a 5 week cycle..

# Monthly Backups are run on the 1st of the month..

# Monthly Backups are NOT rotated automatically...

# It may be a good idea to copy Monthly backups offline or to another

# server..

#

#=====================================================================

# Please Note!!

#=====================================================================

#

# I take no resposibility for any data loss or corruption when using

# this script..

# This script will not help in the event of a hard drive crash. If a 

# copy of the backup has not be stored offline or on another PC..

# You should copy your backups offline regularly for best protection.

#

# Happy backing up...

#

#=====================================================================

# Restoring

#=====================================================================

# Firstly you will need to uncompress the backup file.

# eg.

# gunzip file.gz (or bunzip2 file.bz2)

#

# Next you will need to use the mysql client to restore the DB from the

# sql file.

# eg.

# mysql --user=username --pass=password --host=dbserver database < /path/file.sql

# or

# mysql --user=username --pass=password --host=dbserver -e "source /path/file.sql" database

#

# NOTE: Make sure you use "<" and not ">" in the above command because

# you are piping the file.sql to mysql and not the other way around.

#

# Lets hope you never have to use this.. [img]http://www.thaihosttalk.com/public/style_emoticons/<#EMO_DIR#>/smile.gif[/img]

#

#=====================================================================

# Change Log

#=====================================================================

#

# VER 2.5 - (2006-01-15)

#		Added support for setting MAXIMUM_PACKET_SIZE and SOCKET parameters (suggested by Yvo van Doorn)

# VER 2.4 - (2006-01-23)

#    Fixed bug where weekly backups were not being rotated. (Fix by wolf02)

#    Added hour an min to backup filename for the case where backups are taken multiple

#    times in a day. NOTE This is not complete support for mutiple executions of the script

#    in a single day.

#    Added MAILCONTENT="quiet" option, see docs for details. (requested by snowsam)

#    Updated path statment for compatibility with OSX.

#    Added "LATEST" to additionally store the last backup to a standard location. (request by Grant29)

# VER 2.3 - (2005-11-07)

#    Better error handling and notification of errors (a long time coming)

#    Compression on Backup server to MySQL server communications. 

# VER 2.2 - (2004-12-05)

#    Changed from using depricated "-N" to "--skip-column-names".

#    Added ability to have compressed backup's emailed out. (code from Thomas Heiserowski)

#    Added maximum attachment size setting.

# VER 2.1 - (2004-11-04)

#    Fixed a bug in daily rotation when not using gzip compression. (Fix by Rob Rosenfeld)

# VER 2.0 - (2004-07-28)

#    Switched to using IO redirection instead of pipeing the output to the logfile.

#    Added choice of compression of backups being gzip of bzip2.

#    Switched to using functions to facilitate more functionality.

#    Added option of either gzip or bzip2 compression. 

# VER 1.10 - (2004-07-17)

#    Another fix for spaces in the paths (fix by Thomas von Eyben)

#    Fixed bug when using PREBACKUP and POSTBACKUP commands containing many arguments.

# VER 1.9 - (2004-05-25)

#    Small bug fix to handle spaces in LOGFILE path which contains spaces (reported by Thomas von Eyben)

#    Updated docs to mention that Log email can be sent to multiple email addresses.

# VER 1.8 - (2004-05-01)

#    Added option to make backups restorable to alternate database names

#    meaning that a copy of the database can be created (Based on patch by Rene Hoffmann)

#    Seperated options into standard and advanced.

#    Removed " from single file dump DBMANES because it caused an error but

#    this means that if DB's have spaces in the name they will not dump when SEPDIR=no.

#    Added -p option to mkdir commands to create multiple subdirs without error.

#    Added disk usage and location to the bottom of the backup report.

# VER 1.7 - (2004-04-22)

#    Fixed an issue where weelky backups would only work correctly if server

#    locale was set to English (issue reported by Tom Ingberg)

#    used "eval" for "rm" commands to try and resolve rotation issues.

#    Changed name of status log so multiple scripts can be run at the same time.

# VER 1.6 - (2004-03-14)

#   Added PREBACKUP and POSTBACKUP command functions. (patch by markpustjens)

#   Added support for backing up DB's with Spaces in the name.

#   (patch by markpustjens)

# VER 1.5 - (2004-02-24)

#   Added the ability to exclude DB's when the "all" option is used.

#   (Patch by kampftitan)

# VER 1.4 - (2004-02-02)

#   Project moved to Sourceforge.net

# VER 1.3 - (2003-09-25)

#   Added support for backing up "all" databases on the server without

#    having to list each one seperately in the configuration.

#   Added DB restore instructions.

# VER 1.2 - (2003-03-16)

#   Added server name to the backup log so logs from multiple servers

#   can be easily identified.

# VER 1.1 - (2003-03-13)

#   Small Bug fix in monthly report. (Thanks Stoyanski)

#   Added option to email log to any email address. (Inspired by Stoyanski)

#   Changed Standard file name to .sh extention.

#   Option are set using yes and no rather than 1 or 0.

# VER 1.0 - (2003-01-30)

#   Added the ability to have all databases backup to a single dump

#   file or seperate directory and file for each database.

#   Output is better for log keeping.

# VER 0.6 - (2003-01-22)

#   Bug fix for daily directory (Added in VER 0.5) rotation.

# VER 0.5 - (2003-01-20)

#   Added "daily" directory for daily backups for neatness (suggestion by Jason)

#   Added DBHOST option to allow backing up a remote server (Suggestion by Jason)

#   Added "--quote-names" option to mysqldump command.

#   Bug fix for handling the last and first of the year week rotation.

# VER 0.4 - (2002-11-06)

#   Added the abaility for the script to create its own directory structure.

# VER 0.3 - (2002-10-01)

#   Changed Naming of Weekly backups so they will show in order.

# VER 0.2 - (2002-09-27)

#   Corrected weekly rotation logic to handle weeks 0 - 10 

# VER 0.1 - (2002-09-21)

#   Initial Release

#

#=====================================================================

#=====================================================================

#=====================================================================

#

# Should not need to be modified from here down!!

#

#=====================================================================

#=====================================================================

#=====================================================================

PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin 

DATE=`date +%Y-%m-%d_%Hh%Mm`				# Datestamp e.g 2002-09-21

DOW=`date +%A`							# Day of the week e.g. Monday

DNOW=`date +%u`						# Day number of the week 1 to 7 where 1 represents Monday

DOM=`date +%d`							# Date of the Month e.g. 27

M=`date +%B`							# Month e.g January

W=`date +%V`							# Week Number e.g 37

VER=2.5									# Version Number

LOGFILE=$BACKUPDIR/$DBHOST-`date +%N`.log		# Logfile Name

LOGERR=$BACKUPDIR/ERRORS_$DBHOST-`date +%N`.log		# Logfile Name

BACKUPFILES=""

OPT="--quote-names --opt"			# OPT string for use with mysqldump ( see man mysqldump )



# Add --compress mysqldump option to $OPT

if [ "$COMMCOMP" = "yes" ];

	then

		OPT="$OPT --compress"

	fi



# Add --compress mysqldump option to $OPT

if [ "$MAX_ALLOWED_PACKET" ];

	then

		OPT="$OPT --max_allowed_packet=$MAX_ALLOWED_PACKET"

	fi



# Create required directories

if [ ! -e "$BACKUPDIR" ]		# Check Backup Directory exists.

	then

	mkdir -p "$BACKUPDIR"

fi



if [ ! -e "$BACKUPDIR/daily" ]		# Check Daily Directory exists.

	then

	mkdir -p "$BACKUPDIR/daily"

fi



if [ ! -e "$BACKUPDIR/weekly" ]		# Check Weekly Directory exists.

	then

	mkdir -p "$BACKUPDIR/weekly"

fi



if [ ! -e "$BACKUPDIR/monthly" ]	# Check Monthly Directory exists.

	then

	mkdir -p "$BACKUPDIR/monthly"

fi



if [ "$LATEST" = "yes" ]

then

	if [ ! -e "$BACKUPDIR/latest" ]	# Check Latest Directory exists.

	then

		mkdir -p "$BACKUPDIR/latest"

	fi

eval rm -fv "$BACKUPDIR/latest/*"

fi



# IO redirection for logging.

touch $LOGFILE

exec 6>&1           # Link file descriptor #6 with stdout.

                    # Saves stdout.

exec > $LOGFILE     # stdout replaced with file $LOGFILE.

touch $LOGERR

exec 7>&2           # Link file descriptor #7 with stderr.

                    # Saves stderr.

exec 2> $LOGERR     # stderr replaced with file $LOGERR.





# Functions



# Database dump function

dbdump () {

mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 > $2

return 0

}



# Compression function plus latest copy

SUFFIX=""

compression () {

if [ "$COMP" = "gzip" ]; then

	gzip -f "$1"

	echo

	echo Backup Information for "$1"

	gzip -l "$1.gz"

	SUFFIX=".gz"

elif [ "$COMP" = "bzip2" ]; then

	echo Compression information for "$1.bz2"

	bzip2 -f -v $1 2>&1

	SUFFIX=".bz2"

else

	echo "No compression option set, check advanced settings"

fi

if [ "$LATEST" = "yes" ]; then

	cp $1$SUFFIX "$BACKUPDIR/latest/"

fi	

return 0

}





# Run command before we begin

if [ "$PREBACKUP" ]

	then

	echo ======================================================================

	echo "Prebackup command output."

	echo

	eval $PREBACKUP

	echo

	echo ======================================================================

	echo

fi





if [ "$SEPDIR" = "yes" ]; then # Check if CREATE DATABSE should be included in Dump

	if [ "$CREATE_DATABASE" = "no" ]; then

		OPT="$OPT --no-create-db"

	else

		OPT="$OPT --databases"

	fi

else

	OPT="$OPT --databases"

fi



# Hostname for LOG information

if [ "$DBHOST" = "localhost" ]; then

	HOST=`hostname`

	if [ "$SOCKET" ]; then

		OPT="$OPT --socket=$SOCKET"

	fi

else

	HOST=$DBHOST

fi



# If backing up all DBs on the server

if [ "$DBNAMES" = "all" ]; then

        DBNAMES="`mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST --batch --skip-column-names -e "show databases"| sed 's/ /%/g'`"



	# If DBs are excluded

	for exclude in $DBEXCLUDE

	do

		DBNAMES=`echo $DBNAMES | sed "s/\b$exclude\b//g"`

	done



        MDBNAMES=$DBNAMES

fi

	

echo ======================================================================

echo AutoMySQLBackup VER $VER

echo http://sourceforge.net/projects/automysqlbackup/

echo 

echo Backup of Database Server - $HOST

echo ======================================================================



# Test is seperate DB backups are required

if [ "$SEPDIR" = "yes" ]; then

echo Backup Start Time `date`

echo ======================================================================

	# Monthly Full Backup of all Databases

	if [ $DOM = "01" ]; then

		for MDB in $MDBNAMES

		do

 

			 # Prepare $DB for using

		        MDB="`echo $MDB | sed 's/%/ /g'`"



			if [ ! -e "$BACKUPDIR/monthly/$MDB" ]		# Check Monthly DB Directory exists.

			then

				mkdir -p "$BACKUPDIR/monthly/$MDB"

			fi

			echo Monthly Backup of $MDB...

				dbdump "$MDB" "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql"

				compression "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql"

				BACKUPFILES="$BACKUPFILES $BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql$SUFFIX"

			echo ----------------------------------------------------------------------

		done

	fi



	for DB in $DBNAMES

	do

	# Prepare $DB for using

	DB="`echo $DB | sed 's/%/ /g'`"

	

	# Create Seperate directory for each DB

	if [ ! -e "$BACKUPDIR/daily/$DB" ]		# Check Daily DB Directory exists.

		then

		mkdir -p "$BACKUPDIR/daily/$DB"

	fi

	

	if [ ! -e "$BACKUPDIR/weekly/$DB" ]		# Check Weekly DB Directory exists.

		then

		mkdir -p "$BACKUPDIR/weekly/$DB"

	fi

	

	# Weekly Backup

	if [ $DNOW = $DOWEEKLY ]; then

		echo Weekly Backup of Database \( $DB \)

		echo Rotating 5 weeks Backups...

			if [ "$W" -le 05 ];then

				REMW=`expr 48 + $W`

			elif [ "$W" -lt 15 ];then

				REMW=0`expr $W - 5`

			else

				REMW=`expr $W - 5`

			fi

		eval rm -fv "$BACKUPDIR/weekly/$DB_week.$REMW.*" 

		echo

			dbdump "$DB" "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql"

			compression "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql$SUFFIX"

		echo ----------------------------------------------------------------------

	

	# Daily Backup

	else

		echo Daily Backup of Database \( $DB \)

		echo Rotating last weeks Backup...

		eval rm -fv "$BACKUPDIR/daily/$DB/*.$DOW.sql.*" 

		echo

			dbdump "$DB" "$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql"

			compression "$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql$SUFFIX"

		echo ----------------------------------------------------------------------

	fi

	done

echo Backup End `date`

echo ======================================================================





else # One backup file for all DBs

echo Backup Start `date`

echo ======================================================================

	# Monthly Full Backup of all Databases

	if [ $DOM = "01" ]; then

		echo Monthly full Backup of \( $MDBNAMES \)...

			dbdump "$MDBNAMES" "$BACKUPDIR/monthly/$DATE.$M.all-databases.sql"

			compression "$BACKUPDIR/monthly/$DATE.$M.all-databases.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/monthly/$DATE.$M.all-databases.sql$SUFFIX"

		echo ----------------------------------------------------------------------

	fi



	# Weekly Backup

	if [ $DNOW = $DOWEEKLY ]; then

		echo Weekly Backup of Databases \( $DBNAMES \)

		echo

		echo Rotating 5 weeks Backups...

			if [ "$W" -le 05 ];then

				REMW=`expr 48 + $W`

			elif [ "$W" -lt 15 ];then

				REMW=0`expr $W - 5`

			else

				REMW=`expr $W - 5`

			fi

		eval rm -fv "$BACKUPDIR/weekly/week.$REMW.*" 

		echo

			dbdump "$DBNAMES" "$BACKUPDIR/weekly/week.$W.$DATE.sql"

			compression "$BACKUPDIR/weekly/week.$W.$DATE.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/weekly/week.$W.$DATE.sql$SUFFIX"

		echo ----------------------------------------------------------------------

		

	# Daily Backup

	else

		echo Daily Backup of Databases \( $DBNAMES \)

		echo

		echo Rotating last weeks Backup...

		eval rm -fv "$BACKUPDIR/daily/*.$DOW.sql.*" 

		echo

			dbdump "$DBNAMES" "$BACKUPDIR/daily/$DATE.$DOW.sql"

			compression "$BACKUPDIR/daily/$DATE.$DOW.sql"

			BACKUPFILES="$BACKUPFILES $BACKUPDIR/daily/$DATE.$DOW.sql$SUFFIX"

		echo ----------------------------------------------------------------------

	fi

echo Backup End Time `date`

echo ======================================================================

fi

echo Total disk space used for backup storage..

echo Size - Location

echo `du -hs "$BACKUPDIR"`

echo

echo ======================================================================

echo If you find AutoMySQLBackup valuable please make a donation at

echo http://sourceforge.net/project/project_donations.php?group_id=101066

echo ======================================================================



# Run command when we're done

if [ "$POSTBACKUP" ]

	then

	echo ======================================================================

	echo "Postbackup command output."

	echo

	eval $POSTBACKUP

	echo

	echo ======================================================================

fi



#Clean up IO redirection

exec 1>&6 6>&-      # Restore stdout and close file descriptor #6.

exec 1>&7 7>&-      # Restore stdout and close file descriptor #7.



if [ "$MAILCONTENT" = "files" ]

then

	if [ -s "$LOGERR" ]

	then

		# Include error log if is larger than zero.

		BACKUPFILES="$BACKUPFILES $LOGERR"

		ERRORNOTE="WARNING: Error Reported - "

	fi

	#Get backup size

	ATTSIZE=`du -c $BACKUPFILES | grep "[[:digit:][:space:]]total$" |sed s/\s*total//`

	if [ $MAXATTSIZE -ge $ATTSIZE ]

	then

		BACKUPFILES=`echo "$BACKUPFILES" | sed -e "s# # -a #g"`	#enable multiple attachments

		mutt -s "$ERRORNOTE MySQL Backup Log and SQL Files for $HOST - $DATE" $BACKUPFILES $MAILADDR < $LOGFILE		#send via mutt

	else

		cat "$LOGFILE" | mail -s "WARNING! - MySQL Backup exceeds set maximum attachment size on $HOST - $DATE" $MAILADDR

	fi

elif [ "$MAILCONTENT" = "log" ]

then

	cat "$LOGFILE" | mail -s "MySQL Backup Log for $HOST - $DATE" $MAILADDR

	if [ -s "$LOGERR" ]

		then

			cat "$LOGERR" | mail -s "ERRORS REPORTED: MySQL Backup error Log for $HOST - $DATE" $MAILADDR

	fi	

elif [ "$MAILCONTENT" = "quiet" ]

then

	if [ -s "$LOGERR" ]

		then

			cat "$LOGERR" | mail -s "ERRORS REPORTED: MySQL Backup error Log for $HOST - $DATE" $MAILADDR

			cat "$LOGFILE" | mail -s "MySQL Backup Log for $HOST - $DATE" $MAILADDR

	fi

else

	if [ -s "$LOGERR" ]

		then

			cat "$LOGFILE"

			echo

			echo "###### WARNING ######"

			echo "Errors reported during AutoMySQLBackup execution.. Backup failed"

			echo "Error log below.."

			cat "$LOGERR"

	else

		cat "$LOGFILE"

	fi	

fi



if [ -s "$LOGERR" ]

	then

		STATUS=1

	else

		STATUS=0

fi



# Clean up Logfile

eval rm -f "$LOGFILE"

eval rm -f "$LOGERR"



exit $STATUS

โอวขอบคุณครับ แต่อันนี้ได้แค่เครืองเดียวหนิครับ ต้องใช้หลายๆ files

[nazezaza]

@Dino

Script ที่ผมใช้ export & ftp

01 #!/bin/sh

02 output_name=mysql.all.db.`date +%Y%m%d%H%M`.sql

03 #echo $output_name

04 mysqldump -u<mysql user> -p<mysql password> --all-databases > /path/to/backup/$output_name

05 gzip /path/to/backup/$output_name

06 /usr/local/bin/ncftpput -u <ftp user> -p <ftp password> <ftp server IP> / /path/to/backup/$output_name.gz

07 rm -Rf /path/to/backup/$output_name.gz

คำอธิบาย

01 shell script header

02 กำหนดชื่อไฟล์ ตามด้วย ปี เดือน วัน ชั่วโมง นาที

03 Debug code ถ้าเอา # ออก มันจะแสดงชื่อไฟล output จากบรรทัด 02

04 สั่ง export mysql ทุก database ออกมาใส่ไว้ในไฟล์ จากบรรทัด 02 โปรดสังเกต -u<mysql user> และ -p<mysql password> ไม่ต้องมีเว้นวรรคหลัง -u และ -p

05 สั่ง gzip ซะ

06 สั่ง FTP ไปไว้ที่ FTP server โปรดสังเกต หลัง -u <ftp user> และ -p <ftp password> ตรงนี้ต้องเว้นวรรคระหว่าง -u <ftp user> และ -p <ftp password>

07 สั่งลบ local file ซะ

ปล. โปรดสังเกตอีกสองจุดคือ บรรทัดที่ 06, 07 หลังจาก $output_name จะต้องต่อท้ายด้วย .gz เนื่องมาจากบรรทัดที่ 05 เราทำการสั่ง gzip ไฟล์ ซึ่งจะทำให้ไฟล์นั้นๆ ถูกต่อท้ายด้วย .gz โดยอัตโนมัติ

ปล2. หลังจาก Backup แล้ว อย่าลืมทดสอบ restore กลับบนอีกเครื่องด้วยนะครับ มีบางกรณีที่ restore กลับแล้ว error ถ้าเจอก็ลองแก้ไขดู เดี๋ยวจะต้องมานั่งปวดตับ ณ เวลาที่ต้องการใช้งานมันจริงๆ

ขอถามนิดนะครับ

บรรทัดที่ 06 /usr/local/bin/ncftpput << ncftpput คืออะไรหรอครับแล้วถ้าใช้เป็น vsftpd จะต้องแก้เป็นยังไงหรอครับ

แล้วก็บรรทัดที่ 06 นี้ใส่ port ตรงไหนหรอครับ port พื้นฐาน 21 ถ้าผมใช้เป็นอย่างอื่นจะใส่ยังไงครับ

ปล. ถามไว้นานมากแล้วไม่มีเวลาทดลองซักทีตอนนี้ว่ากะว่าจะลองทำดู ขอบคุณครับ

[DinoNet]

@macrofile

ขอถามนิดนะครับ

บรรทัดที่ 06 /usr/local/bin/ncftpput << ncftpput คืออะไรหรอครับแล้วถ้าใช้เป็น vsftpd จะต้องแก้เป็นยังไงหรอครับ

แล้วก็บรรทัดที่ 06 นี้ใส่ port ตรงไหนหรอครับ port พื้นฐาน 21 ถ้าผมใช้เป็นอย่างอื่นจะใส่ยังไงครับ

ปล. ถามไว้นานมากแล้วไม่มีเวลาทดลองซักทีตอนนี้ว่ากะว่าจะลองทำดู ขอบคุณครับ

ncftpput เป็น ftp client ครับ ใช้ FTP file ไป server ตัวอื่นครับ เพราะถ้าใช้คำสั่ง FTP ธรรมดาๆ จะไม่สามารถใช้ inline ได้ เลยต้องใช้ utility ตัวนี้ช่วยครับ

ส่วน vsftpd เป็น ftp server ครับ คนละเรื่อง(แต่เกี่ยวกัน)

ปล. ncftpput download ได้ที่ http://www.ncftp.com

[nazezaza]

ขอบคุณครับ