MySQL Backup Script with User Permisions

#!/bin/bash
# Copyright (c) 2005 nixCraft project 
# This script is licensed under GNU GPL version 2.0 or above
# Author Vivek Gite 
# Addapted to grab multiple databases and users by Dale Caon and output to file rather than another database
# ------------------------------------------------------------
# SETME First - local mysql user/pass
_lusr="root"
_lpass='*****'
_lhost="localhost"
 
_folder='/backups/mysql'
_file='mysql.sql'
#### Edit below at own risk! #####
 
# Set up folder and file to hold user and data dump
if [ -d $_folder ]
then
        #Don't do anything
        echo -n '' > "$_folder/$_file"
else
        mkdir -p "$_folder/"
fi;
 
cd $_folder
 
# Make sure you can connect to local db server
mysqladmin -u "$_lusr" -p"$_lpass" -h "$_lhost"  ping &>/dev/null || { echo "Error: Mysql server is not online or incorrect values set for _lusr, _lpass, and _lhost"; exit 2; }
 
##### Step 1: Okay build .sql file with db dump ####
for i in $(mysql -u "$_lusr" -p"$_lpass" -h "$_lhost" -e'show databases' | sed '1,1d' |  grep -v 'information_schema' | grep -v 'mysql'); do
        echo "create database IF NOT EXISTS $i; " >> "$_file"
done;
 
#### Step 2: Build mysql query to grab all privs and user@host combo for given db_username ####
mysql -u "$_lusr" -p"$_lpass" -h "$_lhost" -B -N \
-e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" \
mysql \
| mysql  -u "$_lusr" -p"$_lpass" -h "$_lhost" \
| grep -v root | grep -v debian-sys-maint \
| sed 's/Grants for .*/#### &/' | sed 's/$/;/'\
| sed 's/\\\\_/_/' >> "$_file"
 
#### Step 3: Dump database contents for all databases ####
for i in $(mysql -u "$_lusr" -p"$_lpass" -h "$_lhost" -e'show databases' | sed '1,1d' |  grep -v 'information_schema' | grep -v 'mysql'); do
        echo "use $i;" >> "$_file"
        mysqldump -u "$_lusr" -p"$_lpass" -h "$_lhost" "$i" >> "$_file"
done;
 
## Compressing SQL ##
tar -zcf $_file.tar.gz $_file
rm $_file