Restoring a MySQL database backup is a crucial task that can sometimes be time-consuming, especially for large databases. Monitoring the progress of the restore process is essential to estimate completion time and ensure everything is proceeding smoothly.
In this blog post, we will explore two distinct and effective methods to calculate the percentage progress of the MySQL restore process.
- Linux native Input/Output (I/O) statistics
- Pipe viewer utility
By following these approaches, we can effectively monitor the restoration process and manage your MySQL database restoration efficiently.
Monitoring MySQL database restore progress using Linux native Input/Output (I/O) statistics
We would use the /proc/<pid>/io file, which contains the IO statistics for each running process. For this particular case, we would use the pid of the MySQL restore process, which can provide valuable I/O statistics that can be used to estimate the progress of the MySQL restore process. The key metric to focus on is the rchar value, which represents the total number of bytes read from storage by the MySQL restore process since its start. By comparing this value with the size of the backup file being restored, we can calculate the restore progress in percentage.
Here’s how we can use the i/o utility to get an estimate of the MySQL backup restore process:
Step 1: Obtain the Process ID (PID) of MySQL restore process
Use the ps command or any other process monitoring tool to find the PID of the MySQL restore process. For example:
1 | $ ps aux | grep mysql |
Step 2: Access the I/O statistics
Once we have the PID, use the cat command to view the I/O statistics for the MySQL backup restore process:
1 | cat /proc/$(pid of restore)/io |
Replace the PID in the above command with the value found in step one.
Step 3: Focus on the ‘rchar’ Value
The output of the cat command will display various I/O statistics for the MySQL process. Look for the ‘rchar’ field, representing the total bytes read from storage.
Step 4: Calculate the restore progress
Compare the ‘rchar’ value with the size of the backup file being restored. If you know the size of the backup file in bytes, you can use the following formula to calculate the restore progress in percentage:
Restore Progress (%) = (rchar / Backup File Size) * 100
For example, if the ‘rchar’ value is 100 MB (100,000,000 bytes) and the backup file size is 1 GB (1,000,000,000 bytes), the restore progress would be (100,000,000 / 1,000,000,000) * 100 = 10%.
Step 5: Continuously monitor the progress
To get real-time updates on the restore progress, we can set up a monitoring process to periodically check the ‘rchar’ value and calculate the percentage. By comparing it with the total size of the backup file, we can monitor the progress continuously.
I have written a simple script to get the live progress of the restore.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | while true; do if [ -f /proc/<RESTORE_PID>/io ] then bytes_progress=$(cat /proc/<RESTORE_PID>/io | awk '/^rchar/ {print $2}') bytes_total=$(cat <BACKUP_FILE> | wc -c) progress=$(awk "BEGIN {printf "%.2f", ((100 * $bytes_progress)/$bytes_total)}") echo "Progress: $progress%" sleep 5; continue; else break; fi done |
In the above code block, replace the value of the RESTORE_PID with the PID of the MySQL restore process and BACKUP_FILE with the file name of the backup which we are restoring.
Let’s take an example of this method to understand how this works.
Consider we have a logical backup of a single table available taken using mysqldump in the backup.sql file.
1 | -rw-r--r--. 1 root root 2.3G Jul 19 05:14 backup.sql |
Restore this backup into MySQL using the below command:
1 | mysql -u [username] -p[password] -h [host] [database_name] < backup.sql |
Get the PID of the restore process:
1 2 | $ ps -ef | grep mysql root 2563 2449 13 08:42 pts/2 00:00:00 mysql test |
Replace the PID and backup file with the above value and execute the block code, which shows the progress of the restore every five seconds.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | while true; do if [ -f /proc/2563/io ] then > bytes_progress=$(cat /proc/2563/io | awk '/^rchar/ {print $2}') > bytes_total=$(cat /root/backup.sql | wc -c) > progress=$(awk "BEGIN {printf "%.2f", ((100 * $bytes_progress)/$bytes_total)}") > echo "Progress: $progress%" > sleep 5; > continue; > else > break; > fi > done Progress: 37.36% Progress: 39.91% Progress: 42.05% Progress: 44.56% Progress: 46.99% |
Monitoring MySQL database restore progress using Pipe Viewer (pv)
Now, we will explore how to monitor the progress of restoring a MySQL database logical backup restore using the Pipe Viewer utility. The Pipe Viewer utility, often abbreviated as pv, allows us to track the progress of data being transferred through a pipe. By leveraging pv, we can easily monitor the progress of our database restore process and gain insights into the estimated completion time. We will also discuss different options provided by pipe viewer and demonstrate their usage.
What is Pipe Viewer Utility?
The Pipe Viewer utility, pv, is a command-line tool designed to monitor the progress of data flowing through a pipe. It provides a visual representation of data transfer, including information such as transfer speed, progress percentage, and estimated completion time. By using pv, we can obtain real-time feedback on the progress of a task involving data transfer.
Installing Pipe Viewer:
To install pv in Linux, you can use the package manager specific to your distribution. For Ubuntu/Debian, use apt-get; for CentOS/RHEL, use yum;
For Debian-based systems, open the terminal and execute the following command:
1 | $ sudo apt-get install pv |
For RHEL-based Linux, first, we need to turn on the EPEL repository using the below command.
1 | $ yum install epel-release |
After that, run the following command to install the pv utility.
1 | $ sudo yum install pv |
Restore the database using pv:
Consider we have a logical backup available taken using mysqldump in backup.sql file, and we need to restore with an ETA.
1 | $ pv backup.sql | mysql -u [username] -p[password] -h [host] [database_name] |
Once the above restore command is executed, you’d see an output similar to the below which shows the progress of the database restore along with elapsed time, transfer rate, number of bytes transferred, and time remaining until completion.
1 | 242MiB 0:00:54 [3.81MiB/s] [===========================>] 25% ETA 0:02:40 |
If the backup file is compressed, the following command can be used to restore the database.
$ pv backup.sql.gz | gunzip | mysql -u [username] -p[password] -h[host] [database_name]
In this example, the logical backup backup.sql.gz is piped to pv, which displays the progress. The backup file is then decompressed using gunzip and piped to the mysql command to restore the database. Replace [username], [password], and [database_name] with your own values.
Monitor the progress:
Once the command is executed, pv will start displaying the progress of the database restore. You will see information such as data transfer speed, progress percentage, and estimated completion time. This allows you to keep track of the restore process and estimate when it will be finished.
After the completion of the restoration, you will see the below output:
1 | 963MiB 0:03:29 [ 4.6MiB/s] [===============================================>] 100% |
Understanding Pipe Viewer Options:
Pipe viewer provides various options to customize its behavior. Here are some commonly used options:
-p or –progress: Displays a progress bar.
-t or –timer: Shows the elapsed time since the start.
-r or –rate: Displays the transfer rate in bytes per second.
-b or –bytes: Shows the number of bytes transferred.
-e or –eta: Estimates the time remaining until completion.
-a or –average-rate: Shows the average transfer rate.
-s or –size: Specifies the total size of the input data.
By default, pipe viewer uses the -p, -t, -r, and -b options. This provides a basic progress bar, elapsed time, transfer rate, and bytes transferred.
Conclusion
Monitoring the progress of a MySQL database restoration is essential for efficient database management and timely completion. By leveraging I/O statistics and calculating the restore progress in percentage, you can gain valuable insights into the restoration process. By utilizing the Pipe Viewer utility (pv), monitoring the progress of a database restore becomes a straightforward task. With pv, you gain real-time insights into data transfer, including speed, percentage completion, and estimated completion time. This valuable information allows you to manage your database restoration process efficiently, ensuring you stay informed and can plan accordingly. Happy Monitoring!
Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.
Little optimization on statement
bytes_total=$(cat <BACKUP_FILE> | wc –c)
is slower than
bytes_total=$(wc –c <BACKUP_FILE>|awk ‘{print $1}’)
Thanks for sharing this information! Really helpful.
Correct the script:
Why? Because yours returns error: