Binary Log for Backup in MySQL
MySQL Backup Basics – Part 2
Division 6
Forward: In this part of the series, we see how the binary log is used for backup.
By: Chrysanthus Date Published: 7 Aug 2012
Introduction
Binary log Files
A binary log file records events (SQL statements) from all users of the server as they use the server. As we 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 names 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. We shall 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. Another program, we shall 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):
mysqlbinlog binlog.0000001
Primitive Recovery
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 back:
mysqlbinlog binlog.000001 binlog.000002 binlog.000002 | mysql -u root –ppswrd
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 and the names of the 3 binary log files separated by spaces. Next you have a space and the | symbol. Then a space and the mysql client program command which takes the user name (root) and password. In some operating system, you might have to replace the mysql word in the command with something like, "C:Program FilesMySQLMySQL Server 5.1binmysql" . You may also have to precede the mysqlbinlog utility name with a path. You may also have to precede each log file name with a path.
In the above command, mysqlbinlog converts the binary log file contents into text and sends all the text to the mysql client program (preventing you from typing the text).
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.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT