数据库引擎

了解MySql必须牢牢记住其体系结构图，Mysql是由SQL接口，解析器，优化器，缓存，存储引擎组成的。

MySQL体系结构图

• Connectors：指的是不同语言与SQL的交互
• Management Services & Utilities ：系统管理和控制工具
• Connection Pool ：连接池，管理缓冲用户连接、线程处理等需要缓存的需求
• SQL interface: SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select from 就是调用SQL interface
• Parser
  ：解析器。
  SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。主要功能：
  • 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的
  • 如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的
• Optimizer:查询优化器。
  SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。用一个例子就可以理解： select uid,name from user where gender = 1;这个select查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender过滤。这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤；将这两个查询条件联接起来生成最终查询结果。
• Caches & Buffers：查询缓存。
  如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
• Engines：存储引擎。
  存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）,现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB。MySQL 8默认的存储引擎是InnoDB。MySQL也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎都是允许的。

常用存储引擎

简单来说，存储引擎就是指表的类型以及表在计算机上的存储方式。存储引擎其实就是如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。

存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，Oracle有OLTP(On-Line Transaction Processing 联机事务处理，强调的是内存效率，实时性比较高)和OLAP(On-Line Analytical Processing 联机分析处理，强调的是数据分析，响应速度要求没那么高)模式的区分。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。

MySQL中的数据用各种不同的技术存储在文件（或内存）中，这些技术中的每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。在MySQL中的存储引擎有很多种，可以通过“SHOW ENGINES”语句来查看。

InnoDB存储引擎

InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。MySQL默认的存储引擎就是InnoDB。InnoDB是一个平衡了高可用和高性能的通用存储引擎。

优势

• 保护用户数据：DML操作，通过事务来遵循ACID模型
• 高性能：行级锁，一致性读取
• 最小化主键查找的IO：聚簇索引
• 数据完整性：外键
• 崩溃恢复
• 在主内存缓存索引数据和缓存表
• 外键
• 校验机制
• 只要你在设计表时选择了合适的主键，主键列where、order by、group by、join操作会被自动优化
• 自适应哈希索引

InnoDB架构

InnoDB的整体架构可以分为两个部分：内存架构、磁盘架构

InnoDB在内存中主要包括下面几个部分：缓冲池、Change缓冲区、自适应哈希索引、Log缓冲区

• 缓冲池：存储访问时的缓存表和索引数据。在专用服务器上，通常会为缓冲池分配80%的物理内存。可以快速从内存获取数据，加快了处理速度。
• Change Buffer：Change Buffer是一种特殊的数据结构，当某些页面不在缓冲池中，缓存会改变二级索引page，这可能会造成insert，update，delete（DML）操作会与其他从缓冲池中的读操作加载的page合并。不同于聚簇索引，二级索引通常不唯一，同时二级索引的插入相对随机。同时，为了避免频繁的IO随机读写，当更新和删除操作时，并不会立即写入磁盘，而是会选择系统空闲时定期进行写入磁盘的操作。Change Buffer在内存中，是缓冲池中的一部分，在磁盘中，是系统表空间的一部分。
• 自适应哈希索引：InnoDB可以基于搜索的模式，使用索引键前缀构建哈希索引，也就是说，这个哈希索引是由经常访问的索引页面构建的。在不牺牲事务特性和可靠性的基础上，使InnoDB像一个内存数据库一样工作，也就是说在一定情况下，通过这种哈希索引的方式会提升查询速度。InnoDB中存在一种监视索引搜索的机制，但这种机制有时反倒带来额外的开销。所以在选择是否使用哈希索引前，可能需要做好基准测试，否则还是建议禁用。

InnoDB在磁盘中存储的信息包括：各种表空间（TableSpace），Redo Log。

InnoDB对数据存储方式的设计，主要是基于表空间的形式。表空间的种类如下图所示：

使用InnoDB表的限制，来自MySQL官方文档：

1. 一个表最多包含1017列，表示并没有创建过这样多列的表
2. 一个表最多可以创建64个二级索引
3. 索引键前缀长度限制为3072字节

