Removing Old Binary Logs from the Master and Then Starting

When using replication, removing binary logs is risky because a slave may not yet have finished with one of the logs you plan to remove.

For this example you'll need to delete the data and reset the master, to start from a clean slate, and then add some data:

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> INSERT INTO replication_table (f1,f2) VALUES(3,'third'); 

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)

it will be smoothly replicated onto the slave:

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

This example demonstrates that replication is not about an exact copy of the data. Rather, it's about replicating the statements from one server onto the other. This will result in an exact copy of the data, but if the master.info file or the binary logs are tampered with, MySQL will not be able to follow the commands in sequence, which may result in the data getting out of sync.