3 # $Id: sqlbackup.sh,v 2.0 2006/10/17 17:32:05 mistur Exp $
4 # ----------------------------------------------------------------------
5 # AlternC - Web Hosting System
6 # Copyright (C) 2002 by the AlternC Development Team.
8 # ----------------------------------------------------------------------
10 # Valentin Lacambre's web hosting softwares: http://altern.org/
11 # ----------------------------------------------------------------------
14 # This program is free software; you can redistribute it and/or
15 # modify it under the terms of the GNU General Public License (GPL)
16 # as published by the Free Software Foundation; either version 2
17 # of the License, or (at your option) any later version.
19 # This program is distributed in the hope that it will be useful,
20 # but WITHOUT ANY WARRANTY; without even the implied warranty of
21 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22 # GNU General Public License for more details.
24 # To read the license please visit http://www.gnu.org/copyleft/gpl.html
25 # ----------------------------------------------------------------------
26 # Original Author of file: Benjamin Sonntag - 2003-03-23
27 # Purpose of file: MySQL Database backup shell script for AlternC
28 # ----------------------------------------------------------------------
29 # Changed by Yoann Moulin : 2006-10-16
30 # * Adding an other possibilty for name of the backup files which
31 # avoid renaming old backup files (name rotation methode)
32 # this methode include the date of the backup day in the name of the
34 # Usefull for person who use rsync, rsnapshot, etc... this methode
35 # avoid to sync old files which just has been rename but seem diff
39 # Get mysql user and password :
40 . /etc/alternc/local.sh
42 # get the date of the day
45 # echo function, used for output wrapping when run in daemon
47 # usage: print [option] <message>
48 # without option, print <message> in any case on the stdout
51 # error : print <message> in any case and indicate that an error message
52 # debug : print <message> if debug mode is active
53 # info : print <message> if verbose mode is active
56 # if backup running in daemon mode, printing in log file if an otpion
57 # is gived to the function
60 # if a log level is given to the print function
61 # 'error', 'info' or 'debug'
63 if [ "$1" == "error" ] || [ "$1" == "info" ] || [ "$1" == "debug" ];
65 # read it and remove it for arg list
71 # - No log level is specified
72 # - Log level equal to 'error'
73 # => print in any case on stdout
74 # => add to log file as well if $DAEMON set to 'ON'
75 # - Log level equal to 'debug' and $DEBUG is set to on
76 # - Log level equal to 'info' and $VERBOSE set to 'ON'
77 # => print on log file if $DAEMON set to 'ON', on stdout if not
78 if [ -z "$log_level" ] ||
79 [ "$log_level" == "error" ] ||
80 [ "$DEBUG" == "ON" -a "$log_level" == "debug" ] ||
81 [ "$log_level" == "info" -a "$VERBOSE" == "ON" ] ;
83 if [ "$DAEMON" == "ON" ] ; then
84 # function without option must be print on stdout in anycase
85 # even if print in the log file
86 if [ -z "$log_level" ] || [ "$log_level" == "error" ];
88 echo "$EXEC_CMD $log_level: $*"
90 logger -p local0.$log_level -t sqlbackup "$*"
92 if [ -z "$log_level" ];
119 # mysql -B uses tab as a separator between fields, so we have to mess
120 # with IFS in order to get the correct behaviour
123 # read parameter given by mysql
124 while read login pass db count compressed target_dir; do
126 debug "read $login \$pass $db $count $compressed $target_dir"
127 # restore $IFS after read parameter
130 # by default : DOBAKCUP set to yes
133 if [ "$compressed" -eq 1 ]; then
139 # The target directory must exist
140 test -d "$target_dir" || mkdir -p "$target_dir"
142 # if $SQLBACKUP_TYPE is set to "rotate" classical rotation files methode will be used
143 # use incrementale number in the name of files where the highest number indicate
145 # if the rotate type is not set or set to date, the name of the export file will contain the date
146 # of the backup on won't be rotate by the classic rotate number
147 # usefull if you're using rsync or rsnapshop or everything base on rsync to avoir to copy
148 # rotate files which just change name
150 # ------------------------------------------------------------------ #
151 # the variable SQLBACKUP_TYPE must be set in /etc/alternc/local.sh #
152 # ------------------------------------------------------------------ #
153 if [ $SQLBACKUP_TYPE == "rotate" ]; then
158 while [ $i -gt 1 ] ; do
162 if [ -e "${target_dir}/${db}.sql.${next_i}${ext}" ]; then
163 mv -f "${target_dir}/${db}.sql.${next_i}${ext}" \
164 "${target_dir}/${db}.sql.${i}${ext}" 2>/dev/null || true
166 i=$next_i # loop should end here
169 # move most recently backup with a rotate file name
170 if [ -e "${target_dir}/${db}.sql${ext}" ]; then
171 mv -f "${target_dir}/${db}.sql${ext}" \
172 "${target_dir}/${db}.sql.${i}${ext}" 2>/dev/null || true
175 name_backup_file="${db}"
180 # calcul the mtime parameter for find
181 # $count is the number of backup to keep
182 # daily : if we are keeping X backup, deleting the file which has the mtime at X + 1 days
183 # weekly : if we are keeping X backup, deleting the file which has the mtime at (X + 1) * 7 day
184 # echo "last2del=( $count + 1 ) * $coef "
186 last2del=$(( ( $count + 1 ) * $coef ))
188 # find the oldest backup file need to be delete
189 # find ${target_dir} : in the target_dir
190 # -name \"${db}.*sql.*\" : All files like <db_name>.*sql.*
191 # -maxdepth 0 : only in the target dir (on not in the subdirectory)
192 # -mtime $last2del : files with the exact mtime set to $last2del
193 # daily : ( number of backup to keep + 1 ) days
194 # weekly : ( number of backup to keep + 1 ) * 7 days
195 # -exec rm -f {} \; : remove all files found
197 debug "find ${target_dir} -name \"${db}.*sql${ext}\" -maxdepth 1 -mtime +$last2del -exec rm -f {} \; -ls"
198 find ${target_dir} -name "${db}.*sql${ext}" -maxdepth 1 -mtime +${last2del} -exec rm -f {} \; -ls || true
200 # set the name of the backup file with the date of the day
201 name_backup_file="${db}.${DATE}"
205 # if the backup exite and SQLBACKUP_OVERWRITE is set to NO, cancel backup
206 if [ -f "${target_dir}/${name_backup_file}.sql${ext}" ] && [ "$SQLBACKUP_OVERWRITE" == "no" ] ; then
208 info "sqlbackup.sh: ${target_dir}/${name_backup_file}.sql${ext}: already exist"
209 info " => no backup done as specify in allow-overwrite = $SQLBACKUP_OVERWRITE"
212 # if the backup exite and SQLBACKUP_OVERWRITE is set to RENAME, add
213 elif [ -f "${target_dir}/${name_backup_file}.sql${ext}" ] && [ "$SQLBACKUP_OVERWRITE" == "rename" ] ; then
215 info "sqlbackup.sh: ${target_dir}/${name_backup_file}.sql${ext}: already exist"
216 info " => renaming the new file as specify in allow-overwrite = $SQLBACKUP_OVERWRITE"
218 name_backup_file="${name_backup_file}.${hours}"
220 # if the backup exite and SQLBACKUP_OVERWRITE is set OVERWRITE, add
221 elif [ -f "${target_dir}/${name_backup_file}.sql${ext}" ] && [ "$SQLBACKUP_OVERWRITE" == "overwrite" ] ; then
223 info "sqlbackup.sh: ${target_dir}/${name_backup_file}.sql${ext}: already exist"
224 info " => overwrite file as specify in allow-overwrite = $SQLBACKUP_OVERWRITE"
230 # --add-drop-table : Add a 'drop table' before each create.
231 # usefull if you want to override the database without delete table before
232 # this is need to used restore from the alternc interface
233 # --allow-keywords : Allow creation of column names that are keywords.
235 # --quote-names : Quote table and column names with `
236 # Usefull if you have space in table or column names
237 # --force : Continue even if we get an sql-error.
238 # To avoid end of script during backup script execution
239 # Allow script to backup other database if one of the have an error
240 # --quick : Don't buffer query, dump directly to stdout.
241 # optimisation option
242 # --extended-insert : Allows utilization of the new, much faster INSERT syntax.
243 # optimization option
244 # --add-locks : Add locks around insert statements.
245 # --lock-tables : Lock all tables for read.
246 # those 2 options avoid insert during dump which can create an unconsistent
247 # state of the database backup
248 if [ "$DO_BACKUP" == "YES" ]; then
249 command="mysqldump --defaults-file=/etc/alternc/my.cnf --add-drop-table --allow-keywords --quote-names --force --quick --add-locks --lock-tables --extended-insert $db"
250 if [ "$compressed" -eq 1 ] ; then
251 debug "$command > ${target_dir}/${name_backup_file}.sql${ext}"
252 $command | gzip -c > "${target_dir}/${name_backup_file}.sql${ext}" || echo "backup failed for ${name_backup_file}"
254 debug "$command > ${target_dir}/${name_backup_file}.sql${ext}"
255 $command > "${target_dir}/${name_backup_file}.sql${ext}" || echo "backup failed for ${name_backup_file}"
264 # read_parameters gets all command-line arguments and analyzes them
269 # for all parameter give to the script
270 while [ "$1" != "" ] ; do
272 -h|--help) usage; exit ;;
273 -v|--verbose) VERBOSE="ON" ;;
274 -d|--debug) DEBUG="ON" ;;
275 -t|--type) shift; TYPE="$1";;
276 -n|--name-methode) shift; SQLBACKUP_TYPE="$1";;
277 -a|--allow-ovewrite) shift; SQLBACKUP_OVERWRITE="$1" ;;
278 daily|weekly) TYPE="$1";; # backwards compatibility
280 error "invalid option -- $1"
281 error "Try \`sqlbackup.sh --help' for more information."
284 # in case of no argument give to an option
285 # shift execute an exit if already empty
286 # add test to avoid this at least to print error message
287 [ "$1" != "" ] && shift
291 debug "SQLBACKUP_TYPE = $SQLBACKUP_TYPE"
292 debug "SQLBACKUP_OVERWRITE = $SQLBACKUP_OVERWRITE"
296 if [ "$TYPE" == "daily" ]; then
300 elif [ "$TYPE" == "weekly" ] ; then
304 elif [ -n "$TYPE" ] ; then
305 error "missing argument: type"
306 error "Try \`sqlbackup.sh --help' for more information."
309 error "invalid argument: type -- $TYPE"
310 error "Try \`sqlbackup.sh --help' for more information."
314 if ! ( [ -z "$SQLBACKUP_TYPE" ] ||
315 [ "$SQLBACKUP_TYPE" == "date" ] ||
316 [ "$SQLBACKUP_TYPE" == "rotate" ] ) ; then
317 error "invalid argument: name-methode -- $SQLBACKUP_TYPE"
318 error "Try \`sqlbackup.sh --help' for more information."
322 if ! ( [ -z "$SQLBACKUP_OVERWRITE" ] ||
323 [ "$SQLBACKUP_OVERWRITE" == "no" ] ||
324 [ "$SQLBACKUP_OVERWRITE" == "rename" ] ||
325 [ "$SQLBACKUP_OVERWRITE" == "overwrite" ] ); then
326 error "invalid argument: allow-ovewrite -- $SQLBACKUP_OVERWRITE"
327 error "Try \`sqlbackup.sh --help' for more information."
333 # a quick intro to the software, displayed when no params found
335 echo "Usage: sqlbackup.sh [OPTION] -t TYPE
337 sqlbackup.sh is a script used by alternc for sql backup
339 Mandatory arguments to long options are mandatory for short options too.
340 -v, --verbose set verbose mode on
341 -d, --debug set debug mode on
342 -n, --name-method METHOD set the method type for files' name
343 -a, --allow-override OVERRIDE specify the behaviour if backup files already exist
344 -t, --type TYPE set backup type
345 -h, --help display this help and exit
347 the TYPE arguments specify type of backup. Here are the values:
349 daily Execute a daily backup on all databases set to daily backup
350 weekly Execute a daily backup on all databases set to weekly backup
352 the METHOD argument the type for files' name. Here are the values:
354 date insert in the backup file's name the date of the backup
356 rotate rename file as file.<number><extension> where <number>
359 the OVERRIDE argument the behaviour of the script if a backup file already exist.
362 no if a backup file already exist, no backup done
363 rename if a backup file already exist, add an extension to the new
366 overwrite if a backup file already exist, overwrite it with the new
371 # read all paramter before doing anything before
376 # select backup information from the alternc database in the db table
377 # all backup for the specify mode (daily or weekly)
379 # --batch : Print results with a tab as separator, each row on a new line.
380 # avoid seperator like "|" which are not usefull in a shell script
381 # need to set the IFS environment variable to "\t" (tabbulation) for
382 # the `read' command (indicate field separator by default `read'
384 # tail -n '+2' permit to skip the first line (legende line)
385 # execut dobck on all database found by the sql request
387 # the "<< EOF" mean send data to the command until EOF (end of file)
389 debug /usr/bin/mysql --defaults-file=/etc/alternc/my.cnf --batch
390 /usr/bin/mysql --defaults-file=/etc/alternc/my.cnf --batch << EOF | tail -n '+2' | dobck
391 SELECT login, pass, db, bck_history, bck_gzip, bck_dir
393 WHERE bck_mode=$mode;