mysqldump and mysql import scripts

a small collection of mysql dump scripts

« Back to overview

Prerequisites

The scripts below require some environment variables to be set (such as the mysql host), to make them more reusable.
Take care not to mix up database names though.

You can set the environment variables on the command line

MYSQL_HOST=..
MYSQL_DATABASE=...
MYSQL_USER=...
MYSQL_PASSWORD=...

Creating a mysql dump

Below are some examples of creating mysqldumps for specific purposes.

mysqldump full db dump; works nice on aurora serverless

mysqldump --add-drop-table --single-transaction -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_HOST" $MYSQL_DATABASE > $MYSQL_DATABASE-$( date "+%Y%m%d-%H%M%S" ).sql

mysqldump full db dump; works nice on fortrabbit

mysqldump  --set-gtid-purged=OFF --add-drop-table --lock-tables=false --single-transaction=true --disable-keys=true  --quick -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" $MYSQL_DATABASE > $MYSQL_DATABASE_$( date "+%Y%m%d-%H%M%S" ).sql

Importing a mysqldump

By disabling foreign key checks when importing, you can prevent errors due to referential integrity (these occur because a table is dropped that might have relations with other tables, or records are inserted while the related record does not yet exist).

mysql import with ignored foreign key checks

mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" -u"$MYSQL_USER" -p$MYSQL_PASS -h"$MYSQL_HOST" $MYSQL_DATABASE < $MYDUMPNAME.sql

Dump data only from specific tables

mysqldump -uUSER -pPASSWORD -hHOST --skip-add-drop-table --no-create-info --single-transaction=true DBNAME TABLE1 TABLE2 > data.sql

mysqldump and mysql import scripts

Prerequisites

Creating a mysql dump

Importing a mysqldump

Also see: