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. ||
MySQL backup script
Here's my little backup script to dump each database into separate gzip-ed file:
#!/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
There are no comments on this page. [Add comment]