0

Note: A backend developer here with little to no experience in setting up database servers and replication.

What needs to be done

Setup DB replication of an existing database with the following constraints:

  1. Replicate only a specific list of tables/views with different names in the replicated database.
  2. Change the name of the tables/views in the replicated database (during the replication process)
  3. Setup a user on the replicated DB with further restrictions with which only a set of table/view can be viewed/updated/deleted

Progress so far

I have already read the document here, however, I did not find anything concrete to help me move forward with all the use-cases I wish to support!

Use Case

Show only essential data to the external vendor.

PS: If there are any other approaches other than replication, would be happy to consider and implement that as well.

  • 1
    this will also need to be kept in sync with original data or would it be just a snapshot of original and then both will be used independently ? – Harsh Gundecha May 30 at 12:25
  • @HarshGundecha It has to be in sync with the original data. – qwertynik May 31 at 6:01
0

On the master host you have to add to the [mysqld] section of the my.cnf:

. . . . .
binlog_do_db = mydatabase
. . . . .

Master service should be restarted then.
You can rename the replica's database but you can't rename tables, views and other objects. If you want to avoid collisions between local and replicated objects you have to replicate all the objects into the different database.
On the slave host you have to add to the same section:

. . . . .
# DB name mapping (if you need that)
slave.replicate_rewrite_db = mydatabase->mydb
# DB objects filtering - only listed objects will be replicated
slave.replicate_do_table    = mydb.mytable
slave.replicate_do_table    = mydb.myview
. . . . .

Slave service should be restarted too.
Next you have to make an initial copy of the data (remotely):

mysqldump --host 11.22.33.44 -u username -ppassword --master-data=2 \ 
--quick --hex-blob --add-drop-table --create-options --extended-insert \
--disable-keys --allow-keywords mydatabase mytable myview > mdump.sql

Next you have to create an empty target DB on the slave and fill it by dumped data:

mysql -u username -ppassword -e 'CREATE DATABASE mydb;'
mysql -u username -ppassword mydb < mdump.sql

Then you have to create the slave process on the slave host. mdump.sql file contains binlog filename and position in the header so you can copy and paste them:

mysql -u username -ppassword -e 'CHANGE MASTER "slave" TO \
                                 master_host="11.22.33.44",
                                 master_user="ruser",
                                 master_password="rpass",
                                 master_log_file="filename",
                                 master_log_pos="position";'

Now you can start the slave and check its state:

mysql -p username -ppassword -e 'START SLAVE "slave"; SHOW ALL SLAVES STATUS\G'
| improve this answer | |
  • Have not been able to work with the setup yet. I will keep you posted on how it goes. – qwertynik Jun 1 at 4:34
  • 1
    Have managed to setup replication. However table names cannot be modified as you said. Used triggers to write into the table names of choice and then replicate those into the replicas. – qwertynik Jun 4 at 9:42

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.