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. 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 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=, --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).