How to see the process of exporting a SQL file?

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.

See also my articles:
How to see the process of importing a SQL file?
Import and export MySQL databases
View information about MySQL databases

Leave a comment

Leave a Reply

Discover more from IT Blog

Subscribe now to keep reading and get access to the full archive.

Continue reading