MySQL程序

目录

MySQL程序

常用的MySQL的程序

mysqld程序 

mysql客户端

客户端命令的常用的选项

 配置文件

配置文件语法

MySQL客户端命令

​编辑 .sql 文件中执行SQL语句

mysqlcheck （表维护程序）

 Mysqldump（数据库备份程序）

 mysqladmin 服务器管理程序

mysqlshow （显示数据库、表和列的信息）

mysqldumpslow （总结慢查询日志文件）

mysqlbinlog （处理二进制日志文件）

mysqlslap （负载仿真客户端）

MySQL程序

 MySQL服务器程序

• mysqld：MySQL服务器的守护进程，主要负责处理客户端的连接，执行SQL语句，管理数据库存储等
  • 支持多种存储引擎，同时提供事务管理、并发控制等功能
  • 后台自动启动，不需要手动运行
•  mysqld_safe：mysqld的启动脚本，提供了对mysqld进程的监控和管理
  • 自动重启崩溃的mysqld进程，提高服务器的可靠性

MySQL客户端程序

• mysql：客户端，主要用户连接MySQL服务器，执行SQL语句，同时管理数据库
  • 支持交互式和批处理模式，提供丰富的命令行选项等

// 连接服务器
mysql -u username -p

// 执行SQL脚本
mysql -u username -p < script.sql

其余程序，参考下文笔记内容 

常用的MySQL的程序

• mysqld：

  • 作用：MySQL 服务器的守护进程，MySQL 数据库的核心程序，必须在运行状态下才能提供服务。

• mysql：

  • 作用：MySQL 客户端程序，用于交互式地输入 SQL 语句或者批处理执行 SQL 文件的命令行工具。

• mysqlcheck：

  • 作用：用于检查、修复、分析和优化表的表维护客户端。

• mysqldump：

  • 作用：将 MySQL 数据库的内容转储为 SQL、文本或 XML 文件的客户端工具，常用于备份。

• mysqlimport：

  • 作用：将文本文件导入 MySQL 表的客户端工具，适合批量导入数据。

• mysqladmin：

  • 作用：执行数据库管理操作的客户端工具，可以用来创建或删除数据库、刷新权限、重新载入日志文件等，还可以用于检查 MySQL 服务器的状态和版本信息。

• mysqlshow：

  • 作用：显示数据库、表以及列和索引信息的客户端工具，帮助快速查看数据库结构。

• mysqldumpslow：

  • 作用：用于读取和汇总慢查询日志内容的实用程序，帮助分析系统中的慢查询。

• mysqlbinlog：

  • 作用：从二进制日志中读取 SQL 语句的实用程序，用于查看日志或恢复数据。

• mysqlslap：

  • 作用：MySQL 的负载测试工具，用来模拟多个客户端同时访问 MySQL 服务器，并输出每个阶段的使用时间，适用于性能测试。

mysqld程序 

基本作用分析

mysqld是MySQL的主服务器进程，运行在后台，主要负责监听客户端连接的请求，主要用于处理所有的数据库操作，包括数据的读写、事务管理等。

mysqld支持运行在多种操作系统上，同时提供一致的功能和性能；与此同时，支持的多种数据库引擎

mysqld的启动和关闭 

启动mysqld

• 直接执行mysqld，带上一些必要的选项 

mysqld [选项]

关闭mysqld

• 可以直接使用mysqladmin工具，也可以通过发送信号对其进行关闭

mysqladmin -u root -p shutdownkill -TERM <mysqld进程ID>

mysqld 核心功能分析 

• 连接管理：可以负责客户端连接，通过套接字编程、TCP或者命名管道接收客户端连接，同时为每个连接对应一个服务器线程，处理客户端的请求
• SQL请求处理：可以对查询的命令进行解析， 同时进行查询优化
• 事务管理：提供ACID特性，同时支持四种事务的隔离级别
• 存储引擎：其可以通过存储引擎API与不同的引擎进行交互

mysql客户端

连接数据库的方式（db_name：数据库名称）

// 长选项格式
mysql --user=user_name --password [数据库名]// 短选项格式
mysql -u user_name -p [数据库名]// 输入密码

客户端命令的常用的选项

连接相关选项

-h  , --host：指定要连接的MySQL服务器主机名或者IP地址（如果不指定，则默认连接到本地主机，对于本地主机连接，MySQL则会尝试使用unix套接字或者的命名管道）

mysql -h 127.0.0.1 -u root -p

-p ，--port：指定MySQL服务器的端口号

mysql -h 192.168.1.100 -P 3306 -u user -p

-u ， --user ：指定连接MySQL服务器的用户名

mysql -u root -p

-p , --password：用于提示输入密码或者直接在命令行中提供密码

