Replication in MySQL - Part - 2

This post is the sequel of my first post about Replication in MySQL. In that post, I've explained how to set up Replication. In this post, I'll discuss about different options in Replication.

As I said in my previous post, as soon as the replication is set up with that procedure, it is a server level replication. All changes made at the master is replicated in the slave including operations like creating databases etc. But that may not be a feasible option in all the environments. So, there are options to implement replication in different scenarios. All these options should be availed at the slave.

Replicating a Single Database

Shut down the slave and include the following option in its configuration file and start it again,

replicate-do-db=db_name

Now test this configuration by making any changes in the master database and observing them getting replicated at the slave database. If any database is created at the master then it won't be created at the slave.

Replicating Data Between Databases with Different Names

There is no rule or restriction that replication should happen among the databases with same names. If you wish to replicate data among the databases with the different names then here is the option which should be included in the slave's configuration file,

replicate-rewrite-db=master_db_name->slave_db_name

Turn on the slave server and notice any changes made in the master database is replicated in the slave database whose name is different from that of master.

Ignore a Particular Database for Replication

There is also an option to ignore a particular database while having a server level replication. Just include this option in your slave's configuration file,

replicate-ignore-db=db_name

Replicating Data Within Same Sever

If you do not have two separate MySQL instances, you can check how replication works by implementing it within the same instance. This means, a single instance can work as a master as well as a slave.

To do this, follow the same procedure you follow when two instances are available but include the following option in your master's configuration file,

replicate-same-server-id

This option takes in Boolean values and the default value is set to 0. This prevents the infinite circular loops in replication. As master is also the slave here, it skips the events happening at the slave part. If this option is set to 1 then it doesn't skip writing the slave events to the binary log which have the slave server id. This results in high increase of disk space usage due to the larger log file size.

While doing replication like this, remember to include the following option in addition to the above one,

replicate-rewrite-db=master_db_name->slave_db_name

As we know that replication configured first is a server level replication and whatever the operation done at master is replicated at the slave. Here the master and slave is only one, if you create a database then the slave cannot create it and this error is shown in the Last_IO_Error column of the following statement,

show slave status;

In order to see the data getting replicated from one database to another database, the above option should be included in the configuration file.

Replicating Data at Object Level

Replication can be even done at the object level. In other RDBMS products like SQL Server, Replication is confined only to object level, for database level there are options like Log Shipping, Mirroring etc. In MySQL, there are no many high availability solutions, replication is the most used option.

The following are the different object level replication options to be included at the slave,

• If you need a single table to be replicated then use replicate-do-table=table_name. If multiple tables are needed then use this option for multiple times with different table names.
• If a table should be ignored from getting replicated then use replicate-ignore-table=table_name. If multiple tables have to be ignored then use this option for multiple times with different table names.
• If your database has tables starting with the same naming convention and you want to restrict updates on those tables from getting replicated then use the option replicate-wild-do-table=table_name. Suppose you don't want to replicate the updates happening on the tables starting with the name fact_ then use this option as replicate-wild-do-table=fact_. "_" and "%" are the patterns that can be used here.
• If you want to ignore updates on tables with a naming convention then use replicate-wild-ignore-table=name.

To know more options about replication, click here.

This is the basic information regarding Replication in MySQL. I'll come up with more options and other stuff in my future articles.