0

Ok, so here's the scenario:

  1. Our master went down, boom! gone (Call this one DB1)
  2. We switched our backup replica server. Ok, life is good, sort of (Call this DB2)
  3. Binary logs were not turned on for DB2, therefore we are missing a few hours of logs
  4. We want to bring another server up and sync with DB 2

Question: Can we generate the logs for the few hour gap? Or are they gone? I have the logs for everything up to that position and after but not during.

Followups: Data is both InnoDb and MyIsam. The binary logs have been turned on for DB2. The issue is that they were turned on 2 hours after the server was in production. So there is two hours worth of binary logs that are missing

If i'm down voted, please tell me what I can do to improve my question.

6
  • Is all your data on DB2 InnoDB ??? Jul 11, 2014 at 18:17
  • Where you running MySQL Replication ? Do you know how to setup MySQL Replication ? Jul 11, 2014 at 18:20
  • How much data do you have in your database ? Please run SELECT SUM(data_length+index_length) TotalSpaceForMySQL FROM information_schema.tables and tell me the number. Jul 11, 2014 at 18:45
  • 1
    Did you have log-slave-updates configured on DB2 ? Jul 11, 2014 at 18:50
  • No, but I think that sounds like a good idea to do forward on all slaves should this happen again.
    – SeanDowney
    Jul 11, 2014 at 21:46

1 Answer 1

2

Can we generate the logs for the few hour gap? Or are they gone?

They are gone. You cannot do a sync with that gap. However, it should not matter.

You need to setup replication from scratch on DB1.

During off-hours, run this on DB2

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS=""
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
echo "SET SQL_LOG_BIN = 0;" > MySQLData.sql
echo "STOP SLAVE;" >> MySQLData.sql
mysql ${MYSQL_CONN} -ANe"RESET MASTER;"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "SET SQL_LOG_BIN = 1;" >> MySQLData.sql

You just setup replication on DB1 and load MySQLData.sql into it.

BTW Make sure log-slave-updates is configured on DB1 and DB2

1
  • Don't forget about the --events option. Jul 13, 2014 at 5:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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