Just answer the F*#king question

Home > MySQL > mySQL backup script

mySQL backup script

November 16th, 2009 Leave a comment Go to comments

This backup is listed on the mySQL site somewhere. Backs up a DB broken down into individual tables. Great if you have large data sets. This one also purges the files after 7 days ( does leave the directories behind though )

#setup suffix=`date +%Y%m%d` dest=/mirror/mysql/SQL01 cmd='/usr/bin/mysqldump'


databases=(`echo 'show databases;' | mysql -h SQL01 -u root | grep -v ^Database$`)
for d in "${databases[@]}"; do

if [[ $d != 'tmp' && $d != 'test' ]]

then

echo "DATABASE ${d}"

s="use ${d}; show tables;"

tables=(`echo ${s} | mysql -h SQL01 -u root | grep -v '^Tables_in_'`)

for t in "${tables[@]}"; do

if [[ $t != 'tbl_parameter' && $t != 'tbl_session' ]]

then

echo " TABLE ${t}"

path="${dest}/${suffix}/${d}"

mkdir -p ${path}

${cmd} --user=root --host SQL01 --quick --add-drop-table --all ${d} ${t} | bzip2 -c > ${path}/${t}.sql.bz2

fi

done

fi

done
# delete old dumps (retain 5 days)

find ${dest} -mtime +10 -exec rm {} \;