MySQL Replication With single Master DB to multiple Slave DBs

Question

I am creating the MySQL master-slave replication on CentOS 7 with MySQL 5.6

I have one Database like DBMaster on the Master server and multiple Database on the slave like DBSlave1, DBSlave2 and more. All the tables are same into the Master and Slave Databases.

I want when ever any record insert, update or delete in the Master DB tables that replicated to all the Database of slave.

For example: I have table name like student in Master and slave Databases. When ever I insert data into the student table in Master Database it will be replicated into the all the Databases student table of slave.

I have already got success in single Database and same name on Master and slave server.

Below are the configuration files details of both the server:

Master server's my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Slave Server's my.cnf

[mysqld]
server-id=2
replicate-wild-do-table=DBSlave%.dbtb1

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock


symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Database details given below:

Master Server

create database DBMaster;
create table DBMaster.dbtb1(name varchar(100));

Slave Server

create database DBSlave1;
create table DBSlave1.dbtb1(name varchar(100));

create database DBSlave2;
create table DBSlave2.dbtb1(name varchar(100));

AS you can see the Database name on both the sever are different for that I have use the below statement into the slave server for replication.

replicate-wild-do-table=DBSlave%.dbtb1

But when I try to insert data into the Master Data base server I got the below error or slave server status:

Error 'Table 'DBMaster.dbtb1' doesn't exist' on query. Default database: ''. Query: 'insert into DBMaster.dbtb1 values ('Punu')'

Please tell me what is wrong in the configuration. I am trying this this first time.

Answer 1

Make sure each of your replicas has a different value of server_id. If all have same server_id, then the master may only send an event (such as the create table event) to one of the replicas.

Regardless, and since you are new to MySQL, I'd like to discourage you from using replicate-do* and replicate-ignore*, at least until you're really sure of what you're doing. Replication filters have many surprising effects. Normally, you don't use any filters in your production setup.

Answer 2

Based on the author's comment: "I wan to replicate Master's specific table to the multiple database of slave", this warrants a completely new answer.

The original question was unclear, and now I understand something very different. You have a single master, single replica, a single table, and you want that table to replicate into multiple tables (i.e. get multiple copies of that table) on the single replica.

There is nothing built-in to support that. You may potentially solve that with Tungsten Replicator but I would assume this is an excessive overkill and a significant operational overhead.

Answer 3

I think you want to replicate DB to different DB name on slave. Right?

You can use replicate-rewrite-db variable for do this.

For example. If you want to replicate DBmaster1 to DBslave1 just put this in your my.cnf

replicate-rewrite-db="DBmaster1->DBslave1"

But it has a few limitation.

For example DDL on Database (Alter database, Drop Database) not work.