MySQL数据库文件管理

阅读量：4171 次

发布时间：2019-05-26

本文共 21569 字，大约阅读时间需要 71 分钟。

MySQL数据库文件管理

概述

MySQL 在整体架构上分为 Server 层和存储引擎层。

其中 Server 层，包括连接器、查询缓存、分析器、优化器、执行器等，存储过程、触发器、视图和内置函数都在这层实现。数据引擎层负责数据的存储和提取，如 InnoDB、MyISAM、Memory 等引擎。在客户端连接到 Server 层后，Server 会调用数据引擎提供的接口，进行数据的变更。

单点（Single），适合小规模应用，复制（Replication），适合中小规模应用，集群（Cluster），适合大规模应用。

mysql数据库文件分类:

1)参数文件: my. cnf

2)日志文件:错误日志文件、二进制日志文件、慢查询日志文件、全日志文件。

3) socket文件: ip + port

4) pid文件: mysql实例的进程ID文件

5) mysql表结构文件:表结构定义文件

6)存储引擎相关的文件:数据文件/重做/undo

一 my.cnf参数文件

My.cnf默认/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

生产环境，一般手工指定参数文件

mysqld --defaults-file=/mysql/data/3306/my.cnf --user=mysql &

1.1参数分类

mysql参数

按服务器变量分:静态变量参数、动态变量参数

按修改级别分:

会话级别:

set session 参数名=参数值;

set @@session.参数名=参数值;

全局级别:

set global 参数名=参数值;

set @@global.参数名=参数值;

--注意:动态变量参数、会话级别，在下次重启失效，默认会去读my.cnf，如果想永久生效，还是改my.cnf文件。

1.2 如何查看mysql所有的参数

方法1 系统命令行

mysqld --verbose --help | more

方法2数据库命令查看

mysql -uroot -proot

mysql> show variables;

show variables like '%innodb%';

查innodb相关的参数

show session variables ;

show global variables ;

select @@session.autocommit;

select @@autocommit ;

select @@global.autocommit ;

1.3 官方手册

System variable 系统变成=yes，可以修改。

name :参数名

Cmd-Line:配置文件,mysqld_safe -变量名=值

option file: 配置文件,yes , mysqld_safe -变量名=值

System Var :能改,yes是系统变量

statusVar:不能改,yes是指标

VarScope:全局/会话/两者

dynamic:动态/静态

1.4 设置参数文件

永久修改-必须要修改参数文件

1.4.1 需要重启数据库-生产不推荐-需要重启

修改参数的时候，可以先看看官方稳定，比如wait_timeout，需要修改interactive_timeout。

mysql> show variables like 'wait_timeout';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| wait_timeout | 1800 |

+---------------+-------+

1 row in set (0.00 sec)

mysql> show variables like 'interactive_timeout';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| interactive_timeout | 1800 |

+---------------------+-------+

1 row in set (0.00 sec)

修改my.cnf文件

vim /mysql/data/3306/my.cnf

interactive_timeout = 1800

wait_timeout = 1800

[root@mysql5 ~]# service mysql restart

Shutting down MySQL.... [确定]

Starting MySQL. [确定]

show variables ##会话

show global variables ##全局变量

mysql> show variables like '%timeout%';

+-----------------------------+----------+

| Variable_name | Value |

+-----------------------------+----------+

| interactive_timeout | 1860 |

| wait_timeout | 1860 |

+-----------------------------+----------+

mysql> select @@global.wait_timeout;

+-----------------------+

| @@global.wait_timeout |

+-----------------------+

| 1900 |

+-----------------------+

1 row in set (0.00 sec)

1.4.2 不需要重启数据库-生产推荐

再数据库命令行执行，再修改配置文件。

mysql> set global interactive_timeout=1800;

Query OK, 0 rows affected (0.00 sec)

mysql> set global wait_timeout=1800;

Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%timeout%';

+-----------------------------+----------+

| Variable_name | Value |

+-----------------------------+----------+

| interactive_timeout | 1900 |

=| wait_timeout | 1900 |

+-----------------------------+----------+

mysql> select @@global.wait_timeout;

+-----------------------+

| @@global.wait_timeout |

+-----------------------+

| 1900 |

+-----------------------+

1 row in set (0.00 sec)

1.4.3 会话修改-推出会话-还原

set wait_timeout=1200;

