1

Maintaining Log Files in mysql

MySQL mainly having four different kinds of logs:

error log, general log, binary log and slow log

1. The Error Log

The error log contains information indicating when mysqld was started and stopped and also any
critical errors that occur while the server is running. If mysqld 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 a stack trace if mysqld dies. The trace can be used
to determine where mysqld died

By default error log is enabled , located in “/usr/local/mysql/data

/” with “[Link]” (in generic/source installaion) and in rpm (/var/lib/mysql/) with
“[Link]”

mysql> show variables like "log_error";

we can change the error log file path in [Link]

[mysqld]
log-error=/var/log/[Link]

Beginning with MySQL 4.0.10, you can specify where mysqld stores the error log file with the --log-
error[=file_name] option. If no file_name value is given, mysqld uses the name host_name.err and
writes the file in the data directory. (Prior to MySQL 4.0.10, the Windows error log name is [Link].)
If you execute FLUSH LOGS, the error log is renamed with a suffix of -old and mysqld creates a new
empty log file.

#mysqladmin flush-logs or mysql > flush logs

#mv host_name.err-old backup-directory

2. The General Query Log

The general query log is a general record of what mysqld 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 in a client and want to know exactly
what the client sent to mysqld.
 2

To enable the general query log, start mysqld with the --log[=file_name] or -l [file_name] option.
mysql> show variables like "log";
# /etc/init.d/mysql start –log=[Link]

# vi /usr/local/mysql/data/[Link]

3. The Binary Query Log

The binary log is a set of log files that contain information about data modifications made to a
MySQL server instance. The log is enabled by starting the server with the --log-bin option.
The binary log was introduced in MySQL 3.23.14. It contains all statements that update data. It also
contains statements that potentially could have updated it (for example, a DELETE which matched no
rows), unless row-based logging is used. Statements are stored in the form of "events" that describe
the modifications. The binary log also contains information about how long each statement took that
updated data.
The binary log also contains some other metadata, including:
 Information about the state of the server that is needed to reproduce statements correctly
 Error codes
 Metadata needed for the maintenance of the binary llog itself (for example, rotate events)
The binary log is a trace of changes of the server's global state generated during its operation. The
events that it contains describe changes of this state. More precisely, binary log events describe
actions that can be used to reproduce the same changes of global state which have happened on
server.
The binary log has two important purposes:
 For replication, the binary log is used on master replication servers as a record of the
statements to be sent to slave servers. Many details of binary log format and handling are
specific to this purpose. The master server sends the events contained in its binary log to its
slaves, which execute those events to make the same data changes that were made on the
master. A slave stores events received from the master in its relay log until they can be
executed. The relay log has the same format as the binary log.
 Certain data recovery operations require use of the binary log. After a backup file has been
restored, the events in the binary log that were recorded after the backup was made are re-
executed. These events bring databases up to date from the point of the backup.
There are two types of binary logging:
 Statement-based logging: Events contain SQL statements that produce data changes (inserts,
updates, deletes)
 Row-based logging: Events describe changes to individual rows
Mixed logging uses statement-based logging by default but switches to row-based logging
automatically as necessary.
 3

mysql> show variables like "log_bin";

mysql> show variables like "binlog_format";

SHOW BINARY LOGS

SHOW BINARY LOGS

SHOW MASTER LOGS

A user with the SUPER or REPLICATION CLIENT privilege may execute this statement.

SHOW BINLOG EVENTS Syntax

SHOW BINLOG EVENTS

