Mixing
How to get the Slave commit timestamp using MySql replication?
I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that.
By reading its reference manual, I found out that I could get useful information in the performance_schema database.
When I look into the table replication_connection_status on the Slave server I can find the following information, among others:
- When the original Master commited the transaction (LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)
- When the immediate Master commited the transaction (LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP).
- When the Slave started and finished to write the updates in its relay log (LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP).
But it lacks the information about when the transaction was commited on this Slave server.
The alternative solution:
Perform replication of these updates, from the Slave server to a third server (its master would be the Slave we're trying to get this info from), and then looking the column LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP on the third server, which would be when the main Slave server has commited the transaction.
A possible solution:
The table events_transactions_history may contain the time where the transaction ended, but the time format is confusing.
According to 2:
The timer baseline (“time zero”) occurs at Performance Schema
initialization during server startup. TIMER_START and TIMER_END values
in events represent picoseconds since the baseline. TIMER_WAIT values
are durations in picoseconds.
I have no idea of how to know what this "time zero" would be exactly, all we're given is that it occurs during server startup, but how could I get this exact time?
And of course, if there's an easier solution that I've missed, let me know.
Thanks.
mysql replication database-replication mysql-8.0
asked Nov 22 '18 at 5:52
Wilson NetoWilson Neto
265
add a comment |
I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that.
By reading its reference manual, I found out that I could get useful information in the performance_schema database.
When I look into the table replication_connection_status on the Slave server I can find the following information, among others:
- When the original Master commited the transaction (LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)
- When the immediate Master commited the transaction (LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP).
- When the Slave started and finished to write the updates in its relay log (LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP).
But it lacks the information about when the transaction was commited on this Slave server.
The alternative solution:
Perform replication of these updates, from the Slave server to a third server (its master would be the Slave we're trying to get this info from), and then looking the column LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP on the third server, which would be when the main Slave server has commited the transaction.
A possible solution:
The table events_transactions_history may contain the time where the transaction ended, but the time format is confusing.
According to 2:
The timer baseline (“time zero”) occurs at Performance Schema
initialization during server startup. TIMER_START and TIMER_END values
in events represent picoseconds since the baseline. TIMER_WAIT values
are durations in picoseconds.
I have no idea of how to know what this "time zero" would be exactly, all we're given is that it occurs during server startup, but how could I get this exact time?
And of course, if there's an easier solution that I've missed, let me know.
Thanks.
mysql replication database-replication mysql-8.0
asked Nov 22 '18 at 5:52
Wilson NetoWilson Neto
265
add a comment |
I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that.
By reading its reference manual, I found out that I could get useful information in the performance_schema database.
When I look into the table replication_connection_status on the Slave server I can find the following information, among others:
- When the original Master commited the transaction (LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)
- When the immediate Master commited the transaction (LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP).
- When the Slave started and finished to write the updates in its relay log (LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP).
But it lacks the information about when the transaction was commited on this Slave server.
The alternative solution:
Perform replication of these updates, from the Slave server to a third server (its master would be the Slave we're trying to get this info from), and then looking the column LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP on the third server, which would be when the main Slave server has commited the transaction.
A possible solution:
The table events_transactions_history may contain the time where the transaction ended, but the time format is confusing.
According to 2:
The timer baseline (“time zero”) occurs at Performance Schema
initialization during server startup. TIMER_START and TIMER_END values
in events represent picoseconds since the baseline. TIMER_WAIT values
are durations in picoseconds.
I have no idea of how to know what this "time zero" would be exactly, all we're given is that it occurs during server startup, but how could I get this exact time?
And of course, if there's an easier solution that I've missed, let me know.
Thanks.
mysql replication database-replication mysql-8.0
asked Nov 22 '18 at 5:52
Wilson NetoWilson Neto
265
I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that.
By reading its reference manual, I found out that I could get useful information in the performance_schema database.
When I look into the table replication_connection_status on the Slave server I can find the following information, among others:
- When the original Master commited the transaction (LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)
- When the immediate Master commited the transaction (LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP).
- When the Slave started and finished to write the updates in its relay log (LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP).
But it lacks the information about when the transaction was commited on this Slave server.
The alternative solution:
Perform replication of these updates, from the Slave server to a third server (its master would be the Slave we're trying to get this info from), and then looking the column LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP on the third server, which would be when the main Slave server has commited the transaction.
A possible solution:
The table events_transactions_history may contain the time where the transaction ended, but the time format is confusing.
According to 2:
The timer baseline (“time zero”) occurs at Performance Schema
initialization during server startup. TIMER_START and TIMER_END values
in events represent picoseconds since the baseline. TIMER_WAIT values
are durations in picoseconds.
I have no idea of how to know what this "time zero" would be exactly, all we're given is that it occurs during server startup, but how could I get this exact time?
And of course, if there's an easier solution that I've missed, let me know.
Thanks.
mysql replication database-replication mysql-8.0
mysql replication database-replication mysql-8.0
asked Nov 22 '18 at 5:52
Wilson NetoWilson Neto
265
asked Nov 22 '18 at 5:52
Wilson NetoWilson Neto
265
edited Nov 22 '18 at 13:24
Wilson Neto
asked Nov 22 '18 at 5:52
Wilson NetoWilson Neto
265
asked Nov 22 '18 at 5:52
Wilson NetoWilson Neto
265
asked Nov 22 '18 at 5:52
Wilson NetoWilson Neto
265
265
add a comment |
add a comment |
0
active
oldest
votes
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%2f53424647%2fhow-to-get-the-slave-commit-timestamp-using-mysql-replication%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53424647%2fhow-to-get-the-slave-commit-timestamp-using-mysql-replication%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
