Replicating with an Active Binary Log on the Master

Copy this data onto a clean slave (if you've run previous examples on the slave server, make sure you delete the master. info file, and start the slave with the skip-slave-start option).

Now flush the logs on the master, simulating a server that's been running a while:

mysql>

FLUSH LOGS;

mysql>

FLUSH LOGS;

Now, when you look at the master status, you'll see that the server is already onto its third binary log:

mysql>

SHOW MASTER STATUS;

+-----------+----------+--------------+------------------+  File  Position  Binlog_do_db  Binlog_ignore_db  +-----------+----------+--------------+------------------+  g-bin.003  4    +-----------+----------+--------------+------------------+ 1 row in set (0.00 sec)

Now start the slave, and start it replicating from the correct point:

mysql>

CHANGE MASTER TO MASTER_LOG_FILE='g-bin.003',MASTER_LOG_POS=4;

Query OK, 0 rows affected (0.01 sec) mysql>

SLAVE START;

Query OK, 0 rows affected (0.00 sec)

The slave will now start from the correct log on the master. You still have two other binary logs on the master server, taking up space, and you'll need to start maintaining the log files to ensure they don't get out of hand. You may feel tempted to delete logs one and two, but you cannot safely do this if there is still the possibility that slaves may need to make use of them.

To check this, you'll need to check the slave status for each slave. In this case, there's only one:

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.003  4  s-bin.003  830  g-bin.003  Yes  Yes  replication_db   0   0  4  1885

You can see that the slave is using g-bin.003 and is up-to-date (position 4). If all slaves are
up-to-date, you can safely remove binary logs 1 and 2 from the master, with the PURGE MASTER LOGS statement, as follows :

mysql>

PURGE MASTER LOGS TO "g-bin.003";

Query OK, 0 rows affected (0.00 sec)

If you did a listing in the data directory (or wherever you've specified the binary logs to be), you'll see that the two earlier ones have been removed. This statement will fail if an active slave is attempting to read a log you're trying to delete, giving the following error:

mysql>

PURGE MASTER LOGS TO "g-bin.003";

ERROR: A purgeable log is in use, will not purge

If slave is not connected, and you purge a binary log that has not yet been used, that slave will be unable to continue replicating. At some stage this process may be automated, but for now you'll have to check each slave manually to see its position. Let's see what would happen if you didn't check. First, stop the slave:

mysql>

SLAVE STOP;

Query OK, 0 rows affected (0.00 sec)

Now, on the master, flush the logs once more, add a new record, and then purge the binary logs:

mysql>

FLUSH LOGS;

Query OK, 0 rows affected (0.00 sec) mysql>

INSERT INTO replication_table (f1,f2) VALUES(4,'fourth');

Query OK, 1 row affected (0.00 sec) mysql>

FLUSH LOGS;

Query OK, 0 rows affected (0.00 sec) mysql>

SHOW MASTER STATUS;

+-----------+----------+--------------+------------------+  File  Position  Binlog_do_db  Binlog_ignore_db  +-----------+----------+--------------+------------------+  g-bin.005  4    +-----------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>

PURGE MASTER LOGS TO "g-bin.005";

Query OK, 0 rows affected (0.02 sec)

Now, if you restart the slave, it will not replicate, as it's looking for a nonexistent binary log:

mysql>

SLAVE START;

Query OK, 0 rows affected (0.00 sec)

The problem now is that the most recent INSERT statement on the master does not appear in a log anywhere because all "old" logs have been purged. If you've made a backup of the binary logs, you can easily restore it, but if not, you can manually rerun the statement and then make the slave look at the most recent log, as follows:

mysql>

INSERT INTO replication_table (f1,f2) VALUES(4,'fourth');

Query OK, 1 row affected (0.00 sec) mysql>

CHANGE MASTER TO MASTER_LOG_FILE='g-bin.005',MASTER_LOG_POS=4;

Query OK, 0 rows affected (0.01 sec)

Now, if you add another record to the master:

mysql>

INSERT INTO replication_table (f1,f2) VALUES(5,'fifth');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM replication_table; +------+--------+  f1  f2  +------+--------+  1  first   2  second   3  third   4  fourth   5  fifth  +------+--------+ 5 rows in set (0.00 sec)