18.MYSQL数据库(1)

MySQL 数据库

本章内容

  • 关系型数据库基础
  • 安装MySQL
  • 管理数据库和表
  • 用户和权限管理
  • 函数,存储过程,触发器和事件
  • MySQL架构
  • 存储引擎
  • 服务器选项,系统和状态变量
  • 优化查询和索引管理
  • 锁和事务管理
  • 日志管理
  • 备份还原(重点掌握)
  • MySQL集群
  • 压力测试

1 数据库原理

1.1 数据的时代

  • 涉及的数据量大
  • 数据不随程序的结束而消失
  • 数据被多个应用程序共享
  • 大数据

数据的分类:

  • 结构化的数据:即有固定格式和有限长度的数据。例如填的表格就是结构化的数据,国籍:中华人
    民共和国,民族:汉,性别:男,这都叫结构化数据
  • 非结构化的数据:非结构化的数据越来越多,就是不定长、无固定格式的数据,例如: 网页,图片文
    件,有时候非常大,有时候很小;例如语音,视频都是非结构化的数据
  • 半结构化数据:比如:XML或者HTML的格式的数据

1.2 数据库的发展史

1.2.1 文件管理系统的缺点

  • 编写应用程序不方便
  • 不支持对文件的并发访问
  • 无安全控制功能
  • 难以按用户视图表示数据
  • 数据间联系弱
  • 数据冗余不可避免
  • 应用程序依赖性

1.2.2 数据库系统发展阶段

  • 萌芽阶段:文件系统
    使用磁盘文件来存储数据
  • 初级阶段:第一代数据库
    出现了网状模型、层次模型的数据库
  • 中级阶段:第二代数据库
    关系型数据库和结构化查询语言
  • 高级阶段:新一代数据库
    “关系-对象”型数据库

1.3 DBMS 数据库管理系统

  • Database:数据库是数据的汇集,它以一定的组织形式存于存储介质上
  • DBMS:Database Management System, 是管理数据库的系统软件,它实现数据库系统的各种功能。是数据库系统的核心
  • DBA:Database Administrator:数据库管理员,负责数据库的规划、设计、协调、维护和管理等工作
  • Applicatoin:应用程序,指以数据库为基础的应用程序

1.4 数据库管理系统的优点

  • 程序与数据相互独立
  • 保证数据的安全、可靠
  • 最大限度地保证数据的正确性
  • 数据可以并发使用并能同时保证一致性
  • 相互关联的数据的集合
  • 较少的数据冗余

1.5 数据库管理系统的基本功能

  • 数据定义
  • 数据处理
  • 数据安全
  • 数据备份

1.6 数据库系统的架构

  • 单机架构
  • 大型主机/终端架构
  • 主从式架构(C/S)
  • 分布式架构

1.7 各种数据库管理系统

1.7.1 层次数据库

在这里插入图片描述
分层结构由IBM在20世纪60年代开发,并在早期大型机DBMS中使用。记录的关系形成了一个树状模
型。这种结构简单,但缺乏灵活性,因为这种关系仅限于一对多关系。
代表数据库:IBM IMS(信息管理系统)

1.7.2 网状数据库

在这里插入图片描述
1964年通用电气GE公司的 Charles Bachman 成功地开发出世界上第一个网状数据库IDS(集成数据存
储),IDS 具有数据模式和日志的特征,只能在GE主机运行

1.7.3 RDBMS 关系型数据库

Relational Database Management System,关系模型最初由IBM公司的英国计算机科学家埃德加·科德
(Edgar F. Codd)于1969年描述,1974年,IBM开始开发系统R,这是一个开发RDBMS原型的研究项目。
然而,第一个商业上可用的RDBMS是甲骨文,于1979年由关系软件(现为甲骨文公司)发布

1.7.3.1 关系统型数据库相关概念

  • 关系Relational :关系就是二维表,其中:表中的行、列次序并不重要
  • 行row:表中的每一行,又称为一条记录record
  • 列column:表中的每一列,称为属性,字段,域field
  • 主键Primary key:PK ,一个或多个字段的组合, 用于惟一确定一个记录的字段,一张表只有一个主
    键, 主键字段不能为空NULL
  • 唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而
    且UK字段可以为NULL
  • 域domain:属性的取值范围,如,性别只能是’男’和’女’两个值,人类的年龄只能0-150

1.7.3.2 常用关系数据库

  • MySQL: MySQL, MariaDB, Percona Server
  • PostgreSQL: 简称为pgsql,EnterpriseDB
  • Oracle
  • MSSQL Server
  • DB2

1.7.3.3 数据库排名

https://db-engines.com/en/ranking

在这里插入图片描述

1.8 关系型数据库理论

1.8.1 实体-联系模型E-R

  • 实体Entity:客观存在并可以相互区分的客观事物或抽象事件称为实体,在E-R图中用矩形框表示实
    体,把实体名写在框内
  • 属性:实体所具有的特征或性质
  • 联系:联系是数据之间的关联集合,是客观存在的应用语义链
    • 实体内部的联系:指组成实体的各属性之间的联系。如职工实体中,职工号和部门经理号之间有一种关联关系
    • 实体之间的联系:指不同实体之间联系。例:学生选课实体和学生基本信息实体之间
    • 实体之间的联系用菱形框表示

1.8.2 联系类型

  • 一对一联系(1:1)
  • 一对多联系(1:n):外键
  • 多对多联系(m:n):增加第三张表

1.8.3 数据的操作

开发工程师 CURD (Create,Update,Read,Delete)

  • 数据提取:在数据集合中提取感兴趣的内容。SELECT
  • 数据更新:变更数据库中的数据。INSERT、DELETE、UPDATE

1.8.4 数据库规划流程

  1. 收集数据,得到字段
    收集必要且完整的数据项
    转换成数据表的字段
  2. 把字段分类,归入表,建立表的关联
    关联:表和表间的关系
    分割数据表并建立关联的优点
    节省空间
    减少输入错误
    方便数据修改
  3. 规范化数据库

1.8.5 数据库的正规化分析

数据库规范化,又称数据库或资料库的正规化、标准化,是数据库设计中的一系列原理和技术,以减少数据库中数据冗余,增进数据的一致性。关系模型的发明者埃德加·科德最早提出这一概念,并于1970年代初定义了第一范式、第二范式和第三范式的概念

设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,不同的规范要求被称为不同范式,各种范式呈递次规范,越高的范式数据库冗余越小

目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴德斯科范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般数据库只需满足第三范式(3NF)即可

规则是死的,人是活的,所以范式是否必须遵守,要看业务需要而定

掌握范式的目的是为了在合适的场景下违反范式

1.8.5.1 第一范式:1NF

无重复的列,每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性,确保每一列的原子性。除去同类型的字段,就是无重复的列。

说明:第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。

1.8.5.2 第二范式:2NF

第二范式必须先满足第一范式,属性完全依赖于主键,要求表中的每个行必须可以被唯一地区分,通常为表加上每行的唯一标识PK,非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键。
在这里插入图片描述

1.8.5.3 第三范式:3NF

满足第三范式必须先满足第二范式属性,非主键属性不依赖于其它非主键属性。第三范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息,非PK的字段间不能有从属关系。

在这里插入图片描述

1.8.6 SQL 结构化查询语言简介

SQL:Structure Query Language,结构化查询语言是1974年由Boyce和Chamberlin提出的一个通用的、功能极强的关系性数据库语言

SQL解释器:将SQL语句解释成机器语言

数据存储协议:应用层协议,C/S

  • S:server, 监听于套接字,接收并处理客户端的应用请求
  • C:Client

客户端程序接口

  • CLI
  • GUI

应用编程接口

  • ODBC:Open Database Connectivity
  • JDBC:Java Data Base Connectivity

1.8.7 SQL 基本概念

  • 约束:constraint,表中的数据要遵守的限制
  • 主键:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行;必须提供数据,即
    NOT NULL,一个表只能有一个
  • 惟一键:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行;允许为NULL,一个
    表可以存在多个
  • 外键:一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
  • 检查:字段值在一定范围内
  • 索引:将表中的一个或多个字段中的数据复制一份另存,并且按特定次序排序存储

1.8.8 关系运算

  • 选择:挑选出符合条件的行
  • 投影:挑选出需要的字段
  • 连接:表间字段的关联

1.8.9 数据抽象

  • 物理层:数据存储格式,即RDBMS在磁盘上如何组织文件
  • 逻辑层:DBA角度,描述存储什么数据,以及数据间存在什么样的关系
  • 视图层:用户角度,描述DB中的部分数据

1.8.10 关系模型的分类

  • 关系模型
  • 基于对象的关系模型
  • 半结构化的关系模型:XML数据

2 MySQL安装和基本使用

2.1 MySQL 介绍

在这里插入图片描述

2.1.1 MySQL 历史

1979年:TcX公司 Monty Widenius,Unireg
1996年:发布MySQL1.0,Solaris版本,Linux版本
1999年:MySQL AB公司,瑞典
2003年:MySQL 5.0版本,提供视图、存储过程等功能
2008年:Sun公司 以10亿美元收购MySQL
2009年:Oracle公司以 75 亿美元收购 sun 公司
2009年:Monty成立MariaDB

2.2.2 MySQL系列

2.2.2.1 MySQL 的三大主要分支

  • mysql
  • mariadb
  • percona Server

2.2.2.2 官方网址

https://www.mysql.com/

http://mariadb.org/

https://www.percona.com/

2.2.2.3 官方文档

https://dev.mysql.com/doc/

https://mariadb.com/kb/en/

https://www.percona.com/software/mysql-database/percona-server

2.2.2.4 版本演变

MySQL:5.1 --> 5.5 --> 5.6 --> 5.7 -->8.0
MariaDB:5.1 -->5.5 -->10.0–> 10.1 --> 10.2 --> 10.3 --> 10.4 --> 10.5
MySQL主流5.6,5.7版本,8.0也有企业在使用。

MySQL被Sun收购后,搞了个过渡的6.0版本,没多久就下线了,后来被Oracle收购后,终于迎来了像样的
5.6版本,之后就是5.7、8.0版本。由于6.0版本号已被用过,7.x系列版本专用于NDB Cluster,因而新
版本号从8.0开始。

2.2.3 MySQL的特性

  • 开源免费
  • 插件式存储引擎:也称为“表类型”,存储管理器有多种实现版本,功能和特性可能均略有差别;用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎
MyISAM ==> Aria
InnoDB ==> XtraDB
  • 单进程,多线程
  • 诸多扩展和新特性
  • 提供了较多测试组件

2.2 MySQL 安装方式介绍和快速安装

MySQL server端端口号为3306

2.2.1 安装方式介绍

  • 程序包管理器管理的程序包
  • 源代码编译安装
  • 二进制格式的程序包:展开至特定路径,并经过简单配置后即可使用

2.2.2 RPM包安装MySQL

CentOS 安装光盘
项目官方:https://downloads.mariadb.org/mariadb/repositories/
国内镜像:https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/
https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/

CentOS 8:安装光盘直接提供

  • mysql-server:8.0
  • mariadb-server : 10.3.17
[root@centos8 ~]#yum install mysql-server			#安装
[root@centos8 ~]#systemctl enable --now mysqld		#第一次启动时,会自动把系统数据初始化放在所在目录里
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@centos8 ~]#ls /var/lib/mysql					#数据库存放(系统数据和用户数据)所在路径
 auto.cnf          ib_buffer_pool   mysql.ibd            private_key.pem
 binlog.000001     ibdata1          mysql.sock           public_key.pem
 binlog.index      ib_logfile0      mysql.sock.lock      server-cert.pem
 ca-key.pem        ib_logfile1      mysql_upgrade_info   server-key.pem
 ca.pem            ibtmp1           mysqlx.sock          sys
 client-cert.pem  '#innodb_temp'    mysqlx.sock.lock     undo_001
 client-key.pem    mysql            performance_schema   undo_002

CentOS 7:安装光盘直接提供

  • mariadb-server:5.5 服务器包
  • mariadb 客户端工具包

范例: CentOS 7 安装MySQL5.7

[root@centos7 ~]#vim /etc/yum.repos.d/mysql.repo					#建立仓库
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck=0

-------
[root@centos7 ~]#yum install -y mysql-community-server.x86_64 		#安装
[root@centos7 ~]#systemctl enable --now mysqld						#启动服务
-------
[root@centos7 ~]#mysql					#centos7会默认生成一个密码
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@centos7 ~]#grep password /var/log/mysqld.log
2021-01-28T09:11:01.698455Z 1 [Note] A temporary password is generated for root@localhost:JUI4NslG5C:o
2021-01-28T09:15:37.741193Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)

----
#使用默认密码连接,更改密码
----

[root@centos7 ~]#mysql -uroot -p'JUI4NslG5C:o'
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 3
Server version: 5.7.33

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> ALTER USER root@'localhost' identified by 'Magedu0!';
Query OK, 0 rows affected (0.00 sec)							#密码需设置复杂一些,简单密码不允许
    ->
方法二: 
[root@centos7 ~]#mysqladmin -uroot -p'JUI4NslG5C:o' password 'Magedu0!'

范例: CentOS 7 安装Mariadb

参考网站信息,配置yum源
https://mariadb.org/download/#mariadb-repositories

在这里插入图片描述

[root@centos7 ~]#vim /etc/yum.repos.d/mariadb.repo
# MariaDB 10.5 CentOS repository list - created 2021-01-28 09:39 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.5/centos7-amd64
gpgkey=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

[root@centos7 ~]#yum install -y MariaDB-server.x86_64 
[root@centos7 ~]#systemctl enable --now mariadb.service

CentOS 6:

  • mysql-server:5.1 服务器包
  • mysql 客户端工具包

范例: Ubuntu 安装 MySQL

[root@ubuntu1804 ~]#apt install mysql-server
[root@ubuntu1804 ~]#systemctl status mysql.service

2.3 初始化脚本提高安全性

运行脚本:mysql_secure_installation

设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库

范例: 针对MySQL5.6前版本进行安全加固

1、安装mysql5.6
[root@localhost ~]# yum install -y mysql-community-server.x86_64
2、启动服务
[root@localhost ~]# systemctl enable --now mysqld
3、运行脚本
[root@localhost ~]# mysql_secure_installation



NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y 		#是否设置root密码?
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y		#是否删除匿名账户?
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y		不允许根用户远程登录?
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y		#删除测试数据库并访问它
 - Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 ... Failed!  Not critical, keep moving...
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y			#立即重新加载权限表
 ... Success!




All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


Cleaning up...
-----------

问:为什么要对MySQL旧版本进行加固?
[root@localhost ~]# mysql
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)

答:不需输入密码,且匿名账户可以登录
[root@localhost ~]# mysql -uxxx
Welcome to the MySQL monitor. 

2.3 MySQL 组成

2.3.1 客户端程序

  • mysql: 交互式或非交互式的CLI工具
  • mysqldump:备份工具,基于mysql协议向mysqld发起查询请求,并将查得的所有数据转换成
  • insert等写操作语句保存文本文件中
  • mysqladmin:基于mysql协议管理mysqld
  • mysqlimport:数据导入工具

MyISAM存储引擎的管理工具:

  • myisamchk:检查MyISAM库
  • myisampack:打包MyISAM表,只读

2.3.2 服务器端程序

  • mysqld_safe
  • mysqld
  • mysqld_multi 多实例 ,示例:mysqld_multi --example

2.3.3 用户账号

mysql用户账号由两部分组成:

USERNAME'@'HOST'

说明:
HOST限制此用户可通过哪些远程主机连接mysql服务器

支持使用通配符:

% 匹配任意长度的任意字符,相当于shell中*, 示例: 172.16.0.0/255.255.0.0 或 172.16.%.%

_ 匹配任意单个字符,相当于shell中? 

2.3.4 mysql 客户端命令

2.3.4.1 mysql 运行命令类型

  • 客户端命令:本地执行,每个命令都完整形式和简写格式
mysql> \h, help
mysql> \u,use
mysql> \s,status
mysql> \!,system
  • 服务端命令:通过mysql协议发往服务器执行并取回结果,命令末尾都必须使用命令结束符号,默认为分号
#示例:
mysql>SELECT VERSION();

2.3.4.2 mysql 使用模式

  • 交互模式
  • 脚本模式:
 mysql -uUSERNAME -pPASSWORD < /path/somefile.sql
 cat /path/somefile.sql | mysql -uUSERNAME -pPASSWORD
 mysql>source   /path/from/somefile.sql

2.3.4.3 mysql命令使用格式

mysql [OPTIONS] [database]

mysql客户端常用选项:

-A, --no-auto-rehash 禁止补全
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e   “SQL“ 执行SQL命令
-V, --version 显示版本
-v  --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置

登录系统:

#默认空密码登录
mysql  -uroot  -p

运行mysql命令:

mysql>use mysql
mysql>select user(); #查看当前用户
mysql>SELECT User,Host,Password FROM user;

范例:mysql的配置文件,修改提示符

#查看mysql版本
[root@centos8 ~]#mysql -V
mysql Ver 15.1 Distrib 10.3.11-MariaDB, for Linux (x86_64) using readline 5.1
#临时修改mysql提示符
[root@centos8 ~]#mysql -uroot -pcentos --prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"
#临时修改mysql提示符
[root@centos8 ~]#export MYSQL_PS1="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"  
#持久修改mysql提示符
[root@centos8 ~]#vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"                                        
         
