数据库进阶之MySQL 程序

1.目标

1> 了解mysqlId服务端程序

2> 掌握mysql客户端程序的使用

3> 了解工具包中的其他程序

2. MySQL程序简介

本章介绍 MySQL 命令⾏程序以及在运⾏这些程序时指定选项的⼀般语法(如:mysql -uroot -p)。 对常⽤程序进⾏详细的讲解(实用工具的使用方法)，包括它们的选项。

MySQL安装完成通常会包含如下程序：

• Linux系统程序(工具类程序)⼀般在 /usr/bin⽬录下，可以通过命令查看：ll /usr/bin/mysql*

 然后还有一个:ll /usr/sbin/mysql* 命令,执行如下

• windows系统⽬录：你的安装路径\MySQL Server 8.0\bin，可以通过命令查看：dir "D:\mysql\mysql-8.3.0-winx64\bin\*.exe"

每个 MySQL 程序都有许多不同的选项。⼤多数程序都提供⼀个 --help 选项，可以使⽤该选项

来获取程序不同选项的描述。例如: mysql --help，可以通过在命令⾏或配置⽂件中指定选项来覆

盖 MySQL 程序的默认选项值

后续会详细学习以下MySQL程序

注意: 除了mysqlIds是服务端程序,后面的都是客户端工具

程序名	作⽤
mysqld(d表示daemon 表示后台程序)	MySQL的守护进程即 MySQL 服务器，要使⽤MySQL 服务器 mysqld 必须正在运⾏状态
mysql	MySQL客⼾端程序，⽤于交互式输⼊ SQL 语句或以批处理模式从⽂件执⾏SQL的命令⾏⼯具(登录数据库服务,输入sql语句,展示响应结果)
mysqlcheck	⽤于检查、修复、分析和优化表的表维护客⼾端
mysqldump	将 MySQL 数据库转储到 SQL、⽂本或 XML ⽂件中的客⼾端(便于在不同的系统之间来回传输)
mysqlimport	将⽂本⽂件导⼊到表的客⼾端⼯具(备份和恢复再介绍)
mysqladmin	执⾏管理操作的客⼾端，例如创建或删除数据库、重新加载授权表、将表刷新到磁盘以及重新打开⽇志⽂件。Mysqladmin还可以⽤于从服务器检索版本、进程和状态信息。
mysqlshow	显⽰数据库、表、列和索引信息的客⼾端(和mysql执行的show语法效果一样,区别是不需要登录,直接发送请求即可)
mysqldumpslow	⽤于读取和汇总慢速查询⽇志内容的实⽤程序(慢查询: 查询时间超过阈值,然后记录下来)
mysqlbinlog	从⼆进制⽇志中读取SQL语句的实⽤程序(处理二进制日志)。mysqlbinlog ⽂件中包含的已执⾏SQL语句的⽇志，可⽤于从崩溃中恢复数据。
mysqlslap	客⼾端负载⼯具，模拟多个客⼾端同时访问MySQL服务器，并报告每个阶段的使⽤时间。

其他程序可以去官方网站看:MySQL :: MySQL 8.4 Reference Manual :: 6.5.1 mysql — The MySQL Command-Line Client

3. mysqlId - MySQL 服务器(介绍)

• mysqlId也被称为MySQL服务器(是一个服务器程序), 是⼀个多线程程序，对数据目录(mysql主要的工作目录)进⾏访问管理(包含数据库和表)。数据⽬录也是其他信息(如⽇志⽂件和状态⽂件)的默认存储位置。

• 当 MySQL 服务器启动时，会侦听指定的端⼝、处理来⾃客⼾端程序的⽹络连接，并管理不同客户

端对数据库的访问

• mysqlId 程序又很多选项可以在启动的时候指定, 可以运行下面命令进行查看

mysqld --verbose --help 注意这里不需要连接mysql服务器的

4. mysql-MySQL 命令行客户端

mysql 客户端简介

mysql 是一个简单的sql shell, 可以输入命令和执行sql语句, 当执行sql语句的时候, 查询结果以ASCII表格式显示

mysql的基本使用命令,比如连接

短选项格式

mysql -uroot -p [db_name] 最后这个参数是指定连接的是哪个数据库

我们不加mysql参数显示的结果

当我输入 mysql -uroot -p mysql  加了参数, 输入密码. 然后查看选择的数据库, 发现就是mysql数据库

相当于不加参数后, 再执行 use mysql 这个命令

另一种连接方式

长选项格式:

mysql --user=root --password [db_name]

 mysql 客户端选项

指定选项的方式

• 在mysql后面的命令行列出路径 刚刚上面的长选项短选项格式就是这个

• 在mysql后面指定配置文件的路径, 以便在程序启动的时候读取配置文件中的选项, 并解析对应程序选项的值, 并应用在启动参数中 

我们打开我们的MySQL客户端的路径, 然后去看它的属性

具体是这个样子

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" "--defaults-file=C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" "-uroot" "-p" "--default-character-set=utf8mb4"

下面是解析

• 使用环境变量种的选项(不介绍)

mysql 客户端命令常用选项

