Cron SQL Dump to CSV format


#1

Hello.
I currently have a cron job setup
that does a SQL dump nightly.

The cron is working fine, and the
dumps are happening, but instead
of .sql format, I need the values
dumped in a CSV format.

It can be a .txt extension, as long
as the values are comma separated.

This is the current command I am
running with the cron:

domains=( mydomain.com )
sqldbs=( mydomain_dbname )
opath=$HOME/backup/
mysqlhost=mysql.mydomain.com
username=me
password=you
suffix=$(date +%m-%d-%Y)
for (( i = 0 ; i < ${#domains[@]} ; i++ ))
do
	cpath=$opath${domains[$i]}
	if [ -d $cpath ]
	then
		filler="just some action to prevent syntax error"
	else
		echo Creating $cpath
		mkdir -p $cpath
	fi
	mysqldump -c -h $mysqlhost --user $username --password=$password ${sqldbs[$i]} > ${cpath}/${sqldbs[$i]}_$suffix.sql
done

After doing a little research, I found you can add the following:

--fields-terminated-by=","

But I am not sure:

  1. if this is applicable to what I am doing
  2. where to put this new command within the existing command
    and
  3. should I change the .sql at the end of the current command to .txt

Thanks in advance for any and all assistance.


#2

–fields-terminated-by can go almost anywhere in the existing “mysqldump” line. Probably the easiest place to put it is right after “–password=$password”.

Keep in mind that the SQL dumps generated with this command CAN NOT BE RESTORED into a MySQL database, as the comma-separated format is ambiguous. (Consider what happens if the data in one of your tables includes a comma.) If you are intending to use the files generated by this script as a backup, you will need to leave them in SQL format.


#3

OK thanks for the quick reply.

What about my third question?
Should I change the extension
on the command to .txt instead
of .sql?

Also a quick overview of what I am
trying to accomplish.

My client’s site has several forms,
which are all saved to a SQL database.

My client has developed a CRM, which is
also SQL based.

The original plan was for the data that was
entered into the site SQL database, can be
imported into the CRM database, and have
tasks and triggers assigned to them there.

As I mentioned earlier, the SQL dumps were
working fine, but the person developing the CRM
had a problem with the SQL format.

Here is his email to me:

Hence, my request to have the data exported to a CSV format.

But according to andrew, the resulting .txt file cannot be restored into a MySQL database :expressionless:

Once I get clarity on the .sql vs. .txt extension in the command, I will do a test run and see
if –fields-terminated-by is the solution we are looking for.

Thanks again for the speedy reply. Much appreciated!


#4

A full MySQL dump includes both the table structure and contents, as both are necessary to restore a backup; the table structure appears before the data. It’s possible that your client was seeing the CREATE TABLE statements in the SQL dump and not looking past them to see the following data; the mysqldump output from the script you posted above should include both, though.


#5

My client did some digging last night,
and found a couple of items.

Here is one command:

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1

and here is another:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

And the results are sent to /tmp/result.text in CSV format.

They both have elements of –fields-terminated-by so
I wonder of this would work:

--fields-terminated-by="," --lines-terminated-by=\"

and ultimately give me a command of:

domains=( mydomain.com )
sqldbs=( mydomain_dbname )
opath=$HOME/backup/
mysqlhost=mysql.mydomain.com
username=me
password=you
suffix=$(date +%m-%d-%Y)
for (( i = 0 ; i < ${#domains[@]} ; i++ ))
do
    cpath=$opath${domains[$i]}
    if [ -d $cpath ]
    then
        filler="just some action to prevent syntax error"
    else
        echo Creating $cpath
        mkdir -p $cpath
    fi
    mysqldump -c -h $mysqlhost --user $username --password=$password --fields-terminated-by="," --lines-terminated-by=\"  ${sqldbs[$i]} > ${cpath}/${sqldbs[$i]}_$suffix.txt or csv
done

Any thoughts on that command andrew, or anyone else?

I also found out that he is using MS-SQL for the CRM,
not MySQL.

Thanks