[root@centos8 ~]#mysql --print-defaults -v
mysql would have been started with the following arguments:
--prompt=\r:\m:\s(\u@\h) [\d]>\_ -v
[root@centos8 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10:29:30(root@localhost) [(none)]> use mysql
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
10:29:34(root@localhost) [mysql]> exit

范例:配置所有MySQL 客户端的自动登录

[root@centos8 ~]#vim /etc/my.cnf
[client]			
user=root
password=Magedu0!                                                                              
                       
[mysql]
prompt=(\\u@\\h) [\\d]>\\_

2.3.4.4 mysqladmin命令

mysqladmin 命令格式

mysqladmin [OPTIONS] command command....

范例:

#查看mysql服务是否正常,如果正常提示mysqld is alive
mysqladmin -uroot -p'password'   ping
#关闭mysql服务,但mysqladmin命令无法开启
mysqladmin -uroot -p'password' shutdown
#创建数据库testdb
mysqladmin -uroot -p'password'   create testdb
#删除数据库testdb
mysqladmin -uroot -p'password'   drop testdb
#修改root密码
mysqladmin -uroot -p'old-password' password 'magedu'
#日志滚动,生成新文件/var/lib/mysql/mariadb-bin.00000N
mysqladmin -uroot -p'password' flush-logs

2.3.5 服务器端配置

2.3.5.1 服务器端配置文件

服务器端(mysqld):工作特性有多种配置方式
1、命令行选项:

2、配置文件:类ini格式,集中式的配置,能够为mysql的各应用程序提供配置信息

服务器端配置文件:

/etc/my.cnf #Global选项

/etc/mysql/my.cnf #Global选项

~/.my.cnf #User-specific 选项

配置文件格式:
[mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqldump]
[server]
[client]

格式:

parameter = value

说明:

_和- 相同
1,ON,TRUE意义相同, 0,OFF,FALSE意义相同,无区分大小写

2.3.5.2 socket 连接说明

服务器监听的两种 socket 地址:

  • ip socket: 监听在tcp的3306端口,支持远程通信 ,侦听3306/tcp端口可以在绑定有一个或全部接
    口IP上
  • unix sock: 监听在sock文件上,仅支持本机通信。如:/var/lib/mysql/mysql.sock)
    说明:host为localhost 时自动使用unix sock。

2.3.5.3 关闭mysqld网络连接

只侦听本地客户端, 所有客户端和服务器的交互都通过一个socket文件实现,socket的配置存放
在/var/lib/mysql/mysql.sock) 可在/etc/my.cnf修改。
范例:

[root@centos7 ~]#vim /etc/my.cnf
[mysqld]
skip-networking=1		#忽略网络功能

2.5 通用二进制格式安装 MySQL

2.5.1 实战案例:通用二进制格式安装 MySQL 5.6

2.5.1.1 准备用户

groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 -d /data/mysql mysql

2.5.1.2 准备数据目录,建议使用逻辑卷

#可选做,后面的脚本mysql_install_db可自动生成此目录
mkdir /data/mysql
chown mysql:mysql /data/mysql

2.5.1.3 准备二进制程序

tar xf mysql-VERSION-linux-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -sv mysql-VERSION mysql
chown -R root:root /usr/local/mysql/

2.5.1.4 准备配置文件

cd /usr/local/mysql
cp -b support-files/my-default.cnf   /etc/my.cnf
vim /etc/my.cnf
#mysql语句块中添加以下三个选项
[mysqld]
datadir = /data/mysql
innodb_file_per_table = on #在mariadb5.5以上版的是默认值,可不加
skip_name_resolve = on    #禁止主机名解析,建议使用

2.5.1.5 创建数据库文件

yum -y install perl perl-devel
yum -y install libaio* 

cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
[root@centos8 mysql]#ls /data/mysql/ -l
total 110604
-rw-rw---- 1 mysql mysql 12582912 Jun  1 16:44 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Jun  1 16:44 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jun  1 16:44 ib_logfile1
drwx------ 2 mysql mysql     4096 Jun  1 16:44 mysql
drwx------ 2 mysql mysql     4096 Jun  1 16:44 performance_schema
drwx------ 2 mysql mysql     4096 Jun  1 16:44 test

2.5.1.6 准备服务脚本,并启动服务

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
#如果有对应的service 文件可以执行下面
cp /usr/local/mysql/support-files/systemd/mariadb.service
/usr/lib/systemd/system/
systemctl daemon-reload
systemctl enable --now mariadb

2.5.1.7 PATH路径

echo 'PATH=/user/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

2.5.1.8 安全初始化

/user/local/mysql/bin/mysql_secure_installation

2.5.2 实战案例:一键安装mysql-5.6二进制包的脚本

2.5.2.1 离线安装mysql-5.6二进制包的脚本

[root@centos8 ~]#vim install_mysql5.6.sh
#!/bin/bash
DIR=`pwd`
NAME="mysql-5.6.47-linux-glibc2.12-x86_64.tar.gz"
FULL_NAME=${DIR}/${NAME}
DATA_DIR="/data/mysql"
yum install -y libaio perl-Data-Dumper  
if [ -f ${FULL_NAME} ];then
    echo "安装文件存在"
else
    echo "安装文件不存在"
        exit 3
fi
if [ -h /usr/local/mysql ];then
    echo "Mysql 已经安装"
    exit 3
else
   tar xvf ${FULL_NAME}   -C /usr/local/src
    ln -sv /usr/local/src/mysql-5.6.47-linux-glibc2.12-x86_64 /usr/local/mysql
    if id mysql;then
        echo "mysql 用户已经存在,跳过创建用户过程"
    else
       useradd  -r   -s /sbin/nologin mysql
     fi
    
    if id mysql;then
   chown  -R mysql.mysql /usr/local/mysql/*
        if [ ! -d /data/mysql ];then
            mkdir -pv /data/mysql && chown  -R mysql.mysql /data   -R
           /usr/local/mysql/scripts/mysql_install_db  --user=mysql --
datadir=/data/mysql  --basedir=/usr/local/mysql/
 cp /usr/local/src/mysql-5.6.47-linux-glibc2.12-x86_64/supportfiles/mysql.server /etc/init.d/mysqld
 chmod a+x /etc/init.d/mysqld
 cp ${DIR}/my.cnf   /etc/my.cnf
 ln -sv /usr/local/mysql/bin/mysql /usr/bin/mysql
 /etc/init.d/mysqld start
 chkconfig --add mysqld
 else
            echo "MySQL数据目录已经存在,"
 exit 3
 fi
    fi
fi
[root@centos8 ~]#cat /etc/my.cnf
[mysqld]
socket=/data/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
[client]
port=3306
socket=/data/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/tmp/mysql.sock
[root@centos8 ~]#ls
install_mysql5.6.sh my.cnf mysql-5.6.47-linux-glibc2.12-x86_64.tar.gz

2.5.2.2 在线安装mysql-5.6二进制包的脚本

#!/bin/bash
. /etc/init.d/functions
DIR=`pwd`
MYSQL_VERSION=5.6.51
NAME="mysql-${MYSQL_VERSION}-linux-glibc2.12-x86_64.tar.gz"
FULL_NAME=${DIR}/${NAME}
URL=http://mirrors.163.com/mysql/Downloads/MySQL-5.6
DATA_DIR="/data/mysql"
rpm -q wget || yum -y -q install wget
wget $URL/$NAME || { action "下载失败,异常退出" false;exit 10; }
yum install -y -q libaio perl-Data-Dumper autoconf
if [ -f ${FULL_NAME} ];then
   action "安装文件存在"
else
   action "安装文件不存在" false
    exit 3
fi
if [ -e /usr/local/mysql ];then
   action "Mysql 已经安装" false
    exit 3
else
   tar xf ${FULL_NAME} -C /usr/local/src
    ln -sv /usr/local/src/mysql-${MYSQL_VERSION}-linux-glibc2.12-x86_64
/usr/local/mysql
    if id mysql;then
       action "mysql 用户已经存在,跳过创建用户过程"
    else
       useradd -r -s /sbin/nologin mysql
    fi
    if id mysql;then
        chown -R mysql.mysql /usr/local/mysql/*
        if [ ! -d /data/mysql ];then
            mkdir -pv /data/mysql && chown -R mysql.mysql /data
           /usr/local/mysql/scripts/mysql_install_db --user=mysql --
datadir=/data/mysql --basedir=/usr/local/mysql/
            cp /usr/local/src/mysql-${MYSQL_VERSION}-linux-glibc2.12-
x86_64/support-files/mysql.server /etc/init.d/mysqld
            chmod a+x /etc/init.d/mysqld
            cat > /etc/my.cnf <<-'EOF'
 [mysqld]
            socket=/data/mysql/mysql.sock
            user=mysql
 symbolic-links=0
 datadir=/data/mysql
 innodb_file_per_table=1
 [client]
 port=3306
 socket=/data/mysql/mysql.sock
 [mysqld_safe]
 log-error=/var/log/mysqld.log
 pid-file=/tmp/mysql.sock
 EOF
            ln -sv /usr/local/mysql/bin/mysql /usr/bin/mysql
           /etc/init.d/mysqld start
           chkconfig --add mysqld
        else
           action "MySQL数据目录已经存在" false
            exit 3
        fi
    fi
fi

2.5.3 实战案例:通用二进制安装安装MySQL 5.7 和 MySQL8.0

2.5.3.1 安装相关包

yum  -y install libaio numactl-libs

2.5.3.2 用户和组

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

2.5.3.3 准备程序文件

wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.31-linuxglibc2.12-x86_64.tar.gz
tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local
cd /usr/local/
ln -s mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
chown -R root.root /usr/local/mysql/

2.5.3.4 准备环境变量

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

2.5.3.5 准备配置文件

cp /etc/my.cnf{,.bak}
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock        
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

2.5.3.6 初始化数据库文件并提取root密码

2.5.3.6.1 方式1: 生成随机密码
mysqld --initialize --user=mysql --datadir=/data/mysql
...省略...
2019-07-04T13:03:54.258140Z 1 [Note] A temporary password is generated for
root@localhost: LufavlMka6,!  #注意生成root的初始密码
grep password /data/mysql/mysql.log
2019-12-26T13:31:30.458826Z 1 [Note] A temporary password is generated for
root@localhost: LufavlMka6,!
awk '/temporary password/{print $NF}' /data/mysql/mysql.log
LufavlMka6,!
2.5.3.6.2 方式2: 生成 root 空密码
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql 

2.5.3.7 准备服务脚本和启动

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

2.5.3.8 修改口令

mysqladmin -uroot -p'LufavlMka6,!'   password magedu

2.7.9 测试登录

#修改前面生成的随机密码为指定密码
mysqladmin -uroot -p'LufavlMka6,!'   password magedu
#修改前面生成的空密码为指定密码
mysqladmin -uroot   password magedu

2.5.3.9 测试登录

mysql -uroot -pmagedu

2.5.4 实战案例:一键安装MySQL5.7 和 MySQL8.0 二进制包的脚本

2.5.4.1 离线安装脚本

#!/bin/bash
. /etc/init.d/functions
SRC_DIR=`pwd`
#MYSQL='mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz'
MYSQL='mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz'
COLOR='echo -e \E[01;31m'
END='\E[0m'
MYSQL_ROOT_PASSWORD=magedu
check (){
if [ $UID -ne 0 ]; then
 action "当前用户不是root,安装失败" false
  exit 1
fi
cd  $SRC_DIR
if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
 $COLOR"请将相关软件放在${SRC_DIR}目录下"$END
        exit
elif [ -e /usr/local/mysql ];then
       action "数据库已存在,安装失败" false
        exit
else
 return
fi
}
install_mysql(){
    $COLOR"开始安装MySQL数据库..."$END
 yum  -y -q install libaio numactl-libs &> /dev/null
    cd $SRC_DIR
   tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s /usr/local/$MYSQL_DIR /usr/local/mysql
    chown -R root.root /usr/local/mysql/
   id mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ; action "创建
mysql用户"; }
        
    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
   . /etc/profile.d/mysql.sh
    ln -s /usr/local/mysql/bin/* /usr/bin/
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                  
                                               
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
   [ -d /data ] || mkdir /data
   mysqld --initialize --user=mysql --datadir=/data/mysql
    cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
   chkconfig --add mysqld
   chkconfig mysqld on
    service mysqld start
   [ $? -ne 0 ] && { $COLOR"数据库启动失败,退出!"$END;exit; }
    MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}'
/data/mysql/mysql.log`
   mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD
&>/dev/null
   action "数据库安装完成"
}
check
install_mysql

2.5.4.2 在线安装脚本

[root@centos7 ~]#ccat install_online_mysql5.7or8.0_for_centos.sh 
#!/bin/bash
. /etc/init.d/functions
SRC_DIR=`pwd`
MYSQL='mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz'
URL=http://mirrors.163.com/mysql/Downloads/MySQL-5.7
#MYSQL='mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz'
#URL=http://mirrors.163.com/mysql/Downloads/MySQL-8.0
COLOR='echo -e \E[01;31m'
END='\E[0m'
MYSQL_ROOT_PASSWORD=magedu
check (){
if [ $UID -ne 0 ]; then
 action "当前用户不是root,安装失败" false
  exit 1
fi
cd  $SRC_DIR
rpm -q wget || yum -y -q install wget
wget  $URL/$MYSQL
if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
 $COLOR"请将相关软件放在${SRC_DIR}目录下"$END
        exit
elif [ -e /usr/local/mysql ];then
       action "数据库已存在,安装失败" false
        exit
else
 return
fi
}
install_mysql(){
    $COLOR"开始安装MySQL数据库..."$END
 yum  -y -q install libaio numactl-libs   libaio &> /dev/null
    cd $SRC_DIR
   tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s /usr/local/$MYSQL_DIR /usr/local/mysql
    chown -R root.root /usr/local/mysql/
   id mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ; action "创建
mysql用户"; }
        
    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
   . /etc/profile.d/mysql.sh
 ln -s /usr/local/mysql/bin/* /usr/bin/
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=`hostname -I|cut -d. -f4`
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                  
                                               
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
   mysqld --initialize --user=mysql --datadir=/data/mysql
    cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
   chkconfig --add mysqld
   chkconfig mysqld on
    service mysqld start
     [ $? -ne 0 ] && { $COLOR"数据库启动失败,退出!"$END;exit; }
    MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}'
/data/mysql/mysql.log`
   mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD
&>/dev/null
   action "数据库安装完成"
}
check
install_mysql

2.6 源码编译安装 MySQL 5.6

2.6.1 安装相关依赖包

[root@centos7 ~]#cd /usr/local/src
[root@centos7 src]#yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel   ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel perl-Data-Dumper

2.6.2 做准备用户和数据目录

useradd -r -s /sbin/nologin -d /data/mysql mysql	#创建一个mysql的用户,数据库文件存放在/data/mysql下

2.6.3 准备数据库目录

mkdir /data/mysql
chown -R mysql.mysql /data/mysql	#把所属组设置成mysql

2.6.4 源码编译安装

编译安装说明
利用cmake编译,而利用传统方法,cmake的重要特性之一是其独立于源码(out-of-source)的编译功能,
即编译工作可以在另一个指定的目录中而非源码目录中进行,这可以保证源码目录不受任何一次编译的
影响,因此在同一个源码树上可以进行多次不同的编译,如针对于不同平台编译
编译选项:https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html

2.6.4.1 下载并解压缩源码包

[root@centos7 ~]#ls
anaconda-ks.cfg  mysql-5.6.51.tar.gz
[root@centos7 ~]#tar xvf mysql-5.6.51.tar.gz -C /usr/local/src

2.6.4.2 源码编译安装MySQL

[root@centos7 ~]#cd /usr/local/src
[root@centos7 ~]#cd mysql-5.6.51/
cmake . \
-DCMAKE_INSTALL_PREFIX=/apps/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make && make install 

提示:如果出错,执行rm -f CMakeCache.txt

2.6.5 准备环境变量

echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
.     /etc/profile.d/mysql.sh
或者用软链接实现
ln -s /usr/local/mysql/bin/mysql /usr/local/bin

2.6.6 生成数据库文件

cd   /apps/mysql/		#进入该目录下
scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql

2.6.7 准备配置文件

cp -b /apps/mysql/support-files/my-default.cnf /etc/my.cnf

#针对旧版本或mariadb-10.2.18.tar.gz
cp /apps/mysql/support-files/my-huge.cnf   /etc/my.cnf

2.6.8 准备启动脚本,并启动服务

cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

2.6.9 安全初始化

mysql_secure_installation

2.7 基于 dockcer 容器创建MySQL

范例:

[root@ubuntu1804 ~]#docker run --name mysql -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.30

2.8 MySQL多实例

2.8.1 多实例介绍和方案

2.8.1.1 多实例介绍

  • 什么是数据库多实例
    多实例类似微信双开,端口号类比微信账号,数据库类比聊天窗口,表类比聊天记录
    MySQL多实例就是在一台服务器上同时开启多个不同的服务端口(如:3306、3307等),同时运
    行多个MySQL服务进程,这些服务进程通过不同的Socket监听不同的服务端口来提供服务。
    多实例可能是MySQL的不同版本,也可能是MySQL的同一版本实现
  • 多实例的好处
    可有效利用服务器资源。当单个服务器资源有剩余时,可以充分利用剩余资源提供更多的服务,且
    可以实现资源的逻辑隔离节约服务器资源。例如公司服务器资源紧张,但是数据库又需要各自尽量
    独立的提供服务,并且还需要到主从复制等技术,多实例就是最佳选择
  • 多实例弊端
    存在资源互相抢占的问题。比如:当某个数据库实例并发很高或者SQL查询慢时,整个实例会消耗
    大量的CPU、磁盘I/O等资源,导致服务器上面其他的数据库实例在提供服务的质量也会下降,所以
    具体的需求要根据自己的实际情况而定。

2.8.1.2 MySQL多实例常见的配置方案

  • 单一的配置文件、单一启动程序多实例部署方式
    MySQL官方文档提到的单一配置文件、单一启动程序多实例部署方式
    耦合度太高,一个配置文件不好管理。不是很推荐。
  • 多配置文件、多启动程序部署方式
    多配置文件、多启动程序部署方式是针对每个实例都有独立的配置文件和目录,管理灵活,此方案耦合度较低
    工作开发和运维的统一原则:降低耦合度。所以建议的此方式。

2.8.2 实战案例 1: CentOS 8 实现 MySQL 8.0 二进制安装的多实例

本案例适用于以版本

mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz
mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

2.8.2.1 实战目标

CentOS8 二进制安装MySQL8.0,并实现三个实例

2.8.2.2 环境说明

一台系统CentOS 8.X主机

2.8.2.3 前提准备

关闭SElinux
关闭防火墙
时间同步

2.8.2.4 实现步骤

2.8.2.4.1 下载MySQL二进制文件并解压缩
[root@centos8 ~]#wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86_64.tar
#解压
[root@centos8 ~]#tar xf mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
#创建软链接
[root@centos8 ~]#ln -s /usr/local/mysql-8.0.23-linux-glibc2.12-x86_64/ /usr/local/mysql

2.8.2.4.2 创建用户和组配置权限
[root@centos8 ~]#useradd -r -s /sbin/nologin mysql
#更改所有者,所属组
[root@centos8 ~]#chown -R mysql.mysql /usr/local/mysql/
2.8.2.4.3 配置环境变量
[root@centos8 ~]#echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos8 ~]#. /etc/profile.d/mysql.sh
#或用软链接实现
[root@centos8 ~]#ln -s /usr/local/mysql/bin/mysql /usr/local/bin
2.8.2.4.4 创建各实例数据存放的目录
[root@centos8 ~]#cd /usr/local
[root@centos8 local]#mkdir -p /mysql/{3306,3307,3308}
[root@centos8 local]#chown -R mysql.mysql /mysql/
[root@centos8 local]#ll /mysql/
total 0
drwxr-xr-x 2 mysql mysql 6 Jan 28 13:09 3306
drwxr-xr-x 2 mysql mysql 6 Jan 28 13:09 3307
drwxr-xr-x 2 mysql mysql 6 Jan 28 13:09 3308
[root@centos8 local]#tree /mysql
/mysql
├── 3306
├── 3307
└── 3308
3 directories, 0 files
2.8.2.4.5 初始化各实例数据库文件
#先安装包,否则会出错
[root@centos8 local]#yum -yq install libaio
#针对每个实例初始化,生成空密码
[root@centos8 local]#mysqld --initialize-insecure --user=mysql --datadir=/mysql/3306
[root@centos8 local]#mysqld --initialize-insecure --user=mysql --datadir=/mysql/3307
[root@centos8 local]#mysqld --initialize-insecure --user=mysql --datadir=/mysql/3308

[root@centos8 local]#tree /mysql/ -d
/mysql/
├── 3306
│   ├── #innodb_temp
│   ├── mysql
│   ├── performance_schema
│   └── sys
├── 3307
│   ├── #innodb_temp
│   ├── mysql
│   ├── performance_schema
│   └── sys
└── 3308
    ├── #innodb_temp
    ├── mysql
    ├── performance_schema
    └── sys

15 directories

2.8.2.4.6 准备配置文件/etc/my.cnf
[root@centos8 ~]#file `which mysqld_multi`
/usr/local/mysql/bin/mysqld_multi: Perl script text executable
#每个实例中对应mysqlx_port行MySQL8.0版本需要分别指定,MySQL5.7无需指定
[root@centos8 ~]#cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /mysql/3306
port = 3306
mysqlx_port = 33060
socket = /mysql/3306/mysql3306.sock
pid-file = /mysql/3306/mysql3306.pid
log-error=/mysql/3306/mysql3306.log

[mysqld3307]
datadir = /mysql/3307
port = 3307
mysqlx_port = 33070          
socket = /mysql/3307/mysql3307.sock
pid-file = /mysql/3307/mysql3307.pid
log-error=/mysql/3307/mysql3307.log

[mysqld3308]
datadir = /mysql/3308
port = 3308
mysqlx_port = 33080
socket = /mysql/3308/mysql3308.sock
pid-file = /mysql/3308/mysql3308.pid
log-error=/mysql/3308/mysql3308.log
2.8.2.4.7 启动多实例
#说明:用 mysqld_multi start N 启动多个实例,
#注意数字N和my.cnf中的[mysqldN]对应,示例:1-3就是启动[mysqld1]、[mysqld2]、[mysqld3]配置段的MySQL实例

#启动三个MySQL实例
[root@centos8 ~]#mysqld_multi start 3306        
[root@centos8 ~]#mysqld_multi start 3307
[root@centos8 ~]#mysqld_multi start 3308

# 或者用下面命令批量启动多个实例
[root@centos8 ~]#mysqld_multi start 3306-3308

执行mysqld_multi报错

-bash: /usr/local/mysql/bin/mysqld_multi: /usr/bin/perl: bad interpreter: No such file or directory

解决:yum -y install perl perl-devel
#登录实例查看状态
[root@centos8 local]#mysql -uroot -S /mysql/3306/mysql3306.sock 

#三种方式创建数据库
1.[root@centos8 local]#mysql -uroot -S /mysql/3306/mysql3306.sock 
mysql> create database db3306;

2.[root@centos8 local]#mysql -uroot -h127.0.0.1 -P3307
mysql> create database db3307;

3.[root@centos8 local]#mysqladmin -uroot -S /mysql/3308/mysql3308.sock create db3308
2.8.2.4.8 关闭多实例
#方法1
[root@centos8 ~]#mysqld_multi stop 3306-3308

#方法2
[root@centos8 ~]#for i in {3306..3308};do mysqladmin -uroot -S
/mysql/$i/mysql$i.sock shutdown ;done

2.8.2.4.9 安全加固
#批量修改多个实例root密码
[root@centos8 ~]#for i in {3306..3308};do mysqladmin -S /mysql/$i/mysql$i.sock password 123456;done

#批量验证密码连接
[root@centos8 ~]#for i in {3306..3308};do mysqladmin -uroot -p123456 -S /mysql/$i/mysql$i.sock ping ;done
2.8.2.4.10 配置开机启动多实例


2.8.3 实战案例 2:CentOS 8 实现mariadb的yum安装的多实例

2.8.3.1 实战目的

CentOS 8 yum安装mariadb-10.3.17并实现三个实例

2.8.3.2 环境要求

一台系统CentOS 8.X主机

2.8.3.4 实现步骤

2.8.3.4.1 安装mariadb
[root@centos8 ~]# yum -y install mariadb-server
2.8.3.4.2 准备三个实例的目录
[root@centos8 ~]# mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
[root@centos8 ~]# chown -R mysql.mysql /mysql
[root@centos8 ~]# tree -d /mysql/
/mysql/
├── 3306
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
├── 3307
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
└── 3308
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket

21 directories
2.8.3.4.3 生成数据库文件
[root@centos8 ~]# mysql_install_db --user=mysql --datadir=/mysql/3306/data
[root@centos8 ~]# mysql_install_db --user=mysql --datadir=/mysql/3307/data
[root@centos8 ~]# mysql_install_db --user=mysql --datadir=/mysql/3308/data
2.8.3.4.4 准备配置文件
[root@centos8 ~]# vim /mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mysql.log
pid-file=/mysql/3306/pid/mysql.pid

#重复上面步骤设置3307,3308
[root@centos8 ~]#sed 's/3306/3307/' /mysql/3306/etc/my.cnf >
/mysql/3307/etc/my.cnf
[root@centos8 ~]#sed 's/3306/3308/' /mysql/3306/etc/my.cnf >
/mysql/3308/etc/my.cnf
2.8.3.4.5 准备启动脚本
[root@centos8 ~]#vim /mysql/3306/bin/mysqld
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="magedu"
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
     printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
    else
     printf "MySQL is running...\n"
      exit
    fi
}
function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}
function_restart_mysql()
{
   printf "Restarting MySQL...\n"
   function_stop_mysql
    sleep 2
   function_start_mysql
   }
case $1 in
start)
   function_start_mysql
;;
stop)
   function_stop_mysql
;;
restart)
   function_restart_mysql
;;
*)
   printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
[root@centos8 ~]#chmod +x /mysql/3306/bin/mysqld
#重复上述过程,分别建立3307,3308的启动脚本
2.8.3.4.6 启动服务
[root@centos8 ~]#/mysql/3306/bin/mysqld start
[root@centos8 ~]#/mysql/3307/bin/mysqld start
[root@centos8 ~]#/mysql/3308/bin/mysqld start
[root@centos8 ~]#ss -ntl
State       Recv-Q       Send-Q         Local Address:Port       Peer
Address:Port    
LISTEN       0             128                  0.0.0.0:22            
0.0.0.0:*        
LISTEN       0             128                     [::]:22                
[::]:*        
LISTEN       0             80                         *:3306                
*:*        
LISTEN       0             80                         *:3307                
*:*        
LISTEN       0             80                         *:3308                
*:* 
2.8.3.4.7 登录实例
[root@centos8 ~]#/mysql/3308/bin/mysqld start
#两种连接方法
[root@centos8 ~]#mysql -h127.0.0.1 -P3308
[root@centos8 ~]#mysql -uroot -S /mysqldb/3306/socket/mysql.sock
#确认连接的端口
MariaDB [(none)]> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port         | 3308 |
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]>

#关闭数据库,需要手动输入root的密码
[root@centos8 ~]#/mysql/3308/bin/mysqld stop
Stoping MySQL...
Enter password:
[root@centos8 ~]#/mysql/3308/bin/mysqld start
Starting MySQL...
2.8.3.4.8 修改root密码
#加上root的口令
[root@centos8 ~]#mysqladmin -uroot -S /mysql/3306/socket/mysql.sock password
'magedu'
[root@centos8 ~]#mysqladmin -uroot -S /mysql/3307/socket/mysql.sock password
'magedu'
[root@centos8 ~]#mysqladmin -uroot -S /mysql/3308/socket/mysql.sock password
'magedu'
#或者登录mysql,执行下面也可以
Mariadb>update mysql.user set password=password("centos") where user='root';
Mariadb>flush privileges;
#重复步骤,分别修改别外两个实例3307,3308对应root口令

2.8.3.4.9 测试连接
[root@centos8 ~]#mysql -uroot -p -S /mysql/3306/socket/mysql.sock #提示输入口令才
能登录
2.8.3.4.10 开机启动
[root@centos8 ~]#vi /etc/rc.d/rc.local
#在最后一行加下面内容
for i in {3306..3308};do /mysql/$i/bin/mysqld start;done
[root@centos8 ~]#chmod +x /etc/rc.d/rc.local

3 SQL 语言

3.1 关系型数据库的常见组件

  • 数据库:database
  • 表:table,行:row 列:column
  • 索引:index
  • 视图:view
  • 存储过程:procedure
  • 存储函数:function
  • 触发器:trigger
  • 事件调度器:event scheduler,任务计划
  • 用户:user
  • 权限:privilege

3.2 SQL语言的兴起与语法标准

3.2.1 SQL 语言规范

在数据库系统中,SQL 语句不区分大小写,建议用大写
SQL语句可单行或多行书写,默认以 " ; " 结尾
关键词不能跨多行或简写
用空格和TAB 缩进来提高语句的可读性
子句通常位于独立行,便于编辑,提高可读性
注释:

  • SQL标准:
#单行注释,注意有空格
-- 注释内容  
#多行注释
/*注释内容
注释内容
注释内容*/
  • MySQL注释:
