Select Page

Simple bash script to MySQLdump and gzip all databases in separate files

by | Nov 26, 2016 | DB

#!/bin/sh
NOW=$(date +"%d-%m-%Y")
# set MySQL login info
MUSER="USERNAME"
MPASS="PASSWORD"
MHOST="localhost"
# guess binary names 
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
 
# get all db names 
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 FILE=mysql-$db.$NOW-$(date +"%T").gz
 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

Exporting MySQL into a second DB without replacing tables

by | Oct 21, 2016 | DB

The following command exports a MySQL database allowing to import the result in a second DB without dropping the existing tables.

mysqldump --single-transaction --no-create-info --insert-ignore DB TABLE --where="EXISTS(SELECT 1 FROM TABLE_NAME WHERE COLLUMN_NAME__datetime > now() - interval 6 month)" | pv | gzip > DUMPDB.sql.gz
pv DUMP.sql.gz | gunzip | mysql --force dbname