mysql从入门到放弃之数据库体系结构与管理

文章目录

前言
一、体系结构
- 1、mysql c/s结构介绍
- 2、mysql实例组成
- 3、mysqld程序运行原理
- - 3.1、mysqld守护进程结构
  - 3.2、引入sql语句结构化的查询语言
  - 3.3、探索一条SQL语句的执行过程
二、mysql逻辑存储结构
三、mysql物理存储结构
- - 3.1、innodb存储引擎的段、区、页之间的关系
四、mysql基础管理
- - 4.1、用户管理
  - 4.2、权限管理
  - 4.3、连接管理
五、mysql多实例管理

前言

第一篇文章中主要学习了mysql二进制的基本安装及数据库初始化等操作,本篇文章主要了解mysql的体系结构和管理，例如: mysql的实例组成、逻辑存储结构、物理存储结构等方面展开学习

提示：以下是本篇文章正文内容，下面案例可供参考

一、体系结构

1、mysql c/s结构介绍

在这里插入图片描述

从上图可知,mysql有两种连接方法
	第一种:
		TCP/IP ： mysql -uroot -p -h IP地址 -P 端口
	第二种: 如果你看了我第一篇文章,就大概了解了socket连接方法的配置
		Socket：  mysql -uroot -p -S /data/mysql3306/data/run/mysql.sock

2、mysql实例组成

	mysqld守护进程 + master thread + 干活的thread + 预分配内存

3、mysqld程序运行原理

3.1、mysqld守护进程结构

mysqld守护进程结构

3.2、引入sql语句结构化的查询语言

     DQL  数据查询语言
     DDL  数据定义语言
     DML  数据操作语言
     DCL  数据控制语言

3.3、探索一条SQL语句的执行过程

名称	作用
应用程序层	通过TCP/IP或者socket连接数据库
连接层	1、提供连接协议TCP/IP或者socket 2、验证用户名、密码、端口、地址合法性 3、开启专用连接线程，show processlist可以查看到线程(接受sql语句、返回执行结构) 4、将语句交给下一层
SQL层	1、接受sql语句 2、语法检查和sql_mode 3、语义检查(属于哪种查询语言)、权限检查 4、解析语句,生成多种执行计划树 5、通过优化器算法进行评估(cpu、io、内存、执行时间) 6、优化器选择最优的执行方法 7、语句执行器，真正执行sql语句 8、提供查询缓存(默认不开启) 9、日志记录(默认未开启)审计日志、通用日志、binlog日志
存储引擎层	磁盘、内存、网络，1、根据sql执行结果,去磁盘上找对应的数据。找到磁盘上16进制的数据，再次返回到sql层,结构化成表的方式,再由连接层线程展现处理

二、mysql逻辑存储结构

在这里插入图片描述

 库         --->                类似 linux目录
 表         --->                类似 linux文件       
    列(字段)
    行(记录)
    表属性 (元数据)
    列属性

三、mysql物理存储结构

在这里插入图片描述

库  
	使用文件系统的目录来表示  (在数据库的数据目录下创建一个目录，然后可以再数据库中使用show databases查到)即数据目录下的目录名就是库名,如下图所示
表
    InnoDB (默认存储引擎)
       time_zone.frm  存储的表结构
       time_zone.ibd  存储的数据记录和索引
       ibdata1        数据字典信息
    MyISAM
       user.frm  存储的表结构(列、列属性)
       user.MYD  存储的数据记录
       user.MYI  存储索引
如下图所示

在这里插入图片描述

3.1、innodb存储引擎的段、区、页之间的关系

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

一般情况下(非分区表)
   一个表就是一个段、一个段由多个区构成、一个区再由(16k)* 64个连续的页 总计1M大小

四、mysql基础管理

4.1、用户管理

用户的作用？
	登录mysql、管理mysql对象(库表)

用户的定义
	用户名@'白名单'
例如:
	mysql> select user,host from mysql.user;
			+---------------+-----------+
			| user          | host      |
			+---------------+-----------+
			| test          | %         |
			| mysql.session | localhost |
			| mysql.sys     | localhost |
			| root          | localhost |
			+---------------+-----------+

用户的操作
	mysql> create user test@'192.168.0.%' identified by '123';  创建用户并设置密码
    mysql> select user，host from mysql.user;                   查看用户
    mysql> grant all on *.* to test@'%' identified by '123';    创建用户并授权、设置密码(仅限于mysql8.0之前的版本)
    mysql> alter user test@'%' identified by '123';             修改用户密码
    mysql> drop  user test@'%';                                 删除用户

4.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

授权命令
	 with grant option：超级管理员才具备的，给别的用户授权的功能
	 
     grant 权限 on 作用目标 to 用户@'白名单' identified by '密码' with grant option;  
     
     作用目标示例:
        *.*         所有库、所有表
        test.*      test库下的所有表
        test.user   test库下的user表
        
     授权示例:
        创建一个管理员用户root,可以通过10网段管理数据库
           grant all on *.* to root@'10.0.0.%' identified by '123';
           
        创建一个应用用户test,可以通过10网段,对test库下的所有表进行增删改查
           grant select,insert,update,delete on test.* to test@'10.0.0.%' identified by '123';

权限回收：
    mysql> show grants for test@'10.0.0.%';  查询用户权限
    	+-----------------------------------------------------------------------+
		| Grants for test@10.0.0.%                                              |
		+-----------------------------------------------------------------------+
		| GRANT USAGE ON *.* TO 'test'@'10.0.0.%'                               |
		| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'test'@'10.0.0.%' |
		+-----------------------------------------------------------------------+
		
    mysql> revoke delete on 库名.表名 from 用户名@'白名单'; 权限回收