• Undo Log：Undo Log是与事务读写关联的，主要作用在事务回滚和多版本并发控制中。Undo Log在回滚段中存储，回滚段在Undo表空间和全局临时表空间中。Undo log被分为insert undo log 和update undo log。Insert undo log 只在事务回滚时需要，一旦事务提交就被丢弃。Update undo log 也被用在一致性读，在一致性读中可能需要update undo log的信息来生成该行数据早期的版本。
• Redo Log：也就是ib_logfile0和ib_logfile1两个文件。这里结合的是MySQL的WAL（Write-Ahead Logging）也就是先写日志，再写磁盘，具体过程是下面这样：当有一条记录要更新，先将记录写到redo log，并更新内存，InnoDB会在空闲的时候，把操作记录更新到磁盘。

官方建议的最佳实践

• 指定主键
• 外键
• 关闭自动提交
• DML的事务进行分组
• 不要用lock table，如果希望某行的独占写，用select … for update
• 启用innode_file_per_table

MyISAM

在MySQL 5.1 及之前的版本，MyISAM 是默认的存储引擎。
适用场景：查询效率很高，适合大量读操作的场景。

• 每个表创建3个文件：
  • .frm文件：存放表结构定义信息
  • .MYD文件：存储表数据
  • .MYI文件：存放索引
• 数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的
• 所有数据指都按小字节（low byte first）存储，因此独立于操作系统(可移植性)，但没有明显降低速度，只是需要多处理一下对齐问题，况且获取列值所花的时间不是最主要的
• 所有数字键都按大小字节(hign byte first)存储，利于压缩
• BLOB和TEXT列可以创建索引
• 每一个character列可以使用不同的字符编码
• 会保存表的具体行数，count(*)时效率高，不用遍历全表。
• 使用B树索引,string索引会被压缩，当string是索引第一项时还会压缩前缀
• 支持真正的变长字段varchar
• 支持并发的insert
• 加锁与并发：对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录（这被称为并发插入,Concurrent Insert）
• 不支持事务处理
• 非聚簇索引，支持全文索引，索引数据和表数据分开存储。
• 支持3种不同的存储格式：
  • 静态表（固定长度）:长度固定，优点是存储非常迅速，容易缓存，出现故障时容易恢复；缺点是占用的空间通常比动态表多；
  • 动态表：记录长度不固定，这样存储的优点是占用的空间相对小，但是频繁的更新和删除记录会产生碎片，需要定时执行optimize table语句或myisamchk -r命令来改善性能。出现故障时恢复相对困难；
  • 压缩表：占用非常小的磁盘空间，因为每个记录都是被单独压缩的，所以只有非常小的访问开支。压缩表由myisamchk工具创建。

MEMORY

适用场景：存储临时、不重要的数据，例如作为缓存，适合大量读的情形

• 不支持变长的数据类型（including BLOB and TEXT）
• 不支持外键约束
• 不支持压缩
• 不支持MVCC
• 支持哈希索引和B树索引，不支持全文索引和T树索引
• mysql服务关闭或重启，数据会消失（表还在）
• 数据量不能超过内存大小
• 性能限制
• 单线程执行
• 表更新用表级锁（高并发读写情形下，表级锁严重降低性能，还不如InnoDB快）
• 内置的临时表（也在内存中）太大时会自动转成磁盘存储，但用户自创的内存表永远不会转化
• 可以从persistent data source装载数据到内存表

Characteristics of MEMORY Tables（内存表特性）

被删除的row会放进一个链表(不会回收内存)，等插入新数据时拿出来复用，只有整个表被删除后才会回收内存。采用定长的行存储，即使是varchar也是定长存储的。

默认使用哈希索引，并且允许非唯一的哈希索引(但如果字段含大量重复值，性能会很低，这种情况最好用B树索引)，被索引字段可以有NULL。

Managing Memory Use

# 释放内存
DELETE * FROM table_name;        # 释放所有row占用的内存
TRUNCATE TABLE table_name;       # 释放所有row占用的内存
DROP TABLE table_name;           # 释放整个table占用的内存
ALTER TABLE table_name ENGINE=MEMORY;   # 释放deleted row链表占用的内存
SET max_heap_table_size = 1024*1024*2;# 设定单个内存表的空间限制，单位是Byte，这里是2MB，默认16MB

