Welcome to Tip #1 in MySQL Replication Series. In this tip we will go over what to do when you only want to replicate certain data to slave(s). Most general way to tell what is replicated to which slave is to include following configuration directive in my.cnf file depending on your environment and your goals. We will start with slave server side options since you have more flexibility on slave on what to replicate and what not to.
Slave server options:
- replicate-do-db = dbname (or) replicate-do-db = dbname1, dbname2, …, dbnameN
This option is used on slave server to tell the server to only replicate dbname db on this particular host. You would want to use this if you have a master which is replicating to multiple slaves and each slave may contain different database for read performance reasons. - replicate-ignore-db = dbname (or) replicate-ignore-db = dbname1, dbname2, …, dbnameN
This option is used on slave server to tell server to ignore database(s) listed. This is used when you want to replicate every database except certain individual ones. For example, you may want to replicate-ignore-db=testdb - replicate-do-table = dbname.tablename
This specifies a table from a database to be replicated. - replicate-ignore-table = dbname.tablename
This is very useful and often ignored. If you have logging table which you only do writes to but never read from, there is no real point to replicate that table to slave(s). This way you ignore specific tables. - replicate-wild-do-table=dbname.tablename%
This is another option which can prove itself to be very useful. Let’s say you have database with multiple type of open source installations (phpbb, wordpress, drupal, etc), and you want to designate slave(s) to only be used for phpbb, you would do: replicate-wild-do-table=dbname.phpbb% - replicate-wild-ignore-table=dbname.tablename%
Another option which can be used instead of above example. Say you wanted everything on this particular slave BUT phpbb tables. You would put this in your config: replicate-wild-ignore-table=dbname.phpbb%
NOTE:Â for wildcards, you can use generic mysql wildcards.
Master server options:
- binlog-do-db = dbname (or) binlog-do-db = dbname1, dbname2, …, dbnameN
This option is used on master server to tell the master server to only log queries for dbname db. This is helpful if your master has multiple databases but you only want to replicate selected few to all slaves. - binlog-ignore-db = dbname (or) binlog-ignore-db = dbname1, dbname2, …, dbnameN
Ignore specified database and do not log queries referring to list database(s).
In order for mysql to know which database queries to log (or not to log) you have to make sure you specify what is your default db by doing mysql_select_db() or issuing “use dbname” command. For example, if you had binlog-do-db=forums and you issue a query: delete from main.users (basically saying, delete everything from users table in main database), it will still log the command even though main database is not in the binlog-do-db list. To get around this, you should do the following: use main; delete from users; If you do not do that, your query “delete from main.users” will be logged and executed on slave(s). There is a high probability that you do not have a test database on your slave.
NOTE: This post is part of series on MySQL Replication. Here is the original post: MySQL Replication Series: How does MySQL replication work?
————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.
It’s also worth noting that starting with 5.1.21, you can also replicate only certain columns in tables.
If a table on a slave has less columns than the one onf the master, extra ones will be ignored (I’m assuming each query in the binlog will then just go through the filter to get those extra columns stripped off).
If the slave has more columns, they need to have default values.
http://dev.mysql.com/doc/refman/5.1/en/replication-features-differing-tables.html
just some notes:
1) be careful with the binlog-do-db settings — you’re usually better off not constricting what the master sends, because what you don’t want replicated to database A today, you may want replicated to database B tomorrow. There are exceptions to this, certainly, but it’s not a bad rule to start with.
2) using “IF EXISTS” and “IF NOT EXISTS” can really help avoid replication issues with queries like “DROP TABLE IF EXISTS” since if it doesn’t exist, you don’t want replication to break, you just want the server to move on.
Watchout with “replicate-do-db” when using mysql 4.1 !!
I must add a few precisions :
Because database names can contain commas (at least in 4.1), if you supply a comma separated list then the list will be treated as the name of a single database !! (not what we want at all)
To specify more than one database, use this option multiple times, once for each database.
Hope that helps
Pingback: MySQL Replication Series (tip #1): what should be replicated and what should not be replicated? | Fredonfire
Hello there! This post could not be written any better! Reading through
this post reminds me of my previous room mate! He always kept chatting about this.
I will forward this page to him. Fairly certain he will have a
good read. Thank you for sharing!
my web blog: ulga na dzieci
Hi there just wanted to give you a brief heads up and let you know a few of the images aren’t loading properly.
I’m not sure why but I think its a linking issue.
I’ve tried it in two different web browsers and both show the same results.|
Also visit my web-site – pit 28 2013 (http://programypity.pl)
Pingback: MySQL Replication Series tip #1: what should be replicated | WhirCat
I needed to compose you a bit of observation in order to give many thanks the moment again relating to the amazing methods you have shown in this case. This is really incredibly generous of people like you to make extensively all numerous people would have distributed as an ebook to get some cash for themselves, and in particular given that you could have done it if you ever wanted. Those tricks in addition acted to be a fantastic way to be certain that some people have similar dreams just like my very own to learn more with regard to this condition. I am certain there are a lot more pleasurable sessions ahead for individuals who read your site.
I must express my respect for your generosity supporting people who really want help on this important issue. Your personal commitment to passing the solution up and down appeared to be unbelievably insightful and has frequently made regular people like me to arrive at their aims. The interesting tutorial can mean so much to me and substantially more to my colleagues. Thank you; from everyone of us.
I would like to voice my admiration for your kind-heartedness giving support to those people who require guidance on your question. Your real dedication to passing the message all through appears to be extremely informative and have usually helped women like me to attain their targets. The informative tips and hints entails a great deal to me and even more to my fellow workers. Regards; from all of us.
I needed to write you that tiny note just to say thank you once again for those nice opinions you have shared in this article. This has been certainly remarkably generous with people like you to allow easily exactly what a number of us would’ve distributed as an electronic book to get some cash on their own, mostly now that you might have tried it in the event you considered necessary. These basics likewise acted as the easy way to recognize that many people have the identical dream really like my personal own to grasp many more when it comes to this problem. I think there are lots of more pleasant instances ahead for folks who look into your blog post.
I wish to express my thanks to you for rescuing me from such a setting. After browsing through the online world and meeting opinions which were not helpful, I assumed my entire life was well over. Being alive minus the strategies to the problems you’ve resolved by way of your entire report is a crucial case, and those that could have in a negative way affected my entire career if I hadn’t noticed your site. The competence and kindness in touching a lot of things was crucial. I am not sure what I would have done if I had not discovered such a step like this. It’s possible to at this time relish my future. Thanks a lot very much for your specialized and results-oriented help. I won’t think twice to suggest your web site to anyone who should receive counselling about this issue.
I wanted to compose a simple comment to appreciate you for some of the fabulous items you are giving out on this website. My time-consuming internet lookup has at the end of the day been paid with really good points to exchange with my family. I would say that we site visitors actually are quite blessed to exist in a perfect site with very many wonderful professionals with useful concepts. I feel quite grateful to have come across your webpages and look forward to some more awesome moments reading here. Thanks once again for a lot of things.
I simply wanted to appreciate you yet again. I’m not certain the things that I would have used without the entire tips discussed by you over this industry. This was a very terrifying matter in my circumstances, but considering the very expert mode you processed that took me to leap over happiness. Now i’m happy for the assistance and in addition sincerely hope you really know what a powerful job that you’re providing training others via a web site. Probably you’ve never met any of us.
Thanks so much for giving everyone an exceptionally spectacular possiblity to read from here. It’s usually very sweet plus stuffed with a lot of fun for me personally and my office mates to visit the blog at the very least 3 times in one week to read the new guidance you will have. And lastly, I’m so actually astounded with all the perfect opinions you give. Some 2 ideas in this article are rather the most effective I have had.
I have been exploring for a little bit for any high-quality articles or weblog posts on this sort of area . Exploring in Yahoo I at last stumbled upon this site. Reading this information So i’m satisfied to exhibit that I’ve a very good uncanny feeling I came upon just what I needed. I most definitely will make sure to don’t overlook this web site and provides it a glance on a continuing basis.
I appreciate, result in I discovered just
what I used to be looking for. You have ended my 4 day long hunt!
God Bless you man. Have a great day. Bye