Revision [3498]

Last edited on 2009-08-21 01:38:03 by WikiAdmin
Additions:
To perform a consistent backup when database locking is not an issue, use
--lock-all-tables --extended-insert or (-e -x)

Revision [1916]

Edited on 2008-09-01 21:19:36 by WikiAdmin
Additions:
Mysqlpdump (python) - http://www.fr3nd.net/projects/mysqlpdump/
mk-parallel-dump (perl) - http://maatkit.sourceforge.net/doc/mk-parallel-dump.html
Deletions:
Mysqlpdump - http://www.fr3nd.net/projects/mysqlpdump/

Revision [1915]

Edited on 2008-09-01 21:18:44 by WikiAdmin
Additions:
**Tools**
There's really no need to reinvent the wheels. Check out the great tools:
Mysqlpdump - http://www.fr3nd.net/projects/mysqlpdump/
-----

Revision [1575]

Edited on 2008-03-27 19:50:40 by WikiAdmin
Additions:
gzip -f ${MYSQLBACKUPPATH}/MYB-${DB}-${DATESTAMP}.sql
Deletions:
gzip ${MYSQLBACKUPPATH}/MYB-${DB}-${DATESTAMP}.sql

Revision [1574]

Edited on 2008-03-27 19:49:48 by WikiAdmin
Additions:
find $MYSQLBACKUPPATH -name "MYB*.sql.gz" -mtime +7 | xargs rm -f
$MYSQLBIN/mysqldump --opt -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} ${DB} > ${MYSQLBACKUPPATH}/MYB-${DB}-${DATESTAMP}.sql 2>> $LOGFILE
gzip ${MYSQLBACKUPPATH}/MYB-${DB}-${DATESTAMP}.sql
Deletions:
find $MYSQLBACKUPPATH -name "*.sql.gz" -mtime +7 | xargs rm -f
$MYSQLBIN/mysqldump --opt -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} ${DB} > ${MYSQLBACKUPPATH}/${DB}-${DATESTAMP}.sql 2>> $LOGFILE
gzip ${MYSQLBACKUPPATH}/${DB}-${DATESTAMP}.sql

Revision [1573]

Edited on 2008-03-27 19:46:52 by WikiAdmin
Additions:
MYSQLBIN="/usr/bin"
LOGFILE="/var/log/mysqldumps.log"
FAILFLAG=0
DATABASES=`$MYSQLBIN/mysql -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} -Nse "show databases;"`
if [ $? -gt 0 ]; then
echo "========== Error: Could not execute 'show databases' ==========" >> $LOGFILE
fi
echo "========== Starting Database Dumps ==========" >> $LOGFILE
echo Backup up ${DB} >> $LOGFILE
$MYSQLBIN/mysqldump --opt -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} ${DB} > ${MYSQLBACKUPPATH}/${DB}-${DATESTAMP}.sql 2>> $LOGFILE
if [ $? -gt 0 ]; then
echo "Backup of ${DB} failed" >> $LOGFILE
FAILFLAG=1
fi
gzip ${MYSQLBACKUPPATH}/${DB}-${DATESTAMP}.sql
if [ $FAILFLAG -gt 0 ]; then
echo "========== Errors Encountered During Data Dump ==========" >> $LOGFILE
else
echo "========== All Databases Dumped Successfully ==========" >> $LOGFILE
fi
Deletions:
DATABASES=`mysql -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} -Nse "show databases;"`
echo Backup up ${DB}
mysqldump --opt -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} ${DB} | gzip > ${MYSQLBACKUPPATH}/${DB}-${DATESTAMP}.sql.gz

Revision [1506]

Edited on 2008-03-18 16:26:59 by WikiAdmin
Additions:
#MYSQLCONN="-S /var/lib/mysql/mysql.sock" # Use UNIX socket
#MYSQLCONN="-h 192.168.1.1" # Use TCP/IP
Deletions:
#MYSQLCONN="-S /var/lib/mysql/mysql.sock"
#MYSQLCONN="-h 192.168.1.1"

Revision [1505]

