Mysql replication from existing slave to new slave
I'm hoping you experts can help advise me, I've got this topology:
Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB
Backup server is a slave of Live DB, and Test DB is a slave of Backup server
The replication from Live to backup server works as expected, the replication from backup to test is where I'm a little confused, if I create a test DB on backup, I can see it replicated to Test DB, but if I do the same on Live DB, it doesn't filter down to Test DB (but does to Backup).
Another thing I notice is that the binlog sizes on Backup are very small, only 150 Bytes, whereas they are close to 100M on Live, (binlog sizes set the same on live and backup)
Maybe this is by design?
Maybe it's not possible to chain slaves together?
I goal of Test DB was (once working) to replicated from our Hosted Datacenter to our internal office, then use the internal slave to update our Dev / QA / Training DBs here (reducing bandwidth usage).
Here are what the related settings are on the backup server:
(/etc/mysql/my.cnf)
log_bin = /srv/mysql-binlogs/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 100M
binlog_cache_size = 16M
binlog-format = MIXED
Specs:
mysql 5.5Debian Squeeze
Thanks, Your advice would be much appreciated
replication mysql-5.5
edited Sep 11 '17 at 4:51
alexander.polomodov
26017
asked Jan 28 '14 at 11:02
DBA NoobieDBA Noobie
12
bumped to the homepage by Community♦ 6 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'm hoping you experts can help advise me, I've got this topology:
Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB
Backup server is a slave of Live DB, and Test DB is a slave of Backup server
The replication from Live to backup server works as expected, the replication from backup to test is where I'm a little confused, if I create a test DB on backup, I can see it replicated to Test DB, but if I do the same on Live DB, it doesn't filter down to Test DB (but does to Backup).
Another thing I notice is that the binlog sizes on Backup are very small, only 150 Bytes, whereas they are close to 100M on Live, (binlog sizes set the same on live and backup)
Maybe this is by design?
Maybe it's not possible to chain slaves together?
I goal of Test DB was (once working) to replicated from our Hosted Datacenter to our internal office, then use the internal slave to update our Dev / QA / Training DBs here (reducing bandwidth usage).
Here are what the related settings are on the backup server:
(/etc/mysql/my.cnf)
log_bin = /srv/mysql-binlogs/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 100M
binlog_cache_size = 16M
binlog-format = MIXED
Specs:
mysql 5.5Debian Squeeze
Thanks, Your advice would be much appreciated
replication mysql-5.5
edited Sep 11 '17 at 4:51
alexander.polomodov
26017
asked Jan 28 '14 at 11:02
DBA NoobieDBA Noobie
12
bumped to the homepage by Community♦ 6 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'm hoping you experts can help advise me, I've got this topology:
Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB
Backup server is a slave of Live DB, and Test DB is a slave of Backup server
The replication from Live to backup server works as expected, the replication from backup to test is where I'm a little confused, if I create a test DB on backup, I can see it replicated to Test DB, but if I do the same on Live DB, it doesn't filter down to Test DB (but does to Backup).
Another thing I notice is that the binlog sizes on Backup are very small, only 150 Bytes, whereas they are close to 100M on Live, (binlog sizes set the same on live and backup)
Maybe this is by design?
Maybe it's not possible to chain slaves together?
I goal of Test DB was (once working) to replicated from our Hosted Datacenter to our internal office, then use the internal slave to update our Dev / QA / Training DBs here (reducing bandwidth usage).
Here are what the related settings are on the backup server:
(/etc/mysql/my.cnf)
log_bin = /srv/mysql-binlogs/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 100M
binlog_cache_size = 16M
binlog-format = MIXED
Specs:
mysql 5.5Debian Squeeze
Thanks, Your advice would be much appreciated
replication mysql-5.5
edited Sep 11 '17 at 4:51
alexander.polomodov
26017
asked Jan 28 '14 at 11:02
DBA NoobieDBA Noobie
12
I'm hoping you experts can help advise me, I've got this topology:
Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB
Backup server is a slave of Live DB, and Test DB is a slave of Backup server
The replication from Live to backup server works as expected, the replication from backup to test is where I'm a little confused, if I create a test DB on backup, I can see it replicated to Test DB, but if I do the same on Live DB, it doesn't filter down to Test DB (but does to Backup).
Another thing I notice is that the binlog sizes on Backup are very small, only 150 Bytes, whereas they are close to 100M on Live, (binlog sizes set the same on live and backup)
Maybe this is by design?
Maybe it's not possible to chain slaves together?
I goal of Test DB was (once working) to replicated from our Hosted Datacenter to our internal office, then use the internal slave to update our Dev / QA / Training DBs here (reducing bandwidth usage).
Here are what the related settings are on the backup server:
(/etc/mysql/my.cnf)
log_bin = /srv/mysql-binlogs/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 100M
binlog_cache_size = 16M
binlog-format = MIXED
Specs:
mysql 5.5Debian Squeeze
Thanks, Your advice would be much appreciated
replication mysql-5.5
replication mysql-5.5
edited Sep 11 '17 at 4:51
alexander.polomodov
26017
asked Jan 28 '14 at 11:02
DBA NoobieDBA Noobie
12
edited Sep 11 '17 at 4:51
alexander.polomodov
26017
asked Jan 28 '14 at 11:02
DBA NoobieDBA Noobie
12
edited Sep 11 '17 at 4:51
alexander.polomodov
26017
edited Sep 11 '17 at 4:51
alexander.polomodov
26017
edited Sep 11 '17 at 4:51
alexander.polomodov
26017
26017
asked Jan 28 '14 at 11:02
DBA NoobieDBA Noobie
12
asked Jan 28 '14 at 11:02
DBA NoobieDBA Noobie
12
asked Jan 28 '14 at 11:02
DBA NoobieDBA Noobie
12
12
bumped to the homepage by Community♦ 6 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♦ 6 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 |
add a comment |
2 Answers
2
active
oldest
votes
so you are creating database on backup server and it's replicating on TestDB,but if you are creating on live server it's not replicating on backup server.
In the above case check for replicate-do-db or replicate-ignore-db variable on backup server under my.cnf file.
answered Jan 28 '14 at 14:50
lalitlalit
17617
Anything that happens on live gets correctly replicated to backup, but doesn't filter down to testdb, but anything I add to backup (only as a test), correctly replicates down to test. those values (replicate-do-db and replicate-ignore-db are commented out on all servers) I want everything replicated.
– DBA Noobie
Jan 28 '14 at 22:12
if it helps, the only difference on the servers are the locations of the binlogs, on live it's in the default /var/lib/mysql directory, on backup it's on /srv/mysql-binlogs. (Test binlogs are in /var/lib/mysql)
– DBA Noobie
Jan 28 '14 at 22:20
add a comment |
I missed one very important option:
log_slave_updates
Now the binlogs are growing!
ref: MySQL Slave Relay Logging but not logging Binary Log
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_log_slave_updates
edited Apr 13 '17 at 12:42
Community♦
1
answered Jan 29 '14 at 0:52
DBA NoobieDBA Noobie
12
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%2f57681%2fmysql-replication-from-existing-slave-to-new-slave%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
so you are creating database on backup server and it's replicating on TestDB,but if you are creating on live server it's not replicating on backup server.
In the above case check for replicate-do-db or replicate-ignore-db variable on backup server under my.cnf file.
answered Jan 28 '14 at 14:50
lalitlalit
17617
Anything that happens on live gets correctly replicated to backup, but doesn't filter down to testdb, but anything I add to backup (only as a test), correctly replicates down to test. those values (replicate-do-db and replicate-ignore-db are commented out on all servers) I want everything replicated.
– DBA Noobie
Jan 28 '14 at 22:12
if it helps, the only difference on the servers are the locations of the binlogs, on live it's in the default /var/lib/mysql directory, on backup it's on /srv/mysql-binlogs. (Test binlogs are in /var/lib/mysql)
– DBA Noobie
Jan 28 '14 at 22:20
add a comment |
so you are creating database on backup server and it's replicating on TestDB,but if you are creating on live server it's not replicating on backup server.
In the above case check for replicate-do-db or replicate-ignore-db variable on backup server under my.cnf file.
answered Jan 28 '14 at 14:50
lalitlalit
17617
Anything that happens on live gets correctly replicated to backup, but doesn't filter down to testdb, but anything I add to backup (only as a test), correctly replicates down to test. those values (replicate-do-db and replicate-ignore-db are commented out on all servers) I want everything replicated.
– DBA Noobie
Jan 28 '14 at 22:12
if it helps, the only difference on the servers are the locations of the binlogs, on live it's in the default /var/lib/mysql directory, on backup it's on /srv/mysql-binlogs. (Test binlogs are in /var/lib/mysql)
– DBA Noobie
Jan 28 '14 at 22:20
add a comment |
so you are creating database on backup server and it's replicating on TestDB,but if you are creating on live server it's not replicating on backup server.
In the above case check for replicate-do-db or replicate-ignore-db variable on backup server under my.cnf file.
answered Jan 28 '14 at 14:50
lalitlalit
17617
so you are creating database on backup server and it's replicating on TestDB,but if you are creating on live server it's not replicating on backup server.
In the above case check for replicate-do-db or replicate-ignore-db variable on backup server under my.cnf file.
answered Jan 28 '14 at 14:50
lalitlalit
17617
answered Jan 28 '14 at 14:50
lalitlalit
17617
answered Jan 28 '14 at 14:50
lalitlalit
17617
answered Jan 28 '14 at 14:50
lalitlalit
17617
17617
Anything that happens on live gets correctly replicated to backup, but doesn't filter down to testdb, but anything I add to backup (only as a test), correctly replicates down to test. those values (replicate-do-db and replicate-ignore-db are commented out on all servers) I want everything replicated.
– DBA Noobie
Jan 28 '14 at 22:12
if it helps, the only difference on the servers are the locations of the binlogs, on live it's in the default /var/lib/mysql directory, on backup it's on /srv/mysql-binlogs. (Test binlogs are in /var/lib/mysql)
– DBA Noobie
Jan 28 '14 at 22:20
add a comment |
Anything that happens on live gets correctly replicated to backup, but doesn't filter down to testdb, but anything I add to backup (only as a test), correctly replicates down to test. those values (replicate-do-db and replicate-ignore-db are commented out on all servers) I want everything replicated.
– DBA Noobie
Jan 28 '14 at 22:12
if it helps, the only difference on the servers are the locations of the binlogs, on live it's in the default /var/lib/mysql directory, on backup it's on /srv/mysql-binlogs. (Test binlogs are in /var/lib/mysql)
– DBA Noobie
Jan 28 '14 at 22:20
Anything that happens on live gets correctly replicated to backup, but doesn't filter down to testdb, but anything I add to backup (only as a test), correctly replicates down to test. those values (replicate-do-db and replicate-ignore-db are commented out on all servers) I want everything replicated.
– DBA Noobie
Jan 28 '14 at 22:12
Anything that happens on live gets correctly replicated to backup, but doesn't filter down to testdb, but anything I add to backup (only as a test), correctly replicates down to test. those values (replicate-do-db and replicate-ignore-db are commented out on all servers) I want everything replicated.
– DBA Noobie
Jan 28 '14 at 22:12
if it helps, the only difference on the servers are the locations of the binlogs, on live it's in the default /var/lib/mysql directory, on backup it's on /srv/mysql-binlogs. (Test binlogs are in /var/lib/mysql)
– DBA Noobie
Jan 28 '14 at 22:20
if it helps, the only difference on the servers are the locations of the binlogs, on live it's in the default /var/lib/mysql directory, on backup it's on /srv/mysql-binlogs. (Test binlogs are in /var/lib/mysql)
– DBA Noobie
Jan 28 '14 at 22:20
add a comment |
I missed one very important option:
log_slave_updates
Now the binlogs are growing!
ref: MySQL Slave Relay Logging but not logging Binary Log
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_log_slave_updates
edited Apr 13 '17 at 12:42
Community♦
1
answered Jan 29 '14 at 0:52
DBA NoobieDBA Noobie
12
add a comment |
I missed one very important option:
log_slave_updates
Now the binlogs are growing!
ref: MySQL Slave Relay Logging but not logging Binary Log
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_log_slave_updates
edited Apr 13 '17 at 12:42
Community♦
1
answered Jan 29 '14 at 0:52
DBA NoobieDBA Noobie
12
add a comment |
I missed one very important option:
log_slave_updates
Now the binlogs are growing!
ref: MySQL Slave Relay Logging but not logging Binary Log
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_log_slave_updates
edited Apr 13 '17 at 12:42
Community♦
1
answered Jan 29 '14 at 0:52
DBA NoobieDBA Noobie
12
I missed one very important option:
log_slave_updates
Now the binlogs are growing!
ref: MySQL Slave Relay Logging but not logging Binary Log
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_log_slave_updates
edited Apr 13 '17 at 12:42
Community♦
1
answered Jan 29 '14 at 0:52
DBA NoobieDBA Noobie
12
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
1
answered Jan 29 '14 at 0:52
DBA NoobieDBA Noobie
12
answered Jan 29 '14 at 0:52
DBA NoobieDBA Noobie
12
answered Jan 29 '14 at 0:52
DBA NoobieDBA Noobie
12
12
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%2f57681%2fmysql-replication-from-existing-slave-to-new-slave%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
