Tài liệu MySQL Administrator''''s Bible- P12 doc

Logging and Replication

IN THIS CHAPTER

Logging in MySQL

Configuring replication

Replication topologies

Correcting data

When changes are made to the database mysqld writes to

binary logs, which are used in the replication process. Much

of this chapter concerns configuration options that are used

to manage both logging and replication. These options can be configured

from server startup using the command line or set in the configuration file.

Unless the material specifically says that an option can only be configured

from the command line or the configuration file, either method is possible.

In addition, many of the configuration options can be controlled at server

runtime.

Log Files

Four log files are used by mysqld: the error log, the binary log, the gen￾eral query log, and the slow query log. A fifth log type, the relay log, is

used by a slave server. If the logs are enabled, mysqld writes them to the

data directory unless otherwise specified. By default, no logging is enabled.

If you find that an error log is being written to when

the configuration file does not enable the error log,

there is no need to worry. Some startup scripts specify error log files. These

startup scripts are dependent upon the installation.

Error log

The error log contains entries for when the mysqld daemon started and

stopped and also any critical errors that occur while the server is running.

517

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

Information such as when the event scheduler executes an event and when replication starts and

stops is also written to the error log. On some operating systems the error log additionally will

write a stack trace if mysqld dies. This stack trace can be very useful for troubleshooting system

failures. The error log is a plain text file.

To enable the error log, specify the log-error option. The error log will be written to the data

directory using the format host_name.err. To customize the filename, give the log-error

option a filename, such as:

log-error=/var/log/mysqld.err

The log_warnings option is used to control whether or not warning messages are logged to

the error log. The default value is 1 (enabled). If the value is greater than 1, aborted connections

are written to the error log. Warning logging can be disabled using a value of 0.

If you do not specify log-error, or if you use the --console option on Windows, errors are

written to stderr, the standard error output. Even though you are not required to have an

error log, it is extremely useful to help troubleshoot issues.

Binary logs

The binary logs are used for several reasons. They can be used to perform a point-in-time recov￾ery during a recovery process. How this is done is detailed in Chapter 13. Another function of

the binary log is to enable replication. This is covered later in this chapter.

The contents of the binary log are any statements that occur in the server that could potentially

modify the databases. Non-modifying statements such as SELECT are not logged. However, a

non-modifying statement will be logged if it is part of a transaction that modifies data, because

the entire transaction will be logged.

To enable binary logging, use the log-bin option. The binary log index file is a plain text file

that keeps track of the current binary logs. By default, its name is mysql-bin.index. To set

the filename and path of the binary logs and binary log index file, specify the following options:

log-bin = /data/logs/binary/changelog

log-bin-index = /data/logs/relay/binarylog.index

Binary log data is stored in a binary format. This means you can not just open the file with

a text editor and read it. To display the binary logs in text/readable format you must use the

mysqlbinlog tool. The operation of the mysqlbinlog tool is pretty straightforward.

In the following example the entire contents of the mysql-bin.00001 binary log are converted

into text format and copied into a new file called output.sql:

shell> mysqlbinlog mysql-bin.00001 > output.sql

518

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Logging and Replication 16

If you leave off the > output.sql the contents will be sent to the console.

Numerous options can be used with mysqlbinlog. Table 16-1 lists the frequently used mysql￾binlog options. The standard options for command-line programs that interact with mysqld,

such as -u, -h, -p, and -P, are not listed

TABLE 16-1

Frequently used mysqlbinlog Options

Option Description

read-from￾remote-server

Used to read the binary log from a remote server.

start-position Determines the position in the binary log where mysqlbinlog

begins reading data.

end-position Determines the position in the binary log where mysqlbinlog

stops reading data.

start-datetime Begins reading from the binary log at the first event with a

timestamp equal to or greater than the specified datetime.

end-datetime Stops reading from the binary log at the first event with a

timestamp equal to or greater than the specified datetime.

The following is a more complicated example, reading from a remote server:

shell> mysqlbinlog --read-from-remote-server -uuser -p \

mysql-bin.000001 -h 192.168.2.65 -P 3306 \

--start-position=932 --stop-position=1132 > remote000001.sql

Here is an example showing how to use the time-based parameters:

shell> mysqlbinlog mysql-bin.000001 \

--start-datetime="2009-02-15 17:34:40" \

--stop-datetime="2009-02-15 17:34:56" > sixteensecs.sql

This is a good way to extract a smaller log if you know approximately what time an event hap￾pened.

mysqlbinlog does not change the original binary log.

519

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

Relay logs

Relay logs are used by a replication slave to record events received from the master server

before execution on the slave server. They utilize the same binary format as the binary logs

and, like the binary logs, can be viewed using the mysqlbinlog tool. By default a slave server

stores the relay logs in datadir. In addition to the relay logs there is the relay-log.index

file, which is used to keep track of the currently used relay log. Also, the relay-log.info

file documents the currently used relay log file and position, plus the position in the master

binary log.

If you want to change the location for the relay logs, there are three configuration values you

will want to add to the configuration file. These values are relay-log, relay-log-index, and

relay-log-info. If you wanted to store all these logs in the directory /data/logs/relay

you would need to add this to your MySQL configuration file:

relay-log = /data/logs/relay/relay-bin

relay-log-index = /data/logs/relay/relay-bin.index

relay-log-info-file = /data/logs/relay/relay-bin.info

As you can see, you can specify filenames in addition to directories. Relay logs are automatically

deleted by the SQL thread of the slave server when it has executed all events in the log and it is

no longer needed.

General and slow query logs

The general query and slow query log files are used to log the activities occurring in the

database. If the general query log is enabled it logs all activity on the server. In addition to the

actual SQL statements being executed it logs information such as when clients both connect and

disconnect. It can be a very useful tool when troubleshooting issues.

The general log writes statements as they are sent to mysqld, including ones that result

in errors.

It is not advisable to turn on the general query log on a production machine unless

it is really necessary. Because it logs all server activity it can be quite a detriment to

performance. The general query log can be turned on and off while the server is still running, so if

a general log is needed for debugging purposes it can be enabled for a short period of time.

Beginning in MySQL Server 5.1 general and slow queries can be logged to either a log file or a

table in the mysql database. The general queries are logged to the general_log table and the

slow queries are logged to the slow_log table.

Why would you want to log this information to a database table? If all log statements are

stored in a table rather than in a text file, you can use a simple SELECT statement to query the

data — speeding up the information search of your logs.

520

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Logging and Replication 16

To set the location of both the slow and general query log output, set the server variable

log_output. Possible values for log_output are:

■ FILE

■ TABLE

■ NONE

■ FILE,TABLE

The default value is FILE.

The log_output server variable is an example of a dynamic variable. That means

that it can be set while the server is running. As an example, to set log_output to

NONE (turning off both general and slow query logs), execute the following using mysql:

mysql> SET GLOBAL log_output=NONE;

Table 16-2 lists the server variables used to manage the general and slow query logs.

TABLE 16-2

Server Variables Used to Manage General and Slow Query Logs

Server Variable Description

log_output= [ NONE |

FILE | TABLE |

FILE,TABLE]

Determines where both general query and slow query logs are

stored.

general_log= [ON|OFF] Used to turn on or off the general query log. Possible values

are ON and OFF.

general_log_file

[=filename]

Specifies the location of the general query log file. The default

location is the data directory.

slow_query_log Possible values are ON and OFF.

slow_query_log_file

[=filename]

Specifies the location of the slow query log file. The default

location is the data directory.

long_query_time=num Queries that take longer than num seconds are logged in the

slow query log. Defaults to 10.

log-queries-not￾using-indexes

Queries that do a full table scan will be logged.

min_examined_row_

limits=num

Only logs queries in slow query log that examine at least num

specified rows. Defaults to 0. This option is usually used in

conjunction with log-queries-not-using-indexes, to

log queries on large tables that do not use indexes.

521

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.