Create CSV from mysqldump command

Hello.
I'm Mandai, in charge of Wild on the development team.

Do you ever want to edit MySQL data with Excel?
This time, I have summarized how to convert MySQL data to CSV and make it readable in Excel.

The mysqldump command is often used to write the contents of mysql to a file, but unless you specify otherwise, the output will be in SQL format.

This is fine when moving data to another DB, but what if you want to process this file with Excel?

The mysqldump command provides the --tab option to meet this request.

The version of MySQL prepared for this verification was MySQL Community Server 5.6.35, which was installed via the official mysql repository.

 

Try acquiring it using the mysqldump command alone

When acquiring csv using the mysqldump command, there are some errors that are often encountered.
That's something like this:

mysqldump -uroot -p --tab=/tmp hogehoge fugafuga
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

Even though I am exporting as root, for some reason I get an error.
What this means is that the MySQL server startup option has "--secure-file-priv", so files cannot be written to any directory other than the specified one.

Let's see how to check this.
First, log in to MySQL.

mysql> select @@global.secure_file_priv; +---------------------------+ | @@global.secure_file_priv | +---- -----------------------+ | /var/lib/mysql-files/ | +-------------- -------------+ 1 row in set (0.00 sec)

 
In the case of the official repository MySQL server, files can only be exported to the /var/lib/mysql-files/ directory by default.

No, you're such an idiot.
Up until now, I was able to export SQL.

I thought this too, but it's just redirecting the mysqldump command results to a file.
After all, mysqldump does not write to the file.

With the --tab option, mysqldump itself writes to the path specified after "=", so writing to a path other than the one specified by "@@global.secure_file_priv" will result in an error.

So, in this environment, the file output of the mysqldump command using the --tab option will be as follows.

mysqldump -uroot -p --tab=/var/lib/mysql-files/ hogehoge fugafuga

 
Now you can output tab-delimited CSV (TSV) to /var/lib/mysql-files/.

 

@@global.secure_file_priv path cannot be accessed or @@global.secure_file_priv cannot be modified

There will be times like this.

In that case, there is nothing you can do about it, so here are the steps you can take:
1. First get the SQL using mysqldump
2. Import the output SQL to a controllable MySQL server
3. Output CSV

If you have time, this is fine.
I don't have time, and I don't want to waste time.
In such a case, you can use grep and sed to convert SQL to CSV.

# tsv mysqldump -uroot -p -t [target schema] [target table] | grep 'INSERT INTO' | sed -e 's/INSERT INTO `.*` VALUES //' -e 's/);$// ' -e 's/^(//' -e 's/),(/)\n(/g' -e 's/)\n/\n/g' -e 's/\n(/ \n/g' -e 's/,/\t/g' -e "s/'//g" > [Output file name]

 

# CSV (comma separated, enclosed in double quotes) mysqldump -uroot -p -t [target schema] [target table] | grep 'INSERT INTO' | sed -e 's/INSERT INTO `.*` VALUES //' -e 's/^(/"/' -e 's/);$/"/' -e 's/),(/)\n(/g' -e "s/'//g" -e ' s/)\n/"\n/g' -e 's/\n(/\n"/g' -e 's/,/","/g' > [Output file name]

 The above command was tested on GNU sed 4.2.1 (CentOS 6.8).
The order in which sed replaces data is important, but depending on the content of the data, it may not be possible to convert it properly (especially if the string contains ")" or "(").
It can be processed with a one-liner, so give it a try.

 
That's it.