A Simple Terrain Model
Nasa's Space Shuttle Atlantis Transiting the Sun
Installing Proprietary nVidia Drivers on Ubuntu Linux
Compiling and Installing Freespace 2 on Ubuntu Linux
Garmin Edge 205 GPS Cycle Training Tool
Reading Email with PHP and IMAP/POP3
Journal Language Quiz Calendar
 

Taking a Dump with MySQL

 Mon 01 Dec 2008
4:04AM
compton
The mysqldump command is widely documented because it really can be very useful, and also because it offers a blistering array of options. It's most obvious use is for dumping databases for backup or relocation purposes:

mysqldump [options] [database [tables]]


It can either dump a whole database in one go, or you can specify a list of specific tables to be dumped.

Dumps are simply SQL statements which when executed will recreate the database (or specific tables or rows). This makes the command potentially useful for cross-platform data-exports, should that ever be important. Consequently though, mysqldump's output will benefit hugely from compression, which is easily accomplished by piping the output into gzip:

mysqldump --extended-insert=FALSE --compact -c -C --single-transaction --add-drop-table --quick -u dbuser -p myDatabase myTable1 myTable2 myTable3 | gzip > export.sql.gz


So far so good. To import the file, you just need to stick it into mysql. If the SQL statements are in an uncompressed file, you can simply use the following:

mysql -u dbuser -p -h localhost myDatabase < dumpfile.sql


If gzipped, use this:

gunzip < dumpfile.sql.gz | mysql -u dbuser -p -h localhost myDatabase

Copy Selected Data between Mirrored Databases


mysqldump provides a quick way to copy selected items from one database to another e.g. from a live database to a development mirror. If you're just copying a few rows, you can copy and paste from a terminal on the live machine to a MySQL client on dev:

mysqldump --extended-insert=FALSE --compact --no-create-info -c -C --single-transaction --where='column=whatever' --quick -u dbuser -p myDatabase myTable1 myTable2 myTable3

Copying MySQL Databases over an SSH Connection


I got this cool tip from this chap's list of 25 SSH commands. Whenever you're copying data from one database server to another, you can stream it directly over SSH by using the lovely old pipe operator. Run the following on the machine where the source database is located:

mysqldump --compact -ceC --add-drop-table --single-transaction --quick -u dbuser -p myDatabase myTable1 | ssh -C user@destination "mysql -u dbuser -pPASSWORD myDatabase"


Note that we've got the MySQL password for the target server in the command - this is a potential security risk as it could be available in your history file. If this is likely to be an issue, there are ways around it, e.g. by storing the password in a file somewhere secure on the target machine and getting it from there (via cat), or by setting HISTCONTROL to ignorespace and beginning this command with a space.

Creating Reports


You can also use mysqldump to create simple CSV reports based on single database tables, like so:

mysqldump --compact -c -C --single-transaction --quick -u dbuser -p --where='column=whatever' --fields-terminated-by=, --fields-enclosed-by=\" --tab=/tmp myDatabase myTable1


Note the fields-terminated-by option which sets the comma as the field delimiter. Also, the CSV file will be saved in the directory specified by the --tab option. Also saved in this location will be a SQL file for the creation of the table (not sure if --no-create-info will work, use of the -r option followed by a name for the CSV file should though).
 
Leave Comment