在前一节中我们学习了 mysql 的物理文件组成,接下来我们来学习 mysql 的逻辑模块组成。
逻辑模块组成:
MySQL 逻辑结构可以看成是二层架构,第一层我们通常叫做 SQL Layer,在 MySQL 数据库系
统处理底层数据之前的所有工作都是在这一层完成的,包括权限判断,sql 解析,执行计划
优化,query cache 的处理等等;第二层就是存储引擎层,我们通常叫做 StorageEngine Layer,
也就是底层数据存取操作实现部分,由多种存储引擎共同组成。所以,可以用如下一张最简
单的架构示意图来表示 MySQL 的基本架构,如图所示:
“--prompt=name”参数对于做运维的人来说是一个非常重要的参数选项,其主要功能是定
制自己的 mysql 提示符的显示内容。在默认情况下,我们通过 mysql 登入到数据库之后,mysql
的提示符只是一个很简单的内容”mysql>“,没有其他任何附加信息。非常幸运的是 mysql
通过“--prompt=name”参数给我们提供了自定义提示信息的办法,可以通过配置显示登入
的主机地址,登录用户名,当前时间,当前数据库 schema,MySQL Server 的一些信
息等等。我个人强烈建议将登录主机名,登录用户名和所在的 schema 这三项加入提示内容,
因为当大家手边管理的 MySQL 越来越多,操作越来越频繁的时候,非常容易因为操作的时
候没有太在意自己当前所处的环境而造成在错误的环境执行了错误的命令并造成严重后果
的情况。如果我们在提示内容中加入了这几项之后,至少可以更方便的提醒自己当前所处环
境,以尽量减少犯错误的概率。
个人强烈建议提示符定义: "\\u@\\h : \\d \\r:\\m:\\s> ",显示效果:
切换数据库:
提示符解释:
\u 表示用户名, \h 表示主机名, \d 表示当前数据库,\r 小时(12 小时制),\m 分种,\s
秒,\R The current time, in 24-hour military time (0–23)
“--tee=name”参数也是对运维人员非常有用的参数选项,用来告诉 mysql,将所有输入和
输出内容都记录进文件。在我们一些较大维护变更的时候,为了方便被查,最好是将整个操
作过程的所有输入和输出内容都保存下来。
假如 mysql 命令行状态下,要进行大量的交互操作,其实可以把这些操作记录在 log 中进行
审计,很简单 mysql -u root -p --tee=/path/xxxx.log
也可以 在服务器上的/etc/my.cnf 中的[client]加入
tee =/tmp/client_mysql.log 即可.
注:若没有[client]就添加即可
或者在 mysql>提示符下执行下面的命令
mysql 其他参数选项可以通过 MySQL 官方参考手册查阅,也可以通过执行“mysql --help”
或 man mysql 得到帮助信息之后通过自行实验来做进一步的深刻认识。
2、mysqladmin
Usage: mysqladmin [OPTIONS] command command ...
mysqadmin,顾名思义,提供的功能都是与 MySQL 管理相关的各种功能。如 MySQL Server
状态检查,各种统计信息的 flush,创建/删除数据库,关闭 MySQL Server 等等。mysqladmin
所能做的事情,虽然大部分都可以通过 mysql 连接登录上 MySQL Server 之后来完成,但是
大部分通过 mysqladmin 来完成操作会更简单更方便。这里将介绍一下经常使用到的几个常
用功能:
ping 命令可以很容易检测 MySQL Server 是否还能正常提供服务
mysql 本机上测试:
在其他主机上测试 mysql server 是否正常提供服务
注 1:地址 192.168.1.1 是 mysql server 的 ip
注 2:mysql server 的防火墙要允许 3306/tcp 通信
注 3:在 mysql server 上创建授权用户
status 命令可以获取当前 MySQL Server 的几个基本的状态值:
mysqladmin status 命令结果有下述列
Uptime:是 mysql 服务器运行的秒数。
Threads:活跃线程的数量即开启的会话数。
Questions: 服务器启动以来客户的问题(查询)数目 (只要跟 mysql 作交互,不管查询表,
还是查询服务器状态都记一次)。
Slow queries:是慢查询的数量。
Opens:mysql 已经打开的数据库表的数量
Flush tables: mysql 已经执行的 flush tables,refresh 和> 注:flush tables //刷新表(清除缓存)
reload 重载授权表
refresh 洗掉所有表并关闭和打开日志文件
open:打开数据库的表的数量,以服务器启动开始。
Queries per second avg:select 语句平均查询时间
Memory in use 分配的内存(只有在 MySQL 用--with-debug 编译时可用)
Max memory used 分配的最大内存(只有在 MySQL 用--with-debug 编译时可用)
processlist 获取当前数据库的连接线程信息:
监控 mysql 进程运行状态:
上面的这三个功能在一些简单监控脚本中经常使用到的。
mysqladmin 其他参数选项可以通过执行“mysqladmin --help”或 man mysqladmin 得到帮助
信息。
编写一个简单的 mysql 监控脚本,内容如下:
附加知识点 1:
Mysql 的系统数据库:
1) INFORMATION_SCHEMA 数据字典:此数据库存贮了其他所有数据库的信息(元数据)。
元数据是关于数据的数据,如 database name 或 table name,列的数据类型,或访问权
限等。
INFORMATION_SCHEMA 库的主要系统表
TABLES 表:提供了关于数据库中的表和视图的信息。(Table_schema 字段代表 数据表所属的数据
库名)
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='数据库名';
COLUMNS 表:提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='数据库名
‘' AND TABLE_NAME='表名'
TABLE_CONSTRAINTS 表:存储主键约束、外键约束、唯一约束、check 约束。各字段的说明
信息
ELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA=' 数 据 库 名
' AND TABLE_NAME='表名'
STATISTICS 表:提供了关于表索引的信息。
SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=' 数 据 库 名
' AND TABLE_NAME='表名'
2)performance_schema 性能字典,此数据库为数据库性能优化提供重要的参考信息
3)MYSQL 数据库: 该数据库也是个核心数据库,存储用户的权限信息与帮助信息。
4)MySQL5.7 提供了 sys 系统数据库。 sys 数据库里面包含了一系列的存储过程、自定义函
数以及视图来帮助我们快速的了解系统的元数据信息。sys 系统数据库结合了
information_schema 和 performance_schema 的相关数据,让我们更加容易的检索元数据。
附加知识点 2:
未经允许不得转载传播--陈英宏
博客地址:hongge.blog.51cto.com
mysql 有关 show 的用法
SHOW DATABASES 列出 MySQL Server 上的数据库。
SHOW TABLES [FROM db_name]列出数据库中的表。
SHOW TABLE STATUS [FROM db_name]列出数据库的表信息,比较详细。
SHOW COLUMNS FROM tbl_name [FROM db_name]列出表的列信息,
同 SHOW FIELDS FROM tbl_name [FROM db_name],
DESCRIBE tbl_name [col_name]。
SHOW FULL COLUMNS FROM tbl_name [FROM db_name] 列 出 表 的 列 信 息 , 比 较 详 细 ,
同 SHOW FULL FIELDS FROM tbl_name [FROM db_name]。
SHOW INDEX FROM tbl_name [FROM db_name]列出表的索引信息。
SHOW STATUS 列出 Server 的状态信息。
SHOW VARIABLES 列出 MySQL 系参数值
SHOW PROCESSLIST 查看当前 mysql 查询进程
SHOW GRANTS FOR user 列出用户的授权命令
3、mysqldump:
这个工具其功能就是将 MySQL Server 中的数据以 SQL 语句的形式从数据库中 dump 成文本
文件。mysqldump 是做为 MySQL 的一种逻辑备份工具
4、mysqlbinlog
mysqlbinlog 程序的主要功能就是分析 MySQL Server 所产生的二进制日志(也就是 binlog)。
通过 mysqlbinlog,我们可以解析出 binlog 中指定时间段或者指定日志起始和结束位置的内
容解析成 SQL 语句。
三 、Mysqlslap 性能测试 MySQL 二 种存储引擎
mysqlslap 是 mysql 自带的基准测试工具,优点:查询数据,语法简单,灵活容易使用.该工具可以
模拟多个客户端同时并发的向服务器发出查询更新,给出了性能测试数据而且提供了多种引
擎的性能比较.msqlslap 为 mysql 性能优化前后提供了直观的验证依据,建议系统运维和 DBA
人员应该掌握一些常见的压力测试工具,才能准确的掌握线上数据库支撑的用户流量上限及
其抗压性等问题。
现在看一下这个压力测试工具 mysqlslap,关于他的选项手册上以及--help 介绍的很详细。
这里解释一下一些常用的选项。
--concurrency 代表并发数量,多个可以用逗号隔开。例如:concurrency=50,100,200
--engines 代表要测试的引擎,可以有多个,用分隔符隔开。
--iterations 代表要运行这些测试多少次,即运行多少次后,得到结果。
--auto-generate-sql 代表用系统自己生成的 SQL 脚本来测试。
--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的
(read,write,update,mixed)
--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数
/并发数来计算。比如倒数第二个结果 2=200/100。
--debug-info 代表要额外输出 CPU 以及内存的相关信息(注:只有在 MySQL 用--with-debug
编译时可)。
--number-int-cols 代表测试表中的 INTEGER 类型的属性有几个。
--number-char-cols 代表测试表的 char 类型字段的数量。
--create-schema 代表自己定义的模式(在 MySQL 中也就是库即创建测试的数据库)。
--query 代表自己的 SQL 脚本。
--only-print 如果只想打印看看 SQL 语句是什么,可以用这个选项。
--csv=name生产CSV格式数据文件
查看 Mysql 数据库默认最大连接数
注:通过 mysqlslap 工具对 mysql server 进行压力测试,可以通过--concurrency、
--number-of-queries 等选项的值查看每次测试的结果,通过反复测试、优化得出 mysql server
的最大并发数。
如果 mysqlslap 工具输出结果为 Segmentation fault (core dumped)基本表示走超出 mysql
server 的负载。 谢谢观看,真心的希望能帮到您!