Binary Log File in MySQL
Backup Basics in MySQL – Part 1
MySQL Course
Foreword: In this part of the series I talk about MySQL log files, especially the one called, Binary Log, which is used during backup.
By: Chrysanthus Date Published: 16 Jun 2015
Introduction
Pre-Knowledge
This series is part of the volume, MySQL Course. The links to the different series you should have read before reaching here are given at the bottom of the page. You should be reading the series in the order given and in each series you should read the parts in the order given.
Log Files
MySQL log files are files that record what the MySQL server is doing. A log file is simply called, “log”. I will talk about 3 logs of MySQL in this article: the general query log, the error log and the binary log. The binary log is needed for backup, so I will talk about that, in depth.
The General Query Log
The general query log is a general record of what MySQL (server) is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error from a client and want to know exactly what the client sent to the server.
The server writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed.
The Error Log
The error log contains information indicating when the server was started and stopped and also any critical errors that occur while the server is running. If the server notices a table that needs to be automatically checked or repaired, it writes a message to the error log.
On some operating systems, the error log contains what is known as “stack trace” if the server dies. The stack trace can be used to determine where the server died.
So the general log records all activities, while the error log records only errors and error related activities.
The Binary Log
SQL statements are called events. 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 mainly SQL statement events, so that when the server is damaged, you can use it for restoration. Of course the binary log should be saved in a drive that is not part of the computer, which has the MySQL server and data.
The binary log file has 3 formats, called, Statement-Based Logging, Row-Based Logging and Mixed Logging. The default format is Statement-Based Logging. However, with a few MySQL versions, the default format is Mixed Logging. In this basic tutorial I assume the default for your version is Statement-Based Logging and I will not talk about the formats any further.
To enable the binary log, start the server with the --log-bin[=base_name] option. For example, in my Windows operating system, I would start the server with something like:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --console --log-bin=e:\dir1\dir2\filename
filename should not have an extension. You can precede the base_name with the absolute path.
Binary Files
Logging is a continuous process. As long as there is activity, the SQL statements are recorded into the binary log file. So you cannot have only one binary log file; it will be too big. The server attaches a number at the end of each binary log file name; the higher the number, the more recent is the file.
A new binary log file is started (opened) when the current one reaches a maximum size of 1GB. You can change this value, but I will not go into that. Also, each time the server starts or flushes (see later) logs, a new binary log file is started.
To keep track of which binary log files have been created, the server also creates a binary log index file, whose purpose is to hold the names of all created binary log files. By default, this has the same base_name as the binary log files, but with an extension of “.index”. You can change the name of the binary log index file, but I will not go into that.
With binary logging, the term “binary log file” generally denotes an individual numbered file containing database (SQL statements) events, while the term “binary log” collectively denotes the set of numbered binary log files plus the index file.
The word, “log” in this article means a different thing from the word, “login” in the phrase, “login to the database server”.
That is it, for this part of the series. We stop here and continue in the next part.
Chrys
Related Links
Implementing Database in MySQLProgramming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
MySQL Transaction
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course
NEXT