# 注释内容

3.2.2 数据库对象和命名

数据库的组件(对象):
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则:

  • 必须以字母开头,后续可以包括字母,数字和三个特殊字符(# _ $)
  • 不要使用MySQL的保留字

3.2.3 SQL语句分类

  • DDL: Data Defination Language 数据定义语言
    CREATE,DROP,ALTER
  • DML: Data Manipulation Language 数据操纵语言
    INSERT,DELETE,UPDATE
  • DQL:Data Query Language 数据查询语言
    SELECT
  • DCL:Data Control Language 数据控制语言
    GRANT,REVOKE
  • 软件开发:CRUD

3.2.4 SQL语句构成

关健字Keyword组成子句clause,多条clause组成语句

示例:

SELECT *                 #SELECT子句
FROM products             #FROM子句
WHERE price>666         #WHERE子句

说明:一组SQL语句由三个子句构成,SELECT,FROM和WHERE是关键字

获取SQL 命令使用帮助:

官方帮助:https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html

mysql> HELP KEYWORD

3.2.5 字符集和排序

早期MySQL版本默认为latin1,从MySQL8.0开始默认字符集已经为 utf8mb4

查看支持所有字符集:

SHOW CHARACTER SET;

查看支持所有排序规则:

SHOW COLLATION;

查看当前使用的排序规则

SHOW VARIABLES LIKE 'collation%';

设置服务器默认的字符集,

#多实例位置
[root@centos8 ~]#vim /mysql/3306/etc/my.cnf 

vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

设置mysql客户端默认的字符集

#多实例位置
[root@centos8 ~]#vim /etc/my.cnf.d/client.cnf 

vim /etc/my.cnf
#针对mysql客户端
[mysql]
default-character-set=utf8mb4
#针对所有MySQL客户端
[client]
default-character-set=utf8mb4

查看当前字符集的使用情况

MariaDB [(none)]> show variables like 'character%';

3.3 管理数据库

3.3.1 创建数据库

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name'; 

范例:

创建数据库

MariaDB [(none)]> create database db3306;

查看系统默认的字符集

MariaDB [(none)]> show variables like 'character%';

查看字符集及对应的排序规则

MariaDB [(none)]> SHOW COLLAATE;

人为指定字符集,不指定排序规则

MariaDB [(none)]> create database db2 character set utf8 ;

[root@centos8 db3306]#cat ../db2/db.opt 
default-character-set=utf8
default-collation=utf8_general_ci

人为指定字符集,指定排序规则

MariaDB [(none)]> create database db3 character set utf8 COLLATE utf8_bin ;
MariaDB [(none)]> show create database db3;
+----------+-------------------------------------------------------------------------------+
| Database | Create Database                                                               |
+----------+-------------------------------------------------------------------------------+
| db3      | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+-------------------------------------------------------------------------------+
1 row in set (0.000 sec)

3.3.2 修改数据库

ALTER DATABASE DB_NAME character set utf8;

范例:

MariaDB [(none)]> alter database db3 character set utf8mb4;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show create database db3;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| db3      | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.000 sec)

3.3.3 删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

3.3.4 查看数据库列表

SHOW DATABASES; 

3.4 数据类型

数据类型:

  • 数据长什么样
  • 数据需要多少空间来存放

数据类型

  • 系统内置数据类型
  • 用户定义数据类型

MySQL支持多种内置数据类型

  • 数值类型
  • 日期/时间类型
  • 字符串(字符)类型

数据类型参考链接
https://dev.mysql.com/doc/refman/8.0/en/data-types.html

在这里插入图片描述
选择正确的数据类型对于获得高性能至关重要,三大原则:

  1. 更小的通常更好,尽量使用可正确存储数据的最小数据类型
  2. 简单就好,简单数据类型的操作通常需要更少的CPU周期
  3. 尽量避免NULL,包含为NULL的列,对MySQL更难优化

3.4.1 整数型

tinyint(m) 1个字节 范围(-128~127)

smallint(m) 2个字节 范围(-32768~32767)

mediumint(m) 3个字节 范围(-8388608~8388607)

int(m) 4个字节 范围(-2147483648~2147483647)

bigint(m) 8个字节 范围(±9.22*10的18次方)

上述数据类型,如果加修饰符unsigned后,则最大值翻倍

如:tinyint unsigned的取值范围为(0~255)

int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,Int(1)和Int(20)是相同的BOOL,BOOLEAN:布尔型,是TINYINT(1)的同义词。zero值被视为假,非zero值视为真

3.4.2 浮点型(float和double),近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位

double(m,d) 双精度浮点型16位精度(8字节) m总个数,d小数位

设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位

3.4.3 定点数

在数据库中存放的是精确值,存为十进制

decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。

例如: decimal(18,9)小数点两边将各存储9个数字,一共使用9个字节:其中,小数点前的9个数字用4个字节,小数点后的9个数字用4个字节,小数点本身占1个字节

浮点类型在存储同样范围的值时,通常比decimal使用更少的空间。float使用4个字节存储。double占
用8个字节

因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用decimal,例如存储财务数据。但在数据量比较大的时候,可以考虑使用bigint代替decimal

3.4.4 字符串(char,varchar,text)char(n) 固定长度,最多255个字符,注意不是字节

varchar(n) 可变长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
longtext 可变长度,最多2的32次方-1个字符
BINARY(M) 固定长度,可存二进制或字符,长度为0-M字节
VARBINARY(M) 可变长度,可存二进制或字符,允许长度为0-M字节
内建类型:ENUM枚举, SET集合

char和varchar的比较:
参考:https://dev.mysql.com/doc/refman/8.0/en/char.html
在这里插入图片描述
1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉,所以char类型存储的字符串末尾不能有空格,varchar不限于此

2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n< n>255),所以varchar(4),存入3个字符将占用4个字节

3.char类型的字符串检索速度要比varchar类型的快

varchar 和 text:
1.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n< n>255),text是实际字符数+2个字节。

2.text类型不能有默认值

3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text

3.4.5 二进制数据BLOB

BLOB和text存储方式不同,TEXT以文本方式存储,英文存储区分大小写,而Blob以二进制方式存储,
不分大小写

BLOB存储的数据只能整体读出

TEXT可以指定字符集,BLOB不用指定字符集

3.4.6 日期时间类型date 日期 ‘2008-12-2’

time 时间 ‘12:25:36’

datetime 日期时间 ‘2008-12-2 22:06:44’

timestamp 自动存储记录修改时间

YEAR(2), YEAR(4):年份

timestamp 此字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类型的字段可以存放这条记录最后被修改的时间

3.4.7 修饰符

适用所有类型的修饰符:

NULL 数据列可包含NULL值,默认值

NOT NULL 数据列不允许包含NULL值,相当于网站注册表中的 * 为必填选项

DEFAULT 默认值

PRIMARY KEY 主键,所有记录中此字段的值不能重复,且不能为NULL

UNIQUE KEY 唯一键,所有记录中此字段的值不能重复,但可以为NULL

CHARACTER SET name 指定一个字符集

适用数值型的修饰符:

AUTO_INCREMENT 自动递增,适用于整数类型

UNSIGNED 无符号

范例:关于AUTO_INCREMENT

MariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name           | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset   | 1     |
+--------------------------+-------+
2 rows in set (0.001 sec)
# auto_increment_offset 定义初始值
# auto_increment_increment 定义步进

范例:

MariaDB [db1]> insert t1 values(null);
Query OK, 1 row affected (0.001 sec)

MariaDB [db1]> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
+------------+
2 rows in set (0.000 sec)

MariaDB [db1]> insert t1 values(null);
ERROR 167 (22003): Out of range value for column 'id' at row 1



#上面表的数据类型无法存放所有数据,修改过数据类型实现
MariaDB [db1]> alter table t1 modify id bigint auto_increment ;
Query OK, 2 rows affected (0.023 sec)              
Records: 2 Duplicates: 0  Warnings: 0
MariaDB [db1]> desc t1;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra         |
+-------+------------+------+-----+---------+----------------+
| id   | bigint(20) | NO   | PRI | NULL   | auto_increment |
+-------+------------+------+-----+---------+----------------+
1 row in set (0.001 sec)
MariaDB [db1]> insert t1 values(null);
Query OK, 1 row affected (0.001 sec)
MariaDB [db1]> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
| 4294967296 |
+------------+
3 rows in set (0.000 sec)

3.5 DDL 语句

表:二维关系

设计表:遵循规范

定义:字段,索引

  • 字段:字段名,字段数据类型,修饰符
  • 约束,索引:应该创建在经常用作查询条件的字段上

3.5.1 创建表

创建表:

CREATE TABLE

获取帮助:

HELP CREATE TABLE

创建表的方法
(1) 直接创建

CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符,
...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
#表选项:
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

注意:

  • Storage Engine是指表类型,也即在表创建时指明其使用的存储引擎
  • 同一库中不同表可以使用不同的存储引擎
  • 同一个库中表建议要使用同一种存储引擎类型

范例:创建表

1. MariaDB [db1]> create table student (id int auto_increment primary key ,name varchar(4) not null ,age tinyint unsigned,gender ENUM('M' , 'F'));
 
2. MariaDB [db1]> create table student (
    -> id int auto_increment primary key ,
    -> name varchar(4) not null ,
    -> age tinyint unsigned ,
    -> gender ENUM('M' , 'F'))
    -> ;
Query OK, 0 rows affected (0.004 sec)

3.MariaDB [db1]> create table emp like teacher;


#查询表结构
MariaDB [db1]> desc student
    -> ;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(11)             | NO   | PRI | NULL    | auto_increment |
| name   | varchar(4)          | NO   |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)


3.5.2 表查看

查看表:

SHOW TABLES [FROM db_name]

查看表创建命令:

SHOW CREATE TABLE tbl_name

查看表结构:

DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name

查看表状态:

SHOW TABLE STATUS LIKE 'tbl_name'

查看支持的engine类型

SHOW ENGINES;

查看库中所有表状态

SHOW TABLE STATUS FROM db_name

3.5.3 修改和删除表

修改表

ALTER TABLE 'tbl_name'
#字段:
#添加字段:add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段:drop
#修改字段:
alter(默认值), change(字段名), modify(字段属性)

查看修改表帮助

Help ALTER TABLE

删除表

DROP TABLE [IF EXISTS] 'tbl_name';

修改表范例

ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
ALTER TABLE students ADD gender ENUM('m','f');
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students DROP age;
DESC students;


#新建表无主键,添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;

3.6 DML 语句

DML: INSERT, DELETE, UPDATE

3.6.1 INSERT 语句

功能:一次插入一行或多行数据

语法

Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

简化写法:

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

范例:批量导入
前提条件:字段数必须一样,字段的数据类型要匹配

MariaDB [db1]> insert emp select * from student ;
Query OK, 4 rows affected (0.001 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [db1]> select * from emp;
+--------+------+-------------+------+--------+
| emp_id | name | phone       | age  | gender |
+--------+------+-------------+------+--------+
|      1 | ming | NULL        |   20 | M      |
|      2 | hong | 13800138000 |   18 | NULL   |
|      3 | cai  | 10086       |   21 | M      |
|      4 | bai  |             |   20 | NULL   |
+--------+------+-------------+------+--------+
4 rows in set (0.000 sec)

范例:跨库调用别的表

MariaDB [db2]> create table test select * from db1.emp;

3.6.2 UPDATE 语句

语法:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
   [WHERE where_condition]
   [ORDER BY ...]
   [LIMIT row_count]

注意:一定要有限制条件,否则将修改所有行的指定字段

可利用mysql 选项避免此错误:

mysql -U | --safe-updates| --i-am-a-dummy

alias mysql='mysql -U'
[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates

3.6.3 DELETE 语句

删除表中数据,但不会自动缩减数据文件的大小。

语法:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
   [WHERE where_condition]
   [ORDER BY ...]
   [LIMIT row_count]
#可先排序再指定删除的行数

注意:一定要有限制条件,否则将清空表中的所有数据

[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates

如果想清空表,保留表结构,也可以使用下面语句,此语句会自动缩减数据文件的大小。

TRUNCATE TABLE tbl_name;

缩减表大小

OPTIMIZE TABLE tb_name

3.7 DQL 语句

3.7.1 单表操作

语法:

SELECT
 [ALL | DISTINCT | DISTINCTROW ]
 [SQL_CACHE | SQL_NO_CACHE]
 select_expr [, select_expr ...]
   [FROM table_references
   [WHERE where_condition]
   [GROUP BY {col_name | expr | position}
     [ASC | DESC], ... [WITH ROLLUP]]
   [HAVING where_condition]
   [ORDER BY {col_name | expr | position}
     [ASC | DESC], ...]
   [LIMIT {[offset,] row_count | row_count OFFSET offset}]
   [FOR UPDATE | LOCK IN SHARE MODE]

说明:

  • 字段显示可以使用别名:
    col1 AS alias1, col2 AS alias2, …
  • WHERE子句:指明过滤条件以实现"选择"的功能:放在group by之前按
    过滤条件:布尔型表达式
    算术操作符:+, -, *, /, %
    比较操作符:=,<=>(相等或都为空), <>, !=(非标准SQL), >, >=, <, <=
    BETWEEN min_num AND max_num
    IN (element1, element2, …)
    IS NULL, IS NOT NULL
    DISTINCT 去除重复行,范例:SELECT DISTINCT gender FROM students;
    LIKE: % 任意长度的任意字符 _ 任意单个字符
    RLIKE:正则表达式,索引失效,不建议使用
    REGEXP:匹配字符串可用正则表达式书写模式,同上
    逻辑操作符:NOT,AND,OR,XOR
  • GROUP BY:根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
    常见聚合函数: count(), sum(), max(), min(), avg()
    HAVING: 对分组聚合运算后的结果指定过滤条件,放在group by之后
    一旦分组 group by ,select语句后只跟分组的字段,聚合函数
  • ORDER BY: 根据指定的字段对查询结果进行排序
    升序:ASC
    降序:DESC
  • LIMIT [[offset,]row_count]:对查询的结果进行输出行数数量限制
  • 对查询结果中的数据请求施加"锁"
    FOR UPDATE: 写锁,独占或排它锁,只有一个读和写操作
    LOCK IN SHARE MODE: 读锁,共享锁,同时多个读操作

范例:字段别名

select "学员信息:", Name 姓名,StuID 学员,Gender 性别 from students;

范例:判断是否为NULL

select * from students where classid is not null;

范例:模糊查询,以shi开头的名

select * from students where name like 'shi%';

范例: 取反

select * from students where not (age > 20 or gender = 'F' ) ;

范例: 去重

select distinct age from students;

范例:分组统计

MariaDB [hellodb]> select classid,count(*) 数量 from students group by classid;
+---------+--------+
| classid | 数量   |
+---------+--------+
|    NULL |      2 |
|       1 |      4 |
|       2 |      3 |
|       3 |      4 |
|       4 |      4 |
|       5 |      1 |
|       6 |      4 |
|       7 |      3 |
+---------+--------+
8 rows in set (0.001 sec)

注意:一旦使用分组group by,在select 后面的只能采用分组的列和聚合函数,其它的列不能放在select后面,否则根据系统变量SQL_MODE的值不同而不同的结果

# 对多个字段分组统计,字段位置不同无影响
select classid,gender,count(*) from students group by classid,gender;
select classid,gender,count(*) from students group by gender,classid;

范例: 排序,默认从大到小排序;desc倒序

#只取前3个
MariaDB [hellodb]> select * from students order by name desc limit 3;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
+-------+---------------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)


#跳过前3个只显示后续的2个
MariaDB [hellodb]> select * from students order by name desc limit 3,2;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    19 | Xue Baochai |  18 | F      |       6 |      NULL |
|    16 | Xu Zhu      |  21 | M      |       1 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.000 sec)


范例:正序排序时将NULL记录排在最后

select * from students order by -classid desc;

范例:分组排序

MariaDB [hellodb]> select classid,count(*) 数量 from students group by classid order by 数量;
+---------+--------+
| classid | 数量   |
+---------+--------+
|       5 |      1 |
|    NULL |      2 |
|       2 |      3 |
|       7 |      3 |
|       1 |      4 |
|       4 |      4 |
|       3 |      4 |
|       6 |      4 |
+---------+--------+
8 rows in set (0.000 sec)

MariaDB [hellodb]> select gender 性别,classid 班级,avg(age) 平均年龄 from students where classid is not null group by classid,gender order by classid,gender;
+--------+--------+--------------+
| 性别   | 班级   | 平均年龄     |
+--------+--------+--------------+
| F      |      1 |      19.5000 |
| M      |      1 |      21.5000 |
| M      |      2 |      36.0000 |
| F      |      3 |      18.3333 |
| M      |      3 |      26.0000 |
| M      |      4 |      24.7500 |
| M      |      5 |      46.0000 |
| F      |      6 |      20.0000 |
| M      |      6 |      23.0000 |
| F      |      7 |      18.0000 |
| M      |      7 |      23.0000 |
+--------+--------+--------------+
11 rows in set (0.000 sec)

范例: 分组和排序的次序

顺序: group by,having,order by

范例:时间字段进行过滤查询,并且timestamp可以随其它字段的更新自动更新

MariaDB [hellodb]> create table testdate (id int auto_increment primary key,date timestamp default current_timestamp on update current_timestamp);
MariaDB [hellodb]> insert testdate() values();
MariaDB [hellodb]> insert testdate values(),(),();


MariaDB [hellodb]> select * from testdate ;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2021-02-22 22:32:10 |
|  2 | 2021-02-22 22:32:26 |
|  3 | 2021-02-22 22:32:26 |
|  4 | 2021-02-22 22:32:26 |
+----+---------------------+
4 rows in set (0.000 sec)

#修改其它字段,会自动更新timestamp字段
MariaDB [hellodb]> update testdate set id=10 where id=4;

MariaDB [hellodb]> select * from testdate ;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2021-02-22 22:32:10 |
|  2 | 2021-02-22 22:32:26 |
|  3 | 2021-02-22 22:32:26 |
| 10 | 2021-02-22 22:34:48 |
+----+---------------------+
4 rows in set (0.000 sec)

3.7.2 多表查询

多表查询,即查询结果来自于多张表
在这里插入图片描述

  • 子查询:在SQL语句嵌套着查询语句,性能较差,基于某语句的查询结果再次进行的查询
  • 联合查询:UNION
  • 交叉连接:笛卡尔乘积 CROSS JOIN
  • 内连接:
    等值连接:让表之间的字段以"等值"建立连接关系
    不等值连接
    自然连接:去掉重复列的等值连接 , 语法: FROM table1 NATURAL JOIN table2;
  • 外连接:
    左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
    右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
    完全外连接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL 不支持此SQL语法
  • 自连接:本表和本表进行连接查询

3.7.2.1 子查询

子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表,主要有以下四种常见的用法.

  1. 用于比较表达式中的子查询;子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers);
update students set Age=(SELECT avg(Age) FROM teachers) where stuid=25;
  1. 用于IN中的子查询:子查询应该单独查询并返回一个或多个值重新构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
  1. 用于EXISTS 和 Not EXISTS
    EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS 内部有一个子查询语句(SELECT… FROM…), 将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空,返回一个布尔值。将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果为非空值,则EXISTS子句返回TRUE,外查询的这一行数据便可作为外查询的结果行返回,否则不能作为结果
#查询学生表中teacherid是否与教师表中id一致,一致则为真,打印出来
MariaDB [hellodb]> select * from students s where exists (select * from teachers t where s.teacherid=t.tid);
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)

#说明:
1、EXISTS (或 NOT EXISTS) 用在 where之后,且后面紧跟子查询语句(带括号)
2、EXISTS (或 NOT EXISTS) 只关心子查询有没有结果,并不关心子查询··																																												的结果具体是什么
3、上述语句把students的记录逐条代入到Exists后面的子查询中,如果子查询结果集不为空,即说
明存在,那么这条students的记录出现在最终结果集,否则被排除
  1. 用于FROM子句中的子查询
    使用格式:
SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;

select s.stuid ,s.age from (select * from students) as s;

范例:子查询

MariaDB [hellodb]																																																														> select stuid,name,age from students where age > (select avg(age) from students);
+-------+--------------+-----+
| stuid | name         | age |
+-------+--------------+-----+
|     3 | Xie Yanke    |  53 |
|     4 | Ding Dian    |  32 |
|     6 | Shi Qing     |  46 |
|    13 | Tian Boguang |  33 |
|    16 | Xu Zhu       |  45 |
|    25 | Sun Dasheng  |  77 |
+-------+--------------+-----+
6 rows in set (0.001 sec)

范例:子查询用于更新表

MariaDB [hellodb]> update teachers set age=(select avg(age) from students) where tid=4;

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  27 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.000 sec)

3.7.2.2 联合查询

联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同的.

SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

范例:联合查询

#多表纵向合并union
MariaDB [hellodb]> select stuid,name,age,gender from students
    -> union
    -> select * from teachers ;

MariaDB [hellodb]> select * from emp;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | mage          |  20 | M      |
+-----+---------------+-----+--------+
3 rows in set (0.000 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  27 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.000 sec)

# 联合查询时,两张表内相同内容只显示一次,即去重。
MariaDB [hellodb]> select * from teachers union select * from emp;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  27 | F      |
|   3 | mage          |  20 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.000 sec)

#全部显示使用union all
MariaDB [hellodb]> select * from teachers union all select * from emp;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  27 | F      |
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | mage          |  20 | M      |
+-----+---------------+-----+--------+
7 rows in set (0.000 sec)

范例:去重记录

#单表内去重
MariaDB [hellodb]> select * from emp union select * from emp;
MariaDB [hellodb]> select distinct * from emp;

3.7.2.3 交叉连接

cross join 即多表的记录之间做笛卡尔乘积组合,并且多个表的列横向合并相加, "雨露均沾

比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列

交叉连接生成的记录可能会非常多,建议慎用。

范例:交叉连接

#横向合并,交叉连接(横向笛卡尔)
MariaDB [hellodb]> select * from students cross join teachers;
MariaDB [hellodb]> select * from students , teachers;

#字段名有重复时,需要明确是那张表的字段名
MariaDB [hellodb]> select stuid ,students.name  student_name,tid,teachers.name teacher_name from students cross join teachers;

MariaDB [hellodb]> select stuid ,s.name  student_name,tid,t.name teacher_name from students s cross join teachers t;

+-------+---------------+-----+---------------+
| stuid | student_name  | tid | teacher_name  |
+-------+---------------+-----+---------------+
|     1 | Shi Zhongyu   |   1 | Song Jiang    |
|     1 | Shi Zhongyu   |   2 | Zhang Sanfeng |
|     1 | Shi Zhongyu   |   3 | Miejue Shitai |
|     1 | Shi Zhongyu   |   4 | Lin Chaoying  |
......

3.7.2.4 内连接

inner join 内连接取多个表的交集

范例:

#内连接inner join                                                                                                                                                                                                                                                                                                                                  ···			
MariaDB [hellodb]> select * from students s inner join teachers t on s.stuid
+-------+-------------+-----+--------+---------+-----------+-----+----------
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name     
+-------+-------------+-----+--------+---------+-----------+-----+----------
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   1 | Song Jian
|     2 | Shi Potian  |  22 | M      |       1 |         7 |   2 | Zhang San
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |   3 | Miejue Sh
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoy
+-------+-------------+-----+--------+---------+-----------+-----+----------
4 rows in set (0.000 sec)

#查询学生对应的老师
MariaDB [hellodb]> select s.name ,t.name from students s inner join teachers t on s.teacherid=t.tid;
+-------------+---------------+
| name        | name          |
+-------------+---------------+
| Yu Yutong   | Song Jiang    |
| Shi Zhongyu | Miejue Shitai |
| Ding Dian   | Lin Chaoying  |
+-------------+---------------+
3 rows in set (0.001 sec)


3.7.2.4 左和右外连接

范例:左,右外连接

#左外连接
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |    4 | Lin Chaoying  |   27 | F      |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    16 | Xu Zhu        |  45 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|    25 | Sun Dasheng   |  77 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.000 sec)


#右外连接
MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid=t.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |   22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  |  27 | F      |
|     5 | Yu Yutong   |   26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|  NULL | NULL        | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.000 sec)


#左外连接扩展(去除交集)
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where t.tid is null;
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name | Age  | Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
|     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL | NULL | NULL   |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL | NULL | NULL   |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL | NULL | NULL   |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL | NULL | NULL   |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL | NULL | NULL   |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL | NULL | NULL   |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL | NULL | NULL   |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL | NULL | NULL   |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL | NULL | NULL   |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL | NULL | NULL   |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL | NULL | NULL   |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL | NULL | NULL   |
|    16 | Xu Zhu        |  45 | M      |       1 |      NULL | NULL | NULL | NULL | NULL   |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL | NULL | NULL   |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL | NULL | NULL   |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL | NULL | NULL   |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL | NULL | NULL   |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL | NULL | NULL   |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL | NULL | NULL   |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL | NULL | NULL   |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL | NULL | NULL   |
|    25 | Sun Dasheng   |  77 | M      |    NULL |      NULL | NULL | NULL | NULL | NULL   |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
22 rows in set (0.001 sec)

