MySQL has several different log files that 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 | Deprecated: 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 data directory. You can
force mysqld to reopen the log files (or in some cases
switch to a new log) by executing FLUSH LOGS. See section 7.26 FLUSH Syntax.
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\data\mysql.err').
This contains information indicating 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. See section I.1.4 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 sent 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 are written
after the query is executed, but before any locks are released.
NOTE: The update log is replaced by the binary log. See section 23.4 The Binary Log. With this you can do anything that you can do with the update log.
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 should 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.
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. See section 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.
You can use the following options to mysqld to affect what is logged
to the binary log:
binlog-do-db=database_name |
Tells the master it should log updates for the specified database, and
exclude all others not explicitly mentioned.
(Example: binlog-do-db=some_database)
|
binlog-ignore-db=database_name |
Tells the master that updates to the given database should not be logged
to the binary log (Example: binlog-ignore-db=some_database)
|
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. See section 4.16.4 mysqld Command-line Options.
You can also use the update logs when you have a mirrored database on another host and you want to replicate the changes that have been made to the master database. See section 22.1 Database Replication with Update Log.
Go to the first, previous, next, last section, table of contents.