Learning Mysql

The Binary Log
An update log contains all the information needed to recreate any changes to the database since the server was started or the logs were flushed this feature allows you to always have an up-to-date backup of your database. You can keep a list of every SQL
query that changes data on the server bypassing the log-bin option to the MySQL
server (
mysqld_safe
, mysqld-nt.exe
, or mysqld
).
If no preferred name and directory is specified for the logfile, the server will use the file
<hostname>-bin in the MySQL data directory. Individual logfiles will have the extensions .000001, .000002, and soon any extensions you specify to the log-bin option are ignored. For example, on a machine with the hostname eden
, the binary logfiles are typically named eden-bin.000001, eden-bin.000002, and soon. It’s also common to seethe word mysql used in place of the hostname. The update log is saved in a compact binary format prior to MySQL version 5.0, the log-update option would save an update login text format. However, the text format is deprecated and is treated the same as log-bin in MySQL 5.0 and later.
When the server is shutdown, it ensures that all modifications to data have been written
(flushed) to the binary log. The next time the server is started, it opens anew logfile alongside the old one with an incremented number in the extension. For example, the current binary logfile might be called eden-bin.000012; after the server is restarted, it creates the new logfile eden-bin.000013 to log all modifications to the database since the restart. The logs can be manually flushed at anytime using the FLUSH LOGS
command in the monitor, or the mysqladmin flush-logs command from the command line.
You can view the SQL statements in the binary log by using the msqlbinlog command and specifying the full path to the binary logfile. For example, if on this system the
MySQL data directory is /usr/lib/mysql/data, you can view the contents of the binary logfile eden-bin.000002 by typing mysqlbinlog /usr/lib/mysql/data/eden-bin.000002
You’ll need to have the necessary permissions to access the MySQL data directory and to read the binary logfile on your host system. You might see something like this when you open a logfile:
use music;
SET TIMESTAMP=1151221361;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
/*!\C utf8 SET @@session.character_set_client=33,@@session.collation_connection=33,
@@session.collation_server=8;
DROP TABLE IF EXISTS artist at 30551
#060625 17:42:41 server id 1 end_log_pos 30794 Query thread_id=168
exec_time=0 error_code=0
SET TIMESTAMP=1151221361;

Download 4.24 Mb.

Share with your friends: