Mysql Binary Log (1)

檢視log：

mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |     29653 |

| mysql-bin.000002 |   1035665 |

| mysql-bin.000014 |       107 |

+------------------+-----------+

14 rows in set (0.00 sec)

檢視當前的log：

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000014 |      107 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

切換log：

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

檢視log裡的events：

mysql> show binlog  events in "mysql-bin.000013";

+------------------+-----+-------------+-----------+-------------+------------------------------------------------+

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                           |

+------------------+-----+-------------+-----------+-------------+------------------------------------------------+

| mysql-bin.000013 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.25a-log, Binlog ver: 4         |

| mysql-bin.000013 | 107 | Query       |         1 |         204 | use `test`; create table tb1(name varchar(50)) |

| mysql-bin.000013 | 204 | Query       |         1 |         272 | BEGIN                                          |

| mysql-bin.000013 | 272 | Table_map   |         1 |         316 | table_id: 33 (test.tb1)                        |

| mysql-bin.000013 | 316 | Write_rows  |         1 |         350 | table_id: 33 flags: STMT_END_F                 |

| mysql-bin.000013 | 350 | Xid         |         1 |         377 | COMMIT /* xid=22 */                            |

| mysql-bin.000013 | 377 | Rotate      |         1 |         420 | mysql-bin.000014;pos=4                         |

+------------------+-----+-------------+-----------+-------------+------------------------------------------------+

7 rows in set (0.00 sec)

mysql> show binlog    events in "mysql-bin.000013"  from 204;

+------------------+-----+------------+-----------+-------------+--------------------------------+

| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                           |

+------------------+-----+------------+-----------+-------------+--------------------------------+

| mysql-bin.000013 | 204 | Query      |         1 |         272 | BEGIN                          |

| mysql-bin.000013 | 272 | Table_map  |         1 |         316 | table_id: 33 (test.tb1)        |

| mysql-bin.000013 | 316 | Write_rows |         1 |         350 | table_id: 33 flags: STMT_END_F |

| mysql-bin.000013 | 350 | Xid        |         1 |         377 | COMMIT /* xid=22 */            |

| mysql-bin.000013 | 377 | Rotate     |         1 |         420 | mysql-bin.000014;pos=4         |

+------------------+-----+------------+-----------+-------------+--------------------------------+

5 rows in set (0.00 sec)

Event_type

This is the type of the event. We have seen three different types here, but there are many more. The type of the event is the basic way that we can transport information

to the slave. Currently—in MySQL 5.1.18 to 5.1.39—there are 27 events (several

of them are not used, but they are retained for backward compatibility), but this

is an extensible range and new events are added if later versions require additional

events.

Server_id

This is the server ID of the server that created the event.

Log_name

This is the name of the file that stores the event. An event is always contained in a single file and will never span two files.

Pos

This is the position of the file where the event starts; that is, it’s the first byte of the event.

End_log_pos

This gives the position in the file where the event ends and the next event starts.

This is one higher than the last byte of the event, so the bytes in the range Pos to End_log_pos − 1 are the bytes containing the event and the length of the event can be computed as End_log_pos − Pos.

Info

This is human-readable text with information about the event. Different information

is printed for different events, but you can at least count on the query event

to print the statement that it contains.

RESET MASTER 與 RESET SLAVE

The RESET MASTER command removes all the binlog files and clears the binlog index file.

The RESET SLAVE statement removes all files used by replication on the slave to get a clean start.

Neither the RESET MASTER nor the RESET SLAVE command is designed to

work when replication is active, so:

• When executing the RESET MASTER command (on the master), make

sure that no slaves are attached.

• When executing the RESET SLAVE command (on the slave), make

sure that the slave does

To make the server automatically purge old binlog files, set the expire-logs-days option—which is available as a server variable as well—to the number of days that you want to keep binlog files. Remember that as with all server variables, this setting is not preserved

between restarts of the server. So if you want the automatic purging to keep

going across restarts, you have to add the setting to the my.cnf file for the server.

To purge the binlog files manually, use the PURGE BINARY LOGS command, which comes

in two forms:

PURGE BINARY LOGS BEFORE datetime

This form. of the command will purge all files that are before the given date. If

datetime is in the middle of a logfile (and it usually is), all files before the one holding

datetime will be purged.

PURGE BINARY LOGS TO 'filename'

This form. of the command will purge all files that precede the given file. In other

words, all files before filename in the output from SHOW MASTER LOGS will be removed,

leaving filename as the first binlog file.