Query OK, 0 rows affected (0.00 sec)

select @@wait_timeout;

+----------------+

| @@wait_timeout |

+----------------+

| 1200 |

+----------------+

1 row in set (0.00 sec)

set session wait_timeout=1400;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@wait_timeout;

+----------------+

| @@wait_timeout |

+----------------+

| 1400 |

+----------------+

1 row in set (0.00 sec)

set local wait_timeout=1500;

Query OK, 0 rows affected (0.00 sec)

select @@wait_timeout;

+----------------+

| @@wait_timeout |

+----------------+

| 1500 |

+----------------+

1 row in set (0.01 sec)

set @@session.wait_timeout=1600;

Query OK, 0 rows affected (0.00 sec)

select @@wait_timeout;

+----------------+

| @@wait_timeout |

+----------------+

| 1600 |

+----------------+

1 row in set (0.00 sec)

1.5 设置参数

1.5.1 全局参数

set global wait_timeout=1800;

set @@global.wait_timeout=1600;

set @@ global.wait_timeout =@@ session.wait_timeout;

1.5.2 会话参数

set wait_timeout=1200;

set session wait_timeout=1400;

set @@session.wait_timeout=1600;

set local wait_timeout=1500;

set @@session.wait_timeout=@@global.wait_timeout;

1.6 参数查看

1.6.1 全局参数

select @@global.wait_timeout;

show global variables like '%timeout%';

1.6.2 会话参数

select @@wait_timeout;

select @@session.wait_timeout;

select @@local.wait_timeout;

show variables like '%timeout%';

show local variables like '%timeout%';

show session variables like '%timeout%';

二MySQL日志文件管理

2.1 MySQL各类日志文件介绍

错误日志(error log)

二进制日志(binlog)

慢查询日志(slow query LOG )

通用日志(general log)

重做日志(redo LOG)

中断日志(relay LOG)

DDL日志(ddl LOG)

undo log

2.2 错误日志(error log)

错误日志: mysql启动/运行/关闭过程的记录，记录错误/警告/正常的信息。--相当于oracle的alert日志

show variables like 'log_error%';

+---------------------+-----------------------------------+

| Variable_name | Value |

+---------------------+-----------------------------------+

| log_error | /mysql/log/3306/itpuxdb-error.err |

| log_error_verbosity | 3 |

+---------------------+-----------------------------------+

2 rows in set (0.00 sec)

log_ error_ verbosity:

1:只记录错误日志

2:记录错误+警告日志

3:记录错误/警告/正常的信息默认3

DB端的报错

cat /mysql/log/3306/itpuxdb-error.err | grep erro

more /mysql/log/3306/itpuxdb-error.err

OS端的报错

查看系统报错

cat /var/log/messages

2.3二进制日志(binlog)

二进制日志，记录数据库发生更改的SQL语句，以二进制方式保存在磁盘中。--相当于oracle的归档日志

作用:恢复，复制，审计。

特点:

1)记录的是SQL语句的形式。

2) commit提交的时候才写binoog,如果1个事务有5个语句，commit之前，写binlog_buffer , commi t时候从binlog_buffer写回磁盘的binlog不会覆盖，是一直存在的。

3)可以做备份后的恢复

4)对所有的表起作用

2.3.1 开启binlog

是否开启

show variables like '%log_bin%';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin | OFF |

| log_bin_basename | |

| log_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)

格式

show variables like '%binlog%';

+-----------------------------------------+----------------------+

| Variable_name | Value |

+-----------------------------------------+----------------------+

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_error_action | ABORT_SERVER |

| binlog_format | ROW |

| binlog_group_commit_sync_delay | 0 |

| binlog_group_commit_sync_no_delay_count | 0 |

| binlog_gtid_simple_recovery | ON |

| 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_statements_unsafe_for_binlog | ON |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 1073741824 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| sync_binlog | 1

修改参数文件my.cnf

Log_bin=/mysql/log/3306/binlog/bin-log

Log_bin_index=/mysql/log/3306/binlog/bin-log.index

mkdir -p /mysql/log/3306/binlog

chown mysql:mysql /mysql/log/3306/binlog

chmod -R 775 /mysql/log/3306/binlog/

vim my.cnf

log_bin=/mysql/log/3306/binlog/bin-log

log_bin_index=/mysql/log/3306/binlog/bin-log.index

