MySQL Replication Series (tip #1): what should be replicated and what should not be replicated?


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.


7 Responses to “ MySQL Replication Series (tip #1): what should be replicated and what should not be replicated? ”

  1. April 21st, 2008 | 8:11 pm

    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

  2. April 22nd, 2008 | 10:31 pm

    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.

  3. August 17th, 2009 | 7:55 pm

    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

  4. October 14th, 2011 | 7:37 pm

    [...] There is a high probability that you do not have a test database on your slave.Read more: http://crazytoon.com/2008/04/21/mysql-replication-replicate-by-choice/#ixzz1aoXhL09a Posted in Database, Information Technology, MySQL Replication, SQL« MySQL Replication [...]

  5. July 27th, 2013 | 11:04 pm

    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

  6. October 17th, 2013 | 1:36 pm

    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)

  7. February 10th, 2014 | 2:49 pm

    [...] MySQL Replication Series tip #1: what should be replicated and what should not be replicated? | Tech… 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 slaves. 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. Share this:EmailLinkedInTwitterGoogle [...]

Leave a reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image