0

A few tables in replica server (db2) were marked as crashed. We copied all database files (whole /var/lib/mysql) to another server (db3) in order to repair those tables. In the end we had to use pt-archive tool to copy data for the two biggest tables from primary server (db1). I copied fixed table files from temporary server (db3) to old replica (db2) and started replication. It's working but I want to start new replication from primary server (db1) to new replica (db3), which was used as a temporary.

Can I do this?

  1. Copy master.info and relay-log.info files
  2. Set server_id = 3 in configuration file
  3. RESET SLAVE
  4. Start new replication with using information from copied files from step 1
CHANGE MASTER TO MASTER_HOST='XXXX', MASTER_USER='XXX', MASTER_PASSWORD='pass',
  MASTER_PORT=3306, MASTER_LOG_FILE='master1-bin.000096', MASTER_LOG_POS=568;

Have I missed something?

2
  • MyISAM? InnoDB?
    – Rick James
    Commented Jan 6, 2023 at 16:49
  • We are using both: MyISAM and InnoDB. We are going to migrate all MyISAM tables to InnoDB in a future.
    – Ela
    Commented Jan 14, 2023 at 13:52

1 Answer 1

0

Probably should not RESET -- the "info" has what it needs as of the copy.

Step 0: SLAVE STOP; -- Otherwise the data and the logs will be out of sync.

(There may be more 'errors'.) I have only copied the entire server, then updated "server_id" in my.cnf. (Plus any changes needed for diff RAM size.)

marked as crashed -- That implies the table is Engine=MyISAM. You really should change to InnoDB. Meanwhile, just REPAIR TABLE for each table that is "marked as crashed". No need to redo the replication.

1
  • I don't need to stop slave - it's already stopped. server_id variable is commented out in my.cnf file. I want to do RESET because we copied files when replication between db1 and db2 was working (events were copied to db2 (to relay files) but non of this changes were applied because of error). Additionally relay logs include old db2 server host name in file names so it can generate some errors. On db1 binary logs are still available I sill can get all events.
    – Ela
    Commented Jan 6, 2023 at 19:19

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.