MySQL 基础介绍

MySQL介绍

相关概念

DB（DataBase）数据库。是一个存储数据的容器

DBA（Database Administrator）：数据库管理员。负责数据库的管理和维护的专业人员

DBMS（Database Management System）：数据库管理系统。是一种软件，用于创建和管理数据库。

App（Application）：应用程序。执行特定任务或一系列任务的软件程序

DBA使用DBMS管理DB，DBMS提供了访问和操作数据库的接口，App通过DBMS的接口访问DB，在这个过程中DBA需要确保App能高效、安全地访问DB。

MySQL 发展历史

MySQL 的第一个版本在 1996 年发布，命名方式是以其创始人 Michael Widenius 的女儿 My 命名。最初在 2008 年MySQL被 Sun Microsystems 收购。2010 年，甲骨文公司（Oracle）收购了 Sun Microsystems，从而也获得了 MySQL。甲骨文公司收购 Sun Microsystems（包括 MySQL）后，社区对 MySQL 未来可能闭源的担忧加剧，尽管到目前为止，MySQL 仍然是开源的。

所以MySQL的创始人Michael Widenius 基于MySQL5.5 创建了 MariaDB，这是为了确保有一个持续开源的分支。MariaDB 的名字是以他的另一位女儿 Maria 命名，MariaDB 旨在完全兼容 MySQL，包括 API 和命令行，确实使其成为 MySQL 的一个直接替代品。MariaDB 致力于保持与 MySQL 的兼容性，同时也引入了一些新的特性和改进。

虽然MariaDB 最初是基于 MySQL 5.5 版本开发的。MariaDB 的首个版本是 MariaDB 5.1，它在很大程度上与 MySQL 5.1 相同，但包括了一些额外的特性和改进，这些在 MySQL 中并不存在。随着时间的推移，MariaDB 开始引入更多的独立特性和改进，逐渐与 MySQL 的后续版本产生更多的差异。

MySQL 三大分支

Oracle MySQL

由 Oracle Corporation 维护的官方 MySQL 分支。在 Oracle 收购 Sun 公司后，Oracle MySQL 成为了最官方的 MySQL 版本。它提供社区版和企业版，其中企业版含有一些额外的特性和支持。

官网：https://www.mysql.com/

MariaDB

MariaDB 是 MySQL 的一个流行分支，由 MySQL 的原始开发者之一 Michael Widenius 创建。它致力于保持与 MySQL 的兼容性，同时加入了一些新的特性和改进。MariaDB 被设计为 MySQL 的直接替代品，且许多 Linux 发行版已经将 MariaDB 作为默认的数据库系统。

官网：https://mariadb.org/

Percona Server

Percona Server 是另一个流行的 MySQL 分支，由 Percona LLC 维护。它与 Oracle MySQL 和 MariaDB 都高度兼容，但专注于提供更高的性能和可靠性，特别适合高负载的生产环境。Percona Server 提供了一些独特的性能优化和特性，这些在标准 MySQL 中并不可用。

官网：https://www.percona.com/

MySQL 特点说明

MySQL 架构

MySQL是基于C/S架构开发而来，由客户端和服务端组成。

服务端： 它负责存储数据、处理数据操作请求（如查询、更新、删除等），并维护数据的完整性和安全性。

客户端： 以是任何用于与 MySQL 服务器交互的软件。这包括命令行客户端（如 mysql 命令行工具）、图形界面工具，或者通过数据库连接库（如 JDBC, ODBC）集成在各种应用程序中的客户端。

MySQL 账号

在MySQL中，账号是由两部分组成的，即用户名和主机名字，通过 @ 符号分隔用户名和主机名

user_name@host_name

通过主机名限制此用户可通过哪些远程主机连接mysql服务器，在创建用户或者给用户授权时，通常需要使用引号将用户名和主机名括起来，尤其是在用户名或主机名包含特殊字符或是保留字，用于确保 MySQL 正确解析这些字符串

