MySQL Replication Error & Solutions

on Slave Server:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.0.250
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 55208256
               Relay_Log_File: mysql-relay.000016
                Relay_Log_Pos: 55208402
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes

          Replicate_Ignore_DB: mysql,information_schema,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 55208256
              Relay_Log_Space: 55208597
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'replicant@54.179.161.17:3306' - retry-time: 10  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2154
1 row in set (0.00 sec)



Solution:

1. Login to slave server and telnet to master server ip for 3306,80,22 etc
2. if telnet not connecting mysql port then check communication with both server. or create replication from private ip
=======================================================================================================================

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.120
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 15240406
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 28533289
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
          Replicate_Ignore_DB: mysql,information_schema,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1677
                   Last_Error: Column 19 of table 'dbte' cannot be converted from type 'varchar(255)' to type 'smallint(6)'
st                 Skip_Counter: 0
          Exec_Master_Log_Pos: 28533143
              Relay_Log_Space: 2447667467
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1677
               Last_SQL_Error: Column 19 of table 'dbtest' cannot be converted from type 'varchar(255)' to type 'smallint(6)'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2154
1 row in set (0.00 sec)


Solution -1 :

Add below line in my.cnf  on slave server

vim /etc/my.cnf
slave-skip-errors = 1677,1062
:wq


Please execute below commands as directed on Slave server.

STOP SLAVE;
set global sql_slave_skip_counter=1;
START SLAVE;
show slave status \G


Solution -2 :

Add below line in my.cnf under [mysqld] section:


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

slave_type_conversions="ALL_LOSSY,ALL_NON_LOSSY"

slave-skip-errors=1677,1062

:wq

# service mysqld restart
===============================================================================================================


If replication error showing duplicate entry then do below settings in my.cnf and restart mysql service;

vim /etc/my.cnf

slave_skip_errors=1062,1032,1677

:wq

service mysqld restart