mysqldump script (per tables)


#!/bin/bash

# This is PER TABLE backup. Each table will be backed up individually

## to enable passwordless mysqdump, put your password in /etc/mysql/[mysqld|percona|maria].conf.d/client.conf
# [client]
# user=""
# pass=""

THEDB="mydbname" # THE DATABASE
OUTPUT="/home/backup/mysqldump"
DATE=`date +%Y%m%d`
OPTS="--max_allowed_packet=512M "

tables=`mysql -e "use $THEDB; show tables;" | tr -d "| " | grep -v -E "^Tables_in_" `
mkdir -p $OUTPUT/$THEDB-$DATE

for table in $tables; do
STAGEDDATE=`date +%Y%m%d`
echo "Dumping table : " $table
mysqldump -a $OPTS $THEDB $table > $OUTPUT/$THEDB-$DATE/$STAGEDDATE-$table.sql
gzip $OUTPUT/$THEDB-$DATE/$STAGEDDATE-$table.sql
done

echo "Backup done at " $OUTPUT/$THEDB-$DATE

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.