在给MySQL账号指定主机名的时候，主机名可以指定为一个具体的IP地址也可以通过通配符来指定一个网段或者其它。（标准SQL常用的两个通配符 _ 和 % 它都是支持的）

例如：

tom@'192.168.2.1'

tom@'192.168.%.%'

MySQL SQL方言

SQL 方言指的是特定于数据库系统的 SQL 语言变种。虽然大多数数据库系统遵循 SQL 的核心标准，但它们通常会添加一些特定于系统的命令和功能，这些就构成了“方言”。

列出所有数据库

show databases;

切换当前数据库上下文

# 选择一个特定的数据库作为后续操作的目标数据库

use 数据库名称；

查看当前所处的数据库中所有表

show tables

查看当前 MySQL 服务器上的所有活动线程（即客户端连接）

show processlist;

查看表结构，显示列的属性信息：

desc table_name;

刷新权限：

FLUSH PRIVILEGES

该命令在 MySQL 中用于重新加载授权表（即用户和权限相关的表），以便立即应用对这些表所做的任何更改。当直接修改 MySQL 的授权表，如 mysql.user、mysql.db 等来更改用户权限或添加新用户时，这些更改不会立即生效。FLUSH PRIVILEGES 命令强制 MySQL 服务器重新读取授权表，使更改立即生效。

基于标准SQL的扩展

数据库系统通常会在标准 SQL 的基础上引入特定的扩展和变体，以提供额外的功能和优化，使其更适合于该数据库管理系统（DBMS）的特定需求和功能。

创建用户：

create user 'username'@'host' identified by  'password'

创建用户的时候需要使用引号将用户名以及主机名部分括起来，确保 MySQL 正确解析这些字符串

修改用户名：

rename user@'host' newuser@'host'

删除用户

drop user username@'host'

用户授权

创建一个用户后，默认只有一个 USAGE 权限，此时仅仅能登录数据库系统

GRANT
    [权限类型]     # 可以指定各种权限，如 SELECT, INSERT, UPDATE, DELETE 等。也可以使用 ALL PRIVILEGES 表示授予所有权限。
ON
    [数据库对象]  # 可以是数据库、表或者其他数据库对象。格式通常为 数据库名.表名。授予权限到所有数据库和表，可以使用 *.*。
TO
    [用户名]     # 格式通常为 '用户名'@'主机名'。

例如：

GRANT ALL PRIVILEGES ON *.* TO ehigh@'localhost';

说明： 在MySQL5.7.6之前的版本，可以在授权的时候创建用户

GRANT 权限 ON 数据库对象 TO 用户名 identified by 'password'

查看指定用户的全部权限：

SHOW GRANTS FOR username@'hostname'

取消用户指定权限：

EVOKE
    [权限类型]
ON
    [数据库对象]
FROM
    [用户名];

例如：

REVOKE SELECT ON db1.* FROM 'ehigh'@'localhost';

修改用户密码：

不同版本的MySQL修改密码的方式有所不同。

MySQL5.6版本及其以下

mysql5.6不能使用alter user命令来修改密码。可一使用update何set password来修改密码。

update mysql.user set password=password('123456') where User="xxx" and Host = "xxx";

set password for xxx@xxx = password('xxx');

MySQL 5.7 版本及其以上

从 MySQL 5.7 开始，推荐使用 ALTER USER 命令来修改密码。这是因为 MySQL 5.7 做了很多安全性方面的改进，包括如何处理密码。

ALTER USER 'username'@'hostname' IDENTIFIED BY 'newpassword';

例如：

alter user ehigh@'localhost' identified by 'eHIGH2014'

MySQL 服务端

查看版本信息

除了使用一些客户端工具可以查看版本信息外，还可以直接用mysqld的-V参数来查看版本信息

mysqld -V

使用 mysqld --verbose --help | less 可以查看mysqld的更多选项信息，mysqld是mysql服务端的主程序。

MySQL 默认数据库

在安装好MySQL服务端后，会默认提供四个数据库。

