In order to see the process of exporting a database to an SQL file, you can use the Pipe Viewer utility.
Install Pipe Viewer on Ubuntu/Debian with the command:
sudo apt install pv
In Fedora, like this:
sudo yum install pv
I will give an example of a simple export with compression when the process is not visible and how much time is left until completion:
mysqldump zabbix | gzip -c > /backups/zabbix_full_`date +%Y-%m-%d`.sql.gz
In order for Pipe Viewer to calculate how much time is left until the export is complete, he needs to specify the size of the database, so let’s see its size (where zabbix is the name of the database):
SELECT Data_BB / POWER(1024,1) Data_KB, Data_BB / POWER(1024,2) Data_MB, Data_BB / POWER(1024,3) Data_GB FROM ( SELECT SUM(data_length) Data_BB FROM information_schema.tables WHERE table_schema IN ('zabbix') ) A;
Now start the export process (where 3369M – I specified the size of the database):
mysqldump -uUSERNAME -pPASSWORD zabbix | pv -s 3369M > zabbix.sql
Pipe Viewer will show how many megabytes are exported, how many percent are completed, how much time has passed and how much is left until the export is complete. If you specified an incorrect or approximate size of the database, then the process may end earlier or take longer, depending on the actual size of the database.