{{parent page="MySQL"}}

**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===
%%(bash)
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 [[http://www.innodb.com/support/documentation/innodb-hot-backup-manual#backup.uncompressed 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:
%%(bash;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
%%