Requirement : we have one test server and one production server .
Schema should be same on both server.Mean all schema related changes (add new column,table,update column type) should be reflect on frequent bases or defined regular time interval.
Possible way :Take backup of test db without data and restore it on production but we cannot follow it every time.By this way production data will be lost.
Our need is schema related changes should be change automatically.
So we will implement replication by using black hole storage engine property.
For better understanding one revise about replication:
Replication: It is a process of reflect all changes to the database, both data and structure over the node(server, client).It is total game play of binary logs.
binary logs are a set of binary files and an index.
binary logs have three format – Row bases binary, Statement base binary and Mix binary (Row + Statement).
Classification of replication bases on binary:
1. Row based replication.
2. Statement bases replication.
Row based replication : with row based replication ,every row modification happen on master node and is then applied on slave node . Means SQL statements that change the rows are not recorded, instead the entire updated row is written to the binary log. Only table creation, drop, truncate or alter the actual SQL statement is recorded in binary logs.
Statement bases replication: With statement-based replication, every SQL statement that could modify data is on the master. Those SQL statements are replayed on the slaves against the same dataset and in the same context.