Quickly scripted the accepted answer's procedure in bash:
#!/usr/bin/env bash
DATABASES="$(mysql -e 'show databases \G' | grep "^Database" | grep -v '^Database: mysql$\|^Database: binlog$\|^Database: performance_schema\|^Database: information_schema' | sed 's/^Database: //g')"
mysqldump --databases $DATABASES -r alldatabases.sql && echo "$DATABASES" | while read -r DB; do
mysql -e "drop database \`$DB\`"
done && \
/etc/init.d/mysql stop && \
find /var/lib/mysql -maxdepth 1 -type f \( -name 'ibdata1' -or -name 'ib_logfile*' \) -delete && \
/etc/init.d/mysql start && \
mysql < alldatabases.sql && \
rm -f alldatabases.sql
Save as purge_binlogs.sh
and run as root
.
Excludes mysql
, information_schema
, performance_schema
(and binlog
directory).
Assumes you have administrator credendials in /root/.my.cnf
and that your database lives in default /var/lib/mysql
directory.
You can also purge binary logs after running this script to regain more disk space with:
PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;