Repairing or starting a new replication node

For starting new nodes or resetting nodes that broke for whatever reason, follow these steps. You'll need a backup file as created by the script above.

Note that to reset a clone, you need to use the following - this is mostly useful for a two node setup, if you have more it's a tad complicated in comparison.

  1. Copy to the new machine (Node2) the most recent backup from the existing machine (Node1).

  2. Stop the MySQL server on Node2, unpack the files it into the database directory, start the server again.

  3. On MySQL @ Node1: grant replication slave on *.* to repl@Node2 identified by 'mypassword';

  4. On both servers, make sure you have the right my.cnf settings, server-id and log-bin must be set.

  5. Double check local/ to make sure you have the same server_id as server-id, and that you have different sequence intervals. If the sequences isn't setup correctly there will be replication failures and much sadness.

  6. On MySQL @ Node2: change master to master_host = 'Node1', master_user='repl', master_password='mypassword', master_log_file='<take from mysql_replication_log_pos table, column log_file>', master_log_pos='<take from mysql_replication_log_pos table column log_pos>';

  7. On MySQL @ Node2: start slave;

  8. show slave status; and show master status; will give you an idea of what's going on. If Node1 has been active for awhile after the database backup was extracted the Seconds_behind_master value on Node2 will work it's way down to zero as the replication catches up. NULL in that fields means replication stopped and the time to catch up is unknown.