3.7.2.5 完全外连接

MySQL 不支持完全外连接full outer join语法

范例:完全外连接

#MySQL不支持完全外连接 full outer join,利用以下方式法代替
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid
    -> union
    -> select * from students s right join teachers t on s.teacherid=t.tid;

#完全外连接的扩展示例
MariaDB [hellodb]> select * from students s left  join teachers t on s.teacherid=t.tid where t.tid is null union select * from students s right join teachers t on s.teacherid=t.tid where s.teacherid is null;

范例:三张表连接

MariaDB [hellodb]> select name,course,score from students st inner join scores sc on st.stuid=sc.stuid inner join courses on sc.courseid=courses.courseid;
+-------------+----------------+-------+
| name        | course         | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian |    77 |
| Shi Zhongyu | Weituo Zhang   |    93 |
| Shi Potian  | Kuihua Baodian |    47 |
| Shi Potian  | Daiyu Zanghua  |    97 |
| Xie Yanke   | Kuihua Baodian |    88 |
| Xie Yanke   | Weituo Zhang   |    75 |
| Ding Dian   | Daiyu Zanghua  |    71 |
| Ding Dian   | Kuihua Baodian |    89 |
| Yu Yutong   | Hamo Gong      |    39 |
| Yu Yutong   | Dagou Bangfa   |    63 |
| Shi Qing    | Hamo Gong      |    96 |
| Xi Ren      | Hamo Gong      |    86 |
| Xi Ren      | Dagou Bangfa   |    83 |
| Lin Daiyu   | Taiji Quan     |    57 |
| Lin Daiyu   | Jinshe Jianfa  |    93 |
+-------------+----------------+-------+
15 rows in set (0.000 sec)

3.7.2.6 自连接

自连接, 即表自身连接自身
范例:自连接

MariaDB [hellodb]> select e.name emp,l.name leader from emp e inner join emp l on e.leaderid=l.id;
+----------+----------+
| emp      | leader   |
+----------+----------+
| zhangsir | mage     |
| wang     | zhangsir |
| li       | wang     |
| zhao     | wang     |
+----------+----------+
4 rows in set (0.000 sec)

MariaDB [hellodb]> select e.name emp,l.name leader from emp e left join emp l on e.leaderid=l.id;
+----------+----------+
| emp      | leader   |
+----------+----------+
| mage     | NULL     |
| zhangsir | mage     |
| wang     | zhangsir |
| li       | wang     |
| zhao     | wang     |
+----------+----------+
5 rows in set (0.000 sec)

MariaDB [hellodb]> select e.name emp,ifnull(l.name,"无上级") leader from emp e left join emp l on e.leaderid=l.id;
+----------+-----------+
| emp      | leader    |
+----------+-----------+
| mage     | 无上级    |
| zhangsir | mage      |
| wang     | zhangsir  |
| li       | wang      |
| zhao     | wang      |
+----------+-----------+
5 rows in set (0.000 sec)

3.7.3 SELECT 语句处理的顺序

在这里插入图片描述
查询执行路径中的组件:查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎SELECT语句的执行流程:

FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDERBY --> LIMIT

练习
导入hellodb.sql生成数据库

1. 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
2. 以ClassID为分组依据,显示每组的平均年龄
3. 显示第2题中平均年龄大于30的分组及平均年龄
4. 显示以L开头的名字的同学的信息
5. 显示TeacherID非空的同学的相关信息
6. 以年龄排序后,显示年龄最大的前10位同学的信息
7. 查询年龄大于等于20岁,小于等于25岁的同学的信息
8. 以ClassID分组,显示每班的同学的人数
9. 以Gender分组,显示其年龄之和
10. 以ClassID分组,显示其平均年龄大于25的班级
11. 以Gender分组,显示各组中年龄大于25的学员的年龄之和
12. 显示前5位同学的姓名、课程及成绩
13. 显示其成绩高于80的同学的名称及课程
14. 取每位同学各门课的平均成绩,显示成绩前三名的同学的姓名和平均成绩
15. 显示每门课程课程名称及学习了这门课的同学的个数
16. 显示其年龄大于平均年龄的同学的名字
17. 显示其学习的课程为第1、2,4或第7门课的同学的名字
18. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
19. 统计各班级中年龄大于全校同学平均年龄的同学

3.8 VIEW 视图

视图:虚拟表,保存有实表的查询结果,相当于别名

利用视图,可以隐藏表的真实结构,在程序中利用视图进行查询,可以避免表结构的变化,而修改程序,降低程序和数据库之间的耦合度

创建方法:

CREATE   VIEW view_name [(column_list)]
     AS select_statement
     [WITH [CASCADED | LOCAL] CHECK OPTION]

查看视图定义:

SHOW CREATE VIEW view_name #只能看视图定义
SHOW CREATE TABLE view_name # 可以查看表和视图

删除视图:

DROP VIEW [IF EXISTS]
   view_name [, view_name] ...
   [RESTRICT | CASCADE]

注意:视图中的数据事实上存储于"基表"中,因此,其修改操作也会针对基表实现;其修改操作受基表限制,视图不存储数据,因此没有.ibd文件。

范例:

MariaDB [hellodb]> create view v_emp_leader as select e.name emp,ifnull(l.name,"无上级") leader from emp e left join emp l on e.leaderid=l.id;

MariaDB [hellodb]> show table status like 'v_emp_leader'\G
*************************** 1. row ***************************
            Name: v_emp_leader
          Engine: NULL
         Version: NULL
      Row_format: NULL
            Rows: NULL
  Avg_row_length: NULL
     Data_length: NULL
 Max_data_length: NULL
    Index_length: NULL
       Data_free: NULL
  Auto_increment: NULL
     Create_time: NULL
     Update_time: NULL
      Check_time: NULL
       Collation: NULL
        Checksum: NULL
  Create_options: NULL
         Comment: VIEW
Max_index_length: NULL
       Temporary: NULL
1 row in set (0.000 sec)


MariaDB [hellodb]> select * from v_emp_leader;
+----------+-----------+
| emp      | leader    |
+----------+-----------+
| mage     | 无上级    |
| zhangsir | mage      |
| wang     | zhangsir  |
| li       | wang      |
| zhao     | wang      |
+----------+-----------+
5 rows in set (0.000 sec)

3.9 FUNCTION 函数

函数:分为系统内置函数和自定义函数

  • 系统内置函数参考:
https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html
https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
  • 自定义函数:user-defined function UDF,保存在mysql.proc(MySQL8.0 中已经取消此表)表中
    创建UDF语法
CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name
type,...])
   RETURNS {STRING|INTEGER|REAL}
 runtime_body

说明:

  • 参数可以有多个,也可以没有参数
  • 无论有无参数,小括号()是必须的
  • 必须有且只有一个返回值

查看函数列表:

show function status;

查看函数定义

show create functon function_name

删除UDF

drop function function_name

调用自定义函数语法

select function_name(parameter_value,...)

范例:

#无参UDF
create function simplefun() returns varchar(20) return "hello world";

MariaDB [hellodb]> select simpleFun();
+-------------+
| simpleFun() |
+-------------+
| hello world |
+-------------+
1 row in set (0.000 sec)

#有参数UDF
DELIMITER //
CREATE FUNCTION deleteById(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN
 DELETE FROM students WHERE stuid = id;
 RETURN (SELECT COUNT(*) FROM students);
END//
DELIMITER ;

范例: MySQL8.0 默认开启二进制不允许创建函数

#默认MySQL8.0开启二进制日志,而不允许创建函数
mysql> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS
SQL DATA in its declaration and binary logging is enabled (you *might* want to
use the less safe log_bin_trust_function_creators variable)
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)
#打开此变量允许二进制日志信息函数创建
mysql> set global log_bin_trust_function_creators=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW FUNCTION STATUS like 'simple%'\G
*************************** 1. row ***************************
                 Db: hellodb
               Name: simpleFun
               Type: FUNCTION
             Definer: root@localhost
           Modified: 2021-02-01 21:28:41
             Created: 2021-02-01 21:28:41
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
 Database Collation: utf8_general_ci
 1 row in set (0.00 sec)

范例: Mariadb10.3 默认没有开启二进制日志,所以可以创建函数

#Mariadb默认没有开启二进制日志,所以可以创建函数
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
[root@centos8 ~]#systemctl restart mariadb
MariaDB [hellodb]> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.000 sec)
#开启二进制功能后,也不能创建函数
MariaDB [hellodb]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello
World";
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS
SQL DATA in its declaration and binary logging is enabled (you *might* want to
use the less safe log_bin_trust_function_creators variable)
MariaDB [hellodb]> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.001 sec)
#修改变量允许创建函数
MariaDB [hellodb]> set  global log_bin_trust_function_creators=ON;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello
World";
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
                 Db: hellodb
               Name: simpleFun
               Type: FUNCTION
             Definer: root@localhost
           Modified: 2021-02-01 21:32:23
             Created: 2021-02-01 21:32:23
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.001 sec)

MySQL中的变量
两种变量:系统内置变量和用户自定义变量

  • 系统变量:MySQL数据库中内置的变量,可用@@var_name引用
  • 用户自定义变量分为以下两种
    普通变量:在当前会话中有效,可用@var_name引用
    局部变量:在函数或存储过程内才有效,需要用DECLARE 声明,之后直接用 var_name引用

自定义函数中定义局部变量语法

DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]

说明:局部变量的作用范围是在BEGIN…END程序中,而且定义局部变量语句必须在BEGIN…END的第一行定义

为变量赋值语法

SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name

范例:

DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED)
RETURNS SMALLINT
BEGIN
 DECLARE a, b SMALLINT UNSIGNED;
 SET a = x, b = y;
 RETURN a+b;
END//
DELIMITER ;

MariaDB [hellodb]> select addtwonumber (10,20);
+----------------------+
| addtwonumber (10,20) |
+----------------------+
|                   30 |
+----------------------+
1 row in set (0.000 sec)


范例:

.....
DECLARE x int;
SELECT COUNT(*) FROM tdb_name INTO x;
RETURN x;
END//

范例:自定义的普通变量

#方法1
MariaDB [hellodb]> select count(*) from students into @num ;
#方法2
MariaDB [hellodb]> select count(*) into @num from students;
#查看变量
MariaDB [hellodb]> select @num;
+------+
| @num |
+------+
|   24 |
+------+
1 row in set (0.000 sec)

3.10 PROCEDURE 存储过程

存储过程:多表SQL的语句的集合,可以独立执行,存储过程保存在mysql.proc表中

存储过程优势
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程,提高了运行速度,同时降低网络数据传输量.

存储过程与自定义函数的区别
存储过程实现的过程要复杂一些,而函数的针对性较强

存储过程可以有多个返回值,而自定义函数只有一个返回值

存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用

无参数的存储过程执行过程中可以不加(),函数必须加 ( )

创建存储过程

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
routime_body
proc_parameter : [IN|OUT|INOUT] parameter_name type

说明:其中IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型

查看存储过程列表

SHOW PROCEDURE  STATUS;

查看存储过程定义

SHOW CREATE PROCEDURE sp_name 

调用存储过程

CALL sp_name ([ proc_parameter [,proc_parameter ...]])

说明:当无参时,可以省略"()",当有参数时,不可省略"()"

存储过程修改

ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

范例:

#创建含参存储过程:包含IN参数和OUT参数
delimiter //
CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM students WHERE stuid >= id;
SELECT row_count() into num;
END//
delimiter ;
call deleteById(20,@Line);
SELECT @Line;
#说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行
数值的用户变量@Line,select @Line;输出被影响行数
#row_count() 系统内置函数,用于存放前一条SQL修改过的表的记录数

流程控制
存储过程和函数中可以使用流程控制来控制语句的执行

  • IF:用来进行条件判断。根据是否满足条件,执行不同语句
  • CASE:用来进行条件判断,可实现比IF语句更复杂的条件判断
  • LOOP:重复执行特定的语句,实现一个简单的循环
  • LEAVE:用于跳出循环控制,相当于SHELL中break
  • ITERATE:跳出本次循环,然后直接进入下一次循环,相当于SHELL中continue
  • REPEAT:有条件控制的循环语句。当满足特定条件时,就会跳出循环语句
  • WHILE:有条件控制的循环语句

3.11 TRIGGER 触发器

触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、激活从而实现执行

创建触发器

CREATE [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
   trigger_time trigger_event
    ON tbl_name FOR EACH ROW
   trigger_body

说明:
trigger_name:触发器的名称
trigger_time:{ BEFORE | AFTER },表示在事件之前或之后触发
trigger_event::{ INSERT |UPDATE | DELETE },触发的具体事件
tbl_name:该触发器作用在表名

范例:

#创建触发器,在向学生表INSERT数据时,学生数增加,DELETE学生时,学生数减少
CREATE TABLE student_info (
 stu_id INT(11) NOT NULL AUTO_INCREMENT ,
 stu_name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (stu_id)
);

CREATE TABLE student_count (
 student_count  INT(11) DEFAULT 0
);
INSERT INTO student_count VALUES(0);

CREATE TRIGGER trigger_student_count_insert
AFTER INSERT
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;

CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;

查看触发器

#在当前数据库对应的目录下,可以查看到新生成的相关文件:trigger_name.TRN,table_name.TRG
SHOW TRIGGERS;
#查询系统表information_schema.triggers的方式指定查询条件,查看指定的触发器信息。
USE information_schema;
SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert';

** 删除触发器**

DROP TRIGGER trigger_name;

3.12 Event 事件

3.12.1 Event 事件介绍

3.12.2 Event 管理

3.12.2.1 相关变量和服务器选项

MySQL事件调度器event_scheduler负责调用事件,它默认是关闭的。这个调度器不断地监视一个事件是否要调用, 要创建事件,必须打开调度器

服务器系统变量和服务器选项:

event_scheduler:默认值为OFF,设置为ON才支持Event,并且系统自动打开专用的线程

范例:开启和关闭event_scheduler

#默认事件调度功能是关闭的
MariaDB [hellodb]> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.000 sec)

#临时开启事件调度功能
MariaDB [hellodb]> set global event_scheduler=1;
Query OK, 0 rows affected (0.001 sec)

3.12.2.3 范例

3.13 MySQL 用户管理

相关数据库和表

元数据数据库:mysql
系统授权表:db, host, user,columns_priv, tables_priv, procs_priv, proxies_priv

用户帐号:

'USERNAME'@'HOST'
@'HOST': 主机名: user1@'web1.magedu.org'
IP地址或Network
 通配符: %   _
 示例:wang@172.16.%.%  
     user2@'192.168.1.%'
     mage@'10.0.0.0/255.255.0.0'

创建用户:CREATE USER

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']#示例:
create user test@'10.0.0.0/255.255.255.0' identified by '123456';
create user test2@'10.0.0.%' identified by '123456';

新建用户的默认权限:USAGE

用户重命名:RENAME USER

RENAME USER old_user_name TO new_user_name;

删除用户:

DROP USER 'USERNAME'@'HOST

范例:删除默认的空用户

MariaDB [(none)]> drop user ''@'centos7.magedu.org';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user,host from mysql.user;
+------+--------------------+
| user | host               |
+------+--------------------+
| root | 127.0.0.1          |
| root | ::1                |
| root | centos7.magedu.org |
| root | localhost          |
+------+--------------------+
4 rows in set (0.00 sec)

修改密码:
注意:

  • 新版mysql中用户密码可以保存在mysql.user表的authentication_string字段中
  • 如果mysql.user表的authentication_string和password字段都保存密码,authentication_string优先生效
#方法1,用户可以也可通过此方式修改自已的密码
SET PASSWORD FOR 'user'@'host' = PASSWORD('password'); #MySQL8.0 版本不支持此方法,因为password函数被取消;mariadb支持
例:set password for test@'10.0.0.%'=password('654321');  

set password for root@'localhost'='123456' ;  #MySQL8.0版本支持此方法,此方式直接将密码123456加密后存放在mysql.user表的authentication_string字段;mariadb不支持

#方法2
ALTER  USER test@'%' IDENTIFIED BY 'centos';  #通用改密码方法, 用户可以也可通过此方式
修改自已的密码,MySQL8 版本修改密码
例:alter user test@'10.0.0.%' identified by '111111';


#方法3 此方式MySQL8.0不支持,因为password函数被取消
UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
例:update mysql.user set authentication_string="" where host='10.0.0.%';

#mariadb 10.3
update mysql.user set authentication_string=password('ubuntu') where
user='mage';
#此方法需要执行下面指令才能生效:
FLUSH PRIVILEGES;

忘记管理员密码的解决办法:

  1. 启动mysqld进程时,为其使用如下选项:
--skip-grant-tables
--skip-networking
  1. 使用UPDATE命令修改管理员密码
  2. 关闭mysqld进程,移除上述两个选项,重启mysqld

范例:Mariadb 和MySQL5.6版之前破解root密码

