Mysql Binary Logs Tutorial
Mysql Binary Logs Tutorial | Mysql Binary Log Backups:
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows).The binary log has two important purposes:
1) For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.
2) Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.The binary log is not used for statements such as SELECT or SHOW that do not modify data.The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported, row-based logging, statement-based logging and mixed-base logging.
To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin.we have to uncommnet the following filed from my.ini or my.cnf file to enable the binary log-
# binary logging is required for replication
log-bin=mysql-bin // base name is mysql-bin
The server creates a new file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size(which is 1gb).Binary log size can be seen using following statement:
SHOW VARIABLES LIKE 'max_binlog_size'
A replication slave server by default does not write to its own binary log any data modifications that are received from the replication master. To log these modifications, start the slave with the --log-slave-updates option in addition to the --log-bin option.
You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS. You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days.
RESET Syntax
RESET reset_option;
reset_option can be any of the following:
1)MASTER .
2)SLAVE.
PURGE BINARY LOGS Syntax
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2012-03-22 22:46:26';
Expire_logs_days Syntax
We can set the variable expire_logs_days in following way:-
1) Mysql> SET GLOBAL expire_logs_days = 6;
Query OK, 0 rows affected (0.00 sec)
Mysql> show variables like 'expire_%';
+------------------+--------+
| Variable_name| Value |
+------------------+--------+
| expire_logs_days | 7 |
+------------------+--------+
1 row in set (0.00 sec)
2) Also we can cghange in Masters my.cnf or my.ini file like this:-
#expire bin logs
expire_logs_days = 7
You can display the contents of binary log files with the mysqlbinlog utility. So to read and display the contents of the binary log file named binlog.000001, use this command:
mysqlbinlog binlog.000001
The binary log files and its data are likely to be very huge, thus making it almost impossible to read anything on screen. However, you can pipe the output of mysqlbinlog into a file which can be open up for later browsing in text editor, by using the following command:
mysqlbinlog binlog.000001 > filename.txt
References:-
1)http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
2)http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows).The binary log has two important purposes:
1) For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.
2) Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.The binary log is not used for statements such as SELECT or SHOW that do not modify data.The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported, row-based logging, statement-based logging and mixed-base logging.
To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin.we have to uncommnet the following filed from my.ini or my.cnf file to enable the binary log-
# binary logging is required for replication
log-bin=mysql-bin // base name is mysql-bin
The server creates a new file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size(which is 1gb).Binary log size can be seen using following statement:
SHOW VARIABLES LIKE 'max_binlog_size'
A replication slave server by default does not write to its own binary log any data modifications that are received from the replication master. To log these modifications, start the slave with the --log-slave-updates option in addition to the --log-bin option.
You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS. You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days.
RESET Syntax
RESET reset_option;
reset_option can be any of the following:
1)MASTER .
2)SLAVE.
PURGE BINARY LOGS Syntax
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2012-03-22 22:46:26';
Expire_logs_days Syntax
We can set the variable expire_logs_days in following way:-
1) Mysql> SET GLOBAL expire_logs_days = 6;
Query OK, 0 rows affected (0.00 sec)
Mysql> show variables like 'expire_%';
+------------------+--------+
| Variable_name| Value |
+------------------+--------+
| expire_logs_days | 7 |
+------------------+--------+
1 row in set (0.00 sec)
2) Also we can cghange in Masters my.cnf or my.ini file like this:-
#expire bin logs
expire_logs_days = 7
You can display the contents of binary log files with the mysqlbinlog utility. So to read and display the contents of the binary log file named binlog.000001, use this command:
mysqlbinlog binlog.000001
The binary log files and its data are likely to be very huge, thus making it almost impossible to read anything on screen. However, you can pipe the output of mysqlbinlog into a file which can be open up for later browsing in text editor, by using the following command:
mysqlbinlog binlog.000001 > filename.txt
References:-
1)http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
2)http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
