Avoiding Too Many Updates

This example shows what can happen if you do not start the slave at the correct point in the master binary log.

Starting with a clean master, add a few records, and take note of the binary log details immediately after making the copy:

mysql> DELETE FROM replication_table; mysql> RESET MASTER; mysql> INSERT INTO replication_table (f1,f2) VALUES(1,'first'); mysql> INSERT INTO replication_table (f1,f2) VALUES(2,'second'); mysql> SHOW MASTER STATUS; +-----------+----------+--------------+------------------+ | File      | Position | Binlog_do_db | Binlog_ignore_db | +-----------+----------+--------------+------------------+ | g-bin.001 | 280      |              |                  | +-----------+----------+--------------+------------------+ 1 row in set (0.00 sec)

Copy the data to the slave, and start the slave. The slave should be clean (no master.info file and no data in the replication_db database) and contain what looks like an ordinary set of options in its configuration file, as follows:

 master-host     = 192.168.4.100 master-user     = replication_user master_password = replication_pwd server-id       = 3 replicate-do-db = replication_db 

Start the slave server and take a look at the slave status to see that replication has started correctly:

mysql> SHOW SLAVE STATUS; +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | Master_Host   | Master_User      | Master_Port | Connect_retry |  Master_Log_File    | Read_Master_Log_Pos | Relay_Log_File           |  Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running |  Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno  | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | 192.168.4.100 | replication_user | 3306        | 60            |  g-bin.001 | 280                 | s-bin.003 |  325           | g-bin.001    | Yes              | Yes                | replication_db  |                     | 0          |            | 0             | 280                 | 329             | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ 1 row in set (0.00 sec) 

Everything seems to be working smoothly. Replication has begun, and the slave is at the same point as the master, that is binary log g-bin.001 and position 280. However, when you examine the data on the slave, you're in for a nasty surprise:

mysql> SELECT * FROM replication_table; +------+--------+ | f1   | f2     | +------+--------+ |    1 | first  | |    2 | second | |    1 | first  | |    2 | second | +------+--------+ 4 rows in set (0.00 sec)

The problem is that the slave started replicating from the beginning of the first binary log, which means it repeated the two INSERT statements even though the copy of the data was made after that. There are two solutions. You can either run RESET MASTER on the master immediately after making the copy, or run a CHANGE MASTER TO… statement on the slave before starting to replicate to set it to begin at the right point, as you did in the "Replicating with an Active Binary Log on the Master" section (which entails starting the server with the skip-slave-start option).