information_schema：MySQL 5.0之后增加，一个虚拟数据库（存在于内存中）且只读，包含了 MySQL 服务器的元数据，如数据库、表、列、索引等的信息。

performance_schema：MySQL 5.5开始新增的数据库，存储了关于 MySQL 服务器性能的详细数据，包括各种性能指标和内部执行统计信息。

mysql：MySQL的核心数据库，存储了数据库的用户账户、权限、系统配置等关键数据。

sys 数据库：MySQL5.7之后新增，提供了一个更易于理解和使用的视图层，以访问和解释 performance_schema 数据。它旨在简化数据库管理员对性能数据的查询和分析。

例如： 如果我们要备份一个数据库中的用户信息，备份mysql这个数据库就行了。

服务端组成

总的来说MySQL服务端可以理解为由两大部分组成：核心的 mysqld 守护进程和辅助的管理脚本或工具

服务端主程序： mysqld不管是旧版本（MySQL5.5）还是新版本（MySQL8.0），它都是服务端的主程序，是用于处理数据库操作（如连接请求、SQL 查询等）的守护进程。

辅助程序：

1、 旧版本（MySQL5.5）启动mysqld这个进程的时候，会通过一个封装的脚本（mysqld_safe）来启动，目的是安全地启动 mysqld 并在出现问题时尝试重启它。在新版本的 MySQL 中，由于 mysqld 自身已经集成了更多的安全和稳定性特性，可以直接运行mysqld进程。

2、 如果想在一个物理服务器上启动多个mysql实例，可以通过mysqld_multi工具来进行管理，因为每个实例都有自己的数据目录、端口号和配置文件。

服务端初始化

比如在重置了MySQL服务端的数据目录后，需要初始化MySQL的一些基本数据，才能正常启动MySQL服务端。

旧版本初始化数据

在一些旧版本中（MySQL5.6及以下），是通过mysql_install_db工具来初始化基本数据，用于创建必要的系统表和目录。（mysql_install_db 是 MySQL 的一个辅助脚本，它通常与 MySQL 服务器一起分发）

新版本初始化数据

MySQL5.7.6及其以上的版本中，mysqld提供了两个参数来初始化基本数据。

--initialize-insecure  # 初始化得root用户无密码，可以无密码登录

--initialize  # 这个命令会为root用户创建一个临时随机密码 在MySQL错误日志中找到这个随机生成的临时密码

初始化数据的时候有以下注意点：

1、需要确保MySQL的数据目录是空的，即里面没有任何内容，否则可能没法初始化成功

2、需要确保MySQL服务的进行是停止状态，不然只会再数据目录生成部分文件。例如：只会生成一个auto.conf文件

3、需要确保MyQL配置文件中的innodb_force_recovery选项不是启用状态

4、用户必须有足够的权限来创建、写入和修改MySQL的数据目录中的文件和子目录，所以可以用root用户身份来执行，如果不成功就用mysql用户来下执行。sudo -u mysql mysqld --initialize-insecure

MySQL 客户端

MySQL 是一个基于客户端-服务器（C/S）架构的数据库管理系统。在这种架构中，数据存储和管理由位于服务器端的 MySQL 数据库服务器负责。要对这些数据进行管理或执行操作，需要通过客户端应用程序连接到 MySQL 服务器。

1、客户端工具通过网络（使用 TCP/IP 协议）或本地 socket 套接字连接到 MySQL 服务器。这个连接过程涉及到认证和授权，确保只有拥有适当凭证的用户才能访问数据库。

2、建立连接后，客户端可以发送SQL命令到服务端进行数据管理，在MySQL中，有很多SQL方言或者对标准SQL的一些扩展命令。

3、服务端接收到客户端的请求后，进行相应的响应。

客户端可以是多种形式，例如命令行工具（如 mysql 客户端）、图形用户界面工具（如 MySQL Workbench）、程序语言中的数据库连接库（如 Java 中的 JDBC）等。这些客户端提供了与 MySQL 服务器交互的不同方式和界面。

