MySQL data sample

Hi,

so today I didn't find any (well at least without iterating through mysqldump) way to export a subset of a database tables so I wrote the following bash script:

partial-mysqldump

1. usage() { echo "Usage: $0 [-h <host>] [-u <user>] [-p <pass>] [-d <database>] [-t [nametables]] [-f <output_file>] [-l <limit>]"1>&2; exit 1; }
3. while getopts ":t:f:d:l:p:u:h" opt; do
4.     case "${opt}" in
5.         f)
6.             f=${OPTARG}
7.             ;;
8.         d)
9.             d=${OPTARG}
10.             ;;
11.         p)
12.             p=${OPTARG}
13.             ;;
14.         h)
15.             h=${OPTARG}
16.             ;;
17.         u)
18.             u=${OPTARG}
19.             ;;
20.         l)
21.             l=${OPTARG}
22.             ;;
23.         t)
24.             t+=("$OPTARG")
25.             ;;
26.         *)
27. echo
28. echo "[ERROR]: Unknown, ignoring ${OPTARG}"
29.             usage
30.             ;;
31.     esac
32. done
33. shift $((OPTIND-1))
35. if [ -z "${h}" ]; then
36.     h='127.0.0.1'
37. fi
39. if [ -z "${l}" ]; then
40.     limit=''
41. else
42.     limit="where=\"1 limit $l\""
43. fi
44. if [ -z "${d}" ] || [ -z "${t}" ] || [ -z "${f}" ]; then
45.     usage
46. else
47.    for val in ${t[@]}; do
48.      mysqldump \
49.        -h $h \
50.        -u $u \
51.        -p$p \
52.        --no-create-info \
53.        --databases $d \
54.        --table $val \
55.        --lock-tables=false \
56.        $limit \
57.      >> $f
58.    done
59.    gzip -9 -c $f > $f.gz; rm $f
60. fi

so now, i can just limit my data and export what I want.

./partial-mysqldump -u <user> -h <host> -p <password> -d <database> -t <table1> <table2> <table3> -f test -l 1000

will dump first 1000 rows from table1, table2 and table3.

The problema with mysqldump --where="1 limit <yourlimit>" option is that it counts the overall result set and not a limit for each table.

Cheers
Tiago