[root@mysql5 3306]# service mysql restart

Shutting down MySQL.. [确定]

Starting MySQL. [确定]

mysql> show master status;

Empty set (0.00 sec)

没有开

show master status;

+----------------+----------+--------------+------------------+-------------------+

+----------------+----------+--------------+------------------+-------------------+

| bin-log.000002 | 154 | | | |

+----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

[root@mysql5 binlog]# ll

总用量 12

-rw-r----- 1 mysql mysql 177 8月 1 05:08 bin-log.000001

-rw-r----- 1 mysql mysql 154 8月 1 05:08 bin-log.000002

-rw-r----- 1 mysql mysql 76 8月 1 05:08 bin-log.index

二进制文件查看

mysqlbinlog -vv /mysql/log/3306/binlog/bin-log.000001

2.4 慢查询日志(slow query LOG )

show variables like '%_query%';

+------------------------------+-----------------------------------+

| Variable_name | Value |

+------------------------------+-----------------------------------+

| binlog_rows_query_log_events | OFF |

| ft_query_expansion_limit | 20 |

| have_query_cache | YES |

| long_query_time | 10.000000 |

| slow_query_log | ON |

| slow_query_log_file | /mysql/log/3306/itpuxdb-query.err

慢查询是大于10秒

long_query_time | 10.000000

查看慢查询日志

cat /mysql/log/3306/itpuxdb-query.err

慢查询的原因一般是:

1) Lock_ time锁等待时间太长 ###我的理解又阻塞

2) examined处理的数据太多 ###索引没有写好

没有使用索引，也会记录到慢查询日志

show variables like '%using_indexes%';

+----------------------------------------+-------+

| Variable_name | Value |

+----------------------------------------+-------+

| log_queries_not_using_indexes | ON |

| log_throttle_queries_not_using_indexes | 10

log_throttle_queries_not_using_indexes 10分钟记录一次没有使用，索引的SQL语句次数。

log_queries_not_using_indexes 当off时，如果使用了索引，就算慢，也不会记录日志。

如何分析和查询使用没有使用索引

Explain select * from itpux.dept where danme like ‘%hr%’;

2.4.1 慢查询MySQL自带工具Mysqldumpslow

mysqldumpslow /mysql/log/3306/itpuxdb-query.err

mysqldumpslow --help

-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 --查询时间

-t NUM just show the top n queries --top 返回多少行

-n NUM abstract numbers with at LEAST n digits WITHIN names --返回至少N条数据

-g PATTERN grep: only consider stmts that include this string --正规匹配模式，不区分大小写加条件显示结果。

2.4.2 获取慢查询日志文件中锁定时间最长的10条SQL语句

mysqldumpslow -s al -t 10 /mysql/log/3306/itpuxdb-query.err

2.4.3 获取慢查询日志文件中按查询时间排序的前10条里面包含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /mysql/log/3306/itpuxdb-query.err

2.5通用日志(general log)

记录了所有的对mysql数据库请求的信息

show variables like '%general%';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 5

Current database: *** NONE ***

+------------------+-------------------------------------+

| Variable_name | Value |

+------------------+-------------------------------------+

| general_log | OFF |

| general_log_file | /mysql/log/3306/itpuxdb-general.err

set @@global.general_log=on;

Query OK, 0 rows affected (0.00 sec)

show variables like '%general%';

+------------------+-------------------------------------+

| Variable_name | Value |

+------------------+-------------------------------------+

| general_log | ON |

2.6重做日志(redo LOG)

在数据库挂了以后，通过redo LOG +旧的数据块，进行数据前滚，再这undo进行回滚，来保证数据的一致性。

特点：1记录dml操作，redolog是循环的。能保证脏项没有写磁盘上时，对应的redolog是不会被覆盖的。

2mysql里面的redolog只能用于崩溃恢复。

3 只针对innodb的表又作用。

> LOG buffer > LOG file, 触发机制:

1)每1s写。

2)大于log buffer空间的1/2的时候

3) commi t的时候

4) log buffer写到1M的时候

5)日志先写机制，后合的脏页写磁盘的时候，就先把日志写过来。

innodb_log_file_size = 200M

innodb_log_files_in_group = 2

innodb_log_buffer_size = 16M

show variables like '%innodb_log%';

+-----------------------------+-----------+

