| 32 | | dobck() { |
|---|
| | 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() { |
|---|
| 49 | | i="$count" |
|---|
| 50 | | if [ ! -d "$target_dir" ] ; then |
|---|
| 51 | | echo "$target_dir is not a directory, skipping" >&2 |
|---|
| 52 | | continue |
|---|
| | 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 | |
|---|
| 54 | | while [ "$i" -gt 1 ]; do |
|---|
| 55 | | next_i=$(($i - 1)) |
|---|
| 56 | | mv -f "${target_dir}/${db}.sql.${next_i}${ext}" \ |
|---|
| 57 | | "${target_dir}/${db}.sql.${i}${ext}" 2>/dev/null || true |
|---|
| 58 | | i=$next_i # loop should end here |
|---|
| 59 | | done |
|---|
| 60 | | mv -f "${target_dir}/${db}.sql${ext}" \ |
|---|
| 61 | | "${target_dir}/${db}.sql.${i}${ext}" 2>/dev/null || true |
|---|
| 62 | | if [ "$compressed" -eq 1 ]; then |
|---|
| 63 | | mysqldump --defaults-file=/etc/alternc/my.cnf ${db} --add-drop-table --allow-keywords -Q -f -q -a -e | |
|---|
| 64 | | gzip -c > "${target_dir}/${db}.sql${ext}" |
|---|
| 65 | | else |
|---|
| 66 | | mysqldump --defaults-file=/etc/alternc/my.cnf ${db} --add-drop-table --allow-keywords -Q -f -q -a -e \ |
|---|
| 67 | | > "${target_dir}/${db}.sql" |
|---|
| | 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" |
|---|
| 75 | | if [ "$1" = "daily" ]; then |
|---|
| 76 | | # Daily : |
|---|
| 77 | | mode=2 |
|---|
| 78 | | else |
|---|
| 79 | | # weekly: |
|---|
| 80 | | mode=1 |
|---|
| 81 | | fi |
|---|
| 82 | | |
|---|
| 83 | | /usr/bin/mysql --defaults-file=/etc/alternc/my.cnf -B << EOF | tail -n '+2' | dobck |
|---|
| | 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 |
|---|
