MySQL Binary Log for Backup
Backup Basics in MySQL – Part 2
Foreword: In this part of the series, I explain how the binary log is used for backup.
By: Chrysanthus Date Published: 16 Jun 2015
Binary log Files
A binary log file records events (SQL statements) from all users of the server as they use the server. As you saw in the previous part of the series, the number of binary log files for a server, increases as the server is being used. Each file is numbered; the higher the number the more recent is the file. At all times, there will be a current file that is recording the current events. There is also an index file whose name has the extension, “.index”. This index file has the name of the binary log files.
Example of Binary Log
A listing of a UNIX operating system directory that has the binary log files can be as follows:
-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index
This is a listing for a server that has been running for a few days. Note the numbering of the files (000001, 000002, 000003, etc.). In this example the base name is, gbichot2; in this name, 2 is part of the base name and not the numbering scheme.
The Flush Logs Statement
This is a statement that closes the current log file and reopens a new one. A binary log file would normally close when you shut down the server or when the log has reached its maximum size. However, this statement forces the current one to close and a new one to begin. You will see an example for the use of this statement later. The sequence number of a new binary log file is incremented by one relative to the previous file number.
MySQL installation comes with a number of programs. The mysql client, which you use to type in SQL statements is one of them. (You should use PurePerl MySQL API Command Line Tool in place of mysql client.) Another program, I will talk about here is called, mysqlbinlog. A program like this is called a utility or a tool. The binary log files are not saved in text format the way you typed the SQL statements. They are saved in binary (coded) format. The mysqlbinlog utility can be used to display each binary log file content in text format, showing the SQL statements that were typed. The following command (in command prompt window) will display the binlog.0000001 binary log file on the screen (do not forget to press the Enter key after typing such a command):
The mysqlbinlog file is in the bin directory of the server. In my computer, the full path to this file is, "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqlbinlog.exe".
As I said in the previous part of the series, the binary log (set of binary log files) should be kept in a device (drive) that is link (network) to the computer having the MySQL server but the device should not be part of the computer.
Assume that the computer that has the MySQL server has crashed (had a problem and its database is destroyed). One way to get back the database is from the binary log (set of binary log files). Remember that the binary log has all the SQL statements including the data definition (e.g. table creation) statements and data manipulation (e.g. insert) statements. If the binary log was kept in a save place, then all you have to do for restoration, is to re-install MySQL server in a new computer or the repaired computer and copy everything (the database) back.
Assume that the binary log consists of the files, binlog.0000001, binlog.0000002 and binlog.0000003 . The following command at the command prompt will copy everything to the file, myFile.txt:
mysqlbinlog --result-file=myFile.txt binlog.000001 binlog.000002 binlog.000003
The command begins with the name of the mysqlbinlog utility, which converts each binary log file content into text. This is followed by a space, the output text file name and the names of the 3 binary log files separated by spaces. You may have to precede the mysqlbinlog utility name with a path. You may have to precede the text file name with a path. You may also have to precede each log file name with a path.
To complete the recovery, copy all the content of the text file and past into the PurePerl MySQL API Command Line Tool window. The events will be executed as new SQL statements.
Limitations of Primitive Recovery
After about one month in a company, the number of binary log files are too many. The above UNIX listing shows the number for just a few days; so imagine what the listing for a month will look like (very long). Imagine that the server crashes for the first time after 6 months. The binary log files will be just too many and it will not be convenient to type all the names into the mysqlbinlog command line. The mysqlbinlog command may not take all the names as it has a limited number of file names it can take. I will offer the solution to this problem in the following parts of the series.
That is it for this part of the series. We stop here and continue in the next part.
Related LinksImplementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course