[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables  
skip-networking  
[root@centos8 ~]#systemctl restart mysqld|mariadb
[root@centos8 ~]#mysql
#方法1
#mariadb 旧版和MySQL5.6版之前
MariaDB [(none)]> update mysql.user set password=password('') where user='root';
#mariadb 新版
MariaDB [(none)]> update mysql.user set authentication_string=password('') where user='root';
#方法2
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> alter user root@'localhost' identified by '';

[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables                                                              
         
#skip-networking
[root@centos8 ~]#systemctl restart mysqld|mariadb
[root@centos8 ~]#mysql -uroot -pubuntu

范例: MySQL5.7和8.0 破解root密码

[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables  
skip-networking  #MySQL8.0不需要
[root@centos8 ~]#systemctl restart mysqld
[root@centos8 ~]# mysql

#方法1
mysql> update mysql.user set authentication_string='' where user='root'

#方法2
mysql> flush privileges;
#再执行下面任意一个命令
mysql> alter user root@'localhost' identified by '';
mysql> set password for root@'localhost'='';

[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables                                                              
         
#skip-networking
[root@centos8 ~]#systemctl restart mysqld
[root@centos8 ~]#mysql

范例: 删库跑路之清空root密码方法

#此方法适用于包安装方式的MySQL或Mariadb
[root@centos8 ~]# systemctl stop mysqld
[root@centos8 ~]# rm -rf /var/lib/mysql/*
[root@centos8 ~]# systemctl start mysqld

3.14 权限管理和DCL语句

3.14.1 权限类别

权限类别:

  • 管理类
  • 程序类
  • 数据库级别
  • 表级别
  • 字段级别

管理类:

  • CREATE USER
  • FILE
  • SUPER
  • SHOW DATABASES
  • RELOAD
  • SHUTDOWN
  • REPLICATION SLAVE
  • REPLICATION CLIENT
  • LOCK TABLES
  • PROCESS
  • CREATE TEMPORARY TABLES

程序类:针对 FUNCTION、PROCEDURE、TRIGGER

  • CREATE
  • ALTER
  • DROP
    EXCUTE

库和表级别:针对 DATABASE、TABLE

  • ALTER
  • CREATE
  • CREATE VIEW
  • DROP INDEX
  • SHOW VIEW
  • WITH GRANT OPTION:能将自己获得的权限转赠给其他用户

数据操作

  • SELECT
  • INSERT
  • DELETE
  • UPDATE

字段级别

  • SELECT(col1,col2,…)
  • UPDATE(col1,col2,…)
  • INSERT(col1,col2,…)

所有权限

  • ALL PRIVILEGES 或 ALL

3.14.2 授权

授权:GRANT
范例:

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
GRANT ALL ON wordpress.* TO wordpress@'10.0.0.%' ;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%'  WITH GRANT OPTION;


WITH GRANT OPTION  自己得到权限的同时,也能给别人授权


#创建用户和授权同时执行的方式在MySQL8.0取消了

GRANT ALL ON wordpress.* TO wordpress@'192.168.8.%' IDENTIFIED BY 'magedu';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.8.%' IDENTIFIED BY 'magedu'
WITH GRANT OPTION;

范例:授权

#在mysql8.0上,不能同时创建用户和授权,因此要分开操作
mysql> create user hello@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on hellodb.* to hello@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

#在mariadb中,可以同时实现
MariaDB [(none)]> grant all on hellodb.* to 'hello'@'10.0.0.%' identified by '123456'; 

3.14.3 取消权限

取消授权:REVOKE

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON
[object_type] priv_level FROM user [, user] ...

范例:

MySQL [(none)]> revoke all on hellodb.* from hello@'10.0.0.%';

3.14.4 查看指定用户获得的授权

Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];

注意:
MariaDB服务进程启动时会读取mysql库中所有授权表至内存

(1) GRANT或REVOKE等执行权限操作会保存于系统表中,MariaDB的服务进程通常会自动重读授权表,使之生效

(2) 对于不能够或不能及时重读授权表的命令,可手动让MariaDB的服务进程重读授权表:
mysql> FLUSH PRIVILEGES;

3.15 MySQL的图形化的远程管理工具

常见的图形化管理工具:

  • Navicat
  • SQLyog

4 MySQL 架构和性能优化

在这里插入图片描述

4.1 存储引擎

在这里插入图片描述
查看支持的存储引擎:

 show engines;

查看默认的存储引擎:

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

4.1.1 MyISAM 存储引擎

MyISAM 引擎特点

  • 不支持事务
  • 表级锁定
  • 读写相互阻塞,写入不能读,读时不能写
  • 只缓存索引
  • 不支持外键约束
  • 不支持聚簇索引
  • 读取数据较快,占用资源较少
  • 不支持MVCC(多版本并发控制机制)高并发
  • 崩溃恢复性较差
  • MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

  • 只读(或者写较少)
  • 表较小(可以接受长时间进行修复操作)

MyISAM 引擎文件

  • tbl_name.frm 表格式定义
  • tbl_name.MYD 数据文件
  • tbl_name.MYI 索引文件

4.1.2 InnoDB 引擎

InnoDB引擎特点

  • 行级锁
  • 支持事务,适合处理大量短期事务
  • 读写阻塞与事务隔离级别相关
  • 可缓存数据和索引
  • 支持聚簇索引
  • 崩溃恢复性更好
  • 支持MVCC高并发
  • 从MySQL5.5后支持全文索引
  • 从MySQL5.5.5开始为默认的数据库引擎

InnoDB数据库文件
在这里插入图片描述

  • 所有InnoDB表的数据和索引放置于同一个表空间中
数据文件:ibdata1, ibdata2,存放在datadir定义的目录下
表格式定义:tb_name.frm,存放在datadir定义的每个数据库对应的目录下
  • 每个表单独使用一个表空间存储表的数据和索引
两类文件放在对应每个数据库独立目录中
数据文件(存储数据和索引):tb_name.ibd
表格式定义:tb_name.frm

启用:innodb_file_per_table=ON (MariaDB 5.5以后版是默认值)

4.1.3 其它存储引擎

  • Performance_Schema:Performance_Schema数据库使用
  • Memory :将所有数据存储在RAM中,以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
  • MRG_MyISAM:使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组,并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境,如数据仓库
  • Archive :为存储和检索大量很少参考的存档或安全审核信息,只支持SELECT和INSERT操作;支持行级锁和专用缓存区
  • Federated联合:用于访问其它远程MySQL服务器一个代理,它通过创建一个到远程MySQL服务器的客户端连接,并将查询传输到远程服务器执行,而后完成数据存取,提供链接单独MySQL服务器的能力,以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
  • BDB:可替代InnoDB的事务引擎,支持COMMIT、ROLLBACK和其他事务特性
  • Cluster/NDB:MySQL的簇式数据库引擎,尤其适合于具有高性能查找要求的应用程序,这类查找需求还要求具有最高的正常工作时间和可用性
  • CSV:CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
  • BLACKHOLE :黑洞存储引擎接受但不存储数据,检索总是返回一个空集。该功能可用于分布式数据库设计,数据自动复制,但不是本地存储
  • example:"stub"引擎,它什么都不做。可以使用此引擎创建表,但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

4.1.4 管理存储引擎

查看mysql支持的存储引擎

show engines;

查看当前默认的存储引擎

show variables like '%storage_engine%';

设置默认的存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine= InnoDB

查看库中所有表使用的存储引擎

show table status from db_name;

查看库中指定表的存储引擎

show table status like  'tb_name';
show create table tb_name;

设置表的存储引擎:

CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

4.2 MySQL 中的系统数据库

  • mysql 数据库
  • information_schema 数据库
  • performance_schema 数据库
  • sys 数据库

4.3 服务器配置和状态

可以通过mysqld选项,服务器系统变量和服务器状态变量进行MySQL的配置和查看状态

官方帮助:

https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-andstatus-variables/

注意:

  • 其中有些参数支持运行时修改,会立即生效
  • 有些参数不支持动态修改,且只能通过修改配置文件,并重启服务器程序生效
  • 有些参数作用域是全局的,为所有会话设置
  • 有些可以为每个用户提供单独(会话)的设置

4.3.1 服务器选项

注意: 服务器选项用横线,不用下划线

获取mysqld的可用选项列表:

#查看mysqld可用选项列表和及当前值
mysqld --verbose --help

#获取mysqld当前启动选项
mysqld --print-defaults

设置服务器选项方法:

  1. 在命令行中设置
shell> /usr/bin/mysqld_safe --skip-name-resolve=1
shell> /usr/libexec/mysqld --basedir=/usr
  1. 在配置文件my.cnf中设置
vim /etc/my.cnf
[mysqld]
skip_name_resolve=1
skip-grant-tables

4.3.2 服务器系统变量

服务器系统变量:可以分全局和会话两种

注意: 系统变量用下划线,不用横线

获取系统变量

SHOW GLOBAL VARIABLES; #只查看global变量
SHOW [SESSION] VARIABLES;#查看所有变量(包括global和session)

#查看指定的系统变量
SHOW VARIABLES LIKE 'VAR_NAME';
SELECT @@VAR_NAME;

#查看选项和部分变量
[root@centos8 ~]#mysqladmin variables

修改服务器变量的值:

help SET

修改全局变量:仅对修改后新创建的会话有效;对已经建立的会话无效

SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;

修改会话变量:

SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;

4.3.3 服务器状态变量

服务器状态变量:分全局和会话两种

状态变量(只读):用于保存mysqld运行中的统计数据的变量,不可更改

SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;

范例:

mysql> select * from students;

mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 4     |
+---------------+-------+
1 row in set (0.00 sec)

4.3.4 服务器变量 SQL_MODE

SQL_MODE:对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置

参考:
https://mariadb.com/kb/en/library/sql-mode/
https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sql-mode

常见MODE:

  • NO_AUTO_CREATE_USER: 禁止GRANT创建密码为空的用户
  • NO_ZERO_DATE:在严格模式,不允许使用’0000-00-00’的时间
  • ONLY_FULL_GROUP_BY: 对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY
    中出现,那么将认为这个SQL是不合法的
  • NO_BACKSLASH_ESCAPES: 反斜杠""作为普通字符而非转义字符
  • PIPES_AS_CONCAT: 将"||"视为连接操作符而非"或"运算符

范例:CentOS 8 修改SQL_MODE变量实现分组语句控制

#修改前MariaDB [hellodb]> select classid,count(*),stuid from students group by classid;
+---------+----------+-------+
| classid | count(*) | stuid |
+---------+----------+-------+
|       1 |        1 |     2 |
|       2 |        2 |     1 |
|       3 |        2 |     5 |
|       4 |        1 |     4 |
|       5 |        1 |     6 |
|       6 |        1 |     9 |
|       7 |        1 |     8 |
+---------+----------+-------+
7 rows in set (0.000 sec)


#修改sql_mode
MariaDB [hellodb]> set sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

#修改后
MariaDB [hellodb]> select classid,count(*),stuid from students group by classid;
ERROR 1055 (42000): 'hellodb.students.StuID' isn't in GROUP BY

范例:CentOS 7 修改SQL_MODE变量

MariaDB [hellodb]> create table test (id int ,name varchar(3));
Query OK, 0 rows affected (0.04 sec)
MariaDB [hellodb]> insert test values(1,'abcde');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [hellodb]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | abc |
+------+------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show variables like 'SQL_MODE';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode     |       |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> SET SQL_MODE=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'SQL_MODE';
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| Variable_name | Value                                                        
                                                                               
      |
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| sql_mode     |
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIV
ISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> insert test values(2,'magedu');
ERROR 1406 (22001): Data too long for column 'name' at row 1

4.4 Query Cache 查询缓存(非重点)

4.5 INDEX 索引

4.5.1 索引介绍

索引:是排序的快速查找的特殊数据结构,定义作为查找条件的字段上,又称为键key,索引通过存储引擎实现
优点:

  • 索引可以降低服务需要扫描的数据量,减少了IO次数
  • 索引可以帮助服务器避免排序和使用临时表
  • 索引可以帮助将随机I/O转为顺序I/O

缺点:

  • 占用额外空间,影响插入速度

索引类型:

  • B+ TREE、HASH、R TREE、FULL TEXT
  • 聚簇(集)索引、非聚簇索引:数据和索引是否存储在一起
  • 主键索引、二级(辅助)索引
  • 稠密索引、稀疏索引:是否索引了每一个数据项
  • 简单索引、组合索引: 是否是多个字段的索引
  • 左前缀索引:取前面的字符做索引
  • 覆盖索引:从索引中即可取出要查询的数据,性能高

4.5.2 索引结构

参考链接 :

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树

参考链接: https://www.cs.usfca.edu/~galles/visualization/BST.html

在这里插入图片描述
红黑树

参考链接:https://www.cs.usfca.edu/~galles/visualization/RedBlack.html
在这里插入图片描述
B-Tree 索引
参考链接: https://www.cs.usfca.edu/~galles/visualization/BTree.html
在这里插入图片描述

B+Tree索引
参考链接: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
在这里插入图片描述

B+Tree索引:按顺序存储,每一个叶子节点到根结点的距离是相同的;左前缀索引,适合查询范围类的数据

P1指针:5~28; P2指针:28~65; P3指针:65~;指针占6个字节

面试题: InnoDB中一颗的B+树可以存放多少行数据?

假设定义一颗B+树高度为2,即一个根节点和若干叶子节点。那么这棵B+树的存放总行记录数=根节点指针数*
单个叶子记录的行数。这里先计算叶子节点,B+树中的单个叶子节点的大小为16K,假设每一条目为1K,那么
记录数即为16(16k/1K=16),然后计算非叶子节点能够存放多少个指针,假设主键ID为bigint类型,那么
长度为8字节,而指针大小在InnoDB中是设置为6个字节,这样加起来一共是14个字节。那么通过页大小/(主
键ID大小+指针大小),即16384/14=1170个指针,所以一颗高度为2的B+树能存放16*1170=18720条这样
的记录。根据这个原理就可以算出一颗高度为3的B+树可以存放16*1170*1170=21902400条记录。所以在
InnoDB中B+树高度一般为2-3层,它就能满足千万级的数据存储

可以使用B+Tree索引的查询类型:(假设前提: 姓,名,年龄三个字段建立了一个复合索引)

  • 全值匹配:精确所有索引列,如:姓wang,名xiaochun,年龄30
  • 匹配最左前缀:即只使用索引的第一列,如:姓wang
  • 匹配列前缀:只匹配一列值开头部分,如:姓以w开头的记录
  • 匹配范围值:如:姓ma和姓wang之间
  • 精确匹配某一列并范围匹配另一列:如:姓wang,名以x开头的记录
  • 只访问索引的查询

B+Tree索引的限制:

  • 如不从最左列开始,则无法使用索引,如:查找名为xiaochun,或姓为g结尾
  • 不能跳过索引中的列:如:查找姓wang,年龄30的,只能使用索引第一列

特别提示:
索引列的顺序和查询语句的写法应相匹配,才能更好的利用索引为优化性能,可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

Hash索引

Hash索引:基于哈希表实现,只有精确匹配索引中的所有列的查询才有效,索引自身只存储索引列对应的哈希值和数据指针,索引结构紧凑,查询性能好

Memory存储引擎支持显式hash索引,InnoDB和MyISAM存储引擎不支持

适用场景:只支持等值比较查询,包括=, <=>, IN()

不适合使用hash索引的场景

  • 不适用于顺序查询:索引存储顺序的不是值的顺序
  • 不支持模糊匹配
  • 不支持范围查询
  • 不支持部分索引列匹配查找:如A,B列索引,只查询A列索引无效

地理空间数据索引R-Tree( Geospatial indexing )

MyISAM支持地理空间索引,可使用任意维度组合查询,使用特有的函数访问,常用于做地理数据存
储,使用不多

InnoDB从MySQL5.7之后也开始支持

全文索引(FULLTEXT)

在文本中查找关键词,而不是直接比较索引中的值,类似搜索引擎
InnoDB从MySQL 5.6之后也开始支持

聚簇和非聚簇索引,主键和二级索引

聚集索引磁盘上摆列的次序就是主键的次序

MyISAM存储引擎:索引存放在.MYI文件中;数据存放在.MYD文件中
在这里插入图片描述
冗余和重复索引:

  • 冗余索引:(A),(A,B),注意如果同时存在,仍可能会使用(A)索引
  • 重复索引:已经有索引,再次建立索引

4.5.3 索引优化

参考资料: 阿里的《Java开发手册》

https://developer.aliyun.com/topic/java2020

4.5.4 管理索引

创建索引:

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);
help CREATE INDEX;

删除索引:

DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引:

SHOW INDEXES FROM [db_name.]tbl_name;

优化表空间:

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

范例:

#创建索引
mysql> create index idx_name on testlog(name(8));
#创建复合索引
mysql> create index idx_name_age on testlog(name,age);




#查看索引
mysql> show index from testlog \G

4.5.5 EXPLAIN 工具

可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息,用来查看查询优化器如何执行查询

参考资料: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

语法:

EXPLAIN SELECT clause

EXPLAIN输出信息说明:
在这里插入图片描述
说明: type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:NULL> system >
const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >
range > index > ALL ,一般来说,得保证查询至少达到range级别,最好能达到ref

在这里插入图片描述
范例:创建索引和使用索引

#创建索引前
mysql> explain select * from students where name='Lin Chong';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql> create index idx_name on students(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#创建索引后
mysql> explain select * from students where name='Lin Chong';
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | ref  | idx_name      | idx_name | 152     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)



4.6 并发控制

4.6.1 锁机制

锁类型:

  • 读锁:共享锁,也称为 S 锁,只读不可写(包括当前事务) ,多个读互不阻塞
  • 写锁:独占锁,排它锁,也称为 X 锁,写锁会阻塞其它事务(不包括当前事务)的读和写
  • S 锁和 S 锁是兼容的,X 锁和其它锁都不兼容,举个例子,事务 T1 获取了一个行 r1 的 S 锁,另外事务 T2 可以立即获得行 r1 的 S 锁,此时 T1 和 T2 共同获得行 r1 的 S 锁,此种情况称为锁兼容,但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁,则必须等待 T1 对行 r1 锁的释放,此种情况也称为锁冲突

锁粒度:

  • 表级锁:MyISAM
  • 行级锁:InnoDB

实现

  • 存储引擎:自行实现其锁策略和锁粒度
  • 服务器级:实现了锁,表级锁,用户可显式请求

分类:

  • 隐式锁:由存储引擎自动施加锁
  • 显式锁:用户手动请求

锁策略:在锁粒度及数据安全性寻求的平衡机制

4.6.2 显式使用锁

帮助:https://mariadb.com/kb/en/lock-tables/

加锁

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias]
lock_type] ...
lock_type:
READ  	 #读锁
WRITE  	 #写锁

解锁

UNLOCK TABLES

关闭正在打开的表(清除查询缓存),通常在备份前加全局读锁

FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]   

查询时加写或读锁

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

范例:加读锁

mysql> lock tables students read;
Query OK, 0 rows affected (0.00 sec)

