December 6, 2007

 Excluding MySQL tables during mysqldump

Excluding MySQL tables is not included in some versions of MySQL. Here's a bash script that can do just that:

- create a file called mysqldump.sh

- in the file, put:

#!/bin/sh
for TABLE in $(echo "show tables" | mysql -u [mysqluser] -p[mysqlpassword] [atmaildatabase] | grep -v Tables_in_ | grep -v [Excluded Tables])
do
mysqldump -v --add-drop-table -u [mysqluser] -p[mysqlpassword] [atmaildatabase] ${TABLE} > [mysqldumpfile.sql]
done

- replace the items in brackets:
- [mysqluser] for your MySQL user
- [mysqlpassword] for your MySQL password
- [atmaildatabase] for your @Mail database
- [Excluded Tables] for your excluded tables
- [mysqldumpfile.sql] for your MySQL dump file location

- note that you can also extend the script by adding more excluded tables. Just change the file like so:

for TABLE in $(echo "show tables" | mysql -u [mysqluser] -p[mysqlpassword] [atmaildatabase] | grep -v Tables_in_ | grep -v [Excluded Tables] | grep -v [Another Excluded Table] | grep -v [Another Excluded Table])

- give it the right permissions:

% chmod 755 mysqldump.sh

- execute:

% ./mysqldump.sh

This is especially useful if you need a cronjob that will backup your MySQL databases, without the log tables.


Filed under: Backup,Linux version — info @ 2:53 pm

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment