MySQL has a lot of different log files which can help you find
out what's going on inside
|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 |
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. See section 7.26
FLUSH Syntax (Clearing Caches).
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
This contains information on when mysqld was started and stopped and
also any critical errors found when running. If
safe_mysqld needs to restart
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 automaticly 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.3 Using a stack trace.
If you want to know what happens within
mysqld, you should start
--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
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
-l option from
mysql.server or change it to
The entries in this log are written as
mysqld receives the questions.
This may be different than the order in which the statements is 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. See section 22.4 The Binary Log. With this you can do anything that you can do with the update log.
When started with the
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
mysqld will create log file names of type
# is a number that is incremented each
time you execute
mysqladmin refresh, execute
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
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
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
-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 flush-logs, execute the
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
You can change the name of the binary log index file with the
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
For example, you can update a MySQL server from the binary log
mysqlbinlog log-file | mysql -h server_name
You can also use the `mysqlbinlog' program to read the binary log direct from a remote mysql server!
mysqlbinlog --help will give you more information of how to use
If you are using
SET AUTO_COMMIT=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 (
INSERT) that changes
a transactional table (like BDB tables) is cached until a
Any updates to a not transactional table is 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.
max_binlog_cache_size can be used to restrict the total size used
to cache a multi-transaction query.
When started with the
mysqld writes a log file containing all SQL commands that took
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 is executed.
If no file name is given, it defaults to the name of the host machine
-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 takes 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 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 21.1 Database Replication with Update Log.
Go to the first, previous, next, last section, table of contents.