MySQL Replication With single Master DB to multiple Slave DBs

MySQLreplication

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.

Best Answer

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.

Related Question