01-MySQL数据库

MySQL数据库

MySQL数据库

一、MySQL的安装

方式一：yum安装
方式二：rpm包安装（需要解决依赖）
方式三：源码安装（安装较慢，安装时间长）
方式四：二进制安装（企业常用安装方式，不挑系统）

1.下载MySQL软件包

官网：https://www.mysql.com/
下载界面：https://www.mysql.com/downloads/
- 分为专业版；集群版；社区版（免费）MySQL Community Server
官方文档：https://dev.mysql.com/doc/
使用版本：mysql-8.0.36-linux-glibc2.12-x86_64
下载地址：https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz

2.系统环境准备

操作系统：Centos7.9(3.10.0-1160.119.1.el7.x86_64)
关闭系统安全服务

[root@db01 ~]# systemctl stop firewalld 
[root@db01 ~]# systemctl disable firewalld
[root@db01 ~]# setenforce 0
[root@db01 ~]# sed -i '7s#enforcing#disabled#g' /etc/selinux/config

[root@db01 ~]# useradd mysql -M -s /sbin/nologin
[root@db01 ~]# id mysql
uid=1000(mysql) gid=1000(mysql) groups=1000(mysql)

[root@db01 ~]# mkdir -p /data/3306/data
[root@db01 ~]# chown mysql.mysql /data/3306/data
[root@db01 ~]# ll -d  /data/3306/data/
drwxr-xr-x 7 mysql mysql 4096 Feb 20 02:57 /data/3306/data/

卸载系统中默认数据库程序（根据自身系统情况调整）

[root@db01 ~]# rpm -qa|grep mysql
[root@db01 ~]# yum remove -y `rpm -qa|grep mysql`

安装软件所需的程序依赖

[root@db01 ~]# yum install -y libaio-devel

3.数据库程序包安装部署（二进制包）

[root@db01 ~]# cd /usr/local/
[root@db01 ~]# ll mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz 
-rw-r--r-- 1 root root 618056612 Feb 20 10:32 mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz

[root@db01 ~]# tar xf mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz
[root@db01 ~]# ln -s mysql-8.0.36-linux-glibc2.12-x86_64 mysql    
bin/mysql程序命令-mysql 登录数据库 
[root@db01 ~]# vim /etc/profile 
export PATH="$PATH:/usr/local/mysql/bin"
[root@db01 ~]# source /etc/profile
[root@db01 ~]# mysql -V

4.初始化数据库

#确保/data/3306/data 为空（不为空初始化会失败）
[root@db01 ~]# mysqld --initialize-insecure  --user=mysql  --datadir=/data/3306/data  --basedir=/usr/local/mysql
--initialize-insecure  #可以实现不安全初始化  数据库没有默认密码设置（免密登录）
--initialize           #可以实现安全初始化    数据库会有默认密码设置 后续也需要重新设置
--user=mysql           #指定创建数据库默认数据属主和属组 以及指定数据库进程管理用户信息
--datadir              #指定数据库存储和加载数据目录
--basedir              #指定数据库二进制程序目录

5.编写数据库服务配置文件

作用：

可以设置数据库服务功能并使功能永久生效

可以让数据库服务进程加载

[root@db01 ~]# vim /etc/my.cnf
[mysqld] 
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock

配置文件详解

[mysqld]     --配置标签信息（服务端配置标签 客户端配置标签） 
user=mysql   --数据库进程用户信息
basedir=/usr/local/mysql   -- 加载程序目录  Linux系统 mysql5.6  mysql5.7 mysql8.0 （多实例）   
datadir=/data/3306/data    -- 加载数据目录
socket=/tmp/mysql.sock     -- 配置连接数据库的socket（客户端命令可以连接访问服务端）

6.启动运行数据库服务

①启动方式

方式一：使用源码包中的脚本文件启动停止

[root@db01 ~]# /usr/local/mysql/support-files/mysql.server 
Usage: mysql.server  {start|stop|restart|reload|force-reload|status}  [ MySQL server options ]

#为了方便使用将脚本文件复制到/etc/init.d/中
[root@db01 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

#使用方式
[root@db01 ~]# /etc/init.d/mysqld start|stop|restart|reload|force-reload|status

方式二：service命令启动数据库服务

service mysqld start
service mysqld stop

方式三：systemctl命令启动数据库服务

systemctl enable mysqld
systemctl start mysqld
systemctl stop mysqld

方式四：编写数据库服务service文件

[root@db01 ~]# cat /usr/lib/systemd/system/mysql.service
[Unit]
Description=Mysql Server
After=network.target remote-fs.target nss-lookup.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecRestart=/usr/local/mysql/support-files/mysql.server restart
[Install]
WantedBy=multi-user.target
[root@db01 ~]# systemctl daemon-reload
[root@db01 ~]# systemctl start mysql.service

②启动日志

cat /data/3306/data/db01.err

二、数据库服务密码管理

1.设置初始密码

注意：如果使用--initialize（安全初始化）会生成一个随机密码

①使用shell命令设置

[root@db01 ~]# mysqladmin -uroot password '密码'

②使用SQL语句设置

[root@db01 ~]# mysql
mysql> alter user root@'localhost' identified by '密码';

2.修改密码信息

①使用shell命令设置

[root@db01 ~]# mysqladmin -uroot -p321321 password '456456'

②使用SQL语句设置

[root@db01 ~]# mysql
mysql> alter user root@'localhost' identified by '密码';

3.重置密码信息(遗忘密码)

①关闭数据库服务

[root@db01 ~]# systemctl stop mysql.service

②采用安全模式启动数据库（免密登录）

[root@db01 ~]# mysqld --skip-grant-tables --skip-networking  &
--skip-grant-tables   启动数据库不会加载授权表
--skip-networking     启动数据库只会创建进程信息，不会生成网络端口信息

③重置密码信息

[root@db01 ~]# mysql
mysql> flush privileges;	-- 重新将磁盘授权表加载到内存中
mysql> alter user root@'localhost' identified by '密码';

④重新正常启动数据库服务

[root@db01 ~]# pkill mysql
[root@db01 ~]# /etc/init.d/mysqld start
[root@db01 ~]# mysql -uroot -p密码

三、数据库服务用户管理

1.创建用户信息

①设置用户主机域/白名单信息方式

语法格式

用户名@'作用域'

作用域格式

##本地访问
localhost		#仅本地可访问
127.0.0.1		#仅本地可访问(效果与localhost相同)

##远程访问
主机名			  #db主机要能解析到
IP地址		   #允许单个IP地址访问
10.0.0.%		
10.0.%	10.%	#允许此网段内访问
%				#允许任意客户端主机连接访问

②创建用户信息

语法格式

create user 用户名@'作用域';						#免密登录
create user 用户名@'作用域' identified by '密码'   #设置密码

③用户登录

本地用户
mysql -uroot -p密码
远程用户
mysql -uroot -p密码 -h数据库服务端地址  -P数据库服务端口

2.查看用户信息

语法格式

用户信息存储在mysql.user表中，我们只查看用户名，作用域，密码
#查看所有用户信息
mysql> select user,host,authentication_string from mysql.user;

#查看当前登录用户信息
mysql> select user();

3.删除用户信息

语法格式

mysql> drop user 用户名@'作用域'

#下方操作慎用，操作不当会造成表内容全部丢失
delete from mysql.user where user='用户名' and host=‘作用域’;

四、数据库服务权限管理

1.查看所有权限

mysql> show privileges;

Delete           Tables						-- 可以删除表中数据  	
Insert           Tables						-- 可以插入数据权限  	
Select           Tables						-- 可以查询表数据信息	
Update           Tables						-- 可以修改表数据信息 	
--------------------------------------------------------
Alter            Tables                  	-- 修改表的属性信息 
Create           Databases,Tables,Indexes	-- 创建数据库和数据表权限
Create user      Server Admin 				-- 可以创建新用户
Drop             Databases,Tables			-- 可以删除库，可以删除表
Grant option     Databases,Tables			-- 可以给别人进行授权的权限
Show databases   Server Admin				-- 可以查看所有数据库信息
Usage            Server Admin				-- 只能登录数据库权限

2.设置用户权限

语法格式

grant 权限01,权限02,权限03..  on 操作对象 to 用户名@'白名单'

操作对象

*.*       	 -- 可以管理所有库，以及库中的所有表
库名.*    	-- 可以管理指定库，以及库中的所有表 
库名.表名      -- 可以管理指定库，以及库中的指定表

针对不同用户设置权限

##针对管理员设置权限：DBA 
grant all on *.* to admin@'localhost';
##针对运维人员设置权限：SA 
grant Alter,Create,Delete,Drop,Insert,Select,Update on www.* to ops@'localhost';
##针对开发人员设置权限：DEV
grant Delete,Insert,Select,Update on www.t1 to dev@'localhost';

版本命令彩蛋

mysql 8.0之前版本：可以利用授权命令进行授权 并创建用户
grant select on *.* name@'localhost' identified by '123456';
mysql 8.0之后版本：授权和用户创建要分开执行 
create user ...
grant 权限 ...

3.查看用户权限

show grants for 用户名@'作用域'

4.撤销用户权限

revoke DROP on 操作对象 from 用户名@'作用域';
revoke DROP on `www`.* from `dev`@`localhost`;

5.用户权限授权表

都存放于mysql库中

①全局授权信息

user表			#用户对所有库所有表的权限信息  全局权限设置

②数据库授权信息

db表				#用户对指定库所有表的权限信息  针对数据库权限设置

③数据表授权信息

tables_priv表	#用户对指定库指定表的权限信息  针对数据表权限设置

五、数据库连接管理

1.数据库连接方法

①本地连接

利用套接字文件（利用本地socket文件）客户端加载socket文件 =（路径/名称）= 服务端创建socket文件
当客户端加载的sock文件和服务端创建sock文件不匹配，应
- 修改服务端配置信息
- 调整客户端连接方式（加载的sock信息）
```
mysql -uroot -p123123 -S /data/3306/data/mysql.sock
```

②远程连接

利用TCP/IP网络协议

mysql -uroot -p012012 -h 数据库服务端地址  -P 数据服务端端口

2.数据库连接方式

①利用客户端命令进行连接

缺点：命令参数不便于记忆连接登录后的语句操作没有补全功能
使用mysql命令登录

本地登录： mysql -u用户  -p密码   -S指定套接字文件
远程登录： mysql -u用户  -p密码	 -h数据库服务端地址  -P数据库服务端端口

使用mysqladmin登录更改密码

本地登录：mysqladmin -u用户  -p密码   -S指定套接字文件  password '新密码'
远程登录：mysqladmin -u用户  -p密码	 -h数据库服务端地址  -P数据库服务端端口  password '新密码'

使用mysqldump登录保存、备份数据

本地备份 ：mysqldump -u用户  -p密码   -S指定套接字文件 备份参数信息
远程备份 ：mysqldump -u用户  -p密码 -h 数据库服务端地址  -P 数据服务端端口 备份参数信息

②利用客户端工具进行连接

MySQL官方出品远程工具：MySQL workbench 或者其他的连接工具
下载地址：https://dev.mysql.com/downloads/workbench/
连接方式：

1)创建远程用户信息
mysql> create user root@'10.0.0.%' identified by '123456';

#直接授权用户所有权限，默认不会给用户创建用户的权限，需要加with grant option
mysql> grant all on *.* to root@'10.0.0.%' with grant option;

③利用程序代码连接数据库

python    连接数据库驱动-pymysql 
golang    连接数据库驱动-gomysql
java      连接数据库驱动-jar 
php       连接数据库驱动-phpmysql

六、数据库服务配置管理

1.加载数据库配置文件

默认路径信息：

/etc/my.cnf 
/etc/mysql/my.cnf 
/usr/local/mysql/etc/my.cnf 
~/.my.cnf

自定义路径信息：

# 以放在/data/mysql01.conf为例  使用--defaults-file指定加载配置文件
mysqld --defaults-file=/data/mysql01.conf &

这条命令的作用是：
启动 MySQL 数据库服务（mysqld）。
使用 /data/mysql01.conf 文件作为配置文件（而不是默认的配置文件）。
将 mysqld 进程放到后台运行。

2.进程信息

[root@db01 ~]# ps -ef  | grep mysqld
root       5231      1  0 Feb21 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/3306/data --pid-file=/data/3306/data/db01.pid


mysql      5372   5231  0 Feb21 ?        00:03:47 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=db01.err --pid-file=/data/3306/data/db01.pid --socket=/tmp/mysql.sock

主进程：管理进程管理子进程状态

root       5231      1  0 Feb21 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/3306/data --pid-file=/data/3306/data/db01.pid

子进程：工作进程处理客户端请求（SQL语句请求）

mysql      5372   5231  0 Feb21 ?        00:03:47 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=db01.err --pid-file=/data/3306/data/db01.pid --socket=/tmp/mysql.sock

3.编写数据库配置文件

配置文件中主要通过设置标签来定义配置，标签用[]定义
标签信息的类型
- 客户端标签
  - [client](全局定义)
  - [mysql][mysqladmin][mysqldump](局部定义)
- 服务端标签
  - [server](全局定义)
  - [mysqld][mysqld_safe] (局部定义)
