Консольный дампер

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

#!/bin/sh
##
## @Author: Anton Pribora, //anton-pribora.ru, 2009
##

help() {
  cat <<EOF
This is alternative mysql dumper. It's have following options:
  $0 [-h host] [-u user] [-p password] [-f charset] [-t charset] (db_name | -l | --help)
  
Optional options:
    -h host       MySQL host
    -u user       MySQL user name
    -p password   MySQL user password
    -f charset    Replace FROM charset
    -t charset    Replcae TO charset
    
Main options:
    db_name       Dump db_name database
    -l            Show all databases
    --help        Show this help
    
Author - Anton Pribora, //anton-pribora.ru, 2009
EOF
  exit
}

[ $# -eq 0 ] && help

mysql_run() {
  ${MYSQL_COMMAND} $@
  if [ $? -ne 0 ]; then 
    echo "$0: MySQL ERROR occur. Try to use --help option." 1>&2;
    exit 1;
  fi
}

show_version() {
  echo 'select version()' | mysql_run
}

show_databases() {
  echo "show databases" | mysql_run
}

show_tables() {
  echo "show tables from \`$1\`" | mysql_run
}

dump_create_table() {
  echo "show create table \`$1\`.\`$2\`" | mysql_run | awk 'BEGIN{FS="\t"}{gsub("\\\\n", "\n", $2); print $2";"}'
}

dump_table() {
  echo "select * from \`$1\`.\`$2\`" | mysql_run | awk -v table="$2" 'BEGIN{FS="\t"}{gsub("\"", "\\\""); gsub("\t", "\", \""); print "INSERT INTO `"table"` VALUES (\""$0"\");"}'
}

charset_filter() {
  sed -E -e "s/CHARSET=${REPLACE_FROM}/CHARSET=${REPLACE_TO}/g"
}

MYSQL_CLIENT=`which mysql`
MYSQL_OPTIONS="--skip-column-names"
MYSQL_DB=

REPLACE_FROM=latin1
REPLACE_TO=utf8

SHOW_DATABASES=

while [ $# -gt 0 ];
do
  case "$1" in
    -h) MYSQL_OPTIONS="${MYSQL_OPTIONS} --host='$2'"; shift 2;;
    -u) MYSQL_OPTIONS="${MYSQL_OPTIONS} --user='$2'"; shift 2;;
    -p) MYSQL_OPTIONS="${MYSQL_OPTIONS} -p$2"; shift 2;;
    -f) REPLACE_FROM=$2; shift 2;;
    -t) REPLACE_TO=$2; shift 2;;
    -l|-1) SHOW_DATABASES="1"; shift;;
    --help) help; shift;;
     *) MYSQL_DB=$1; shift;;
  esac
done

MYSQL_COMMAND="${MYSQL_CLIENT} ${MYSQL_OPTIONS}"

if [ -n "${SHOW_DATABASES}" ]; then show_databases; exit; fi

echo -e "--\n-- MySQL version: `show_version`\n-- Database: ${MYSQL_DB}\n-- Date: `date`\n-- \n"

for TABLE in `show_tables ${MYSQL_DB}`
do
  echo -e "-- \n-- Table \`${TABLE}\` structure\n-- "
  dump_create_table ${MYSQL_DB} ${TABLE} | charset_filter
  echo 
  
  echo -e "-- \n-- Table \`${TABLE}\` dump\n-- "
  dump_table ${MYSQL_DB} ${TABLE}
  echo 
done