MariaDB Replication – Replicate only specific tables and views

database-designmariadbmigrationreplicationschema-migration

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.

Best Answer

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'
Related Question