Safe Import of a Large MySQL Dump on a Production Server

Importing a large SQL dump on a production server can significantly increase disk, CPU, and MySQL load. This is especially noticeable on servers with HDDs or when the server is already under high load from websites.
During database restoration, MySQL actively writes data, updates indexes, works with redo/undo logs, and performs disk synchronization operations. As a result, PHP-FPM, Nginx, and other services may become slower, while users may start receiving 502 and 504 errors.
To reduce the impact of the import on running websites, you can control the rate at which data is fed to MySQL and lower the priority of the client process.

How SQL Dump Import Works

During a standard import:
mysql -u root -p database_name < dump.sqltwo main processes are involved:
dump.sql
    ↓
mysql
    ↓
mysqld
    ↓
database

MySQL Client

The client is responsible for:

• reading the SQL file;
• processing SQL commands on the client side;
• sending queries to the MySQL server;
• receiving responses from mysqld.

MySQL Server — mysqld

The mysqld process performs the main workload:

• INSERT, UPDATE, and DELETE operations;
• table creation;
• structural changes;
• index updates;
• working with the InnoDB buffer pool;
• writing redo/undo logs;
• locking;
• writing data to disk;
• fsync and other I/O operations.

Therefore, it is important to understand:
The main load during an import is generated by the  mysqld  process, not by the  mysql  client.

Why an Import Can Slow Down Websites

During database restoration, mysqld actively uses the disk subsystem.
On a server with an HDD, this can be especially critical because PHP-FPM, Nginx, MySQL, and the database import may run simultaneously, competing for disk resources.
If the disk cannot handle all requests efficiently, I/O wait times increase.
As a result, you may see:

• high iowait;
• high await;
• %util close to 100%;
• slower MySQL performance;
• increased PHP-FPM response times;
• 502 Bad Gateway errors;
• 504 Gateway Timeout errors.

On SSDs, this problem is usually much less noticeable, but it can still occur during a large import or under high existing system load.

How to Determine Whether the Disk Is the Bottleneck

During the import, it is recommended to open a separate SSH session and monitor the system.

Checking Disk Load

iostat -xz 1Key metrics include:

• %util — how actively the device is being used;
• await — average I/O wait time;
• r/s — number of read operations;
• w/s — number of write operations.

If %util remains close to 100% for a prolonged period while await increases significantly during the import, the disk subsystem is becoming a bottleneck.

Checking Processes Using the Disk

iotop -oPaThis command shows processes that are actively performing read and write operations.
During an import, the main source of disk activity will usually be mysqld, not mysql.
This is normal: mysqld executes the SQL queries and writes the resulting data to the database.

Checking CPU and I/O Wait

vmstat 1Pay particular attention to wa. A high wa value means that the CPU is spending a significant amount of time waiting for I/O operations to complete.
For an overall view of system load, you can also use:
htop

Limiting Import Speed with pv

One of the most practical ways to control the import is to use pv.
For example:
pv -L 5m dump.sql | mysql -u root -p database_nameThe parameter:

-L 5m

limits the data transfer rate to approximately 5 MB/s.
The workflow looks like this:
dump.sql
     ↓
pv -L 5m
     ↓
mysql
     ↓
mysqld

This prevents the entire dump from being sent to MySQL at the maximum possible speed. It is recommended to start with a relatively low transfer rate.
For an HDD, you could start with:
pv -L 2m dump.sql | mysql -u root -p database_nameIf the system load remains acceptable, the rate can be increased:
pv -L 5m dump.sql | mysql -u root -p database_nameor:
pv -L 10m dump.sql | mysql -u root -p database_nameThe appropriate value depends on the disk performance, amount of RAM, current server load, and database structure.
An important point: pv does not directly limit the I/O performed by mysqld.
This distinction is critical.
The command:

pv -L 5m

limits the rate at which the client reads and sends data, but it does not impose a limit on the write operations performed by mysqld.
For example, the same amount of SQL data can create very different levels of disk activity:
INSERT into a table without indexes
          ↓
relatively low load
versus:
INSERT into a large table
          ↓
updating multiple indexes
          ↓
redo/undo operations
          ↓
fsync
          ↓
significantly higher disk load
Therefore, 5 MB/s of incoming SQL data does not mean 5 MB/s of disk writes.
pv should be considered a rate limiter for feeding work to MySQL, rather than a direct disk I/O limiter.

Using nice

