Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've setup a 3-node multi master replication with percona xtradb cluster, it works perfectly.

Now I've tried to add some read-only slaves setting up replication as usual but it seems the binlog doesn't include the new inserts,

I've set binlog_do_db on the master of a database, slave says the log position is the same as the master ones but the new data isn't there.

Is there a special way to do replication on xtradb cluster?

share|improve this question
    
1) What is the version of Percona Server in PXC? 2) Are you using MySQL or Percona Server in the ReadOnly Slave? 3) What is SHOW VARIABLES LIKE 'binlog_format'; from PXC and the Slaves? –  RolandoMySQLDBA Jun 7 '13 at 17:58
    
1) 5.5.30 2) Plain MySQL from Ubuntu packages 3) PXC = ROW, slave = STATEMENT now I remember for PXC you have to change the binlog_format. Going to try to change that :) –  alex88 Jun 7 '13 at 18:25
    
On a side note, please make sure all Slaves are running 5.5.x. –  RolandoMySQLDBA Jun 7 '13 at 18:36
    
What version of MySQL on the Slaves ? Please run mysqld --version –  RolandoMySQLDBA Jun 7 '13 at 20:14
    
Version is mysqld Ver 5.5.31-0ubuntu0.12.04.2 (sorry for the delay) –  alex88 Jun 7 '13 at 22:04
add comment

2 Answers 2

up vote 3 down vote accepted

it seems the binlog doesn't include the new inserts

I'm not sure whether you're saying the binlog actually doesn't include them, and you have confirmed this with mysqlbinlog, or that it "seems" like it doesn't, because they don't replicate.

PXC needs log_slave_updates turned on at the node serving as master to the asynchronous slave, otherwise, not everything will be written to the master's binary log. This is very different than an ordinary MySQL server as master, where log_slave_updates will do nothing at all (unless the master is actually a slave to another master).

If that's not it, remove replicate_do_db and binlog_do_db and all of their related options from your configuration and then remove them from your brain. They should never be added unless you know exactly how they work, in your sleep. The simplest and by far most reliable replication configuration is, and will always be, replicate everything, which is the default.

Forget about binlog_format on the slave. It makes absolutely no difference unless the slave, itself, has other, subtended slaves... and if the master is using ROW format, the slave will still log in ROW format if you do indeed have it configured with subtended slaves. Also, the slave's binlogs (not to be confused with the relay logs) will not log statements received from an upstream master unless log_slave_updates is enabled on the slave.

The same thing goes for innodb_flush_log_at_trx_commit. It does not impact actual replication. It's a setting the determines a tradeoff between ACID compliance and performance.

share|improve this answer
    
Added log_slave_updates and it worked perfectly! Thanks! –  alex88 Jun 8 '13 at 6:43
add comment

Binary Log Format : I was thinking that since Percona XtraDB Cluster (PXC) required using binlog_format as ROW, all the read-only Slaves should also have that setting.

binlog_do_db : It will record every option for a given database only. Notwithstanding, this is an option that is sometimes misunderstood. Please read this blog entry from mysqlperformanceblog on how replication can get confused.

Concerning binlog_do_db for ROW based binlogs

Row-based logging. Logging is restricted to database db_name. Only changes to tables belonging to db_name are logged; the default database has no effect on this. Suppose that the server is started with --binlog-do-db=sales and row-based logging is in effect, and then the following statements are executed:

USE prices;
UPDATE sales.february SET amount=amount+100;

The changes to the february table in the sales database are logged in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, when using the row-based logging format and --binlog-do-db=sales, changes made by the following UPDATE are not logged:

USE prices;
UPDATE prices.march SET amount=amount-25;

Even if the USE prices statement were changed to USE sales, the UPDATE statement's effects would still not be written to the binary log.

Another important difference in --binlog-do-db handling for statement-based logging as opposed to the row-based logging occurs with regard to statements that refer to multiple databases. Suppose that the server is started with --binlog-do-db=db1, and the following statements are executed:

USE db1;
UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;

If you are using statement-based logging, the updates to both tables are written to the binary log. However, when using the row-based format, only the changes to table1 are logged; table2 is in a different database, so it is not changed by the UPDATE. Now suppose that, instead of the USE db1 statement, a USE db4 statement had been used:

USE db4;
UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;

In this case, the UPDATE statement is not written to the binary log when using statement-based logging. However, when using row-based logging, the change to table1 is logged, but not that to table2—in other words, only changes to tables in the database named by --binlog-do-db are logged, and the choice of default database has no effect on this behavior.

Based on this, I would suggest, not do binlog_do_db in PXC, but, do replicate_do_db on the Slaves instead.

share|improve this answer
    
Wow, Thank You !!! –  RolandoMySQLDBA Jun 7 '13 at 18:32
    
Thank you ;) basically the difference between binlog_do_db and replicate_do_db is that PXC doesn't send binlogs only for some db to slaves but slaves writes only selected db affected rows? or? –  alex88 Jun 7 '13 at 18:34
    
Just tried to set binlog_format = ROW on slave (without the replicate_do_db option), same thing, master position is synced but new data isn't on the slave, anything else that could be wrong? –  alex88 Jun 7 '13 at 19:20
    
Did you run a fresh CHANGE MASTER TO command on the Slave to clear the relay logs? –  RolandoMySQLDBA Jun 7 '13 at 19:26
    
Please run SHOW SLAVE STATUS\G. Do Slave_IO_Running and Slave_SQL_Running both say yes? –  RolandoMySQLDBA Jun 7 '13 at 19:27
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.