Introducing the mysqld-long-query-time Option in MySQL 8.0.30 mysqldump and Its Practical Usage

1 New Feature Introduction

MySQL 8.0.30 adds the mysqldump logical backup tool option mysqld-long-query-time (refer to option [1]), which sets a session‑level slow‑query threshold for the backup session via the long_query_time variable.

Why It Matters

In production environments the global long_query_time is usually set low; because mysqldump performs full‑table scans, its queries often exceed this threshold and flood the slow‑query log with backup‑related entries. Specifying mysqld-long-query-time allows a separate, appropriate threshold for the backup session, reducing noise in the log.

2 Usage

Specify the backup option mysqld-long-query-time with a concrete value (range 0‑31536000 seconds). If omitted, mysqldump falls back to the global long_query_time variable.

# Add mysqldump backup option --mysqld-long-query-time=100
# Queries with execution time < 100 s will not be recorded in the slow‑query log
mysqldump -ubackup_user -h10.186.58.39 -P3000 -p123456 --all-databases \
--mysqld-long-query-time=100 > /opt/all_databases.sql

3 Comparative Experiment

Two scenarios were tested: (1) running mysqldump without the new option, and (2) running it with --mysqld-long-query-time=100 . The slow‑query logs were examined after backing up tables of varying sizes (10 w, 100 w, 1 000 w, 5 000 w).

Environment Information

# Database server configuration
8c16g150g(ssd)

# MySQL version
SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.30   |
+-----------+

# mysqldump version
mysqldump  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

# Global slow‑query threshold
SHOW VARIABLES LIKE '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+

# Slow‑query log enabled
SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| slow_query_log      | ON    |
| slow_query_log_file | /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log |
+---------------------+-------+

Scenario 1 – No Option

Running mysqldump without mysqld-long-query-time generated numerous slow‑query entries, e.g., a 77.57 s query for a 50 M‑row table exceeded the global 0.1 s threshold.

# Sample slow‑query log entry
# Time: 2024-03-20T06:25:51.689799Z
# User@Host: backup_user[backup_user] @ node1 [10.186.58.39]  Id: 31
# Query_time: 77.574346  Lock_time: 0.000000  Rows_sent: 50000000  Rows_examined: 50000000
use fq_5000w;
SELECT * FROM `sbtest1`;

Scenario 2 – With Option

After clearing the slow‑query log and running mysqldump with --mysqld-long-query-time=100 , the log remained empty, confirming that backup‑generated queries were filtered out.

# Clear slow‑query log
cat /dev/null > /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log

# Run backup with option
mysqldump -ubackup_user -h10.186.58.39 -P3000 -p123456 --all-databases \
--mysqld-long-query-time=100 > /opt/all_databases.sql

# Verify log is empty
cat /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log

4 Conclusion

In MySQL 8.0.30, setting the mysqld-long-query-time option for mysqldump allows a suitable session‑level slow‑query threshold, effectively filtering out backup‑induced slow queries from the log.

5 Additional Notes

The mysqld-long-query-time option is also supported in MySQL 5.7 backups.

The option does not accept decimal values (e.g., 0.1); using a non‑integer will cause the backup to fail.

References

[1] option_mysqldump_mysqld-long-query-time