数据库的使用

相关名词

data：数据
db：数据库，简称为数据的集合
dba：数据库管理员
dbms：数据库管理系统，通常指MySQL或者其他数据库管理软件
dbs：数据库系统，上述所有硬件软件人员数据等全部的集合

数据库管理系统种类

RDBMS（关系型数据库）：以多张二维表的方式来存储，又给多张表建立了一定的关系

NoSQL（非关系型数据库）：多以json格式进行存储数据

MySQL体系结构管理

客户端与服务器模型

mysql是一个典型的C/S服务结构
mysql自带的客户端程序（/application/mysql/bin）
mysql：客户端命令行登录
mysqladmin：执行数据库命令
mysqldump：备份数据库
mysqld一个二进制程序，后台的守护进程

应用连接MySQL方式
1.远程连接：TCP/IP，PHP应用可安装于另一台机器；
mysql -uroot -p123456 -h127.0.0.1
2.socket连接，默认连接方式，本地连接，快速无需建立TCP连接，通过/application/mysql/tmp下的mysql.sock文件
mysql -uroot -p123456 –protocol=socket

mysqld服务结构

连接层：
验证用户的合法性（IP、端口、用户名）
提供两种连接方式（socket、TCP/IP）
验证操作权限
提供一个于SQL层交互的专用线程

SQL层：
接收连接层传来的SQL语句
检查语句
检查语义
解析器，解析SQL语句，生成多种执行计划
优化器，根据多种执行计划，选择最优方式
执行器，执行优化器传来的最优方式SQL（提供与存储引擎交互的线程、接收返回数据，优化成表的形式返回SQL）
将数据存入缓存
记录日志，binlog

存储引擎
接收上层的执行结构
取出磁盘文件和相应数据
返回给SQL层，结构优化之后生成表格，由专用线程返回给客户端
存储引擎分为多种种类
mysql自带的表部分使用的是myisam

自己创建一个表，编译的时候默认指定使用innodb

mysql逻辑结构

数据库中的逻辑对象与Linux进行对比

MySQL	Linux
库	目录
show databases;	ls -l /
use mysql	cd /mysql
表	文件
show tables;	ls
二维表=元数据+真实数据行	文件=文件名+文件属性

mysql物理结构

MySQL最底层的物理结构是数据文件，也就是说与存储引擎层打交道的文件-数据文件

在MySQL中数据表与段、区、页的关系住哟啊体现在存储引擎层，它们构成了逻辑表到物理存储的层级映射关系。不同存储引擎的实现略有差异，以MySQL默认的InnoBD存储引擎为例

具体层级关系：InnoDB中，数据表的物理存储遵循“表->表空间->段->区->页”的层级结构，其中“段、区、页”是物理存储的核心单位
数据表（逻辑对象） -> 表空间（数据文件容器）-> 段（不同类型数据的集合） -> 区（连续页的分组） -> 页（最小存储单位）

具体对应关系
1.数据表与表空间：数据表是逻辑概念，对应create table定义的结构和数据。表空间是物理容器，用于存储数据表的所有数据（包括段、区、页）。InnoDB默认使用共享表空间（ibdata1）或独立表空间（.ibd文件）存储数据。一个数据表的数据最终会存储在一个或多个表空间文件中。

show variables like ‘datadir’查看真实数据目录

2.表空间与段：表空间由多个段组成，段是“同类型数据的集合”。一个数据表至少包含2个核心段；
数据段：存储表中的实际数据行；
索引段：存储表的索引数据；
若表有多个索引（如主键索引、二级索引），则每个索引会对应独立的索引段。分区表中，每个分区会单独生成一套数据段和索引段，实现分区隔离。
3.段与区：区是段的分配单位，一个段由多个连续的区组成。区的大小固定为1MB（InnoDB中1个区=64 个16KB的页或128个8KB的页，由页大小决定）。段扩展时，会以“区”为单位向表空间申请空间，避免频繁分配小单位导致的碎片。
3.区与页：页是InnoDB最小IO单位，一个区由连续的多个页组成。页的大小可配置（默认16KB，可选4KB、8KB、32KB、64KB），由innodb_page_size参数指定。页是数据读写的基本单位即使只修改一行数据，InnoDB也会加载整个页到内存，修改后再写回磁盘，避免全表重写的高代价。

MySQL用户权限管理

