Mysqldump with progressbar

The mysqldump-command doesn't really support viewing the progress of exporting a database. I guess it's because it gives overhead, and it's frankly quite difficult to generate a fluid progressbar.

Therefore I've created a simple script to show an estimation of the process of a mysqldump. It does so by calculating the size of all the tables in the given database and comparing this with the size of the resulting sql file.

#!/bin/bash

dbname="$1"
if [ -z "$dbname" ]; then
  echo "$0: Missing database operand."
  echo "Usage: $0 database_name"
  exit 1
fi

MB=$((1024*1024))
outfile="$(date +%Y%m%d)".$dbname.sql

size=$(sudo mysql --defaults-file=/etc/mysql/debian.cnf --silent --skip-column-names \
  -e "SELECT CEIL(SUM(data_length) / $MB) \
      FROM information_schema.TABLES \
      WHERE table_schema='$dbname';")

echo "Export will be around "$size"MB."

sudo mysqldump --defaults-file=/etc/mysql/debian.cnf --events --routines --max-allowed-packet=512MB --quick --quote-names \
  $dbname | pv -cN $outfile --size "$size"m > $outfile

echo "File written to: $outfile"

realsize=$(ls -s --block-size=$MB $outfile | cut -d' ' -f1)
echo "Actual filesize is "$realsize"MB."

exit 0

Simply save the snippet above as mysqldump-progress.sh or something similar. The usage is quite simple:

bash mysqldump-progress.sh databasename

Allthough this method isn't precise at all, it gives a good indication of the progress. Handy dandy when exporting bigger databases.

Updates

2020-05-05

Added sudo to ensure access to defaults file.

Changed options to mysqldump to ensure large databases can be properly exported, also include events and routines.

Remove the need for bc, make pv show more details.