mysql -u root -p

 --protocol ：指定连接MySQL服务器所使用的协议类型（TCP/SOCKET/PIPE/MEMORY）

mysql --protocol=TCP -h localhost -u user -p

-S， --socket：用于指定套接字文件或者windows命名管道

mysql -S /var/lib/mysql/mysql.sock -u user -p

 执行SQL语句与脚本

-e , --execute：直接在命令行中执行指定的SQL语句

mysql -u root -p -e "SHOW DATABASES;"

--init-command：在建立连接后立即执行指定的SQL语句 

mysql -u user -p --init-command="SET NAMES utf8mb4"

--database , -D ：使用默认使用的数据库

mysql -u user -p -D mydatabase

--ssl：启用SSL加密处理，确保客户端与服务端的通信是加密的

mysql -u user -p --ssl

输出格式和显示

-t，--table ：将查询结果按照表格的形式显示

mysql -u user -p -t -e "SELECT * FROM users;"

-s , --silent：以安静模式输出，去除分隔符和标题

mysql -u user -p -s -e "SELECT * FROM users;"

-N , --skip-column-names：在结果中不显示列名

mysql -u user -p -N -e "SELECT * FROM users;"

--colum-type-info：显示查询结果中每一列的数据类型信息

mysql -u user -p --column-type-info -e "SELECT * FROM users;"

文件和日志 

-E  , --vertical ：以垂直格式显示输出，每行显示一列

mysql -u user -p -E -e "SELECT * FROM users WHERE id=1;"

--tee：用于将所有的查询和结果同时输出到指定文件中

mysql -u user -p --tee=/path/to/logfile

--prompt：自定义MySQL客户端的提示符

mysql -u user -p --prompt="MySQL [\d]> "

其他常用选项 

mysql -u user -p --comments

--help：显示帮助信息

mysql --help

--version , -V ：显示MySQL客户端的版本信息

mysql --version

--compress：客户端和服务器之间使用压缩协议

mysql -u user -p --compress

--comments：启用或者禁用在查询中发送的注释内容

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

命令行中使用选项的特殊注意点 

• 选项后面使用单破折号 或者双破折号最终的效果类似

•  选项名称是区分大小写的，小写和大写都是合法，但是表示的含义不同

// 下面两个等价mysql --version --help
mysql -v -?// 下面两个等价mysql --version
mysql -V

•  某些选项是需要在后面指定一个数值的，例如在-h后面需要添加一个IP地址指定程序中的Mysql主机

mysql -h 127.0.0.1
mysql --host=127.0.0.1

• 带值的长格式选项，通常是使用=符号来分割选项的名称和数值；但是对于带值的短选项，则选项值可以紧跟在选项后，也可以使用空格隔开然后输入

//下面两者是等价的
mysql -p123456 //123456是密码
mysql -p 123456

• 在 MySQL 选项名称中，破折号（-）和下划线（_）在大多数情况下可以互换使用。比如选项 --skip-grant-tables 和 --skip_grant_tables 是等价的，效果相同。不过需要注意的是，某些情况下破折号不能被替换为下划线（通常开头的--不可以更换）
• 数值选项，如果数值后带有K则表示*1024，M G则是依次后推

// mysqladmin对服务器执行1024次ping，每次ping之间休眠3秒mysqladmin --count=1k --sleep=3 ping -uroot -p

•  命令行中的包含空格的选项值必须使用双引号引起来，--execute为例，连接到MySQL服务器的时候执行一条语句，但是不进入服务器的内部

mysql --execute="SQL语句"
mysql -e "SQL语句"

 

 配置文件

使用方法

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

• 分析conf.d 和 mysql.conf.d 文件夹中内容，前者是客户端配置文件，后者是服务端配置文件

 

• Windows系统中的配置文件后缀名为.ini 或者 .cnf
• Linux系统的中的配置文件后缀名为 .cnf

读取配置文件优先级问题分析

• --defaults-extra-file是用来提供额外的位置文件的，不会影响其他默认的配置文件 

•  上述的选项是全部的配置文件都会被加载，而下述的则是只有指定的配置文件会被加载

配置文件语法

配置文件的基本方法（文件在/etc/my.cnf中）

选项文件中指定选项的时候，省略两个前导破折号，并且每一个行表示一个选项

 省略两个前破折号（命令行选项和配置文件选项）

• 命令行选项，使用MySQL命令行工具的时候，选项通常是以--开头，但是配置文件中不需要直接写破折号，而是直接写选项名称

 // 命令行选项
mysqld --port=3306 --datadir=/var/lib/mysql
mysql --user=root --password

// 配置文件选项[mysqld]
port=3306
datadir=/var/lib/mysql[client]
user=root
password=your_password

配置文件的基本语法

基本组成

