The MySQL Log Files
===================
MySQL has several different log files that can help you find out what's
going on inside `mysqld':
debugging the isam code.
The query log Established connections and executed queries.
The update Deprecated: Stores all statements that changes data
log
The binary Stores all statements that changes something. Used also
log for replication
The slow log Stores all queries that took more than `long_query_time'
seconds to execute or didn't use indexes.
All logs can be found in the `mysqld' data directory. You can force
`mysqld' to reopen the log files (or in some cases switch to a new log)
by executing `FLUSH LOGS'. *Note FLUSH::.
The Error Log
-------------
The error log file contains information indicating when `mysqld' was
started and stopped and also any critical errors found when running.
needs to be automatically checked or repaired.
On some operating systems, the error log will contain a stack trace for
where `mysqld' died. This can be used to find out where `mysqld' died.
*Note Using stack trace::.
Beginning with MySQL 4.0.10 you can specify where `mysqld' stores the
error log file with the option `--log-error[=filename]'. If no file
name is given `mysqld' will use `mysql-data-dir/'hostname'.err' on Unix
and `\mysql\data\mysql.err' on Windows. If you execute `flush logs'
the old file will be prefixed with `--old' and `mysqld' will create a
new empty log file.
In older MySQL versions the error log handling was done by
`mysqld_safe' which redirected the error file to `'hostname'.err'. One
could change this file name with the option `--err-log=filename'.
If you don't specify `--log-error' or if you use the `--console' option
the errors will be written to stderr (the terminal).
On Windows, the output is always written to the `.err' file if
`--console' is not given.
The General Query Log
---------------------
be very
useful when you suspect an error in a client and want to know exactly
what `mysqld' thought the client sent to it.
a
production environment, you can remove the `--log' option from
`mysql.server' or change it to `--log-bin'. *Note Binary log::.
log
which are written after the query is executed, but before any locks are
released.
The Update Log
--------------
*Note*: the update log is replaced by the binary log. *Note Binary
log::. With this you can do anything that you can do with the update
log. *The update log will be removed in MySQL 5.0*.
When started with the `--log-update[=file_name]' option, `mysqld'
writes a log file containing all SQL commands that update data. If no
filename is given, it defaults to the name of the host machine. If a
filename is given, but it doesn't contain a path, the file is written
in the data directory. If `file_name' doesn't have an extension,
`mysqld' will create log file names like so: `file_name.###', where
`###' is a number that is incremented each time you execute `mysqladmin
refresh', execute `mysqladmin flush-logs', execute the `FLUSH LOGS'
statement, or restart the server.
*Note*: for the above scheme to work, you must not create your own
files with the same filename as the update log + some extensions that
may be regarded as a number, in the directory used by the update log!
If you use the `--log' or `-l' options, `mysqld' writes a general log
with a filename of `hostname.log', and restarts and refreshes do not
cause a new log file to be generated (although it is closed and
reopened). In this case you can copy it (on Unix) by doing:
mv hostname.log hostname-old.log
mysqladmin flush-logs
cp hostname-old.log to-backup-directory
rm hostname-old.log
Update logging is smart because it logs only statements that really
update data. So an `UPDATE' or a `DELETE' with a `WHERE' that finds no
rows is not written to the log. It even skips `UPDATE' statements that
set a column to the value it already has.
The update logging is done immediately after a query completes but
before any locks are released or any commit is done. This ensures that
the log will be logged in the execution order.
If you want to update a database from update log files, you could do the
following (assuming your update logs have names of the form
`file_name.###'):
shell> ls -1 -t -r file_name.[0-9]* | xargs cat | mysql
`ls' is used to get all the log files in the right order.
This can be useful if you have to revert to backup files after a crash
and you want to redo the updates that occurred between the time of the
backup and the crash.
The Binary Log
--------------
The intention is that the binary log should replace the update log, so
we recommend you to switch to this log format as soon as possible! The
update log will be removed in MySQL 5.0.
contain
queries that don't modify any data. If you want to log all queries
(for example to find a problem query) you should use the general query
log. *Note Query log::.
The binary log is also used when you are replicating a slave from a
master. *Note Replication::.
When started with the `--log-bin[=file_name]' option, `mysqld' writes a
log file containing all SQL commands that update data. If no file name
is given, it defaults to the name of the host machine followed by
`-bin'. If file name is given, but it doesn't contain a path, the file
is written in the data directory.
If you supply an extension to `--log-bin=filename.extension', the
extension will be silenty removed.
To the binary log filename `mysqld' will append an extension that is a
number that is incremented each time you execute `mysqladmin refresh',
execute `mysqladmin flush-logs', execute the `FLUSH LOGS' statement or
restart the server. A new binary log will also automatically be created
when the current one's size reaches `max_binlog_size'. Note if you are
using transactions: a transaction is written in one chunk to the binary
log, hence it is never split between several binary logs. Therefore, if
you have big transactions, you may see binlogs bigger than
`max_binlog_size'.
You can delete all binary log files with the `RESET MASTER' command
(*note `RESET': RESET.), or only some of them with `PURGE MASTER LOGS'
(*note Replication Master SQL::).
You can use the following options to `mysqld' to affect what is logged
to the binary log (please make sure to read the notes which follow this
table):
*Option* *Description*
`binlog-do-db=database_name' Tells the master that it should log updates
to the binary log if the current database
(that is, the one selected by `USE')
database is 'database_name'. All others
databases which are not explicitly mentioned
are ignored. Note that if you use this you
should ensure that you only do updates in
the current database. (Example:
`binlog-do-db=some_database')
Example of what does not work as you could
expect it: if the server is started with
`binlog-do-db=sales', and you do `USE
prices; UPDATE sales.january SET
amount=amount+1000;', this query will not be
written into the binary log.
`binlog-ignore-db=database_name' Tells the master that updates where the
current database (that is, the one selected
by `USE') is 'database_name' should not be
stored in the binary log. Note that if you
use this you should ensure that you only do
updates in the current database. (Example:
`binlog-ignore-db=some_database')
Example of what does not work as you could
expect it: if the server is started with
`binlog-ignore-db=sales', and you do `USE
prices; UPDATE sales.january SET
amount=amount+1000;', this query will be
written into the binary log.
The rules are evaluated in the following order, to decide if the query
should be written to the binary log or not:
1. Are there `binlog-do-db' or `binlog-ignore-db' rules?
* No: write the query to the binlog and exit.
* Yes: go to step below.
2. So there are some rules (`binlog-do-db' or `binlog-ignore-db' or
both). Is there a current database (has any database been selected
by `USE'?)?
* No: do *NOT* write the query, and exit.
* Yes: go to step below.
3. There is a current database. Are there some `binlog-do-db' rules?
* Yes: Does the current database match any of the `binlog-do-db'
rules?
* Yes: write the query and exit.
* No: do *NOT* write the query, and exit.
* No: go to step below.
4. There are some `binlog-ignore-db' rules. Does the current
database match any of the `binlog-ignore-db' rules?
* Yes: do not write the query, and exit.
* No: write the query and exit.
"ignore
other databases").
To be able to know which different binary log files have been used,
`mysqld' will also create a binary log index file that contains the
name of all used binary log files. By default this has the same name as
the binary log file, with the extension `'.index''. You can change the
name of the binary log index file with the `--log-bin-index=[filename]'
option. You should not manually edit this file while `mysqld' is
running; doing this would confuse `mysqld'.
If you are using replication, you should not delete old binary log
files until you are sure that no slave will ever need to use them. One
way to do this is to do `mysqladmin flush-logs' once a day and then
remove any logs that are more than 3 days old. You can remove them
manually, or preferably using `PURGE MASTER LOGS' (*note Replication
Master SQL::) which will also safely update the binary log index file
for you (and which can take a date argument since MySQL 4.1)
A connection with the `SUPER' privilege can disable the binary logging
of its queries using `SET SQL_LOG_BIN=0'. *Note Replication Master
SQL::.
You can examine the binary log file with the `mysqlbinlog' utility.
For example, you can update a MySQL server from the binary log as
follows:
shell> mysqlbinlog log-file | mysql -h server_name
See *Note mysqlbinlog:: for more information on the `mysqlbinlog'
utility and how to use it.
If you are using `BEGIN [WORK]' or `SET AUTOCOMMIT=0', you must use the
MySQL binary log for backups instead of the old update log, which will
be removed in MySQL 5.0.
The binary logging is done immediately after a query completes but
before any locks are released or any commit is done. This ensures that
the log will be logged in the execution order.
that
change tables are cached until a `COMMIT' command is sent to the
server. At this point `mysqld' writes the whole transaction to the
binary log before the `COMMIT' is executed. Every thread will, on
start, allocate a buffer of `binlog_cache_size' to buffer queries. If
a query is bigger than this, the thread will open a temporary file to
store the transaction. The temporary file will be deleted when the
thread ends.
The `max_binlog_cache_size' (default 4G) can be used to restrict the
total size used to cache a multi-query transaction. If a transaction is
bigger than this it will fail and roll back.
If you are using the update or binary log, concurrent inserts will be
converted to normal inserts when using `CREATE ... SELECT' or `INSERT
... SELECT'. This is to ensure that you can recreate an exact copy of
your tables by applying the log on a backup.
The Slow Query Log
------------------
When started with the `--log-slow-queries[=file_name]' option, `mysqld'
writes a log file containing all SQL commands that took more than
`long_query_time' seconds to execute. The time to get the initial table
locks are not counted as execution time.
The slow query log is logged after the query is executed and after all
locks has been released. This may be different from the order in which
the statements are executed.
If no file name is given, it defaults to the name of the host machine
suffixed with `-slow.log'. If a filename is given, but doesn't contain
a path, the file is written in the data directory.
The slow query log can be used to find queries that take a long time to
execute and are thus candidates for optimisation. With a large log, that
can become a difficult task. You can pipe the slow query log through the
`mysqldumpslow' command to get a summary of the queries which appear in
the log.
You are using `--log-long-format' then also queries that are not using
indexes are printed. *Note Command-line options::.
Log File Maintenance
--------------------
The MySQL Server can create a number of different log files, which make
it easy to see what is going on. *Note Log Files::. However, you must
clean up these files regularly, to ensure that the logs don't take up
too much disk space.
When using MySQL with log files, you will want to remove/backup old log
files from time to time and tell MySQL to start logging to new files.
*Note Backup::.
On a Linux (`Red Hat') installation, you can use the `mysql-log-rotate'
script for this. If you installed MySQL from an RPM distribution, the
script should have been installed automatically. Note that you should
be careful with this script if you are using the binary log for
replication!
On other systems you must install a short script yourself that you
start from `cron' to handle log files.
You can force MySQL to start using new log files by using `mysqladmin
flush-logs' or by using the SQL command `FLUSH LOGS'. If you are using
MySQL Version 3.21, you must use `mysqladmin refresh'.
The above command does the following:
* If standard logging (`--log') or slow query logging
(`--log-slow-queries') is used, closes and reopens the log file
(`mysql.log' and ``hostname`-slow.log' as default).
* If update logging (`--log-update') is used, closes the update log
and opens a new log file with a higher sequence number.
shell> cd mysql-data-directory
shell> mv mysql.log mysql.old
shell> mysqladmin flush-logs
and then take a backup and remove `mysql.old'.
[Назад] [Содержание] [Вперед]
| Главная |