Here's a BASH one-liner to truncate all tables from a list of databases:
for j in database_name1 database_name2; \
do for i in `echo 'show tables ' |mysql $j \
|grep -v 'Tables_in'`; do mysql $j -e "truncate $i"; done; done
Please note, truncating will remove all the data from the target tables without any prompting. Perhaps change "truncate $i" to "describe $i" first to make sure the tables in the result set are the ones intended to be emptied.
One more thing: if you want to iterate over every table in all MySQL databases (except information_schema
and mysql
, I would hope!), substitute the following for the above "database_name1 database_name2":
`echo 'show databases' | mysql | awk '$1 != "information_schema" && \
$1 != "mysql" {if (NR > 1) {print}}'`
So, here's a sample that's less destructive; it performs OPTIMIZE for all tables in every MySQL database (exceptions as noted above):
for j in `echo 'show databases' | mysql | \
awk '$1 != "information_schema" && $1 != \
"mysql" {if (NR > 1) {print}}'`; do for i in \
`echo 'show tables ' |mysql $j |grep -v \
'Tables_in'`; do mysql -e "optimize table $j.$i"; \
done; done
Modify the "action" performed as needed and with much trepidation!