Here is a script for dumping tables from all databases in MySql.
#!/bin/bash
# *************************************************************
# file: mysqldump.sh
# date: 2011-12-22 00:22
# author: (c) by Mats Ahlbom - mats@ahlbom.nu
# description: Get a mysqldump-file for eath table in all databases
# *************************************************************
# name of database user ( must have LOCK_TABLES rights )…
dbUsername=”mythtv”
# password of database user…
dbPassword=”mythtv”
# path to backup directory…
dbBackup=”/home/mathz/mysqldump”
# Delete old dumped tables true/false
delete_old=”flase”
# *************************************************************
# Do not edit below this line!
# get current date ( YYYY-MM-DD )…
date=$( date +%Y-%m-%d )
# create backup directory if not exists…
[ ! -d “$dbBackup” ] && mkdir -p $dbBackup
if [ $delete_old = “true” ]; then
find $dbBackup/ -type f -name ‘*.sql.gz’ -exec rm -rf {} ‘;’ >/dev/null 2>&1
fi
# loop all databases…
for db in $( mysql -u $dbUsername –password=$dbPassword -Bse “show databases” ); do
# loop all tables in database
for tables in $( mysql -u $dbUsername –password=$dbPassword -Bse “show tables” ${db} ); do
# get mysqldump of current tavble…
mysqldump -u $dbUsername –password=$dbPassword –opt –databases $db –tables $tables | gzip -9 >${dbBackup}/${db}-${tables}-${dat
e}.sql.gz
done
done
# *************************************************************
# EOF
Leave a Reply