MySQL体系结构
体系结构
C/S(客户端/服务端)模型
mysql支持两种方式
- TCP/IP(远程)
- mysqk -uroot -pxxx -h 192.168.1.1 -P3306
- Socket(本地)
- mysqk -uroot -pxxx -S /tmp/mysql.sock
mysql实例的构成
mysqld+master thread + 干活的thread +预分配的内存
SQL语句引入
结构化的查询语言
- DQL 数据查询语言
- DDL 数据定义语言
- DML 数据操作语言
- DCL 数据控制语言
mysqld守护进程运行结构与原理
一条sql语句执行原理(mysqld处理sql语句的过程)
语言描述,要是描述的不正确,请指正一下
SQL语句先是用远程或者本地的方式连接到msyqld守护进程的连接层,连接层验证身份信息,确认无误后,通过一个专属线程连接用户,连接层把sql语句移交给sql层,sql层接收到sql语言后,显示进行sql语法检查和sql_MODE检查,透过后检查SQL语义,分析判断到底是DLL,DML,DCL,DQL,等哪一种,进行sql语句的预处理,把sql语句解析多种解决方案,优化这些方案并进行评估,通过优化器算法使cpu,mem,IO消耗最小化,优化器会选择一条最佳的执行方案,通过执行器,执行sql语句,sql层还提供数据查询缓存,把这些过程通过日志记录再审计日志和通用日志上,把执行结构发给存储引擎层,存储引擎收到sql层执行的结构去磁盘上找到对应的数据,这些数据是16进制的,返还给sql层,二位图标的形式,通过线程展现出来
mysql逻辑存储结构
库(相当于目录)
create database kk charset utf8mb4;-------------->mkdir xxx
show databases; --------------------->ls /
usr kk----------------------------cd xxx
表(相当于文件)
列(字段)
列属性
数据行(记录)
表总属性
mysql物理存储结构
数据存储位置/data/mysql/data
库
- 直接存储在找个目录里/data/mysql/data
表:
- MyISAM(存储引擎)【相当于ext2】位置:
- /data/mysql/data/mysql/
- user.frm:存储的表结构(列,列属性)
- user.MYD:存储数据记录
- user.MYI:存储索引
- InnoDB(存储引擎)[相当于xfs]位置
- /data/mysql/data/mysql/
- time_zone.frm:存储表结构(列,列属性)
- time_zone.ibd:存储数据记录和数据索引【相当于一块磁盘】
- 存储引擎会将找个time_zone.ibd做格式化分成16Kb的64个格子
mysql> select 16*64; +-------+ | 16*64 | +-------+ | 1024 | +-------+ 1 row in set (0.00 sec)
- 最多就是1M,调32Kb就会分32个格子
- 一个页叫page,默认16Kb,
- 一个区就是所有的页加起来,默认64个页加起来叫一个区
- mysql的表,会有多个区构成,把多个区构成一个段,一个表就是一个段
- bdata1: 数据字典信息
mysql中什么是段,区,页?
inoodb的存储引擎
一般情况下(非分区表)
一个页就是page,默认16Kb,总共有64个页
页的总和就是一个区,默认是64个页的总和,大小为1Mb
一个段就是一个表,由多个区构成一张表一个段
MySQL用户和权限管理
用户的作用:
- 登陆mysql
- 管理mysql
用户的定义:
- 用户名@'白名单'
- kk@'%'
- kk@'localhost'
- kk@'127.0.0.1'
- kk@'10.0.0.%'
- kk@'10.0.0.5%'----10.0.0.[50-59]
- kk@'10.0.0.0/255.255.254.0'----添加具体的子网掩码
用户的操作:
- 创建用户及用户密码:
create user kk@'192.168.5.%' identified by '123';
mysql> create user kk@'192.168.5.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
- 查看用户:
select user,host from mysql.user;
mysql> select user,host from mysql.user;
+---------------+-------------+
| user | host |
+---------------+-------------+
| kk | 192.168.5.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-------------+
4 rows in set (0.00 sec)
8.0版本以前,可以自动创建用户并授权,以后版本就用不了这种方式了
grant all on *.* to kk@'10.0.0.%' identified by '123';
- 修改用户
改不了用户名,删了重建,修改用户密码
alter user kk@'192.168.5.%' identified by '1234';
mysql> alter user kk@'192.168.5.%' identified by '12'; Query OK, 0 rows affected (0.00 sec) mysql> alter user kk@'192.168.5.%' identified by '123'; Query OK, 0 rows affected (0.01 sec)
- 删除用户
drop user kk@'192.168.5.%'
mysql> drop user kk@'192.168.5.%';
Query OK, 0 rows affected (0.00 sec)
权限管理
权限列表:
- ALL
- with grant option(给别人授权的权限,本地管理员,必须加到末尾)
- SELECT,INSERT,UPDATE,DELETE(应用用户增删改查)
ALL+whit grant option =超级管理员
ALL=管理员
SELECT,INSERT,UPDATE,DELETE应用用户
授权命令:
grant all on *.* to kk@'192.168.5.%' identified by '123';
grant 权限 on 作用目标 to 用户 identified by '密码';
作用目标:
- *.*(递归,所有库所有表授权)
- 库.*(某个库下所有的表授权)
- kk(库).t1(对kk库里的t1表进行授权)
授权需求:
1.创建管理员用户root,可以通过192.168网段,管理数据库
grant all on *.* to root@'192.168.0.%' identified by '123' whit grant option;
2.创建一个应用用户,可以通过192.168网段,在kk库下所有表进行增删改查
grant SELECT,INSERT,UPDATE,DELETE on kk.* to ww@'192.168.0.%' identified by '123'
mysql> grant SELECT,INSERT,UPDATE,DELETE on *.* to kk@'192.168.5.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
权限的查询:
show grants for kk@'192.168.5.%'
mysql> show grants for kk@'192.168.5.%';
+-------------------------------------------------------------------+
| Grants for kk@192.168.5.% |
+-------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'kk'@'192.168.5.%' |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
权限的回收:
revoke delete on *.* from kk@'192.168.5.%'
mysql> show grants for kk@'192.168.5.%';
+-------------------------------------------------------------------+
| Grants for kk@192.168.5.% |
+-------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'kk'@'192.168.5.%' |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke delete on *.* from kk@'192.168.5.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for kk@'192.168.5.%';
+-----------------------------------------------------------+
| Grants for kk@192.168.5.% |
+-----------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO 'kk'@'192.168.5.%' |
+-----------------------------------------------------------+
1 row in set (0.01 sec)
生成环境开用户:
如何沟通:
- 1.是否有邮件批复
- 2.对哪些库和表做操作
- 3.做什么操作
- 4.从什么地址登陆
MySQL连接管理
提前将用户授权做好
mysql命令:
TCP/IP:
- mysql -uroot -p -h 192.168.5.36 -P3306
[root@mysql ~]# mysql -ukk -p123 -h 192.168.5.36 -P3306
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 22
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
socket:
- mysql -uroot -p123 -S /tmp/mysql.sock(不加就是默认的方式)
[root@mysql ~]# mysql -uroot -p123 -S /tmp/mysql.sock
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 23
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
登陆的区别,可以明显看出区别
mysql> show processlist;
+----+------+-------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-------------+------+---------+------+----------+------------------+
| 23 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 24 | kk | mysql:59954 | NULL | Sleep | 12 | | NULL |
+----+------+-------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
同时出现-h和-S,优先走的远程的方式
客户端工具
navicat
sqlyog
多实例管理
生产中应用广泛,一台机器跑多台数据库
示例:
准备多个目录
- mkdir -p /data/330{7,8,9}/data
[root@mysql ~]# mkdir -p /data/330{7,8,9}/data
准备多个配置文件
3307配置文件:
cat > /data/3307/my.cnf <<x
[mysqld]
basedir=/server/tools/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=9
log_bin=/data/3307/mysql-bin
x
[root@mysql ~]# cat > /data/3307/my.cnf <<x > [mysqld] > basedir=/server/tools/mysql > datadir=/data/3307/data > socket=/data/3307/mysql.sock > log_error=/data/3307/mysql.log > port=3307 > server_id=9 > log_bin=/data/3307/mysql-bin > x
3308配置文件:
cat > /data/3308/my.cnf <<x
[mysqld]
basedir=/server/tools/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=9
log_bin=/data/3308/mysql-bin
x
[root@mysql ~]# cat > /data/3308/my.cnf <<x > [mysqld] > basedir=/server/tools/mysql > datadir=/data/3308/data > socket=/data/3308/mysql.sock > log_error=/data/3308/mysql.log > port=3308 > server_id=9 > log_bin=/data/3308/mysql-bin > x
3309配置文件:
cat > /data/3309/my.cnf <<x
[mysqld]
basedir=/server/tools/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3308
server_id=9
log_bin=/data/3309/mysql-bin
x
[root@mysql ~]# cat > /data/3309/my.cnf <<x > [mysqld] > basedir=/server/tools/mysql > datadir=/data/3309/data > socket=/data/3309/mysql.sock > log_error=/data/3309/mysql.log > port=3308 > server_id=9 > log_bin=/data/3309/mysql-bin > x
初始化数据库
mysqld --initialize --user=mysql --basedir=/server/tools/mysql --datadir=/data/3307/data
mysqld --initialize --user=mysql --basedir=/server/tools/mysql --datadir=/data/3308/data
mysqld --initialize --user=mysql --basedir=/server/tools/mysql --datadir=/data/3309/data
生成启动脚本分别写三个类推
cp -a /xxx/mysql/support-files/mysql.server /etc/init.d/mysqld3307
chkconfig --add /etc/init.d/mysqld3307
chmod +x /etc/init.d/mysqld3307
chkconfig mysqld3307 on
chkconfig --list mysqld3307
授权:
chown -R mysql.mysql /data/

评论