# On database, each table
for T in `mysql -N -B -e 'show tables from dbname'`; \
   do echo $T; \
     mysqldump [connecting_options] dbname $T \
        | gzip -c > $T.dump.gz ; \
done
# Each database of a full mysql server
        for DB in `mysql -u root -p -N -B -e 'show databases'`; \
        do echo $DB; \
                mysqldump --add-drop-table -c -u root -p --databases $DB \
                | gzip -c > $DB.sql.gz ; \
        done
# whole server
mysql --password=mypass --batch --skip-column-names --execute="SHOW DATABASES" \
 | grep -v "mysql" \
 | xargs mysqldump --password=mypass --databases > dump.sql
# more complex, see:
# <a href="http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
#" title="http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
#">http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
#</a>     Posted by Philip Sbrogna on February 2 2006 4:05pm
# setup
suffix=`date +%Y%m%d`
dest=/mirror/mysqldumps
cmd='/usr/bin/mysqldump'
databases=(`echo 'show databases;' | mysql -u  --password='' | grep -v ^Database$`)
for d in "${databases[@]}"; do
if [[ $d != 'tmp' &amp;&amp; $d != 'test' ]]
then  echo "DATABASE ${d}"
s="use ${d}; show tables;"
tables=(`echo ${s} | mysql -u  --password='' | grep -v '^Tables_in_'`)
for t in "${tables[@]}"; do
if [[ $t != 'tbl_parameter' &amp;&amp; $t != 'tbl_session' ]]
then echo " TABLE ${t}"
path="${dest}/${suffix}/${d}"
mkdir -p ${path}
${cmd} --user= --password='' --quick --add-drop-table --all ${d} ${t} | bzip2 -c &gt; ${path}/${t}.sql.bz2
fi
done
fi
done
# delete old dumps (retain 5 days)
find ${dest} -mtime +5 -exec rm {} \;

#sql split line
csplit -f l dump.sql "/^-- Database/"-1 {*}

#sql split line
csplit -f l dump.sql "/^-- Table structure/"-1 {*}