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.