4.3、连接管理

连接数据库参数
            -u  用户名
            -p  密码
            -S  指定socket文件
            -h  指定连接主机
            -P  指定端口
            -e  执行的sql语句(不进入数据库就可执行)
            <   导入sql文件

mysql内置命令
            mysql> help
                  ?         (\?) Synonym for `help'.
                  clear     (\c) Clear the current input statement.                                      结束上个命令的运行
                  connect   (\r) Reconnect to the server. Optional arguments are db and host.
                  delimiter (\d) Set statement delimiter.
                  edit      (\e) Edit command with $EDITOR.
                  ego       (\G) Send command to mysql server, display result vertically.                格式化输出sql结果
                  exit      (\q) Exit mysql. Same as quit.                                               退出mysql
                  go        (\g) Send command to mysql server.
                  help      (\h) Display this help.
                  nopager   (\n) Disable pager, print to stdout.
                  notee     (\t) Don't write into outfile.
                  pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
                  print     (\p) Print current command.
                  prompt    (\R) Change your mysql prompt.
                  quit      (\q) Quit mysql.                                                             退出mysql
                  rehash    (\#) Rebuild completion hash.
                  source    (\.) Execute an SQL script file. Takes a file name as an argument.           在mysql内执行sql导入
                  status    (\s) Get status information from the server.
                  system    (\!) Execute a system shell command.                                         在mysql内使用shell命令
                  tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
                  use       (\u) Use another database. Takes database name as argument.
                  charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
                  warnings  (\W) Show warnings after every statement.
                  nowarning (\w) Don't show warnings after every statement.
                  resetconnection(\x) Clean session context.

远程连接登录与本地连接登录区别:
       mysql> show processlist;
                  +----+------+---------------+------+---------+------+----------+------------------+
                  | Id | User | Host          | db   | Command | Time | State    | Info             |
                  +----+------+---------------+------+---------+------+----------+------------------+
                  |  2 | root | localhost     | NULL | Sleep   |   53 |          | NULL             |
                  |  4 | test | python1:51148 | NULL | Query   |    0 | starting | show processlist |
                  +----+------+---------------+------+---------+------+----------+------------------+
            
       经过对比可以发现Host列中不一样,如果是远程,则是主机名:端口，本地则是localhost

五、mysql多实例管理

1、创建3307目录
            mkdir -p /application/mysql3307/data
            mkdir -p /data/mysql3307/{data,log,binlog}

2、授权
            chown -R mysql.mysql /application/mysql3307
            chown -R mysql.mysql /data/mysql3307

3、先停止3306数据库并拷贝安装文件、启动文件
            systemctl stop mysql_3306
            cp -rp /application/mysql3306/* /application/mysql3307/
            cp -rp /etc/systemd/system/mysql_3306.service /etc/systemd/system/mysql_3307.service

4、先移走3307目录下的my.cnf文件
            mv /application/mysql3307/my.cnf /application/mysql3307/my.cnf.bak

5、修改3307的启动文件
            vim /etc/systemd/system/mysql_3307.service
            [Unit]
			Description=Mysql server
			After=syslog.target
			After=network.target
			
			[Service]
			User=mysql
			Group=mysql
			LimitNOFILE=102400
			ExecStart=/application/mysql3307/bin/mysqld --defaults-file=/application/mysql3307/my.cnf
			
			[Install]
			WantedBy=multi-user.target

6、初始化3307数据库
            /application/mysql3307/bin/mysqld --initialize-insecure --user=mysql --basedir=/application/mysql3307 --datadir=/data/mysql3307/data

7、再将my.cnf文件移动到原来位置
            mv /application/mysql3307/my.cnf.bak /application/mysql3307/my.cnf

8、修改3307数据库的my.cnf
            [mysqld]
            user=mysql
            basedir=/application/mysql3307
            datadir=/data/mysql3307/data
            socket=/data/mysql3307/run/mysql.sock
            server_id=7
            port=3307
            log_error_verbosity         = 3
            log_error                   = /data/mysql3307/log/mysqld-err.log
            log_timestamps              = SYSTEM
            log-bin                     = /data/mysql3307/binlog/mysql-bin
            [mysql]
            socket=/data/mysql3307/run/mysql.sock

9、启动3306、3307数据库，给3307库创建管理员账户，并执行检查命令
            启动
                  systemctl restart mysql_3306
                  systemctl restart mysql_3307
            创建管理员
                  [root@python1 ~]# mysqladmin -uroot -p password 123 -S /data/mysql3307/run/mysql.sock
            
            检查
                  [root@python1 ~]# mysql -S /data/mysql3306/run/mysql.sock -e "select @@server_id" -uroot -p123
                  [root@python1 ~]# mysql -S /data/mysql3307/run/mysql.sock -e "select @@server_id"
                  [root@python1 log]# mysql -S /data/mysql3306/run/mysql.sock -e "select @@server_id" -uroot -p123
			                  +-------------+
			                  | @@server_id |
			                  +-------------+
			                  |           6 |
			                  +-------------+
                  [root@python1 log]# mysql -S /data/mysql3307/run/mysql.sock -e "select @@server_id"
			                  +-------------+
			                  | @@server_id |
			                  +-------------+
			                  |           7 |
			                  +-------------+
           检查端口如下所示
           		ss -lntp | grep 330