To get this straight – I’m not a big fan of filtered or partial MySQL Replication (as of version MySQL 5.0) – there is enough gotchas with replication itself and getting things right with filtering can get quite bumpy road. In some applications however it is very helpful so lets see what one should do or should not do to make it work.
Experience shared here mainly applies to logical level replication. MySQL 5.1 can use row level replication which alleviates some of these issues but I guess it will still take some time before it becomes massively used.
First thing you need to know about partial replication – you can do filtering on the Master or on the Slave. Filtering on the Slave is normally preferred as in this case you get full binary log on the Master which is not only helpful for replication needs but also if you ever need to do roll forward recovery while recovering from backup. There are however some cases when you really do not need events in binary log – even for backup recovery, for example dealing with temporary tables (unless they are used to populate normal tables using SQL).
On the Master you can do filtering using binlog-do-db and binlog-ignore-db commands. This is there a lot of people stop reading documentation, specify databases they want to replicate (or ignore) and hoping it would work.
In reality however these filters are very fragile – you need to always have default database specified and you only need to update this default database for filtering to work normally. If you’re using this rules you have to be both sure your application(s) only write to default database and everyone who has access to the database always keeps this in mind. In quite a lot of cases I’ve seen such filtering being broken by developer forgetting this restriction and running one time update query without selecting proper default database.
Using this option takes a lot of discipline and so do not work well for a lot of installations, especially as MySQL Server do not offer any enforcements – ie there is no way to forbid updating not default database so you can ensure your application and staff members really follow these rules.
Another option to filter writes on the Master is of course manual filtering – using SET SQL_LOG_BIN=0 if you need to skip some statements from binary logging. This command requires SUPER Privilege and extreme care in use but it can be very helpful in advanced cases allowing replication to control what will be logged to binary log and so replicated.
On the Slave you have more options to filter the data. There is replicate-do-db and replicate-ignore-db options which closely mirror behavior of their binlog-do-* counterparts and which are as easy to break. So I generally would not recommend using these.
There are also replicate-do-table, replicate-ignore-table, replicate-wild-do-table and replicate-wild-ignore-table options which act differently. Instead of looking at the query and analyzing which tables this query is using.
This works in much larger amount of cases, but not all of them. For example stored procedures are handled (because binary logging is done on statements as executed inside stored procedures), however Stored Functions are not – so if you have updates done by stored functions the tables are not taken into account.
The traditional pre MySQL 5.0 way to break such replication is also using multi-table update or delete statements, if you happen to replicate one table or another this would not work. Though this is rather easy to avoid, but it is still remains a way how one can break filtered replication by executing some statements on the master.
Even though replicate-*-table options also have some issues they are much safer than database based counterparts.
In some cases however you can’t really do filtering on the slave or it gets really inefficient – what if you’re replicating over long distance and would like to save on traffic or if write load is high and it would be waste to pull binary logs to large variety of slaves?
This is the case when BLACKHOLE storage engine can come into the play. In the nutshell you can create intermediately slave which has BLACKHOLE as default-storage engine which would fetch all binary logs from the master, filter out only what you need replicated and pass it to the group of slaves. If you have several group of slaves which need different data sets replicated to them – you can use number of such filtering servers.
The good thing about BLACKHOLE filtered server is – it is generally rather lightweight as it only needs to fetch logs filter and write filtered logs – the queries are formally executed but it is rather fast as there is no any real data to modify.
Daniel Schneller wrote pretty good tutorial on creation for setting up BLACKHOLE Based Replication Filtering so I will not go into much of details but just note couple of issues you need to watch out for.
First having BLACKHOLE Slave in the middle you get into classical issues of chain replication – the latency will increase a bit (though not much because query execution on such slave is very fast) and what is more important you will get into more complicated math of dealing with binary log positions. For example cloning Master (and dropping not replicated tables) to set up replication becomes more tricky because you need to perform mapping between master positions which you get with backup to distribution slave position. You can also clone other slaves which are getting the same filtered stream of data though it does not help if you would like to add more tables to be replicated to the slaves.
Another issue is of course creating and altering tables. If you have Innodb tables you’re normally OK because you can use –skip-innodb on the filtering slave and default-storage-engine=BLACKHOLE to ensure all Innodb tables are created as BLACKHOLE. However you can’t do the same with MyISAM tables, because MyISAM can’t be disabled and so if you create tables as MYISAM or ALTER them to MyISAM you will get them as MyISAM on BLACKHOLE server as well. So you need to watch out for this one as well.
As you can see no matter which road you take with partial MySQL Replication there are things to be careful with, so if you’re considering to implement it make sure there are skills and discipline in your team to make sure you do not shot yourself in the foot.
If you’re not doing things which do not work Filtered Replication can work pretty well for you.
P.S It is in my todo to see how well MySQL 5.1 row based replication works and performs and I will be testing filtered scenarios as well. Hopefully everything is taken care of in this case.
I’m not sure if its ready for production, but couldn’t you use that mysql proxy on the blackhole server to filter out and replace any MyISAM to blackhole? This would I guess corrupt the binlogs… and possibly change all your slaves to blackhole… never mind then. Is it possible to change it to a conditional blackhole, so only on the blackhole server it uses that engine?
Hi Peter,
Regarding this statement:
“…using SET SQL_LOG_BIN=0 if you need to skip some statements from binary logging. This command requires SUPER Privilege and extreme care in use…”
Can you detail a lil’ more on the extreme care you’re talking about? What are the hell scenarios that might hit our beloved replication/mysql servers?
I mean, once I know what that sql statement does, I’m pretty much aware that every DML I’m issuing AFTER sql_log_bin = 0 is happening at the session level, on the local box, nothing being written in the binlog.
Thanks.
Francis,
Generally MySQL Proxy is another way one could possibly filter binary logs, and this way you may be able to implement even more flexible log filtering and do it on the fly instead of spooling copy of logs like blackhole server does. Though as I have not tested it am not writing about it though this would be interesting exercise.
Gigiduru,
No indeed it is as simple as you describe but you need to have extreme care which statements do you filter out.
Manually running ALTER TABLE on slave and master separately is actually rather good use.
“MySQL 5.1 can use row level replication which elevates some of these issues” — it took me a minute to understand. I think you mean “alleviates” the issues.
My personal opinion is row-level replication will be better in many ways, but I bet a lot of people are going to be really surprised when they see how it really performs for their specific scenarios! I think people may be looking forward to “this will solve all problems.” Sort of how people think “I’ll just set up NDB cluster, a cluster will solve all my problems” 😉
Baron,
Thanks. This is what I meant 🙂 Indeed row level replication becomes very important with a lot of new features as stored procedures and triggers, as well as with high concurrency transactional engines – I’m not sure Falcon or PBXT could use logical replication at all. It surely has different properties but I hope it will be good replaced in more cases than Innodb Cluster is.
Funny enough back when 4.1 was in beta and 5.0 was in early development (before NDB Cluster) I discussed some guys in MySQL if logical replication is good enough, highlighting new features planned for MySQL 5.0 like Stored Procedures and Triggers can be hard to get with statement based replication. There were discussions to get row level replication at that point but it dropped as there was a way to get it work with logical replication. In MySQL 5.0 a lot of work was done to really make things work with logical replication as it was not easy and still in MySQL 5.1 row level replication was added.
Hi Peter
Maybe here is not the best place to post my doubt, but I guess you know.
Do you know if exist a lenght limit in the MySql replication?
In “show binlog events in ‘Ulisses.000003’ from 22392415 limit 1;” the query is not completely. And because it my slave gone down.
Any tip?
Thanks!
Jonatas – Brazil
Great post ! Just wanted to add that there is also a replicate-rewrite-db option that is useful for replication filtering.
A simple question regarding replication, can we have row based replication. I means to say a specific row of table1 should be replicated to slave 1 and the other row of same table should be replicated to say slave 2 (not the both rows to both slaves) on the basis of some column like PK.
Hi Kashif, thanks for the question. I’m Percona’s community manager. This post is a few years old so the best place to get some answers is on the Percona MySQL discussion forums. Here’s the url: https://www.percona.com/forums/
Hi,
I have filtered out slave as below
replicate-wild-do-table=DB1.%
replicate-wild-ignore-table=DB2.norep%
means i want to execute all statements on tables related to DB1 database and all tables except table names starting with norep from DB2. After configuring this , DB1 replication is fine but slave ignoring all transaction on DB2 even table names are not starting with norep.
[mysqld]
server-id=4
master-host = 192.168..x.xxx
master-user = username
master-password = password
master-port = 3306
replicate-rewrite-db=db1->dummy
replicate-rewrite-db=db1->db1
replicate-wild-do-table=db1.tablename1
replicate-wild-do-table=db1.tablename2