ARCHIVE

适用场景：作为仓库，存储大量的独立的作为历史记录的数据（插入速度快，但查询支持较差）

• 不支持索引
• 没有存储大小限制（InnoDB是64TB)
• 能很好的压缩数据
• 使用行级锁
• 支持INSERT、REPLACE、SELECT,不支持DELETE、UPDATE

存储

使用zlib无损数据压缩。数据insert后即被压缩，放在一个压缩缓冲区中，select操作会导致清空缓冲区，此时数据被真正存储。支持批处理insert。

读取

行会根据需要解压，不设缓冲。select会导致全表扫描。select是读一致性的。大量查询会影响压缩。使用REPAIR TABLE或OPTIMIZE TABLE能获取更好的压缩。

如何选择MySQL存储引擎

使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

• 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
• 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
• 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

操作配置存储引擎

1. 查看mysql所支持的存储引擎，以及从中得到mysql默认的存储引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

1. 查看mysql默认的存储引擎

mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.01 sec)

1. 查看具体某一个表所使用的存储引擎

mysql> show create table employees.employees;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                        |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

1. 准确查看某个数据库中的某一表所使用的存储引擎

mysql> show table status from employees where name='employees';
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| employees | InnoDB |      10 | Dynamic    | 299246 |             50 |    15220736 |               0 |            0 |   4194304 |           NULL | 2019-08-15 16:28:35 | NULL        | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.02 sec)

1. 创建表的时候可以指定存储引擎

mysql> create table mytable (name varchar(32)) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

1. 可以修改表的存储引擎。

mysql> alter table mytable engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

会导致大量的IO问题,不建议使用;存储引擎的转换会导致原有表的特性消失,例如外键等;
\7. 修改配置文件，指定默认存储引擎

[root@www ~]# vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB

1. 修改系统变量

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)
mysql> set default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.01 sec)

MySQL自带工具介绍

MySQL数据库不仅提供了数据库的服务器端应用程序，同时还提供了大量的客户端工具程序，如mysql、mysqladmin、mysqldump等等

mysql命令

mysql命令是使用最多的一个命令工具了，为用户提供一个命令行接口来操作管理MySQL服务器。
语法格式：

mysql [options] db_name

例如：

[root@www testdb]# mysql -e 'select user,host from user' mysql -uroot -pCom.123456
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| app_developer    | %         |
| app_read_only    | %         |
| app_writes       | %         |
| developer        | %         |
| emp_developer    | %         |
| emp_read_only    | %         |
| emp_read_write   | %         |
| emp_writes       | %         |
| mary             | %         |
| dbadmin          | localhost |
| lisi             | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| zhangsan         | localhost |
+------------------+-----------+

可以执行mysql —help来获取基本帮助信息

下面主要介绍一些在运维过程中会用到的相关选项：

-e参数：告诉MySQL执行-e后面的命令，而不是通过mysql连接登录到MySQL服务器。此参数在写一些基本的MySQL检查和监控脚本中非常有用。

例1:

通过binlog_cache_use以及binlog_cache_disk_use来分析设置的binlog_cache_size是否足够

[root@www testdb]# mysql -uroot -pCom.123456 -e 'show status like "%binlog_cache%"'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 0     |
+-----------------------+-------+

例2

通过脚本创建数据库、表及对表进行增、删、改、查操作。

脚本内容如下：

[root@www script]# vim mysql1.sh 
#!/bin/bash
HOSTNAME='127.0.0.1'
PORT=3306
USERNAME='root'
PASSWORD='Com.123456'
DBNAME='test_db'
TABLENAME='tb1'
#create database
create_db_sql="create database if not exists ${DBNAME}"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME} (name varchar(20),id int default 0)"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
#insert data to table
insert_sql="insert into ${TABLENAME} values ('tom',1)"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${DBNAME} -e "${insert_sql}"
#select data
select_sql="select * from ${TABLENAME}"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${DBNAME} -e "${select_sql}"
#update data
update_sql="update ${TABLENAME} set id=3"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${DBNAME} -e "${update_sql}"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${DBNAME} -e "${select_sql}"
#delete data
delete_sql="delete from ${TABLENAME}"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${DBNAME} -e "${delete_sql}"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}  ${DBNAME} -e "${select_sql}"

