MySQL-lesson02 体系结构管理

1、服务器与客户端模型

C/S服务结构(client/server)

MySQL自带的客户端：

1. mysql
2. mysqldump
3. mysqladmin

第三方：

1. navicat
2. sqlyog
3. phpmyadmin

2、连接MySQL的方式

• TCPIP模式:远程连接
  mysql -uwordpress -p123 -h 10.0.0.51 -P 3306

• Socket模式:本地连接
  mysql -uroot -p123 //默认socket启动
  mysql -uroot -p123 -S /application/mysql/tmp/mysql.sock //指定socket目录启动

问题：以下是哪种连接方式

• mysql -uroot -p123
  socket
• mysql -uroot -p123 -h127.0.0.1
  TCP
• mysql -uroot -p123 -hlocalhost
  socket
• mysql -uroot -p123 -h127.0.0.1 -S /tmp/mysql.sock
  TCP

总结:

1. -h不一定都是tcp连接
2. MySQL默认使用socket连接
3. -S只允许本地连接,无法远程连接
4. tcp连接需要建立3次握手,所以MySQL默认使用socket(速度快)

3、实例介绍

• 实例：一个进程+多个线程+预分配的内存结构
• MySQL在启动的过程中会启动后台守护进程，并生成工作线程，预分配内存结构供MySQL处理数据使用
• Mysqld是一个守护进程但是本身不能自主启动
• 一个完整的数据库管理系统=实例+数据

4、Mysqld的程序结构

image.png

连接层功能：

1. TCP/IP或者Socket的连接方式
2. 验证用户名密码功能
3. 连接线程(客服):接收SQL语句,返回执行结果

SQL层：

1. 语法检查模块,检查上层发过来的SQL,进行语法检查
2. 权限检查模块,检测当前登录用户是否有权限操作数据库对象
3. 语义检查模块,识别语句种类（DDL、DCL、DML、DQL）
4. 解析器,解析出SQL语句所有可能的执行方式,这些方式被称之为"执行计划"
5. 优化器,基于执行代价(系统资源的消耗作为维度(cpu\mem\IO)).管理员可以通过间接的方法,干预优化器的选择
6. 执行器,按照优化器选择的"最优"的执行计划,执行SQL语句.得出结论:某某磁盘的某某位置,发送给存储引擎层.
7. 提供查询缓存:有局限性.一般会用redis产品来替代.
8. 记录查询日志

存储引擎层：

1. 接收SQL层传来的SQL语句
2. 和"磁盘"(文件系统)打交道的层次
3. 根据SQL层执行的结果,去磁盘上找到对应的数据.结构化成表的模式,返回给用户

5、MySQL的逻辑结构（抽象结构）

• 库(database,schema)
  组成：库名，库属性

• 表(table)
  组成：表名字，列(列名字，列属性)，表属性，表数据

• 一个库有多张表（一个目录有多个文件）

6、MySQL物理结构（如何使用磁盘）

1. 库: 在磁盘上就是一个目录
2. 表: 在磁盘上就是多个文件，用于存储表的信息

[root@db01 oldboy]# create table t1(id int);
[root@db01 oldboy]# ls -l
-rw-rw---- 1 mysql mysql    61 Nov 14 17:54 db.opt
-rw-rw---- 1 mysql mysql  8556 Nov 14 17:56 t1.frm        #t1表文件
-rw-rw---- 1 mysql mysql 98304 Nov 14 17:56 t1.ibd        #t1表文件

[root@db01 oldboy]# create table t2(id int)engine=myisam;
[root@db01 oldboy]# ls -l
-rw-rw---- 1 mysql mysql  8556 Nov 14 17:57 t2.frm         #t2表文件
-rw-rw---- 1 mysql mysql     0 Nov 14 17:57 t2.MYD          #t2表文件
-rw-rw---- 1 mysql mysql  1024 Nov 14 17:57 t2.MYI         #t2表文件

• MySQL的最底层的物理结构是数据文件，也就是说，存储引擎层，打交道的文件，是数据文件。
• 库名，表名，一律小写

7、表的段、区、页

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

8、MySQL 用户权限管理

8.1 MySQL 用户的作用:

1. 登录MySQL
2. 管理mysql的对象(库\表)

8.2 用户表现方式

mysql用户的表现方式：用户名@'白名单'

白名单支持设置方法:
'user1'@'10.0.0.200'
'user1'@'10.0.0.%'
'user1'@'10.0.0.5%'
'user1'@'10.0.0.0/255.255.255.0'
'user1'@'rdbmsa.com'
'user1'@'%'

8.3 用户管理

增：
mysql> create user oldboy@'10.0.0.%' identified by '123';
查：
mysql> select user,host,password from mysql.user;      //mysql5.6
mysql> select user ,host ,authentication_string from mysql.user    //mysql5.7
改:
mysql> alter user oldboy@'10.0.0.%' identified by '456';
删：
mysql> drop user oldboy@'10.0.0.%';

修改密码

# 设置密码帮助
   help set password  