标签中的配置项
- 使用 便签名 --help 获取标签选项信息

七、数据库服务实例应用

1.功能作用

提高数据库高可用能力
可以提高数据库安全性
便于对数据库进行监控
可以减少企业运营成本

2.同版本构建多实例

多实例规划（三个）

主机IP	端口	数据库目录	配置文件	套接字文件
10.0.0.51	3306	/data/3306/data	/data/3306/my3306.cnf	/tmp/mysql3306.sock
10.0.0.51	3307	/data/3307/data	/data/3307/my3307.cnf	/tmp/mysql3307.sock
10.0.0.51	3308	/data/3308/data	/data/3308/my3308.cnf	/tmp/mysql3308.sock

①进行多实例数据库目录初始化

#创建数据库目录
[root@db01 ~]# mkdir -p /data/3306/data
[root@db01 ~]# mkdir -p /data/3307/data
[root@db01 ~]# mkdir -p /data/3308/data
[root@db01 ~]# chown -R  mysql.mysql /data/

#进行服务初始化
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data --basedir=/usr/local/mysql 
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/usr/local/mysql

②写多实例配置文件

#3306实例
[root@db01 ~]# vim /data/3306/my3306.cnf
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql3306.sock

#3307实例
[root@db01 ~]# vim /data/3307/my3307.cnf
[mysqld] 
port=3307
user=mysql
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock

#3308实例
[root@db01 ~]# vim /data/3308/my3308.cnf
[mysqld] 
port=3308
user=mysql
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock

③运行启动多实例服务

[root@db01 ~]# mysqld --defaults-file=/data/3306/my3306.cnf &
[root@db01 ~]# mysqld --defaults-file=/data/3307/my3307.cnf &
[root@db01 ~]# mysqld --defaults-file=/data/3308/my3308.cnf &

④查看端口号，多实例是否启动

[root@db01 ~]# netstat  -tnulp | grep mysql
tcp6       0      0 :::3306      :::*      LISTEN      31871/mysqld        
tcp6       0      0 :::3307      :::*      LISTEN      31462/mysqld        
tcp6       0      0 :::3308      :::*      LISTEN      31526/mysqld

⑤指定套接字文件尝试登录

[root@db01 ~]# mysql -S/tmp/mysql3306.sock
[root@db01 ~]# mysql -S/tmp/mysql3307.sock 
[root@db01 ~]# mysql -S/tmp/mysql3308.sock

3.不同版本构建多实例

①下载、安装、初始化

#下载
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz

[root@db-01 local 08:40]# ll mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz 
-rw-r--r-- 1 root root 343082156 Oct 27  2022 mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz
[root@db-01 local 08:40]# ll mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz 
-rw-r--r-- 1 root root 660017902 Oct 27  2022 mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
#安装
tar xf mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz
tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

ln -s mysql-5.6.48-linux-glibc2.12-x86_64 mysql56 
ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql57 

#初始化
mkdir -p /data/3356/data && chown mysql.mysql /data/3356/data
mkdir -p /data/3357/data && chown mysql.mysql /data/3357/data

/usr/local/mysql56/scripts/mysql_install_db --user=mysql --datadir=/data/3356/data --basedir=/usr/local/mysql56   
/usr/local/mysql57/bin/mysqld --initialize-insecure --user=mysql --datadir=/data/3357/data --basedir=/usr/local/mysql57

②编写数据库服务配置文件

cat >/data/3356/my.cnf<<EOF
[mysqld]
port=3356
user=mysql 
basedir=/usr/local/mysql56
datadir=/data/3356/data
socket=/tmp/mysql3356.sock 
EOF 

cat >/data/3357/my.cnf<<EOF
[mysqld]
port=3357
user=mysql 
basedir=/usr/local/mysql57
datadir=/data/3357/data
socket=/tmp/mysql3357.sock 
EOF

③启动运行不同版本多实例

cat>/etc/systemd/system/mysql56.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql56/bin/mysqld --defaults-file=/data/3356/my.cnf
LimitNOFILE=5000
EOF

cat>/etc/systemd/system/mysql57.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/3357/my.cnf
LimitNOFILE=5000
EOF

systemctl daemon-reload 
systemctl start mysql56
systemctl start mysql57

八、数据库服务SQL语句分类

**数据库主要使用 SQL语句（Structured Query Language）具有循环语句判断语句功能 -- 数据库存储过程（数据库中的脚本） **

1.DDL语句(定义)

数据定义语句 Data Definition Language

create：创建数据库 创建数据表 创建索引信息 
alter ：修改数据库属性信息（字符集 校对规则）  修改数据表属性信息（字符集 校对规则 表结构-列名 数据类型 约束属性 索引 引擎？ 名称）
drop  ：删除数据库 删除数据表（磁盘层面删除数据） 慎用
show  ：做以上操作的查看确认

1)数据库管理操作语句CADU

①创建库

CREATE DATABASE db_name;  
CREATE SCHEMA db_name;

#设置字符集和校对规则
CREATE DATABASE db_name CHARACTER SET charset_name COLLATE collation_name;

②修改库

alter  DATABASE db_name CHARACTER SET charset_name COLLATE collation_name;

③删除库

drop database db_name;  
注意：逻辑上是把数据库信息删除了 | 物理上是包数据库目录和数据表文件从磁盘上删除了   慎用

④切换库

use db_name;
use db_name.tb_name;

2)数据表管理操作语句

①创建表

create table 表名 (
    字段名 数据类型 约束 属性,
    字段名 数据类型 约束 属性,索引设置
) 引擎设置 字符集 校对规则

e.g:
CREATE TABLE student (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL,
  mobile num CHAR(11) NOT NULL DEFAULT '未知',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `mobile num_UNIQUE` (`mobile num` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

②修改表

1）修改表名
ALTER TABLE oldname RENAME TO  newname;
rename table newname to oldname;

2）修改字符集/校对规则 
ALTER TABLE tb_name  CHARACTER SET gbk , COLLATE gbk_chinese_ci ;

3）修改表字段信息
添加新的字段：
#添加的列在表中所有字段最后面  默认添加到最后边
ALTER TABLE table_name ADD COLUMN 字段名 数据类型 约束 属性;
#添加的列在表中指定列后面   after
ALTER TABLE table_name ADD COLUMN 字段名 数据类型 约束 属性 AFTER `字段名`;  
#添加的列在表中首行   first
ALTER TABLE table_name ADD COLUMN 字段名 数据类型 约束 属性 first;

③修改原有字段

1）修改字段名称
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name 约束属性信息;
注意：修改字段名称也要添加其原有的约束属性信息

2）修改字段数据类型 属性 约束 
ALTER TABLE table_name CHANGE COLUMN column_name  约束属性信息;
e.g:
ALTER TABLE student CHANGE COLUMN age CHAR(3) NULL DEFAULT '20' ,ADD UNIQUE INDEX `age_UNIQUE` (`age` ASC) VISIBLE;

3）删除已有字段：
ALTER TABLE table_name DROP COLUMN column_name;

④删除表

1)表和数据都删除		---将磁盘中的表文件删除，删除后会释放磁盘空间
drop table 表名;

2)只删除表中数据		---清空数据表
truncate 表名;		效率快；可以释放磁盘空间；会清空自增序列	（常用）
delete from 表名;		效率慢；无法释放磁盘空间（标记删除）；不会重置自增序列

⑤查看表的结构和属性

#获取创建表语句 从而了解表的结构 表的引擎和字符集/校对规则 还有注释信息		更详细
show create table table_name;

#获取表结构 数据类型 约束属性设置
desc table_name;

#查看表的详细索引设置
show index from table_name;

3)数据库的别名设置/视图设置

①表别名设置

语法格式

SELECT a.id, a.name, b.order_date
FROM customers AS a					#将customers表设置别名为a
JOIN orders AS b					#将orders表设置别名为 b	这两处的AS均可省略
ON a.id = b.customer_id;

示例

select a.tno,a.tname,b.cname,group_concat(d.sno)from teacher as a 
   join course b 
   on a.tno=b.tno 
   join sc c 
   on b.cno=c.cno 
   join student d 
   on c.sno=d.sno 
   where c.score<60
   group by a.tno,b.cname

②列别名设置

语法格式

SELECT customer_name AS 'Customer Name'
FROM customers;

示例

select a.tno as '讲师编号',a.tname as '讲师名字',b.cname as '课程名称',group_concat(d.sno) as '学生学号',group_concat(d.sname) as '学生姓名' from teacher as a 
   join course b 
   on a.tno=b.tno 
   join sc c 
   on b.cno=c.cno 
   join student d 
   on c.sno=d.sno 
   where c.score<60
   group by a.tno,b.cname;

③视图设置

语法设置

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

示例

   create view stunde_fail as select a.tno as '讲师编号',a.tname as '讲师名字',b.cname as '课程名称',group_concat(d.sno) as '学生学号',group_concat(d.sname) as '学生姓名' from teacher as a 
   join course b 
   on a.tno=b.tno 
   join sc c 
   on b.cno=c.cno 
   join student d 
   on c.sno=d.sno 
   where c.score<60
   group by a.tno,b.cname;
   
   select * from stunde_fail;

2.DCL语句(控制)(三、四)

数据控制语句 Data Control Language

grant： 授权权限信息
revoke：回收权限信息
create user：创建用户  
alter user： 修改用户
commit：  操作提交语句  -- 数据库事务知识
rollback：操作回滚语句  -- 数据库事务知识

3.DML语句(操作)

数据操作语句 Data Manipulation Language

1)数据表相关数据管理

运维人员：插入数据修改数据删除数据查询数据
开发人员：CRUD(create=创建数据-注册 read=读取数据-登录 update=修改数据-信息调整 delete=删除数据-订单信息删除)

2)插入数据 insert

①标准插入方式

insert into 表名 (列名01,列名02...) values (值01,值02,....)

②简单插入方式

#若表中具有自增列 默认值的列 可以为空的列 都可以忽略插入
insert into 表名 (列名01,列名02...) values (值01,值02,....)
#假设第一列为自增列
insert into city values (0,值01,值02,....);

③批量插入方式

insert into city values (0,值01,值02,....),(0,值01,值02,....);

3)修改数据 update

update 表名 set 列名01=值01,列名02=值02 where 条件表达式
PS：如果update不加where进行修改，表示对全表所有行数据进行修改
e.g:	update city set name='ding',district='sex' where id=4085;

4)删除数据 delete

delete from city where 条件表达式
PS：如果delete不加where进行删除，表示对全表所有行数据进行删除

4.DQL语句(查询)

数据查询语句 Data Query Language

查看表中的数据  select 
查看单表数据信息 select + from + where + group by + having + order by + limit  
查看多表数据信息 select + join on + union all 
其他信息查看
查看数据库变量信息（内置变量-状态变量 功能变量）
查看数据库函数信息（获取特定数据）
https://dev.mysql.com/doc/refman/8.4/en/indexes.html

1)单表查询数据

①全表数据查询

select * from 表名;        -- 整个表数据都加载,需要对磁盘性能消耗更多
select 列名1，列名2 from 表名 -- 指定列信息全部显示

②条件查询数据 where

select */列名称 from 表名 where 条件表达式

以官网给出的测试库为例，创建测试数据：https://dev.mysql.com/doc/index-other.html

mysql -uroot -p123123  <./world.sql
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |    id主键列 序号
| Name        | char(35) | NO   |     |         |                |    城市信息 
| CountryCode | char(3)  | NO   | MUL |         |                |    国家编码 
| District    | char(20) | NO   |     |         |                |    省份信息
| Population  | int      | NO   |     | 0       |                |    人口数量 
+-------------+----------+------+-----+---------+----------------+

条件表达式写法

等值查询数据

select * from 表名 where 列名='字段';
#查询中国的所有城市信息
select * from city where CountryCode='CHN';

范围查询数据

select * from 表名 where 列名<num;
#范围的表示方式：< > <= >=   <> | !=不等于
#查询全世界所有城市信息，人口数量小于10000;
select * from city where Population<10000;

多条件查询数据(and / or)

and：根据第一个条件先进行筛选过滤，然后将筛选后的结果在交给第二条件进行筛选过滤
or： 根据第一个条件先进行筛选过滤，然后在经过第二个条件重新筛选过滤，将两个条件筛选后的数据做合并
#查询中国的所有城市信息，并且人口数量小于100000;
select * from city where CountryCode='CHN' and Population<100000
#查询美国和中国所有城市信息
select * from city where CountryCode='CHN' or CountryCode='USA';
#查询中国所有城市信息，并且人口数量小于100000，再查询美国所有城市信息，并且人口数量大于1000000；
select * from city where CountryCode='CHN' and Population<100000 or CountryCode='USA' and Population>1000000;

使用in / not in /between and 代替多条件查询

select * from 表名 where 列名 in (字段1，字段2)；
select * from 表名 where 列名 not in (字段1，字段2)；
select * from 表名 where 列名 between num1 and num2;
e.g.
#查询美国和中国所有城市信息
select * from city where CountryCode in ('CHN','USA');
select * from city where CountryCode not in ('CHN','USA');
#查询全世界的所有城市信息，并且人口数量小于100000，但是大于10000;
select * from city where population between 10000 and 100000;

③特殊方式查询 like

匹配查询

select * from 表名 where 列名 like  '字符串%'  #查找此列中以字符串开头的行

去重查询

select count(Distinct 列名) from 表名 where 列名='字段';
#Distinct为去重函数，count为统计个数的函数

查看空字段信息

select * from 表名 where 列名 is null;   
select * from 表名 where 列名 is not null;

④分组查询数据 group by

实现分组查询的步骤：

指定分组查询的列
将分组列相同信息做排序
将排序后相同行信息进行合并

示例：

#查询统计每个国家的人口总数  
select CountryCode,sum(population) from city group by CountryCode;
#查询中国每个省份的城市数量 
select District,count(name) from city where CountryCode='CHN' group by District;
#查询中国每个省份的城市数量,并且显示对应城市信息
select District,count(name),group_concat(name) from city where CountryCode='CHN' group by District;

扩展说明：

1）聚合函数
count()   -- 做计数统计函数（统计行数）
sum()     -- 对数值列进行求和
max()     -- 取出指定数值列最大值
min()     -- 取出指定数值列最小值
avg()     -- 取出指定数值列平均值 sum()/count()
group_concat -- 将多行信息整合为一行显示
2）sql_mode配置项 
保证数据库录入数据和显示数据的合理性

⑤聚合过滤查询 having

where : 根据条件信息过滤数据指定过滤数据表中有的数据不识别聚合函数
having : 根据条件信息过滤数据过滤聚合处理的数据信息，在表中并不存在支持识别聚合函数

示例：

#查询中国每个省份城市数量，并且将城市数量大于10的省份显示
select District,count(name) from city where CountryCode='CHN'  group by District having count(name)>10;
#查询全世界各个国家人口数量，并且将人口数量大于500w国家显示输出
select countrycode,sum(population) from city group by countrycode  having sum(population)>5000000;

⑥排序查询数据 order by

desc（descending order） -- 降序排序
asc （ascending order） -- 升序排序默认

示例：

#查询全世界各个国家人口数量，并且将人口数量大于500w国家显示输出，将国家人口数量进行排序显示
select countrycode,sum(population) from city group by countrycode  having sum(population)>5000000 order by sum(population);
-- 默认order by采用升序排序
select countrycode,sum(population) from city group by countrycode  having sum(population)>5000000 order by sum(population) desc;
-- 可以利用desc实现降序排序

⑦截取数据信息 limit

语法格式

limit n;		   --- 取前n行
limit n,m;         --- n表示从第几行后开始截取，m表示截取几行信息
limit n offset m;  --- n表示截取几行信息，m表示从第几行开始截取

示例：

#查询全世界各个国家人口数量，并且将人口数量大于500w国家显示输出，将国家人口数量进行排序显示，显示人口数量最多的前5名国家信息  
select countrycode,sum(population) from city group by countrycode  having sum(population)>5000000 order by sum(population) desc limit 5;

2)多表查询数据（连表查询数据）

创建测试数据

创建测试数据表
create database school;

CREATE TABLE student (
    sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
    sname VARCHAR(20) NOT NULL COMMENT '姓名',
    sage TINYINT UNSIGNED NOT NULL COMMENT '年龄',
    ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course (
    cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
    cname VARCHAR(20) NOT NULL COMMENT '课程名字',
    tno INT NOT NULL COMMENT '教师编号'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE sc (
    sno INT NOT NULL COMMENT '学号',
    cno INT NOT NULL COMMENT '课程编号',
    score INT NOT NULL DEFAULT 0 COMMENT '成绩'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher (
    tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
    tname VARCHAR(20) NOT NULL COMMENT '教师名字'
) ENGINE=INNODB CHARSET=utf8;

# 在数据库与数据表中插入模拟数据
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(1,'zhang3',18,'m'),
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f'),
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f'),
(8,'dezyan',20,'m'),
(9,'oldgirl',20,'f'),
(10,'oldp',25,'m');

INSERT INTO teacher(tno,tname)
VALUES
(101,'dezyan'),
(102,'xiaoQ'),
(103,'xiaoA'),
(104,'xiaoB');

INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103),
(1004,'go',105);

INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

①笛卡尔乘积方式进行多表连接

#直接将两表拼接，没有逻辑性关系性可言
mysql> select * from teacher,course;

②内连接方式，实现多表拼接

#当两表的tno列相同，拼接成一个表
select * from teacher,course where teacher.tno=course.tno;
#使用inner join   on	拼接多个数据表更简单
select * from teacher inner join course on teacher.tno=course.tno;
--多个数据使用可使用多次join …… on

③外连接方式，实现多表拼接（索引应用有关）

定义驱动表（主表），在定义被驱动表（子表）

左外连接：左边为驱动表，右边为被驱动

右外连接：右边为驱动表，左边为被驱动
驱动表所有数据信息都要显示，被驱动和驱动有关联数据要显示，无关联数据用null填充
左外连接

左侧表的tno有值的一列全部显示
select * from teacher left join course on teacher.tno=course.tno;

右外连接

select * from teacher right join course on teacher.tno=course.tno;

④多表拼接的两种方式

**方式一：横向拼接当调取数据出自多张表 ** 使用join
方式二：纵向拼接可以将多个相同数据表做合并 使用union

一个大表生成时，会将大表进行拆分（可以增加并发量 可以减少磁盘IO消耗）
student_2024 + student_2025  纵向 
select * from student union select * from student02;         -- 拼接完信息，若有重复情况 会取重显示
select * from student union all select * from student02;     -- 拼接完信息, 所有行信息都会显示，包含重复信息

3)特殊信息查询

①变量查询

分类：

状态变量：不能进行改动配置，只能用于查看（可以配合监控平台监控数据运行情况）

系统变量：可以进行改动配置，会影响数据功能和性能

查看方式

show variables                       	-- 查看所有变量
show variables like '匹配变量字段'  	-- 过滤查看指定变量
select @@sql_mode;

设置方式：

临时设置：
set global    变量名=变量值 （字符串加引号 数值不用加引号）  -- 全局设置 （变量设置 会影响所有用户 只有重启数据服务才会还原）
set [session] 变量名=变量值 （字符串加引号 数值不用加引号）  -- 会话设置 （变量设置 会影响当前用户 当会话断开变量设置还原）
永久设置：
在配置文件中添加

②函数查询

查看方式

select 函数();

③数据库中的元数据信息

元数据信息==数据库属性信息

select * from  information_schema.表名\G
  TABLE_CATALOG: def
   TABLE_SCHEMA: school         -- 库名
     TABLE_NAME: student02      -- 表名
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB         
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 4              -- 行数
 AVG_ROW_LENGTH: 4096           -- 每行占用磁盘字节量（平均）
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0              -- 索引结构占用磁盘情况
      DATA_FREE: 0              -- 数据存储碎片情况
 AUTO_INCREMENT: 5
    CREATE_TIME: 2025-02-25 11:01:43
    UPDATE_TIME: 2025-02-25 11:08:39
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb3_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT:

4)统计数据库资产信息（数据资产）（面试）

①获取每个库中表的个数和名称信息

#统计有几个数据库服务器;统计数据库实例数量;统计每个实例中有多少数据库 show databases;  统计每个数据库中表信息和数量
 select table_schema,count(*),group_concat(table_name) 
 from information_schema.tables 
 where table_schema not in ('mysql','sys','performance_schema','information_schema') 
 group by table_schema;
+--------------+----------+---------------------------------------+
| TABLE_SCHEMA | count(*) | group_concat(table_name)              |
+--------------+----------+---------------------------------------+
| school       |        5 | course,sc,student,stunde_fail,teacher |
| test01       |        1 | test01                                |
| world        |        3 | city,country,countrylanguage          |
+--------------+----------+---------------------------------------+

②获取每个数据库数据占用磁盘空间

#上一个工作数据库的存储量
select table_schema,sum(table_rows*avg_row_length+index_length)/1024/1024
from information_schema.tables 
where table_schema not in ('mysql','sys','performance_schema','information_schema') 
group by table_schema;
+--------------+-------------------------------------------------------+
| TABLE_SCHEMA | sum(table_rows*avg_row_length+index_length)/1024/1024 |
+--------------+-------------------------------------------------------+
| school       |                                            0.06248379 |
| test01       |                                            0.01562405 |
| world        |                                            0.76261139 |
+--------------+-------------------------------------------------------+

③获取具有碎片信息的表

select table_schema,table_name,data_free 
from information_schema.tables 
where table_schema not in ('mysql','sys','performance_schema','information_schema') and data_free >0 ;
---------------------------------------
table_schema | table_name | data_free |
---------------------------------------

#整理碎片数据表
alter table table_schema.table_name engine=innodb;
#批量整理碎片数据表
select concat("alter table ",table_schema,".",table_name," engine=innodb;") 
from information_schema.tables 
where table_schema not in ('mysql','sys','performance_schema','information_schema') and data_free >0 ;

5)练习

1）统计zhang3，学习了几门课？
解题思路步骤：
a 需要建立多个表的关联模型（E-R模型）
b 根据需求，确认需要查询的数据表 
  student sc course 
c 将多张表信息做拼接（内连接方式） 大表==单表 
  select * from student 
  join sc
  on student.sno=sc.sno
  join course 
  on sc.cno=course.cno;
d 利用大表(单表查询) select+from+where+group by+having+order by+limit 
  select student.sname,count(course.cname) from student 
  join sc
  on student.sno=sc.sno
  join course 
  on sc.cno=course.cno
  where student.sname='zhang3';  

2）查询zhang3，学习的课程名称有哪些？#假设有重名的情况
  select student.sno,student.sname,group_concat(course.cname) from student 
  join sc
  on student.sno=sc.sno
  join course 
  on sc.cno=course.cno
  where student.sname='zhang3'
  group by student.sno; 

3）查询xiaoA老师教的学生名？
  select teacher.tno,teacher.tname,group_concat(student.sname) from teacher
  join course
  on teacher.tno=course.tno
  join sc
  on course.cno=sc.cno
  join student
  on sc.sno=student.sno
  where teacher.tname='xiaoA'
  group by teacher.tno;   
   +-----+-------+---------------------------------------------+
   | tno | tname | group_concat(student.sname)                 |
   +-----+-------+---------------------------------------------+
   | 103 | xiaoA | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp |
   +-----+-------+---------------------------------------------+


4）查询xiaoA老师教课程的平均分数？
   select teacher.tno,teacher.tname,course.cname,avg(sc.score) from teacher 
   join course 
   on teacher.tno=course.tno 
   join sc 
   on course.cno=sc.cno
   where teacher.tname='xiaoA'
   group by teacher.tno,course.cno;  
   +-----+-------+-------+---------------+
   | tno | tname | cname | avg(sc.score) |
   +-----+-------+-------+---------------+
   | 103 | xiaoA | mysql |       76.7500 |
   +-----+-------+-------+---------------+

5）每位老师所教课程的平均分，并按平均分排序？
   select teacher.tno,teacher.tname,course.cname,avg(sc.score) from teacher 
   join course 
   on teacher.tno=course.tno 
   join sc 
   on course.cno=sc.cno
   group by teacher.tno,course.cno
   order by avg(sc.score);
   +-----+--------+--------+---------------+
   | tno | tname  | cname  | avg(sc.score) |
   +-----+--------+--------+---------------+
   | 102 | xiaoQ  | python |       70.0000 |
   | 103 | xiaoA  | mysql  |       76.7500 |
   | 101 | dezyan | linux  |       80.6667 |
   +-----+--------+--------+---------------+


6）查询xiaoA老师教的不及格的学生姓名？
   select teacher.tno,teacher.tname,course.cname,group_concat(student.sno),group_concat(student.sname) from teacher 
   join course 
   on teacher.tno=course.tno 
   join sc 
   on course.cno=sc.cno 
   join student 
   on sc.sno=student.sno
   where teacher.tname='xiaoA' and sc.score<60
   group by teacher.tno,course.cname;
   +-----+-------+-------+---------------------------+-----------------------------+
   | tno | tname | cname | group_concat(student.sno) | group_concat(student.sname) |
   +-----+-------+-------+---------------------------+-----------------------------+
   | 103 | xiaoA | mysql | 3,5                       | li4,zh4                     |
   +-----+-------+-------+---------------------------+-----------------------------+

7）查询所有老师所教学生不及格的信息？
   select teacher.tno,teacher.tname,course.cname,group_concat(student.sno),group_concat(student.sname) from teacher 
   join course 
   on teacher.tno=course.tno 
   join sc 
   on course.cno=sc.cno 
   join student 
   on sc.sno=student.sno
   where sc.score<60
   group by teacher.tno,course.cname;
   +-----+-------+--------+---------------------------+-----------------------------+
   | tno | tname | cname  | group_concat(student.sno) | group_concat(student.sname) |
   +-----+-------+--------+---------------------------+-----------------------------+
   | 102 | xiaoQ | python | 1                         | zhang3                      |
   | 103 | xiaoA | mysql  | 3,5                       | li4,zh4                     |
   +-----+-------+--------+---------------------------+-----------------------------+

