SuiteCRM SQL database dump to CSV files

Recently I needed to dump our workplaces existing CRM database to a format that was easy to work with, namely CSV instead of the more likely SQL format.

To do this easily and quickly I developed a small script that would automatically grab the SQL servers credentials from your config file and then dump the files all from a single file. Your config.php usually resides in the htdocs folder on your server.

#!/bin/sh

# sqlHost=$(grep db_host_name config.php | cut -f4 -d\' | cut -f1 -d\:)
confFile=/opt/crm/apps/suitecrm/htdocs/config.php
sqlHost=127.0.0.1
sqlUser=$(grep db_user_name "$confFile" | cut -f4 -d\')
sqlPass=$(grep db_password "$confFile" | cut -f4 -d\')
sqlTable=$(grep db_name "$confFile" | cut -f4 -d\')
dumpDirectory=db_dump_$(date +%Y-%m-%d)

echo $sqlHost $sqlUser $sqlPass $sqlTable $dumpDirectory

mkdir "$dumpDirectory"
for table in $(mysql -h $sqlHost -u$sqlUser -p$sqlPass $sqlTable -sN -e "SHOW TABLES;"); do mysql -B -u$sqlUser -p$sqlPass $sqlTable -h $sqlHost -e "SELECT * FROM $table;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $dumpDirectory/$table.csv; done
find $dumpDirectory -type f -empty -delete

Make the script executable, run it and all being well you should have a folder containing all your csv files.

One point worth noting is the manual setting of $sqlHost as localhost doesn’t seem to work with the mysql command so this may require changing if you’re not running SQL and the script on the same machine.