[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

SHOW BINLOG EVENTS does not work with relay log files. You can use SHOW RELAYLOG EVENTS for
this purpose.

Setting The Binary Log Format

You can select the binary logging format explicitly by starting the MySQL server with --binlog-
format=type. The supported values for type are:
 STATEMENT causes logging to be statement based.
 ROW causes logging to be row based.
 MIXED causes logging to use mixed format.
Prior to MySQL 5.7.7, statement-based logging format was the default. In MySQL 5.7.7 and later, row-
based logging format is the default.

Advantages of statement-based replication

 Proven technology that has existed in MySQL since 3.23.

 Less data written to log files. When updates or deletes affect many rows, this results
in much less storage space required for log files. This also means that taking and restoring from
backups can be accomplished more quickly.
 Log files contain all statements that made any changes, so they can be used to audit the data-
base.

Disadvantages of statement-based replication

 4

 Statements that are unsafe for SBR. Not all statements which modify data (such
as INSERT DELETE, UPDATE, and REPLACE statements) can be replicated using statement-based
replication. Any nondeterministic behavior is difficult to replicate when using statement-based
replication. Examples of such Data Modification Language (DML) statements include the follow-
ing:
 A statement that depends on a UDF or stored program that is nondeterministic, since
the value returned by such a UDF or stored program or depends on factors other than the
parameters supplied to it. (Row-based replication, however, simply replicates the value re-
turned by the UDF or stored program, so its effect on table rows and data is the same on
both the master and slave.)
 DELETE and UPDATE statements that use a LIMIT clause without an ORDER BY are non-
deterministic.
 Deterministic UDFs must be applied on the slaves.

 Statements using any of the following functions cannot be replicated properly using
statement-based replication:

o LOAD_FILE()
o UUID(), UUID_SHORT()
o USER()
o FOUND_ROWS()
o SYSDATE() (unless both the master and the slave are started with the --sysdate-
is-now option)
o GET_LOCK()
o IS_FREE_LOCK()
o IS_USED_LOCK()
o MASTER_POS_WAIT()
o RAND()
o RELEASE_LOCK()
o SLEEP()
o VERSION()
However, all other functions are replicated correctly using statement-based replication, in-
cluding NOW() and so forth.
 Statements that cannot be replicated correctly using statement-based replication are logged
with a warning like the one shown here:

 [Warning] Statement is not safe to log in statement format.

 A similar warning is also issued to the client in such cases. The client can display it using SHOW
WARNINGS.
 INSERT ... SELECT requires a greater number of row-level locks than with row-based replica-
tion.
 UPDATE statements that require a table scan (because no index is used in the WHERE clause)
must lock a greater number of rows than with row-based replication.
 5

 For InnoDB: An INSERT statement that uses AUTO_INCREMENT blocks other nonconflict-
ing INSERT statements.
 For complex statements, the statement must be evaluated and executed on the slave before
the rows are updated or inserted. With row-based replication, the slave only has to modify the
affected rows, not execute the full statement.

 If there is an error in evaluation on the slave, particularly when executing complex statements,
statement-based replication may slowly increase the margin of error across the affected rows
over time.
 Stored functions execute with the same NOW() value as the calling statement. However, this is
not true of stored procedures.
 Deterministic UDFs must be applied on the slaves.

 Table definitions must be (nearly) identical on master and slave

Advantages of row-based replication

 All changes can be replicated. This is the safest form of replication.

Note
Statements that update the information in the mysql database—such as GRANT, REVOKE and the
manipulation of triggers, stored routines (including stored procedures), and views—are all replic-
ated to slaves using statement-based replication.
For statements such as CREATE TABLE ... SELECT, a CREATE statement is generated from the table
definition and replicated using statement-based format, while the row insertions are replicated
using row-based format.
 Fewer row locks are required on the master, which thus achieves higher concurrency, for the
following types of statements:

 INSERT ... SELECT

 INSERT statements with AUTO_INCREMENT
 UPDATE or DELETE statements with WHERE clauses that do not use keys or do not
change most of the examined rows.
 Fewer row locks are required on the slave for any INSERT, UPDATE, or DELETE statement.

Disadvantages of row-based replication

 RBR can generate more data that must be logged. To replicate a DML statement (such as
an UPDATE or DELETE statement), statement-based replication writes only the statement to the
binary log. By contrast, row-based replication writes each changed row to the binary log. If the
statement changes many rows, row-based replication may write significantly more data to the
binary log; this is true even for statements that are rolled back. This also means that making and
restoring a backup can require more time. In addition, the binary log is locked for a longer time to
write the data, which may cause concurrency problems. Use binlog_row_image=minimal to re-
duce the disadvantage considerably.
 6

 Deterministic UDFs that generate large BLOB values take longer to replicate with row-based
replication than with statement-based replication. This is because the BLOB column value is
logged, rather than the statement generating the data.
 You cannot see on the slave what statements were received from the master and executed.
However, you can see what data was changed usingmysqlbinlog with the options --base64-
output=DECODE-ROWS and --verbose.
Alternatively, use the binlog_rows_query_log_events variable, which if enabled adds
a Rows_query event with the statement to mysqlbinlogoutput when the -vv option is used.
 For tables using the MyISAM storage engine, a stronger lock is required on the slave
for INSERT statements when applying them as row-based events to the binary log than when ap-
plying them as statements. This means that concurrent inserts on MyISAM tables are not suppor-
ted when using row-based replication.

The logging format also can be switched at runtime. To specify the format globally for all clients, set
the global value of the binlog_format system variable:
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

mysql> show master status;

# scp mysql-bin.00001 /home/

#mysqladmin -proot -u root flush logs

Take the binary log file backup
# mysqlbinlog mysql-bin.000005 > [Link];
mysqlbinlog mysql-bin.000027 --start-datetime='2013-05-04 4:22:00' --stop-datetime='2013-05-04
4:25:12' > [Link];

Restore the binlog file

# mysqlbinlog mysql-bin.000005 | mysql -proot -u root

To reduce the amount of data retrieved from binary logs, there are several options that can be used to
limit the data that is been returned. Among the useful ones are listed below:
–start-datetime=datetime
Start reading the binary log at the first event having a timestamp equal to or later than the datetime
argument. The datetime value is relative to the local time zone on the machine where you run
mysqlbinlog. The value should be in a format accepted for the DATETIME or TIMESTAMP data types.
For example:
 7

mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000001

–stop-datetime=datetime
Stop reading the binary log at the first event having a timestamp equal or posterior to the datetime
argument. This option is useful for point-in-time recovery. See the description of the –start-datetime
option for information about the datetime value.
–start-position=N
Start reading the binary log at the first event having a position equal to the N argument. This option
applies to the first log file named on the command line.
–stop-position=N
Stop reading the binary log at the first event having a position equal or greater than the N argument.
This option applies to the last log file named on the command line.
mysqlbinlog mysql-bin.000027 --start-position=106 --stop-position=310 > [Link];

mysqlbinlog -proot -uroot mysql-bin.0003 --start-date='2013-10-06 20:07:00' --stop-datetime='2013-

10-06 20:10:00' | mysql -proot -u root

4. The Slow Query Log

The slow query log consists of SQL statements that took more than long_query_time seconds to
execute and (as of MySQL 5.1.21) required at least min_examined_row_limit rows to be examined.
The default value of long_query_time is 10. Beginning with MySQL 5.1.21, the minimum is 0, and the
value can be specified to a resolution of microseconds. For logging to a file, times are written including
the microseconds part. For logging to tables, only integer times are written; the microseconds part is
ignored. Prior to MySQL 5.1.21, the minimum value is 1, and the value for this variable must be an
integer.
Control the slow query log at server startup as follows:


#vi /etc/[Link]
[mysqld]
long_query_time=30
log_slow_queries=[Link]
log-output=file
or
log-output= table
 8

>SET GLOBAL log_queries_not_using_indexes=1

 Before 5.1.6, the slow query log destination is always a file. To enable the log, start mysqld with
the --log-slow-queries[=file_name] option.
 As of MySQL 5.1.6, the destination can be a file or a table, or both. Start mysqld with the --log-
slow-queries[=file_name] option to enable the slow query log, and optionally use --log-output
to specify the log destination (as described in Section 5.2.1, “Selecting General Query and Slow
Query Log Output Destinations”).
 As of MySQL 5.1.12, as an alternative to --log-slow-queries, use --slow_query_log[={0|1}] to
specify the initial slow query log state. In this case, the default slow query log file name is used.
With no argument or an argument of 1, --slow_query_log enables the log. With an argument
of 0, this option disables the log.
 As of MySQL 5.1.29, use --slow_query_log[={0|1}] to enable or disable the slow query log, and
optionally --slow_query_log_file=file_name to specify a log file name. The --log-slow-queries
option is deprecated.
mysql> show variables like "long%";

mysql> show variables like "slow%";

The server uses the controlling parameters in the following order to determine whether to write a
query to the slow query log:
1. The query must either not be an administrative statement, or --log-slow-admin-statements
must have been specified.
2. The query must have taken at least long_query_time seconds, or --log-queries-not-using-
indexes must have been specified and the query used no indexes for row lookups.
3. The query must have examined at least min_examined_row_limit rows.

expire_logs_days

The number of days for automatic binary log file removal. The default is 0, which means “no automatic
removal.” Possible removals happen at startup and when the binary log is flushed.

Purge binary logs;

mysql> show master status;

 9

mysql> show binary logs;

mysql> purge binary logs to 'mysql-bin.000006';
mysql> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'

DDL Log

The DDL log, or metadata log, records metadata operations generated by data definition statements
such as DROP TABLE and ALTER TABLE. MySQL uses this log to recover from crashes occurring in the
middle of a metadata operation. When executing the statement DROP TABLE t1, t2, we need to en-
sure that both t1 and t2 are dropped, and that each table drop is complete. Another example of this
type of SQL statement is ALTER TABLE t3 DROP PARTITION p2, where we must make certain that the
partition is completely dropped and that its definition is removed from the list of partitions for tab-
let3.
A record of metadata operations such as those just described are written to the file ddl_log.log, in the
MySQL data directory. This is a binary file; it is not intended to be human-readable, and you should
not attempt to modify it in any way.
ddl_log.log is not created until it is actually needed for recording metadata statements, so it is pos-
sible for this file not to be present on a MySQL server that is functioning in a completely normal man-
ner.
There are no user-configurable server options or variables associated with this file.