MySQL binary log

The binary log contains all statements that update data or potentially could have updated it.

Corresponding lines in my.cnf:

[...]
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# WARNING: Using expire_logs_days without bin_log crashes the server!
expire_logs_days        = 10
max_binlog_size         = 256M

[...]

Dealing with binary logs

mysql> SHOW master logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000010 | 1073741886 |
| mysql-bin.000011 | 1073741886 |
| mysql-bin.000012 |    2684333 |
| mysql-bin.000013 |        117 |
| mysql-bin.000014 |        117 |
| mysql-bin.000015 |        394 |
| mysql-bin.000016 |         98 |
+------------------+------------+
6 ROWS IN SET (0.00 sec)

Howto remove binary logs:

Do NOT delete binary logs via rm - always do this the mysql way!

Remove all logs before 'mysql-bin.000011'

mysql> purge BINARY logs TO 'mysql-bin.000011';
Query OK, 0 ROWS affected (0.60 sec)

Result:

mysql> show master logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000011 | 1073741886 |
| mysql-bin.000012 |    2684333 |
| mysql-bin.000013 |        117 |
| mysql-bin.000014 |        117 |
| mysql-bin.000015 |        394 |
| mysql-bin.000016 |         98 |
+------------------+------------+
6 rows in set (0.00 sec)

Remove all binary logs older than given date:

mysql -e "PURGE BINARY LOGS BEFORE '2009-07-01 0:00:00';

or within mysql terminal:

mysql> PURGE BINARY LOGS BEFORE '2012-08-31';
Query OK, 0 ROWS affected (2.10 sec)

Set some global mysql variables relevant to binary logs:

mysql> SELECT @@global.expire_logs_days;
+---------------------------+
| @@global.expire_logs_days |
+---------------------------+
|                         0 |
+---------------------------+
1 ROW IN SET (0.00 sec)
 
mysql> SET @@global.expire_logs_days=10;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> SELECT @@global.expire_logs_days;
+---------------------------+
| @@global.expire_logs_days |
+---------------------------+
|                        10 |
+---------------------------+
1 ROW IN SET (0.00 sec)
 
mysql> SELECT @@global.max_binlog_size;
+--------------------------+
| @@global.max_binlog_size |
+--------------------------+
|               1073741824 |
+--------------------------+
1 ROW IN SET (0.00 sec)
 
mysql> SET @@global.max_binlog_size=268435456;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> SELECT @@global.max_binlog_size;
+--------------------------+
| @@global.max_binlog_size |
+--------------------------+
|                268435456 |
+--------------------------+
1 ROW IN SET (0.00 sec)

Remember: these are all temporary changes (on the fly), don't forget to add theses settings to your my.cnf!

mysql/binary-log.txt · Last modified: 2013-11 by tb
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0 ipv6 ready