1.体系结构

1.1C/S(客户端/服务端)模型介绍1.webp

  1. TCP/IP方式（远程、本地）：
  2. mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
  3. Socket方式(仅本地)：
  4. mysql -uroot -poldboy123 -S /tmp/mysql.sock

1.2实例介绍

  1. 实例=mysqld后台守护进程+Master Thread +干活的Thread+预分配的内存
  2. 公司=老板+经理+员工+办公室

1.3mysqld程序运行原理

1.3.1mysqld程序结构

2.webp

1.3.2一条SQL语句的执行过程

1.3.2.1连接层

  2. (1)提供链接协议:TCP/IP,SOCKET
  3. (2)提供验证:用户,密码,IP,SOCKET
  4. (3)提供专用连接线程:接收用户SQL,返回结果
  5. 通过以下语句可以查看到连接线程基本情况
  6. mysql>show processlist;

1.3.2.2 SQL层(重点)

```sql

(1)接收上层传送的SQL语句 (2)语法验证模块:验证语句语法,是否满足SQL_MODE (3)语义检查:判断SQL语句的类型 DDL:数据定义语言 DCL:数据控制语言 DML:数据操作语言 DQL:数据查询语言 … (4)权限检查:用户对库表有没有权限 (5)解析器:对语句执行前,进行预处理,生成解析树(执行计划),说白了就是生成多种执行方案 (6)优化器:根据解析器得出的多种执行计划,进行判断,选择最优的执行计划 代价模型:资源(CPU IO MEM)的耗损评估性能好坏 (7)执行器:根据最优执行计划,执行SQL语句,产生执行结果 执行结果:在磁盘的XXXX位置上 (8)提供查询缓存(默认是没开启的),会使用radis tair 替代查询缓存功能 (9)提供日志记录(日志管理章节):binlog,默认是没有开启的

  1. 1.3.2.3存储引擎层(类似于linux中的文件系统)
  2. ```sql
  3. 负责根据SQL层执行的结果,从磁盘上拿数据.
  4. 将16进制的磁盘数据,交由SQL结构化化成表
  5. 连接成的专用线程返还给用户

1.4逻辑结构

3.png

1.4.1库

  1. 库名,库属性

1.4.2表

  1. 表名
  2. 属性
  3. 列:列名(字段),列属性(数据类型,约束等)
  4. 数据行(记录)

1.5物理存储结构引入

4.png

1.5.1 库的物理存储结构

  1. 用文件系统的目录来存储

1.5.2表的物理存储结构

  1. MyISAM（一种引擎）的表：
  2. -rw-r----- 1 mysql mysql   10816 Apr 18 11:37 user.frm
  3. -rw-r----- 1 mysql mysql     396 Apr 18 12:20  user.MYD
  4. -rw-r----- 1 mysql mysql    4096 Apr 18 14:48 user.MYI
  6. InnoDB(默认的存储引擎)的表：
  7. -rw-r----- 1 mysql mysql    8636 Apr 18 11:37 time_zone.frm
  8. -rw-r----- 1 mysql mysql   98304 Apr 18 11:37 time_zone.ibd
  9. time_zone.frm：存储列相关信息
  10. time_zone.ibd：数据行+索引

1.5.3表的段、区、页

  1. 页：最小的存储单元，默认16k
  2. 区：64个连续的页，共1M
  3. 段：一个表就是一个段，包含一个或多个区

2.基础管理

2.1用户，权限管理

2.1.1用户

作用：

  1. 登录，管理数据库逻辑对象

定义：

  1. 用户名@'白名单'
  2. 白名单的支持方式?
  3. gaomeng@'10.0.0.%'
  4. gaomeng@'%'
  5. gaomeng@'10.0.0.200'
  6. gaomeng@'localhost'
  7. gaomeng@'db01'
  8. gaomeng@'10.0.0.5%'
  9. gaomeng@'10.0.0.0/255.255.254.0'

管理操作

  1. 增:
  2. mysql> create user gaomeng@'10.0.0.%' identified by '123';
  3. 查:
  4. mysql> desc mysql.user;
  5. mysql> select user,host,authentication_string from mysql.user;
  6. 改:
  7. mysql> alter user gaomeng@'10.0.0.%' identified by '456';
  8. 删:
  9. mysql> drop user gaomeng@'10.0.0.%';

2.1.2 权限

权限管理操作

  1. grant all on db01.* to gaomeng@'10.0.0.%';

常用权限介绍:

  1. ALL:
  2. 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 TABLESPACE
  3. ALL : 以上所有权限，一般是普通管理员拥有的
  4. with grant option：超级管理员才具备的，给别的用户授权的功能

权限作用范围:

  1. *.*        ----->管理员用户
  2. db01.*     ----->开发和应用用户
  3. db01.t1

需求1 :windows机器的navicat登录到linux中的MySQL,管理员用户.

  1. mysql> grant all on *.* to admin@'10.0.0.%' identified by '123456';

需求2 : 创建一个应用用户app,能从windows上登录MySQL,并能操作db01库

  1. mysql> grant select,update,insert,delete on db01.* to app@'10.0.0.%' identified by '123';

2.1.3开发人员用户授权流程:

  1. 1.权限
  2. 2.对哪个库进行操作
  3. 3.开发人员所在的ip 地址段
  4. 4.密码要求

2.1.4提示:8.0在grant命令添加新特性

  1. 建用户和授权分开
  2. grant不再支持自动创建用户,不支持改密码
  3. 授权之前,必须要提前创建用户

