MariaDB: Export huge tables using mysqldump

21 Jul 2020

Have you ever gotten this error when you are trying to export huge tables using mysqldump?

mysqldump: Error 1969: Query execution was interrupted (max_statement_time exceeded) when dumping table `table_name` at row: 999999999

You can try using the --quick option. It exported more than double the amount of records, but still failed with the same error.

$ mysqldump --quick  database_name table_name > some_file.sql

To overcome this problem, I had to extend the max_statement_time for the user using the below command.

GRANT USAGE ON *.* TO some_user@'%' WITH MAX_STATEMENT_TIME 43200;
GRANT SELECT ON database_name.table_name TO some_user@'%';

I exported the table using the configured user successfully.

$ mysqldump -u some_user -p --quick database_name table_name > some_file.sql