Export a MySQL Database with mysqldump in a similar way as phpMyAdmin

Estimated reading time of this article: 2 minutes

phpMyAdmin allows to export MySQL databases in an easy and versatile way. These DB exports can easily imported into other databases, e.g. from a production DB to a local development DB. However, MySQL dumps created by mysqldump cannot be imported easily into other databases. I've written a bash script which exports MySQL DBs in a similar way as phpMyAdmin.

I'm using triggers in my DBs. I use the pattern as described in MySQL disable all triggers. Disabling of triggers is included in the commands below.

MySQL Export DB Data and Structures

#!/bin/bash

db="DB_NAME"
username="DB_username"
DATE=`date +"%Y%m%d_%H%M%S"`
DUMP_FILE="dbdump_${db}_$DATE.sql.gz"
logfile="dbdump.log"

(set -o pipefail; mysqldump -u$username --databases $db --dump-date --hex-blob --complete-insert --skip-lock-tables --single-transaction --routines --log-error=$logfile 2>>$logfile \
| sed -r "s/^\s*USE.*;/-- Created: `date +"%d.%m.%Y %T"`\n\n\0\n\nSET @disable_triggers = 1; -- Disable triggers/i" \
| sed -e "\$aSET @disable_triggers = NULL; -- Enable triggers" \
| gzip -9 >$DUMP_FILE 2>>$logfile)

MySQL Export DB Data

Truncate tables before INSERT statements.

#!/bin/bash

db="DB_NAME"
username="DB_username"
DATE=`date +"%Y%m%d_%H%M%S"`
DUMP_FILE="dbdump_${db}_$DATE.sql.gz"
logfile="dbdump.log"

(set -o pipefail; mysqldump -u$username --databases $db --dump-date --hex-blob --complete-insert --skip-lock-tables --single-transaction --no-create-db --no-create-info --skip-triggers --log-error=$logfile 2>>$logfile \
| sed -r "s/^\s*USE.*;/-- Created: `date +"%d.%m.%Y %T"`\n\n-- \0 -- Disable setting of original DB\n\nSET @disable_triggers = 1; -- Disable triggers/i" \
| sed -r 's/^\s*LOCK TABLES (`[^`]+`) WRITE;/\0\nTRUNCATE \1; -- Clear tables before insert/ig' \
| sed -e "\$aSET @disable_triggers = NULL; -- Enable triggers" \
| gzip -9 >$DUMP_FILE 2>>$logfile)

MySQL Export DB Structures

Exports DB structures. MySQL conditional execution comments are replaced by unconditional (i.e. normal) statements.

Note: MySQL conditional execution comments could be multiline. Non greedy patterns cannot be matched with SED, only with Perl.

#!/bin/bash

db="DB_NAME"
username="DB_username"
DATE=`date +"%Y%m%d_%H%M%S"`
DUMP_FILE="dbdump_${db}_$DATE.sql"
logfile="dbdump.log"

(set -o pipefail; mysqldump -u$username --databases $db --dump-date --no-data --skip-lock-tables --routines --log-error=$logfile | perl -0 -pe 's|/\*![0-5][0-9]{4} (.*?)\*/|\1|sg' >$DUMP_FILE 2>>$logfile)

Check Success after Export

Write the following code immediately after the mysqldump command, otherwise the exit state will be lost.

if (($? > 0)); then
  echo "ERROR"
  less $logfile
  exit 1
else
  echo "SUCCESS"
fi
(aktualisiert am ) –