选项--长格式	短格式	说明
--host	-h	--host = host_name,-hhost_name 连接到指定主机上的 MySQL 服务(默认的地址127.0.0.1 localhost)
--port	-P(大写p)	--port=port_num, -pport_num TCP/IP 连接使用的端口号(默认是3306)
user	-u	--user=user_name , -uuser_name ⽤于连接到MySQL 服务器的⽤⼾名
--password	-p(小写p)	--password[=password] , -p[password] ⽤于连接到MySQL 服务器的密码。可选，如果没有给出， 会提⽰⽤⼾输⼊
--defaults-file		--defaults-file=file_name使⽤指定的选项⽂件。如果该⽂件不存在，则会发⽣错误。
--default-characterset		--default-character-set=charset_namecharset_name 将作为客户端和当前连接的默认字符集，例： utf8mb4
--database	-D	--database=db_name , -Ddb_name 指定要使⽤的数据库
--compress	-C	--compress , -C 如果可能，压缩客户端和服务器之间传输的所有信息
--reconnect		--reconnect  如果客户端与服务器的连接丢失，⾃动尝试重新连接(丢失重连)
--quick	-q	--quick , -q 不缓存查询结果，收到⼀⾏打印⼀⾏，如果输出被挂起，可能会降低服务器速度(谨慎使用)
--protocol		--protocol={TCP|SOCKET|PIPE|MEMORY}⽤于连接到服务器的传输协议, 默认为TCP
--delimiter		--delimiter=str 设置SQL语句分隔符(标记sql语句结束的字符)。默认值为分号 ( ; )
--execute	-e	--execute=statement , -estatement 执⾏指定的SQL语句并退出。 直接在mysql客户端直接执行sql语句,并且直接退出, 连接成功之后, 读取execute后的sql语句,直接就会把结果返回, 不用先登录,然后写sql,然后等待结果,最后退出,直接可以通过这个参数一步搞定
--version	-V	--version , -V 显示版本信息并退出。
--help	-？	--help , -? 显示帮助信息并退出。

注意: 如果选项的值中包含空格，那么值需要包含在双引号中

在命令行中使用选项

规则:

1> -- 表示长格式, -表示短格式. 例如：-？和 --help 都表示MySQL 程序显示他的帮助消息

2> 选项名称区分大小写. -v 和 -V 都是合法的,但是含义不同, 他们分别是 --verbose(尽可能详细显示结果) 和 --version(显示mysql版本号) 选项的相应缩写格式

3> 在某些选项后面需要指定一个值, 比如: 长格式:mysql -h 127.0.0.1 或mysql --host=127.0.0.1 表示向客户端程序指定MySQL 服务器主机

4> 对于带值的长格式选项,使用 = 分割选项名和值, 对于短格式, 选项值就直接跟在选项之后, 也可以用空格分开. 如: 长格式: --host=127.0.0.1, 段格式: -h127.0.0.1 和-h 127.0.0.1; 但是指定密码选项的短格式,选项和值不能有空格: 

mysql -ptest test是表示密码,但是没有指定要访问的数据库

mysql -p test test 表示的是指定访问的数据库, 但是没有指定密码

注意: 在命令⾏中，第⼀个不带破折号 - 的值被解析为要访问的数据库名, 选项+值是一组

5> 在选项名称中, - 和 _ 大多情况下可以互换, 但是前导破折号不能转为下划线

如: --skip-grant-tables 和 --skin_grant_tables 是等价的 但是不能写成 __skin_grant_tables

6> 对于采用数值的选项, 值的后缀可以带有K,M,G表示乘数 1024、1024^2或 1024^3(指定缓冲区大小)如: mysqladmin --count=1K --sleep=3 ping -uroot -p 告诉 mysqladmin对服务器执⾏ 1024 次 ping，每次 ping 之间休眠 3 秒

7> 在命令行中包含空格的选项值, 必须用"" 引起来, 比如我们使用 --execute/-e选项和mysql一起使用的时候, 表示把一个或多个sql语句发送给服务器执行并显示结果

没使用execute之前

使用execute后: mysql -u root -p -e "select version();select now();"

直接就返回结果

选项(配置)文件 

大多数MySQL程序都可以从选项文件(配置文件)中读取启动选项. 可以在选项文件中指定常用选项, 这样就不用再每次创建程序的时候, 在命令行里面输入它们. 大部分的选项文件都是纯文本格式, 可以用文本编辑器进行创建(vscode)

使用方法

选项 --defaults-file 可以指定要使用的选项文件, 客户端程序会读取并应用选项文件中的配置

Linux

mysql --defaults-file=/etc/mysql/my.cnf -uroot -p

conf.d

mysql.conf.d/ mysql服务端

虽然把客户端和服务器的配置文件做了区分, 但是一般不会分别在对应的配置文件中配置, 而是把所有的配置都写在默认的配置文件中, 便于维护和管理

windows

mysql "--defaults-file=C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" -uroot -p 这一段是windows下默认的配置文件路径

注意, 每次修改配置之前,必须要先进行备份

如果在使用mysql程序的时候(客户端/服务端), 没有指定配置文件的路径, 那么就会自动读取默认路径下的配置文件. 下面介绍默认配置文件地址和加载顺序

选项文件位置以及加载顺序

在 Windows 系统读取选项⽂件

mysql 按照下面顺序查找并读取选项/配置 文件. 如果文件不存在则需要手动创建. 读取的顺序从上到下, 后读取的文件中配置的选项优先级越高.

⽂件名	说明
%WINDIR%\my.ini , %WINDIR%\my.cnf	全局
C:\my.ini , C:\my.cnf	全局
BASEDIR\my.ini , BASEDIR\my.cnf	全局
defaults-extra-file	如果存在其他选项⽂件可以通过 --defaults-extra-file选项指定
%APPDATA%\MySQL\.mylogin.cnf	登录路径选项（仅限客⼾端）针对客户端的配置文件
DATADIR\mysqld-auto.cnf	系统变量 (仅限服务器) 这个是放我们的数据库表的目录,实现持久化存储

优先级和读取顺序(注意一下)

对每个配置文件进行逐一的解释

%WINDIR%\my.ini , %WINDIR%\my.cnf

先去c盘下的windows去查看有没有my.ini, 如果有就加载并读取相应的选项值, 如果没有就继续往下找

BASEDIR\my.ini , BASEDIR\my.cnf    BASEDIR这个是代表安装路径

defaults-extra-file 后面讲linux会说区别