创建用户lisi，并授权

[root@www ~]# mysql -uroot -pCom.123456 -e "create user if not exists 'lisi'@'192.168.154.137' identified by 'Com.123456'"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@www ~]# mysql -uroot -pCom.123456 -e "grant all on test_db.* to 'lisi'@'192.168.154.137'"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@www ~]# mysql -uroot -pCom.123456 -e "show grants for 'lisi'@'192.168.154.137'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------------------------------------------------+
| Grants for lisi@192.168.154.137                                 |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `lisi`@`192.168.154.137`                  |
| GRANT ALL PRIVILEGES ON `test_db`.* TO `lisi`@`192.168.154.137` |
+-----------------------------------------------------------------+

测试lisi用户连接数据库

[root@www ~]# mysql -ulisi -pCom.123456 -h 192.168.154.137
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

赋予脚本可执行权限

[root@www script]# chmod +x /root/mysql/script/mysql1.sh

执行脚本

[root@www script]# ./mysql1.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| name | id   |
+------+------+
| tom  |    1 |
+------+------+
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| name | id   |
+------+------+
| tom  |    3 |
+------+------+
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.

-E,–vertical参数：使用此参数，登入后所有查询结果都将以纵列显示，效果和在sql语句后以‘\G’结尾一样。

[root@www script]# mysql -uroot -pCom.123456 -E -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Database: bank
*************************** 2. row ***************************
Database: employees
*************************** 3. row ***************************
Database: information_schema
*************************** 4. row ***************************
Database: mysql
*************************** 5. row ***************************
Database: performance_schema
*************************** 6. row ***************************
Database: sys
*************************** 7. row ***************************
Database: test_db
*************************** 8. row ***************************
Database: testdb

-H,–html参数：以HTML格式显示

-X,–xml参数：以xml格式显示

–prompt=name参数：对运维人员来说是一个非常重要的参数选项。主要功能是定制自己的mysql提示符的显示内容，可以通过配置显示登入的主机地址、登录用户名、当前时间、当前数据库schema、MySQL Server的一些信息等。这样就可以时刻看到自己的所处环境，减少出错的几率，建议登录主机名、登录用户名和所在的schema这3项必须加入提示信息。

[root@www mysql]# mysql -h 192.168.154.137 -ulisi -pCom.123456 --prompt="\\u@\\h:\\d\\R:\\m:\\s>"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
lisi@192.168.154.137:(none)17:46:51>use test_db;
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
lisi@192.168.154.137:test_db17:47:05>

• \u 表示用户名
• \h 表示主机名
• \d 表示当前数据库
• \R 小时（24小时制）
• \r 小时（12小时制）
• \m 分钟
• \s 秒

–tee=name参数：也是对运维人员非常有用的参数选项，可以将所有操作记录到文件中，方便查询。

[root@www mysql]# mysql -h 192.168.154.137 -ulisi -pCom.123456 --prompt="\\u@\\h:\\d\\R:\\m:\\s>" --tee=/tmp/mysql.log
mysql: [Warning] Using a password on the command line interface can be insecure.
Logging to file '/tmp/mysql.log'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
lisi@192.168.154.137:(none)17:51:59>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_db            |
+--------------------+
2 rows in set (0.00 sec)
lisi@192.168.154.137:(none)17:52:02>use test_db;
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
lisi@192.168.154.137:test_db17:52:07>quit
Bye
#查询记录文件
[root@www mysql]# cat /tmp/mysql.log 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
lisi@192.168.154.137:(none)17:51:59>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_db            |
+--------------------+
2 rows in set (0.00 sec)
lisi@192.168.154.137:(none)17:52:02>use test_db;
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
lisi@192.168.154.137:test_db17:52:07>quit

可以将其加入到配置文件中

[root@www mysql]# vim /etc/my.cnf
[client]
tee=/tmp/mysql_client.log

也可以在mysql>提示符下输入：

mysql> tee /mysql_client.log
Logging to file '/mysql_client.log'

其它选项可以参照MySQL官方手册进行查询：https://dev.mysql.com/doc/refman/8.0/en/programs-client.html

mysqladmin