| Variable_name | Value |

+-----------------------------+-----------+

| innodb_log_group_home_dir | ./

放在数据库data目录

-rw-r----- 1 mysql mysql 209715200 8月 1 05:08 ib_logfile0

-rw-r----- 1 mysql mysql 209715200 7月 13 20:11 ib_logfile1

innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

2.7 undo log

innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：

1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

show variables like "%undo%";

+--------------------------+------------+

| Variable_name | Value |

+--------------------------+------------+

| innodb_max_undo_log_size | 2147483648 |

| innodb_undo_directory | ./ |

| innodb_undo_log_truncate | ON |

| innodb_undo_logs | 128 |

| innodb_undo_tablespaces | 3 |

+--------------------------+------------+

2.8中断日志(relay LOG)

与二进制日志有点相似，用于存取从服务器的I0线程接收来自主服务器发来的变更日志。

show variables like '%relay%';

2.9DDL日志(ddl LOG)

记录元数据变更的操作( DDL操作，create/drop/alter/truncate/ .)

文件名: ddl_ .log. log

为了在DDL崩溃时可以恢复，以二进制方式存取，不可读，文件大小可存放100W个左右的记录，相当于4个G。

如果满了后要清，否则就不能再运行别的DDL语句。

三 Mysql日志分析工具

3.1 mysqldumps low

mysql官方提供的慢查询日志分析工具

主要功能包括统计不同慢sql的

出现次数(COUNT)

执行耗费的平均时间和累计总耗费时间(time)

等待锁耗费的时间(lock)

发送给客户端的行总数(rowS)

扫描的行总数( rows)

sql语句本身

3.2 mysqlbinlog:

binlog官方的二进制日志分析工具

3.3 imryprofi:

对于只想看sql语句及执行次数的用户来说，比较推荐。

3.4 mysql - explain-slow-log:

德国人写的一个perl 脚本，功能上有点瑕疵。不推荐使用。

3.5 mysql-LOG- filter:

喜欢简洁报表的，推荐使用一下。

四 mysqlsla:

hackmysql.com推出的一款日志分析工具(该网站还维护了mysqlreport, mysq1idxchk 等比较实用的mysql工具)。整体来说，功能非常强大.输出的数据报表非常有利于分析慢查询的原因，包括执行频率、数据量、查询消耗等。

1mysqlsla工具介绍

整体来说，功能非常强大。

输出的数据报表非常有利于分析慢查询的原因，包括执行频率、数据量、查询消耗等。

2mysqlsla解决的问题

分析日志: mysql所有日志，包括自定义日志，

general LOG : 分析业务的逻辑，业务特点

slow log:找瓶颈

binary log:恢复数据

3mysqlsla处理日志的流程

加载日志>解析日志>过滤日志>排序>出报告>重演

核心功能:过滤日志、出报告

4.1 安装mysqlsla

tar -vxf mysqlsla-2.03.tar.tar

perl Makefile.PL

make

make install

4.2 mysqlsla使用

mysqlsla --help

man mysqlsla for help or visit

man mysqlsla

4.2.1分析慢查询日志

mysqlsla -lt slow /mysql/log/3306/itpuxdb-query.err

Report for slow logs: /mysql/log/3306/itpuxdb-query.err

8 queries total, 4 unique ##这个日志里面又8个查询，去重后有4个

Sorted by 't_sum'

Grand Totals: Time 0 s, Lock 0 s, Rows sent 1.26k, Rows Examined 4.06k ##执行的时间，锁定的时间，返回行数，总体扫描的行数。

______________________________________________________________________ 001 ___

Count : 3 (37.50%) #SQL执行的次数

Time : 14.752 ms total, 4.917 ms avg, 3.36 ms to 6.268 ms max (51.22%) ##这个SQL慢的百分比-再这个日志里面

Lock Time (s) : 8.02 ms total, 2.673 ms avg, 1.541 ms to 3.765 ms max (90.28%) ##这个SQL锁定的百分比-再这个日志里面

Rows sent : 3 avg, 3 to 3 max (0.71%)

Rows examined : 485 avg, 368 to 718 max (35.82%)

Database : mysql

Users :

root@ 192.168.198.1 : 100.00% (3) of query, 100.00% (8) of all users

Query abstract:

