Safe online dumping of an InnoDB database in the presence of DDL changesConsistent logical backup of...
Fired for using Stack Exchange. What did I do wrong?
How bad is a Computer Science course that doesn't teach Design Patterns?
Build ASCII Podiums
Ramanujan's radical and how we define an infinite nested radical
Why is Bernie Sanders maximum accepted donation on actblue $5600?
Is it ethical to apply for a job on someone's behalf?
Why don't reads from /dev/zero count as I/O?
Coworker is trying to get me to sign his petition to run for office. How to decline politely?
Will linear voltage regulator step up current?
Why is Shelob considered evil?
Rudeness by being polite
How do I handle a blinded enemy which wants to attack someone it's sure is there?
What does the expression "Happy is as happy does" mean?
How to write painful torture scenes without being over-the-top
Stream.findFirst different than Optional.of?
Is Screenshot Time-tracking Common?
I hate taking lectures, can I still survive in academia?
What does “to the numbers” mean in landing clearance?
Could Comets or Meteors be used to Combat Global Warming?
How to encircle section of matrix in LaTeX?
How to achieve physical gender equality?
boss asked me to sign a resignation paper without a date on it along with my new contract
Buying a "Used" Router
Is Apex Sometimes Case Sensitive?
Safe online dumping of an InnoDB database in the presence of DDL changes
Consistent logical backup of databases that use MyISAM and InnoDB enginesWhat is the proper way to backup MySQL database with rsnapshot?mysqldump --single-transaction, yet update queries are waiting for the backupWhat are the optimal mysqldump settings?Is it safe to run parallel innodb single-transaction dumps of individual tables?Restoring MySQL dump ERROR 1449MySQL quick database transfer between two serversDumping consistent db on heavy I/O load for migrationWhat dump file options or MariaDB setting is slowing down the load of data?mysql dump is giving error while doing table alteration
When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + writes to the database.
However, there is the following nasty gotcha in the documentation:
While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
My backups are automated and periodic. I have no easy way to ensure that no one is going to issue such a command against a table while the dump is in progress.
So, I was wondering if there is a simple way to raise the isolation level of the dump from 'repeatable read' to 'serializable' and whether or not this would solve this gotcha while still minimizing blockage of concurrent reads + updates?
I'm aware that running with --lock-tables or --lock-all-tables instead doesn't suffer from the above gotcha, but I believe these approaches cause far more blocking.
I'm wondering if there is a happy middle ground between the two approaches? Or, if I want a surely safe and consistent dump then I still need to go with --lock-tables instead of --single-transaction --skip-lock-tables?
Thanks!
mysql innodb mariadb mysqldump
asked 15 hours ago
jschultz410jschultz410
1011
New contributor
jschultz410 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + writes to the database.
However, there is the following nasty gotcha in the documentation:
While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
My backups are automated and periodic. I have no easy way to ensure that no one is going to issue such a command against a table while the dump is in progress.
So, I was wondering if there is a simple way to raise the isolation level of the dump from 'repeatable read' to 'serializable' and whether or not this would solve this gotcha while still minimizing blockage of concurrent reads + updates?
I'm aware that running with --lock-tables or --lock-all-tables instead doesn't suffer from the above gotcha, but I believe these approaches cause far more blocking.
I'm wondering if there is a happy middle ground between the two approaches? Or, if I want a surely safe and consistent dump then I still need to go with --lock-tables instead of --single-transaction --skip-lock-tables?
Thanks!
mysql innodb mariadb mysqldump
asked 15 hours ago
jschultz410jschultz410
1011
New contributor
jschultz410 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + writes to the database.
However, there is the following nasty gotcha in the documentation:
While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
My backups are automated and periodic. I have no easy way to ensure that no one is going to issue such a command against a table while the dump is in progress.
So, I was wondering if there is a simple way to raise the isolation level of the dump from 'repeatable read' to 'serializable' and whether or not this would solve this gotcha while still minimizing blockage of concurrent reads + updates?
I'm aware that running with --lock-tables or --lock-all-tables instead doesn't suffer from the above gotcha, but I believe these approaches cause far more blocking.
I'm wondering if there is a happy middle ground between the two approaches? Or, if I want a surely safe and consistent dump then I still need to go with --lock-tables instead of --single-transaction --skip-lock-tables?
Thanks!
mysql innodb mariadb mysqldump
asked 15 hours ago
jschultz410jschultz410
1011
New contributor
jschultz410 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + writes to the database.
However, there is the following nasty gotcha in the documentation:
While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
My backups are automated and periodic. I have no easy way to ensure that no one is going to issue such a command against a table while the dump is in progress.
So, I was wondering if there is a simple way to raise the isolation level of the dump from 'repeatable read' to 'serializable' and whether or not this would solve this gotcha while still minimizing blockage of concurrent reads + updates?
I'm aware that running with --lock-tables or --lock-all-tables instead doesn't suffer from the above gotcha, but I believe these approaches cause far more blocking.
I'm wondering if there is a happy middle ground between the two approaches? Or, if I want a surely safe and consistent dump then I still need to go with --lock-tables instead of --single-transaction --skip-lock-tables?
Thanks!
mysql innodb mariadb mysqldump
mysql innodb mariadb mysqldump
asked 15 hours ago
jschultz410jschultz410
1011
New contributor
jschultz410 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 15 hours ago
jschultz410jschultz410
1011
New contributor
jschultz410 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 15 hours ago
jschultz410jschultz410
1011
New contributor
jschultz410 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 15 hours ago
jschultz410jschultz410
1011
asked 15 hours ago
jschultz410jschultz410
1011
1011
New contributor
jschultz410 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
jschultz410 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
jschultz410 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You could isolate the dump by placing it on a replication slave and stopping the replication before the dump.
As table modification isn't transactional, changing the isolation level won't help.
If you keep the binary logs (and use --master-data) with mysqldump, you could revert to the previous backup and replay the binary logs. This would be immune to the impacts to an single inconsistent dump.
answered 12 hours ago
danblackdanblack
1,8041212
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
});
}
});
jschultz410 is a new contributor. Be nice, and check out our Code of Conduct.
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%2f230309%2fsafe-online-dumping-of-an-innodb-database-in-the-presence-of-ddl-changes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could isolate the dump by placing it on a replication slave and stopping the replication before the dump.
As table modification isn't transactional, changing the isolation level won't help.
If you keep the binary logs (and use --master-data) with mysqldump, you could revert to the previous backup and replay the binary logs. This would be immune to the impacts to an single inconsistent dump.
answered 12 hours ago
danblackdanblack
1,8041212
add a comment |
You could isolate the dump by placing it on a replication slave and stopping the replication before the dump.
As table modification isn't transactional, changing the isolation level won't help.
If you keep the binary logs (and use --master-data) with mysqldump, you could revert to the previous backup and replay the binary logs. This would be immune to the impacts to an single inconsistent dump.
answered 12 hours ago
danblackdanblack
1,8041212
add a comment |
You could isolate the dump by placing it on a replication slave and stopping the replication before the dump.
As table modification isn't transactional, changing the isolation level won't help.
If you keep the binary logs (and use --master-data) with mysqldump, you could revert to the previous backup and replay the binary logs. This would be immune to the impacts to an single inconsistent dump.
answered 12 hours ago
danblackdanblack
1,8041212
You could isolate the dump by placing it on a replication slave and stopping the replication before the dump.
As table modification isn't transactional, changing the isolation level won't help.
If you keep the binary logs (and use --master-data) with mysqldump, you could revert to the previous backup and replay the binary logs. This would be immune to the impacts to an single inconsistent dump.
answered 12 hours ago
danblackdanblack
1,8041212
answered 12 hours ago
danblackdanblack
1,8041212
answered 12 hours ago
danblackdanblack
1,8041212
answered 12 hours ago
danblackdanblack
1,8041212
1,8041212
add a comment |
add a comment |
jschultz410 is a new contributor. Be nice, and check out our Code of Conduct.
jschultz410 is a new contributor. Be nice, and check out our Code of Conduct.
jschultz410 is a new contributor. Be nice, and check out our Code of Conduct.
jschultz410 is a new contributor. Be nice, and check out our Code of Conduct.
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%2f230309%2fsafe-online-dumping-of-an-innodb-database-in-the-presence-of-ddl-changes%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
