Context Navigation

source: alternc/trunk/src/sqlbackup.sh @ 2257

Revision 2257, 16.5 KB checked in by anarcat, 5 years ago (diff)

complete rewrite of the sqlbackup.sh script, will be reworked and audited before release

Closes: #1081

Property svn:executable set to *

Line
1	#!/bin/bash
2
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.
7	# http://alternc.org/
8	# ----------------------------------------------------------------------
9	# Based on:
10	# Valentin Lacambre's web hosting softwares: http://altern.org/
11	# ----------------------------------------------------------------------
12	# LICENSE
13	#
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.
18	#
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.
23	#
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
33	# file
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
36	# for sync script
37	set -e
38
39	# Get mysql user and password :
40	. /etc/alternc/local.sh
41	. /etc/alternc/sqlbackup.conf
42
43	# get the date of the day
44	DATE=`date +"%Y%m%d"`
45
46	# echo function, used for output wrapping when run in daemon
47	# mode.
48	# usage: print [option] <message>
49	# without option, print <message> in any case on the stdout
50	#
51	# options :
52	# error : print <message> in any case and indicate that an error message
53	# debug : print <message> if debug mode is active
54	# info : print <message> if verbose mode is active
55	#
56	# notes :
57	# if backup running in daemon mode, printing in log file if an otpion
58	# is gived to the function
59	print() {
60
61	# if a log level is given to the print function
62	# 'error', 'info' or 'debug'
63	log_level=""
64	if [ "$1" == "error" ] \|\| [ "$1" == "info" ] \|\| [ "$1" == "debug" ];
65	then
66	# read it and remove it for arg list
67	log_level="$1"
68	shift
69	fi
70
71	# if
72	# - No log level is specified
73	# - Log level equal to 'error'
74	# => print in any case on stdout
75	# => add to log file as well if $DAEMON set to 'ON'
76	# - Log level equal to 'debug' and $DEBUG is set to on
77	# - Log level equal to 'info' and $VERBOSE set to 'ON'
78	# => print on log file if $DAEMON set to 'ON', on stdout if not
79	if [ -z "$log_level" ] \|\|
80	[ "$log_level" == "error" ] \|\|
81	[ "$DEBUG" == "ON" -a "$log_level" == "debug" ] \|\|
82	[ "$log_level" == "info" -a "$VERBOSE" == "ON" ] ;
83	then
84	if [ "$DAEMON" == "ON" ] ; then
85	# function without option must be print on stdout in anycase
86	# even if print in the log file
87	if [ -z "$log_level" ] \|\| [ "$log_level" == "error" ];
88	then
89	echo "$EXEC_CMD $log_level: $*"
90	fi
91	echo "`date +"%b %d %T"` `hostname` $EXEC_CMD $log_level: $*" >> $F_LOG
92	else
93	if [ -z "$log_level" ];
94	then
95	echo "$*"
96	else
97	echo "$log_level: $*"
98	fi
99	fi
100	fi
101
102	}
103
104	error() {
105	print "error" $*
106	}
107
108	info() {
109	print "info" $*
110	}
111	debug() {
112	print "debug" $*
113	}
114
115	function dobck() {
116	local ext
117	local i
118	local old_ifs
119
120	# mysql -B uses tab as a separator between fields, so we have to mess
121	# with IFS in order to get the correct behaviour
122	old_ifs="$IFS"
123	IFS=" "
124	# read parameter given by mysql
125	while read login pass db count compressed target_dir; do
126
127	debug "read $login \$pass $db $count $compressed $target_dir"
128	# restore $IFS after read parameter
129	IFS="$old_ifs"
130
131	# by default : DOBAKCUP set to yes
132	DO_BACKUP="YES"
133
134	if [ "$compressed" -eq 1 ]; then
135	ext=".gz"
136	else
137	ext=""
138	fi
139
140	# if $TYPE_NAME_BACKUP is set to "rotate" classical rotation files methode will be used
141	# use incrementale number in the name of files where the highest number indicate
142	# the oldest files
143	# if the rotate type is not set or set to date, the name of the export file will contain the date
144	# of the backup on won't be rotate by the classic rotate number
145	# usefull if you're using rsync or rsnapshop or everything base on rsync to avoir to copy
146	# rotate files which just change name
147	#
148	# ------------------------------------------------------------------ #
149	# the variable TYPE_NAME_BACKUP must be set in /etc/alternc/local.sh #
150	# ------------------------------------------------------------------ #
151	if [ $TYPE_NAME_BACKUP == "rotate" ]; then
152
153	i="$count"
154
155	# rotate all backup
156	while [ $i -gt 1 ] ; do
157
158	next_i=$(($i - 1))
159
160	if [ -e "${target_dir}/${db}.sql.${next_i}${ext}" ]; then
161	mv -f "${target_dir}/${db}.sql.${next_i}${ext}" \
162	"${target_dir}/${db}.sql.${i}${ext}" 2>/dev/null
163	fi
164	i=$next_i # loop should end here
165	done
166
167	# move most recently backup with a rotate file name
168	if [ -e "${target_dir}/${db}.sql${ext}" ]; then
169	mv -f "${target_dir}/${db}.sql${ext}" \
170	"${target_dir}/${db}.sql.${i}${ext}" 2>/dev/null
171	fi
172
173	name_backup_file="${db}"
174	else
175	# ---------------
176	# default methode
177	# ---------------
178	# calcul the mtime parameter for find
179	# $count is the number of backup to keep
180	# daily : if we are keeping X backup, deleting the file which has the mtime at X + 1 days
181	# weekly : if we are keeping X backup, deleting the file which has the mtime at (X + 1) * 7 day
182	# echo "last2del=( $count + 1 ) * $coef "
183	#
184	last2del=$(( ( $count + 1 ) * $coef ))
185
186	# find the oldest backup file need to be delete
187	# find ${target_dir} : in the target_dir
188	# -name \"${db}.sql.\" : All files like <db_name>.sql.
189	# -maxdepth 0 : only in the target dir (on not in the subdirectory)
190	# -mtime $last2del : files with the exact mtime set to $last2del
191	# daily : ( number of backup to keep + 1 ) days
192	# weekly : ( number of backup to keep + 1 ) * 7 days
193	# -exec rm -f {} \; : remove all files found
194	#
195	debug "find ${target_dir} -name \"${db}.*sql${ext}\" -maxdepth 1 -mtime +$last2del -exec rm -f {} \; -ls"
196	find ${target_dir} -name "${db}.*sql${ext}" -maxdepth 1 -mtime +${last2del} -exec rm -f {} \; -ls
197
198	# set the name of the backup file with the date of the day
199	name_backup_file="${db}.${DATE}"
200
201	fi
202
203	# if the backup exite and ALLOW_OVERWRITE_BACKUP is set to NO, cancel backup
204	if [ -f "${target_dir}/${name_backup_file}.sql${ext}" ] && [ "$ALLOW_OVERWRITE_BACKUP" == "no" ] ; then
205
206	info "sqlbackup.sh: ${target_dir}/${name_backup_file}.sql${ext}: already exist"
207	info " => no backup done as specify in allow-overwrite = $ALLOW_OVERWRITE_BACKUP"
208	DO_BACKUP="NO"
209
210	# if the backup exite and ALLOW_OVERWRITE_BACKUP is set to RENAME, add
211	elif [ -f "${target_dir}/${name_backup_file}.sql${ext}" ] && [ "$ALLOW_OVERWRITE_BACKUP" == "rename" ] ; then
212
213	info "sqlbackup.sh: ${target_dir}/${name_backup_file}.sql${ext}: already exist"
214	info " => renaming the new file as specify in allow-overwrite = $ALLOW_OVERWRITE_BACKUP"
215	hours=`date +"%H%M"`
216	name_backup_file="${name_backup_file}.${hours}"
217
218	# if the backup exite and ALLOW_OVERWRITE_BACKUP is set OVERWRITE, add
219	elif [ -f "${target_dir}/${name_backup_file}.sql${ext}" ] && [ "$ALLOW_OVERWRITE_BACKUP" == "overwrite" ] ; then
220
221	info "sqlbackup.sh: ${target_dir}/${name_backup_file}.sql${ext}: already exist"
222	info " => overwrite file as specify in allow-overwrite = $ALLOW_OVERWRITE_BACKUP"
223
224	fi
225
226	###
227	# mysqldump Option :
228	# --add-drop-table : Add a 'drop table' before each create.
229	# usefull if you want to override the database without delete table before
230	# this is need to used restore from the alternc interface
231	# --allow-keywords : Allow creation of column names that are keywords.
232	#
233	# --quote-names : Quote table and column names with `
234	# Usefull if you have space in table or column names
235	# --force : Continue even if we get an sql-error.
236	# To avoid end of script during backup script execution
237	# Allow script to backup other database if one of the have an error
238	# --quick : Don't buffer query, dump directly to stdout.
239	# optimisation option
240	# --all : Include all MySQL specific create options.
241	# Permit keep information like type or comment
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	# remove because lock is allow for alternc user
249	if [ "$compressed" -eq 1 ] && [ "$DO_BACKUP" == "YES" ]; then
250	debug "msqldump -h\"$MYSQL_HOST\" -u\"$login\" -p\"XXXX\" \"$db\" --add-drop-table --allow-keywords -Q -f -q -a -e \ "
251	debug " \| gzip -c > \"${target_dir}/${name_backup_file}.sql${ext}\""
252
253	mysqldump -h"$MYSQL_HOST" -u"$login" -p"$pass" "$db" \
254	--add-drop-table \
255	--allow-keywords \
256	--quote-names \
257	--force \
258	--quick \
259	--all \
260	--extended-insert \
261	\| gzip -c > "${target_dir}/${name_backup_file}.sql${ext}"
262
263	elif [ "$DO_BACKUP" == "YES" ] ; then
264	debug "mysqldump -h\"$MYSQL_HOST\" -u\"$login\" -p\"XXXX\" \"$db\" --add-drop-table --allow-keywords -Q -f -q -a -e \ "
265	debug " > \"${target_dir}/${name_backup_file}.sql\""
266
267	mysqldump -h"$MYSQL_HOST" -u"$login" -p"$pass" "$db" \
268	--add-drop-table \
269	--allow-keywords \
270	--quote-names \
271	--force \
272	--quick \
273	--all \
274	--extended-insert \
275	> "${target_dir}/${name_backup_file}.sql"
276	fi
277
278	IFS=" "
279	done
280	IFS="$old_ifs"
281	}
282
283	# read_parameters gets all command-line arguments and analyzes them
284	#
285	# return:
286	read_parameters() {
287
288	# for all parameter give to the script
289	while [ "$1" != "" ] ; do
290	case "$1" in
291	-h\|--help) usage; exit ;;
292	-v\|--verbose) VERBOSE="ON" ;;
293	-d\|--debug) DEBUG="ON" ;;
294	-t\|--type) shift; TYPE="$1";;
295	-n\|--name-methode) shift; TYPE_NAME_BACKUP="$1";;
296	-a\|--allow-ovewrite) shift; ALLOW_OVERWRITE_BACKUP="$1" ;;
297	*)
298	error "invalide option -- $1"
299	error "Try \`sqlbackup.sh --help' for more information."
300	exit ;;
301	esac
302	# in case of no argument give to an option
303	# shift execute an exit if already empty
304	# add test to avoid this at least to print error message
305	[ "$1" != "" ] && shift
306	done
307
308	debug "TYPE = $TYPE"
309	debug "TYPE_NAME_BACKUP = $TYPE_NAME_BACKUP"
310	debug "ALLOW_OVERWRITE_BACKUP = $ALLOW_OVERWRITE_BACKUP"
311
312
313	# check options
314	if [ "$TYPE" == "daily" ]; then
315	# Daily :
316	mode=2
317	coef=1
318	elif [ "$TYPE" == "weekly" ] ; then
319	# Weekly:
320	mode=1
321	coef=7
322	elif [ -n "$TYPE" ] ; then
323	error "missing argument: type"
324	error "Try \`sqlbackup.sh --help' for more information."
325	exit
326	else
327	error "invalide argument: type -- $TYPE"
328	error "Try \`sqlbackup.sh --help' for more information."
329	exit
330	fi
331
332	if ! ( [ -z "$TYPE_NAME_BACKUP" ] \|\|
333	[ "$TYPE_NAME_BACKUP" == "date" ] \|\|
334	[ "$TYPE_NAME_BACKUP" == "rotate" ] ) ; then
335	error "invalide argument: name-methode -- $TYPE_NAME_BACKUP"
336	error "Try \`sqlbackup.sh --help' for more information."
337	exit
338	fi
339
340	if ! ( [ -z "$ALLOW_OVERWRITE_BACKUP" ] \|\|
341	[ "$ALLOW_OVERWRITE_BACKUP" == "no" ] \|\|
342	[ "$ALLOW_OVERWRITE_BACKUP" == "rename" ] \|\|
343	[ "$ALLOW_OVERWRITE_BACKUP" == "overwrite" ] ); then
344	error "invalide argument: allow-ovewrite -- $ALLOW_OVERWRITE_BACKUP"
345	error "Try \`sqlbackup.sh --help' for more information."
346	exit
347	fi
348
349	}
350
351	# a quick intro to the software, displayed when no params found
352	usage() {
353	echo "Usage: sqlbackup.sh [OPTION] -t TYPE
354
355	sqlbackup.sh is a script used by alternc for sql backup
356
357	Mandatory arguments to long options are mandatory for short options too.
358	-v, --verbose set verbose mode on
359	-d, --debug set debug mode on
360	-n, --name-method METHOD set the method type for files' name
361	-a, --allow-override OVERRIDE specify the behaviour if backup files already exist
362	-t, --type TYPE set backup type
363	-h, --help display this help and exit
364
365	the TYPE arguments specify type of backup. Here are the values:
366
367	daily Execute a daily backup on all databases set to daily backup
368	weekly Execute a daily backup on all databases set to weekly backup
369
370	the METHOD argument the type for files' name. Here are the values:
371
372	date insert in the backup file's name the date of the backup
373	(default value)
374	rotate rename file as file.<number><extension> where <number>
375	is incremented
376
377	the OVERRIDE argument the behaviour of the script if a backup file already exist.
378	Here are the values:
379
380	no if a backup file already exist, no backup done
381	rename if a backup file already exist, add an extension to the new
382	backup file
383
384	overwrite if a backup file already exist, overwrite it with the new
385	backup"
386
387	}
388	debug begin $@
389	# read all paramter before doing anything before
390	read_parameters $@
391	debug end
392
393	###
394	# select backup information from the alternc database in the db table
395	# all backup for the specify mode (daily or weekly)
396	# option :
397	# --batch : Print results with a tab as separator, each row on a new line.
398	# avoid seperator like "\|" which are not usefull in a shell script
399	# need to set the IFS environment variable to "\t" (tabbulation) for
400	# the `read' command (indicate field separator by default `read'
401	# use space)
402	# tail -n '+2' permit to skip the first line (legende line)
403	# execut dobck on all database found by the sql request
404	#
405	# the "<< EOF" mean send data to the command until EOF (end of file)
406	#
407	debug /usr/bin/mysql -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"XXXX" "$MYSQL_DATABASE" -B
408	/usr/bin/mysql -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASS" \
409	"$MYSQL_DATABASE" --batch << EOF \| tail -n '+2' \| dobck
410	SELECT login, pass, db, bck_history, bck_gzip, bck_dir
411	FROM db
412	WHERE bck_mode=$mode;
413	EOF
414
415	# vim: et sw=4

Note: See TracBrowser for help on using the repository browser.

Download in other formats: