MySQL示例 结构体系及基础管理
体系结构
C/S(客户端/服务端)模型介绍
TCP/IP方式(远程、本地):
mysql -uroot -p123 -h 10.0.0.51 -P3306
Socket方式(仅本地):
mysql -uroot -p123 -S /tmp/mysql.sock
实例介绍
实例=mysqld后台守护进程+Master Thread +干活的Thread+预分配的内存
公司=老板+经理+员工+办公室
mysqld程序运行原理
mysqld程序结构
一条SQL语句的执行过程
- 连接层
(1)提供连接协议:TCP/IP 、SOCKET
(2)提供验证:用户、密码,IP,SOCKET
(3)提供专用连接线程:接收用户SQL,返回结果
通过以下语句可以查看到连接线程基本情况
mysql> show processlist;
- SQL层 (重点)
(1)接收上层传送的SQL语句
(2)语法验证模块:验证语句语法
(3)语义检查:判断SQL语句的类型
DDL :数据定义语言(Data Definition Language)
DCL :数据控制语言(Data Control Language)
DML :数据操作语言(Data Manipulation Language)
DQL:数据查询语言(Data Query Language)
(4)权限检查:用户对库表有没有权限
(5)解析器:进行SQL的预处理,产生执行计划
(6)优化器:根据解析器得出的多种执行计划,进行判断,选择最优的执行计划
代价模型:资源(CPU IO MEM)的耗损评估性能好坏
(7)执行器:根据最优执行计划,执行SQL语句,产生执行结果
执行结果:在磁盘的xxxx位置上
(8)提供查询缓存(默认是没开启的),会使用redis tair替代查询缓存功能
(9)提供日志记录(日志管理章节):binlog,默认是没开启的。
- 存储引擎层(类似于Linux中的文件系统)
负责根据SQL层执行的结果,从磁盘上拿数据。将16进制的磁盘数据,交由SQL架构化成表,由连接层的专用线程返回给用户。
逻辑结构
库:库名,库权限,库数据类型等
表(二维表)
列:字段
行:记录
MySQL为了将来数据的准确、规范、有意义,对于传统认知的二维表添加了更多的功能
表属性:权限,存储引擎,字符集
列属性:数据类型、约束、其他属性(默认值,自增长,注释)
物理存储结构引入
库的物理存储结构
用文件系统的目录来存储
表的物理存储结构
- MyISAM(一种引擎)的表:
user.frm:列的相关信息
user.MYD:数据行
user.MYI:索引信息
- InnoDB(默认的存储引擎)的表:
time_zone.frm:存储列相关信息
time_zone.ibd:数据行+索引
表的段、区、页(16k)(了解)
页:最小的存储单元
区:1个或多个连续的页
段:一个表就是一个段,包含一个或多个区
基础管理
用户、权限管理
用户
作用:
登录,管理数据库对象
定义:
用户@‘白名单’
白名单?
允许哪些IP能够登录数据库
支持的方式?
app@’%’ 所有地址
app@‘10.0.0.%’ 10.0.0.0/24 网段的地址
app@‘localhost’ 本地主机
app@‘10.0.0.5%’ 10.0.0.50~10.0.0.59
app@‘10.0.0.0/255.255.254.0’ 子网掩码不是24位的
管理操作:
增:
mysql> create user app@'10.0.0.%' identified by '123';
查:
mysql> select user ,host ,authentication_string from mysql.user
改:
mysql> alter user app@'10.0.0.%' identified by '456';
删:
mysql> drop user app@'10.0.0.%';
权限
介绍
SELECT,INSERT, 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 TABLESPAC
ALL : 以上所有权限,一般是普通管理员拥有的
with grant option:超级管理员才具备的,给别的用户授权的功能
应用用户:SELECT,INSERT, UPDATE, DELETE
权限管理操作:
语法:
需求例子:windows机器的navicat登录到linux中的MySQL,管理员用户。
grant all on 对象 to 用户 identified by '密码';
mysql> grant all on \*.* to root@'10.0.0.%' identified by '123';
对象:
*.* —>chmod 755 -R / ----》针对管理员
app.* chmod 755 -R /app ----->应用用户
app.t1 chmod 755 -R /app/t1
需求:创建一个应用用户app用户,能从windows上登录mysql,并能操作app库。
mysql> grant select ,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';
开发人员用户授权流程:
1.权限
2.对谁操作
3.你从哪来
4.密码要求
提示:8.0在grant命令添加新特性
建用户和授权分开了
grant 不再支持自动创建用户了,不支持改密码
授权之前,必须要提前创建用户。
查看授权:
mysql> show grants for app@'10.0.0.%';
回收权限:
revoke delete on app.* from app@'10.0.0.%';
连接管理
自带客户端命令
mysql 常用参数:
-u 用户
-p 密码
-h IP
-P 端口
-S socket文件
-e 免交互执行命令
< 导入SQL脚本
登录方法一——远程登录:
[root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306
Enter password:
登录方法二——本地登录:
mysql> select @@socket;     查看socket位置
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock
Enter password:
[root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;"
Enter password:
+---------------+-----------+
| user | host |
+---------------+-----------+
| abc | 10.0.0.% |
| app | 10.0.0.% |
| root | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
[root@db01 ~]#
[root@db01 ~]# mysql -uroot -p <world.sql
Enter password:
[root@db01 ~]#
多种启动方式介绍
提示:
以上多种方式,都可以单独启动MySQL服务
mysqld_safe和mysqld一般是在临时维护时使用。
另外,从Centos 7系统开始,支持systemd直接调用mysqld的方式进行启动数据库
初始化配置
初始化配置的方法
预编译
配置文件
命令行
初始配置文件
- 初始化配置文件的默认读取路径
[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
- 初始化配置文件读取顺序
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
默认情况下,MySQL启动时,会依次读取以上配置文件,如果有重复选项,会以最后一个文件设置的为准。
但是,如果启动时加入了–defaults-file=xxxx时,以上的所有文件都不会读取
- 配置文件的书写方式
[标签]
配置项=xxxx
标签类型:服务端、客户端
服务器端标签:
[mysqld]
[mysqld_safe]
[server]
客户端标签:
[mysql]
[mysqldump]
[client]
- 配置文件的示例展示:
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\\d]>
多实例的应用
准备多个目录
mkdir -p /data/330{7,8,9}/data
准备配置文件
vim /data/3307/my.cnf
[mysqld]
basedir=/data/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
vim /data/3308/my.cnf
[mysqld]
basedir=/data/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
vim /data/3309/my.cnf
[mysqld]
basedir=/data/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.lo
port=3309
server_id=9
初始化三套数据
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/data/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/data/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/data/mysql
systemd管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service
vim mysqld3307.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
授权
chown -R mysql.mysql /data/*
启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
验证多实例
[root@db01 ~]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1231/sshd
tcp6 0 0 :::3307 :::* LISTEN 2749/mysqld
tcp6 0 0 :::3308 :::* LISTEN 2784/mysqld
tcp6 0 0 :::3309 :::* LISTEN 2816/mysqld
tcp6 0 0 :::22 :::* LISTEN 1231/sshd
mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"