How to monitor the progress of backup in MySQL or MariaDB

Scenario:

I am taking backup of many databases in MySQL or MairaDB and I would like to monitor the progress of backups.


Solution:

Either MySQL or MariaDB does not provide direct way to monitor the progress of backup. You can use PV("Pipe Viewer") to monitor the progress of backups. PV("Pipe Viewer") is a tool for monitoring the progress of data through the pipeline. It can be used to visualize how quickly data is passing through and progress.

First of all we need to install the PV. I am using Centos. If  I try to install PV on Centos, I will get below error.
sudo yum install pv
Including mirror: mirror.es.its.nyu.edu
 * base: mirror.es.its.nyu.edu
 * extras: repo.ialab.dsu.edu
 * updates: centos-distro.cavecreek.net
No package pv available.
Error: Nothing to do

If you are running Centos on your Server, first of all you will be installing EPEL (Extra Packages for Enterprise Linux) Repository. Below below commands to intall EPEL Repository.

wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# rpm -ivh epel-release-latest-7.noarch.rpm
Once the EPEL repository installed, you can check by using below command

# yum repolist

Now you are ready to install the pv, use below command to run the pv

# sudo yum install pv


Backup with Progress Bar:

Once the pv is installed, you will be using mysqldump with pv. In PV you have to provide estimated size of file.  You can get estimated size of backups by checking the size of database. The backup file is most like smaller than actual database size.

SELECT table_schema AS "DBName", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "DB Size in(MB)" FROM information_schema.TABLES GROUP BY table_schema;
 Use below command to take backup with progress

# mysqldump  | pv --progress --size 50m > backupfile.sql


50M is 50MB that is I am expecting the size of my file.

Let's say If I am taking the  backup of all the databases in single file, I can use below mysqldump command with pv to monitor the progress of my backup. I am expecting the size of backup file around 325 MB.

# sudo mysqldump -u root -p --all-databases | pv --progress --size 325M >/mysqlbackup/alldbs.sql