Mysqldump with progressbar

Exporting Mysql database 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

set -e

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

MB=$((1024*1024))
CORRECTIONPERCENT=110
outfile="$(date +%Y%m%d.%H%M%S)".$dbname.sql.gz

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."
echo "Compressed will be around "$(($size / 10))"MB."

sudo mysqldump --defaults-file=/etc/mysql/debian.cnf -E -R --max-allowed-packet=512MB -q --single-transaction -Q --skip-comments \
  $dbname | pv -cN $outfile --size "$size"m | gzip > $outfile

size=$(($size*$CORRECTIONPERCENT/100))
echo "File written to: $outfile"

realsize=$(ls -s --block-size=$MB $outfile | cut -d' ' -f1)
echo "Actual filesize is "$realsize"MB."
echo "Dump can be imported using: pv $outfile | zcat | sudo mysql --defaults-file=/etc/mysql/debian.cnf $dbname"

exit 0

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

bash dbexport.sh databasename

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

Importing Mysql database with progressbar

Even though the script will output an example one-liner how to re-import the export, the file can easily be imported with the following command:

pv 19960101.133700.dbname.sql.gz | zcat | sudo mysql dbname

Don't forget to point to the proper filename and set the proper dbname.

Import / Export with MariaDB --defaults-file=/etc/mysql/debian.cnf obsolete

As you may know, some of the original creators of MySQL created MariaDB, since they didn't like the direction Oracle was bringing the project in. Personally I'm a huge fan of open sourced, community-driven and free projects like this. Now, this script will absolutely work with MariaDB — since they're mostly backwards compatible — but might throw you errors in some versions. If so, simply remove the   --defaults-file=/etc/mysql/debian.cnf from the mysql and mysqldump commands.

If you're interested, the mysql and mysqldump commands work without credentials, because of the unix socket authentication plugin, which simply checks if you're root.

Updates

2023-05-30

Added $CORRECTIONPERCENT to calculate 10% more size to correct for SQL overhead.

2022-08-17

Changed filename to include time too.

Simplified filename to dbexport.sh .

Used mysqldump options from The best rsync incremental backup script.

Added gzip compression, we're assuming a 90% compression ratio here.

Added separate section for MariaDB.

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.