语法：

 mysqladmin [options] command [command-options] [command [command-options]] ...

顾名思义，提供的功能都是与MySQL管理相关的各种功能。如MySQL Server状态检查，各种统计信息的flush，创建/删除数据库，关闭MySQL Server等等。mysqladmin所能做的事情，虽然大部分可以通过mysql连接登录上服务器后来完成，但是大部分通过mysqladmin来完成操作会更简单更方便。下面介绍一下经常使用到的几个功能：

ping命令

可以很容易检测MySQL Server是否还能正常提供服务。

mysql本机上测试：

[root@www ~]# mysqladmin -u root -pCom.123456 -h localhost ping
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive

在其它主机上测试MySQL Server是否正常提供服务

[root@www ~]# mysqladmin -u lisi -pCom.123456 -h 192.168.154.137 ping
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive

• 注1：地址192.168.154.137是MySQL Server的IP
• 注2：MySQL Server的防火墙要允许3306/tcp通信
• 注3：在MySQL Server上要创建授权用户

status

此命令可以获取当前MySQL Server的几个基本的状态值。

[root@www ~]# mysqladmin -u lisi -pCom.123456 -h 192.168.154.137 status
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Uptime: 2248  Threads: 2  Questions: 11  Slow queries: 0  Opens: 132  Flush tables: 3  Open tables: 36  Queries per second avg: 0.004

• Uptime：是MySQL服务器运行的时间（秒）
• Threads：活跃线程的数量，即开启的会话数
• Questions：服务器启动以来客户的问题（查询）数目，只要跟mysql做交互，不管查询表，还是查询服务器状态都记一次。
• Slow queries：慢查询的数量
• Opens：MySQL已经打开的数据库表的数量
• Flush tables：MySQL已经执行的flush tables（刷新表，清除缓存）,refresh（清洗所有表并关闭和打开日志文件）和reload（重载授权表）命令的数量
• Open tables：打开数据库的表的数量，以服务器启动开始
• Queries per second avg：select语句平均查询时间

processlist

获取当前数据库的连接线程信息

监控MySQL进程运行状态

[root@www ~]# mysqladmin -u root  -pCom.123456  processlist status
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+-----------------+-----------------------+----+---------+------+-----------------------------+------------------+
| Id | User            | Host                  | db | Command | Time | State                       | Info             |
+----+-----------------+-----------------------+----+---------+------+-----------------------------+------------------+
| 4  | event_scheduler | localhost             |    | Daemon  | 3309 | Waiting for next activation |                  |
| 17 | root            | localhost             |    | Sleep   | 80   |                             |                  |
| 23 | lisi            | 192.168.154.137:53832 |    | Sleep   | 6    |                             |                  |
| 24 | root            | localhost             |    | Query   | 0    | starting                    | show processlist |
+----+-----------------+-----------------------+----+---------+------+-----------------------------+------------------+
Uptime: 3312  Threads: 4  Questions: 30  Slow queries: 0  Opens: 132  Flush tables: 3  Open tables: 36  Queries per second avg: 0.009

上面的这三个功能在一些简单监控脚本中经常使用到。

mysqladmin其他参数选项可以通过执行‘mysqladmin —help’或‘man mysqladmin’得到帮助信息。

编写一个简单的MySQL监控脚本，内容如下：

[root@www script]# vim check_mysql.sh 
#/bin/bash
USERNAME='root'
PASSWORD='Com.123456'
HOST='localhost'
#检测mysql server是否正常提供服务
mysqladmin -u${USERNAME} -p${PASSWORD} -h${HOST} ping
#获取mysql当前的几个状态值
mysqladmin -u${USERNAME} -p${PASSWORD} -h${HOST} status
#获取数据库当前的连接信息
mysqladmin -u${USERNAME} -p${PASSWORD} -h${HOST} processlist
#获取当前数据库的连接数
mysql -u${USERNAME} -p${PASSWORD} -h${HOST} -BNe "select host,count(host) from processlist group by host;" information_schema
#显示mysql的uptime
mysql -u${USERNAME} -p${PASSWORD} -h${HOST} -e "show status like '%uptime%'" | awk '/ptime/ {calc = $NF / 3600;print $(NF-1),calc"Hour"}'
# 查看数据库的大小
mysql -u${USERNAME} -p${PASSWORD} -h${HOST} -e "select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;"

