物理存储结构
文件
-
参数文件:Mysql实例启动初始化一些参数
-
日志文件：包括错误日志等，二进制日志文件，慢查询日志文件，查询日志文件等
-
套接字文件：当用UNIX域套接字方式进行连接时需要的文件
-
pid文件：MySQL实例的进程ID文件
-
表结构定义文件：存储Mysql表结构定义文件
-
存储引擎文件 ：每种存储引擎都有自己的文件存储数据，一般使用的引擎为InnoDB
参数文件
可以通过以下命令查看参数文件的位置：
$ mysql --help |grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf /usr/local/opt/mysql@5.7/my.cnf ~/.my.cnf
日志文件
错误日志
错误日志文件对MySQL的启动，运行，关闭过程进行了记录，该文件不仅记录了所有的错误信息，也记录了一些告警信息或正确的信息。通过命令show variables like 'log_error';可以定位错误日志文件位置。
mysql> -- 错误日志文件路径
mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | ./xjt2016.local.err |
+---------------+---------------------+
1 row in set (0.00 sec)
慢查询日志
慢查询日志记录了执行超过一个阈值的SQL语句，通过该日志，可以帮助定位存在问题的SQL语句，并进行优化。通过参数long_query_time设置这个阈值，默认10，单位秒。
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
其他一些参数介绍：
| 参数 | 说明 |
|---|---|
| slow_query_log_file | 慢查询日志文件路径 |
| slow_query_log | 用于指定是否打开慢查询日志 |
| long_query_time | 超过多少秒的查询就写入日志 |
| log_output=file | 必须指定file或者是table如果是table则慢查询信息会保存到mysql库下的slow_log表中。这点要清楚。默认值是NONE |
| log_queries_not_using_indexes | 未使用索引的查询也被记录到慢查询日志中（可选）。尽量少使用 |
mysql自带了一个查看慢日志的工具mysqldumpslow
查询日志
查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行，默认文件名为：主机名.log。因为SQL的量大，默认是不开启的。一些特殊情况（如排除故障）可能需要临时开启一下。
mysql> SHOW VARIABLES LIKE '%general%';
+------------------+----------------------------------+
| Variable_name | Value |
+------------------+----------------------------------+
| general_log | OFF |
| general_log_file | /usr/local/var/mysql/xjt2016.log |
+------------------+----------------------------------+
2 rows in set (0.00 sec)
- general_log:用于开启general log。ON表示开启，OFF表示关闭。
- log_output:日志输出的模式。FILE表示输出到文件，TABLE表示输出到mysq库的general_log表，NONE表示不记录general_log。
- general_log_file:日记输出文件的路径，这是log_output=FILE时才会输出到此文件
set global general_log=on; # 开启general log模式
set global general_log=off; # 关闭general log模式
set global general_log_file='file-path.log';//输出文件地址
二进制日志
概念
二进制日志（binary log）记录了对Mysql数据库执行更改的所有操作，不包括select和show这类操作，因为这类操作对数本身没有修改。 二进制日志主要有以下几个作用：
- 恢复：某些数据的恢复需要二进制日志
- 复制：主从同步
- 审计：通过对二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击
存储路径
通过在配置文件my.cnf中配置参数log-bin[=name]可以启动二进制日志。如果不指定name，则默认二进制日志文件名为主机名，后缀为二进制日志的序列号，所在的路径为数据库所在的目录：
mysql> SHOW GLOBAL VARIABLES LIKE "%datadir%";
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| datadir | /usr/local/var/mysql/ |
+---------------+-----------------------+
1 row in set (0.00 sec)
开关配置
设置开启binlog其实需要设置三个参数，如下：
# Binary Logging.
log-bin=binlog
binlog-format=Row
server-id=1
通过命令show variables like "%binlog%";查看到的相关参数，一般不会去设置，所以不一一列举。
binlog日志模式（binlog_format）
mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。参考文章
-
Statement Level模式 每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
-
Row Level模式 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
-
Mixed模式（混合模式） 以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
相关操作命令
1.查看当前bin log 文件
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 403 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.获取binlog文件列表
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 903 |
| binlog.000002 | 403 |
+---------------+-----------+
2 rows in set (0.00 sec)
3.所有binlog文件
mysql> show master logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 903 |
| binlog.000002 | 403 |
+---------------+-----------+
2 rows in set (0.00 sec)
4.查看指定binlog文件的内容
mysql> show binlog events in 'binlog.000001';
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.23-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids | 1 | 154 | |
| binlog.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 219 | Query | 1 | 295 | BEGIN |
| binlog.000001 | 295 | Table_map | 1 | 369 | table_id: 108 (kdzs_xcx.print_bg_trade) |
| binlog.000001 | 369 | Update_rows | 1 | 509 | table_id: 108 flags: STMT_END_F |
| binlog.000001 | 509 | Xid | 1 | 540 | COMMIT /* xid=14208 */ |
| binlog.000001 | 540 | Anonymous_Gtid | 1 | 605 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 605 | Query | 1 | 716 | use `kdzs_xcx`; optimize table print_bg_trade |
| binlog.000001 | 716 | Anonymous_Gtid | 1 | 781 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 781 | Query | 1 | 903 | use `kdzs_xcx`; alter table print_bg_trade engine=InnoDB |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
11 rows in set (0.00 sec)
5.将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件。
mysql> flush logs;
Query OK, 0 rows affected (0.07 sec)
6.删除所有二进制日志，并重新开始记录
mysql> reset master;
Query OK, 0 rows affected (0.09 sec)
7.其他
-- binlog_format
-- 1. STATEMENT模式（SBR）每一条会修改数据的sql语句会记录到binlog中。
-- 2. ROW模式（RBR）不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。
-- 3. MIXED模式（MBR）以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog
-- max_binlog_cache_size -- 最大binlog缓存大小
-- binlog_format = MIXED //binlog日志格式，mysql默认采用statement，建议使用mixed
-- log-bin = /data/mysql/mysql-bin.log//binlog日志文件
-- expire_logs_days = 7 //binlog过期清理时间
-- max_binlog_size = 100m //binlog每个日志文件大小
-- binlog_cache_size = 4m //binlog缓存大小
-- max_binlog_cache_size = 512m //最大binlog缓存大小
Mysqlbinlog解析工具
套接字文件
在UNIX系统下本地连接Mysql可以采用UNIX域套接字方式，这种方式需要一个套接字（socket）文件。套接字文件可以由参数socket控制，一般在/tmp目录下，名为mysql.socket。
mysql> show variables like '%socket%';
+-----------------------------------------+-----------------+
| Variable_name | Value |
+-----------------------------------------+-----------------+
| performance_schema_max_socket_classes | 10 |
| performance_schema_max_socket_instances | -1 |
| socket | /tmp/mysql.sock |
+-----------------------------------------+-----------------+
3 rows in set (0.02 sec)
pid文件
当Mysql实例启动时，会将自己的进程ID写入一个文件当中——该文件即为PID文件。该文件可由参数pid_file控制，默认位于数据库目录下，文件名为主机名.pid。
mysql> show variables like '%pid_file%';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| pid_file | xjt2016.local.pid |
+---------------+-------------------+
1 row in set (0.00 sec)
- 未指定 pid 文件时，pid 文件默认名为 主机名.pid，存放的路径在默认 MySQL 的数据目录。通过 mysqld_safe 启动 MySQL 时，mysqld_safe 会检查 pid 文件，如果 pid 文件不存在，不做处理；如果文件存在，且 pid 已占用则报错 "A mysqld process already exists"，如果文件存在，但 pid 未占用，则删除 pid 文件
- 查看 MySQL 的源码可以知道，mysqld 启动后会通过
create_pid_file函数新建 pid 文件，通过getpid()获取当前进程pid并将pid写入pid文件 - 因此，通过
mysqld_safe启动时， MySQL pid文件的作用是：在数据文件是同一份，但端口不同的情况下，防止同一个数据库被启动多次。
表结构定义文件
因为MySQL插件式存储引擎的体系结构的关系，MySQL数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。
frm还用来存放视图的定义，创建视图的时候，跟建表一样，在数据库目录下会创建一个frm文件。
InnoDB存储引擎文件
表空间文件
InnoDB采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。该文件为默认的表空间文件（tablespace file），也就是共享表空间文件。可以通过参数innodb_data_file_path对其进行设置，格式如下:
innodb_data_file_path=datafile_space1[;datafile_space2]...
用户可以通过多个文件组成一个表空间，同时指定文件的属性，如：
innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend
/db/ibdata1和/dr2/db/ibdata2两个文件一起组成表空间，若两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。同时两个文件名后面跟着属性，表示ibdata1的大小为2000MB，文件ibdata2的大小为2000MB，该文件可以自动增长（autoextend）。 若设置了参数innodb_file_per_table,则用户可以将每一个基于InnoDB存储引擎的表产生一个独立的表空间。独立表空间的命名规则为：表名.ibd。独立表空间仅存储表的数据，索引和插入缓存BITMAP等信息，其余信息还是存放在默认的表空间中。
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.02 sec)
重做日志文件
参考资料：
在默认情况下，在InnoDB存储引擎的目录（innodb_data_home_dir）下会有两个名为ib_logfile0和ib_logfile1文件，这两个文件即为重做日志文件（redo log file）。
- 每个InnoDB存储引擎至少有1个重做日志文件组（group）,每个文件组下至少有两个重做日志文件
- 可以设置多个镜像日志组（mirrored log groups），将不同的文件组放在不同的磁盘上，提高重做日志的高可用性
- 日志组中的每个重做日志文件大小一致，并以循环写入的方式运行
一些参数说明：
| 参数 | 说明 | 值 |
|---|---|---|
| innodb_log_buffer_size | 日志缓冲池的大小 | 16777216 |
| innodb_log_checksums | 开启或关闭redo checksum | ON |
| innodb_log_compressed_pages | 判定在压缩page时，是否在redo中存储压缩页数据 | ON |
| innodb_log_file_size | 日志组的大小,默认为5M | 50331648 |
| innodb_log_files_in_group | 日志组的数量，默认为2 | 2 |
| innodb_log_group_home_dir | 日志组所在的路径，默认为data的home目录 | ./ |
| innodb_log_write_ahead_size | 表示redo log写前的块大小 | 8192 |
| innodb_mirrored_log_groups | 指定了日志镜像文件组的数量，默认为1，代表只有一个日志文件组，没有镜像 |
重做日志与binlog的区别：
- binlog是MySQL Server层记录的日志;redo log是InnoDB存储引擎层的日志
- binlog记录的是一个事务的具体操作内容，即该日志是逻辑日志；redo log记录的是关于每一个页（page）的更改的物理情况
- 写入的时间不同，binlog仅在事务提交前进行提交，即只写磁盘一次（不论事务有多大）；而在事务进行的过程中，却不断有重做日志条目（redo）entry被写入到重做日志文件中。
重做日志的条目结构：
- redo_log_type:重做日志类型，占用1字节
- space：表空间ID，采用压缩方式，占用空间可能小于4字节
- page_no：页的偏移量，采用压缩的方式
- redo_log_body:表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析
重做日志的写入过程： 重做日志的写入，是先写入一个重做日志缓冲（redo log buffer）中，然后按照一定的条件顺序写入日志文件，如图： 
重做日志缓冲往磁盘写入时，是按512字节 ，也就是一个扇区的大小进行写入。因为扇区是写入的最小单位，因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有dubbowrite。
重做日志写入的条件：
- master thread每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务已经提交。
- 参数
innodb_flush_log_at_trx_commit控制触发写磁盘，表示在提交（commit）操作时，处理重做日志的方式
innodb_flush_log_at_trx_commit参数解释
- 0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
- 1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
- 2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。
为了保证事务的ACID中的持久性，必须将参数innodb_flush_log_at_trx_commit的值设置为1，也就是每当有事务提交时，就必须确保事务都已经写入重做日志文件。
1768
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
