6

I'm attempting to set up replication for a large (120 GB) MySQL database and I messed up.

I shut down the master, copied the data files, and restarted the master. Now I've set up the slave & started it up, and I realize that I neglected to get the MASTER_LOG_FILE and MASTER_LOG_POS values needed to setup replication.

I still have an untouched copy of the data files. Is there any way to determine MASTER_LOG_FILE and MASTER_LOG_POS from those files?

Related question:

  • what happens if you set the MASTER_LOG_POS to an earlier point in time?
  • Am I correct in assuming duplicate queries will be executed, possibly resulting in duplicate data?

Edit

I've been investigating the mysqlbinlog command and discovered what looks like the correct info by executing:

mysqlbinlog --to-last-log --start-datetime='2015-06-21 20:05:00'
    mysql-bin.000006 | grep end_log_pos

The output looks something like this:

... a whole bunch more lines ...

#150621 20:10:21 server id 1  end_log_pos 720159843     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:21 server id 1  end_log_pos 720161877     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:21 server id 1  end_log_pos 720162762     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:22 server id 1  end_log_pos 720164796     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:22 server id 1  end_log_pos 720164815     Stop

I'm guessing I should use one of those last two positions, but I'm not sure which.

7
  • Did you start mysql on the Slave yet ??? (Please say no) – RolandoMySQLDBA Jun 23 '15 at 20:23
  • Is mysql-bin.000006 the last binlog ??? – RolandoMySQLDBA Jun 23 '15 at 20:23
  • Yeah, I did - like I said, I messed up :-P And yes, that's the last binlog. But let me be clear - this is an untouched copy of the files from the master, completely separate from the slave. – Kryten Jun 23 '15 at 20:41
  • I know I can fix it if I start over, but I'd prefer to avoid that. – Kryten Jun 23 '15 at 20:41
  • Question: When you started mysql on the Slave, did it open mysql-bin.000007 ??? – RolandoMySQLDBA Jun 23 '15 at 20:42
5

Hopefully, you did not start mysqld on the slave yet.

It just so happens that a binlog position is also the binlog size at the end of every binlog event. In your case, you can use the last binlog the slave sees, which I can assume is mysql-bin.000006. Get the filesize and use that as the position.

Therefore, on the Slave you would run

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='...',
    MASTER_PORT=3306,
    MASTER_USER='repluser',
    MASTER_PASSWORD='replpassword',
    MASTER_LOG_FILE='mysql-bin.00006',
    MASTER_LOG_POS=720164815;
START SLAVE;

and that should do it !!!

I have discussed the binlog position being the filesize before

You also asked

what happens if you set the MASTER_LOG_POS to an earlier point in time?

Yes, it will replay those binlog event and cause issues.

1
  • Awesome! thanks for all your help! And thanks for educating me on replication, which is a new subject for me. – Kryten Jun 23 '15 at 20:51

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.