6)子查询-嵌套查询

select * from (
   select * from t1 where id=(
     select id from t2
   )
   )

九、数据库服务字符设置

1.查看可以设置的编码

mysql> show charset;
#主要使用一下四种
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |   
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |

utf8mb3 等价于早期的utf8字符编码可以识别中文信息每个字符占用3字节
utf8mb4 支持utf8mb3的所有以及emoji表情每个字符占用4字节

2.设置字符编码

修改配置文件

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#添加一行
character-set-server=utf8mb3

查看字符设置

方式一：
mysql> show create database test01;
……DEFAULT CHARACTER SET utf8mb4……

方式二：
mysql> select @@character_set_server;
+------------------------+
| @@character_set_server |
+------------------------+
| utf8mb3                |
+------------------------+

3.设置校对规则/排序规则

校对规则功能作用
- 可以实现区分大小写查询数据
- 可以影响数据显示的默认排序
设置校对规则

show collation; 			#查看所有校对规则信息
utf8mb4_0900_ai_ci   | utf8mb4  | 255 | Yes  | Yes  |  0 | NO PAD   |
ci  	不区分大小写，Case-insensitive的缩写  utf8mb4_0900_ai_ci
cs  	区分大小写，Case-sensitive的缩写      utf8mb4_0900_as_cs
_bin 	采用二进制方式存储，影响数据排序 

#创建方法示例
use test01 
create table t1(info char(3)) charset utf8mb4 collate utf8mb4_0900_ai_ci;
create table t2(info char(3)) charset utf8mb4 collate utf8mb4_0900_as_cs;
create table t3(info char(3)) charset utf8mb4 collate utf8mb4_bin;
-- 创建三个测试数据表，并设置不同校对规则

十、数据库数据类型

创建数据表时，需要对表的列指定数据类型（合理录入数据控制数据磁盘占用影响索引应用）

1.数值类型

整数类型

tinyint  	微小整数  1个字节  范围(-128~127)     				范围(0~255)  
smallint 	小型整数  2个字节  范围(-32768~32767)  				范围(0~65535)
mediumint	中型整数  3个字节  范围(-8388608~8388607)			范围(0~16777215)
int         标准整数  4个字节  范围(-2147483648~2147483647)
bigint      大型整数  8个字节  范围(+-9.22*10的18次方)

浮点类型

float    单精度浮点类型  可以保留的小数位最多 6   无论小数位有多少都只会保存6位
double   双精度浮点类型  可以保留的小数位最多 17  无论小数位有多少最多保存17位
decimal  定点数类型      可以自定义
mysql> create table decimal01 (num decimal(20,19));

2.字符类型

char(n)     存储字符范围 最多255个字符     固定长度字符类型（提高数据检索效率） 
varchar(n)  存储字符范围 最多65535个字符   可变长度字符类型（提高磁盘利用率）

3.日期类型

date  		日期类型  		存储年月日
time  		时间类型  		存储小时分钟秒
datetime   	日期时间类型  	存储年月日 小时分钟秒 1000~9999          
timestamp   日期时间类型   	存储年月日 小时分钟秒 1970~2037

十一、数据库数据约束属性

1.约束设置

主键约束（PK primary key）

主键索引 :限制列信息非空且唯一

  CREATE TABLE `xiaoA`.`test01` (
  `id` INT NOT NULL,
  PRIMARY KEY (`id`));

唯一约束（UQ unique）

唯一索引:限制列的信息不能重复，但可以输入空值

  ALTER TABLE `xiaoA`.`test01` 
  ADD COLUMN `name` VARCHAR(10) NULL AFTER `id`,
  ADD UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE;
  ;

非空约束（NN not null）

限制列的信息不能为空，但是可以重复

  ALTER TABLE `xiaoA`.`test01` 
  ADD COLUMN `gender` ENUM('男', '女') NOT NULL AFTER `name`;

外键约束

当业务功能需要操作多张数据表时，需要控制操作表的顺序
当多张表插入数据和删除数据都会有合理顺序

  create table class(
   id int primary key auto_increment,            		      主键（主表）  
   name varchar(10) not null comment "班级名字，不能为空",   云计算运维班  
   room varchar(10) comment '教室：允许为空'     			  06              
  ) charset utf8;

  create table student(
   id int primary key auto_increment,                         01 02
   number char(10) not null unique comment "学号：不能重复",  2406xx
   name varchar(10) not null comment "姓名",                  张三      97 98 26 27
   c_id int,                                                  外键 （子表）       
   foreign key(c_id) references class(id) 
  ) charset utf8;


  create table class(
   id int primary key auto_increment, 
   name varchar(10) not null comment "班级名字，不能为空",
   room varchar(10) comment '教室：允许为空'        
  ) charset utf8;

  create table student(
   id int primary key auto_increment,
   number char(10) not null unique comment "学号：不能重复",
   name varchar(10) not null comment "姓名", 
   c_id int, 
   foreign key(c_id) references class(id) 
  ) charset utf8;
  
  -- 当设置外键约束，插入数据信息时，需要先在主表中插入数据，然后才能在子表中插入对应数据;
  -- 当设置外键约束，删除数据信息时，需要先在子表中删除数据，然后才能在主表中删除对应数据;

PS: 以上4种约束信息，其中PK UQ NN约束需要创建表时进行设置，FK约束可以创建表后进行设置

2.属性设置

#系统中的一些默认变量
default          设定默认数据信息，可以实现自动填充
auto_increment   设定数值信息自增，可以实现数值编号自增填充（一般配合主键使用）
comment          设定数据注释信息                                    
unsigned         设定数值信息非负，可以实现数值信息列不能出现负数信息

PS：

自增列自定义起始值

#创表时设置：
CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    AUTO_INCREMENT = 100
);
#创表后设置
alter table example auto_increment=100;

定义自增的间隔数值

#动态设置（该值默认为1）
SET SESSION auto_increment_increment = 100;  -- 当前会话生效
SET GLOBAL auto_increment_increment = 100;   -- 全局生效

#在配置文件中设置
[mysqld]
auto_increment_increment=100

sql_mode功能配置作用：保证数据存储和数据调取合理性

#临时配置：
set global sql_mode=配置的项目信息

#永久配置：配置文件中配置
vi /etc/my.cnf 
[mysqld]
sql_mode=配置的项目信息 

#查看sql_mode所有的配置项
show variables like 'sql_mode';
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#配置项详解
ONLY_FULL_GROUP_BY：保证在分组查询数据信息时，分组合并的数据，和其他行数据只能一对一显示，不能出现异常显示输出
STRICT_TRANS_TABLES：可以保证数据录入的完整性 日期信息出现不合理情况
NO_ZERO_IN_DATE,NO_ZERO_DATE：在时间类型中，不能出现0000-00-00时间信息
ERROR_FOR_DIVISION_BY_ZERO：保证数据表中数据信息做除法运算时，被除数不能为0；

十二、数据定义语句定义数据库、表规范说明

**数据定义语句定义数据库规范说明：**   
- 创建数据库名称规范：要和业务有关，不要有大写字母（为了多平台兼容），不要数字开头，不要含有系统关键字信息；
- 创建数据库明确字符：创建数据库时明确（显示）的设置字符集信息，为了避免跨平台兼容性与不同版本兼容性问题；
- 删除数据库操作慎用：在对数据库进行删除操作时，一定要经过严格审计后再进行操作，并且数据库普通用户不能有drop权限；

**数据定义语句定义数据表规范说明：**  
- 创建数据表名称规范：要和业务有关（含库前缀），不要有大写字母，不要数字开头，不要含有系统关键字信息，名称不要太长；
- 创建数据表属性规范：属性信息显示设置，引擎选择InnoDB，字符集选择utf8/utf8mb4，表信息添加注释；
- 创建数据列属性规范：名称要有意义，不要含有系统关键字信息，名称不要太长；
- 创建数据类型的规范：数据类型选择合适的、足够的、简短的； 
- 创建数据约束的规范：每个表中必须都要有主键，最好是和业务无关列，实现自增功能，建议每个列都非空（避免索引失效）/加注释
- 删除数据表操作规范：对于普通用户不能具有删表操作，需要操作删表时需要严格审核
- 修改数据表结构规范：在数据库8.0之前，修改数据表结构需要在业务不繁忙时进行，否则会产生严重的锁

十三、数据库服务体系结构

1.基础结构分析

客户端	-（本地/远程）-	服务端
命令工具代码		进程/线程

2.逻辑结构/物理结构

库	表	字段
数据目录	数据文件.ibd	ibd文件中的数据

3.数据库处理SQL语句层次结构

服务端、客户端

-------------------------------------- 服务层
连接器：实现验证，确保会话连接，产生会话线程

show full processlist; -- 会话线程数量上限受max_connections变量控制

分析器：完成SQL语句的语法分析和词法分析

语法分析：判断SQL语句是否存在语法异常情况

例如：

You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the right syntax to use near 'tabll t1' at line 1

词法分析：分析SQL语句类型 -- 确认操作权限确认操作对象是否存在

例如：

权限异常：

ERROR 1142 (42000): SELECT command denied to user 'test02'@'localhost' for table 'user'

对象异常：

mysql> select user,host from mysql.users;

ERROR 1146 (42S02): Table 'mysql.users' doesn't exist

mysql> select user,host from mysqls.user;

ERROR 1049 (42000): Unknown database 'mysqls

优化器：根据语句信息进行处理会选择最优语句处理方案（与索引有关）
生成多种数据查询方案（解析树）：全表扫描主键索引其他索引
根据查询方案进行数据库处理代价成本评估：CPU资源消耗少 MEM资源消耗少 IO资源消耗少
获取SQL语句执行方案（最优）
执行器：将SQL语句进行处理执行结果（获取数据引擎中的存储位置--引擎中位置信息）

-----------------------引擎层

引擎层（read io线程/write io线程）数据引擎（innodb myisam）等同于linux系统中的文件系统（xfs ext4 ext3）

进行数据存储（磁盘位置点）
进行数据调取（磁盘调取 -- 内存中 IO消耗）

数据库存储引擎不能直接控制管理硬件磁盘，而是借助CPU，再由CPU控制硬件完成数据处理工作

引擎结构

数据页   （page）   ：引擎中最小的数据存储单元		最大16KB
数据区/簇（extent） ：包含连续的多个数据页形成数据区 	  最大1MB
数据段   （segment）：包含连续的多个数据区形成数据段    表文件大小

十四、数据库服务索引知识

索引的作用：加快数据查询 -- 数据库表数据信息目录

1.索引结构构建方法（提高数据查询方法）

全表扫描/遍历查询
- 缺陷：当并发量大表数据量大时消耗磁盘IO资源多导致查询变慢
**二叉树结构查询 **
- 改善：减少磁盘查询数据IO消耗
- 缺陷：查询数据时，消耗IO量不一致，造成有些数据查询快有些数据查询慢
**B+TREE **
- 改善：可以保证查询任意数据消耗IO资源均衡查询任意数据时间代价成本相等
- 适合范围数据查询，一个数据页加载 == 磁盘中1个IO消耗

B+TREE索引结构
根节点（唯一节点）              存储数据范围和指针信息
支节点（多个节点 连续数据页） 	存储数据范围和指针信息
页节点（多个节点 连续数据页） 	用于存储数据信息	     便于范围查询语句页节点之间会有横向指针

2.常见的索引分类方式

①按索引的存储方式

1)聚簇索引方式应用

利用主键列建立索引结构 BTREE结构数据页中会存储每行数据
- 主要是：将多个簇（区-64个数据页-1M）聚集在一起就构成了所谓聚簇索引，也可以称之为主键索引；
- 作用：用来组织存储表的数据行信息的，也可以理解为数据行信息都是按照聚簇索引结构进行存储的，即按区分配空间的；
- 存储：聚簇是多个簇，簇是多个连续数据页(64个)，页是多个连续数据块(4个)，块是多个连续扇区(8个)；
聚簇索引的构建方式
- 数据表创建时，显示的构建了主键信息（pk），主键（pk）就是聚簇索引； ******
- 数据表创建时，没有显示的构建主键信息时，会将第一个不为空的UK的列做为聚簇索引；
- 数据表创建时，以上条件都不符合时，生成一个6字节的隐藏列作为聚簇索引；
应用聚簇索引结构
- 利用根节点可以获取主键范围信息和指针信息从而调取对应支节点数据页
- 利用支节点可以获取主键范围信息和指针信息从而调取对应页节点数据页
- 利用页节点可以对比查询的主键值获取最终需要显示的数据行内容

2)非聚簇(辅助)索引方式应用