mysql

在Linux操作系统中连接到MySQL服务端，最常用的就是命令行工具mysql了（这个命令行客户端工具的名字就叫作mysql），它提供了许多参数选项来和服务端进行交互。

交互式操作

这种方法事交互式连接到服务端，成功连接到服务端后，用户可以直接在命令行界面输入 SQL 命令，然后 MySQL 服务器会处理这些命令并返回结果。这个过程是实时的，允许用户根据需要执行各种 SQL 语句，从而与数据库进行直接的交互。

如果不指定主机地址，默认是localhost，在大多数情况下，连接到 localhost 会使用本地 socket 文件（可以配置），而不是 TCP/IP。如果不指定端口号。默认是3306端口

mysql -h ip_host -u username -p passeord -P port

在这个界面就可以输入相关的SQL语句来和服务端进行交互了，如果要退出连接，输入exit或者quit即可退出。

非交互式操作

非交互式和服务端操作，这种方式一般用在脚本或程序自动执行 SQL 命令。通常需要使用 -e 参数来指定要执行的 SQL 命令，如果SQL语句中没有显式指定数据库，就需要在在用户名和密码参数之后加上数据库名来实现。

例如：

# 没有显示指定数据库
mysql -u username -p students -e "SELECT * FROM table1"

# SQL语句中显示指定数据库就不用加数据库名字
mysql -u username -p database -e "SELECT * FROM stutens.table"

数据库名称.表名称 这种格式是标准 SQL 的一部分，这种格式被称为完全限定的表名，它明确指定了表所属的数据库。

在shell脚本中，经常用到的还有 -B 和 -s 这两个选项。

  • -s 选项可以减少输出的冗余信息，如提示、进度条和一些格式化字符。

  • -B 选项用于将格式化输出的信息，让其更适合程序处理

例如：默认的输出内容是这样的

添加了 -s 选项，让其较少输出一些格式化和额外信息（在某些情况下，仍然会包含列名。所以列名Database跟着输出了）

再添加 -B 选项，可以让输出的内容格式化为适合非交互式程序处理的形式（没有显示列名，直接列出了数据库名称）

单独使用 -B 选项（批处理模式）确实会影响输出格式，使其更适合程序处理，但可能仍然包括列名。而 -s 选项（静默模式）会进一步简化输出，通常会移除列名和其他额外的格式化，所以这就是镜像需要 -B选项和-s选项需要结合使用的原因。

在非交互式模式下使用 mysql 客户端时，自动补全功能并不适用，也不会被触发。非交互式模式意味着命令或脚本是预先定义好的，直接被 mysql 客户端执行，而不是由用户在交互式命令行中实时输入。因此，自动补全功能在这种模式下没有用处。-A 选项用于禁用自动补全功能，其主要目的是为了减少 mysql 客户端启动时读取和构建数据库、表名等信息所需的时间。

例如： 某个非交互式的SQL命令包含了大量的库和表，自动补全功能的初始化可能会显著延长客户端的启动时间。这个时候使用 -A 选项效果很好。

所以综上所述，在shell脚本中使用mysql和服务端交互时一般是这样的：

mysql -A -hx.x.x.x -uusername -ppassword -P3306 -Bse "SQL语句"

mysqladmin

mysqladmin这个客户端工具主要是执行一些管理相关的操作，主要用于数据库的维护、监控和管理任务。

数据库状态查看

mysqladmin -h host -u username -p password -P port  status

检查服务器是否正常运行

该命令会试图连接到 MySQL 服务器，并且在成功连接后返回一个响应，通常用于监控和脚本中以确认 MySQL 服务的状态。如果服务器正在运行并且可以接受连接，命令通常会输出 mysqld is alive

mysqladmin -h host -u username -p password -P port  ping

查看服务器版本等信息信息

mysqladmin -h host -u username -p password -P port  version

posted on 2023-11-22 17:58  背对背依靠  阅读(146)  评论(0)  编辑  收藏  举报