Mysql: set variable in mysqldump

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.

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR Nair

1226

bumped to the homepage by Community♦ 15 mins 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 |

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.

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR Nair

1226

bumped to the homepage by Community♦ 15 mins 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 |

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.

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR Nair

1226

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

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR Nair

1226

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

asked Nov 30 '18 at 3:36

SAGAR Nair

1226

edited Dec 4 '18 at 13:27

dbdemon

3,1122625

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 Nair

1226

asked Nov 30 '18 at 3:36

SAGAR Nair

1226

asked Nov 30 '18 at 3:36

SAGAR Nair

1226

bumped to the homepage by Community♦ 15 mins ago

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

bumped to the homepage by Community♦ 15 mins 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 |

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

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 |

2 Answers
2

active

oldest

votes

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.

answered Nov 30 '18 at 4:38

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

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemon

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');
});

Post as a guest

Name

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

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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.

answered Nov 30 '18 at 4:38

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

answered Nov 30 '18 at 4:38

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

answered Nov 30 '18 at 4:38

Rick James

43.3k22259

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.

answered Nov 30 '18 at 4:38

Rick James

43.3k22259

answered Nov 30 '18 at 4:38

Rick James

43.3k22259

answered Nov 30 '18 at 4:38

Rick James

43.3k22259

answered Nov 30 '18 at 4:38

Rick 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 |

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

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

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemon

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

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemon

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

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemon

3,1122625

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

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemon

3,1122625

edited Dec 4 '18 at 14:10

answered Dec 4 '18 at 13:56

dbdemon

3,1122625

answered Dec 4 '18 at 13:56

dbdemon

3,1122625

answered Dec 4 '18 at 13:56

dbdemon

3,1122625

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Database Administrators Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Xrhrft