Additions:
Deletions:
Additions:
Additions:
gzip -f ${MYSQLBACKUPPATH}/MYB-${DB}-${DATESTAMP}.sql
Deletions:
gzip ${MYSQLBACKUPPATH}/MYB-${DB}-${DATESTAMP}.sql
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
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
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"
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
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
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
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||
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 ||
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
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.
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.
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.
Additions:
$ mysqldump --all-databases --opt | gzip -9 > db.tar.gz
Deletions:
$ mysqldump -q -Q -A | gzip -9 > db.tar.gz
Additions:
Export Mysql to CSV
Deletions:
Export Mysql to CSV
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 ||
Additions:
mysql -uuser -ppwd mydb -B -e "select * from \`mytable\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\ng' > output.csv
Deletions:
mysql -uexampleuser -pletmein exampledb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\ng' > filename.csv
Export Mysql to CSV
mysql -uexampleuser -pletmein exampledb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv