Create a CSV from a mysqldump command

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

Have you ever wanted to edit MySQL data in Excel?
This article summarizes how to convert MySQL data to CSV format so that it can be read in Excel.

The mysqldump command is often used to write the contents of mysql to a file, but if no special specification is given, the output will be in SQL format

This is fine when transferring data to another database, but what if you want to process this file in Excel?

The mysqldump command has the --tab option to meet this need

The version of MySQL used for this test was MySQL Community Server 5.6.35, which was installed via the official MySQL repository

 

Try to get it by using the mysqldump command alone

When using the mysqldump command to retrieve a CSV file, you'll often encounter an error
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'

I'm writing as root, but I'm getting an error for
some reason. This is because the MySQL server startup options include "--secure-file-priv," which means that files cannot be written to any directory other than the specified one.

Let's look at how to verify 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)

 
By default, the MySQL server in the official repository allows files to be written only to the directory /var/lib/mysql-files/

No way, that's ridiculous.
I've been able to export SQL queries before.

I thought the same thing, but that's just redirecting the mysqldump command output to a file, isn't it?
The point is, mysqldump doesn't actually write to a file.

The --tab option causes mysqldump itself to write to the path specified after the "=", so writing to a path other than the one specified in "@@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 look like this:

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

 
This will output a tab-separated CSV (TSV) file to /var/lib/mysql-files/

 

Unable to access the @@global.secure_file_priv path or change @@global.secure_file_priv

There will be times like this

In that case, since there's nothing else you can do, I can think of the following steps:
1. First, get the SQL using mysqldump.
2. Import the outputted SQL into a MySQL server you can control.
3. Output a CSV file.

If you have the time, this is fine.
you don't have the time or don't want to go through the trouble
, you can use grep and sed to convert the 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 delimited, 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 of substitutions in sed is important, and depending on the data content, there may be cases where the conversion does not work properly (especially if the string contains "," or "("). It
can be processed in one line, so give it a try.

 
That's all