#加锁后,当前终端状态
mysql> update students set classid=2 where stuid=25;
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated

#加锁后,其他终端状态
mysql> update students set classid=2 where stuid=25;
#解锁后,立即释放执行
Query OK, 1 row affected (2.94 sec)
Rows matched: 1  Changed: 1  Warnings: 0

范例:加写锁

#添加后,不影响当前终端,拒绝其他终端执行操作
mysql> select * from students limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

#其他终端
mysql> select * from students;

范例: 同时在两个终端对同一行记录修改

#同时对同一行记录执行update

#在第一终端提示1行成功
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#在第二终端提示0行修改
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0

4.6.3 事务

事务 Transactions:一组原子性的 SQL语句,或一个独立工作单元
事务日志:记录事务信息,实现undo,redo等故障恢复功能

4.6.3.1 事务特性

ACID特性:

  • A:atomicity 原子性;整个事务中的所有操作要么全部成功执行,要么全部失败后回滚
  • C:consistency一致性;数据库总是从一个一致性状态转换为另一个一致性状态
  • I:Isolation隔离性;一个事务所做出的操作在提交之前,是不能为其它事务所见;隔离有多种隔离级别,实现并发
  • D:durability持久性;一旦事务提交,其所做的修改会永久保存于数据库中

Transaction 生命周期
在这里插入图片描述

4.6.3.2 管理事务

显式启动事务:

BEGIN
BEGIN WORK
START TRANSACTION

结束事务:

#提交
COMMIT

#回滚,只支持DML语句
ROLLBACK

注意:只有事务型存储引擎中的DML语句方能支持此类操作

自动提交:

set autocommit={1|0} 

默认为1,为0时设为非自动提交
建议:显式请求和提交事务,而不要使用"自动提交"功能

事务支持保存点:

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

查看事务:

#查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

#以下两张表在MySQL8.0中已取消
#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

死锁:
两个或多个事务在同一资源相互占用,并请求锁定对方占用的资源的状态

#杀掉未完成的事务
MariaDB [hellodb]> kill 13;
Query OK, 0 rows affected (0.000 sec)

#查看事务锁的超时时长,默认50s
MariaDB [hellodb]> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name           | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50   |
+--------------------------+-------+
1 row in set (0.001 sec)

4.6.3.3 事务隔离级别

MySQL 支持四种隔离级别,事务隔离级别从上至下更加严格

隔离级别脏读不可重复读幻读加读锁
读未提交可以出现可以出现可以出现
读提交不允许出现可以出现可以出现
可重复读不允许出现不允许出现可以出现
序列化不允许出现不允许出现不允许出现
  • READ UNCOMMITTED
    可读取到未提交数据,产生脏读

  • READ COMMITTED
    可读取到提交数据,但未提交数据不可读,产生不可重复读,即可读取到多个提交数据,导致每次读取数据不一致

  • REPEATABLE READ
    可重复读,多次读取数据都一致,产生幻读,即读取过程中,即使有其它提交的事务修改数据,仍只能读取到未修改前的旧数据。此为MySQL默认设置(不论1提交与否,2都看不见)

  • SERIALIZABLE
    可串行化,未提交的读事务阻塞修改事务(加读锁,但不阻塞读事务),或者未提交的修改事务阻塞其它事务的读写(加写锁,其它事务的读,写都不可以执行)。会导致并发性能差

MVCC和事务的隔离级别:

MVCC(多版本并发控制机制)只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行,而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

指定事务隔离级别:

  • 服务器变量tx_isolation(MySQL8.0改名为transaction_isolation)指定,默认为REPEATABLEREAD,可在GLOBAL和SESSION级进行设置
#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'
#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'
  • 服务器选项中指定
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

范例: MySQL8.0 事务隔离级别系统变量tx_isolation取消

mysql> select @@tx_isolation;
ERROR 1193 (HY000): Unknown system variable 'tx_isolation'

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

4.7 日志管理

MySQL 支持丰富的日志类型,如下:

  • 事务日志:transaction log
    事务日志的写入类型为"追加",因此其操作为"顺序IO";通常也被称为:预写式日志 write ahead
    logging
    事务日志文件: ib_logfile0, ib_logfile1
  • 错误日志 error log
  • 通用日志 general log
  • 慢查询日志 slow query log
  • 二进制日志 binary log
  • 中继日志 reley log,在主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取的事件

4.7.1 事务日志

事务日志:transaction log;先执行,在保存到磁盘中

  • redo log:实现 WAL(Write Ahead Log) ,数据更新前先记录redo log
  • undo log:保存与执行的操作相反的操作,用于实现rollback

事务型存储引擎自行管理和使用,建议和数据文件分开存放

Innodb事务日志相关配置:

show variables like ‘%innodb_log%’;

innodb_log_file_size   50331648 		#每个日志文件大小		
innodb_log_files_in_group  2    	    #日志组成员个数
innodb_log_group_home_dir ./ 			#事务文件路径
innodb_flush_log_at_trx_commit 			#默认为1,事务提交方式

事务日志性能优化

set global innodb_flush_log_at_trx_commit=0|1|2
1模式 提交一次写一次磁盘

0模式 执行操作先放入MySQL buffer缓冲区中,提交将信息写入og_buffer缓冲区,一秒中把数据写入os缓冲区中并刷新到磁盘

2模式 执行操作先放入MySQL log_buffer缓冲区中,一旦提交写入os缓冲区中,一秒中把数据写入到磁盘

在这里插入图片描述

1 此为默认值,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性

0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供
更好的性能,但服务器崩溃可能丢失最后一秒的事务

2 每次提交后都会写入OS的缓冲区,但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些,但操作系
统或停电可能导致最后一秒的交易丢失

高并发业务行业最佳实践,是使用第三种折衷配置(=2):

1.配置为2和配置为0,性能差异并不大,因为将数据从Log Buffer拷贝到OS cache,虽然跨越用户态与内核态,
但毕竟只是内存的数据拷贝,速度很快

2.配置为2和配置为0,安全性差异巨大,操作系统崩溃的概率相比MySQL应用程序崩溃的概率,小很多,设置为2,
只要操作系统不奔溃,也绝对不会丢数据

说明:

  • 设置为1,同时sync_binlog = 1表示最高级别的容错
  • innodb_use_global_flush_log_at_trx_commit=0 时,将不能用SET语句重置此变量( MariaDB10.2.6 后废弃)

4.7.2 错误日志

错误日志

  • mysqld启动和关闭过程中输出的事件信息
  • mysqld运行中产生的错误信息
  • event scheduler运行一个event时产生的日志信息
  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息**
    错误文件路径
MariaDB [hellodb]> show global variables like  'log_error';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.001 sec)

记录哪些警告信息至错误日志文件
log_warnings:数值越大,记录的越详细

#CentOS8 mariadb 10.3 默认值为2
log_warnings=0|1|2|3...				#mysql5.7之前
log_error_verbosity=0|1|2|3...		#mysql8.0

4.7.3 通用日志

通用日志:记录对数据库的通用操作,包括:错误的SQL语句

通用日志可以保存在:file(默认值)或 table(mysql.general_log表)

通用日志相关设置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

范例:启用通用日志

#默认没有启用通用日志
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

#启用
mysql> set global general_log=1;
Query OK, 0 rows affected (0.11 sec)

mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

#默认通用日志存放在文件中
mysql> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

#通用日志存放的文件路径
mysql> select @@general_log_file;
+----------------------------+
| @@general_log_file         |
+----------------------------+
| /var/lib/mysql/centos8.log |
+----------------------------+
1 row in set (0.00 sec)

范例:修改通用日志到表中

mysql> set global log_output="table";
mysql> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output   | TABLE |
+---------------+-------+
1 row in set (0.002 sec)

#general_log表是CSV格式的存储引擎
mysql> show table status like 'general_log'\G
*************************** 1. row ***************************
           Name: general_log
         Engine: CSV
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-02-23 17:33:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: General log
1 row in set (0.00 sec)

#general_log表是CSV的文本文件,可导出查看
[root@centos8 ~]# sz /var/lib/mysql/mysql/general_log.CSV

范例: 查找执行次数最多的前三条语句

mysql> select argument,count(argument) num from mysql.general_log group by argument order by   num desc limit 3;
+---------------------------+-----+
| argument                  | num |
+---------------------------+-----+
| select * from teachers    |  12 |
| select * from students    |   4 |
| select * from general_log |   3 |
+---------------------------+-----+
3 rows in set (0.00 sec)

4.7.4 慢查询日志

慢查询日志:记录执行查询时长超出指定时长的操作

慢查询相关变量

*slow_query_log=ON|OFF #开启或关闭慢查询,支持全局和会话,只有全局设置才会生成慢查询文件

*long_query_time=N #慢查询的阀值,单位秒,默认为10s

slow_query_log_file=HOSTNAME-slow.log  #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
#上述查询类型且查询时长超过long_query_time,则记录日志

*log_queries_not_using_indexes=ON  #不使用索引或使用全索引扫描,不论是否达到慢查询阀值的语

句是否记录日志,默认OFF,即不记录
log_slow_rate_limit = 1 #多少次查询才记录,mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF    #同slow_query_log,MariaDB 10.0/MySQL 5.6.1 版后已删除

范例:开启慢查询

方法一:
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

方法二:(长期开启)
[root@centos8 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
slow_query_log=1
[root@centos8 ~]# systemctl restart mysqld.service 


范例: 慢查询分析工具mysqldumpslow

4.7.5 使用 profile 工具

[root@centos8 ~]#mysqldumpslow --help


[root@centos8 ~]# mysqldumpslow -s c -t 2 /var/lib/mysql/centos8-slow.log  

Reading mysql slow query log from /var/lib/mysql/centos8-slow.log
Count: 7  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=8.0 (56), root[root]@localhost
  select * from testlog limit N

Count: 2  Time=0.04s (0s)  Lock=0.00s (0s)  Rows=100000.0 (200000), root[root]@localhost
  select * from testlog

4.7.5 使用 profile 工具

#打开后,会显示语句执行详细的过程
set profiling = 1;

查看语句,注意结果中的query_id值

#查看语句,注意结果中的query_id值
show profiles ;
mysql> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00038925 | select @@profiling            |
|        2 | 4.00245700 | select sleep(1) from teachers |
|        3 | 0.00015275 | show profiling                |
+----------+------------+-------------------------------+
3 rows in set, 1 warning (0.00 sec)

#显示语句的详细执行步骤和时长

#显示语句的详细执行步骤和时长
Show profile for query #  
mysql> show profile for query 4;
+---------------+----------+
| Status        | Duration |
+---------------+----------+
| starting      | 0.000044 |
| freeing items | 0.000018 |
| cleaning up   | 0.000007 |
+---------------+----------+
3 rows in set, 1 warning (0.00 sec)

显示cpu使用情况

#显示cpu使用情况
Show profile cpu for query # 
mysql> show profile cpu for query 4;
+---------------+----------+----------+------------+
| Status        | Duration | CPU_user | CPU_system |
+---------------+----------+----------+------------+
| starting      | 0.000044 | 0.000009 |   0.000033 |
| freeing items | 0.000018 | 0.000004 |   0.000013 |
| cleaning up   | 0.000007 | 0.000002 |   0.000006 |
+---------------+----------+----------+------------+
3 rows in set, 1 warning (0.00 sec)

4.7.6 二进制日志(备份)

  • 记录导致数据改变或潜在导致数据改变的SQL语句
  • 记录已提交的日志
  • 不依赖于存储引擎类型

功能:通过"重放"日志文件中的事件来生成数据副本

注意:建议二进制日志和数据文件分开存放

二进制日志记录三种格式

  • 基于"语句"记录:statement,记录语句,默认模式( MariaDB 10.2.3 版本以下 ),日志量较少
  • 基于"行"记录:row,记录数据,日志量较大,更加安全,建议使用的格式,MySQL8.0默认格式
  • 混合模式:mixed, 让系统自行判定该基于哪种方式进行,默认模式( MariaDB 10.2.4及版本以上)

格式配置

#更改记录格式
[mysqld]
binlog_format="statement|row|mixed"

MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.001 sec)
#MySQL 8.0 默认使用ROW方式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.07 sec)

二进制日志文件的构成

有两类文件
1.日志文件:mysql|mariadb-bin.文件名后缀,二进制格式,如: on.000001,mariadb-bin.000002
2.索引文件:mysql|mariadb-bin.index,文本格式,记录当前已有的二进制日志文件列表

二进制日志相关的服务器变量:

sql_log_bin=ON|OFF:#是否记录二进制日志,默认ON,支持动态修改,系统变量,而非服务器选项

log_bin=/PATH/BIN_LOG_FILE:#指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可以
[mysqld]
log_bin=/mysql/logbin/mysql-bin

binlog_format=STATEMENT|ROW|MIXED:#二进制日志记录的格式,默认STATEMENT
max_binlog_size=1073741824:#单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G
#说明:文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接)
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0:#设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘
expire_logs_days=N:#二进制日志可以自动删除的天数。 默认为0,即不自动删除

二进制日志相关配置

查看所有的二进制日志文件

show {binary | master} logs;

查看使用中的二进制日志文件

show master status;

在线查看二进制文件中的指定内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

范例:

mysql> show binlog events in 'binlog.000010' from 42181352;

mysqlbinlog:二进制日志的客户端命令工具,支持离线查看二进制日志

mysqlbinlog [OPTIONS] log_file…
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式:YYYY-MM-DD hh:mm:ss
 --stop-datetime=
 --base64-output[=name]
        -v -vvv

范例:

[root@centos8 ~]# mysqlbinlog --start-position=42181352 /var/lib/mysql/binlog.000010 -v

切换日志文件:

mysql> flush logs;
[root@centos8 ~]#mysqladmin   flush-logs

清除指定二进制日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

范例:

mysql> purge binary logs to 'binlog.000008';

mysql> purge binary logs before '2021-02-26 09:08';


删除所有二进制日志,index文件重新记数

RESET MASTER [TO #]; #删除所有二进制日志文件,并重新生成日志文件,文件名从#开始记数,默认从
1开始,一般是master主机第一次启动时执行,MariaDB 10.1.6开始支持TO #

5 备份和恢复

5.1 备份恢复概述

5.1.1 为什么要备份

灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

5.1.2 备份类型

  • 完全备份,部分备份

    • 完全备份:整个数据集
    • 部分备份:只备份数据子集,如部分库或表
  • 完全备份、增量备份、差异备份

    • 增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
      差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单
      在这里插入图片描述
      注意:二进制日志文件不应该与数据文件放在同一磁盘
  • 冷、温、热备份
    冷备:读、写操作均不可进行,数据库停止服务
    温备:读操作可执行;但写操作不可执行
    热备:读、写操作均可执行

    • MyISAM:温备,不支持热备
    • InnoDB:都支持
  • 物理和逻辑备份
    物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快

    逻辑备份:从数据库中"导出"数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度

5.1.3 备份什么

  • 数据
  • 二进制日志、InnoDB的事务日志
  • 用户帐号,权限设置,程序代码(存储过程、函数、触发器、事件调度器)
  • 服务器的配置文件

5.1.4 备份注意要点

  • 能容忍最多丢失多少数据
  • 备份产生的负载
  • 备份过程的时长
  • 温备的持锁多久
  • 恢复数据需要在多长时间内完成
  • 需要备份和恢复哪些数据

5.1.5 还原要点

  • 做还原测试,用于测试备份的可用性
  • 还原演练,写成规范的技术文档

5.1.6 备份工具

  • cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
  • LVM的快照:先加读锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
  • mysqldump:逻辑备份工具,适用所有存储引擎,对MyISAM存储引擎进行温备;支持完全或部分备份;对InnoDB存储引擎支持热备,结合binlog的增量备份
  • xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份
  • MariaDB Backup: 从MariaDB 10.1.26开始集成,基于Percona XtraBackup 2.3.8实现
  • mysqlbackup:热备份, MySQL Enterprise Edition 组件
  • mysqlhotcopy:PERL 语言实现,几乎冷备,仅适用于MyISAM存储引擎,使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

5.1.7 基于 LVM 的快照备份

(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
(3) 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4) 释放锁
mysql> UNLOCK TABLES;
(5) 挂载快照卷,执行数据备份
(6) 备份完成后,删除快照卷
(7) 制定好策略,通过原卷备份二进制日志

5.1.8 实战案例:数据库冷备份和还原

#在源主机(10.0.0.8)执行
[root@centos8 ~]# systemctl stop mariadb
[root@centos8 ~]# cd /var/lib/mysql/
[root@centos8 mysql]#tar cvf /data/mysql-backup-`date +%F`.tar .

#复制相关文件
[root@centos8 mysql]#scp /data/mysql-backup-2021-03-01.tar  10.0.0.108:


#在目标服务器(10.0.0.108)安装mariadb-server,不启动服务
[root@centos8 ~]# yum install mariadb -y
#解压缩到/var/lib/mysql下
[root@centos8 ~]# tar xvf mysql-backup-2021-03-01.tar -C /var/lib/mysql
#查看所有者和所属住是否为mysql
[root@centos8 ~]# ls -l /var/lib/mysql/

#登录测试,检出数据
[root@centos8 ~]# mysql;

5.2 mysqldump 备份工具

5.2.1 mysqldump 说明

逻辑备份工具:

mysqldump, mydumper, phpMyAdmin

Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

mysqldump是MySQL的客户端命令,通过mysql协议连接至mysql服务器进行备份


命令格式:

mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份,但数据库本身定义不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份,包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS]        #备份所有数据库,包含数据库本身定义也会备份

范例:三种备份方式

#1.支持指定数据库和指定多表的备份,但数据库本身定义不备份
[root@centos8 ~]# mysqldump -uroot hellodb > /backup/hellodb_`date +%F_%H-%M-%S`.sql
#因为数据库本身定义没有备份,所以要先创建数据库
[root@centos8 ~]# mysql -e  'create database linux43'
[root@centos8 ~]# mysql linux43 > /backup/hellodb_`date +%F_%H-%M-%S`.sql