mysql用户基础操作

创建用户：create user user01@’192.168.75.%’ identified by ‘123456’;
查看用户：select user,host from mysql.user;
查看数据库：select database()；
删除用户：drop user user01@’192.168.75.%’;
修改密码：set password=PASSOWORD(‘123456’)
update user set password=PASSWORD(‘user01′) where user=’root’ and host=’localhost’;
grant all privileges on *.* to user01@’192.168.175.%’ identified by ‘123456’;

MySQL用户定义：username@‘主机域’
主机域：可以理解为是mysql登录的白名单
主机域格式：
10.1.1.12
10.1.0.1% <-> 10.1.0.1 10.1.0.10-19 10.1.0.100-199
10.1.0% <-> 10.1.0.0/24
10.1.%.%
%
localhost
192.168.1.1/255.255.255.0

忘记root密码
[root@Lain test]# systemctl stop mysqld
[root@Lain test]# mysqld_safe –skip-grant-tables –skip-networking #skip-networking禁止掉3306端口，不允许网络登录
mysql登录一下
update user set password=PASSWORD(‘123456′) where user=’root’ and host=’localhost’;
flush privileges;#刷新

用户权限介绍

grant（授权）all privileges on *.*(任意库的任意表) to user01@192.168.75.135(归属) identified（密码） by “123”；授权超级管理员用户

常见权限

INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,  PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

权限设定：需要对哪些库、表进行操作、从哪里连过来、用户名有没有要求、密码要求、发邮件

一般开发创建用户权限

grant select,update,delete,insert on *.* to 'user01'@'192.168.175.%' identified by '123456';

数据库连接

Mysql自带连接工具

musql -u root -p 123456 -h 127.0.0.1 -P 3306 -e "show databasese;"

对应参数
-u:指定用户
-p:指定密码
-h:指定主机
-P:指定端口
-S:指定sock
-e:指定SQL

免密登录

vim /etc/my.cnf
[client]
user = root
password = 123456

Mysql启动流程

启动
/use/lib/systemd/mysqld.service告诉系统从哪执行mysqld，mysld_safe是个脚本，mysqld才是binary

/etc/init.d/mysqld start -> mysqld_safe -> mysqld
这个三层调用链体现了“分层设计”的思想
/etc/init.d/mysqld：标准化接口，适配系统的服务管理规范
mysqld_safe：中间层守护，提供安全保障和故障恢复
mysqld：核心功能实现，专注于数据库服务本身

Mysql实例初始化配置

在命令行设定启动初始化配置

--skip-grant-tables 
--skip-networking
--datadir=/application/mysql/data
--basedir=/application/mysql
--defaults-file=/etc/my.cnf
--pid-file=/application/mysql/data/db01.pid
--socket=/application/mysql/data/mysql.sock
--user=mysql
--port=3306
--log-error=/application/mysql/data/db01.err

初始化配置文件/etc/my.conf，文件读取顺序
/etc/my.conf
/etc/mysql/my.conf
$MYSQL_HOME/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量 /application/mysql/my.conf）
defaults-extra-file （类似include）
~/my.cnf（隐藏文件）

–defaults-file默认配置文件
如果使用./bin/mysqld_safe 守护进程启动mysql数据库时，使用了 –defaults-file=<配置文件的绝对路径>参数，这时只会使用这个参数指定的配置文件。

mysql多实例配置

创建数据目录 -> 创建配置文件 -> 修改目录权限 -> 初始化数据 -> 启动多实例 -> 设置每个实例的密码 -> 查看server_id -> 进入单独的mysql实例

mkdir -p /data/330{7..9}

touch /data/330{7..9}/my.cnf
touch /data/330{7..9}/mysql.log

vim /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
log-bin=/data/3307/mysql-bin
server_id=7
port=3307
[client]
socket=/data/3307/mysql.sock

chown -R mysql.mysql /data/330*

/application/mysql/scripts/mysql_install_db \
--user=mysql \
--defaults-file=/data/3307/my.cnf \
--basedir=/application/mysql --datadir=/data/3307/data


mysqld_safe --defaults-file=/data/3307/my.cnf &

mysqladmin -S /data/3307/mysql.sock -uroot password '123456'-p

mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"

mysqladmin -S /data/3307/mysql.sock -uroot shutdown

mysql客户端工具