Failed MySQL Replication
English is not my native language so please be patient.
This is the second time I had setup a Master-Slave replication with failures, most of other Q&A show some errors or similar, sadly my configuration finish "fine" but then doesn't replicate.
Master Server: Ubuntu 16.04
Master Database type: MySQL 5.7.19
Slave Server: Ubuntu 16.04
Slave Database type: MySQL 5.7.19
This is the tutorial (which had worked in others servers) I follow, with some aditional steps:
- Setup Bin days to 10. (on master) (This is a database which cannot stop)
- Change bind-address = A.B.C.D (on master)
- Set server-id on master = 1
- Set log_bin and binlog_do_db (on master)
- Restart MySQL service (on master)
- Create replication user and grant privileges. Flush privileges.
- Wait 'till 2AM and get Master Status.
- Dump Database and "scp" to slave Server
- Create database in the slave (same scheme)
- Import database (this takes about 7-8 days)
- Set server-id on slave = 2
- Add relay-log info (on slave)
- Set log_bin and binlog_do_db (on slave)
- Change master host, user, password, file and position.
- Restart MySQL and Start Slave
When I hit the classic "SHOW SLAVE STATUS G", can see the Slave several seconds (465214) behind Master, but this time is getting lower. When this time get to zero, the horror, replication isn't writing nor updating. ¿What's missing?
Thanks in advance for your help.
"SHOW SLAVE STATUS G"
Example - SELECT * FROM cx_retorno ORDER By retfecret DESC LIMIT 5:
Master
retcod retdesc retfecret
294529205 2018-12-27 09:26:11 2018-12-27 09:26:11
294459995 2018-12-27 09:26:08 2018-12-27 09:26:08
294507948 2018-12-27 09:26:05 2018-12-27 09:26:05
294627475 2018-12-27 09:26:02 2018-12-27 09:26:02
Slave
retcod retdesc retfecret
294453420 SIN MOTIVO 2018-12-15 15:29:58
294453421 SIN MOTIVO 2018-12-15 15:29:58
294453422 SIN MOTIVO 2018-12-15 15:29:58
294453423 SIN MOTIVO 2018-12-15 15:29:58
294453424 SIN MOTIVO 2018-12-15 15:29:57
mysql database replication database-replication
asked Dec 27 at 13:11
Elber CM
116213
add a comment |
English is not my native language so please be patient.
This is the second time I had setup a Master-Slave replication with failures, most of other Q&A show some errors or similar, sadly my configuration finish "fine" but then doesn't replicate.
Master Server: Ubuntu 16.04
Master Database type: MySQL 5.7.19
Slave Server: Ubuntu 16.04
Slave Database type: MySQL 5.7.19
This is the tutorial (which had worked in others servers) I follow, with some aditional steps:
- Setup Bin days to 10. (on master) (This is a database which cannot stop)
- Change bind-address = A.B.C.D (on master)
- Set server-id on master = 1
- Set log_bin and binlog_do_db (on master)
- Restart MySQL service (on master)
- Create replication user and grant privileges. Flush privileges.
- Wait 'till 2AM and get Master Status.
- Dump Database and "scp" to slave Server
- Create database in the slave (same scheme)
- Import database (this takes about 7-8 days)
- Set server-id on slave = 2
- Add relay-log info (on slave)
- Set log_bin and binlog_do_db (on slave)
- Change master host, user, password, file and position.
- Restart MySQL and Start Slave
When I hit the classic "SHOW SLAVE STATUS G", can see the Slave several seconds (465214) behind Master, but this time is getting lower. When this time get to zero, the horror, replication isn't writing nor updating. ¿What's missing?
Thanks in advance for your help.
"SHOW SLAVE STATUS G"
Example - SELECT * FROM cx_retorno ORDER By retfecret DESC LIMIT 5:
Master
retcod retdesc retfecret
294529205 2018-12-27 09:26:11 2018-12-27 09:26:11
294459995 2018-12-27 09:26:08 2018-12-27 09:26:08
294507948 2018-12-27 09:26:05 2018-12-27 09:26:05
294627475 2018-12-27 09:26:02 2018-12-27 09:26:02
Slave
retcod retdesc retfecret
294453420 SIN MOTIVO 2018-12-15 15:29:58
294453421 SIN MOTIVO 2018-12-15 15:29:58
294453422 SIN MOTIVO 2018-12-15 15:29:58
294453423 SIN MOTIVO 2018-12-15 15:29:58
294453424 SIN MOTIVO 2018-12-15 15:29:57
mysql database replication database-replication
asked Dec 27 at 13:11
Elber CM
116213
How do you know "replication isn't writing nor updating"? The slave seconds behind master steadily decreasing to zero is promising and would imply that things are in fact replicating. Can you show us the output of "show slave status"?
– MarCPlusPlus
Dec 27 at 13:28
1
@MarCPlusPlus, thanks for your comment. I know isn't updating nor writing because when I search some register cant find it in the slave, even when exist in master. (A simple "Select COUNT(*) from table" could be use as example)
– Elber CM
Dec 27 at 14:20
@ElberCM Include an actual example.
– Petzy
Dec 27 at 14:27
1
@Petzy, thanks for your comment. Done.
– Elber CM
Dec 27 at 14:45
add a comment |
English is not my native language so please be patient.
This is the second time I had setup a Master-Slave replication with failures, most of other Q&A show some errors or similar, sadly my configuration finish "fine" but then doesn't replicate.
Master Server: Ubuntu 16.04
Master Database type: MySQL 5.7.19
Slave Server: Ubuntu 16.04
Slave Database type: MySQL 5.7.19
This is the tutorial (which had worked in others servers) I follow, with some aditional steps:
- Setup Bin days to 10. (on master) (This is a database which cannot stop)
- Change bind-address = A.B.C.D (on master)
- Set server-id on master = 1
- Set log_bin and binlog_do_db (on master)
- Restart MySQL service (on master)
- Create replication user and grant privileges. Flush privileges.
- Wait 'till 2AM and get Master Status.
- Dump Database and "scp" to slave Server
- Create database in the slave (same scheme)
- Import database (this takes about 7-8 days)
- Set server-id on slave = 2
- Add relay-log info (on slave)
- Set log_bin and binlog_do_db (on slave)
- Change master host, user, password, file and position.
- Restart MySQL and Start Slave
When I hit the classic "SHOW SLAVE STATUS G", can see the Slave several seconds (465214) behind Master, but this time is getting lower. When this time get to zero, the horror, replication isn't writing nor updating. ¿What's missing?
Thanks in advance for your help.
"SHOW SLAVE STATUS G"
Example - SELECT * FROM cx_retorno ORDER By retfecret DESC LIMIT 5:
Master
retcod retdesc retfecret
294529205 2018-12-27 09:26:11 2018-12-27 09:26:11
294459995 2018-12-27 09:26:08 2018-12-27 09:26:08
294507948 2018-12-27 09:26:05 2018-12-27 09:26:05
294627475 2018-12-27 09:26:02 2018-12-27 09:26:02
Slave
retcod retdesc retfecret
294453420 SIN MOTIVO 2018-12-15 15:29:58
294453421 SIN MOTIVO 2018-12-15 15:29:58
294453422 SIN MOTIVO 2018-12-15 15:29:58
294453423 SIN MOTIVO 2018-12-15 15:29:58
294453424 SIN MOTIVO 2018-12-15 15:29:57
mysql database replication database-replication
asked Dec 27 at 13:11
Elber CM
116213
English is not my native language so please be patient.
This is the second time I had setup a Master-Slave replication with failures, most of other Q&A show some errors or similar, sadly my configuration finish "fine" but then doesn't replicate.
Master Server: Ubuntu 16.04
Master Database type: MySQL 5.7.19
Slave Server: Ubuntu 16.04
Slave Database type: MySQL 5.7.19
This is the tutorial (which had worked in others servers) I follow, with some aditional steps:
- Setup Bin days to 10. (on master) (This is a database which cannot stop)
- Change bind-address = A.B.C.D (on master)
- Set server-id on master = 1
- Set log_bin and binlog_do_db (on master)
- Restart MySQL service (on master)
- Create replication user and grant privileges. Flush privileges.
- Wait 'till 2AM and get Master Status.
- Dump Database and "scp" to slave Server
- Create database in the slave (same scheme)
- Import database (this takes about 7-8 days)
- Set server-id on slave = 2
- Add relay-log info (on slave)
- Set log_bin and binlog_do_db (on slave)
- Change master host, user, password, file and position.
- Restart MySQL and Start Slave
When I hit the classic "SHOW SLAVE STATUS G", can see the Slave several seconds (465214) behind Master, but this time is getting lower. When this time get to zero, the horror, replication isn't writing nor updating. ¿What's missing?
Thanks in advance for your help.
"SHOW SLAVE STATUS G"
Example - SELECT * FROM cx_retorno ORDER By retfecret DESC LIMIT 5:
Master
retcod retdesc retfecret
294529205 2018-12-27 09:26:11 2018-12-27 09:26:11
294459995 2018-12-27 09:26:08 2018-12-27 09:26:08
294507948 2018-12-27 09:26:05 2018-12-27 09:26:05
294627475 2018-12-27 09:26:02 2018-12-27 09:26:02
Slave
retcod retdesc retfecret
294453420 SIN MOTIVO 2018-12-15 15:29:58
294453421 SIN MOTIVO 2018-12-15 15:29:58
294453422 SIN MOTIVO 2018-12-15 15:29:58
294453423 SIN MOTIVO 2018-12-15 15:29:58
294453424 SIN MOTIVO 2018-12-15 15:29:57
mysql database replication database-replication
mysql database replication database-replication
asked Dec 27 at 13:11
Elber CM
116213
asked Dec 27 at 13:11
Elber CM
116213
edited Dec 27 at 14:44
asked Dec 27 at 13:11
Elber CM
116213
asked Dec 27 at 13:11
Elber CM
116213
asked Dec 27 at 13:11
Elber CM
116213
116213
How do you know "replication isn't writing nor updating"? The slave seconds behind master steadily decreasing to zero is promising and would imply that things are in fact replicating. Can you show us the output of "show slave status"?
– MarCPlusPlus
Dec 27 at 13:28
1
@MarCPlusPlus, thanks for your comment. I know isn't updating nor writing because when I search some register cant find it in the slave, even when exist in master. (A simple "Select COUNT(*) from table" could be use as example)
– Elber CM
Dec 27 at 14:20
@ElberCM Include an actual example.
– Petzy
Dec 27 at 14:27
1
@Petzy, thanks for your comment. Done.
– Elber CM
Dec 27 at 14:45
add a comment |
How do you know "replication isn't writing nor updating"? The slave seconds behind master steadily decreasing to zero is promising and would imply that things are in fact replicating. Can you show us the output of "show slave status"?
– MarCPlusPlus
Dec 27 at 13:28
1
@MarCPlusPlus, thanks for your comment. I know isn't updating nor writing because when I search some register cant find it in the slave, even when exist in master. (A simple "Select COUNT(*) from table" could be use as example)
– Elber CM
Dec 27 at 14:20
@ElberCM Include an actual example.
– Petzy
Dec 27 at 14:27
1
@Petzy, thanks for your comment. Done.
– Elber CM
Dec 27 at 14:45
How do you know "replication isn't writing nor updating"? The slave seconds behind master steadily decreasing to zero is promising and would imply that things are in fact replicating. Can you show us the output of "show slave status"?
– MarCPlusPlus
Dec 27 at 13:28
How do you know "replication isn't writing nor updating"? The slave seconds behind master steadily decreasing to zero is promising and would imply that things are in fact replicating. Can you show us the output of "show slave status"?
– MarCPlusPlus
Dec 27 at 13:28
1
1
@MarCPlusPlus, thanks for your comment. I know isn't updating nor writing because when I search some register cant find it in the slave, even when exist in master. (A simple "Select COUNT(*) from table" could be use as example)
– Elber CM
Dec 27 at 14:20
@MarCPlusPlus, thanks for your comment. I know isn't updating nor writing because when I search some register cant find it in the slave, even when exist in master. (A simple "Select COUNT(*) from table" could be use as example)
– Elber CM
Dec 27 at 14:20
@ElberCM Include an actual example.
– Petzy
Dec 27 at 14:27
@ElberCM Include an actual example.
– Petzy
Dec 27 at 14:27
1
1
@Petzy, thanks for your comment. Done.
– Elber CM
Dec 27 at 14:45
@Petzy, thanks for your comment. Done.
– Elber CM
Dec 27 at 14:45
add a comment |
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%2f53945667%2ffailed-mysql-replication%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53945667%2ffailed-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
How do you know "replication isn't writing nor updating"? The slave seconds behind master steadily decreasing to zero is promising and would imply that things are in fact replicating. Can you show us the output of "show slave status"?
– MarCPlusPlus
Dec 27 at 13:28
1
@MarCPlusPlus, thanks for your comment. I know isn't updating nor writing because when I search some register cant find it in the slave, even when exist in master. (A simple "Select COUNT(*) from table" could be use as example)
– Elber CM
Dec 27 at 14:20
@ElberCM Include an actual example.
– Petzy
Dec 27 at 14:27
1
@Petzy, thanks for your comment. Done.
– Elber CM
Dec 27 at 14:45