• 配置组：以方括号[ ]包围的名称，用于表示一组相关的配置选项
• 配置选项：以option_name = value的形式来指定

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock[mysqld]
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

 配置组的使用

• [ mysqld ]：用于MySQL服务器守护进程
• [ client ]：所有MySQL客户端的通用设置
• [ mysql ]：用于MySQL命令行客户端特定设置
• [ mysqld_safe ]：用于mysqld_safe启动脚本设置

[client]
user = root
password = mypassword[mysql]
prompt = "\\u@\\h [\\d]> "

注释的使用方法

• 使用注释符号 # 或者 ；

# This is a comment
; This is also a comment
[mysqld]
# Set the default storage engine
default_storage_engine = InnoDB

包含其他配置文件

• !include指令：包含指定的配置文件
• !includedir指令：包含指定目录下的所有配置文件 

[mysqld]
!include /etc/mysql/mysql.conf.d/mysqld.cnf
!includedir /etc/mysql/conf.d/

配置文件的解析顺序

• 全局配置文件：先解析/etc/my.cnf等全局配置文件
• 用户配置文件：然后解析用户等级的配置文件 
• 优先级：后面解析的配置文件中的选项会覆盖之前的设置

MySQL客户端命令

命令总览

结束标识符：; 和 \g 和 \G（行显示）

查看服务器状态（status）

connect 客户端命令 

 重新指定SQL语句的结束标识符

退出命令：exit 和 quit命令

tee（\T）指定查询结果放在哪个路径下，类似于日志的功能

prompt：修改输入命令的提示符

 数据库执行系统级别命令 system 

 .sql 文件中执行SQL语句

使用source命令导入

具体使用场景在于开发环境--测试环境---生产环境，例如是把一个数据库从服务器A复制到服务器B中，那么可以先从服务器A导出数据到.sql文件，然后在服务器器B中执行该文件即可

该命令主要应用于MySQL客户端执行外部的SQL脚本文件，相当于将文件中的SQL语句依次在客户端中执行，适用于大量SQL语句、初始化数据库、批量插入和迁移数据的场景

创建sql文件，同时写入SQL语句

进入服务器中执行该sql文件

验证语句是否成功执行

通过客户端直接导入该脚本文件

mysqlcheck （表维护程序）

基本作用分析

mysqlcheck是MySQL提供的一个命令行使用工具，专门用来检查、修复、优化和分析MySQL数据库中的表，其可以与MySQL服务器通信，使用SQL语句来执行这些操作

具体功能分析

• 检查表：检查表的完整性和一致性，查出可能存在的问题
• 修复表：修复受损的表，例如表的索引或者数据不一致的问题
• 优化表：重新构建表和索引，释放未使用的空间，从而提高查询性能
• 分析表：更新表的关键统计信息，从而实现更加高效的查询

基本用法说明

// 基本语法
mysqlcheck [选项] [数据库名 [表名 ...]]

• 数据库名：要操作的数据库名称
• 表名：需要操作的表名，如果不指定默认就是全部的表内容

常用选项

连接选项

• -u , --user：用户，指定连接的用户名
• -p , --password：指定密码
• -h ：主机名，指定要连接的主机
• -p ：指定端口号，不指定就是3306 
• -s ：指定套接字

操作选项

• --all-databases , -A ：检查所有的数据库
• --databases，-B：后面跟一个或者多个数据库名，检查指定的数据库
• --auto-repair：如果表有问题就自动修复
• --check, -c ：检查表（默认操作）
• --repair ， -r：修复表
• --optimize，-o：优化表（不要经常去优化）
• --analyze，-a：分析表
• --extended，-e：执行更加彻底的检查和修复
• --quick：快速模式

其他选项

• --verbose，-v：详细模式，显示更多的信息
• --compress：启用客户端与服务器之间的压缩传输
• --debug：写入调试日志

检查单个数据库中的所有表

检查数据库中的所有表

自动修改发现问题的表

优化表

分析表

使用详细模式 

 Mysqldump（数据库备份程序）

该程序就是用于生成数据库的逻辑备份，其通过数据库表结构、数据、索引等导出一系列SQL语句，利用这些语句可以快速重新构建数据库。

主要功能

• 备份数据库：导出数据库的结构和数据，生成可执行的SQL脚本
• 迁移数据：将数据库从一个服务器迁移到另一个服务器中
• 复制数据库：创建数据库的副本，用于测试或者开发环境
• 导出数据

基本语法

 数据库名 -- 需要备份数据库名称；表名 -- 要备份的表名称，如果不指定表名就要导出整个数据库

mysqldump [选项] 数据库名 [表名 ...]

常用选项分析

备份选项

• --databases：后面加一个或者多个数据库名，备份指定的数据库
• --all-databases：备份所有的数据库
• --tables：后根表名，只备份指定的表
• --routines：包括存储过程和函数
• --evnents：包括事件

