Replicating with an Active Binary Log on the Master

This example demonstrates how to handle the situation where the master has been running for a while with binary logging activated, and you want to set up a replication. First, shut down the slave to avoid any conflicts from the previous example:

% /usr/local/mysql/bin/mysqladmin -uroot -pg00r002b shutdown 020821 23:40:49  mysqld ended.

You'll use the same table as in the previous example. On the master, delete it and reset the binary logs to start afresh before inserting some records, as follows:

mysql> DELETE FROM replication_table; Query OK, 4 rows affected (0.09 sec) mysql> RESET MASTER; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO replication_table (f1,f2) VALUES(1,'first'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO replication_table (f1,f2) VALUES(2,'second'); Query OK, 1 row affected (0.01 sec) 

Now copy this data to the slave, and check the offset on the master's binary log. Make sure no new data was written to the master after you copied the data but before you could check the status:

mysql> SHOW MASTER STATUS; +-----------+----------+--------------+------------------+ | File      | Position | Binlog_do_db | Binlog_ignore_db | +-----------+----------+--------------+------------------+ | g-bin.001 | 280      |              |                  | +-----------+----------+--------------+------------------+ 1 row in set (0.00 sec)

Now, on the slave, perform the same options as in the previous example—that is, copy the data, set the configuration options (with the following change), delete the master.info file if it exists (it would have been created in the previous example in the data directory, as C:\mysql\data or /usr/local/mysql/data), and restart the server. The only difference is that the configuration file should contain the option:

 skip-slave-start 

You don't want to start the slave replicating until you have set it to begin at the right point. Now add some more records to the master:

mysql> INSERT INTO replication_table (f1,f2) VALUES(3,'third'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO replication_table (f1,f2) VALUES(4,'fourth'); Query OK, 1 row affected (0.01 sec)

Now, on the slave, you'll need to tell it to start with the correct binary log file and the correct offset. To do this, set the MASTER_LOG_FILE to g-bin.001 (or whatever was shown when you ran SHOW MASTER STATUS), and set the MASTER_LOG_POS to 280 (or whatever is applicable in your case). Once this is done, start the slave replicating and test the results.

mysql> CHANGE MASTER TO MASTER_LOG_FILE='g-bin.001',  MASTER_LOG_POS=280; Query OK, 0 rows affected (0.00 sec) mysql> SLAVE START; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM replication_table; +------+--------+ | f1   | f2     | +------+--------+ |    1 | first  | |    2 | second | |    3 | third  | |    4 | fourth | +------+--------+ 4 rows in set (0.01 sec)