Edited on 2008-03-18 16:26:08 by WikiAdmin
Additions:
MYSQLBACKUPPATH="/dumps"
MYSQLPASS="xxx"
# Specify connection method
MYSQLCONN=""
#MYSQLCONN="-S /var/lib/mysql/mysql.sock"
#MYSQLCONN="-h 192.168.1.1"
# Script begins
DATABASES=`mysql -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} -Nse "show databases;"`
mysqldump --opt -u${MYSQLUSER} -p${MYSQLPASS} ${MYSQLCONN} ${DB} | gzip > ${MYSQLBACKUPPATH}/${DB}-${DATESTAMP}.sql.gz
exit 0
Deletions:
MYSQLBACKUPPATH="/var/db/mysql/backup"
MYSQLPASS="Agbyg4esh3"
DATABASES=`mysql -u${MYSQLUSER} -p${MYSQLPASS} -Nse "show databases;"`
mysqldump --opt -u${MYSQLUSER} -p${MYSQLPASS} ${DB} | gzip > ${MYSQLBACKUPPATH}/${DB}-${DATESTAMP}.sql.gz

Revision [1192]

Edited on 2007-11-27 02:03:38 by WikiAdmin
Additions:
Here's my little backup script to dump each database into separate gzip-ed file:
%%(bash;mysql_backup.sh)
#!/bin/bash
Deletions:
Here's my little backup script to dump each database into separate gzipped file:
#!/usr/local/bin/bash

Revision [1191]

Edited on 2007-11-27 02:03:01 by WikiAdmin
Additions:
===MySQL backup script===
Here's my little backup script to dump each database into separate gzipped file:
#!/usr/local/bin/bash
MYSQLBACKUPPATH="/var/db/mysql/backup"
MYSQLUSER="root"
MYSQLPASS="Agbyg4esh3"
DATABASES=`mysql -u${MYSQLUSER} -p${MYSQLPASS} -Nse "show databases;"`
DATESTAMP=$(date +%Y%m%d)
# clean up stale archives
find $MYSQLBACKUPPATH -name "*.sql.gz" -mtime +7 | xargs rm -f
# backup dataabse individually
for DB in $DATABASES; do
echo Backup up ${DB}
mysqldump --opt -u${MYSQLUSER} -p${MYSQLPASS} ${DB} | gzip > ${MYSQLBACKUPPATH}/${DB}-${DATESTAMP}.sql.gz
done

Revision [846]

Edited on 2007-08-08 00:52:21 by WikiAdmin
Additions:
|| --opt || --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. ||
|| -A || All databases ||
Deletions:
|| -A || --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. ||
|| --opt || typical backup option||

Revision [845]

Edited on 2007-08-08 00:51:32 by WikiAdmin
Additions:
|| -A || --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. ||
|| --opt || typical backup option||
|| --extended-insert || Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file. ||
Deletions:
|| -A || Dump all databases ||

Revision [738]

Edited on 2007-06-25 20:06:05 by WikiAdmin
Additions:
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]].
Deletions:
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.**

Revision [737]

Edited on 2007-06-25 20:03:30 by WikiAdmin
Additions:
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.**
Deletions:
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.

Revision [736]

Edited on 2007-06-25 20:02:25 by WikiAdmin
Additions:
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.

Revision [735]

Edited on 2007-06-25 20:01:20 by WikiAdmin
Additions:
$ mysqldump --all-databases --opt | gzip -9 > db.tar.gz
Deletions:
$ mysqldump -q -Q -A | gzip -9 > db.tar.gz

Revision [411]

Edited on 2007-04-26 22:42:41 by WikiAdmin
Additions:
===Export Mysql to CSV===
Deletions:
===Export Mysql to CSV==

Revision [410]

Edited on 2007-04-26 22:42:29 by WikiAdmin
Additions:
=== Backup ===
Full backup of MySql databases:
$ mysqldump -q -Q -A | gzip -9 > db.tar.gz
OR
$ mysqlhotcopy database /path/to/some/dir
OR
Shutdown mysql & backup mysql data directory.
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 ` ||
|| -A || Dump all databases ||

Revision [408]

Edited on 2007-04-26 22:41:56 by WikiAdmin
Additions:
mysql -uuser -ppwd mydb -B -e "select * from \`mytable\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > output.csv
Deletions:
mysql -uexampleuser -pletmein exampledb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Revision [407]

The oldest known version of this page was created on 2007-04-26 22:41:19 by WikiAdmin
Valid XHTML 1.0 Transitional :: Valid CSS :: Powered by WikkaWiki