利用指定索引列结构获取对应主键列信息在利用主键列进行回表查询可以获得指定行的完成数据
- 主要是：主要用于辅助聚簇索引查询的索引，一般按照业务查找条件，建立合理的索引信息，也可以称之为一般索引/普通索引/二级索引；
- 作用：主要是将需要查询的列信息可以和聚合索引信息建立有效的关联，从而使数据查询过程更高效，节省IO和CPU消耗
- 存储：调取需要建立的辅助索引列信息，并加上相应主键列的所有信息，存储在特定的数据页中；
辅助索引的构建方式
- 数据表创建时，显示的构建了一般索引信息（mul），一般索引信息（mul）就是辅助索引；
- 数据表创建时，没有显示的构建一般索引信息时，在查询检索指定数据信息，会进行全表扫描查找数据；
应用辅助索引结构
- 利用根节点可以获取索引列范围信息和指针信息从而调取对应支节点数据页
- 利用支节点可以获取索引列范围信息和指针信息从而调取对应页节点数据页
- 利用页节点可以对比查询的索引列值获取对应的主键信息在利用主键信息进行回表查询从而得到完成数据行信息

②按索引的用途

注意：当数据表的数据量较多时（上万行），可以创建列的索引；但数据量较少时（小于万行），不用创建索引信息

1)普通索引(一般索引、二级索引)

普通索引是MySQL中的基本索引类型，使用key或index定义，不需要添加任何限制条件(其他关键语句)；
应用场景：经常进行条件查询的列，并且此列不具有唯一性 select * from student where name=xxx
查询缺陷：当索引出现多个相同数值信息时，查询过程会造成回表次数过多的情况

2)唯一索引

创建唯一索引的字段允许有NULL值，但需要保证索引对应字段中的值是唯一的；
应用场景：字段列是唯一的，并且唯一字段列经常作为条件查询 select * from emp where phone='xxxx'
查询缺陷：如果唯一索引列出现空值信息存储，也会影响查询效率

3)主键索引

主键索引是一种特殊的唯一性索引，用于根据主键自身的唯一性标识每一条记录，主键索引的字段不允许有NULL值
应用场景：每个表中都需要有主键索引，便于利用主键索引查询表中所有数据信息 select * from user where id=xxx
查询缺陷：不便于将主键列作为查询条件列

4)全文索引

全文索引主要用于提高在数据量较大的字段中的查询效率。全文索引和SQL中的LIKE模糊查询类似；
不同的是LIKE模糊查询适合用于在内容较少的文本中进行模糊匹配，全文检索更擅长在大量的文本中进行数据检索。
全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。

5)空间索引

空间索引只能创建在空间数据类型的字段上，其中空间数据类型存储的空间数据是指含有位置、大小、形状以及自身分布特征等多方面信息数据；
MySQL中的空间数据类型有4中，分别是GEOMETRY、POINT、LINESTRING和POLYGON。
需要注意的是，对于创建空间索引的字段，必须将其声明为NOT NULL。

③按索引的结构

1)单列索引

单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一索引或主键索引，只要保证该表索引只对应表中一个字段即可；

2)复合索引（联合索引）减少回表次数

复合索引指的是在表中多个字段上创建一个索引，并且只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用；
例如：在员工表emp的ename和deptno字段上创建一个复合索引，那么只有查询条件中使用了ename字段时，该索引才会被使用。

3.索引的创建方式

①普通索引创建 (MUL)

方式1：在创建表时创建索引

#语法
create table 表名 (字段名1 数据类型 [约束属性信息],...
{index|key} [索引名][索引类型](字段列表));  
#示例
create table test_index01 (id int not null,name char(10) not null,index (name));

方式2：在创建表后再单独创建索引

#语法
create index 索引名 [索引类型] on 数据表名 (字段列表);
#示例
create index name on test_index02 (name);

方式3：修改表信息时创建索引

#语法
alter table 数据表名 add {index|key} [索引名] [索引类型] (字段列表);  
#示例
alter table test_index02 add key (id);

②唯一索引创建 (UNI)

与普通索引创建方法相比，唯一索引创建多了关键字 unique
方式1：在创建表时创建索引

create table 表名 (字段名1 数据类型 [约束属性信息],...
UNIQUE  {index|key} [索引名][索引类型](字段列表));

方式2：在创建表后再单独创建索引

create UNIQUE index 索引名 [索引类型] on 数据表名 (字段列表);

方式3：修改表信息时创建索引

alter table 数据表名 add UNIQUE {index|key} [索引名] [索引类型] (字段列表);

③主键索引创建 (PRI)

主键索引不能创完表后再单独创建
方式1：在创建表时创建索引

#语法
create table 表名 (字段名1 数据类型 [约束属性信息],...
PRIMARY KEY [索引类型](字段列表));  
#示例
create table test_index05 (id int,name char(10),primary key (id));

方式2：修改表信息时创建索引

#语法
alter table 数据表名 add PRIMARY KEY [索引类型] (字段列表);
#示例
alter table test_index06 add primary key (id);

4.查看索引的方式

①desc命令

#语法
desc 表名;
#示例		其中Key列显示索引类型
mysql> create table test_index03 (id int,name char(10),unique index (name));
Query OK, 0 rows affected (0.00 sec)

mysql> desc test_index03;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

②show命令

#语法
show index from 表名；
#示例
mysql> show index from test_index03\G
*************************** 1. row ***************************
        Table: test_index03
   Non_unique: 0
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

输出的列信息解释说明：

PS：其中有值得注意的两个字段

Sub_part字段
- 当索引信息中，Sub_part字段不是为null时，表示创建了一个特殊前缀索引;
- alter table 表 add key (字段名(数值));
- 前缀索引作用：有效控制索引结构的高度，从而控制扫描索引树时，减少消耗的IO资源
- 当索引树高度过高时，也会产生大量IO消耗，影响查询数据效率/存储数据效率（建议3-4层最多不超过5层）
- 可以将存储到索引页节点的信息进行控制，只存储少量关键信息
总结：前缀索引可以控制索引层次结构，减少索引树高度，从而减少IO消耗
Visible字段
- 用于禁用或启用索引功能，在进行数据表数据批量插入时，可以临时禁用索引功能，提高批量插入数据效率
- 默认为启用，启用方法为：
```
ALTER TABLE 表名 ALTER INDEX 索引名 VISIBLE;
```
- 禁用方法为：
```
ALTER TABLE 表名 ALTER INDEX 索引名 INVISIBLE;
```

5.索引的删除方式

①普通索引删除

alter table 表名 drop index 索引名; 
drop index 索引名 on 数据表名;

②唯一索引删除

与普通索引删除方式相同

alter table 表名 drop index 索引名;  
drop index 索引名 on 数据表名;

③主键索引删除

alter table 表名 drop primary key;
drop index primary on 数据表名;

6.数据库索引应用总结

1）数据库索引信息管理由专业人员进行操作;
2）是否创建索引取决于表的数据量
- 基本表中的记录的数据量越多，记录越长，越有必要创建索引。
  创建索引后，查询速度的提升效果会很明显；
  要避免对经常更新的表创建过多的索引，索引中的列也要尽可能少；
- 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，创建索引可能不会产生优化效果
  对经常用于查询的字段应该创建索引，但要避免添加不必要的字段；
3）索引数据量要适度  
- 索引文件占用文件目录和存储空间，因此索引过多会加重系统负担；
- 索引需要自身维护，当基本表的数据增加、删除或修改时，索引也会进行调整和更新，索引文件也要随之变化，以保持与基本表一致；
- 索引过多会影响数据增、删、改的速度；
  索引并非越多越好，一张表中如果有大量的索引，不仅占用磁盘空间，而且还会影响insert、delete、update等操作的性能；
4）避免给表创建索引
- 包含太多重复值的字段；
- 查询中很少被引用的字段；
- 值特别长的字段；
- 查询返回率很高的字段；
- 具有很多NULL值的字段；
- 需要经常增、删、改的字段；
- 记录较少的表；
- 需要频繁、大批量进行数据更新的基本表；

7.数据表访问压力测试（验证数据表索引价值）

表没有索引进行测试

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --iterations=1 --create-schema='dezyan' --query="select * from dezyan.t100w where k2='VWlm'" engine=innodb --number-of-queries=1000 -uroot -p123456 -h10.0.0.51 -verbose

#命令详解
--defaults-file：   需要加载压力测试实例的配置文件 
--concurrency：		模拟高并发访问的数量
--iterations：      并发压力测试的重复次数
--create-schema：   指定数据库信息，表示切换到对应数据库
--query             进行查询压力测试 
--number-of-queries 并发访问的总请求数

#输出结果  用时257秒
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 257.327 seconds
	Minimum number of seconds to run all queries: 257.327 seconds
	Maximum number of seconds to run all queries: 257.327 seconds
	Number of clients running queries: 10
	Average number of queries per client: 100

表创建索引后测试

mysql> alter table t100w add key (k1);
mysql> alter table t100w add key (k2);
#输出结果	用时1.4秒，明显快不少
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 1.460 seconds
	Minimum number of seconds to run all queries: 1.460 seconds
	Maximum number of seconds to run all queries: 1.460 seconds
	Number of clients running queries: 10
	Average number of queries per client: 100

十五、数据库服务执行计划

功能作用：可以看到优化器进行优化选择后的执行方案信息

1.查看执行计划信息

desc SQL语句    
explain SQL语句
#输出结果
mysql> explain select * from dezyan.t100w where k2='VWlm'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t100w
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 997335
     filtered: 10.00
        Extra: Using where

2.执行计划索引类型信息(key)

第七列key输出的内容可能是以下几种
注：本表设置了k1和k2列作为普通索引，其余列都没设

ALL：表示全表扫描方式，没用利用索引扫描类型

出现原因：

#一：查找条件没有应用索引列
例如：指定了num列作查找
mysql> explain select * from dezyan.t100w where num='25503'\G
#二：查询方式应用了匹配查询 匹配条件信息前出现了%字段  注意！是信息前！
例如：
mysql> explain select * from dezyan.t100w where k1 like '%QJ';
#三：查询条件使用的了排除法  使用了not in   或  不等于
例如：
mysql> explain select * from dezyan.t100w where k1 not in ('At');
mysql> explain select * from dezyan.t100w where k1<>'At';

index - 表示全索引扫描方式，需要将索引树全部遍历，才能获取查询的信息

出现原因：

#一：扫描查询列设置了辅助索引信息，但是没有基于索引列设置查询条件
例如：就指定了一列，没有指定具体要查找的信息
mysql> explain select k1 from dezyan.t100w;
补充：在数据库中，有一种特殊索引应用：覆盖索引应用 可以避免出现回表情况（配合联合索引应用 确认数据是否存在）
select k1 from dezyan.t100w where k1='At';  -- 信息确认 不会进行回表查询
select name from dezyan.t100w where id=001; -- 利用联合索引查询特定数据，可以不用回表查询

range - 表示范围索引方式，按照索引的区域范围扫描数据，获取查询的数据信息

出现原因：

#一：找条件是范围信息（> < >= <= between and in or）
例如：
explain select * from dezyan.t100w where k1='At' or k1='ft' or k1='kk';
#二：行模糊配置查询 应用匹配数据方式 字段后出现了%		注意！是字段后！
例如：
explain select * from dezyan.t100w where k1 like 'A%';

**ref - 表示辅助索引等值（常量）查询，精准定义辅助索引的查询条件 **
- 出现原因：
```
#精准查找索引列的一个存在的数据
mysql> explain select * from dezyan.t100w where k1='At';
```
eq_ref - 表示多表连接查询时，被驱动表的连接条件是主键或者唯一键时，获取的数据信息过程；
- 出现原因：
```
desc select city.name, country.name,city.population from city left join country on city.countrycode=country.code;
```
const - 表示主键索引等值（常量）查询（没有回表过程调用辅助索引结构可以直接应用聚簇索引）
- 出现原因：
```
#设置id为主键，精确查找主键的数值
mysql> desc select * from t100w where id=5\G
```
system -- 当表中只有一行数据，并且表的引擎设置为myisam，此时查询表数据信息回显示system类型

十六、数据库服务日志管理

1.数据库服务中的日志及其功能作用

错误日志： 数据目录/db-01.err
- 用于记录客户端连接信息，记录用户登录后的操作行为（SQL语句）
通用日志： 自定义设置
- 记录数据库服务启动运行状态信息（正常提示信息错误提示信息）用于排查数据库运行异常原因
二进制日志：数据目录/binlog
- 主要记录数据库中的事务信息（DML语句信息 DDL语句信息 DCL语句信息）
慢查询日志：slow-xx.log
- 记录在数据库中出现的慢操作的语句，以及没有应用索引的语句

2.创建生成日志信息

[root@db01 ~]# mkdir /data/3306/log
[root@db01 ~]# chown mysql.mysql  /data/3306/log

①通用日志

默认不存在（企业环境一般无需生成）
查看状态

mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+			输出值为布尔类型，0代表关闭

配置开启

#在my.cnf配置文件中启用，并可以设置日志存放路径
general_log=ON
general_log_file=/data/3306/log/general.log

②错误日志

默认创建
查看状态

mysql> select @@log_error;
+-------------+
| @@log_error |
+-------------+
| ./db01.err  |
+-------------+			输出结果为日志存放路径

修改存放路径

#在my.cnf配置文件设置日志存放路径
log_error=/data/3306/log/error.log

③二进制日志

默认创建查看需要应用mysqlbinlog命令查看
修改存放路径

log_bin=/data/3306/log/binlog

④慢查询日志

默认不存在
启用：

