How to Replicate Part of the Tables in MySQL

When you build a DWH or another intensive processing on a database slave copy, you may not want to replicate the whole database. 


How can you replicate only some of the tables or even part of existing tables to a slave?
In General there are 4 ways to replicate data:

1. Database Definition: You can define what database names will or won't be replicated. You can place all the tables that need to be replicated in one database and all the other in another one. That way you can replicate only the first database.
2. Tables Definition: You can define what specific table names you can replicate and what not.
3. Part of a Table: Replication by SELECT limitation is supported starting from version 5.1.21 instantly. Therefore, you can easily replicate only several columns (replication discards non existing columns if they do not exist in the slave). In earlier versions, you could perform Vertical Sharding, where you could keep some of the columns in one table, and the other in another table. Then you may replicate only one of the tables.
4. Part of a Table: Replication by WHERE limitation is not supported instantly. Therefore, you cannot easily replicate only some of the rows from a single table. Yet, you can perform Horizontal Sharding, where you can keep rows groups in different tables. Then you may replicate only some of the tables to your slave.

When Should You Avoid Partial Replication?

If your slave is being used as a copy for DRP or HA, you may still want it to fully match your master. If the copy is done for specific processing and you want to avoid unneeded replication, this is definitely the right tool for you.

Syntax is the King

Most of the syntax is performed in the slave and it is detailed in Sunny Walia post.

Keep Performing,

Moshe Kaplan

MySQL. An Internet Standard.

As you probably already know, last week I gave a presentation at the Database2011 conference on how MySQL become an Internet Standard?

Database2011 my sql sharding

View more presentations from Moshe Kaplan.

The answer is simple: great community and good support and the proof of the pudding is in the eating: everyone uses it including Facebook, Twitter and Google (if you want to hear other opinions try follow the xaprb.com comments flow).


MySQL Limitiations
Yet, if you will explore the MySQL capabilities you may find out that it lucks real multi threading capabilities. its table size is limited to effective number of 50-100M rows, its SELECT performance is limited to 50 statements on a single table per second and its INSERT performance is limited to 700 INSERT statements per second (based on standard Amazon instance and InnoDB engine).
So, how can the Internet industry with dozens of thousands of SQL statements per second in a medium site use such a limited database?


Answers
The answer is simple: Use Sharding. Sharding is a method for slashing your tables into smaller ones and storing parts of the data in each one of them. There are several common strategies including:

1. Vertical Sharding: Tear a table into two, store only few columns in first table and the rest in the second table.
2. Horizontal Sharding: Place each group of rows in another table. Horizontal Sharding can be implemented using various algorithms that include static hashing, hashing with directory mapping, key based directory mapping and signature based mapping.

The main issue with Sharding is reporting. Since direct grouping cannot be supported when rows are spread in various tables, you should use Map Reduce based solutions to accomplish this task.


Emerging Products
In the last year several new COTS Sharding products were introduced to the market. These products use two mechanisms in order to get over the MySQL limitations:

1. Gizzard and ScaleBase (founded by Industry experts Doron Levari and Liran Zelkha) use the MySQL Proxy mechanism in order to implement a load balancing and Sharding solutions in front of regular MySQL databases.
2. Xeround and Akiban implemented a new type of storage engine based on the MySQL Storage engine API.

Will MySQL remain an Internet standard in the future?
MySQL will keep being an Internet standard, unless unexpected decisions will be taken by Oracle that owns the firm. Unwise marketing decision could harm the community and may cause a major shift to other solutions including NoSQL.


Bottom Line
If you have a great internet idea, you have all the needed tools to start.


Keep Performing,
Moshe Kaplan