0

I setup a master slave replication, where the master has the following setup

datadir                = /mnt/DATADIR/

bind-address            = 0.0.0.0

binlog-ignore-db        = information_schema,mysql,performance_schema,sys
slave-skip-errors      = 1062,1452,1146
server-id              = 3
binlog-checksum        = none
log_bin                = /mnt/DATADIR/log-bin.log
expire_logs_days       = 10
max_binlog_size        = 100M

After restarting the master I check the binary log

mysql> SHOW MASTER STATUS;
+----------------+----------+--------------+-------------------------------------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+----------------+----------+--------------+-------------------------------------------------+-------------------+
| log-bin.000003 |      150 |              | information_schema,mysql,performance_schema,sys |                   |
+----------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0,00 sec)

I am running the following command on the slave to change the log position

SLAVE STOP; CHANGE MASTER TO MASTER_LOG_POS=150, MASTER_LOG_FILE='log-bin.000003'; SLAVE START;

The output on the slave

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ...
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000003
          Read_Master_Log_Pos: 150
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 266
        Relay_Master_Log_File: log-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 150
              Relay_Log_Space: 422
              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:

Now testing the replication, I create a dummy schema on the master

mysql> create schema test4;
Query OK, 1 row affected (0,02 sec)

However when I am checking the output of the slave I am getting some error

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ...
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000003
          Read_Master_Log_Pos: 302
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 266
        Relay_Master_Log_File: log-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 150
              Relay_Log_Space: 574
              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: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
1 row in set (0,00 sec)

However when I am checking the binary log file everything seems fine:

mysqlbinlog log-bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160714  8:17:56 server id 3  end_log_pos 123   Start: binlog v 4, server v 5.7.12-0ubuntu1.1-log created 160714  8:17:56
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
lC6HVw8DAAAAdwAAAHsAAAABAAQANS43LjEyLTB1YnVudHUxLjEtbG9nAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AOAGcMo=
'/*!*/;
# at 123
#160714  8:17:56 server id 3  end_log_pos 150   Previous-GTIDs
# [empty]
# at 150
#160714  8:27:46 server id 3  end_log_pos 211   Anonymous_GTID  last_committed=0    sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 211
#160714  8:27:46 server id 3  end_log_pos 302   Query   thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1468477666/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create schema test4
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

What could be the problem? The master version is

mysql> SHOW VARIABLES LIKE "%version%";                                                                                                                                                                     +-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| innodb_version          | 5.7.12                |
| protocol_version        | 10                    |
| slave_type_conversions  |                       |
| tls_version             | TLSv1,TLSv1.1         |
| version                 | 5.7.12-0ubuntu1.1-log |
| version_comment         | (Ubuntu)              |
| version_compile_machine | x86_64                |
| version_compile_os      | Linux                 |
+-------------------------+-----------------------+

And the slave version is

mysql> SHOW VARIABLES LIKE "%version%";                                                                                                                                                                     +-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.73-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0,00 sec)

Is there some new setting which needs to be set i the 5.7 version to be compatible with 5.1?

  • well it seems some issue with relay log files. you could try removing rm relay and then issuing stop slave, change master and then starting slave. Give it a try – Nawaz Sohail Jul 14 '16 at 8:36
  • I already tried this, but does not solve the problem. – OHLÁLÁ Jul 14 '16 at 9:22
  • "slave-skip-errors = 1062,1452,1146" -- Don't sweep them under the rug; fix them. – Rick James Jul 16 '16 at 5:54
0

As shown by SHOW SLAVE STATUS\G it seems Master Host is not known to Slave,

  Slave_IO_State: Waiting for master to send event
                  **Master_Host: ...**

Use this for Change Master,

CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;

Refer link: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

  • The master host is know, I just deleted the IP address for security reason. – OHLÁLÁ Jul 14 '16 at 7:12
  • 1
    Hey, have you checked for the Compatibility things as replication feature is modified a lot from 5.1 to 5.7 please check link – Priyanka Kariya Jul 14 '16 at 9:56
0

Most likely problem: there is an error in the relay log. Usually this is the result of a sudden database shutdown (e.g. server crashed).

The fix for this is to simply tell the database to discard the current relay logs, and start again. To do this:

SHOW SLAVE STATUS;

and note down:

relay_master_log_file

exec_master_log_pos

then run:

STOP SLAVE;
CHANGE MASTER TO  
MASTER_LOG_FILE='relay_master_log_file [from above],
MASTER_LOG_POS= exec_master_log_pos [from above];
START SLAVE;  
  • I already tried that. I stopped the master mysql, removed all the binary logs, then restarted the master and change the log position and file on the slave, but does not work. – OHLÁLÁ Jul 14 '16 at 9:21
  • in my experience it is rarely the Master that is at fault, and almost always the Slaves Relay logs that are at fault, so there should be no need to restart the master / remove the Masters Binary logs. One thing I notice, is that you are Using GTID in the Binary logs above. I can't remember that far back but did MySQL 5.1 support GTID? Maybe try turning it off on the master, and trying again. Although as @Learning DBA mentions above, there are a LOT of differences between 5.1 and 5.7. – IGGt Jul 14 '16 at 10:29
  • I added the following line to the master config file: gtid-mode =OFF. It could be some version compatibility. – OHLÁLÁ Jul 14 '16 at 10:38
0

Replicating from a newer version (5.7) to an older version (5.1) is neither supported, nor likely to work. The other direction 'works'.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.