Sfrgttk

Has a sovereign Communist government ever run, and conceded loss, on a fair election?

What is Tony Stark injecting into himself in Iron Man 3?

Do I need a return ticket to Canada if I'm a Japanese National?

How to install "rounded" brake pads

Should I file my taxes? No income, unemployed, but paid 2k in student loan interest

The (Easy) Road to Code

Use Mercury as quenching liquid for swords?

Why do phishing e-mails use faked e-mail addresses instead of the real one?

Is it a Cyclops number? "Nobody" knows!

What does it take to become a wilderness skills guide as a business?

Inorganic chemistry handbook with reaction lists

Why does a car's steering wheel get lighter with increasing speed

What is the best index strategy or query SELECT when performing a search/lookup BETWEEN IP address (IPv4 and IPv6) ranges?

Vector-transposing function

Why is my explanation wrong?

What does *dead* mean in *What do you mean, dead?*?

How spaceships determine each other's mass in space?

What is the orbit and expected lifetime of Crew Dragon trunk?

Boss Telling direct supervisor I snitched

How do property taxes on school district bonds work?

School performs periodic password audits. Is my password compromised?

What is better: yes / no radio, or simple checkbox?

What is the purpose of a disclaimer like "this is not legal advice"?

A vote on the Brexit backstop

Mysql: set variable in mysqldump

mysql works, mysqldump doesn'tmysqldump issues with DreamHostmysqldump confusionHow to make mysqldump use /*!40101 SET character_set_client = utf8mf4 */;?mysqldump errorSOLUTION - OOM Killer was killing mariadb every hour or soINET6_NTOA output is set as null to varchar(39) variablejson functions: double quotes added when going through a variableWhich way should be selected when move from mysql to mariadb?Join against two tables to get single combined result set

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.

mariadb mysqldump mariadb-10.2

share|improve this question

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR NairSAGAR Nair

1226

bumped to the homepage by Community♦ 33 secs ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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.

mariadb mysqldump mariadb-10.2

share|improve this question

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR NairSAGAR Nair

1226

bumped to the homepage by Community♦ 33 secs ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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.

mariadb mysqldump mariadb-10.2

share|improve this question

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR NairSAGAR Nair

1226

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

share|improve this question

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR NairSAGAR Nair

1226

share|improve this question

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR NairSAGAR Nair

1226

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR NairSAGAR Nair

1226

asked Nov 30 '18 at 3:36

SAGAR NairSAGAR Nair

1226

2 Answers
2

active

oldest

votes

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.

share|improve this answer

answered Nov 30 '18 at 4:38

Rick JamesRick James

43.3k22259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

0

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

share|improve this answer

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemondbdemon

3,1122625

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223798%2fmysql-set-variable-in-mysqldump%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

answered Nov 30 '18 at 4:38

Rick JamesRick James

43.3k22259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Nov 30 '18 at 4:38

Rick JamesRick James

43.3k22259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Nov 30 '18 at 4:38

Rick JamesRick James

43.3k22259

share|improve this answer

answered Nov 30 '18 at 4:38

Rick JamesRick James

43.3k22259

answered Nov 30 '18 at 4:38

Rick JamesRick James

43.3k22259

answered Nov 30 '18 at 4:38

Rick JamesRick James

43.3k22259

0

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

share|improve this answer

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemondbdemon

3,1122625

add a comment | 

0

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

share|improve this answer

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemondbdemon

3,1122625

add a comment | 

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

share|improve this answer

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemondbdemon

3,1122625

max_statement_time=10000

share|improve this answer

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemondbdemon

3,1122625

answered Dec 4 '18 at 13:56

dbdemondbdemon

3,1122625

answered Dec 4 '18 at 13:56

dbdemondbdemon

3,1122625