1

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.

  • 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 '18 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 '18 at 4:40
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.)

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.

  • 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 '18 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 '18 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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