TYPO free: TYPO3 database backup without the heavy tables

There are times when you need to backup your database or even copy the complete database to another server. If you are unlucky, you cannot use the mysqldump tool to make a copy of the complete database at once because that would bring down the server completely. It would also be nice to be able to rsync over the data to another server (the staging server). Then having one huge dump file is also sub-optimal.

A solution is needed that is lightweight. I made a small script that dumps a database using mysqldump. It has the the following featur

Dumps every table into a separate sql file;
Keeps a table 'blacklist';
For blacklisted tables only the structure is dumped, not the data;
If a dump has been done of a table containing more than X lines, then the script sleeps for X seconds so the database server will not be overloaded.


#!/bin/sh
# Database dump tool with table blacklist
# Michiel Roos <michiel@netcreators.com>
 
host=localhost
database=test
username=test
password=secret
binDir=/Applications/MAMP/Library/bin/
backupDir=/Users/dude/tmp/backup/
sleep=5
aLotOfRows=1000
 
skip=(
	sys_log
	cache_hash
	cache_pages
	cache_pagesection
	index_fulltext
	index_grlist
	index_phash
	index_rel
	index_section
	index_stat_search
	index_stat_word
	index_words
	sys_log
	tx_realurl_chashcache
	tx_realurl_errorlog
	tx_realurl_pathcache
	tx_realurl_urldecodecache
	tx_realurl_urlencodecache
)
 
len=${#skip[*]}
 
# for each table in the database
for table in `nice -n 19 ${binDir}mysql --batch -u$username -p$password -h$host -D$database -e 'show tables' | egrep -v Tables_in_`;
do 
	i=0
	noData=''
	while [ $i -lt $len ];
	do
		if [ "$table" = "${skip[$i]}" ];
		then
			#echo "'$table' is equal to '${skip[$i]}'"
			noData='--no-data'
		fi
		let i++
	done
	echo "dumping $table"
	rowCount=`nice -n 19 ${binDir}mysql --batch -u$username -p$password -h$host -D$database -e "SELECT COUNT(*) FROM $table" | egrep -v COUNT`
	nice -n 19 ${binDir}mysqldump -u$username -p$password -h$host $noData --add-drop-database --add-locks --create-options --delayed-insert --disable-keys --quick $database $table > $backupDir$table.sql;
	if [ $rowCount -lt $aLotOfRows ];
	then
		echo "done!"
	else
		echo "done! Sleeping $sleep seconds . . ."
		sleep $sleep
	fi
done;

Sure there is room for improvement. This incarnation is pretty crude. But I found this very usefull already.

Update: don't count the rows if we're only dumping the structure.


#!/bin/sh
# Database dump tool with table blacklist
# Michiel Roos <michiel@netcreators.com>
 
host=localhost
database=test
username=test
password=secret
binDir=/Applications/MAMP/Library/bin/
backupDir=/Users/dude/tmp/backup/
sleep=5
aLotOfRows=1000
 
skip=(
	sys_log
	cache_hash
	cache_pages
	cache_pagesection
	index_fulltext
	index_grlist
	index_phash
	index_rel
	index_section
	index_stat_search
	index_stat_word
	index_words
	sys_log
	tx_realurl_chashcache
	tx_realurl_errorlog
	tx_realurl_pathcache
	tx_realurl_urldecodecache
	tx_realurl_urlencodecache
)
 
len=${#skip[*]}
 
# for each table in the database
for table in `nice -n 19 ${binDir}mysql --batch -u$username -p$password -h$host -D$database -e 'show tables' | egrep -v Tables_in_`;
do 
	i=0
	noData=''
	rowCount=0
	while [ $i -lt $len ];
	do
		if [ "$table" = "${skip[$i]}" ];
		then
			#echo "dumping structure of table '$table'"
			noData='--no-data'
		fi
		let i++
	done
 
	if [ "--no-data" = "${noData}" ];
	then
		echo "dumping structure of '$table'"
		nice -n 19 ${binDir}mysqldump -u$username -p$password -h$host $noData --add-drop-database --add-locks --create-options --delayed-insert --disable-keys --quick $database $table > $backupDir$table.sql;
	else
		echo "dumping '$table'"
		rowCount=`nice -n 19 ${binDir}mysql --batch -u$username -p$password -h$host -D$database -e "SELECT COUNT(*) FROM $table" | egrep -v COUNT`
		nice -n 19 ${binDir}mysqldump -u$username -p$password -h$host --add-drop-database --add-locks --create-options --delayed-insert --disable-keys --quick $database $table > $backupDir$table.sql;
	fi
 
	if [ $rowCount -lt 1000 ];
	then
		echo "done!"
	else
		echo "done! Sleeping $sleep seconds . . ."
		sleep $sleep
	fi
done;

Steffen 24 Jul 2008, 01:11

Hi Michiel,

this afternoon I spend some time on writing backup scripts. Your timing is just perfect ;-)

One suggestion when using your script as a cronjob:

Test the target device or touch the backup file before starting any DB stuff. This gives you the possibility to track down the error more precisely, especially if you use NFS or other network attached storage.

touch $backupDir$table.sql
if [ $? -ne 0 ]
then
echo -e "ERROR in ${0}: Could not touch file $backupDir$table.sql." | mail $RECEIPIENT -s"[Backup error]"
exit 1
fi

Of course you can test if the file already exists:
if [ -f $backupDir$table.sql ]
...
fi

... or loop some time if the storage device is temporarily down (using bash syntax here):

while [ ! -b /dev/sdb1 ] && [ $ATTEMPTS -lt 9 ]
do
let ATTEMPTS+=1
sleep 60
done

fighting for TYPO free code

TYPO3 database backup without the heavy tables

Add some blurb