Recovering from broken MySQL replication. [SOLVED]

Discussion in 'ISPConfig 3 Priority Support' started by TTZX, Apr 5, 2016.

  1. TTZX

    TTZX New Member HowtoForge Supporter

    I have had a slave server down for a while and after getting it back on line the MySQL replication is not working. This is the error:
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

    What is the best way to recover from this error? I will have backups for MySQL from the master server if needed.

    I was planning on re doing the replication as per the howto ( but this requires stopping both MySQL servers to copy the files over. When I try and stop the MySQL server on the slave server it keeps restarting. I have disabled the MySQL monitoring in the web interface for the slave server but this didn't help.

    Any help would be appreciated before I shoot myself in the foot! ;) Thanks.
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    I guess you will have to export the data from master server and reimport it to the slave and then setup the replication again.
  3. TTZX

    TTZX New Member HowtoForge Supporter

    How can I stop the mysql service from restarting?
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    If ispconfig is restarting it, then you can e.g. comment out the ispconfig cronjob in the root crontab temporarily.
  5. florian030

    florian030 Well-Known Member HowtoForge Supporter

    If you run mysql master-master and don`t have problems about some missing / outdated data, you can also adjust MASTER_LOG_POS and MASTER_LOG_FILE to the last working position.
    But i would rebuild the replication. ;)
    There is no need to copy the files. You can import the data from the other master using LOAD DATA or by importing a dump (i prefer importing a dump). You can find additional infos here.
  6. TTZX

    TTZX New Member HowtoForge Supporter

    Got it working now. Ran into a couple of issues I'll jot down here to jog my memory or help someone else if they have the same problem.

    After importing the data into the slave server db I couldn't get the slave to connect to the master. Turned out I forgot that I had set up SSL certificates sometime after I had followed the guild I linked to here. I needed to update the paths on the slave db using MASTER_SSL_CERT and MASTER_SSL_KEY. Guess I could have avoided this issue if I used the same paths but different files on each server.

    After this I still couldn't get the slave server to connect to the db on the master. So I tried to log in to the master db from a terminal on the slave server and got:
    Host 'host_name' is blocked because of many connection errors.
    Unblock with 'mysqladmin flush-hosts'​
    Funny, I didn't see this helpful message in either the master of slave mysql logs. So I run FLUSH HOSTS; on the master server but I still couldn't connect. I then restarted both mysql services and everything resumed normal operation. Don't know why that worked but its worth a try if nothing else.

    I've tested replications in both directions by creating and deleting tables and all seems to be OK.

    Thanks for help guys :).
    till likes this.

Share This Page