Galera cluster mysqldump technique
I have the below setup for which I need to take a backup for.
- 5 nodes
- running MariaDB
- tables for databases use 'InnoDB'
- using Galera for clustering
- having Binary log, but I have cron job to flush (purging older than 10 or 30 days, each log file is about 1GB)
- the directory '/var/lib/mysql' within container, I guess it is the data directory, is ~33G today with 30 mysql-bin.* files
My questions:
- Should I run 'mysqldump' on all 5 nodes, or is it OK to run on only one node?
- How can I do incremental backup? Should I stop the cron job of purging binary log older than 10 or 30 days?
- Any suggestion?
Kindly help.
Thanks,
Ashish
mysql mariadb galera
asked Nov 15 '18 at 7:22
Ashish VermaAshish Verma
11
add a comment |
I have the below setup for which I need to take a backup for.
- 5 nodes
- running MariaDB
- tables for databases use 'InnoDB'
- using Galera for clustering
- having Binary log, but I have cron job to flush (purging older than 10 or 30 days, each log file is about 1GB)
- the directory '/var/lib/mysql' within container, I guess it is the data directory, is ~33G today with 30 mysql-bin.* files
My questions:
- Should I run 'mysqldump' on all 5 nodes, or is it OK to run on only one node?
- How can I do incremental backup? Should I stop the cron job of purging binary log older than 10 or 30 days?
- Any suggestion?
Kindly help.
Thanks,
Ashish
mysql mariadb galera
asked Nov 15 '18 at 7:22
Ashish VermaAshish Verma
11
add a comment |
I have the below setup for which I need to take a backup for.
- 5 nodes
- running MariaDB
- tables for databases use 'InnoDB'
- using Galera for clustering
- having Binary log, but I have cron job to flush (purging older than 10 or 30 days, each log file is about 1GB)
- the directory '/var/lib/mysql' within container, I guess it is the data directory, is ~33G today with 30 mysql-bin.* files
My questions:
- Should I run 'mysqldump' on all 5 nodes, or is it OK to run on only one node?
- How can I do incremental backup? Should I stop the cron job of purging binary log older than 10 or 30 days?
- Any suggestion?
Kindly help.
Thanks,
Ashish
mysql mariadb galera
asked Nov 15 '18 at 7:22
Ashish VermaAshish Verma
11
I have the below setup for which I need to take a backup for.
- 5 nodes
- running MariaDB
- tables for databases use 'InnoDB'
- using Galera for clustering
- having Binary log, but I have cron job to flush (purging older than 10 or 30 days, each log file is about 1GB)
- the directory '/var/lib/mysql' within container, I guess it is the data directory, is ~33G today with 30 mysql-bin.* files
My questions:
- Should I run 'mysqldump' on all 5 nodes, or is it OK to run on only one node?
- How can I do incremental backup? Should I stop the cron job of purging binary log older than 10 or 30 days?
- Any suggestion?
Kindly help.
Thanks,
Ashish
mysql mariadb galera
mysql mariadb galera
asked Nov 15 '18 at 7:22
Ashish VermaAshish Verma
11
asked Nov 15 '18 at 7:22
Ashish VermaAshish Verma
11
asked Nov 15 '18 at 7:22
Ashish VermaAshish Verma
11
asked Nov 15 '18 at 7:22
Ashish VermaAshish Verma
11
asked Nov 15 '18 at 7:22
Ashish VermaAshish Verma
11
11
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Galera mostly obviates the need for binlogs and dumps. Your 5 nodes are 5 copies of the data. If any one of the nodes dies, it can (and will) be reconstructed from one of the surviving nodes. (Cf "SST")
You could take a node out of the cluster, dump it by any means, then put it back in. This would provide you with a recovery path is you lost all the nodes.
If all the nodes are in the same datacenter, you are vulnerable to natural disasters (wildfire, earthquake, etc).
In writing out disaster recovery plans, you will discover the answer to "how many days of binlogs to keep".
answered Nov 15 '18 at 20:56
Rick JamesRick James
69.2k561101
Thanks for the response Rick, so the ideal way to take mysqldump is 1>Take a node out of the cluster 2>Take mysqldump from that node 3>Put the node back in the cluster. Also for incremental backups I can just copy the bin logs directly from one of the nodes in the cluster(without removing the node) and then purge the backed up log?
– Ashish Verma
Nov 16 '18 at 6:57
@AshishVerma - I strongly suspect that the binlogs cannot be copied from one node to another. See XtraBackup.
– Rick James
Nov 16 '18 at 15:14
When I take the node out of the cluster for backup, what happens to the transsactions running on that node, Do the transactions rollback or they commit? For a long running query on the node being taken out of the cluster, what would happen to that transaction? Also, for incremental backups I think I can just copy the binary logs from the backup node as I would only need to build only one node in case of total failure of cluster.
– Ashish Verma
Nov 17 '18 at 9:39
1
@AshishVerma - Taking the node out is more graceful than that. "Normally", when you bring the node back into the Cluster, it will do an "IST" (Incremental State Transfer) using stuff in the "gcache" and be quite fast. The fall back is to do an "SST". Study those in the documentation. (The choice between IST and SST is automatic.)
– Rick James
Nov 17 '18 at 15:18
Thanks Rick, I will need to study the documentation for planning a solution as per our environment.
– Ashish Verma
Nov 25 '18 at 16:02
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53314273%2fgalera-cluster-mysqldump-technique%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
Galera mostly obviates the need for binlogs and dumps. Your 5 nodes are 5 copies of the data. If any one of the nodes dies, it can (and will) be reconstructed from one of the surviving nodes. (Cf "SST")
You could take a node out of the cluster, dump it by any means, then put it back in. This would provide you with a recovery path is you lost all the nodes.
If all the nodes are in the same datacenter, you are vulnerable to natural disasters (wildfire, earthquake, etc).
In writing out disaster recovery plans, you will discover the answer to "how many days of binlogs to keep".
answered Nov 15 '18 at 20:56
Rick JamesRick James
69.2k561101
Thanks for the response Rick, so the ideal way to take mysqldump is 1>Take a node out of the cluster 2>Take mysqldump from that node 3>Put the node back in the cluster. Also for incremental backups I can just copy the bin logs directly from one of the nodes in the cluster(without removing the node) and then purge the backed up log?
– Ashish Verma
Nov 16 '18 at 6:57
@AshishVerma - I strongly suspect that the binlogs cannot be copied from one node to another. See XtraBackup.
– Rick James
Nov 16 '18 at 15:14
When I take the node out of the cluster for backup, what happens to the transsactions running on that node, Do the transactions rollback or they commit? For a long running query on the node being taken out of the cluster, what would happen to that transaction? Also, for incremental backups I think I can just copy the binary logs from the backup node as I would only need to build only one node in case of total failure of cluster.
– Ashish Verma
Nov 17 '18 at 9:39
1
@AshishVerma - Taking the node out is more graceful than that. "Normally", when you bring the node back into the Cluster, it will do an "IST" (Incremental State Transfer) using stuff in the "gcache" and be quite fast. The fall back is to do an "SST". Study those in the documentation. (The choice between IST and SST is automatic.)
– Rick James
Nov 17 '18 at 15:18
Thanks Rick, I will need to study the documentation for planning a solution as per our environment.
– Ashish Verma
Nov 25 '18 at 16:02
|
show 1 more comment
Galera mostly obviates the need for binlogs and dumps. Your 5 nodes are 5 copies of the data. If any one of the nodes dies, it can (and will) be reconstructed from one of the surviving nodes. (Cf "SST")
You could take a node out of the cluster, dump it by any means, then put it back in. This would provide you with a recovery path is you lost all the nodes.
If all the nodes are in the same datacenter, you are vulnerable to natural disasters (wildfire, earthquake, etc).
In writing out disaster recovery plans, you will discover the answer to "how many days of binlogs to keep".
answered Nov 15 '18 at 20:56
Rick JamesRick James
69.2k561101
Thanks for the response Rick, so the ideal way to take mysqldump is 1>Take a node out of the cluster 2>Take mysqldump from that node 3>Put the node back in the cluster. Also for incremental backups I can just copy the bin logs directly from one of the nodes in the cluster(without removing the node) and then purge the backed up log?
– Ashish Verma
Nov 16 '18 at 6:57
@AshishVerma - I strongly suspect that the binlogs cannot be copied from one node to another. See XtraBackup.
– Rick James
Nov 16 '18 at 15:14
When I take the node out of the cluster for backup, what happens to the transsactions running on that node, Do the transactions rollback or they commit? For a long running query on the node being taken out of the cluster, what would happen to that transaction? Also, for incremental backups I think I can just copy the binary logs from the backup node as I would only need to build only one node in case of total failure of cluster.
– Ashish Verma
Nov 17 '18 at 9:39
1
@AshishVerma - Taking the node out is more graceful than that. "Normally", when you bring the node back into the Cluster, it will do an "IST" (Incremental State Transfer) using stuff in the "gcache" and be quite fast. The fall back is to do an "SST". Study those in the documentation. (The choice between IST and SST is automatic.)
– Rick James
Nov 17 '18 at 15:18
Thanks Rick, I will need to study the documentation for planning a solution as per our environment.
– Ashish Verma
Nov 25 '18 at 16:02
|
show 1 more comment
Galera mostly obviates the need for binlogs and dumps. Your 5 nodes are 5 copies of the data. If any one of the nodes dies, it can (and will) be reconstructed from one of the surviving nodes. (Cf "SST")
You could take a node out of the cluster, dump it by any means, then put it back in. This would provide you with a recovery path is you lost all the nodes.
If all the nodes are in the same datacenter, you are vulnerable to natural disasters (wildfire, earthquake, etc).
In writing out disaster recovery plans, you will discover the answer to "how many days of binlogs to keep".
answered Nov 15 '18 at 20:56
Rick JamesRick James
69.2k561101
Galera mostly obviates the need for binlogs and dumps. Your 5 nodes are 5 copies of the data. If any one of the nodes dies, it can (and will) be reconstructed from one of the surviving nodes. (Cf "SST")
You could take a node out of the cluster, dump it by any means, then put it back in. This would provide you with a recovery path is you lost all the nodes.
If all the nodes are in the same datacenter, you are vulnerable to natural disasters (wildfire, earthquake, etc).
In writing out disaster recovery plans, you will discover the answer to "how many days of binlogs to keep".
answered Nov 15 '18 at 20:56
Rick JamesRick James
69.2k561101
answered Nov 15 '18 at 20:56
Rick JamesRick James
69.2k561101
answered Nov 15 '18 at 20:56
Rick JamesRick James
69.2k561101
answered Nov 15 '18 at 20:56
Rick JamesRick James
69.2k561101
69.2k561101
Thanks for the response Rick, so the ideal way to take mysqldump is 1>Take a node out of the cluster 2>Take mysqldump from that node 3>Put the node back in the cluster. Also for incremental backups I can just copy the bin logs directly from one of the nodes in the cluster(without removing the node) and then purge the backed up log?
– Ashish Verma
Nov 16 '18 at 6:57
@AshishVerma - I strongly suspect that the binlogs cannot be copied from one node to another. See XtraBackup.
– Rick James
Nov 16 '18 at 15:14
When I take the node out of the cluster for backup, what happens to the transsactions running on that node, Do the transactions rollback or they commit? For a long running query on the node being taken out of the cluster, what would happen to that transaction? Also, for incremental backups I think I can just copy the binary logs from the backup node as I would only need to build only one node in case of total failure of cluster.
– Ashish Verma
Nov 17 '18 at 9:39
1
@AshishVerma - Taking the node out is more graceful than that. "Normally", when you bring the node back into the Cluster, it will do an "IST" (Incremental State Transfer) using stuff in the "gcache" and be quite fast. The fall back is to do an "SST". Study those in the documentation. (The choice between IST and SST is automatic.)
– Rick James
Nov 17 '18 at 15:18
Thanks Rick, I will need to study the documentation for planning a solution as per our environment.
– Ashish Verma
Nov 25 '18 at 16:02
|
show 1 more comment
Thanks for the response Rick, so the ideal way to take mysqldump is 1>Take a node out of the cluster 2>Take mysqldump from that node 3>Put the node back in the cluster. Also for incremental backups I can just copy the bin logs directly from one of the nodes in the cluster(without removing the node) and then purge the backed up log?
– Ashish Verma
Nov 16 '18 at 6:57
@AshishVerma - I strongly suspect that the binlogs cannot be copied from one node to another. See XtraBackup.
– Rick James
Nov 16 '18 at 15:14
When I take the node out of the cluster for backup, what happens to the transsactions running on that node, Do the transactions rollback or they commit? For a long running query on the node being taken out of the cluster, what would happen to that transaction? Also, for incremental backups I think I can just copy the binary logs from the backup node as I would only need to build only one node in case of total failure of cluster.
– Ashish Verma
Nov 17 '18 at 9:39
1
@AshishVerma - Taking the node out is more graceful than that. "Normally", when you bring the node back into the Cluster, it will do an "IST" (Incremental State Transfer) using stuff in the "gcache" and be quite fast. The fall back is to do an "SST". Study those in the documentation. (The choice between IST and SST is automatic.)
– Rick James
Nov 17 '18 at 15:18
Thanks Rick, I will need to study the documentation for planning a solution as per our environment.
– Ashish Verma
Nov 25 '18 at 16:02
Thanks for the response Rick, so the ideal way to take mysqldump is 1>Take a node out of the cluster 2>Take mysqldump from that node 3>Put the node back in the cluster. Also for incremental backups I can just copy the bin logs directly from one of the nodes in the cluster(without removing the node) and then purge the backed up log?
– Ashish Verma
Nov 16 '18 at 6:57
Thanks for the response Rick, so the ideal way to take mysqldump is 1>Take a node out of the cluster 2>Take mysqldump from that node 3>Put the node back in the cluster. Also for incremental backups I can just copy the bin logs directly from one of the nodes in the cluster(without removing the node) and then purge the backed up log?
– Ashish Verma
Nov 16 '18 at 6:57
@AshishVerma - I strongly suspect that the binlogs cannot be copied from one node to another. See XtraBackup.
– Rick James
Nov 16 '18 at 15:14
@AshishVerma - I strongly suspect that the binlogs cannot be copied from one node to another. See XtraBackup.
– Rick James
Nov 16 '18 at 15:14
When I take the node out of the cluster for backup, what happens to the transsactions running on that node, Do the transactions rollback or they commit? For a long running query on the node being taken out of the cluster, what would happen to that transaction? Also, for incremental backups I think I can just copy the binary logs from the backup node as I would only need to build only one node in case of total failure of cluster.
– Ashish Verma
Nov 17 '18 at 9:39
When I take the node out of the cluster for backup, what happens to the transsactions running on that node, Do the transactions rollback or they commit? For a long running query on the node being taken out of the cluster, what would happen to that transaction? Also, for incremental backups I think I can just copy the binary logs from the backup node as I would only need to build only one node in case of total failure of cluster.
– Ashish Verma
Nov 17 '18 at 9:39
1
1
@AshishVerma - Taking the node out is more graceful than that. "Normally", when you bring the node back into the Cluster, it will do an "IST" (Incremental State Transfer) using stuff in the "gcache" and be quite fast. The fall back is to do an "SST". Study those in the documentation. (The choice between IST and SST is automatic.)
– Rick James
Nov 17 '18 at 15:18
@AshishVerma - Taking the node out is more graceful than that. "Normally", when you bring the node back into the Cluster, it will do an "IST" (Incremental State Transfer) using stuff in the "gcache" and be quite fast. The fall back is to do an "SST". Study those in the documentation. (The choice between IST and SST is automatic.)
– Rick James
Nov 17 '18 at 15:18
Thanks Rick, I will need to study the documentation for planning a solution as per our environment.
– Ashish Verma
Nov 25 '18 at 16:02
Thanks Rick, I will need to study the documentation for planning a solution as per our environment.
– Ashish Verma
Nov 25 '18 at 16:02
|
show 1 more comment
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53314273%2fgalera-cluster-mysqldump-technique%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