slow_query_log      			-- 开启慢查询日志功能
slow_query_log_file 			-- 指定慢查询日志路径/名称
long_query_time     			-- 定义超过多长时间执行的语句就是慢查询语句
log_queries_not_using_indexes   -- 当没有走索引的查询语句也要记录到慢查询日志

#在my.cnf中配置，重启数据库生效
vim /data/3306/my3306.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/data/3306/log/slow.log
long_query_time=0.01
log_queries_not_using_indexes=1

3.二进制日志管理

①如何查看二进制日志

方式一：在数据库中进行查看

mysql> show binary logs;   -- 查看数据库生成哪些二进制日志
mysql> show master status; -- 查看数据库中正在加载的二进制日志
mysql> show binlog events in 'binlog.000002';  -- 查看具体binlog文件中内容

show master status输出信息详解

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000014
         Position: 21107179			#可以理解为日志大小/存储事务信息位置点  
     Binlog_Do_DB: 					#设置什么库的语句操作(DDL DML)会记录到binlog  binlog日志进行记录的白名单设置
 Binlog_Ignore_DB: 					#设置什么库的语句操作(DDL DML)不记录到binlog  binlog日志进行记录的黑名单设置
Executed_Gtid_Set: 					#用于给每个事务操作设置一个唯一标识信息

show binlog events in 输出信息详解

mysql>MASTER_LOG_POS=157
*************************** 1. row ***************************
   Log_name: binlog.000002
        Pos: 4				#事件在 binlog 文件中的起始位置偏移量
 Event_type: Format_desc	#表示事件的类型，描述了 binlog 中记录的具体操作。
  Server_id: 1				#表示生成该 binlog 事件的 MySQL 服务器的 ID
End_log_pos: 126			#事件在 binlog 文件中的结束位置偏移量
       Info: Server ver: 8.0.36, Binlog ver: 4
							#事件的详细信息,执行语句
							
注意：
       DDL DCL 语句信息可以正常记录 可以直接在binlog中看到SQL语句
	   DML     语句信息无法正常记录 不能直接在binlog中看到SQL语句

方式二：在命令行中进行查看（mysqlbinlog）

[root@db01 ~]# mysqlbinlog binlog.000002

注意：在binlog文件中查看 DDL DCL是可以明文查看的，文件中看到的DML语句是进行编码转换后的信息

如果要查看具体语句信息，可使用以下命令

[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv  binlog.000002

②binlog的记录格式

查看

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set, 1 warning (0.00 sec)

类型

ROW 		：行格式进行信息记录    	（RBR）    DML语句不能直接记录
e.g:	INSERT INTO `xiaoC`.`t2`SET @1=2  @2='xiaoB' @3='2025:02:27'

STATEMENT	：语句格式进行信息记录	   	（SBR）    DML语句可以直接记录
e.g:	insert into xiaoC.t1 values (2,'xiaoB',now())

mixed       ：混合格式进行信息记录	   	（MBR）
		根据DML语句的应用情况 会自动选择明文 和 编码方式记录

优缺点

ROW（最常用） 
好处：保证数据恢复或者主从同步时，数据信息的一致性  
坏处：DML语句信息不便于阅读

STATEMENT   
好处：DML语句信息便于阅读
坏处：会造成恢复数据或主从同步数据不一致

③应用二进制日志恢复数据

1)创建二进制测试数据
create database xiaoD;
use xiaoD;
create table t1 (id int,name char(10),time datetime);
insert into t1 values (1,'xiaoA',now()),(2,'xiaoB',now());

create database xiaoE;
use xiaoE;
create table t1 (id int,name char(10),time datetime);
insert into t1 values (1,'xiaoC',now()),(2,'xiaoD',now());

2)模拟误操作行为
mysql> drop database xiaoD;

3)查看二进制日志
mysql> show binlog events in 'binlog.000016'\G
*************************** 3. row ***************************
   Log_name: binlog.000016
        Pos: 157				起始点
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 234
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000016
        Pos: 234
 Event_type: Query
  Server_id: 1
End_log_pos: 345
       Info: create database xiaoD /* xid=2218 */
*************************** 20. row ***************************
   Log_name: binlog.000016
        Pos: 1576
 Event_type: Xid
  Server_id: 1
End_log_pos: 1607				结束点
       Info: COMMIT /* xid=2231 */
恢复数据起始位置：pos 157  --start-position
恢复数据结束位置：pos 1607 --stop-position

4）应用binlog日志信息截取
[root@db01 ~]#mysqlbinlog --start-position=157 --stop-position=1607  /data/3306/data/binlog.000016 >/tmp/xiaoD_bak.sql
[root@db01 ~]# ll /tmp/*.sql
-rw-r--r-- 1 root root 8007 Feb 27 07:55 /tmp/xiaoD_bak.sql

5)进行数据信息恢复
[root@db01 ~]# mysql -uroot -p123123  </tmp/xiaoD_bak.sql
mysql> show databases;
| xiaoD              |

④二进制日志如何进行切割处理

方式一：编写脚本实现日志切割

[root@db01 ~]# mysql -uroot -p123123  -e 'flush logs'
[root@db01 ~]# mysqladmin -uroot -p123123  flush-logs

方式二：设置数据库服务配置项实现切割

max_binlog_size  -- 定义binlog日志大小进行切割的变量
#默认日志大小超过1G便会自动切割
mysql> select (@@max_binlog_size/1024/1024/1024);
+------------------------------------+
| (@@max_binlog_size/1024/1024/1024) |
+------------------------------------+
|                     1.000000000000 |
+------------------------------------+
注：默认单位为字节，这里将其转化成了GB为单位

⑤二进制日志信息清理方法

方式一：进行自动清理

binlog_expire_logs_seconds   2592000
-- 按照秒确认切割日志存在的时间，超过配置时间会自动清理,默认为30天
expire_logs_days             0
-- 按照天确认切割日志存在的时间，超过配置时间会自动清理，默认不启用
注：当变量值为0时，代表不启用此配置，按秒和按天分割只能同时启用其中之一
mysql> select @@binlog_expire_logs_seconds;
+------------------------------+
| @@binlog_expire_logs_seconds |
+------------------------------+
|                      2592000 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+

方式二：进行手工清理

purge binary logs to 'binlog日志名';				#清理此日志
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';	  #清理此事件之前的日志

注意：用rm的方式虽然能删除，但是尽量不要使用，容易使数据库崩溃

⑥二进制日志远程备份

作用
- 可以将多个数据库实例二进制日志汇总保存
- 可以实现高可用架构中的数据补偿
实现备份功能的步骤

1)创建新的数据库日志备份服务器
需安装好数据库程序（不用启动）
本次以kylin系统10.0.0.52服务器为备份服务器，主机名为db02

2)创建日志备份存储目录
[root@db02 ~]# mkdir -p /backup/db01-3306/
[root@db02 ~]# cd /backup/db01-3306/

3)执行远程备份命令
[root@db02 db01-3306]#mysqlbinlog -R --host=10.0.0.51 --user=root --password=123456 --raw --stop-never binlog.000016  &
此命令表示从binlog.000016日志开始，及以后所有的binlog日志都会自动备份到执行此命令时所在的目录
[root@db02 ~]# ll
-rw-r-----  1 root root 2573 Feb 27 08:16 binlog.000016
-rw-r-----  1 root root  157 Feb 27 08:16 binlog.000017

4.慢查询日志应用管理：（运维)

①查看慢查询日志

配置启用慢查询日志后记得重启数据库服务

[root@db01 ~]# service mysqld stop
[root@db01 ~]# service mysqld start

删除原有索引，查询测试

use dezyan;  
SET timestamp=1740629303;			---设置当前时间戳
select * from t100w where k1='At';

查看慢查询日志

[root@db01 log]# cat /data/3306/log/slow.log 
# Time: 2025-02-27T13:30:14.713009Z
				---慢查询语句执行时间
# User@Host: root[root] @ localhost []  Id:     8
				---触发慢查询语句客户端信息
# Query_time: 0.407583  Lock_time: 0.000002 Rows_sent: 1056 Rows_examined: 1000000 
				---慢查询时间信息统计Query_time，获取数据行Rows_sent，扫描的数据行Rows_examined
use dezyan;	
				---数据库信息
SET timestamp=1740663014;
select * from t100w where k1='At';
				---SQL语句

②分析慢查询日志

[root@db01 ~]# mysqldumpslow -s c -t 3 /data/3306/log/slow.log
-t 3为可选参数

可选参数

al: average lock time  -- 根据平均数据表锁定时间进行统计
ar: average rows sent  -- 根据平均值找到输出结果集多的语句
at: average query time -- 根据平均查询时间找到对应查询语句
 c: count              -- 根据语句执行次数进行分析
 l: lock time          -- 根据语句锁定时间进行分析
 r: rows sent          -- 根据语句结果集指定行数进行分析
 t: query time         -- 根据语句指定时间进行分

十七、数据库服务备份恢复

1.数据库服务备份数据方式

①物理方式(备份的数据量在50G以上)

采用拷贝物理文件数据进行备份的方式，数据库服务物理数据文件存放路径是：/data/3306/data
实现方式
- 可以在某个特定时间点停机或停止业务访问，然后利用cp和tar命令将物理数据文件备份或打包；(不常用)
- 可以在任意时间节点在不停机不停止业务时，然后利用专业的xtrabackup(Percona Xtrabackup)热备工具进行数据库数据备份；
应用场景
- 当企业数据库服务产生的需要备份的数据量在50G以上，可以选择物理备份（xtrabackup）

②逻辑方式(备份的数据量在50G以内)

可以采用以SQL语句形式把数据库的数据导出保存备份为数据库文件（xxx.sql），文件中会含有大量SQL语句信息；
逻辑方式备份好的数据可以进行更灵活的数据恢复
实现方式
- 可以在任意时间节点在不停机不停止业务时，然后利用专业的mysqldump(MDP)逻辑备份工具进行数据备份；(可以保存更全的数据)
- 可以在任意时间节点在不停机不停止业务时，然后利用二进制日志binlog文件实现逻辑备份数据操作； (可以保存实时数据信息)
- 可以在任意时间节点在不停机不停止业务时，然后利用主从数据库架构实现备份数据信息；
应用场景
- 当企业数据库服务产生的需要备份的数据量在50G以内，可以选择逻辑备份（mysqldump）；

2.数据库服务备份恢复步骤

①逻辑备份与恢复mysqldump

方式一：实现数据全备，恢复

1)备份
本地备份：mysqldump -uroot -p123123  -A >/tmp/all.sql
远程备份：mysqldump -uroot -p123123 -h10.0.0.51 -P3306 -A >/tmp/all.sql
-A参数表示备份所有数据库

2)恢复
命令行：mysql -uroot -p123123 -S /tmp/mysql3306.sock </tmp/all.sql
数据库中：mysql> source /tmp/all.sql;

方式二：实现分库备份（指定库备份）

1)备份
本地备份：mysqldump -uroot -p123123  -B 库名y >xxx.sql
远程备份：mysqldump -uroot -p123123 -h10.0.0.51 -B 库名  >xxx.sql
-B, --databases      实现指定数据库进行备份

2)恢复
命令行：mysql -uroot -p123123 -S /tmp/mysql3306.sock <xxx.sql
数据库中：mysql> source /tmp/dezyan.sql;

方式三：实现分表备份（指定表备份）

1)备份
本地备份：mysqldump -uroot -p123123 库名 表名 >xxx.sql
远程备份：mysqldump -uroot -p123123 -h10.0.0.51 库名 表名>xxx.sql

2)恢复
mysql -uroot -p123123  库名 <xxx.sql

②物理备份与恢复

方式一：cp、tar命令

利用cp或者tar命令进行数据目录物理备份（需要停止数据库服务）

步骤一：需要停止数据库服务 
步骤二：将数据目录进行备份
[root@db01 ~]# cp -a /data/3306/data/* /backup/

步骤三：模拟进行数据删除
[root@db01 ~]# drop database dezyan;

步骤四：利用备份数据目录进行恢复数据
确认数据库服务器进程停止，并清理数据目录（rm -rf /data/3306/data/*）
[root@db01 ~]# cp -a /backup/* /data/3306/data/
数据库恢复完毕，重启数据库服务

方式二：xbk工具

利用xbk工具实现数据目录物理备份（可以实现热备操作恢复数据需要停止服务）

下载安装xtrabackup工具

步骤一：下载工具软件包（安装xbk工具）
下载地址：https://www.percona.com/downloads
查看mysql8.0.36所对应的xtrabackup版本
https://docs.percona.com/percona-xtrabackup/8.0/index.html
使用版本为：percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm

步骤二: 安装软件程序
[root@db01 ~]# yum localinstall -y percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm
若下载失败需解决依赖问题

PS：kylin v10 sp3目前只能使用二进制安装
[root@kylin-db01 ~]# tar xf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
[root@kylin-db01 ~]# ln -s percona-xtrabackup-8.0.35-30-Linux-x86_64 percona
[root@kylin-db01 ~]# cd percona/bin  -- xtrabackup
[root@kylin-db01 ~]# xport PATH="$PATH:/usr/local/percona/bin"

全量备份与恢复

------------------------------全量备份-----------------------------
步骤1：创建备份数据目录，进行数据备份 
[root@db01 ~]# mkdir /backup/full -p
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --host=10.0.0.51 --port=3306 --user=root --password=123456  --backup  --target-dir=/backup/full

#参数详解
--target-dir=/backup/full
--defaults-file   加载备份数据库实例配置文件
--host            连接数据库实例（地址信息）
--port            连接数据库实例（端口信息）
--user            登录信息（用户信息）
--password        登录信息（密码信息）
--backup          进行备份操作
--target-dir      指定保存备份数据目录

###注意注意：
01 确认备份数据的目录一定是空目录
02 xbk工具只能安装到数据库服务器中 实现本地数据备份


步骤2：模拟数据库数据损坏
[root@db01 ~]# rm -rf /data/3306/data/*

----------------------------------全量恢复------------------------
步骤3：停止数据库服务
步骤4：执行数据恢复操作
[root@db01 ~]# chown -R mysql.mysql /data/3306/
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --prepare  --target-dir=/backup/full
--prepare  ：可以将备份过程中，没有保存的数据恢复到内存中
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --copy-back --target-dir=/backup/full
--copy-back：可以将原有数据目录数据（磁盘数据）进行恢复还原

步骤5：需要重新启动数据库服务

增量备份与恢复

------------------------------增量备份-----------------------------
步骤1：需要先进行全量备份
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --host=10.0.0.51 --port=3306 --user=root --password=123456  --backup  --target-dir=/backup/full --parallel=4
注：--parallel=4    增加xtrabackup工具备份线程(默认1线程)，提高备份数据效率

步骤2：再进行增量备份
注：增量备份需要再创建一个新的目录，且新目录也要为空
-创建新增数据：
create database xiaoA;
use xiaoA;
create table t1 (id int);
insert into t1 values (1),(2),(3);

-创建第一次增量备份存放目录
[root@db01 ~]# mkdir -p /backup/week01
-进行第一次增量备份
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --host=10.0.0.51 --port=3306 --user=root --password=123456 --backup --parallel=4 --target-dir=/backup/week01  --incremental-basedir=/backup/full

-再创建新增数据
create database xiaoB;
use xiaoB;
create table t2 (id int);
insert into t2 values (4),(5),(6);

-创建第二次增量备份存放目录
[root@db01 ~]# mkdir -p /backup/week02
-进行第二次增量备份
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --host=10.0.0.51 --port=3306 --user=root --password=123456 --backup --parallel=4 --target-dir=/backup/week02  --incremental-basedir=/backup/week01

----------------------------------增量恢复------------------------
步骤1：模拟数据损坏
rm -rf /data/3306/data/*
ll /data/3306/data/

步骤2：进行数据恢复
1.加载全量备份中，需要进行内存恢复数据
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --prepare  --apply-log-only  --target-dir=/backup/full

2.加载第一次增量备份时，需要进行内存恢复数据，并和全量数据进行整合
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --prepare  --apply-log-only  --target-dir=/backup/full --incremental-dir=/backup/week01
		
3.加载第二次增量备份时，需要进行内存恢复数据，并和全量数据进行整合
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --prepare  --apply-log-only  --target-dir=/backup/full --incremental-dir=/backup/week02

4. 将全量和增量整合后数据，恢复到内存中				
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --prepare   --target-dir=/backup/full

5.加载全备数据目录，实现数据库数据目录恢复（磁盘
[root@db01 ~]# xtrabackup --defaults-file=/data/3306/my3306.cnf --copy-back --target-dir=/backup/full

6.重新对数据目录赋权
[root@db01 ~]# chown -R mysql.mysql /data/3306/

步骤3：重新启动数据库服务，进行测试检查

--apply-log-only  保证保存数据不会进行撤销回滚操作（rollback）,在备份过程中没有提交的数据 （主要应用在增量恢复数据）

十八、数据库服务存储引擎(面试)

1.什么是存储引擎（可以和磁盘对接）

MySQL数据库存储引擎类似是数据库服务中的文件系统，用户可以根据应用的需要选择如何存储和索引数据

操作系统程序本身不能直接控制硬件，而是通过内核和设备驱动程序来调用和管理硬件资源。内核提供了系统调用接口，应用程序（包括数据库）通过这些接口间接访问硬件。
数据库本身是一个应用程序，它没有内核，因此无法直接将数据写入磁盘。数据库通过存储引擎来管理数据的存储和检索。存储引擎的作用类似于操作系统中的文件系统和内存管理模块，它负责将数据组织成适合存储和检索的格式，并通过操作系统的系统调用与磁盘等硬件交互。存储引擎并不是操作系统的内核，而是数据库系统内部的一个组件，它依赖于操作系统的内核来完成底层的硬件操作。

2.存储引擎的类型

①常用的存储引擎

mysql> show engines;
InnoDB  新版本默认的存储引擎，相较于MyISAM功能更多，支持的特性也更多
MyISAM  旧版本默认的存储引擎
MEMORY  非关系型数据库的存储引擎

②InnoDB核心特性有哪些，以及与MyISAM存储引擎之间的区别： (面试题)

InnoDB支持：事务、mvcc(实现多读写并发)、聚簇索引、外键、缓冲区(新版本默认取消了)、AHI、DW（保证数据保存安全性）；MyISAM均不支持
InnoDB支持：行级锁，MyISAM只支持表级锁；
InnoDB支持：数据热备，可以保证业务正常运行，对业务影响低，MyISAM只支持温备份，需要锁表备份；
InnoDB支持：支持CR自动故障恢复，宕机自动恢复，数据安全和一致性可以得到保证；MyISAM不支持，宕机可能丢失当前数据；

3.如何设置存储引擎

①方式一：默认设置

default_storage_engine  -- 利用此配置项或变量调整默认存储引擎
set global default_storage_engine='MyISAM';

②方式二：对表单独设置

alter table t1 engine='MyISAM';

4.存储引擎结构组成

①磁盘组成部分

1）系统表空间（文件 ibdata1）

作用：
- 早前：用于存储数据库服务所有数据信息（元数据信息数据信息）
- 当前：用于change buffer数据信息（存储内存区域中的数据）

2）独立表空间（文件 .ibd）

作用：用于保存表中数据信息
- 早期：ibd文件只保存数据信息（表.ibd 表.frm（存储表结构信息）表名.MYI （存储索引信息）？）
- 当前：ibd文件只保存数据信息保存表的结构信息保存表的索引信息
可以利用ibd文件恢复数据信息：独立表空间迁移过程

步骤一：在数据库中创建测试数据
mysql -uroot -S /tmp/mysql3306.sock <~/t100w_dezyan.sql

步骤二：提前获取恢复数据表的结构信息
show create table t100w;
CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

步骤三：模拟数据库宕机
....

步骤四：部署新的数据库实例
....

步骤五：在新的数据库实例中创建库和表信息
create database dezyan;
use dezyan;
CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

步骤六：实现独立表空间迁移
alter table dezyan.t100w discard tablespace;
-- 物理删除表文件，但逻辑表信息仍然存在
cp -a /data/3306/data/dezyan/t100w.ibd /data/3307/data/dezyan/t100w.ibd
-- 迁移数据表文件
alter table dezyan.t100w import tablespace;
-- 重新加载磁盘中数据表

3）双写文件区域（文件 #ib_16384_0.dblwr #ib_16384_1.dblwr）

功能作用:
- 可以保证内存数据到磁盘中，不会出现损坏的数据，避免数据库异常宕机的数据无法加载情况;(利用CR机制)
- 防止部分写失效：在将数据页写入磁盘时，如果发生崩溃，可能导致数据页部分写入，从而损坏数据。双写机制通过在双写缓冲区中保留副本，避免了这种问题。
- 提高数据可靠性：即使在系统崩溃或硬件故障的情况下，双写机制也能确保数据的完整性和一致性

4）重做文件区域（文件 #ib_redo0）

功能作用:
- 可以保证内存数据到磁盘中，不会出现损坏的数据，避免数据库异常宕机的数据无法加载情况；(利用CR机制)

5）撤销文件区域（文件 undo_001 undo_002）

功能作用:
- 可以将没有提交的数据，进行撤销操作 rollback
- 可以将redo中的某些记录信息，进行回滚操作

6）临时表空间（文件 ibtmp1 temp_1.ibt）

功能作用:
- 可以存储临时表数据信息（排序操作连表子查询 -- 内存）
- 可以保存用户连接时，查询数据信息
PS:利用临时表，可以快速恢复内存数据，减少IO消耗

7）缓存文件区域（文件 ib_buffer_pool）

功能作用:
- 将内存区域对应buffer_pool中存在的信息（热点数据信息），可以快速恢复

②内存组成部分

buffer pool
- 功能作用：存储磁盘中加载数据页信息
- 调整此区域大小：在my.cnf中配置innodb_buffer_pool_size =
- 设置多少？通常建议设置为服务器物理内存的 60%-80%
- 计算公式：buffer_pool_size = (Total RAM - OS Reserve - Other Processes) * 0.75
AHI
- 功能作用：可以给内存区域的热点数据页建立索引，便于从buffer pool区域快速找到所需数据页
innodb_adaptive_hash_index=ON
Change buffer
- 功能作用：可以减少辅助索引的频繁变更情况，降低对数据业务操作的影响
- 调整此区域大小：Change Buffer 的值范围为 0 到 50，占用 Buffer Pool 空间的默认比例为 25%
  - 在my.cnf中设置innodb_change_buffer_max_size =
  - 根据实际业务场景调整。如果写操作较多且读操作较少，可以适当增大 Change Buffer 的比例（例如 30%-40%），以提高写性能
log buffer

功能作用：会将数据库产生日志信息先保存到日志缓冲区，在把缓冲区数据写入到磁盘，减少IO消耗

十九、数据库数据恢复痛点

1.情况一：日志文件被清理过，可能建库语句所在日志已经丢失（数据损坏量大）

方案一：使用最近一次全备，将误删之前所有的binlog，一并恢复
方案二：利用延时从库，进行数据恢复

利用延时从库进行数据恢复是一种有效的策略，尤其适用于主库发生误操作（如误删除表或库）时。以下是基于延时从库进行数据恢复的详细步骤和方法：

### 1. 配置延时从库
首先需要在从库上配置延时复制。例如，设置从库延迟3600秒（1小时）同步主库数据：

STOP SLAVE;
CHANGE MASTER TO master_delay = 3600;
START SLAVE;

通过 `SHOW SLAVE STATUS\G` 可以查看延时从库的延迟时间是否生效。

### 2. 模拟误操作并定位问题
假设主库发生误操作，如删除了一个表：

DROP TABLE some_table;

此时，延时从库会延迟同步该操作，因此在从库上该表仍然存在。

### 3. 停止从库的SQL线程
在从库上停止SQL线程，以防止误操作被同步：

STOP SLAVE SQL_THREAD;

此时，从库的IO线程仍然运行，继续接收主库的二进制日志。

### 4. 查找误操作的Binlog位置
通过 `mysqlbinlog` 工具查找误操作在主库Binlog中的位置：

mysqlbinlog --base64-output=decode-rows -vvv /path/to/binlog | grep -A 50 -B 50 'DROP TABLE'

确定误操作前后的Binlog位置。

### 5. 设置从库同步到误操作前
将从库的复制延迟设置为0，并指定从库同步到误操作前的位置：

CHANGE MASTER TO master_delay = 0;
START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1234;

等待从库同步到指定位置后，SQL线程会停止。

### 6. 备份从库数据
使用工具如 `mysqldump` 或 `Xtrabackup` 备份从库中的数据：

mysqldump -h from_host -P port -u user -p database_name table_name > backup.sql

或者：

xtrabackup --backup --target-dir=/path/to/backup

### 7. 恢复数据到主库
将备份的数据恢复到主库：

mysql -h master_host -P port -u user -p database_name < backup.sql

在恢复过程中，建议关闭二进制日志以避免再次触发误操作。

### 8. 重新设置延时从库
恢复完成后，重新设置从库的延时复制：

STOP SLAVE;
CHANGE MASTER TO master_delay = 3600;
START SLAVE;

### 注意事项
1. **备份的重要性**：在进行任何操作前，建议对从库进行备份，以防止恢复失败。
2. **延时时间的选择**：延时时间应根据业务需求和误操作的发现时间来设定，常见的延时时间为1小时。
3. **双延时从库策略**：为了进一步提高数据安全性，可以配置多个延时从库，例如两个延时从库的同步时间错开。

通过以上步骤，可以利用延时从库快速恢复误操作的数据，减少数据丢失的风险。

2.情况二：所需日志跨越多个文件，如何进行日志信息的截取（数据损坏量大）

启用GTID（全局事务ID）方式

无论跨越多少个日志文件，每个事务操作的事件ID信息都是唯一且递增的（5.6+引入）

GTID功能技术概述：

GTID（global transation id）称为全局事务（事件）ID，标识binlog日志记录的唯一性； server_uuid(实例标识信息)+事件编号==GTID

GTID功能开启设置

在my.cnf配置文件中添加
gtid_mode=on
enforce_gtid_consistency=on

利用gtid截取binlog信息，从而恢复数据
- 在数据库的每次操作，都会被记录事物编号，编号唯一。

常用参数

include-gtids=事务编号 -- 可以截取需要恢复的事务范围信息

语法：
mysqlbinlog --include-gtids=事务编号 binlog日志文件名
示例：
mysqlbinlog --include-gtids=59617675-f589-11ef-9c2f-000c29141679:1-5 binlog.000005 binlog.000006 binlog.000007 >/tmp/add.sql

exclude-gtids=name -- 连续截取多个范围事务信息时，可以排除中间的某个事务不进行截取

语法：
mysqlbinlog --include-gtids=事务编号 --exclude-gtids=事务编号 binlog日志文件名
示例：
mysqlbinlog --include-gtids=59617675-f589-11ef-9c2f-000c29141679:1-5 binlog.000005 binlog.000006 binlog.000007 --exclude-gtids=59617675-f589-11ef-9c2f-000c29141679:2 >/tmp/add02.sql

skip-gtids -- 在截取后文件中，不记录gtid信息（主要用于将截取后文件进行恢复数据）

PS：在利用gtid截取文件默认恢复数据时，具有幂等特性，已经完成过的操作事件信息不回重复执行

3.情况三：如何从日志文件中恢复单库、单表、或者部分行数据信息（少量数据损坏如何快速修复数据）

方案一：可以利用命令单独截取某个数据库的日志信息；（分库备份+截取某个数据库日志信息）使用情况二中的方法
方案二：利用第三方工具（binlog2sql），实现数据闪回功能

1)下载数据闪回工具（binlog2sql）
https://github.com/danfengcao/binlog2sql

2)安装，编辑requirements.txt设定版本
Mysql版本：8.0.36
[root@db01 ~]# vim requirements.txt
PyMySQL==0.9.3
wheel==0.29.0
mysql-replication==0.13

3)安装依赖,检查版本
[root@db01 ~]# yum install -y python3
[root@db01 ~]# pip3 install -r requirements.txt

[root@db01 ~]# pip3 show pymysql
[root@db01 ~]# pip3 show wheel
[root@db01 ~]# pip3 show mysql-replication

4)应用
[root@db01 ~]# cd /root/binlog2sql-master/binlog2sql
命令格式：
python binlog2sql.py -h <hostname> -P <port> -u <username> -p'<password>' -d 库名 -t 表名 --start-file='mysql-bin.000001' --start-position=4 --sql-type=delete
		-h 连接数据库服务端（服务端地址）
		-P 连接数据库服务端（服务端端口）
		-u 登录数据库服务端（用户名）
		-p 登录数据库服务端（密码）      
		--start-file='binlog.000008'  需要从哪个binlog中读取SQL信息
		-d 指定过滤库信息
		-t 指定过滤表信息
		--sql-type  指定过滤的语句类型
		--start-position：指定起始解析位置
		-B  取误操作撤销后的还原语句
		
示例：
[root@db01 ~]# python3 binlog2sql.py -h 10.0.0.51 -P 3306 -u root -p 123123 -d world -t city --sql-type=delete --start-file='binlog.000019' -B

5)恢复方法
[root@db01 ~]# python3 binlog2sql.py -h 10.0.0.51 -P 3306 -u root -p 123123 -d world -t city --sql-type=delete --start-file='binlog.000019' -B > /tmp/re.sql
[root@db01 ~]# mysql -uroot -p123123 < /tmp/re.sql

PS:在使用该工具时，可能会遇见如下错误

RuntimeError: cryptography is required for sha256_password or caching_sha2_password

这种情况跟密码插件有关

密码插件的作用：可以保证数据库中记录密文密码的加密方式
- mysql_native_password：加密方式比较简单生成密文信息简单
- caching_sha2_password：加密方式比较复杂生成密文信息复杂

错误解决方案

ALTER USER 'your_username'@'your_host' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

面试：数据库数据闪回逻辑

1) 需要扫描binlog文件信息
2) 可以过滤binlog文件信息（过滤-库/表/语句）
3）可以将过滤后的语句进行反向操作（生成反向操作语句）
   insert -- 反向语句 -- delete 
   delete -- 反向语句 -- insert 
   update -- 反向语句 -- 将语句中 条件部分信息和修改部分信息最互换

面试：日常企业进行数据备份方案

全备份：
	数据量增长快：每周全备份一次。
	数据量增长慢：每月全备份一次。
	备份工具：xbk+mdp，从从库备份。
增量备份：
	使用 binlog 进行增量备份。
	全备份周期长：保存更多的增量文件。
	全备份周期短：保存较少的增量文件。
恢复策略：
	在恢复数据时，首先恢复最近一次的全备份，然后依次应用增量备份文件，直到恢复到所需的时间点。

二十、数据库服务事务机制

客户端与服务端在交互过程-存储/调取的过程中，需要保证数据的完整性/一致性

利用Innodb引擎，采用事务机制进行数据存储和调取，可以更好的保证数据的完整性和一致性（安全性）

事务机制有如下几大特性：

A - (atomicity): 原子性

当一系列DML操作语句执行时，要么所有操作都成功，要么所有操作都失败。
具有此特性的原因：数据库中具有事务生命周期机制

@控制事务开始与提交结束

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1为自动控制，0为手动

人为控制事务开始到结束(事务开始: begin；每个事务操作结束：commit / rollback )
- 多条 SQL 语句会被组合成一个事务，直到显式调用 COMMIT 提交事务，或者调用 ROLLBACK 回滚事务。
- 在事务提交之前，所有操作都不会永久生效，可以随时回滚到事务开始之前的状态。
- 适用于需要批量操作、复杂逻辑处理或确保数据一致性的场景
```
begin;
DML01;
DML02;
DML03;
commit  原子特性所有操作都成功

begin;
DML01;  成功  
DML02;  成功 
DML03;  失败		--此次失败会将前面两次成功的操作都rollback回滚
commit/quit   原子特性所有操作都失败
```
自动控制事务开始到结束
- 每次执行单条 SQL 语句（如 INSERT、UPDATE、DELETE）时，MySQL 会自动将该操作作为一个独立的事务提交。
- 这意味着每次操作都会立即生效，不需要手动调用 COMMIT。
- 适用于简单的操作场景，或者不需要显式事务控制的场景。
- 自动触发提交机制
  - 1）DDL DCL语句执行时，会自动触发提交机制
  - 2）开启自动提交功能，dml执行完，会自动触发提交机制
- 自动触发回滚机制
  - 1）语句操作失败会触发回滚机制
  - 2）断开会话连接
  - 3）重启数据库服务

C - (consistency): 一致性

当数据库服务出现异常重启之后，需要保证数据库服务重启前和重启后的数据一致.
是应用DWB文件+CR机制实现的

I - (isolation)：隔离性

控制大量并发访问，对数据库进行查询或存储的冲突问题
- 两个并发：
  - 一个客户端访问进行数据存储操作不断存数据
  - 一个客户端访问进行数据查询操作查询的数据总是变化（分析统计）

①基于数据库隔离级别读隔离（MVCC机制-扩展）

读隔离有以下几种隔离级别：

1.READ UNCOMMITTED -- RU(读未提交)

并行处理能力最强会出现读的问题（脏读不可重复读幻读）

set global transaction_isolation='READ-UNCOMMITTED';
脏读情况：并行多个会话事务读取数据时，可以读取未提交的修改记录（读取脏页信息）
不可重复读：当利用数据表进行数据统计时，每次统计的数据信息不一致
幻读情况：当进行范围数据修改时，其他会话同时插入了范围以内数据信息，导致范围修改结果和修改语句不一致（影响范围数据修改调整删除信息）

2.READ COMMITTED -- RC(读已提交)

会出现读的问题（不可重复读幻读）

set global transaction_isolation='READ-COMMITTED';
脏读情况：已解决
不可重复读：依然存在
幻读情况：依然存在

3.REPEATABLE READ -- RR(重复提交) 默认

会出现读的问题 - 读隔离

set global transaction_isolation='REPEATABLE-READ';
脏读情况：已解决
不可重复读：已解决（便于进行数据统计）
幻读情况：已解决（便于进行数据范围处理）

4.SERIALIZABLE -- SR(串行级别)

并行处理能力最弱会出现读的问题 - 将并发读操作进行锁定

set global transaction_isolation='SERIALIZABLE';
写入操作：会将整个表进行锁定（表级锁）
查看操作：会将整个表进行锁定（表级锁）

②- 基于数据库锁机制写隔离

写隔离有以下几种级别：

RU级别 -- 只有行锁机制
RC级别 -- 只有行锁机制
RR级别 -- 会有行锁机制+间隙锁=邻键锁
SR级别 -- 基于表级锁（只有读锁不会阻塞读锁，其余全部都会堵塞；读锁 -- 阻塞写锁写锁 -- 阻塞读锁写入 -- 阻塞写锁）

Myisam 基于表级锁（读锁 -- 不会阻塞写锁写锁 -- 阻塞写锁）

D -（durability）：持久性

可以保证数据库存储的数据信息，可以更好的保存到磁盘中
innodb_flush_log_at_trx_commit -- 影响持久性-- 影响redo信息记录redo日志
- innodb_flush_log_at_trx_commit=1 -- 每次提交事务都会写入log buffer,并写入redo
- innodb_flush_log_at_trx_commit=0 -- 每次log buffer 和 redo信息的写入，会间隔1s钟；
- innodb_flush_log_at_trx_commit=2 -- 每次提交事务先写入log buffer，每间隔1s，将log buffer信息写入到redo
sync_binlog -- 影响持久性 -- 影响binlog信息记录到binlog文件
- sync_binlog=0 -- 禁止写入binlog信息
- sync_binlog=1 -- log buffer 有新的binlog信息，会马上同步到binlog文件
- sync_binlog=N -- log buffer 有新的binlog信息, 积攒到N个事务之后，才会同步到binlog文件
sql_log_bin-- 设置是否有些事务操作，进行binlog记录（恢复数据信息 / 不需要同步从库数据信息）
- sql_log_bin=1 -- 会做事务操作记录到binlog
- sql_log_bin=0 -- 不会做事务操作记录到binlog
innodb_file_per_table -- 影响表数据保存位置
- innodb_file_per_table=1 -- 表示表数据信息会单独保存到对应独立表空间文件中 t1.ibd
- innodb_file_per_table=0 -- 表示表数据信息会汇总保存到共享表空间文件 ibdate1

面试题：持久性的双一配置

“双一配置”是 MySQL 中用于确保事务持久性和数据一致性的关键设置。通过将 innodb_flush_log_at_trx_commit 和 sync_binlog 都设置为 1，可以最大限度地减少数据丢失的风险，但需要根据实际业务需求权衡性能和安全

posted @ 2025-03-21 16:49 丁志岩阅读(21) 评论(0) 收藏举报

刷新页面返回顶部

丁志岩的运维之路

01-MySQL数据库

MySQL数据库

一、MySQL的安装

1.下载MySQL软件包

2.系统环境准备

3.数据库程序包安装部署（二进制包）

4.初始化数据库

5.编写数据库服务配置文件

6.启动运行数据库服务

①启动方式

②启动日志

二、数据库服务密码管理

1.设置初始密码

①使用shell命令设置

②使用SQL语句设置

2.修改密码信息

①使用shell命令设置

②使用SQL语句设置

3.重置密码信息(遗忘密码)

①关闭数据库服务

②采用安全模式启动数据库（免密登录）

③重置密码信息

④重新正常启动数据库服务

三、数据库服务用户管理

1.创建用户信息

①设置用户主机域/白名单信息方式

②创建用户信息

③用户登录

2.查看用户信息

3.删除用户信息

四、数据库服务权限管理

1.查看所有权限

2.设置用户权限

3.查看用户权限

4.撤销用户权限

5.用户权限授权表

①全局授权信息

②数据库授权信息

③数据表授权信息

五、数据库连接管理

1.数据库连接方法

①本地连接

②远程连接

2.数据库连接方式

①利用客户端命令进行连接

②利用客户端工具进行连接

③利用程序代码连接数据库

六、数据库服务配置管理

1.加载数据库配置文件

2.进程信息

3.编写数据库配置文件

七、数据库服务实例应用

1.功能作用

2.同版本构建多实例

①进行多实例数据库目录初始化

②写多实例配置文件

③运行启动多实例服务

④查看端口号，多实例是否启动

⑤指定套接字文件尝试登录

3.不同版本构建多实例

①下载、安装、初始化

②编写数据库服务配置文件

③启动运行不同版本多实例

八、数据库服务SQL语句分类

1.DDL语句(定义)

1)数据库管理操作语句CADU

2)数据表管理操作语句

3)数据库的别名设置/视图设置

①表别名设置

②列别名设置

③视图设置

2.DCL语句(控制)(三、四)

3.DML语句(操作)

1)数据表相关数据管理

2)插入数据 insert

3)修改数据 update

4)删除数据 delete

4.DQL语句(查询)

1)单表查询数据

③外连接方式，实现多表拼接（索引应用有关）

1.索引结构构建方法（提高数据查询方法）

2)复合索引（联合索引）减少回表次数