Tag Archives: replication

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.

MySQL Replication Series: How does MySQL replication work?

Setting up replication is not hard and can be done by following simple directions. You can learn setting up two type of replication MySQL offers here: master-master replication and master-slave replication. Once you have replication set up, you can start playing with it without doing any more changes to the configuration. But let us face it, using replication in production environment and for sites which are either going live or are live, requires deeper knowledge of how replication can be used and what it can (or can not) do. We will start this “MySQL Replication Series” with briefly going over how MySQL replication works. Later in the series I will be posting some tips on how to fine tune the settings to get replication to do what you want it to do. I will be keeping all the tips separate and will add links to all of the tips at the bottom of this post as I post them. Let us start by quick overview of how replication works.

In a MySQL replication setup master server puts all the commands it is executing which affects data (insert, delete, update, truncate, etc) into a file referred to as bin-log file. Slave(s) than pick up the big-log files and execute those commands on themselves. You can see which bin file is currently being used by typing: show master status, you would see output similar to:

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 519132576

This tells us that right now we are writing to mysql-bin.000001 and it is at position 519132576. Why does this position matter? It matters if you are looking at your slaves and trying to figure out where it is currently as far as position goes. If you were to do mysqldump and do it with master info, it is this number which gets added to the dump file so when you import on slave, it knows where to start from.

At this point in time if you go to slave, and type: show slave status\G, you would see something similiar to below:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 519132576

As you can see it is at the same point meaning it is all caught up to the master (this snapshot was taken from a site which was not accepting traffic at the time). Another thing you can look at, even though it is not the most accurate way to measure how behind you are, is to check last row in “show slave status” labeled: Seconds_Behind_Master: 0 <- if this is greater than 0, than you are lagging behind the master. Which in fact just means that it has not played back some of the queries. If it is NULL, it means that your slave is either not started or it has errors which needs attention. You can start and stop the slave by typing: start slave; and stop slave; respectively.

Hopefully this gives an overview of how MySQL replication works. As always, please feel free to correct me, add on to what I have said or just make comments. I am, like many others, learning new things every day.

Tip #1: What should be replicated and what should not be replicated?
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.