MySQL数据库文件管理
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
[[email protected] ~]# 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 [email protected]@ 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 @@[email protected]@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
[[email protected] 3306]# service mysql restart
Shutting down MySQL.. [确定]
Starting MySQL. [确定]
mysql> show master status;
Empty set (0.00 sec)
没有开
show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[[email protected] 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:
log_ output
show variables like ' %log_ output%' ;
建议:file,还有一一个值是table
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 http://hackmysql.com/mysqlsla
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 :
[email protected] 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)
7.3 Mysql表结构文件
再mysql数据库里面
-rw-r----- 1 mysql mysql 9350 7月 20 00:14 dd.frm
-rw-r----- 1 mysql mysql 147456 7月 20 00:14 dd.ibd
innodb:
.frm:表结构文件,记录表的结构定义。
ibd:表的数据和索引信息。
7.4 innodb存储引擎相关的文件
7.4.1 表空间
表空间文件:
共享表空间与独立表空间:
这个参数为off/0,就是共享表空间-所有的数据和索引都放在一个文件,也可以是多个文件。
这个参数为on/1,就是独立表空间-每个表一个表空间, 每个表的数据和索引只存在自己的表空间中。
show variables like 'innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
7.5 数据文件和临时文件
show variables like '%innodb%data%%';
+----------------------------+----------------------------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------------------------+
| innodb_data_file_path | ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G |
| innodb_data_home_dir | |
| innodb_stats_on_metadata | OFF |
| innodb_temp_data_file_path | ibtmp1:200M:autoextend:max:20G |
+----------------------------+----------------------------------------------------------+
第一个文件200m,第二个文件200m,第三个文件200m,最大5G。