mysql的日志文件
日志文件大致分为 error log, binary log, query log, slow query log, innodb redo log ;如图:
1.error log
the error log file contains information indicating when mysqld was started and stopped also any critical
errors that occur while the server is running. IF mysqld notices a table that needs automatically checked
or repaired,it writes a message to the error log you can specify where mysqld stores the error log file with --log-error[=file_name] option.iF no file_name
value is given,mysqld uses the name host_name.err and writes the file in the data directory config variable "log_err" in my.cnf
linux 下设置log_error
mysqld_safe --user=mysql --log-error=/tmp/mysql.errmysql> show variables like '%err%';
+--------------------+---------------+
| Variable_name | Value |
+--------------------+---------------+
| error_count | 0 |
| log_error | .\DSGTEST.err |
| max_connect_errors | 100 |
| max_error_count | 64 |
| slave_skip_errors | OFF |
+--------------------+---------------+
5 rows in set (0.01 sec)
上面载自官方文档, 它已经描述的很清楚了, log-error 记录了 mysql 启动或关闭时的标志性信息,和运行时的关键错误,如果有表需要检查或者修复,它同样会写入日志.
2.binary log
the binary log contains all statements that update data or potentially could have updated it (for example,
a delete which matched no rows).statements are stored in the form of "events"that describe the
modifications.the binary log also contains information about how long each statement took that updadte data.
the binary log does not contain statements that do not modify any data.if you want to log all statements (for example,to identify a problem query),
use the general query log
the primary purpose of the binary log is to be able to update database during a restore operation as fully as possiable,
because the binary log contains all updates done after a backup was made.the binary log is also used on master replication server as record of
the statements to be sent to slave servers
running the server with the binary log enables makes performance abou 1% slower.hower,the benefits of the binary log for restore operations and in allowing you to
set up replication generally outweigh this minor performance decrementbinary log 相关变量和参数
命令行参数:--log-bin [=file_name]
--log-bin-index [=file] 记录生产的log-bin的文件名称
--max_binlog_size
--binlog-do-db=db_name
--binlog-ignore-db=db_name系统变量:
log-binbinlog_cache_size 为每个连接的线程分配这么多日志存放内存
max_binlog_cache_size
max_binlog_size binlog日志文件的最大值,mysql 一个事物不能写到2个日志文件
binlog_cache_use 当前缓存的事物 show status like '%binlog_cache_use%';
binlog_cache_disk_use 磁盘缓存
binlog_do_db 日志只记录指定数据库
binlog_ignore_db 日志忽略指定的数据库
sync_binlog 设置提交跟记录的频率 默认commit 一次往磁盘写一次日志，0代表系统自己维护
This makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction系统变量
mysql> show variables like '%bin%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sql_log_bin | ON |
| sync_binlog | 0 |
+-----------------------------------------+----------------------+--实验部分
mysqld_safe --user=mysql --log-bin/tmp/1.0000000000001 --log-bin-index=/tmp/logbin.index --max-binlog-size=10m --binlog-do-db=test &查看日志
mysql> show binary logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| dsgtest-bin. | |
| dsgtest-bin. | |
+--------------------+-----------+
rows in set (0.00 sec)
mysql> select @@max_binlog_size//;
+-----------------------------+
| @@max_binlog_size// |
+-----------------------------+
| 10.00000000 |
+-----------------------------+
row in set (0.00 sec) mysql> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| dsgtest-bin. | |
| dsgtest-bin. | |
+--------------------+-----------+
rows in set (0.00 sec)日志切换
flush logs;日志初始化 (删除所有日志，从0开始)
reset master;删除部分日志
purge binary logs to 'mysql-bin.010';
purge binary logs before '2008-04-02 22:46:26';
mysql> purge binary logs to 'dsgtest-bin.000002';
Query OK, 0 rows affected (0.03 sec)
mysql> show binary log;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'log'
at line 1
mysql> show binary logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| dsgtest-bin.000002 | 428 |
| dsgtest-bin.000003 | 169 |
| dsgtest-bin.000004 | 120 |
+--------------------+-----------+
3 rows in set (0.00 sec)指定日志失效期
show variable like '%expir%';
set global expire_log_days=5;
mysql> show variables like '%expir%logs%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
mysql> set global expire_logs_days=5;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%expir%logs%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 5 |
+------------------+-------+
1 row in set (0.00 sec)binary log的格式
binlog_forma:statement,row,mixed
statement 以语句的形式
row dml 以2进制形式 ddl 以语句的形式
mixed 自适应模式set session binlog_format='statement';
set session binlog_format='row';
set session binlog_format='mixed'; set global binlog_format='statement';
set global binlog_format='row';
set global binlog_format='mixed';相关变量
mysql> show variables like 'binlog%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | ROW |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
+-----------------------------------------+-------+
mysqlbinarylog 工具(以文本形式读取binlog日志文件)
the binary log files that the server generates are written in binary format.To examine these files in
text format,use the mysqlbinlog utility,you can also use mysqlbinlog to read relay log files written by
a slave server in a replcaition setup.Relay logs have the same format us binary log files;
the output from mysqlbinlog can be re-executed (for example,by using it as input to mysql) to
reapply the statements in the log,this is useful for recovery operations after a server crash,forsync_binlog 实验
create procedure test ()
begin
declare i int;
set i=0;
while i<5000 do
insert into t values(i);
set i=i+1;
commit;
end while;
end;
调用 call test;
set global sync_binlog=0 数据库自己维护
sql> call test;
ery OK, 0 rows affected (2 min 12.65 sec)
mysql> call test;
Query OK, 0 rows affected (4 min 32.38 sec)
sset global ync_binlog=1 commit 一次往磁盘刷新一次
mysql> set global sync_binlog=100;
Query OK, 0 rows affected (0.00 sec)
mysql> call test;
Query OK, 0 rows affected (2 min 8.75 sec)
3.query log
--通用查询日志
The general query log is a general record what mysqld is doing.The server writest information to this log
when clients connect or disconnect ,and it logs each sql statement received from clients.The general query log can be very useful
when you suspect an error in a client and want to know exactly what the client sent to mysqld.mysqld writes statements to query log in the order that it receives them.this may be different from the order
in which they are exected.this is in contrast to the binary log,for which statemens are written whereas the
binary log does not contain statements that ong select data)to enable general query log as of mysql 5.1.6,start mysqld with the --log option,and optionally use
--log-output to specify the log output destination as described in section 5.11.1"server log tables".before 5.1.6,
enable the general query log file with the --log[=file_name] or -[file_name]option.if no file_name
value is given,the default name is host_name.log in the data directory.server restarts and log flushing do not cause a new general query log file to be generated(
although flushing closes and reopens it).on UNIX,you can rename the file and create a new buy using following command;mv host_name.log host_name-old.log
mysqladmin flush-logs
cp host_name-old.log backup-dir
rm host_name-old.log实验(开启日志):
general-log=
general_log_file="DSGTEST.log"
4. slow query log
--慢查询日志
相关变量:
mysql> show variables like '%quer%';
+------------------------------+------------------+
| Variable_name | Value |
+------------------------------+------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 | // 查询时间设置
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON | // 是否打开
| slow_query_log_file | DSGTEST-slow.log | // 文件位置
+------------------------------+------------------+log_queries_not_using_indexes | OFF 没有使用索引的sql
查看是否含义Index
show index from t;mysqldumpslow -s t -t 2 /tmp/mysqlshlow.log1
-s 按时间排序
-t 前2个sql
5. innodb redo log
用来实现灾难恢复(crash recovery)突然断电导致innodb 表空间(table space)中的数据没有被更新到磁盘上。
通过执行redo log能够重新执行这些操作来恢复数据
提升innodb的i/o性能. Innodb引擎把数据和索引都载入到内存中的缓冲池(buffer pool)中。如果每次
修改数据和索引都需要更新到磁盘，必定会大大增加i/o请求,而且因为每次更新的位置都是随机的，
磁头需要频繁定位导致效率低的,所以Innodb 每处理完一个请求(transaction)后只添加
一条日志log,另外有一个线程负责智能地读取日志文件并批量更新到磁盘上，实现最高效的磁盘写入系统变量：
innodb_log_buffer_size 日志缓冲区大小
innodb_log_file_size 日志文件大小
innodb_log_files_in_group 日志组包含的日志数
innodb_log_group_home_dir 日志目录
innodb_flush_log_at_trx_commit
innodb_os_log_written 多少日志写入到日志文件，查看日志生成的频率
innodb_os_log_fsyncs 往磁盘上写日志的次数innodb_flush_log_at_trx_commit
0 日志缓存每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事物提交不做任何操作
1 在每个事物提交时,日志缓冲被写到日志文件，对日志文件做的磁盘操作的刷新(默认值)
2 在每个提交，日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新.对日志文件每秒刷新一次如果要修改innodb log组数
首先修改my.cnf
增加innodb_log_files_in_group=3
然后干净的关闭mysql
mv 走原来的日志文件
启动mysql 自动生产innodb日志文件
修改日志文件的目录
cp 日志文件到新目录
修改my.cnf
innodb_log_group_home_dir =nullinnodb_data_file_path = ibdata1:12M:autoextend; ibdata2:12M:autoextend
注意:innodb_file_per_table=1 是打开独立表空间
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
更多内容来自 http://blog.itpub.net/24577884/viewspace-1061313/