MySQL: Replication Failed: Error 'Duplicate entry'

Environment

• MySQL

Summary
Replication Failed on MySQL as

Error 'Duplicate entry...'

The Original Replication Setup:

Slave: Server-A replicate the whole DATABASE-A from Server-B

Slave: Server-B replicate DATABASE-A exclude DATABASE-A.TableA, DATABASE-A.TableB from Server-A

Showing why it was failed. It was easy to bring Duplicate Entry Error from the Replication between the Two Servers.


Question
Server-A

mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
            File: mysqld-bin.000007
        Position: 1937307
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event

[...]
              
              Replicate_Do_DB: DATABASE-A
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '494' for key 'PRIMARY'' on query. Default database: 'DATABASE-A'. Query: 'INSERT INTO...'

[...]
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '494' for key 'PRIMARY'' on query. Default database: 'DATABASE-A'. Query: 'INSERT INTO...'
1 row in set (0.00 sec)

ERROR:
No query specified

Server-B

mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
            File: mysqld-bin.000020
        Position: 15607161
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State:

[...]

              Replicate_Do_DB: DATABASE-A
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: DATABASE-A.TableA,DATABASE-A.TableB
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:

[...]

                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'DB-SYNC-ACCOUNT@172.16.17.1:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

/var/log/mysqld_error.log:

180627  8:42:48 [ERROR] Slave SQL: Error 'Duplicate entry '494' for key 'PRIMARY'' on query. Default database: 'DATABASE-A'. Query: 'INSERT INTO ...', Error_code: 1062
180627  8:42:48 [Warning] Slave: Duplicate entry '494' for key 'PRIMARY' Error_code: 1062
180627  8:42:48 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysqld-bin.000020' position 8931479

Analysis & Checklist
Server-A & Server-B playing the difference modules for the System. So, Replication required. Working with the Producer, confirmed that:

• Server-A only need the DATABASE-A.TableA from Server-B

• Server-B need almost all Tables on DATABASE-A from Server-A but exclude TableA & TableB

Solution
1: Rebuilt the Database, & modified the Replication Setting on Server-B
Server-A

mysql> STOP SLAVE;

$ mysqldump --u {USER} -p DATABASE-A --ignore-table=DATABASE-A.TableA --ignore-table=DATABASE-A.TableB > export-DATABASE-A.sql

Server-B

mysql> STOP SLAVE;
mysql> TRUNCATE TableC;
mysql> TRUNCATE TableD;
mysql> TRUNCATE TableE;

$ mysql -u {USER} -p DATABASE-A < export-DATABASE-A.sql

/etc/my.cnf:

replicate-do-db=DATABASE-A
replicate-ignore-table=DATABASE-A.TableA,DATABASE-A.TableB

msyql> START SLAVE;

2: Modified the Replication Setting & started back SLAVE on Server-A
/etc/my.cnf:

replicate-do-db=DATABASE-A
replicate-do-table=DATABASE-A.TableA

msyql> START SLAVE;

Update