Mysql: set variable in thesqldump

mariadbmariadb-10.2mysqldump

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.

Best Answer

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.)

Related Question