online prescription solutions
online discount medstore
pills online
buy lorazepam without prescription
xanax for sale
buy xanax without prescription
buy ambien without prescription
ambien for sale
buy modafinil without prescription
buy phentermine without prescription
modafinil for sale
phentermine for sale
lorazepam for sale
buy lexotan without prescription
bromazepam for sale
xenical for sale
buy stilnox without prescription
valium for sale
buy prosom without prescription
buy mefenorex without prescription
buy sildenafil citrate without prescription
buy adipex-p without prescription
librium for sale
buy restoril without prescription
buy halazepam without prescription
cephalexin for sale
buy zoloft without prescription
buy renova without prescription
renova for sale
terbinafine for sale
dalmane for sale
buy lormetazepam without prescription
nobrium for sale
buy klonopin without prescription
priligy dapoxetine for sale
buy prednisone without prescription
buy aleram without prescription
buy flomax without prescription
imovane for sale
adipex-p for sale
buy niravam without prescription
seroquel for sale
carisoprodol for sale
buy deltasone without prescription
buy diazepam without prescription
zopiclone for sale
buy imitrex without prescription
testosterone anadoil for sale
buy provigil without prescription
sonata for sale
nimetazepam for sale
buy temazepam without prescription
buy xenical without prescription
buy famvir without prescription
buy seroquel without prescription
rivotril for sale
acyclovir for sale
loprazolam for sale
buy nimetazepam without prescription
buy prozac without prescription
mogadon for sale
viagra for sale
buy valium without prescription
lamisil for sale
camazepam for sale
zithromax for sale
buy clobazam without prescription
buy diflucan without prescription
modalert for sale
diflucan for sale
buy alertec without prescription
buy zyban without prescription
buy serax without prescription
buy medazepam without prescription
buy imovane without prescription
mefenorex for sale
lormetazepam for sale
prednisone for sale
ativan for sale
buy alprazolam without prescription
buy camazepam without prescription
buy nobrium without prescription
mazindol for sale
buy mazindol without prescription
buy mogadon without prescription
buy terbinafine without prescription
diazepam for sale
buy topamax without prescription
cialis for sale
buy tafil-xanor without prescription
buy librium without prescription
buy zithromax without prescription
retin-a for sale
buy lunesta without prescription
serax for sale
restoril for sale
stilnox for sale
lamotrigine for sale

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