%APPDATA%\MySQL\.mylogin.cnf 针对客户端的配置文件

DATADIR\mysqld-auto.cnf    DATADIR 这个是放我们的数据库表的目录,实现持久化存储(仅用于服务器)

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini    这个默认配置文件是在上面几个执行的中间位置, 不固定. 这个路径是我们常用的操作配置文件的路径

在 Unix 和 Linux 系统上读取的选项⽂件

⽂件名	说明
/etc/my.cnf	全局
/etc/mysql/my.cnf	全局
$MYSQL_HOME/my.cnf	服务器特定选项（仅限服务器）
defaults-extra-file	如果存在其他选项⽂件可以通过 --defaults-extra-file 选项指定
~/.my.cnf	⽤⼾特定选项
~/.mylogin.cnf	⽤⼾特定的登录路径选项（仅限客⼾端）
DATADIR/mysqld-auto.cnf	系统变量 (仅限服务器)

读取顺序和优先级

后读取的会把前读取的覆盖掉(相同的选项值, 后面的覆盖前面的选项值). 对于俩个配置文件中配置 了相同的选项, 那么优先级高的文件就会覆盖掉优先级低的文件中的选项

对每个配置文件进行逐一的解释
 /etc/my.cnf 默认是没有的

defaults-extra-file

我们去官方文档进行查看: MySQL :: MySQL 8.0 Reference Manual :: 6.5.1.1 mysql Client Options

总结: 在widows 系统中配置文件的后缀名为 .ini 或 .conf. 在Linux 系统中配置文件的后缀名为.cnf

选项文件语法

1> 任何长选项都可以在选项文件中指定. 获取选项列表可以使用下面指令

mysql --help

2> 选项文件中指定选项时, 省略俩个前导破折号, 并且每一行表示一个选项

例子: --quick 和 --host=127.0.0.1 在选项⽂件中应表⽰成 quick 和 host=127.0.0.1 (并且需要换行)

quick

host=127.0.0.1

3> 注释: 通常时以 # 或 ; 开头

4> [group] 结点, 名称不区分大小写. 也就是当选项名称和程序名称相同, 则需要使用[]来区分时客户端还是服务器的

[mysqlId]和[mysql] 组就分别适用于mysqlId服务程序和mysql客户端程序(分开对每一个程序进行配置, 而不会影响到其他组)

5> opt_name = value (选项名和值)如: host=127.0.0.1 

选项名对应的值，可以使⽤转义序列 \b , \t , \n , \r , \\ 和 \s 来表⽰退格符、制表符、换⾏符、回⻋符、反斜杠和空格字符

例子:

basedir="C:\\Program Files\\MySQL\\MySQL Server 8.0"

basedir="C:/Program Files/MySQL/MySQL Server 8.0"

