KfWiki: Export Mysql to CSV

HomePage » Database » MySQL » MysqlBackup

Tools
There's really no need to reinvent the wheels. Check out the great tools:
Mysqlpdump (python) - http://www.fr3nd.net/projects/mysqlpdump/
mk-parallel-dump (perl) - http://maatkit.sourceforge.net/doc/mk-parallel-dump.html

Export Mysql to CSV

mysql -uuser -ppwd mydb -B -e "select * from \`mytable\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > output.csv

Backup

Full backup of MySql databases: 
$ mysqldump --all-databases --opt  | gzip -9 > db.tar.gz
	OR
$ mysqlhotcopy database /path/to/some/dir
	OR 
Shutdown mysql & backup mysql data directory.

The --opt options stands for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It's an important option to ensure your backup is consistent. For database with InnoDB as storage engines, mysql must be shutdown before during the database dump. For hotbackup of innodb, please refer to this link.

One can also use ibbackup to perform hotmail on MyISAM and InnoDB databases.

dump options

Option	Explaination
-q, --quick	Do not store all rows in a table to memory, send it to output after a row is read.
-Q	Quote table and column names with `
--opt	--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
-A	All databases
--extended-insert	Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file.

To perform a consistent backup when database locking is not an issue, use

--lock-all-tables --extended-insert or (-e -x)

MySQL backup script

Here's my little backup script to dump each database into separate gzip-ed file:

mysql_backup.sh

#!/bin/bash

MYSQLBIN="/usr/bin"

MYSQLBACKUPPATH="/dumps"

MYSQLUSER="root"

MYSQLPASS="xxx"

LOGFILE="/var/log/mysqldumps.log"

FAILFLAG=0

# Specify connection method

MYSQLCONN=""

#MYSQLCONN="-S /var/lib/mysql/mysql.sock" # Use UNIX socket

#MYSQLCONN="-h 192.168.1.1" # Use TCP/IP

# Script begins

DATABASES=`$MYSQLBIN/mysql -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} -Nse "show databases;"`

if [ $? -gt 0 ]; then

    echo "========== Error: Could not execute 'show databases' ==========" >> $LOGFILE

fi

DATESTAMP=$(date +%Y%m%d)

echo "========== Starting Database Dumps ==========" >> $LOGFILE

# clean up stale archives

find $MYSQLBACKUPPATH -name "MYB*.sql.gz" -mtime +7 | xargs rm -f

# backup dataabse individually

for DB in $DATABASES; do

        echo Backup up ${DB} >> $LOGFILE

        $MYSQLBIN/mysqldump --opt -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} ${DB} > ${MYSQLBACKUPPATH}/MYB-${DB}-${DATESTAMP}.sql 2>> $LOGFILE

        if [ $? -gt 0 ]; then

            echo "Backup of ${DB} failed" >> $LOGFILE

            FAILFLAG=1

        fi

        gzip -f ${MYSQLBACKUPPATH}/MYB-${DB}-${DATESTAMP}.sql

done

if [ $FAILFLAG -gt 0 ]; then

    echo "========== Errors Encountered During Data Dump ==========" >> $LOGFILE

else

    echo "========== All Databases Dumped Successfully ==========" >> $LOGFILE

fi

exit 0

KfWiki : MysqlBackup

Export Mysql to CSV

Backup

MySQL backup script