Mostly, a dump of a db is wanted in SQL. In case of emergency import file. But some people do not comprehend SQL or their SQL doesn’t like your SQL and everything goes down the drain. So there is the CSV or Comma-separated values file. As the name says, it separates the values by commas (and more if needed). Since it’s so dead simple, you will get a lot of different tools and program languages that will make life easy for you to re-import or just search in it. Microsoft Excel and Openoffice Calc both can handle the format as well, so for a quick look, this will do very nicely.
But there is no simple –csv switch in mysqldump, your weapon of choice for these tasks. So here the command that will allow you to do what you are after:
mysqldump -p -u USER -T DIRECTORY --fields-enclosed-by=\" --fields-terminated-by=, DATABASESo this is the short version, and here what it all means:
- -p : Asks for a password, as most users have one. If you don’t specify this on a database with a user/password, it will error
- -u USER : Replace USER with your actual username to connect to the database.
- -T DIRECTORY : This creates a tab delimited file. Not what we wanted but it’s the base we need.
- –fields-enclosed-by=\” : Will add ” characters around the fields. This will allow CSV implementations to find everything that fits together. You will need that backslash or it wont run.
- –fields-terminated-by=, : The so much sought after comma. This replaces the tab and puts a comma in its place, which, you guessed it, creates the CSV file.
- DATABASE : Well you know, the thing this is all about…
To actually be able to do it though, you will need the FILE privilege on this database. Armed with this, you should be able to do your CSV exports easy now.