Skip to main content

Joel Liu's Library tagged replication →  View Popular

20 Sep 06

MySQL Bugs: #9726: Wrong position of replication

  • I've run into this too. My thought is that the binlog is getting sent over the
    network to the slave, but the the local disk write to the binlog on the master
    doesn't get flushed before the power goes down. Then, when the power comes back
    up, the binlog on the master is missing a "block" of events, which the slave
    (whose power didn't go down and thus did flush the contents of the relay-log +
    master.info) has. When the slave reconnects, it then uses the position it knows
    it read up until, but the master never saved it.
12 Jul 06

MySQL 5.0 Reference Manual :: 6.3.4 Replication Relay and Status Files


  • When you back up the slave's data, you should back up these two
    status files as well, along with the relay log files. They are
    needed to resume replication after you restore the slave's data.
    If you lose the relay logs but still have the
    relay-log.info file, you can check it to
    determine how far the SQL thread has executed in the master
    binary logs. Then you can use CHANGE MASTER
    TO     with the MASTER_LOG_FILE and
    MASTER_LOG_POS options to tell the slave to
    re-read the binary logs from that point. Of course, this
    requires that the binary logs still exist on the master server.

  • Relay logs have the same format as binary logs and can be read
    using mysqlbinlog. The SQL thread
    automatically deletes each relay log file as soon as it has
    executed all events in the file and no longer needs it. There is
    no explicit mechanism for deleting relay logs because the SQL
    thread takes care of doing so. However, FLUSH
    LOGS     rotates relay logs, which influences when the SQL
    thread deletes them.

ONLamp.com -- Live Backups of MySQL Using Replication

  • The last two stanzas above set up logging. The second to last
    stanza starts binary logging as we did on the master server, but this
    time on the slave. This is the log that can be used to allow the
    master and the slave to reverse roles, as mentioned earlier.
1 - 7 of 7
Showing 20▼ items per page

Selected Tags

Related Tags

Diigo is about better ways to research, share and collaborate on information. Learn more »

Join Diigo