SET timestamp=N; SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'S' UNION SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'S' UNION SELECT COUNT(*) FROM information_schema.routines WHERE routine_schema = 'S';

4.2.2分析慢查询日志执行时间最长的前10条语句

mysqlsla -lt slow --top 10 /mysql/log/3306/itpuxdb-query.err

4.2.3分析慢查询日志按照总时间t_sum进行排序，按照总执行次数c_sum进行排序。

mysqlsla -lt slow --top 2 --sort t_sum /mysql/log/3306/itpuxdb-query.err

mysqlsla -lt slow --top 2 --sort c_sum /mysql/log/3306/itpuxdb-query.err

4.2.4 将输出结果放在文件中

mysqlsla -lt slow --top 2 --sort t_sum /mysql/log/3306/itpuxdb-query.err

4.2.5 只提取t_center数据库，select和update的慢查询

mysqlsla -lt slow -sf "+select,update" --top 2 -db t_center --sort t_sum /mysql/log/3306/itpuxdb-query.err

五 pt-query-digest:

pt-query-digest是用于分析mysql慢查询的-一个工具，它可以分析binlog、General log、slowlog,也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

5.1 pt-query-digest 安装

yum install -y perl-CPAN

yum install -y *perl-Time-HiRes*

tar -zxvf percona-toolkit-3.0.8_x86_64.tar.gz

perl Makefile.PL PREFIX=/app/percona-toolkit308

make

make install

vim ~/.bash_profile

PATH=$PATH:/mysql/app/mysql/bin:/app/percona-toolkit308/bin:$HOME/bin

pt-query-digest --help

5.2 pt-query-digest 查看总体报告分析

pt-query-digest /mysql/log/3306/itpuxdb-query.err

5.2.1第一部分

# 90ms user time, 30ms system time, 24.14M rss, 173.39M vsz

# Current date: Fri Aug 7 07:04:07 2020

# Hostname: mysql5.7s

# Files: /mysql/log/3306/itpuxdb-query.err

# Overall: 27 total, 12 unique, 0.00 QPS, 0.00x concurrency ______________ #总过27个事务，去重一个12个

# Time range: 2020-07-14T02:34:25 to 2020-08-07T01:23:11

# Attribute total min max avg 95% stddev median

# ============ ======= ======= ======= ======= ======= ======= =======

# Exec time 201ms 531us 67ms 7ms 13ms 13ms 3ms #总体执行时间

# Lock time 29ms 62us 10ms 1ms 4ms 2ms 167us # 锁定时间

# Rows sent 14.95k 0 9.77k 567 964.41 1.75k 59.77 # 返回数据

# Rows examine 19.44k 0 9.77k 737.30 964.41 1.72k 183.58 # 总体查询数据

# Query size 4.03k 22 283 152.89 246.02 77.15 151.03

5.2.2第二部分分组统计结果

# Profile

# Rank Query ID Response time Calls R/Call V/M Item

# ==== ================== ============= ===== ====== ===== ===============

# 1 0x088884A136067321 0.0672 33.5% 2 0.0336 0.07

# 2 0xCE3BF5155081A573 0.0395 19.7% 1 0.0395 0.00 SELECT itpux_m?

# 3 0x98947CCF9160CAC9 0.0351 17.5% 7 0.0050 0.00 SELECT UNION information_schema.TABLES information_schema.COLUMNS information_schema.ROUTINES

# 4 0xA74C738BC1E2EBEB 0.0162 8.1% 7 0.0023 0.00 SELECT information_schema.COLUMNS

# 5 0xB47244168C36E454 0.0130 6.5% 1 0.0130 0.00 SELECT itpuxdb.itpux_obj

# 6 0x30D50836365226DE 0.0115 5.7% 1 0.0115 0.00 SELECT itpuxdb.itpux_yg

# 7 0x19300FCBF95558A0 0.0089 4.4% 1 0.0089 0.00 SELECT itpuxdb.itpux_m?

# MISC 0xMISC 0.0092 4.6% 7 0.0013 0.0 <5 ITEMS>

5.2.3 第三部分每个查询的详细分析

# Query 4: 0.00 QPS, 0.00x concurrency, ID 0xA74C738BC1E2EBEB at byte 3411

# Scores: V/M = 0.00

# Time range: 2020-07-14T02:34:25 to 2020-08-07T01:23:11

# Attribute pct total min max avg 95% stddev median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count 25 7