1) set password for oldguo@'10.0.0.%'=PASSWORD('456');      //不用重启生效
2) update mysql.user set password=PASSWORD('123456') where user='root' and host='localhost';
   //修改密码后重启mysql生效
3) mysqladmin -uroot -p password 123       //-p password 新密码，Enter password:旧密码,没有就回车
   Enter password: 

误删了所有用户

#关闭数据库
[root@db02 mysql-5.7.20]# /etc/init.d/mysqld stop
#启动数据库
[root@db02 mysql-5.7.20]# mysqld_safe --skip-grant-tables --skip-networking
#使用mysql库
mysql> use mysql
#错误方法1、创建root用户
mysql> create user root@’localhost’;
#错误方法2、创建root用户
mysql> insert into user(user,host,password) values('root','10.0.0.55',PASSWORD('123'));
#正确方法创建root用户
mysql> insert into mysql.user values (‘localhost’,’root’,PASSWORD(‘123’),
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
‘Y’,
’Y’,
’Y’,
’Y’,
’Y’,
’Y’,
’Y’,
’Y’,
’Y’,
’Y’,
’Y’,
’Y’,
’Y’,
’’,
’’,
’’,
’’,0,0,0,0,’mysql_native_password’,’’,’N’);

8.4 权限管理操作

# 授权操作：
grant all on \*.\* to wordpress@'10.0.0.%' identified by '123';
# 查看用户拥有的权限：
show grants for app@'10.0.0.%';
# 收回权限操作
revoke select on *.* from 'admin'@'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 TABLESPACE
• ALL : 以上所有权限，一般是普通管理员拥有的
• with grant option：授予grant、revoke 功能，超级管理员才具备的，给别的用户授权的功能
  grant：授权
  revoke：收权

创建用户举例

# 开一个wordpress@'10.0.0.%'用户，能够对所有库和表，进行所有命令操作,除了grant，revoke
grant all on *.* to wordpress@'10.0.0.%' identified by '123';

# 开一个管理员用户admin,能通过10.0.0.%这个网段管理mysql (WITH GRANT OPTION )
grant all on *.* to admin@'10.0.0.%' identified by '123' with grant option;

# 开一个应用用户app,能通过10.0.0.%这个网段对app库下所有表进行SELECT,INSERT, UPDATE, DELETE
grant SELECT,INSERT, UPDATE, DELETE on app.* to app@'10.0.0.%' identified by '123';

# 对app@'10.0.0.%'用户增加删除权限
grant drop on app.* to app@'10.0.0.%';

补充：

1. 多次设置权限,权限是相加关系，如果权限相同，则不变
2. 表会继承上层库的权限，给表授权取并集，新的权限会相加

开发人员用户授权流程

• 询问内容：
  (1) 操作哪些库,表?
  (2) 权限:select update insert
  (3) 从哪登录?
  (4) 用户名密码，规范是库名，要询问程序里的用户，密码要符合公司标准

mysql 8.0新特性

• 授权操作：grant all on . to wordpress@'10.0.0.%' ;

1. 用户要提前创建好,grant只作为授权命令
2. 不支持设置密码的功能

9、连接管理

9.1 自带客户端工具

• mysql、mysqladmin、mysqldump

1、用于数据库的连接管理

MySQL参数：

-u  //用户名
-p  //密码

-S  //指定socket文件位置；如果是编译安装的mysql，可以省略-S，如果其他方式，加上-S     
mysql -uroot -p -S /application/mysql/tmp/mysql.sock 

-h  指定链接的数据库服务器地址
mysql -uroot -p -h 10.0.0.51 

-P  指定链接的端口号，默认3306，改了的话要加上指定端口
mysql -uroot -p -h 10.0.0.51  -P 3307

-e  免交互式执行mysql内部命令，命令行执行命令
mysql -uroot -p -e "select user,host,password from mysql.user;"

<  导入SQL脚本到mysql中
mysql -uroot -p </root/world.sql

• 注意：登录不要把密码写到命令行
  mysql -uroot -p

2、将用户的SQL语句发送到服务端

mysqladmin参数：
参数：-u -p -S -h -P

• mysqladmin命令的使用：
  01）“强制回应 (Ping)”服务器。
  02）关闭服务器。
  03）创建和删除数据库。
  04）显示服务器和版本信息。
  05）显示或重置服务器状态变量。
  06）设置口令。
  07）重新刷新授权表。
  08）刷新日志文件和高速缓存。
  09）启动和停止复制。
  10）显示客户机信息。

#查看MySQL存活状态
[root@db01 ~]# mysqladmin -uroot -p123 ping
#查看MySQL状态信息
[root@db01 ~]# mysqladmin -uroot -p123 status
#关闭MySQL进程
[root@db01 ~]# mysqladmin -uroot -p123 shutdown
#查看MySQL参数
[root@db01 ~]# mysqladmin -uroot -p123 variables
#删除数据库
[root@db01 ~]# mysqladmin -uroot -p123 drop DATABASE
#创建数据库
[root@db01 ~]# mysqladmin -uroot -p123 create DATABASE
#重载授权表
[root@db01 ~]# mysqladmin -uroot -p123 reload
#刷新日志
[root@db01 ~]# mysqladmin -uroot -p123 flush-log
#刷新缓存主机
[root@db01 ~]# mysqladmin -uroot -p123 reload
#修改口令
[root@db01 ~]# mysqladmin -uroot -p123 password