附加知识点1：MySQL的系统数据库

1、information_schema数据字典

此数据库存储了其它所有数据库的信息（元数据）。元数据是关于数据的数据，如database name或table name，列的数据类型，或访问权限等。

mysql> use information_schema;
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;

information_schema库的主要系统表：

• tables表：提供了关于数据库中的表和视图的信息。（table_schema字段代表数据表所属的数据库名）

select table_name from information_schema.tables where table_schema='数据库名';

• columns表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。

select * from information_schema.columns where table_schema='数据库名' and table_name='表名';

• table_constraints表：存储主键约束、外键约束、唯一约束、check约束。

select * 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、sys数据库

此数据库包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息。sys数据库结合了information_schema和performance_schema的相关数据，让我们更加容易的检索元数据。

附加知识点2：mysql有关show的用法

• show databases: 列出MySQL Server上的数据库
• show tables [from db_name]：列出数据库中的表
• show table status [from db_name] [like ‘tablename’]：列出数据库的表信息，比较详细
• show columns from table_name [from db_name]:列出表的列信息
• show fields from table_name [from db_name]:列出表的列信息
• describe table_name [columns_name]: 列出表的列信息
• show full columns from table_name [from db_name]:列出表的列信息，比较详细
• show full fields from table_name [from db_name]: 列出表的列信息，比较详细
• show index from table_name [from db_name]：列出表的索引信息
• show status ：列出Server的状态信息
• show variables： 列出MySQL参数值
• show processlist：查看当前MySQL查询进程
• show grants for user ：列出用户的授权命令

mysqldump

这个工具功能就是将MySQL Server中的数据以SQL语句的形式从数据库中dump成文本文件。是做为MySQL的一种逻辑备份工具。

mysqlbinlog

mysqlbinlog程序的主要功能就是分析MySQL Server所产生的二进制日志（也就是binlog）。通过mysqlbinlog,我们可以将binlog中指定时间段或者指定日志起始和结束位置内容解析成SQL语句。

使用性能测试工具mysqlslap测试存储引擎

mysqlslap是MySQL自带的基准测试工具。优点：查询数据，语法简单、灵活，容易使用。该工具可以模拟多个客户端同时并发的向服务器发出查询更新，给出了性能测试数据，而且提供了多种引擎的性能比较。mysqlslap为MySQL性能优化前后提供了直观的验证依据，使用其作为压力测试工具，才能准确的掌握线上数据库支撑的用户流量上限及其抗压性等问题。可以通过man帮助手册来获取其使用方法。

下面介绍一些常用的选项：

• –concurrency=name：代表并发数量，多个可以用逗号分开。例如：concurrency=50,100,200
• –engine=name：代表要测试的引擎，可以有多个，用分隔符隔开。
• –iterations=#：代表要运行这些测试多少次，即运行多少次后，得到结果。
• –auto-generate-sql: 代表使用系统自己生成的SQL脚本来测试。
• –auto-generate-sql-load-type=name：代表要测试的类型，可以是mixed, update, write, key, read; 默认是混合的mixed。
• –number-of-queries=#：限制每次查询的次数。每个客户端查询的次数为查询总数/并发数量。
• –number-int-cols=name：如果指定了–auto-generate-sql选项，创建表时int列的数量
• –number-char-cols=name：如果指定了–auto-generate-sql选项，创建表时varchar列的数量
• –create-schema=name: 指定测试的数据库名
• –debug-info: 当测试程序退出时打印CPU和内存的debug状态信息
• –query=value：包含select语句的文件或字符串，即自定义查询语句
• –only-print：不连接到数据库，只打印需要做的事情
• –csv[=file_name]：生产csv格式文件

查看数据库默认最大连接数

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.07 sec)

默认最大连接数在生产环境中一般是不够的，可以修改其到1024。

[root@www ~]# vim /etc/my.cnf
[mysqld]
max_connections = 1024

重启MySQL服务，然后查看：

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1024  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)

查看默认的存储引擎：

mysql> show variables like '%default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

测试案例

