Mysql Dump Magento DB script

mysql_dump_mage.sh

#!/bin/bash

echo "//////////////////////////  START  ////////////////////////////"
date
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

day=$(date +%A)
user=$2
host=$1
database=$3
password=$4

if [ -z "$user" -a "${user+xxx}" = "xxx" ]; 
	then
	echo "usage without prompts: ./sql_dump_mage.sh host user database password"
	echo "MySQL User:"
	read -s user
fi

if [ -z "$host" -a "${host+xxx}" = "xxx" ]; 
	then
	echo "MySQL Host:"
	read -s host
fi

if [ -z "$password" -a "${password+xxx}" = "xxx" ]; 
	then
	echo "MySQL Password:"
	read -s password
fi

if [ -z "$database" -a "${database+xxx}" = "xxx" ]; 
	then
	mysql -u $user -p$password -h $host -e "show databases;"
	echo "MySQL Database:"
	read database
fi



file="$DIR/$database-$day.sql"


CMD=`cat <<EOF
USE $database;
TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
EOF
`
echo "RUNNING EXPORT to file $file"

mysql -u $user -p$password -h $host -e "$CMD"

echo "SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;" > $file
mysqldump -u $user -p$password -h $host --opt $database >> $file

echo "COMMIT;" >> $file
gzip -f $file 
date
echo "/////////////////////////   END    ////////////////////////////"
echo "to copy this file to another server that you have ssh access to use scp for example:"
echo "scp $file.tar.gz user@host:/host/destination/path" 
echo "to restore this database backup use the command: mysql -u[user] [targetdatabase] < $file"
echo "if you are going to restore this to another server and not use the same url to access it you will need to update the core_config_data paths"
echo "from mysql: update core_config_data set value = 'http://whatever.your.path.com/' where path like '%/base_url'"