MySQL has a lot of different log files which can help you find
out what's going on inside mysqld:
| The error log | Problems encountering starting, running or stopping mysqld. |
| The isam log | Logs all changes to the ISAM tables. Used only for debugging the isam code. |
| The query log | Established connections and executed queries. |
| The update log | Depricated: Stores all statements that changes data |
| The binary log | Stores all statements that changes something. Used also for replication |
| The slow log | Stores all queries that took more than long_query_time to execute or didn't use indexes.
|
All logs can be found in the mysqld database directory. You can
tell force mysqld to switch to reopen the log files (or in some cases
switch to a new log) by executing FLUSH LOGS. 「7.26 FLUSH 構文 (キャッシュのクリア)」節参照.
mysqld writes all errors to the stderr, which the
safe_mysqld script redirects to a file called
'hostname'.err. (On windows mysqld writes this directly
to `mysql.err').
This contains information on when mysqld was started and stopped and
also any critical errors found when running. If mysqld dies
unexpectedly and safe_mysqld needs to restart mysqld,
safe_mysqld will write a restarted mysqld row in this
file. This log also holds a warning if mysqld notices a table
that 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. 「I.1.3 Using a stack trace」節参照.
If you want to know what happens within mysqld, you should start
it with --log[=file]. This will log all connections and queries
to the log file (by default named 'hostname.log'). This log can
be very useful when you suspect an error in a client and want to know
exactly what mysqld thought the client send to it.
By default, the mysql.server script starts the MySQL
server with the -l option. If you need better performance when
you start using MySQL in a production environment, you can
remove the -l option from mysql.server or change it to
--log-binary.
The entries in this log are written as mysqld receives the questions.
This may be different than the order in which the statements are executed.
This is in contrast to the update log and the binary log which is written
after the query is executed, but before any locks are released.
NOTE: The update log is replaced by the binary log. 「23.4 The Binary Log」節参照. With this you can do anything that you can do with the update log.
--log-update=file_nameオプションを使用してmysqldを起動すると、
データを更新した全てのSQLコマンドをログファイルに書きます。
もし file_name が与えられなかった場合は、ホスト名がデフォルトで使用されます。
もし file_name にディレクトリパスが含まれなかった場合は、
このファイルはデータディレクトリに書かれます。
もし file_name が拡張を持たなかったなら、mysqld は
file_name.### という形でログファイルを作成します。
このログファイルはデータディレクトリにかかれ、
その名前は file_name.### という形式の名前になっています。
### は mysqladmin refresh か mysqladmin flush-logs を実行する度に、
あるいは FLUSH LOGS 構文を実行したり、サーバーをリスタートする度に
自動的に増える数字です。
もし --log か -l オプションを使用した場合、ログファイルの名前は
mysqld は全部のログを `hostname.log' に書き出します。
この場合、リスタートやりフレッシュを行っても新しくログファイルを作りません。
(一度クローズして再度オープンします。)
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
更新ログは、実際に更新されたデータのステートメントだけを書くため、きびきびと動作します。
WHERE を使用した UPDATE か DELETE で結果が得られなかった場合、
ログは書き出されません。
すでにセットされている値に、もう一度項目を更新するような UPDATE もスキップされます。
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.
ログファイルの更新に従ってデータベースを更新したい場合は、次を行います (更新ログファイル名を `file_name.###' と仮定します):
shell> ls -1 -t -r file_name.[0-9]* | xargs cat | mysql
ls は全てのログファイルを正しく並べるために使用しています。
これは、クラッシュ後にバックアップした状態まで戻りたい、あるいは、 バックアップした後からクラッシュするまでの間のデータの更新を行いたい、 そんな場合に使えます。
In the future the binary log will replace the update log, so we recommend you to switch to this log format as soon as possible!
The binary log contains all information that is available in the update log in a more efficient format. It also contains information about how long every query that updated the database took.
The binary log is also used when you are replicating a slave from a master. 「11 Replication in MySQL」節参照.
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.
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.
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.
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 examine the binary log file with the mysqlbinlog command.
For example, you can update a MySQL server from the binary log
as follows:
mysqlbinlog log-file | mysql -h server_name
You can also use the mysqlbinlog program to read the binary log
directly from a remote MySQL server!
mysqlbinlog --help will give you more information of how to use
this program!
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.
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.
All updates (UPDATE, DELETE or INSERT) that change
a transactional table (like BDB tables) are cached until a COMMIT.
Any updates to a non-transactional table are stored in the binary log at
once. 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 handle the bigger cache.
The temporary file will be deleted when the thread ends.
The max_binlog_cache_size can be used to restrict the total size used
to cache a multi-transaction query.
If you are using the update or binary log, concurrent inserts will
not work together with CREATE ... INSERT and INSERT ... SELECT.
This is to ensure that you can recreate an exact copy of your tables by
applying the log on a backup.
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 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 than 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 optimization.
You are using --log-long-format then also queries that are not
using indexes are printed. 「4.16.4 mysqld コマンド行オプション」節参照.
ログは違うホストのデータベースのミラーやマスターデータベースの複製を作る場合にも利用できます。 「11 Replication in MySQL」節参照.
Go to the first, previous, next, last section, table of contents.