mysql的物理结构
--日志文件,数据文件,replication 相关文件和其他小文件(pid,socket,my.cnf等)
1.日志文件
error log
binary log
query log
slow query log
innodb redo log
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.err
mysql> 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)
--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 decrement
binary 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-bin
binlog_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.000001 | 666 |
| dsgtest-bin.000002 | 379 |
+--------------------+-----------+
2 rows in set (0.00 sec)
mysql> select @@max_binlog_size/1024/1024;
+-----------------------------+
| @@max_binlog_size/1024/1024 |
+-----------------------------+
| 10.00000000 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| dsgtest-bin.000001 | 666 |
| dsgtest-bin.000002 | 379 |
+--------------------+-----------+
2 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 以语句的形式
m
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';
C:\mysql\MySQL Server 5.6\data>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.14-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set session binlog_format='row';
Query OK, 0 rows affected (0.00 sec)
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 |
+-----------------------------------------+-------+
9 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> create table test3 select * from test1;
ERROR 1146 (42S02): Table 'test.test1' doesn't exist
mysql> create table test3 select * from test;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0 Warnings: 0
C:\mysql\MySQL Server 5.6\data>mysqlbinlog dsgtest-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#131127 16:30:20 server id 1 end_log_pos 120 CRC32 0x7869b700 Start: binlog v
4, server v 5.6.14-log created 131127 16:30:20 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
nK2VUg8BAAAAdAAAAHgAAAABAAQANS42LjE0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACcrZVSEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQC3
aXg=
'/*!*/;
# at 120
#131127 16:40:59 server id 1 end_log_pos 199 CRC32 0x676e7bbc Query thread_i
d=7 exec_time=0 error_code=0
SET TIMESTAMP=1385541659/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.uniq
ue_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/
;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 199
#131127 16:40:59 server id 1 end_log_pos 314 CRC32 0xdccba63c Query thread_i
d=7 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1385541659/*!*/;
insert into test values(20131120,1,'ylc')
/*!*/;
# at 314
#131127 16:40:59 server id 1 end_log_pos 345 CRC32 0x19715512 Xid = 254
COMMIT/*!*/;
# at 345
#131127 16:45:35 server id 1 end_log_pos 456 CRC32 0x117c7cc6 Query thread_i
d=9 exec_time=0 error_code=0
SET TIMESTAMP=1385541935/*!*/;
create table test2 select * from test
/*!*/;
# at 456
#131127 16:47:01 server id 1 end_log_pos 528 CRC32 0xdc4b93a1 Query thread_i
d=10 exec_time=0 error_code=0
SET TIMESTAMP=1385542021/*!*/;
BEGIN
/*!*/;
# at 528
#131127 16:47:01 server id 1 end_log_pos 745 CRC32 0x661ba877 Query thread_i
d=10 exec_time=0 error_code=0
SET TIMESTAMP=1385542021/*!*/;
CREATE TABLE `test3` (
`birthday` datetime DEFAULT NULL,
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(255) CHARACTER SET gbk DEFAULT NULL
)
/*!*/;
# at 745
#131127 16:47:01 server id 1 end_log_pos 798 CRC32 0xfac2b0a3 Table_map: `test
`.`test3` mapped to number 73
# at 798
#131127 16:47:01 server id 1 end_log_pos 848 CRC32 0x1ebdece5 Write_rows: tabl
e id 73 flags: STMT_END_F
BINLOG '
hbGVUhMBAAAANQAAAB4DAAAAAEkAAAAAAAEABHRlc3QABXRlc3QzAAMSAw8DAP4BBaOwwvo=
hbGVUh4BAAAAMgAAAFADAAAAAEkAAAAAAAEAAgAD//iZkSgAAAEAAAADAHlsY+XsvR4=
'/*!*/;
# at 848
#131127 16:47:01 server id 1 end_log_pos 879 CRC32 0x02bbb92b Xid = 378
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
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,for
sync_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)
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=1
general_log_file="DSGTEST.log"
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
-----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
测试innodb_flush_log_at_trx_commit
set global nnodb_flush_log_at_trx_commit=0;
mysql> call test;
Query OK, 0 rows affected (0.42 sec)
set global nnodb_flush_log_at_trx_commit=1;
mysql> call test;
Query OK, 0 rows affected (2 min 12.85 sec)
mysql> show table status like 'T' \G;
*************************** 1. row ****
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 489465
Avg_row_length: 33
Data_length: 16269312
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2013-11-27 18:34:21
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show global status like 'innodb_os%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| Innodb_os_log_fsyncs | 5009 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 5409280 |
+------------------------------+---------+
4 rows in set (0.00 sec)
---------------mysql 数据文件--------------------------------------------
how mysql uses disk space
Mysql server uses disk space in several ways,primarily for directories and files that are found under a single
location known as the server'data directory.The server uses its data directory to store all the following:
database directories.Each database corresponds to a single directory under the data directory,regardless of what types of
tables you create in the database, For example,a given database is represented by one directory whether it contains myissam tables,innodb tables,or
a mix of the two.
table format file (.frm files) that contain a description of table struncture.Every table has its own .frm file,located in the appropriate database directory.
This is true no matter which stroage engine manages the table.
Data and index fils are created for each table by some stroage engines and in the appropriate database directory.For example,the myissam storage engine create a data file and an index file for each table.
the innodb stroage engine has its own tablespace and log files.the tablespace contains data and index information for all innodb tables,as well as the undo logs that are needed if a transaction must be rolled back.the log files record
information about committed tranasactions and are used to ensure that no data loss occurs.
by default,the tablespace and log files are located in the data directory.the default tablesapce file is named ibdata1 and the default log files are named ib_logfile0 and ib_logfile1.
innodb created the tablesapce file fir a given table in the table's database directory.)
server log files and status files.These files contain information abort the statemens that the server has been processing.logs are used for replication and data recovery,to
obtain information for use in opyimizing query performance,and to determine whether operational problems are occurring.
下面这个参数觉得Inndo table 单独存放还是共享存放
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24577884/viewspace-1061313/，如需转载，请注明出处，否则将追究法律责任。
转播
