2

I recently added a global variable in mmy.cnf max_statement_time=120 ; to stop long running queries and to throw an exception. Its working fine. Then I am unable to run mysqldump for backing up all my databases , obviously its true that we have large databases so backing up using mysqldump will take hours since here I have added max_statement_time to 120 seconds only. So how I can pass this as a session variable thru mysqldump to set it to hours just for mysqldump and still global value remains 120 seconds itself. Apparently I am after something like below :

mysqldump --max_statement_time=1000000 -u root -pMYPASS --all-databases > file.sql

Do we have any option there ? I went thru the mysqldump and found that it has --variable-name=value , but its not accepting this. I am using Mariadb 10.2 version.

Improve this question
2
  • If you create / alter the root user with a large MAX_STATEMENT_TIME does that take precedence over the global variable? Otherwise a creating a feature task of --dump-variable=value seems like sane feature request.
    – danblack
    Nov 30, 2018 at 4:04
  • @danblack Hey could u please tell me how to alter the root user with max_statement_time ? So I could try that.
    – SAGAR Nair
    Nov 30, 2018 at 4:40

2 Answers 2

Reset to default
1

I believe you should be able to specify a session variable by placing it in a backup.cnf file:

max_statement_time=10000

And then using the --defaults-file=backup.cnf parameter to mysqldump.

(You can also place user credentials in that file so that you don't have to show the username and password on the command-line, which is a potential security issue.)

Improve this answer
1
  • The config is not part of the [mysqldump] command line option. But you have to put in the daemon section [mysqld]
    – Thanh Trung
    Jul 14, 2021 at 14:29
0

I suggest you create a new user just for dumping. Give it SUPER privilege so that it can do what it needs. And also give it unlimited max_execution_time.

Then you don't need an extra parameter in the places you were guessing about.

Improve this answer
2
  • Would u givem me the query to create a backup user as u specified with grant option to have max_execution_time, please.
    – SAGAR Nair
    Nov 30, 2018 at 4:41
  • @SAGARNair - To modify an existing user's MAX_STATEMENT_TIME do ALTER USER testuser@localhost WITH MAX_STATEMENT_TIME 10;. Or alternatively: GRANT USAGE ON *.* TO testuser@localhost WITH MAX_STATEMENT_TIME 10; To create a new user, do: CREATE USER testuser@localhost IDENTIFIED BY 'the password' WITH MAX_STATEMENT_TIME 10;
    – dbdemon
    Dec 4, 2018 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.