MySQL Binary Log File
MySQL Backup Basics – Part 1
Division 6
Forward: In this part of the series we look at MySQL log files, especially the one called, Binary Log, which is used during backup.
By: Chrysanthus Date Published: 7 Aug 2012
Introduction
Prerequisite
Before reaching here, you should have covered the series in this blog beginning with the following titles:
Database
Normalization
Referential Integrity in Database
Implementing Database in MySQL
Introduction to MySQL Programming for Events
MySQL Access Privilege Overview
To reach any of the series, just type the title and my name Chrys in the Search Box of this page and click Search.
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 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.
Starting the Server to use Binary Logging
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:dir1dir2filename
The filename should not have an extension. You can precede the base_name with the absolute path.
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
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT