The mysqldump command in MySQL is used to create backups of MySQL databases. One of the options available is --mysqld-long-query-time=#, which sets the long query time in seconds for the mysqldump operation. This option is particularly useful when you want to include or exclude queries that exceed a certain execution time threshold from the dump.
Here are several examples demonstrating how to use --mysqld-long-query-time=# with mysqldump:
Example 1: Setting a long query time of 5 seconds and dumping the database mydatabase:
mysqldump --mysqld-long-query-time=5 mydatabase > mydatabase_backup.sql
Explanation: This command sets the long query time to 5 seconds. When mysqldump is executed, queries taking longer than 5 seconds to execute will be included in the backup file mydatabase_backup.sql.
Example 2: Dumping all databases except test with a long query time of 2 seconds:
mysqldump --all-databases --exclude-database=test --mysqld-long-query-time=2 > alldatabases_backup.sql
Explanation: In this command, the long query time is set to 2 seconds. The dump includes all databases except test, and queries taking longer than 2 seconds are included in alldatabases_backup.sql.
Example 3: Dumping a single table mytable with a long query time of 1 second:
mysqldump mydatabase mytable --mysqld-long-query-time=1 > mytable_backup.sql
Explanation: This command dumps only the mytable table from mydatabase, setting the long query time to 1 second. Queries that take longer than 1 second are included in mytable_backup.sql.
Example 4: Dumping databases with a long query time of 0 seconds (includes all queries):
mysqldump --all-databases --mysqld-long-query-time=0 > alldatabases_full_backup.sql
Explanation: By setting the long query time to 0 seconds, all queries, regardless of their execution time, are included in the backup file alldatabases_full_backup.sql.
Verification Steps: After executing any of the above commands, you can verify the inclusion of queries based on their execution time by inspecting the generated SQL dump file using a text editor or by importing the dump file into another MySQL database instance to analyze query performance.
Also check similar articles.
Logging Errors to File in mysqldump
Locking Tables for Read in mysqldump
Locking All Tables Across Databases in mysqldump
Setting Line Terminator in mysqldump Output
Using INSERT IGNORE Statements in mysqldump
Discussion about this post