0

I tried to setup replication from my AWS EC2 instance to RDS Aurora, I tried this many times,

But now my slave is not updating event the SQL and IO thread are running fine.

And I found this error on master.

found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(7).

On Master:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = repDB
binlog_format           = row

show master status on MASTER

             File: mysql-bin.000008
         Position: 1348
     Binlog_Do_DB: repDB
 Binlog_Ignore_DB:
Executed_Gtid_Set:

show slave status on SLAVE

               Slave_IO_State: Waiting for master to send event
                  Master_Host: XXXXXXXXX
                  Master_User: XXXXXX
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 1348
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 1223
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.rds_history,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_replication_status
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1348
              Relay_Log_Space: 1389
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: XXXX-92fe-11e7-8aa0-0ea243321182
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 00000000-0000-0000-0000-000000000000:0
                Auto_Position: 0

show processlist on MASTER:

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 22
   User: XXXX_user
   Host: ip-XX-XX-XX-XX.ec2.internal:50762
     db: NULL
Command: Binlog Dump
   Time: 867
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 23
   User: root
   Host: localhost
     db: repDB
Command: Query
   Time: 0
  State: starting
   Info: show processlist
2 rows in set (0.00 sec)

show processlist on SLAVE:

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: rdsadmin
   Host: localhost
     db: NULL
Command: Sleep
   Time: 1
  State: delayed send ok done
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: rdsadmin
   Host: localhost
     db: NULL
Command: Sleep
   Time: 1
  State: delayed send ok done
   Info: NULL
*************************** 3. row ***************************
     Id: 7
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 940
  State: Waiting for master to send event
   Info: NULL
*************************** 4. row ***************************
     Id: 8
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 462
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 5. row ***************************
     Id: 9
   User: rdsadmin
   Host: localhost
     db: NULL
Command: Sleep
   Time: 11
  State: cleaned up
   Info: NULL
*************************** 6. row ***************************
     Id: 11
   User: rdsadmin
   Host: localhost
     db: NULL
Command: Sleep
   Time: 11
  State: cleaned up
   Info: NULL
*************************** 7. row ***************************
     Id: 12
   User: test
   Host: 172.31.25.250:54012
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
7 rows in set (0.00 sec)
  • Most people who try to use binlog_do_db do not understand it. Most people who understand binlog_do_db do not try to use it. Eliminate that option, and try again. The default behavior is to replicate everything. – Michael - sqlbot Sep 7 '17 at 1:06
  • Never Mind, Its fixed by disabling the bin log on the Aurora. – Bhuvanesh Sep 7 '17 at 5:08

Your Answer

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

Browse other questions tagged or ask your own question.