2.1.5查看授权

  1. show grants for app@'168.1.4.%';

2.1.6回收权限

  1. revoke delete on app.'10.0.0.%';

2.1.7本地管理员用户密码忘记

  1. mysqld_safe --skip-grant-tables --skip-networking &
  2. mysql> flush privileges;
  3. mysql>  alter user root@'localhost' identified by '123456';

2.2连接管理

2.2.1自带客户端命令

mysql常用参数:

  1. -u                       用户
  2. -p                       密码
  3. -h                       IP
  4. -P                       端口
  5. -S                      socket文件
  6. -e                      免交互执行命令
  7. <                       导入SQL脚本
  9. [root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306
  10. Enter password:
  11. mysql> select @@socket;
  12. +-----------------+
  13. | @@socket        |
  14. +-----------------+
  15. | /tmp/mysql.sock |
  16. [root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock
  17. Enter password:
  18. [root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;"
  19. Enter password:
  20. +---------------+-----------+
  21. | user          | host      |
  22. +---------------+-----------+
  23. | abc          | 10.0.0.%  |
  24. | app          | 10.0.0.%  |
  25. | root          | 10.0.0.%  |
  26. | mysql.session | localhost |
  27. | mysql.sys    | localhost |
  28. | root          | localhost |
  29. +---------------+-----------+
  30. [root@db01 ~]#
  31. [root@db01 ~]# mysql -uroot -p <world.sql
  32. Enter password:
  33. [root@db01 ~]#

2.3多种启动方式介绍
多种启动方式介绍.png
提示:

  1. 以上多种方式，都可以单独启动MySQL服务
  2. mysqld_safe和mysqld一般是在临时维护时使用。
  3. 另外，从Centos 7系统开始，支持systemd直接调用mysqld的方式进行启动数据库

2.4 初始化配置

2.4.0 作用

  1. 控制MySQL的启动
  2. 影响到客户端的连接

2.4.1 初始化配置的方法

  1. 预编译
  2. **配置文件(所有启动方式)**
  3. 命令行参数 (仅限于 mysqld_safe mysqld)

2.4.2 初始配置文件

初始化配置文件的默认读取路径

  1. [root@db01 ~]# mysqld --help --verbose |grep my.cnf
  2. /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
  3. 注:
  4. 默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。
  5. 但是，如果启动时加入了--defaults-file=xxxx时，以上的所有文件都不会读取.

配置文件的书写方式:

  1. [标签]
  2. 配置项=xxxx
  4. 标签类型：服务端、客户端
  5. 服务器端标签：
  6. [mysqld]
  7. [mysqld_safe]
  8. [server]
  10. 客户端标签：
  11. [mysql]
  12. [mysqldump]
  13. [client]
  15. 配置文件的示例展示：
  16. [root@db01 ~]# cat /etc/my.cnf
  17. [mysqld]
  18. user=mysql
  19. basedir=/app/mysql
  20. datadir=/data/mysql
  21. socket=/tmp/mysql.sock
  22. server_id=6
  23. port=3306
  24. log_error=/data/mysql/mysql.log
  25. [mysql]
  26. socket=/tmp/mysql.sock
  27. prompt=Master [\\d]>

2.5 多实例的应用

2.5.1 准备多个目录

  1. mkdir -p /data/330{7,8,9}/data

2.5.2 准备配置文件

  1. cat > /data/3307/my.cnf <<EOF
  2. [mysqld]
  3. basedir=/app/mysql
  4. datadir=/data/3307/data
  5. socket=/data/3307/mysql.sock
  6. log_error=/data/3307/mysql.log
  7. port=3307
  8. server_id=7
  9. log_bin=/data/3307/mysql-bin
  10. EOF
  12. cat > /data/3308/my.cnf <<EOF
  13. [mysqld]
  14. basedir=/app/mysql
  15. datadir=/data/3308/data
  16. socket=/data/3308/mysql.sock
  17. log_error=/data/3308/mysql.log
  18. port=3308
  19. server_id=8
  20. log_bin=/data/3308/mysql-bin
  21. EOF
  23. cat > /data/3309/my.cnf <<EOF
  24. [mysqld]
  25. basedir=/app/mysql
  26. datadir=/data/3309/data
  27. socket=/data/3309/mysql.sock
  28. log_error=/data/3309/mysql.log
  29. port=3309
  30. server_id=9
  31. log_bin=/data/3309/mysql-bin
  32. EOF

2.5.3 初始化三套数据

  1. mv /etc/my.cnf /etc/my.cnf.bak
  2. mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/app/mysql
  3. mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/app/mysql
  4. mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/app/mysql

2.5.4 systemd管理多实例

  1. cd /etc/systemd/system
  2. cp mysqld.service mysqld3307.service
  3. cp mysqld.service mysqld3308.service
  4. cp mysqld.service mysqld3309.service
  6. vim mysqld3307.service
  7. ExecStart=/app/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
  8. vim mysqld3308.service
  9. ExecStart=/app/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
  10. vim mysqld3309.service
  11. ExecStart=/app/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

2.5.5 授权

  1. chown -R mysql.mysql /data/*

2.5.6 启动

  1. systemctl start mysqld3307.service
  2. systemctl start mysqld3308.service
  3. systemctl start mysqld3309.service

2.5.7 验证多实例

  1. netstat -lnp|grep 330
  2. mysql -S /data/3307/mysql.sock -e "select @@server_id"
  3. mysql -S /data/3308/mysql.sock -e "select @@server_id"
  4. mysql -S /data/3309/mysql.sock -e "select @@server_id"