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

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, http://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, http://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}` structuren-- "
 dump_create_table ${MYSQL_DB} ${TABLE} | charset_filter
 echo 
 
 echo -e "-- n-- Table `${TABLE}` dumpn-- "
 dump_table ${MYSQL_DB} ${TABLE}
 echo 
done