使用自带的SQL脚本进行测试

[root@www ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 \
> --iterations=1 --number-int-cols=20 --number-char-cols=30 \
> --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
> --engine=Myisam,innodb --number-of-queries=2000 \
> -uroot -pCom.123456 --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine Myisam
    Average number of seconds to run all queries: 0.554 seconds
    Minimum number of seconds to run all queries: 0.554 seconds
    Maximum number of seconds to run all queries: 0.554 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20
Benchmark
    Running for engine Myisam
    Average number of seconds to run all queries: 0.555 seconds
    Minimum number of seconds to run all queries: 0.555 seconds
    Maximum number of seconds to run all queries: 0.555 seconds
    Number of clients running queries: 200
    Average number of queries per client: 10
Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 0.278 seconds
    Minimum number of seconds to run all queries: 0.278 seconds
    Maximum number of seconds to run all queries: 0.278 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20
Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 0.362 seconds
    Minimum number of seconds to run all queries: 0.362 seconds
    Maximum number of seconds to run all queries: 0.362 seconds
    Number of clients running queries: 200
    Average number of queries per client: 10

测试说明：

模拟测试两次读写并发，第一次100，第二次200，自动生成SQL脚本，测试表包含20个int字段，30个char字段，每次执行2000查询请求。测试引擎分别是MyISAM和InnoDB。

测试结果说明：

MyISAM:第一次100客户端同时发起增查用0.554/s，第二次200客户端同时发起增查用0.555/s;

InnoDB:第一次100客户端同时发起增查用0.278/s，第二次200客户端同时发起增查用0.362/s;

mysqlslap测试工具生成CSV格式数据文件并转换成图表形式：

[root@www ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=400,200 \
> --iterations=1 --number-int-cols=20 --number-char-cols=30 \ 
> --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
> --engine=Myisam,innodb --number-of-queries=20000 \
> -uroot -pCom.123456 --verbose --csv=/root/mysql-test.csv

将mysql-test.csv拷贝到Windows主机上，打开并生成图表

自定义SQL脚本或语句进行测试

首先准备测试用的数据库和表，编写脚本来创建：

[root@www script]# vim mysqlslap_test1.sh 
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="Com.123456"
DBNAME="test1"
TABLENAME="tb01"
# create database
mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME} (stuid int not null primary key,stuname
 varchar(20) not null,stusex char(1) not null,cardid varchar(20) not null,birthday datetime,
 entertime datetime,address varchar(100) default null)"
mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
#insert data to table
i=1
while [ $i -le 20000 ]
do
insert_sql="insert into ${TABLENAME} values($i,'zhangsan','1','1234567890123456','1999-10-10',
'2016-9-3','zhongguo hebeisheng baodingshi')"
mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
let i=i+1
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

授权脚本执行权限后执行脚本生成测试用库及表：

[root@www ~]# mysql -uroot -pCom.123456 test1 -e 'select count(*) from tb01'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|    20000 |
+----------+

执行mysqlslap工具进行测试

[root@www ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 \
> --create-schema='test1' --query='select * from test1.tb01' --engine=myisam,innodb \
> --number-of-queries=20000 -uroot -pCom.123456 --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 156.971 seconds
    Minimum number of seconds to run all queries: 156.971 seconds
    Maximum number of seconds to run all queries: 156.971 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200
Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 180.046 seconds
    Minimum number of seconds to run all queries: 180.046 seconds
    Maximum number of seconds to run all queries: 180.046 seconds
    Number of clients running queries: 200
    Average number of queries per client: 100
Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 176.867 seconds
    Minimum number of seconds to run all queries: 176.867 seconds
    Maximum number of seconds to run all queries: 176.867 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200
Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 183.679 seconds
    Minimum number of seconds to run all queries: 183.679 seconds
    Maximum number of seconds to run all queries: 183.679 seconds
    Number of clients running queries: 200
    Average number of queries per client: 100

通过mysqlslap工具对mysql server进行压力测试，可以通过–concurrency、–number-of-queries等选项的值查看每次测试的结果，通过反复测试、优化得出mysql server的最大并发数。

如果mysqlslap工具输出结果为Segmentation fault(core dumped)基本表示超出MySQL server的负载。