#2.支持指定数据库备份,包含数据库本身定义也会备份
[root@centos8 ~]#mysqldump -uroot -B hellodb linux43 > /backup/hellodb_linux43_B_`date +%F_%H-%M-%S`.sql
#关掉二进制日志再还原,避免产生大批量还原日志
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /backup/hellodb_linux43_B_2021-03-01_16-54-40.sql; 
MariaDB [(none)]> set sql_log_bin=1;


#3.备份所有数据库,包含数据库本身定义也会备份

[root@centos8 ~]#mysqldump -uroot -A > /backup/all_`date +%F_%H-%M-%S`.sql

mysqldump参考:

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump 常见通用选项:

* -A, --all-databases 				#备份所有数据库,含create database
* -B, --databases db_name…  		#指定备份的数据库,包括create database语句
-E, --events:						#备份相关的所有event scheduler
-R, --routines:					#备份所有存储过程和自定义函数
--triggers:						#备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
--default-character-set=utf8		#指定字符集
* --master-data[=#]: 		 	 	#此选项须启用二进制日志
#1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1,适合于主从复制多机使用
#2:记录为被注释的#CHANGE MASTER TO语句,适合于单机使用,适用于备份还原
#此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启--single-transaction)

* -F, --flush-logs					#备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A 或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和--single-transaction或-x,--master-data 一起使用实现,此时只刷新一次二进制日志
--compact       	 				#去掉注释,适合调试,节约备份占用的空间,生产不使用
-d, --no-data    					#只备份表结构,不备份数据,即只备份create table
-t, --no-create-info 				#只备份数据,不备份表结构,即不备份create table
-n,--no-create-db 					#不备份create database,可被-A或-B覆盖
--flush-privileges 					#备份mysql或相关时需要使用
-f, --force       					#忽略SQL错误,继续执行
--hex-blob        					#使用十六进制符号转储二进制列,当有包括BINARY, VARBINARY,BLOB,BIT的数据类型的列时使用,避免乱码
-q, --quick     					#不缓存查询,直接输出,加快备份速度

mysqldump的MyISAM存储引擎相关的备份选项:

MyISAM不支持事务,只能支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作

-x,--lock-all-tables #加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项会关闭此选项功能,注意:数据量大时,可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用

mysqldump的InnoDB存储引擎相关的备份选项:

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备,也可以实现温备但不建议用

--single-transaction
#此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表
(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储
文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP
TABLE,RENAME TABLE,TRUNCATE TABLE,此选项和--lock-tables(此选项隐含提交挂起的事务)选
项是相互排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使用

5.2.2 生产环境实战备份策略

InnoDB建议备份策略

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1
--flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

5.2.3 mysqldump 备份还原实战案例

5.2.3.1 实战案例:特定数据库的备份脚本

[root@centos8 ~]#cat mysql_backup.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=magedu
mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --masterdata=2 --default-character-set=utf8 -q  -B $DB | gzip >
${DIR}/${DB}_${TIME}.sql.gz

5.2.3.2 实战案例:分库备份并压缩

for db in `mysql -e 'show databases' | grep -Ev '^(Database|information_schema|performance_schema|sys|)$'`;do mysqldump -uroot -B ${db} |gzip > /backup/${db}_`date +%F_%H-%M-%S`.sql.gz;done

mysql -uroot -e 'show databases' | grep -Ev '^(information_schema|performance_schema|sys|Database)$'| while read db;do mysqldump -uroot -B ${db} | gzip > /backup/${db}.sql.gz;done

mysql -uroot -e 'show databases' | grep -Ev '^(information_schema|performance_schema|sys|Database)$' |sed -rn 's#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' | bash

mysql -uroot -e 'show databases'|sed -rn '/^(information_schema|performance_schema|sys|Database)$/!s#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p'|bash

5.2.3.3 实战案例:分库备份的实战脚本

[root@centos8 ~]#cat backup_db.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=magedu

[ -d "$DIR" ] || mkdir $DIR
for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ev
"^Database|.*schema$"`;do
 mysqldump -F --single-transaction --master-data=2 --default-characterset=utf8 -q -B $DB | gzip >  ${DIR}/${DB}_${TIME}.sql.gz
done

5.2.3.4 实战案例:完全备份和还原

#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
#备份
[root@centos8 ~]#mysqldump -uroot -pmagedu -A -F --single-transaction --masterdata=2 |gzip > /backup/all-`date +%F`.sql.gz
#还原
[root@centos8 backup]#dnf install mariadb-server
[root@centos8 backup]#gzip -d all-2019-11-27.sql.gz
[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2019-11-27.sql
MariaDB [(none)]> set sql_log_bin=on;

5.2.3.5 实战案例:利用二进制日志,还原数据库最新状态

#二进制日志独立存放
[mysqld]
log_bin=/mysql/logbin/mysql-bin

#完全备份,并记录备份的二进制位置
mysqldump -uroot -A --master-data=2 hellodb > /backup/hellodb`date +%F_%H-%M-%S`.sql

#修改数据库
insert teachers (name,age,gender)values('da','55','M');
insert teachers (name,age,gender)values('xiao','66','M');

#损坏数据库
rm -rf /var/lib/mysql/*

#还原
#CentOS 8 需要事先生成数据库相关文件,CentOS7 不需要执行此步
mysql_install_db  --user=mysql
systemctl restart mariadb


MariaDB [(none)]>set sql_log_bin=0;
MariaDB [(none)]> source /backup/hellodb2021-03-03_19-54-38.sql

#从完全备份中,找到二进制位置
[root@centos8 ~]#grep '^-- CHANGE MASTER TO' /backup/hellodb2021-03-03_19-54-38.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;

#二进制日志的备份
[root@centos8 mysql]#mysqlbinlog mysql-bin.000001 --start-position=328 >/backup/inc.sql

MariaDB [(none)]>set sql_log_bin=0;
MariaDB [(none)]>source /backup/inc.sql;
MariaDB [(none)]>set sql_log_bin=1;

5.2.3.6 实战案例:mysqldump 和二进制日志结合实现差异(增量)备份

完全备份后,假设某天需要在做备份,如何操作

#完全备份
[root@centos8 ~]# mysqldump -A --single-transaction --master-data=2 -F > /backup/all.sql

#假设周一要做备份,观察上面备份文件中记录的二进制文件和位置
[root@centos8 ~]# grep -i "\-\- CHANGE MASTER TO MASTER_LOG_FILE" /backup/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=155;

#由此发现,binlog.000003往后的是后续生成的二进制日志,用sed取出
[root@centos8 ~]# mysql -e 'show master logs;'|awk 'NR!=1{print $1}'| sed -n '/binlog.000003/,$p'
binlog.000003
binlog.000004
binlog.000005

#差异备份,拷贝走binlog.000003~05的文件
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup 

#导出03-05,实现差异备份
[root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000003 > /backup/inc1.sql
[root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000004 > /backup/inc2.sql
[root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000005 > /backup/inc3.sql


mysql> flush logs;
#导出06,实现增量备份
[root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000006 > /backup/inc4.sql





5.2.3.7 实战案例:恢复误删除的表

案例说明:每天2:30做完全备份,早上10:00误删除了表students,10:10才发现故障,现需要将数据库还原到10:10的状态,且恢复被删除的students表

#二进制日志独立存放
[mysqld]
log-bin=/data/mysql/mysql-bin

#完全备份
[root@centos8 ~]# mysqldump -uroot  -p -A -F --single-transaction --master-data=2  > /backup/hellodb-`date +%F_%H-%M-%S`.sql

#完全备份后数据更新
mysql> insert teachers (name,age,gender)value('a',20,'M');
mysql> insert teachers (name,age,gender)value('b',22,'M');

#10:00损坏数据库
mysql> drop database hellodb;

#10:10发现表删除,进行还原
1.停止数据库的网络服务
[root@centos8 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
skip-networking
[root@centos8 ~]# systemctl restart mysqld.service 

2.从完全备份中,找到二进制位置
[root@centos8 ~]#grep '\-\- CHANGE MASTER TO' /backup/hellodb-2021-03-01_22-58-27.sql;
-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=10751;

3.备份从完全备份后的二进制日志
[root@centos8 ~]# mysqlbinlog --start-position=10751 /var/lib/mysql/binlog.000001 > /backup/binlog.sql

4.删除误操作数据库的那一行语句
[root@centos8 ~]# vim /backup/binlog.sql 
drop database hellodb

#至此,所需文件找回
[root@centos8 ~]# ll /backup/
total 16
-rw-r--r-- 1 root root 5134 Mar  1 23:16 binlog.sql
-rw-r--r-- 1 root root 7988 Mar  1 22:58 hellodb-2021-03-01_22-58-27.sql		

#还原
1.暂停二进制日志
mysql> set sql_log_bin=0;
2.先还原完全备份
mysql> source /backup/hellodb-2021-03-01_22-58-27.sql;
3.再还原导出的命令
mysql> source /backup/binlog.sql;

#检查
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
mysql> use hellodb;
Database changed
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | a             |  20 | M      |
|   6 | b             |  22 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

5.3 xtrabackup 备份工具(了解)

5.3.1 xtrabackup 工具介绍

Percona 公司
官网:www.percona.com
percona-server
InnoDB --> XtraDB

Xtrabackup备份工具

percona提供的mysql数据库备份工具,惟一开源的能够对innodb和xtradb数据库进行热备的工具

手册:https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

下载:https://www.percona.com/downloads/

xtrabackup 特点:

  • 备份还原过程快速、可靠
  • 备份过程不会打断正在执行的事务
  • 能够基于压缩等功能节约磁盘空间和流量
  • 自动实现备份检验
  • 开源,免费

xtrabackup工具文件组成

Xtrabackup2.2 版之前包括4个可执行文件:

  • innobackupex: Perl 脚本
  • xtrabackup: C/C++,编译的二进制程序
  • xbcrypt: 加解密
  • xbstream: 支持并发写的流文件格式

说明:
xtrabackup 是用来备份 InnoDB 表的,不能备份非 InnoDB 表,和 MySQL Server 没有交互

innobackupex 脚本用来备份非 InnoDB 表,同时会调用 xtrabackup 命令来备份 InnoDB 表,还会和MySQL Server 发送命令进行交互,如加全局读锁(FTWRL)、获取位点(SHOW SLAVE STATUS)等。即innobackupex是在 xtrabackup 之上做了一层封装实现的

xtrabackup的新版变化
xtrabackup版本升级到2.4后,相比之前的2.1有了比较大的变化:innobackupex 功能全部集成到xtrabackup 里面,只有一个 binary程序,另外为了兼容考虑,innobackupex作为 xtrabackup 的软链接,即xtrabackup现在支持非Innodb表备份,并且 Innobackupex 在下一版本中移除,建议通过xtrabackup替换innobackupex

xtrabackup备份过程
在这里插入图片描述
备份生成的相关文件
使用innobackupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时,innobackupex还会在备份目录中创建如下文件:

  • xtrabackup_info:文本文件,innobackupex工具执行时的相关信息,包括版本,备份选项,备
    份时长,备份LSN(log sequence number日志序列号),BINLOG的位置
  • xtrabackup_checkpoints:文本文件,备份类型(如完全或增量)、备份状态(如是否已经为
    prepared状态)和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。
  • LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的
  • xtrabackup_binlog_info:文本文件,MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置,可利用实现基于binlog的恢复
  • backup-my.cnf:文本文件,备份命令用到的配置选项信息
  • xtrabackup_logfile:备份生成的二进制日志文件

5.3.2 xtrabackup 安装

在EPEL源中

yum install percona-xtrabackup

范例: CentOS 8没有提供

[root@centos7 ~]#yum info percona-xtrabackup
Available Packages
Name       : percona-xtrabackup
Arch       : x86_64
Version     : 2.3.6
Release     : 1.el7
Size       : 4.6 M
Repo       : epel/7/x86_64
Summary     : Online backup for InnoDB/XtraDB in MySQL, Percona Server and
MariaDB
URL         : http://www.percona.com/software/percona-xtrabackup/
License     : GPLv2
Description : Online backup for InnoDB/XtraDB in MySQL, MariaDB and Percona
Server.

范例: 最新版本下载安装:
https://www.percona.com/downloads/XtraBackup/LATEST/


5.3.3 xtrabackup 用法

xtrabackup工具备份和还原,需要三步实现

  1. 备份:对数据库做完全或增量备份
  2. 预准备: 还原前,先对备份的数据,整理至一个临时目录
  3. 还原:将整理好的数据,复制回数据库目录中

xtrabackup 选项参考:
https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html

备份:

innobackupex [option] BACKUP-ROOT-DIR

选项说明:

--user:#该选项表示备份账号
--password:#该选项表示备份的密码
--host:#该选项表示备份数据库的地址
--databases:#该选项接受的参数为数据库名,如果要指定多个数据库,彼此间需要以空格隔开;
如:"xtra_test dba_test",同时,在指定某数据库时,也可以只指定其中的某张表。
如:"mydatabase.mytable"。该选项对innodb引擎表无效,还是会备份所有innodb表
--defaults-file:#该选项指定从哪个文件读取MySQL配置,必须放在命令行第一个选项位置
--incremental:#该选项表示创建一个增量备份,需要指定--incremental-basedir
--incremental-basedir:#该选项指定为前一次全备份或增量备份的目录,与--incremental同时使用
--incremental-dir:#该选项表示还原时增量备份的目录
--include=name:#指定表名,格式:databasename.tablename

Prepare预准备:

innobackupex --apply-log [option] BACKUP-DIR

选项说明:

--apply-log:#一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚
未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。此选项作
用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory:#和--apply-log选项一起使用,当prepare 备份时,做crash recovery分配的内存
大小,单位字节,也可1MB,1M,1G,1GB等,推荐1G
--export:#表示开启可导出单独的表之后再导入其他Mysql中
--redo-only:#此选项在prepare base full backup,往其中合并增量备份时候使用,但不包括对最后一个增量备份的合并

还原:

innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR

选项说明:

--copy-back:#做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back:#这个选项与--copy-back相似,唯一的区别是它不拷贝文件,而是移动文件到目的地。这
个选项移除backup文件,用时候必须小心。使用场景:没有足够的磁盘空间同事保留数据文件和Backup副本
--force-non-empty-directories #指定该参数时候,使得innobackupex --copy-back或--moveback选项转移文件到非空目录,已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从
备份目录拷贝一个在datadir已经存在的文件,会报错失败

还原注意事项:

  1. datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定,否则-
    -copy-back选项不会覆盖
  2. 在restore之前,必须shutdown MySQL实例,不能将一个运行中的实例restore到datadir目录中
  3. 由于文件属性会被保留,大部分情况下需要在启动实例之前将文件的属主改为mysql,这些文件将属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用innobackupex之前完成

5.3.4 实战案例:利用 xtrabackup 实现完全备份及还原

注意:目前percona-xtrabackup-24-2.4.18-1.el8.x86_64.rpm不支持CentOS 8上的mariadb-10.3版本

案例: 利用xtrabackup8.0 完全备份和还原MySQL8.0
方式同下面案例

[root@centos8 ~]#ll percona-xtrabackup-80-8.0.22-15.1.el8.x86_64.rpm
-rw-r--r-- 1 root root 13563076 Feb 5 18:41 percona-xtrabacku

案例:新版 xtrabackup完全备份及还原
本案例基于CentOS 8 的 MySQL5.7 实现,也支持MySQL5.5和Mariadb5.5

#安装mariadb5.5版本,启动服务,导入测试数据;主机和目标主机都需要安装
[root@centos7 ~]#yum -y  install mariadb-server
[root@centos7 ~]#systemctl enable --now mariadb-service
[root@centos7 ~]#mysql < hellodb_innodb.sql

1 安装xtrabackup包
[root@centos7 ~]#yum -y install percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm 

2 在原主机做完全备份到/backup
[root@centos7 ~]#mkdir /backup
[root@centos7 ~]#xtrabackup -uroot --backup --target-dir=/backup/base

#目标主机无需创建/backup目录,直接复制目录本身
[root@centos7 ~]#scp -r /backup/   目标主机:/
[root@centos7 ~]#scp -r /backup/ 10.0.0.105:/

3 在目标主机上还原
1)预准备:确保数据一致,提交完成的事务,回滚未完成的事务
[root@centos7 ~]#xtrabackup --prepare --target-dir=/backup/base
2)复制到数据库目录
注意:数据库目录必须为空,MySQL服务不能启动
[root@centos7 ~]#xtrabackup --copy-back --target-dir=/backup/base
3)还原属性
[root@centos7 ~]#chown -R mysql:mysql /var/lib/mysql
4)启动服务
[root@centos7 ~]#service mysqld start

5.3.5 实战案例:利用xtrabackup完全,增量备份及还原

案例:新版xtrabackup完全,增量备份及还原

1 备份过程
1)完全备份:
[root@centos8 ~]#mkdir /backup/
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base
2)第一次修改数据
3)第一次增量备份
[root@centos8 ~]#xtrabackup -uroot  --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

4)第二次修改数据
5)第二次增量
[root@centos8 ~]#xtrabackup -uroot  --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

6)[root@centos8 ~]#scp -r /backup/* 目标主机:/
#备份过程生成三个备份目录
/backup/{base,inc1,inc2}

2还原过程
1)预准备完成备份,此选项--apply-log-only 阻止回滚未完成的事务
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base

2)合并第1次增量备份到完全备份
[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base--incremental-dir=/backup/inc1

3)合并第2次增量备份到完全备份:最后一次还原不需要加选项--apply-log-only
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base --incrementaldir=/backup/inc2

4)复制到数据库目录,注意数据库目录必须为空,MySQL服务不能启动
[root@centos7 ~]#rm -rf /var/lib/mysql/*
[root@centos7 ~]#xtrabackup --copy-back --target-dir=/backup/base

5)还原属性:
[root@centos7 ~]#chown -R mysql:mysql /var/lib/mysql

6)启动服务:
[root@centos7 ~]#systemctl enable --now mariadb-server
  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页
评论 1

打赏作者

低調!

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值