A better mysqldump for your projects

Published on October 9, 2008 at 12 pm by Aleksandr Tsertkov

I would like to share with you a simple bash script for dumping MySQL table data and structure separately.

Basically it’s a mysqldump wrapper. It allows you to specify tables which data will be ignored in a “data” mode and removes AUTO_INCREMENT automatically added by mysqldump to CREATE TABLE clauses in a “structure” mode.

I usually have this script in tools/ folder of my project and using it this way:

$ tools/sqldump.sh structure > sqldumps/structure.sql
$ tools/sqldump.sh data > sqldumps/data.sql


The script:

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

#!/bin/bash
PATH=/bin:/usr/bin
 
HOSTNAME=localhost
USERNAME=username
PASSWORD=password
DATABASE=database
# list of tables to be ignored in "data" mode
DATA_IGNORE=( table1 table2 table2 )
 
# check input parameters
if [ "$#" -le 0 ]; then
    echo "Usage:"
    echo "`basename $0` data|structure"
    exit 1
fi
 
case "$1" in
    'structure')
        mysqldump \
            --host=$HOSTNAME --user=$USERNAME -p$PASSWORD \
            --skip-comments \
            --add-drop-table \
            --skip-add-locks \
            --skip-disable-keys \
            --skip-dump-date \
            --no-data \
            $DATABASE | sed 's/ AUTO_INCREMENT=[0-9]\+//'
    ;;
 
    'data')
        # construct --ignore-table options
        COUNT=${#DATA_IGNORE[@]}
        IGNORE_TABLES=""
        for (( i=0;i<$COUNT;i++ )); do
            IGNORE_TABLES="$IGNORE_TABLES --ignore-table=$DATABASE.${DATA_IGNORE[$i]}"
        done
 
        mysqldump \
            --host=$HOSTNAME --user=$USERNAME -p$PASSWORD \
            --skip-comments \
            --skip-add-locks \
            --skip-disable-keys \
            --skip-dump-date \
            --no-create-info \
            --extended-insert=0 \
            $IGNORE_TABLES $DATABASE
    ;;
 
    *)
        echo 'Unknown parameter'
        exit 1
    ;;
esac

Update (October 13, 2008):

Option “–extended-insert=0″ added to disable multiline-row INSERT that include all table data in one clause. Now there are separate INSERTs for every table row in the a dump.

Option “–skip-comments” added to disable box related comments (hostname, MySQL version, database name, etc.).

These changes makes SQL more readable and possible to make meaningful diffs between revisions.

Categories: linux/unix, programming
Tags: , ,

One Response

  1. 1.
    Tiago commented on February 7th, 2009 at 12:02 am

    Thanks for sharing =)

Leave a Reply