6> [client] MySQL发⾏版中所有客⼾端程序(mysql,mysqkcheck,mysqkdump....)都会读取并应⽤这个组下的选项(除了mysqld)配置的公共信息，在这个组下可以指定适⽤于所有客⼾端程序的通⽤选项，例如配置⽤⼾名和密码(但要确保只有⾃⼰才可以访问这个⽂件以防⽌密码泄漏)也就是所有客户端程序都会读取client这个结点下配置的公共信息(客户端的公共信息都可以写到[client]里面

例子: 

# 在当前⽤⼾的home⽬录下创建.my.cnf，并在[client]写⼊公共配置

# 这⾥主要设置⽤了主机、端⼝、⽤⼾名、密码

输入 vim mysql.cnf

然后我们打开编辑模式按i,然后把下面的内容粘贴进去,编辑完成按Esc 退出编辑模式, 然后输入:wq退出并保存

[client]

host=127.0.0.1

port=3306

user=root

password=12345

然后我们就可以不输入密码就进入mysql了

7>可以通过 [客⼾端程序名] 的形式为不同的客⼾端程序指定选项(自定义化的配置)，例如 [mysql] ，当运⾏mysql程序时会读取并应⽤该组下的配置，如果选项名与 [client] 重复， [client] 中的

选项将会被覆盖([客⼾端程序名]结点的优先级高于[client]

 # 设置为必须输⼊密码

[mysql]

password

输入mysql之后就需要输入密码了

8> 为特定 MySQL 版本设置选项可以使⽤ [mysqld-5.7] 、 [mysqld-8.0] 的组名(使用不同版本的mysqlId)

9> 在选项⽂件中使⽤ !include 指令来包含其他选项⽂件，例如： !include/home/mydir/myopt.cnf

这里面的cnf是具体的配置文件

10> 在选项⽂件中使⽤ !includedir 指令来搜索指定⽬录中的其他选项⽂件，例如： !include /home/mydir (这里面的mydir是一个指定的目录) 但不保证⽬录中选项⽂件的读取顺序(先扫描到谁, 加载谁)

注意:

1. !includedir 在 Unix和Linux 操作系统中会搜索指定⽬录下任何以 .cnf 为后缀的⽂件.在 Windows 中，会搜索指定⽬录下任何以 .ini 或 .cnf 为后缀的⽂件

2. 只会读取包含⽂件中当前客⼾端的组配置，例如当前运⾏的是mysql程序，那么只会读取[mysql]组中的选项配置

11> 在 Windows中请确保在配置⽂件的最后⼀⾏加⼀个换⾏符，否则该⾏将被忽略

例子: 设置客户端全局编码格式

 输入命令: vim /etc/mysql/my.cnf

设置编码集

[client] # 所有客⼾端程序者会读取这个节点下的配置信息

default-character-set=utf8mb4 # 指定编码格式为utf8mb4

 注意每次对配置文件进行修改, 我们都需要备份

cp mysql.cnf mysql.cnf.bak

mysql 客户端命令

mysql客户端命令的作用:

1> 执行sql语句

2> 执行指令

使⽤mysql客⼾端程序连接到数据库服务器之后，可以发送SQL语句到服务器执⾏，并以 ";"(分

号)、\g 或 \G结束(结束标识符)

1. ; 与 \g 结束表⽰以表格形式展⽰结果

2. \G 结束表⽰以⾏形式展⽰结果

3. 如果当前已经输⼊了⼀部分SQL 语句想重新输⼊可以输⼊Control+C中断当前输⼊

在当前模式下，mysql还有⼀组⾃⼰的命令，可以输⼊ help or \h 查看命令列表

help/ \? 查看命令列表

clear/ \c 清除当前语句(写错了,想取消命令可以用这个)

connect/ \r 重新连接服务器

status/ \s 查看服务器状态(里面包含线程情况, 服务器版本, 字符集...)

 delimiter/  \d  (英文: delimiter 是定界符的意思) 可以设置mysql语句的结束标识符

 

 exit \q  | quit \q 这俩个指令都是退出mysql

tee/ \T 把执行的结果保存在一个指定的目录文件中(相当于日志)

创建文件/root/rs.txt

使用tee指令把指定txt文件作为mysql指令结果记录目录

 

执行mysql 语句 结果会保存在刚刚指定的路径

查看指定的 rs.txt文件 发现都是我们执行的mysql语句结果集

notee/ -t 执行结果不再写入文件

prompt/ \R 修改提示符

这个就是提示符 

source/ \.  加载并执行指定的.sql脚本(把我们本机的sql语句搞到一个文件里面,然后在服务器通过这个命令把文件进行引用,然后进行执行即可)(后续会演示)

system/ \! 在mysql上执行系统命令, 非常危险

 甚至可以查看密码, 十分的危险

 use/ \u 使用数据库

warnings/ \W 显示警告信息

nowarning/ \w 不显示警告信息

help contents 获取服务端的帮助(集成了官方文档)

 我们查看 Data Types 里面 BIT 类型的详细解释信息

从sql文件执行SQL语句

使用场景: 开发环境-->测试环境-->生产环境

使用 source命令进行导入 

有时候我们需要从.sql 文件执行⼀些SQL语句，比如要把⼀个数据库从⼀台服务器A复制到另⼀台服务器B上，那么可以先从服务器A导出数据到.sql⽂件，然后在服务器B执行这个.sql⽂件，在刚刚我们学习了，用help命令查看命令列表，可以看到有⼀个source 命令如下所示：

\. sql文件的绝对路径

我们进行演示

 1. 准备要执行的.sql⽂件，名为test_db.sql，内容如下：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;DROP DATABASE IF EXISTS `test_db`;
CREATE DATABASE `test_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;USE `test_db`;-- ----------------------------
-- Table structure for classes
-- ----------------------------
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes`  (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,`desc` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of classes
-- ----------------------------
INSERT INTO `classes` VALUES (1, '计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法');
INSERT INTO `classes` VALUES (2, '中文系2019级3班', '学习了中国传统文学');
INSERT INTO `classes` VALUES (3, '自动化2019级5班', '学习了机械自动化');-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'Java');
INSERT INTO `course` VALUES (2, '中国传统文化');
INSERT INTO `course` VALUES (3, '计算机原理');
INSERT INTO `course` VALUES (4, '语文');
INSERT INTO `course` VALUES (5, '高阶数学');
INSERT INTO `course` VALUES (6, '英文');-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (`score` decimal(3, 1) NULL DEFAULT NULL,`student_id` int(11) NULL DEFAULT NULL,`course_id` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (70.5, 1, 1);
INSERT INTO `score` VALUES (98.5, 1, 3);
INSERT INTO `score` VALUES (33.0, 1, 5);
INSERT INTO `score` VALUES (98.0, 1, 6);
INSERT INTO `score` VALUES (60.0, 2, 1);
INSERT INTO `score` VALUES (59.5, 2, 5);
INSERT INTO `score` VALUES (33.0, 3, 1);
INSERT INTO `score` VALUES (68.0, 3, 3);
INSERT INTO `score` VALUES (99.0, 3, 5);
INSERT INTO `score` VALUES (67.0, 4, 1);
INSERT INTO `score` VALUES (23.0, 4, 3);
INSERT INTO `score` VALUES (56.0, 4, 5);
INSERT INTO `score` VALUES (72.0, 4, 6);
INSERT INTO `score` VALUES (81.0, 5, 1);
INSERT INTO `score` VALUES (37.0, 5, 5);-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (`id` int(11) PRIMARY KEY AUTO_INCREMENT,`sn` int(11) NOT NULL COMMENT '学号',`name` varchar(20) NOT NULL COMMENT '姓名',`mail` varchar(20) COMMENT 'QQ邮箱'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, 50001, '张三', 'zs@bit.com');
INSERT INTO `student` VALUES (2, 50002, '李四', 'ls@bit.com');
INSERT INTO `student` VALUES (3, 50003, '王五', 'ww@bit.com');
INSERT INTO `student` VALUES (4, 50004, '赵六', 'zl@bit.com');
INSERT INTO `student` VALUES (5, 50005, '钱七', 'qq@bit.com');SET FOREIGN_KEY_CHECKS = 1;

把上面的sql语句保存在test_db.sql文件里面,然后传到你的服务器里面

 删除 .txt文件后缀

 查看当前有没有这个数据库

执行外部sql文件

查看数据库: 发现确实创建了这个数据库

注意, 执行的时候可能没有权限, 我们要切换成root用户

 使⽤mysql客户端导入

不需要登录mysql而是直接在我们linux终端来执行

mysql < test_db.sql -u root -p

执行结果, 发现确实是有数据库和表

注意:

mysql db_name < text_file # 在指定的数据库下执⾏SQL,前提是数据库必须提前建⽴好

mysql < text_file # 不指定数据库.sql中必须有USE [database_name]，来指定要操作的数据库
 

5. 工具包中其他程序的使用 

程序名	作⽤
mysqld(d表示daemon 表示后台程序)	MySQL的守护进程即 MySQL 服务器，要使⽤MySQL 服务器 mysqld 必须正在运⾏状态
mysql	MySQL客⼾端程序，⽤于交互式输⼊ SQL 语句或以批处理模式从⽂件执⾏SQL的命令⾏⼯具(登录数据库服务,输入sql语句,展示响应结果)
mysqlcheck	⽤于检查、修复、分析和优化表的表维护客⼾端
mysqldump	将 MySQL 数据库转储到 SQL、⽂本或 XML ⽂件中的客⼾端(便于在不同的系统之间来回传输)
mysqlimport	将⽂本⽂件导⼊到表的客⼾端⼯具(备份和恢复再介绍)
mysqladmin	执⾏管理操作的客⼾端，例如创建或删除数据库、重新加载授权表、将表刷新到磁盘以及重新打开⽇志⽂件。Mysqladmin还可以⽤于从服务器检索版本、进程和状态信息。
mysqlshow	显⽰数据库、表、列和索引信息的客⼾端(和mysql执行的show语法效果一样,区别是不需要登录,直接发送请求即可)
mysqldumpslow	⽤于读取和汇总慢速查询⽇志内容的实⽤程序(慢查询: 查询时间超过阈值,然后记录下来)
mysqlbinlog	从⼆进制⽇志中读取SQL语句的实⽤程序(处理二进制日志)。mysqlbinlog ⽂件中包含的已执⾏SQL语句的⽇志，可⽤于从崩溃中恢复数据。
mysqlslap	客⼾端负载⼯具，模拟多个客⼾端同时访问MySQL服务器，并报告每个阶段的使⽤时间。

在命令⾏中使⽤MySQL发⾏版中的其他⼯具时，⼀些选项是公共的，⽐如⽤⼾名和密码，使⽤⽅法和mysql相同，在这⾥统⼀列出，后⾯我们在介绍不同的⼯具时，只讨论个性的选项及作⽤，公共选项如下所⽰：

选项--⻓格式	短格式	说明
--host	-h	--host=host_name , -h host_name 连接到指定主机上的 MySQL 服务
--port	-P	--port=port_num , -P port_num TCP/IP 连接使⽤的端⼝号
--user	-u	--user=user_name , -u user_name ⽤于连接到MySQL 服务器的⽤⼾名
--password	-p	--password[=password] , -p[password] ⽤于连接到MySQL 服务器的密码。可选，如果没有给出， 会提⽰⽤⼾输⼊
--defaults-file		--defaults-file=dile_name 使用指定的选项文件, 如果该文件不存在, 则会发生错误
--compress	-C	--compress,-C 如果可能, 压缩客户端和服务器之间传输的所有信息
--protocol		--protocol={TCP|SOCKET|PIPE|MEMORY}
--version	-V	--version, -V 显示版本信息并退出
--help	-?	--help,-? 显示帮助信息并退出

mysqlcheck  - 表维护程序

作用

mysql客户端主要执行表维护, 可以对表进行: 分析, 检查, 优化或回复操作

• 分析的作⽤是查看表的关键字分布(定义了哪些二级索引,通过哪些where条件可以查询到)，能够让 sql ⽣成正确的执⾏计划（⽀持 InnoDB，MyISAM，NDB）

• 检查的作⽤是检查表的完整性以及数据库表和索引是否损坏(当意外断电的时候,写数据有没有写完,返回错误..)（⽀持 InnoDB，MyISAM，ARCHIVE，CSV）

• 优化的作⽤是回收空间、减少碎⽚(整理磁盘碎片)、提⾼I/O（⽀持 InnoDB，MyISAM，ARCHIVE）

• 修复的作⽤是修复可能已经损坏的表（⽀持 MyISAM，ARCHIVE，CSV）

 注意事项:

1. 当使⽤mysqlcheck⼯具时，MySQL服务器必须在运⾏状态

2. 执⾏过程中相应的表将会被锁定，所以其他的操作将会被挂起

3. 并不是所有的存储引擎都⽀持以上四种操作，如果遇到不⽀持的引擎会报出相应的错误

4. 执⾏表修复操作之前对表进⾏备份(修改数据库的时候觉得备份)，在某些情况下可能会导致数据丢失。

使用方法:

使用三种方式来使用mysqlcheck

mysqlcheck [options] db_name [tbl_name ...]  指定数据库名,表名
mysqlcheck [options] --databases db_name ... 多个数据库

mysqlcheck [options] --all-databases 2 所有的数据库

常用选项

mysqlcheck有如下常⽤选项，可以在命令⾏中指定，也可以在选项⽂件中通过 [mysqlcheck] 和 [client] 组进⾏指定

选项	说明
--analyze，-a	分析表
--auto-repair	如果检查的表有损坏，则⾃动修复它。所有表都检查过之后才进⾏必要的修复(自动修复)
--check，-c	检查表中的错误。mysqlcheck的默认操作
--check-only-changed，- C	仅检查⾃上次检查以来更改过的表, 没有修改的表会被忽略
--databases，-B	多个数据库名⽤空格隔开 处理指定数据库中的所有表
--force, -f	即使发⽣SQL错误也要继续
--optimize，-o	优化表
--repair，-r	执⾏可能进⾏的任务修复操作，除了唯⼀键
--skip-database	--skip-database=db_name不需要执⾏检查的数据库名(区分⼤⼩写)(跳过哪些数据库)
--tables	--tables=table_name 多个表名⽤空格隔开 在选项之后的所有名称参数都被视为表名。
--use-frm	对于MyISAM表的修复操作

分析表: mysqlcheck -a 数据库名称 -u root -p  

 检查表: mysqlcheck --check 数据库名称 -uroot -p

 修复表: mysqlcheck --repair 数据库名称 -uroot -p

注意: 修复不支持InnoDB存储引擎, 如果要修复InnoB存储引擎的数据, 要把InnoDB存储引擎的表转化成Mylsam存储引擎

优化表: mysqlcheck --optimize 数据库名 -uroot -p

注意:

1> InnoDB在优化过程中执行的是一个重建的操作

2> 官网建议不要经常取执行优化操作(一天,一小时算经常, 大表一个月优化一次, 小表可以不优化)

mysqlcheck的特殊使用

mysqlcheck程序的默认功能是对数据表进⾏ 检查 操作(相当于指定选项 --check )，如果想要对表进⾏修复操作，可以通过复制原来的mysqlcheck程序，并重命名为mysqlrepair，并运⾏mysqlrepair即可，还可以创建mysqlcheck的快捷⽅式，并把快捷⽅式命名为mysqlrepair然后
直接运⾏，这时就执⾏的是修复操作，通过下表所⽰的命名⽅式可以改变mysqlcheck的默认⾏为

好处: 不需要指定对应的操作选项, 只要指定数据库/表

程序名	说明
mysqlrepair	默认⾏为是修复，相当于选项 --repair
mysqlanalyze	默认⾏为是修复，相当于分析 --analyze
mysqloptimize	默认⾏为是修复，相当于优化 --optimize

Mysqldump - 数据库备份程序

作用: mysqldump客户端程序可以执行逻辑备份并且生成一组SQL语句, 其中包含原始数据库和表的定义以及表中的数据, 以便实现对数据库的简单备份或复制, mysqldump可以生成,.sql,csv或xml格式文件

注意事项:

1. 转储表时必须要有 SELECT 权限(查询)

2. 转储视图时必须要有 SHOW VIEW 权限

3. 转储触发器时必须要有 TRIGGER 权限

4. 如果没有使⽤ --single-transaction 选项时必须要有 LOCK TABLES 权限

5. 如果没有使⽤ --no-tablespaces 选项时必须要有 PROCESS 权限

6. 重新导⼊转储⽂件时，也需要有相应的权限(delete,update...

7. 由于mysqldump是逐⾏转储数据，所以不适⽤于⼤数据量的转储与导⼊

使用方法:

mysqldump的⽅法通常有以下使⽤，可以转储⼀个或多个表或数据库，如下所⽰：

mysqldump [options] db_name [tbl_name ...] 指定一个数据库(表名)

mysqldump [options] --databases db_name ... 指定多个数据库

mysqldump [options] --all-databases 所有的数据库

常用选项:

mysqldump有如下常⽤选项，可以在命令⾏中指定，也可以在选项⽂件中通过 [mysqldump] 和[client] 组进⾏指定

选项	说明
--add-drop-database	在每个 CREATE DATABASE 语句之前添加 DROP DATABASE 语句  ( 相当于 每次创建数据库 前面 drop database if exists 数据库名字)
--add-drop-table	在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句(相当于 每次创建表 前面drop table if exists 数据表名)
--add-drop-trigger	在每个 CREATE TRIGGER 语句之前添加 DROP TRIGGER 语句(创建触发器同上)
--add-locks	⽤ LOCK TABLES 和 UNLOCK TABLES 语句包裹每个表转储(锁起来, 让别的操作不要影响当下执行的操作)
--all-databases，-A	转储所有数据库中的所有表
--databases，-B	--databases=db_name 多个数据库名⽤空格隔开 将参数解释为数据库名称并转储所有的表
--comments，-i	添加注释到转储⽂件 添加注释
--compact	紧凑格式输出 给表添加值的时候, 一口气添加一组, 而不是一个一个添加
--compatible=ansi	⽣成与其他数据库或旧MySQL服务器更兼容的输出
--complete-insert，-c	使⽤包含列名的完整INSERT语句
--events，-E	从转储数据库中转储事件
--extended-insert，-e	使⽤多⾏INSERT语法
--flush-logs，-F	在开始转储前刷新⽇志
--flush-privileges	在转储后刷新权限
--force，-f	转储期间发⽣了SQL错误,也要继续
--hex-blob	使⽤⼗六进制表⽰法转储⼆进制列
--ignore-table	--ignore-table=db_name,table_name 多个表⽤空格隔开 不转储给定的表 哪些表是不需要导入的
--lock-all-tables，-x	锁定所有数据库中的所有表
--lock-tables，-l	在转储之前锁定指定要转储的表
--no-autocommot	--databases=db_name --ignore-table=db_name.table_name

--no-create-db，-n	不要⽣成 CREATE DATABASE 语句 不会出现drop database if exists 数据库名字 语句, 和上面相反
--no-create-info，-t	不要为每个转储的表⽣成 CREATE TABLE 语句
--no-data，-d	不转储表内容(不会生成 insert 语句) 默认是生成的
--skip-add-drop-table	在每个 CREATE TABLE 语句之前不添加 DROP TABLE 语句
--skip-add-locks	不要添加锁
--skip-comments	转储⽂件中不添加注释
--skip-compact	不使⽤紧凑格式
--skip-triggers	不转储触发器
--tables	--tables=table_name 多个表名⽤空格隔开 在选项之后的所有名称参数都被视为表名。 需要转储哪些表
--triggers	转储每个表中的触发器
--xml，-X	以XML格式输出 指定输出格式

导出mysql语句:  mysqldump 数据库名 > 指定的目录.sql -uroot -p  把我们的数据库以文字的形式存在文件里面(具体的数据库和表->sql语句)

 

查看我们转储的文件

导出单个数据库

导出所有数据库, 添加drop if exsist语句, 删除insert语句

mysqldump -A --add-drop-database --no-data > /root/dumpAll.sql -uroot -p

mysqladmin -MySQL 服务器管理程序

作用:  是一个执行管理操作的客户端. 可以用来检查服务器的配置和当前状态, 以及创建和删除数据库等(配合使用mysqladmin的用户必须具备管理员权限)

使用方法:

mysqladmin可以使用下面的语法

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

 常用选项:

可以使用[mysqladmin]和[client]进行指定

支持的命令:

语法中的command 表⽰命令，有些命令后⾯需要跟上⼀个参数，如下列出了mysqladmin 的常⽤命令
1. version

显示服务器的版本信息: mysqladmin version -uroot -p (当前mysql的版本是多少, 协议版本,..)

2. status 

查看服务器状态: mysqladmin status -uroot -p (线程数...)

参数含义

Uptime MySQL 服务器已运⾏的秒数。

Threads 活动线程（客⼾端）的数量。

Questions ⾃服务器启动以来客⼾端的问题（查询）数。

Slow queries 慢SQL的查询数。

Opens 服务器已打开的表数。

Flush tables 服务器已执⾏flush-*、refresh和reload命令的数量。

Open tables 当前打开的表数。

3. create db_name 

创建一个数据库名为 db_name 的数据库: mysqladmin create 数据库名 -uroot -p

注意: 创建数据库使用的编码集是选项文件中配置的编码集, 如果没有指定那么就使用当前MySQL版本默认的编码集

4. drop db_name

删除数据库名为 db_name 的数据库: mysqladmin drop 数据库名 -uroot -p

5. extended-status

显示服务器状态变量的值 描述系统的工作状态

6. flush-hosts

刷新主机缓存中的所有信息

7. flus-logs[log_type...]

刷新所有日志, log_type 中可以提供下一种或多种日志类型binary,engine,error,general,relay,slow,多个类型之间用空格分割

8. flush-privilages

重新加载授权表

9. flush-tables

刷新所有表

10. flush-threads

刷新线程缓存

11. password new_password

设置新密码

• 如果密码中有空格必须⽤双引号把密码包裹起来

• password 后可以省略新密码，mysqladmin会在之后提⽰输⼊新密码

• password 做为最后⼀个command 时才可以省略密码值，否则下⼀个参数将作为密码被设置

12. ping

检查服务器是否可用(不断的发送请求)

13. processlist

显示活动服务器线程的列表: mysqladmin processlist -u root -p

为什么要查进程列表, 当我们服务器的可用连接占满了之后,我们需要让他自动去释放, Mysql默认可用维护150个活动连接, 如果连接被使用完, 那么就可以通过kill指令手动释放那些睡眠时间很长的连接(很多情况是死锁了不释放)

14. kill id, id,...

终止服务器线程, 如果给出了多个线程ID值, 则列表中不能有空格

此时杀死后,我们再执行杀死的那个连接, 发现进行了重新连接

 15. reload

重新加载授权表

16. refresh 

刷新所有表

17. shutdown

停止服务器

18. start-replica

在副本服务器上开始复制. Mysql 8.0.26后的版本开始使用

19. start-slave 

在副本服务器上开始复制. 同18

20. stop-replica

停止副本服务器上的复制. MySQL 8.0.26及以后的版本使⽤此命令。

21. stop-slave 

停止副本服务器上的服务.MySQL 8.0.26 之前使⽤此命令

22. variables

显示服务器系统变量及其值: mysqladmin variables -uroot -p123456

 mysqlshow - 显示数据库, 表和列信息

 作用:

客户端可用于快速查看哪些数据库, 数据库中的表和表中的列或索引(和show databases;返回结果一样)

注意事项:

具体可用看看官方文档: https://dev.mysql.com/doc/refman/8.0/en/show.html

使用方法:

mysqlshow [options] [db_name [tbl_name [col_name]]]

• db_name tbl_name col_name 可以使⽤通配符 * 、 ? 、 % 或 _

• 如果没有指定数据库，则显⽰所有数据库名称列表。

• 如果没有指定表，则显⽰数据库中所有匹配的表。

• 如果没有指定列，则显⽰表中所有匹配的列和列类型。

• 输出仅显⽰当前权限可以访问的数据库、表或列的名称。

常用选项:

显示指定数据库中的表: mysqlshow 数据库名 -uroot -p123456

显示表中所有的列: mysqlshow 数据库 数据表 -uroot -p123456

显示某个表中的某一列: mysqlshow 数据库 数据表 表里面的字段 -uroot -p123456

mysqldumpslow - 总结慢查询日志文件

作用:

在使用MySOL数据库的时候, 经常进行查询操作, 当某些查询语句执行的时间非常长, 超过了设定的阈值, 则称之为慢查询, 慢查询的相关信息被记录成一个日志叫做慢查询日志, mysqldumpslow 可用解析慢查询日志文件并汇总其内容, 有关慢查询日志的内容在服务器配置进行详细解析

进行慢查询对应的SQL优化的依据

我们先使用 mysqladmin status -u root -p123456 查看是否有慢查询语句

再通过 show variables like '%query%' 可用查看系统变量, 其中 long_query_time 是设置的慢查询的指定阈值,此时为10s, 默认的慢查询日志文件是 slow_query_log_file, 此时还涉及一个开关, slow_query_log (默认是off 不记录日志)

注意事项:

通常情况下，mysqldumpslow 会将相似的查询分组并显⽰摘要输出，⼀般会把数字和字符串⽤ N和 "S" 代替,要想显⽰真实的值可以使⽤ -a 和 -n 选项

使用方法:

语法: mysqldumpslow [options] [log_file ...] log_file是具体的慢日志保存路径

参数解释

count: 执行的次数

Time: 单次的耗时

Lock: 申请与释放锁使用的时间

Rows 获取数据所用的时间

mysqldumpslow常用选项

选项	说明
-a	不⽤ N 和 'S' 代替numbers和String
-n N	在名称中包含N个以上的数字⽤ N 代替
-g pattern	仅考虑与指定模式匹配(字符串匹配操作)的慢查询
--help	显⽰帮助信息并退出
-h host_name	与*-slow.log⽂件名对应的MySQL服务器主机名。 可以包含通配符。默认值是*(匹配所有)。 如: /var/lib/mysql/iZ7xv6ge3eb3kvqovp1jw7Z(主机名)-slow.log 把所有集群里面慢日志都集中在一台机器上   主机级别
-i name	服务器实例的名称  mysql级别
-l	不要从总时间中减去锁占⽤的时间
-r	倒序顺列
-s sort_type	如何对输出进⾏排序
-t N	显⽰输出中的前N个查询 分析前n条数据
--verbose , -v	打印有关程序功能的更多信息

-s sort_type sort_type可选的值如下所示:
t , at : 按查询时间或平均查询时间排序，默认排序

l , al : 按锁占⽤时间或平均锁占⽤时间排序

r , ar : 按发送的⾏数或平均发送的⾏数排序

c : 按计数排序

 mysqlbinlog - 处理二进制日志文件

什么是二进制日志文件:

我们平时对数据库的修改，包括对数据的增删改，都会被描述成⼀个"事件"，每个"事件"都会以⼆进制的形式记录在⼀个⽂件⾥，这个⽂件就是服务器的⼆进制⽇志⽂件，称为Binary Log或binlog  关于⼆进制⽇志⽂件的具体格式与使⽤场景我们在MySQL 主从复制(集群部署的时候,数据库的查询比写使用更加频繁, 此时就会把集群分为主节点和若干个从结点,主结点写入,从结点查询,写入的数据(修改数据)会把这个操作作为一个事件保存在binglog里面(某一行,某一个字符做了哪些修改),从结点会定时读取整个binglog,把里面的二进制解析成sql语句然后执行一次, 这就是主从同步)专题 讲解

作用:

mysqlbinlog 能够以⽂本格式显⽰⼆进制⽇志⽂件中的内容。

注意事项

• binlog的默认保存路径是数据⽬录：

◦ Linux下默认⽬录：/var/lib/mysql

◦ Windows下默认⽬录：C:\ProgramData\MySQL\MySQL Server 8.0\Data

• binlog是以 .00000n 结尾命名的⽂件，n不断递增

# binlog的名字可以在选项⽂件⾥配置，我这⾥默认的是

binlog开头root@guangchen-vm:/var/lib/mysql# ll binlog*

mysqlbinlog使用方法

mysqlbinlog [options] log_file ...
先把字符集注解掉: vim /etc/mysql/my.cnf 

 然后我们输入这个指令查看二进制文件: mysqlbinlog binlog.000001

我们介绍一下一个事件

binlog.000010 的输出内容中包含各种事件，事件信息包括 SQL 语句、执⾏语句的服务器 ID、语

句执⾏时的时间戳、花费的时间等等。⽇志的具体格式我们这⾥先不做讨论。

选项文件

mysqlbinlog 有如下常⽤选项，可以在命令⾏中指定，也可以在选项⽂件中通过 [mysqlbinlog] 和 [client] 组进⾏指定

mysqlslap - 负载仿真客户端

作用: mysqlslap是一个诊断程序, 用于模拟MySQL服务器的客户端负载(模拟很多客户端同时连接服务器,执行很多次查询,查看服务器的负载情况,每个查询执行的时间是多少), 并报告每个阶段的时间, 就好比多个客户端正在访问服务器一样.

使用方法: mysqlslap [options]

注意事项:

1. 可以通过 --create 或 --query 选项，指定包含SQL语句的字符串或包含SQL语句的⽂件

2. 如果指定⼀个包含SQL语句的⽂件，默认情况下每⾏必须包含⼀条语句(也就是说，隐式语句分隔符是换⾏符)

3. 如果要把⼀条语句分为多⾏书写，可以使⽤ --delimiter 选项指定不同的分隔符

4. 不能在⽂件中包含注释，因为mysqlslap不能解析注释。

5. mysqlslap运⾏分为三个阶段:

        a. 创建测试数据阶段：创建⽤于测试的库、表或数据，这个阶段使⽤单个客⼾端连接

        b. 运⾏负载测试阶段，这个阶段可以使⽤许多客⼾端连接

        c. 清理阶段：执⾏删除表，断开连接等操作，这个阶段使⽤单个客⼾端连接

示例:

1. 提供⾃定义的创建和查询语句，创建50个客⼾端连接，每个客⼾端进⾏200次 select 查询(在⼀⾏内输⼊命令)  

 mysqlslap --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23);" --query="SELECT * FROM a" --concurrency=50 --iterations=200 -u root -p
然后会执行一段时间, 会生成测试报告

2. 让mysqlslap⽤包含两个 INT 列和三个 VARCHAR 列的表⾃动构建查询的SQL语句。使⽤5个客⼾端，每个客⼾端查询20次。

mysqlslap --concurrency=5 --iterations=20 --number-int-cols=2 --number-chat -uroot -p

3. 从指定的⽂件中加载创建、插⼊和查询SQL语句。SQL⽂件中的语句以 ";" 分隔。使⽤5个客⼾

端，每个客⼾端查询5次。(自己后面执行一下)

mysqlslap --concurrency=5 --iterations=5 --query=query.sql --create=create.sql --delimiter=";" -uroot -p
常用选项:

mysqlslap 有如下常⽤选项，可以在命令⾏中指定，也可以在选项⽂件中通过 [mysqlslap] 和[client] 组进⾏指定