In this configuration, we are interested on replicating a specific database across servers. So let's assume again the following configuration:

Master: 192.168.0.129

Slave: 192.168.0.130

The configuration of the master is pretty simple as the previous time:

1. Enable logging for this specific database

In order to enable logging for this particular database we have to edit the /etc/my.cnf to include the following parameters:

[root@mysqlmaster mysqldata]# cat /etc/my.cnf | grep bin
# changes to the binary log between backups.
# log_bin
log-bin=mysql-bin <- Enable logging
binlog-do-db=newDB <- For database: newDB

After that, we have to create a user which will be used by the slave to retrieve the logs, create a backup and sync both database initially:

Unlike Oracle, the master doesn't care for the slaves.Therefore, it is job of the slave to use a user to connect to the master and extract the data it needs, so it is job of the DBA to allow this :)

[root@mysqlmaster mysqldata]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'oracle_4U';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use newDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      595 | newDB        |                  |                   | <- REMEMBER THIS DATA
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

After we have done that, it is time to create our initial backup for load:

There are couple ways to create the backup as we already mentioned, BUT It should be a consistent one. Which means we cannot just copy paste the files (well we can if we put a shared lock on them, other way is to create a dump) In the previous time we did that via LVM snapshot, so let's do this one with a dump:

[root@mysqlmaster backup]# mysqldump -p --opt newDB > newDB.sql
-- MySQL dump 10.13  Distrib 5.7.21, for Linux (x86_64)
--
-- Host: localhost    Database: newDB
-- ------------------------------------------------------
-- Server version       5.7.21-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
[root@mysqlmaster backup]#

As you can see, the dump isn't anything more than a SQL script.

Once the backup is done, don't forget to remove the shared lock(otherwise people will REALLY hate you :) )

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql>

So let's move forward and prepare our slave.

The slave is the main worker in replication in MySQL configuration. The slave will connect to the master and extract what he needs. The same as the PostgreSQL. So let's get going:

Similar with the system replication, here we have to create empty database, or just put that into our mysqldump option, since we didn't we have to do it ourselves.

[root@mysqlslave newDB]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database newDB;
Query OK, 1 row affected (0.00 sec)

We have to edit the settings, to change the server-id and couple more values:

replicate-wild-do-table=newDB.%
relay-log=/mysqldata/mysql-relay-bin.log
server-id=2(Different than the master)

Change the Slave to point to the Master as follows:

[root@mysqlslave mysqldata]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.129', MASTER_USER='slave_user', MASTER_PASSWORD='oracle_4U', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=595;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

Once the configuration is set, we can now restore the backup taken on the master previously:

[root@mysqlslave backup]# mysql -uroot -p -D newDB < newDB.sql
Enter password:
[root@mysqlslave backup]#

Once everything is done it is time to start our slave:

The slave is started with the following command:

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Congrats, you have a database replication. Now any modification of database newDB on the master will affect only that database on the slave!

Table replication is simply implemented by changing the: replicate-wild-do-table parameter. By just setting the table in this parameter, we can replicate a single table as follows:

root#: cat /etc/my.cnf | grep replicate
replicate-wild-do-table=newDBn.newTable

This will cause the slave, to apply ONLY changes for this table (e.g. newTable), otherwise you will receive the following message in the mysqld.log file:

2018-06-11T09:24:20.828080Z 0 [Note] Event Scheduler: Loaded 0 events
2018-06-11T09:24:20.828208Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.21-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2018-06-11T10:08:00.243701Z 2 [Note] Slave SQL for channel '': Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237