MySQLDump Script Snippets

The following are three script snippets to use for database backup procedures:

# 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 ; \
# 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 ; \
# 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="
#" title="
#</a>     Posted by Philip Sbrogna on February 2 2006 4:05pm
# setup
suffix=`date +%Y%m%d`
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}"
mkdir -p ${path}
${cmd} --user= --password='' --quick --add-drop-table --all ${d} ${t} | bzip2 -c &gt; ${path}/${t}.sql.bz2
# delete old dumps (retain 5 days)
find ${dest} -mtime +5 -exec rm {} \;

Extra snippets:

Will divide a file, in this case an .sql file, onto tables and views in separate files.

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

Please note that I am using the following pattern /^-- Database/ as the marker to split the file. I have also found the following patter on many sql files:

 -- Table structure

Thus, the follwoing would work in such a situation.

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