I recently had to export a database with over 50MM rows, and while I love using PHPMyAdmin for most database operations, the tool’s export functionality tends to crap out on larger data sets.  This article outlines how to export a large MySQL database (with compression to save space!).

Using mysqldump

Enter mysqldump, a database backup program originally written by Igor Romanenko.  The program ships with most MySQL installations, and can be used via the following simple command, entered in your favorite CLI (e.g., Terminal on Mac).

[syntax type=”html|php|js|css”]> mysqldump -u [mysql_user] -p [db_name] | gzip > [destination]/[result_file].sql.gz[/syntax]

You will be prompted to enter the password associated with the mysql_user specified.  There will not be confirmation that the command executed successfully (except for finding the output file).

The mysqldump command will by default export a SQL file with statements for re-creating the database (tables, rows, and all).  Note that the following replacements must be made in the above command:

• [mysql_user] = a MySQL user with at least SELECT privileges on the database
• [db_name] = the name of the MySQL database to export
• [destination] = where the exported file should appear (for example, on a Mac’s desktop, “/Users/[username]/Desktop”)
• [result_file] = name of the result file (sans extension)

If you’re interested, here are some notes on how the command works:

• -u is a flag which says that the following string indicates the MySQL user name to use when connecting to the server
• -p indicates that a password should be used when connecting (it will be prompted on execution)
• | (the pipe) indicates that the output of the previous command should be fed directly as input into the following command.  This is used to compress (gzip) the mysqldump output.
• > indicates to direct output to a specific file.  Without the “>” character and the filename, the results will print right to the display, which is not what we want!

As a side-note, use the -h flag to indicate an external database server.

Exporting to CSV

If you’d like to export the database table data to CSV, you can also do that via mysqldump, though it will take a bit of jiggering.  Take a look at the following line:

[syntax type=”html|php|js|css”]> mysqldump -u [mysql_user] -p -t -T[destination] [db_name] –fields-enclosed-by=\” –fields-terminated-by=,[/syntax]

Again, you’ll need to replace the following:

• [mysql_user] = a MySQL user with at least SELECT privileges on the database
• [db_name] = the name of the MySQL database to export
• [destination] = where the exported file should appear (for example, on a Mac’s desktop, “/Users/[username]/Desktop”)

Some notes on this command:

• -t indicates that the .sql file should not be exported (we don’t care about reconstructing the MySQL tables – we only want a data export)
• -T[destination] indicates that a tab-separated version of the table data should be export, specifically to the [destination] path.
  • Note that no actual file name should be used (e.g., “/Users/djchoi/Desktop” vs. “/Users/djchoi/Desktop/file.txt”).  Instead, a file will be exported for each table in the database specified by [db_name], with the same name as the table name.
  • Also, it matters that there is no space between -T and the [destination]
• –fields-enclosed-by=\” and –fields-terminated-by=, ensure that the output is standard CSV, readable by Excel and whatnot.

This command will actually output a .txt file for each table, but you can simply change the extension to .csv and open the files in whatever spreadsheet program you prefer!

For MAMP users

If you’re using MAMP on Mac OS, you may get an error “mysqldump: command not found“.  This is because your system can’t find mysqldump.  The executable for MAMP’s installation of MySQL can be found at “/Applications/MAMP/Library/bin/mysqldump“, so simply replace the path in the above command like so:

[syntax type=”html|php|js|css”]> /Applications/MAMP/Library/bin/mysqldump -u [mysql_user] -p [db_name] | gzip > [destination]/[result_file].sql.gz[/syntax]

If you want to avoid typing the full path in the future, you can add an alias to your bash config file. (future quick hint coming soon).