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.
mariadb mysqldump mariadb-10.2
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♦ 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.
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
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♦ 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 largeMAX_STATEMENT_TIMEdoes that take precedence over the global variable? Otherwise a creating a feature task of--dump-variable=valueseems 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
edited Dec 4 '18 at 13:27
dbdemon
3,1122625
asked Nov 30 '18 at 3:36
SAGAR NairSAGAR Nair
1226
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
mariadb mysqldump mariadb-10.2
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
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
edited Dec 4 '18 at 13:27
dbdemon
3,1122625
3,1122625
asked Nov 30 '18 at 3:36
SAGAR NairSAGAR Nair
1226
asked Nov 30 '18 at 3:36
SAGAR NairSAGAR Nair
1226
asked Nov 30 '18 at 3:36
SAGAR NairSAGAR Nair
1226
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 largeMAX_STATEMENT_TIMEdoes that take precedence over the global variable? Otherwise a creating a feature task of--dump-variable=valueseems 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 largeMAX_STATEMENT_TIMEdoes that take precedence over the global variable? Otherwise a creating a feature task of--dump-variable=valueseems 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
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
@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 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'sMAX_STATEMENT_TIMEdoALTER 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.)
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
});
}
});
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
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
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
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 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'sMAX_STATEMENT_TIMEdoALTER 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 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'sMAX_STATEMENT_TIMEdoALTER 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 JamesRick 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 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
answered Nov 30 '18 at 4:38
Rick JamesRick James
43.3k22259
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'sMAX_STATEMENT_TIMEdoALTER 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'sMAX_STATEMENT_TIMEdoALTER 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
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
@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.)
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.)
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.)
answered Dec 4 '18 at 13:56
dbdemondbdemon
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.)
answered Dec 4 '18 at 13:56
dbdemondbdemon
3,1122625
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
3,1122625
add a comment |
add a comment |
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.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If you create / alter the root user with a large
MAX_STATEMENT_TIMEdoes that take precedence over the global variable? Otherwise a creating a feature task of--dump-variable=valueseems 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