Tag Archives: mysqldump

How to Exclude Tables from a mysqldump SQL Export Software & Sites 15 MAY 2013

20090910-MySQL-logoWhen backing up or copying across a MySQL database, the command line mysqldump executable is invaluable in automating the creation of SQL dump files which can be used to create or populate database instances.

Now there are a myriad of options available to this little command, but the one I want to highlight today is the ability to ignore specific tables when doing a mysqldump of an entire database. This is particularly useful if you have an overly large table which doesn’t play nicely with an import process, say for example if you were storing base-encoded images in your database. –ignore-table is the switch, and in practice it looks like this:

mysqldump -u username -p database --ignore-table=database.table1 --ignore-table=database.table2 > database.sql

As you can see from the line above, it is possible to specify multiple tables to exclude by simply passing it in multiple times. Also worthwhile to note is the fact that you do need to prefix the table which is to be omitted with the database name. Failing to do this will result in an Illegal Usage error from mysqldump.

Nifty.

Ubuntu Terminal: Efficient Way to Copy Across a MySQL Database from One Server to Another CodeUnit 23 MAY 2011

I often need to pull down a copy of a live MySQL database instance for development work on my local machine. Because my access to bandwidth is limited, I need to come up with the most efficient way of achieving this, and my method for achieving this is chronicled below – mainly so that I can refer to it when I forget!

Anyway, the process is simple enough to explain. First, we ssh into the target server and we use mysqldump to create a SQL dump text file of the required MySQL database. Next, we compress it using the powerful 7-Zip to create a nice and compact 7z file. Exit ssh. To pull it down from the server, we use plain old scp and do a secure copy down to our local machine. Next we uncompress the newly copied 7z archive and once extracted, import it into our local MySQL server using the mysql command.

Nifty.

And a nice example for copy and paste purposes:

ssh #REMOTEUSERNAME#@#HOSTNAME#

mysqldump -u #USERNAME# -p#PASSWORD# -c --add-drop-table --all --quick #DATABASE# > dump.sql 

7za a -t7z -m0=lzma -mx=9 -mfb=64 -md=32m -ms=on dump.7z dump.sql 

exit

scp -v -P #PORT# #REMOTEUSERNAME#@#HOSTNAME#:/home/server/dump.7z /home/craig/dumpscp.7z 

7za e /home/craig/dumpscp.7z 

mysql -u #USERNAME# -p#PASSWORD# database < /home/craig/dump.sql

Ubuntu: A Bash Script to Backup All MySQL Databases Running on a Server CodeUnit 28 JUL 2010

The following bash script is written to automate the process of backing up all your various MySQL databases running on either a local or remote MySQL server, using the useful mysqldump utility to do the actual backups.

What the script does is pretty simple to understand really.

First, you define all your server connections. Then it queries the server to find out which databases are currently running in the MySQL Server instance. Armed with this list, it runs through them all (ignoring the ones you specified on the ignore list) and pulls down a mysqldump of each database, gzipping it to its final backup file name.

Simple eh? So let’s see it then:

#!/bin/bash
MyUSER="mysql_user_account"
MyPASS="mysql_user_account_password"
MyHOST="localhost"

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/var/mysql_backups"

# Main directory where backup will be stored
MBD="$DEST/sql_dumps"

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%Y%m%d-%H%M%S")"

# File to store current backup file
FILE=""

# Store list of databases
DBS=""

# DO NOT BACKUP these databases
IGGY="test"

[ ! -d $MBD ] && mkdir -p $MBD || :
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

echo "Launching backup script at $(date)"

for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];    then
        for i in $IGGY        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then
	FILE="$MBD/$db.$MyHOST.$NOW.sql.gz"
        # do all inone job in pipe,
        # connect to mysql using mysqldump for select mysql database
        # and pipe it out to gz file in backup dir
	echo "Starting backup process for $db (espreports.com) [$(date)]"
	$MYSQLDUMP --opt --compress --single-transaction -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
	echo "-- Complete ($FILE) [$(date)] --"
    fi
done

echo "Backup script completed execution at $(date)"

And we’re done. Nifty. (And damn useful to boot!)