You can also lower the CPU priority of the client:
nice -n 19 mysql -u root -p database_name < dump.sql

nice -n 19

sets a low CPU priority for the mysql process.
However, there is an important limitation: nice applies to the mysql client, not to mysqld. Therefore, it does not lower the CPU priority of the MySQL server itself. On most servers, the mysql client consumes significantly fewer resources than mysqld, so the effect of nice is usually secondary.

Using ionice

You can also lower the I/O priority of the client:
ionice -c 3 mysql -u root -p database_name < dump.sqlThe class:

-c 3

corresponds to the idle I/O class. This means that the client is allowed to perform disk operations primarily when the disk is not needed by processes with higher I/O priority. This can be useful if the SQL dump is stored on the same disk as the websites.
For example:
dump.sql
    ↓
read by mysql
    ↓
HDD
ionice reduces the priority of this read activity and therefore reduces its competition with other disk operations.
However, just like nice, ionice applies to the client, not to mysqld.

Combining pv, nice, and ionice

For a production server, you can use:
pv -L 5m dump.sql | nice -n 19 ionice -c 3 mysql -u root -p database_nameEach tool has a different purpose:

Tool	Purpose
pv -L 5m	Limits the rate at which the dump is fed to MySQL
nice -n 19	Lowers the CPU priority of the client
ionice -c 3	Lowers the I/O priority of the client
mysqld	Performs the actual import

The main mechanism for controlling the import rate is pv.

Why You Should Not Lower the Priority of mysqld

It is possible to change the priority of the MySQL server process itself, but this can be potentially dangerous on a production server.
mysqld simultaneously handles:

• the import;
• website queries;
• background tasks;
• cron jobs;
• administrative operations.

In other words, you cannot simply say:  "Let's lower the priority of mysqld to slow down the import." The priority will apply to all MySQL activity. As a result, normal website queries may become slower along with the import. Therefore, it is generally safer to control the rate at which data is fed to the MySQL client rather than artificially limiting the priority of the entire mysqld process.

Importing a Compressed Dump

If the dump is in .gz format, it can be imported as follows:
pv -L 5m dump.sql.gz | gzip -dc | mysql -u root -p database_nameHowever, there is an important detail: pv limits the read rate of the compressed file.
For example:
5 MB/s .gz
    ↓
gzip
    ↓
can become a significantly larger amount of SQL data
Therefore, the appropriate limit should be determined based on the actual server load.
On systems with limited CPU resources, the additional CPU load caused by gzip decompression may also be noticeable.

How to Determine Whether the Import Is Affecting Websites

During the import, you should monitor not only MySQL but also the entire web stack.

CPU

htop

RAM

free -h

Disk

iostat -xz 1

Process I/O

iotop -oPa

I/O Wait

vmstat 1

Nginx

tail -100 /var/log/nginx/error.log

Apache

tail -100 /var/log/apache2/error.log

PHP-FPM

systemctl status php8.x-fpmIf you observe the following at the same time:

• high iowait;
• high await;
• %util close to 100%;
• increased website response times;
• 502/504 errors;
• long-running MySQL queries;

then the import is having a significant impact on the production environment.

Recommendations

For a safer large-dump import on a production server:

1. Do not start a large import without monitoring the system load.
2. Start with a low pv rate.
3. Monitor iostat, vmstat, iotop, and htop.
4. If necessary, use nice and ionice for the client process.
5. Do not treat nice and ionice as mechanisms for limiting mysqld load.
6. Take table locks and ALTER TABLE operations into account.
7. If possible, import the database into a separate database and switch the application to it after verification.
8. On HDD-based systems, pay particular attention to await, %util, and iowait.
9. Adjust the import rate based on the actual server load rather than using a fixed MB/s value.

Conclusion

When performing a large MySQL import, it is important to distinguish between the mysql client and the mysqld server.
mysql reads the dump and sends SQL commands to the server.
mysqld executes those commands and generates most of the CPU, RAM, and disk I/O load.
Therefore:
pv → controls the rate at which data is fed to MySQL
nice → lowers the CPU priority of the client
ionice → lowers the I/O priority of the client
mysqld → performs the actual import
For a production server, the most practical approach is usually not to lower the priority of the entire mysqld process, but to control the rate at which the dump is fed to MySQL while monitoring the actual system load.
This allows the import to proceed more smoothly and reduces the likelihood that restoring a large database will significantly affect the performance of websites running on the server.