summaryrefslogtreecommitdiff
path: root/localdumpsql
blob: 5b4d5cbbcea9d054078fe914339256e212db4b05 (plain)
  1. #!/bin/bash
  2. #
  3. # /usr/local/sbin/localdumpsql
  4. # Copyright 2001-2006 Jonas Smedegaard <dr@jones.dk>
  5. #
  6. # $Id: localdumpsql,v 1.17 2007-06-10 09:59:26 jonas Exp $
  7. #
  8. # MySQL/PostgreSQL maintenance script
  9. #
  10. # Automagically runs when symlinked to /etc/cron.{daily,weekly,monthly}/
  11. #
  12. # halt on errors
  13. set -e
  14. # Be paranoid about access to created files
  15. umask 277
  16. function usage() {
  17. echo "Usage: `basename $0` daily|weekly|monthly|<whatever> [mysql|postgres [<user> [<db>]]]"
  18. echo " If sqltype, user and db are not provided, all are included"
  19. echo " Tip: Automagically runs when symlinked to /etc/cron.{daily,weekly,monthly}/"
  20. exit 1
  21. }
  22. # automagically configure when run from cron dirs
  23. case `dirname "$0"` in
  24. /etc/cron.daily)
  25. stamp=""
  26. ;;
  27. /etc/cron.weekly)
  28. stamp="weekly"
  29. ;;
  30. /etc/cron.monthly)
  31. stamp="monthly"
  32. ;;
  33. *)
  34. if [ $# -lt 1 -o $# -gt 4 ]; then
  35. usage
  36. fi
  37. stamp="$1"
  38. sqltypes="$2"
  39. users="$3"
  40. databases="$4"
  41. ;;
  42. esac
  43. # Default is all sqltypes
  44. [ -z $sqltypes ] && sqltypes="mysql postgres"
  45. # Define paths
  46. mysql_bin=/usr/bin/mysqldump
  47. postgres_bin=/usr/bin/pg_dump
  48. # Define routines
  49. function gethome_user() { getent passwd "$1" | awk -F: '{print $6}' | head -n 1; }
  50. function getbackupdir_user() { echo "`gethome_user \"$1\"`/backup"; }
  51. function mysqlvalid() { [ -x $mysql_bin ]; }
  52. #function mysqlpasswd_user() { grep password "`gethome_user $1`"/.my.cnf | awk -F= '{print $2}' | awk '{print $1}' | head -n 1; }
  53. function mysql_users() { if [ -n "$1" ]; then echo "$1"; else mysql -uroot mysql -e "select User from user;" | grep -Ev '^(User|debian-sys-maint|root|.+-admin)$' | sort -u; fi; }
  54. function mysql_db_user() { if [ -n "$1" ]; then echo "$1"; else mysql -u root mysql -re "select Db from db where User='$2';" | grep -v '^Db$' | sort -u; fi; }
  55. #function mysqldump_user_db() { $mysql_bin --opt --user="$1" --password="`mysqlpasswd_user $1`" "$2"; }
  56. #function mysqldump_user_db() { $mysql_bin --opt --user="root" --password="`mysqlpasswd_user root`" "$2"; }
  57. #function mysqldump_user_db() { $mysql_bin --opt --user="root" "$2"; }
  58. function mysqldump_user_db() { $mysql_bin --add-drop-table --add-locks --all-databases --quick --lock-tables --disable-keys --user="root" "$2"; } # Avoid --extended-insert
  59. function postgresvalid() { [ -x $postgres_bin ]; }
  60. function postgres_users() { echo "root"; } #FIXME
  61. function postgres_db_user() { if [ -n "$1" ]; then echo "$1"; else su -s /bin/sh postgres -c "/usr/bin/psql -t -c 'select datname from pg_database order by datname' -d template1 | sed -e 's/ //' | grep -v '^template[01]$'"; fi; }
  62. function postgresdump_user_db() { su -s /bin/sh postgres -c "$postgres_bin -O $2"; }
  63. # Check for valid input
  64. for sqltype in $sqltypes; do
  65. case "$sqltype" in
  66. mysql|postgres)
  67. ;;
  68. *)
  69. usage
  70. ;;
  71. esac
  72. done
  73. for sqltype in $sqltypes; do
  74. if ${sqltype}valid; then
  75. for user in `${sqltype}_users "$users"`; do
  76. home="`gethome_user \"$user\"`"
  77. test -d "$home" || continue
  78. for dir in "`getbackupdir_user \"$user\"`" "`getbackupdir_user \"$user\"`/$sqltype"; do
  79. targetdir="$dir"
  80. if [ ! -d "$targetdir" ]; then
  81. mkdir "$targetdir"
  82. fi
  83. chown "$user": "$targetdir"
  84. chmod u=rX,go= "$targetdir"
  85. done
  86. for db in `${sqltype}_db_user "$databases" "$user"`; do
  87. targetfile="$targetdir/$db${stamp:+.$stamp}.sql"
  88. (set +e; ${sqltype}dump_user_db "$user" "$db"; set -e) > "$targetfile"
  89. chown "$user": "$targetfile"
  90. chmod u=r,go= "$targetfile"
  91. done
  92. done
  93. fi
  94. done