mysqldump：备份数据库和表的内容

help命令的使用：

mysql> help
mysql> help contents
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show

source命令的使用：

source    //运行sql脚本文件

# 导入linux目录脚本
mysql> SOURCE /data/mysql/world.sql

# 或者使用非交互式
mysql</data/mysql/world.sql

9.2 第三方的连接工具

• 远程连接工具的使用(SQL编程常用工具):

1. mysql:
   navicat
   sqlyog

2. oracle:
   Toad
   sql/developer
   pl/sql developer

注意：需要加载对应语言程序的API

• 例：开一个管理员用户(ALL)root,能通过windows上的sqlyog登录linux上mysql.
  grant all on . to root@'10.0.0.%' identified by '123';
  测试：mysql -uroot -p123 -h 10.0.0.51 -P 3306
  如果还连不上，是客户端的问题（网络连不上）

9.3 MySQL启动流程

多种启动方式

image.png

• 启动mysql
  /etc/init.d/mysqld start ------> mysqld_safe ------> mysqld

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

• 关闭mysql
  mysqladmin shutdown
  service mysqld stop
  kill pid #进程号
  killall mysqld #数据库服务对应的进程名字
  pkill mysqld #数据库服务对应的进程名字
  kill -9 #禁用，断电会丢数据

出现问题：
1、如果在业务繁忙的情况下，数据库不会释放pid和sock文件
2、号称可以达到和Oracle一样的安全性，但是并不能100%达到
3、在业务繁忙的情况下，丢数据（补救措施，高可用）

10、初始化配置

目的：为了能够让mysql ,按照我们管理员的想法启动

10.1 初始化配置方法：

1. 预编译的时候修改
   cmake 时指定参数，一般建议修改一些不会经常变化的参数

2. 初始化配置文件
   /etc/my.cnf ---->/etc/my.cnf.d/my.cnf(5.7版本包括) ------>/etc/mysql/my.cnf ---> /application/mysql-5.6.38/etc/my.cnf --> ~/.my.cnf

• 默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。
• 配置规范：一个节点只装一个数据库，配置文件只用/etc/my.cnf
• 以defaults参数指定的配置文件，则默认的配置文件无效
  指定配置文件命令：mysqld_safe --defaults-file=/tmp/a.cnf &

3. 使用命令行参数,干预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

• 优先级结论：
  1、命令行
  2、defaults-file
  3、配置文件
  4、预编译

10.2 初始化配置文件的使用

标签分类：

1. 服务端标签：
   作用：影响了数据库的启动
   [server]
   [mysqld]
   [mysqld_safe]

2. 客户端标签：
   作用：影响了客户端连接
   [cient]
   [mysql]
   [mysqladmin]
   [mysqldump]

配置文件的示例：

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/tmp/mysql.sock     //如果没有tmp目录，需创建，并授权
port=3306
log_error=/var/log/mysql.log    //指定错误日志目录
server_id=6

[mysql]   //客户端配置信息尽量不要写用户密码
socket=/application/mysql/tmp/mysql.sock    //客户端socket和服务端socket一定是一样的，否则连不上
prompt=\\ oldguo [\\d]>   //oldguo显示想要的字符串，\\d  显示当前数据库

# 服务端添加此配置，跳过域名解析
[mysqld]
skip_name_resolve  

# 导出文件的安全路径配置
[mysqld]
secure-file-priv=/tmp   //配置完重启

# 配置完重启生效
/etc/init.d/mysqld restart

11、多实例(多个数据库管理系统)应用

• 什么是多实例=多套后台进程+线程+内存结构

11.1 多实例的组成

1. 软件部分：
   多配置文件
   多port
   多socket
   多个日志
   多server_id

2. 数据部分
   初始化多套数据

11.2 多实例配置

# 创建多个专用目录
mkdir /data/330{7..9}/data -p

# 准备多个配置文件
vim /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
skip-name-resolve
server-id=7
--------------------------
vim /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
skip-name-resolve
server-id=8
------------------
vim /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
skip-name-resolve
server-id=9

# 初始化三套数据：
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3307/data
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3309/data

# 修改权限：
touch /data/330{7..9}/mysql.log
chown -R mysql.mysql /data/330*

# 启动数据库：
/application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
/application/mysql/bin/mysqld_safe --defaults-file=/data/3308/my.cnf &
/application/mysql/bin/mysqld_safe --defaults-file=/data/3309/my.cnf &

# 验证：
netstat -lnp|grep 330

# 连接测试：
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'"

# 连接多实例：
mysql -S /data/3307/mysql.sock 
mysql -S /data/3308/mysql.sock
mysql -S /data/3309/mysql.sock

# 关闭多实例:
mysqladmin -S /data/3307/mysql.sock shutdown
mysqladmin -S /data/3308/mysql.sock shutdown
mysqladmin -S /data/3309/mysql.sock shutdown