# Exec time 8 16ms 785us 6ms 2ms 6ms 2ms 1ms

# Lock time 3 1ms 141us 186us 164us 185us 15us 167us

# Rows sent 10 1.53k 96 606 223.14 592.07 173.87 92.72

# Rows examine 15 3.05k 192 1.18k 446.29 1.14k 344.26 183.58

# Query size 26 1.06k 152 165 155 158.58 2.64 151.03

# String:

# Databases itpuxdb (4/57%), mysql (2/28%)... 1 more

# Hosts 192.168.198.1

# Users root

# Query_time distribution

# 1us

# 10us

# 100us #########################

# 1ms ################################################################

# 10ms

# 100ms

# 1s

# 10s+

# Tables

# SHOW TABLE STATUS FROM `information_schema` LIKE 'COLUMNS'\G

# SHOW CREATE TABLE `information_schema`.`COLUMNS`\G

# EXPLAIN /*!50100 PARTITIONS*/

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'information_schema' ORDER BY TABLE_SCHEMA, TABLE_NAME\G

5.3直接分析慢查询文件:

pt-query-digest /mysql/log/3306/itpuxdb-query.err >slow_report.log

5.4 分析最近12小时内的查询:

pt-query-digest --since=12h /mysql/log/3306/itpuxdb-query.err >12hslow_report.log

5.5.分析指定时间范围内的查询:

pt-query-digest --since '2020-08-01 00:00:00' --until '2020-08-09 00:01:00' /mysql/log/3306/itpuxdb-query.err >180slow_report.log

5.6 针对某个用户的慢查询

pt-query-digest --since '2020-08-01 00:00:00' --until '2020-08-09 00:01:00' --filter '($event->{user} || "" ) =~ m/^root/i' /mysql/log/3306/itpuxdb-query.err >00slow_report.log

5.7 分析指含有select语句的慢查询

pt-query-digest --since '2020-08-01 00:00:00' --until '2020-08-09 00:01:00' --filter '($event->{fingerprint} || "" ) =~ m/^select/i' /mysql/log/3306/itpuxdb-query.err >selectslow_report.log

5.8 查询全表扫描或者full join的慢查询

pt-query-digest --since '2020-08-01 00:00:00' --until '2020-08-11 00:01:00' --filter '(($event->{FULL_scan} || "" ) eq "yes") || (($event->{FULL_join} || "" ) eq "yes")' /mysql/log/3306/itpuxdb-query.err >fullslow_report.log

5.9 分析general LOG

pt-query-digest --type=genlog /mysql/log/3306/itpuxdb-query.err >genslow_report.log

5.10分析binlog

mysqlbinlog BIN-log.000001 > BIN-log.000001.sql

pt-query-digest --type=binlog BIN-log.000001.sql >binslow_report.log

七其他文件介绍（socket/pid/表结构/Innodb）

7.1 socket套接字文件

socket: ip+端口(本地IP: port ,远程IP: port)

进行网络通信必需5种信息:协议、本地IP、本地协议端口、远程IP、远程协议端口

mysql> show variables like 'socket%';

+---------------+-----------------------------+

| Variable_name | Value |

+---------------+-----------------------------+

| socket | /mysql/data/3306/mysql.sock |

+---------------+-----------------------------+

1 row in set (0.00 sec)

如果主机上有多个实例，通过连接socket可以连接相应的实例

mysql -uroot -proot -S /mysql/data/3306/mysql.sock

建立连接的大致过程:

1)建立socket套接字

2)给socket套接字赋予地址

3)建立socket连接

7.2 PID文件

Mysql数据库启动的时候，

cat /mysql/data/3306/mysql.pid

3716

ps -ef | grep mysql

mysql 3716 2538 0 01:58 pts/0 00:00:03 /mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data --plugin-dir=/mysql/app/mysql/lib/plugin --user=mysql --log-error=/mysql/log/3306/itpuxdb-error.err --open-files-limit=65536 --pid-file=/mysql/data/3306/mysql.pid --socket=/mysql/data/3306/mysql.sock --port=3306

show variables like '%pid_file%';

+---------------+----------------------------+

| Variable_name | Value |

+---------------+----------------------------+

| pid_file | /mysql/data/3306/mysql.pid |

+---------------+----------------------------+

1 row in set (0.00 sec)