输出格式

• -n ：不生成create database语句
• -t ：不导出表结构，只导出数据
• -d：不导出数据，只导出表结构
• --quick：逐行检索数据，减少内存使用
• --compress：启用客户端和服务器之间的压缩传输

备份数据库

恢复备份数据库 （将mydatabase.sql中的内容导入到mydatabase数据库中）

 恢复备份的方法同样可以使用source命令

只导出表的结构（不含有数据） 

 mysqladmin 服务器管理程序

程序运行在客户端，作用于执行MySQL服务器的管理程序，其允许发送管理命令到MySQL服务器，从而执行诸如监控服务器状态、管理用户账户等任务

主要功能分析

• 服务器控制：用于启动、关闭、重启MySQL服务器
• 状态监控：可以查看服务器的当前状态和统计信息
• 账户管理：修改用户密码、创建以及删除用户操作
• 数据库管理：可以创建和删除数据库
• 调试和日志：用于控制日志文件、刷新权限等

服务器控制命令

 shutdown 

• 用于安全关闭MySQL服务器

mysqladmin -u root -p shutdown

reload

• 重新加载权限表，刷新服务器的权限信息

refresh

• 刷新表和日志，关闭并重新打开所有表文件，刷新日志文件

mysqladmin -u root -p refresh

状态信息命令

 status

• 显示简要的服务器状态信息

extended-status

• 显示详细的服务器状态变量和其具体数值

variables

• 显示服务器的系统变量和当前数值

mysqladmin -u root -p variables

 create db_name 

• 创建一个新的数据库

mysqladmin -u root -p create mydatabase

drop db_name 

• 删除指定的数据库

mysqladmin -u root -p drop mydatabase

使用的服务器管理程序可以结合脚本，实现自动化的管理和监控

mysqlshow （显示数据库、表和列的信息）

基本概述

mysqlshow客户端主要用于快速查询哪些数据库、数据库中的表中的列或者索引。

mysqlshow [option] [db_name [tbl_name[col_name]]]

• 如果没有指定特定的数据库或者行列，那么就会显示数据库所有与之匹配的类型
• 常用选项是和[client]组的指定类似
• 参数是可以使用通配符

基本操作

查看数据库中所有表

显示表中所有列信息

仅仅显示一列

mysqldumpslow （总结慢查询日志文件）

含义

该程序就是解析那些查询时间非常长，执行的时间超过其阈值的查询信息（慢查询），慢查询的相关信息通常需要使用日志记录记录下来（慢查询日志）

mysqldumpslow [options] [log_file]

常用选项分析

• c：按次数排序（count）
• l：按查询锁定时间排序（lock time）
• r：按查询返回的行数排序（rows sent）
• t：按查询执行时间排序（query time）

实践命令

慢查询日志保存位置

mysqlbinlog （处理二进制日志文件）

基本概念

数据库的每一次增删查改都是一次“事件”，每个事件都会以二进制的形式记录到二进制日志文件中，该程序就是专门用于处理二进制日志文件。

主从同步经常使用二进制日志文件，主节点的操作都会记录到二进制文件中，然后从节点通过定期读取二进制文件中的内容来更新自己的数据库。

mysqlbinlog程序则是按照文本的形式来显示二进制日志文件中的内容

mysqlbinlog [options] log_file_name

实践

 二进制日志文件存储

从指定位置开始读取日志

从指定时间开始读取

 

 仅显示指定数据库的相关日志

以详细模式输出二进制日志内容

 

输出内容可以使用重定向的方法输出到文件中

mysqlbinlog binlog.000001 > output.sql

数据恢复操作

mysqlbinlog --start-datetime="2024-09-19 00:00:00" --stop-datetime="2024-09-20 00:00:00" binlog.000001 > restore.sql
mysql -u root -p < restore.sql

主从复制（主从服务器数据同步）

mysqlbinlog --read-from-remote-server --host=master_host --user=replication_user --password=replication_password binlog.000001 > binlog.sql
mysql -u slave_user -p < binlog.sql

mysqlslap （负载仿真客户端）

基本含义

mysqlslap是一个压力测试工具，也就是用于模拟多线程并发对数据库进行查询操作，从而评估数据库的性能。

主要功能分析

• 创建测试表和数据
• 对表中的数据进行并发查询，测试数据库的读写性能
• 测试结束后，删除测试表和数据

mysqlslap [options]

注意事项

• 如果指定一个包含SQL语句的文件，那么默认情况下每行都必须包含一条语句
• 如果要将一条语句分为多行书写，可以使用--delimiter选项指定不同的分隔符

 代码测试

自动生成select查询测试数据库的读性能

指定要自动生成的SQL语句数量

 

指定表中生成的整数和字符列的数量

 

输出压测的详细信息