007、MySQL数据文件
mysql的数据文件：
mysql日志文件
mysql的日志文件，包括5种：
- 错误日志
- 慢查询日志
- 查询日志(全量日志) general log
- 二进制日志 binlog
- 中继日志 relay log
1、错误日志
错误日志文件：对mysql启动，运行，关闭过程进行了记录，不但记录了错误信息，也记录一些警告信息和正确信息，类似于oracle的alter日志。
从进程中就可以看到错误日志的路径(log-error)：
[root@ning ~]# ps -ef|grep mysql
root 1155 1149 0 Mar23 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql 2156 1155 0 Mar23 ? 00:00:48 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/u01/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/u01/data/mysql/error.log --open-files-limit=3072 --pid-file=/u01/data/mysql/ning.pid --socket=/tmp/mysql.sock --port=3306从数据库中查询错误日志的路径：
mysql> show variables like 'log_error';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| log_error | /u01/data/mysql/error.log |
+---------------+---------------------------+
1 row in set (0.00 sec)有时我们也可以直接在error.log里的warnings信息中，获取到优化的帮助。MySQL的错误日志不仅记录错误信息，同时记录优化相关的信息：
[root@ning ~]# vi /u01/data/mysql/error.log例如：如果buffer pool设置的太小，err log中可能会建议调整buffer pool 的大小。
error log日志中还有数据库的启动信息、崩溃恢复信息。
2、慢查询日志
查看慢查询日志是否打开和慢查询日志的位置：
mysql> show variables like '%slow_query%';
+---------------------+--------------------------+
| Variable_name | Value |
+---------------------+--------------------------+
| slow_query_log | ON |
| slow_query_log_file | /u01/data/mysql/slow.log |
+---------------------+--------------------------+
2 rows in set (0.00 sec)慢查询日志能为sql语句的优化带来很好的帮助，查看查询超过某个时间(默认10秒)之后，sql就会记录到慢查询日志中，参数为：
mysql> show variables like '%long_query%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.050000 |
+-----------------+----------+
1 row in set (0.00 sec)
--建议设置为0.1以上MySQL数据库默认并不启动慢查询日志，需要手动将slow_query_log设置为on。
注意：mysql数据库会记录运行时间超过该值的所有sql语句，但是对于正好等于long_query_time的情况，并不会被记录。
开启下面的参数，如果运行的sql语句没有使用索引，则mysql数据库同样会将这条sql语句记录到慢查询日志文件：
mysql> show variables like '%log_quer%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)查看慢查询日志的内容：
[root@ning ~]# vi /u01/data/mysql/slow.log
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
……直接查看slow.log看起来太乱，而数据库本身有一个工具：/usr/local/mysql/mysqldumpslow来查看慢查询日志：
[root@ning ~]# mysqldumpslow -h
Option h requires an argument
ERROR: bad option
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time #根据平均锁定时间排序
ar: average rows sent #根据平均行发送时间排序
at: average query time #根据平均查询时间排序
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries #仅显示前n行
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names #名称中至少包含n位数字的抽象数字
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time举例，根据平均查询时间，将信息转存到文本文件中：
[root@ning ~]# mysqldumpslow -s at /u01/data/mysql/slow.log >1.log
Reading mysql slow query log from /u01/data/mysql/slow.log查看文本文件：
mysql 5.1之后，可以将慢查询日志记录到一张表中，这样查询更加直观，相关的参数为：log_output
注意：当大数据量下，查询效率可能不高，可以把csv引擎调整为myisam存储引擎(不推荐)。
通过数据库表存储慢查询日志：
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.01 sec)
mysql> set global log_output='table';
Query OK, 0 rows affected (0.07 sec)
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)mysql> use mysql;
Database changed
mysql> select * from slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-------------------------------+-----------+
| 2021-06-08 11:25:10 | root[root] @ localhost [] | 00:00:00 | 00:00:00 | 0 | 0 | | 0 | 0 | 1113306 | set global log_output='table' | 1 |
| 2021-06-08 11:25:57 | root[root] @ localhost [] | 00:00:00 | 00:00:00 | 1 | 0 | | 0 | 0 | 1113306 | SELECT DATABASE() | 1 |
| 2021-06-08 11:26:15 | root[root] @ localhost [] | 00:00:00 | 00:00:00 | 0 | 0 | test | 0 | 0 | 1113306 | create table t3(id int) | 1 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+------+----------------+-----------+-----------+-------------------------------+-----------+
3 rows in set (0.00 sec)但是基本上，没有人使用这种方式。
3、查询(全量)日志general log
在数据库中查看全量日志：
mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /u01/data/mysql/ning.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)默认情况下，全量日志是关闭的。
为什么关闭全量日志？
可以先打开全量日志试验一下：
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | ON |
| general_log_file | /u01/data/mysql/ning.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)另外打开一个窗口监控全量日志内容：
[root@ning ~]# tail -f /u01/data/mysql/ning.log
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
210324 14:25:03 19 Query show variables like '%general%'在MySQL中随意执行语句：
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
| t3 |
+----------------+
3 rows in set (0.00 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql> select * from t2;
Empty set (0.00 sec)在全量日志中：
[root@ning ~]# tail -f /u01/data/mysql/ning.log
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
210324 14:25:03 19 Query show variables like '%general%'
210324 14:29:06 19 Query SELECT DATABASE()
19 Init DB test
19 Query show databases
19 Query show tables
19 Field List t1
19 Field List t2
19 Field List t3
210324 14:29:12 19 Query show tables
210324 14:29:18 19 Query select * from t1
210324 14:29:23 19 Query select * from t2可以发现，所有操作的语句都被记录了下来(没有语法错误的语句，有语法错误的语句不会记录)。
而binlog和redo log就不会记录select和show的语句，binlog和redo log会记录正真对数据进行修改的语句。
所以，默认情况下，全量日志是关闭的状态，因为开启全量日志会占用大量的磁盘空间。
什么情况下开启呢？比如：数据库中突然有大量不正常的IP(伪造的)地址链接，而CPU又不是很忙的情况，可以开启全量日志，全量日志中会记录链接数据库的客户端IP地址。
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 20 | root | localhost | test | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)4、二进制日志binlog
二进制日志：记录了对数据执行更改的所有操作，包括执行数据库更改操作的时间和执行时间等信息，但并不包括select和show这类操作，因为这类操作并没有对数据本身进行修改。
二进制日志的作用：
- 恢复(recovery)。某些数据的恢复需要二进制日志，当一个数据库全备文件恢复后，我们可以通过二进制日志进行point_in_position，point_in_time
- 复制(replication)。通过复制和执行二进制日志使得一台远程的mysql数据库(slave或者叫standby)与一台mysql数据库(master, primary)进行实时同步。
影响二进制日志记录的信息和行为参数如下：
- max_binlog_size：指定单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件。从mysql5.0开始的默认值为1G，之前的版本默认为1.1G
- binlog_cache_size：所有未提交的二进制日志会被记录到一个缓存中，等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件，默认是32KB，该参数是基于会话的，也就是说当一个线程开始一个事务，mysql会自动分配一个大小为binlog_cache_size的缓存，会话退出，自动释放。注意：此值设置要小心，不宜过大或过小。当一个事务的记录大于设定的binlog_cache_size，mysql会把缓冲中的日志写入一个临时文件。查看目前使用情况：binlog_cache_use，binlog_cache_disk_use。
如何判断binlog_cache_size设置的大小？查看参数：
mysql> show global status like '%binlog_cache%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Binlog_cache_disk_use | 0 | #如果该参数的值不是0，则证明已经使用到disk了，需要调高binlog_cache_size的值。该参数的值不会从非0恢复为0 | Binlog_cache_use | 0 | +-----------------------+-------+ 2 rows in set (0.00 sec) - sync_binlog：参数值：0，1，N。参数值影响binlog的刷新，0代表交由操作系统管理，1代表实时刷新，N代表N次事务提交后刷新。binlog日志很重要，一般保留15天，如果没有备份集的情况下，需要binlog进行数据恢复。查看binlog文件列表：
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql_bin.000001 | 240 | | mysql_bin.000002 | 143 | | mysql_bin.000003 | 120 | +------------------+-----------+ 3 rows in set (0.05 sec)查看binlog文件的路径：mysql> show variables like '%log_bin%'; +---------------------------------+---------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------+ | log_bin | ON | | log_bin_basename | /u01/data/mysql/mysql-bin | | log_bin_index | /u01/data/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+---------------------------------+ 6 rows in set (0.00 sec)binlog文件是二进制文件，不能使用普通的vi、cat、等命令查看。MySQL数据库提供了工具查看二进制日志文件：/usr/local/mysql/mysqlbinlog[root@ning ~]# mysqlbinlog --help …… [root@ning ~]# mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /u01/data/mysql/mysql-bin.000002 >1.log命令中--no-defaults：不使用默认的输出命令中-v -v：详细输出二进制日志命令中--base64-output=decode-rows：转换成行模式的格式查看文本文件内容：[root@ning ~]# vi 1.log BEGIN /*!*/; # at 7325 #210316 20:59:40 server id 3 end_log_pos 7388 CRC32 0x73730cce Table_map: `mysql`.`help_category` mapped to number 0 # at 7388 #210316 20:59:40 server id 3 end_log_pos 7460 CRC32 0x86da0509 Write_rows: table id 0 flags: STMT_END_F ### INSERT INTO `mysql`.`help_category` ### SET ### @1=26 /* SHORTINT meta=0 nullable=0 is_null=0 */ ### @2='GeometryCollection properties' /* STRING(192) meta=65216 nullable=0 is_null=0 */ ### @3=1 /* SHORTINT meta=0 nullable=1 is_null=0 */ ### @4='' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */ # at 7460 #210316 20:59:40 server id 3 end_log_pos 7534 CRC32 0xe5172ee1 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1615899580/*!*/; COMMIT从begin开始，到commit结束，代表一个事务。
以下几个参数与主从架构有关：
- binlog_do_db：只让某个库记录 binglog，如果复制多个数据库，重复设置这个选项即可
- binlog_ingore_db：不记录指定的数据库的二进制日志，与binlog_do_db互斥，这两个参数一般只设置一个。
- log_slave_update：参数默认是关闭的状态，如果不手动设置，那么bin-log只会记录直接在该库(从库)上执行的SQL语句，由replication机制的SQL线程读取relay-log而执行的SQL语句并不会记录到bin-log，主从从架构需要开启此参数。
- binlog_format：binlog日志的格式，有row，statement，mixed
5、中继日志relay log
中继日志relay log是主从架构里一个很重要的日志，记录在从库。从库通过I/O thread发起请求读取主库的binlog，主库通过I/O dump thread将binlog传递到从库，从库通过I/O thread将发过来的binlog转存到中继日志relay log中，从库的SQL thread发起请求读取relay log，并应用到数据库中，来和主库达成一致的状态。
其他文件
1、套接字文件
Unix系统下本地连接mysql可以采用unix域套接字方式，这种方式需要一个套接字文件。套接字文件由参数socket控制，一般在/tmp目录下，名为mysql.sock
查看socket文件的位置：
[root@localhost ~]# mysql -uroot -proot -S /tmp/mysql.sock
mysql> show variables like 'socket';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| socket | /tmp/mysql.sock |
+---------------+-----------------+
1 row in set (0.00 sec)2、进程文件
查看MySQL进程就可以看到pid进程文件，文件内容就是MySQL 的进程号：
[root@localhost ~]# ps -ef|grep mysql
root 2043 2041 0 09:56 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql 2709 2043 0 09:56 ? 00:00:11 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/u01/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/u01/data/mysql/error.log --open-files-limit=3072 --pid-file=/u01/data/mysql/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306
[root@localhost ~]# cat /u01/data/mysql/localhost.localdomain.pid
2709也可以通过参数查看：
mysql> show variables like '%pid%';
+---------------+-------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------+
| pid_file | /u01/data/mysql/localhost.localdomain.pid |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)3、表结构定义文件
因为mysql插件式存储引擎的体系结构的关系，mysql对于数据的存储是按照表的，所以每个表都会有与之对应的文件。
无论采用何种存储引擎，mysql都有一个以frm为后缀名的文件，该文件记录着该表的表结构定义。
4、innodb存储引擎文件
以innodb为存储引擎的表，每个表还有自己独有的文件，包括表空间文件，重做日志文件。
- 共享表空间文件：将存储的数据按表空间进行存放，默认配置下，会有一个初始大小为10MB，名为ibdata1的文件，该文件就是默认的共享表空间文件，可以用多个文件组成表空间，同时制定文件属性，例如在参数文件中指定：innodb_data_file_path=/u01/data/mysql/ibdata1:1024M:autoextend;/u01/data/mysql/ibdata2:10M:autoextend
- 独立表空间：我们可以将每个基于innodb存储引擎的表单独产生一个表空间，文件名为表名.ibd，这样就不用将所有的数据都存放于默认的表空间中，由参数innodb_file_per_table控制：
mysql> show variables like '%per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.01 sec)这些单独的表空间文件仅仅存储表的数据，索引和插入缓存(insert buffer)等信息，其余信息还是存放在默认的表空间中。
- 重做日志文件：ib_logfile0和ib_logfile1文件对于innodb存储引擎至关重要，它们记录了innodb存储引擎的事务日志。重做日志文件的主要目的是：实例或者介质失败，innodb存储引擎会使用重做日志恢复到失败前的时刻，来保证数据的完整性。innodb存储引擎至少一个重做日志组，每个组下至少两个重做日志文件，两个日志文件大小一致，并以顺序写，循环写的方式使用。
redo log和binlog的区别：
1、redo log记录已提交和未提交的数据，而binlog只记录已提交的数据。
2、redo log又叫事务日志，不会记录myisam存储引擎相关的表，binlog会记录。
一些影响redo log的参数：
mysql> show variables like '%log_file_%';
+---------------------------+-----------+
| Variable_name | Value |
+---------------------------+-----------+
| innodb_log_file_size | 268435456 | #重做日志文件的大小
| innodb_log_files_in_group | 2 | #每组中日志文件的数量
+---------------------------+-----------+
2 rows in set (0.00 sec)
mysql> show variables like 'innodb%group%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ | #重做日志的路径
| innodb_mirrored_log_groups | 1 | #日志镜像文件组的数量，值为1代表没有镜像组
+----------------------------+-------+
3 rows in set (0.00 sec)redo log日志大小怎么设置？不宜太大，也不能太小。日志文件过大导致恢复时间边长，日志文件太小，如果业务量大，日志切换频率太高，造成IO压力过高。
redo log的刷新条件：
- master thread线程每秒的操作都会将重做日志缓存(redo log buffer)写入磁盘的redo log file中，不论事务是否已经提交。
- 由参数innodb_flush_log_at_trx_commit控制，参数有三个值：0代表当事务提交时，并不将事务的重做日志缓存写入磁盘上的日志文件，而是等待主线程每秒的刷新，即每1秒刷新1次；1代表，在commit时将重做日志缓存同步写入磁盘中的redo log file，即实时刷新；2代表重做日志缓存异步写入到磁盘，不能完全保证commit时写入重做日志文件，即交由操作系统控制。
浙公网安备 33010602011771号