1

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.

  • did you connect to the DBMaster database? Or did you just start psql and run an insert command? What tool are you using to insert data? – hot2use Dec 30 '16 at 8:34
  • Yes I am able to connect to the DBMaster and I using the MySQL 5.6 command line client for inserting data. – Ankit Jain Dec 30 '16 at 10:10
  • You want to replicate dbmaster database to different database name on slave ? – HamoonDBA Dec 30 '16 at 10:11
  • yes @ Hamoon Mohammadian. But thing is that All the Database are on the one machine or one MySQL only the Database name is different. – Ankit Jain Dec 30 '16 at 10:17
  • @Hamoon Mohammadian I am talking about the slave Databases. Master Database is on different server/machine. – Ankit Jain Dec 30 '16 at 10:33
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.

  • There is only one slave machine and one instance of the MySQL server. and all the mention Database(DBSlave1 and DBSlave2) created on that slave machine. And Master server_id is 1 and for slave server_id 2. Because of this reason I have use the replicate-wild-do-table=DBSlave%.dbtb1. – Ankit Jain Dec 30 '16 at 10:15
  • @AnkitJain a machine means nothing to MySQL; What's important is the server. And, as I understand, you have multiple servers on that same box. Thus, each of these servers has to have its own configuration, with different server_id. – Shlomi Noach Jan 1 '17 at 9:02
  • On re-reading I understand you don't have multiple instances of MySQL. But, not this further confuses me because I don't really understand the case for replicate-wild-do-table at all. You say: "Because of this reason I have use the..." but I don't see the reason. – Shlomi Noach Jan 1 '17 at 9:04
  • Yes you are right I am not using multiple instance of MySQL @Shlomi Noach. Should I need that for my scenario?. I wan to replicate Master's specific table to the multiple database of slave. – Ankit Jain Jan 2 '17 at 4:16
0

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.

  • Can it work for Insert, delete and update statement? – Ankit Jain Dec 30 '16 at 10:30
  • And I think this will replicate whole database not specific table. – Ankit Jain Dec 30 '16 at 10:31
  • Yes @AnkitJain It can. Look at this document for more information dev.mysql.com/doc/refman/5.6/en/… – HamoonDBA Dec 30 '16 at 10:31
  • Mohammadian I think this will replicate whole database not specific table. – Ankit Jain Dec 30 '16 at 10:38
  • @AnkitJain Yes This replicate whole database. I thought you want to replicate database to diff DB name for all tables. – HamoonDBA Dec 30 '16 at 11:06
0

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.

  • Yes you right @Shlomi Noach. I have single master, single replica and single DB but with multiple tables. From those table I want to replicate some of tables. I have single slave with multiple databases and I want to replicate those specific tables of master into all the multiple databases of single slave. – Ankit Jain Jan 3 '17 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.