MySQL体系结构和管理学习笔记day02

1.MySQL C/S模型

Server : mysqld
Client :
socket : 仅本地连接使用
tcp/ip : 应用连接使用
TCP/IP方式（远程、本地）：
mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket方式(仅本地)：
mysql -uroot -poldboy123 -S /tmp/mysql.sock

2. 实例 **

实例= mysqld ----> master thread ----> 干活的线程 + 预分配的内存结构
公司= boss 经理 员工 办公室

3.mysql的程序结构

3.1 MySQL的专用管理和操作命令SQL

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

3.2 SQL语句的执行过程

select user, host form mysql.user;
(1)mysql -u root -pxxxx

3.2.1 连接层

(1) 提供链接协议
socket
TCP/IP
(2) 验证 user password host port
(3) 提供连接线程
show [full] processlist;
功能: 接收SQL语句,返回结果.]

3.2.2 SQL层 *****

(1) 语法检查和SQL_MODE检查
(2) 语义检查和权限检查
(3) 解析预处理,生成解析树(执行计划树)
(4) 优化会根据自带算法,选择最优的方案(代价模型算法)
代价? cpu io mem
(5) 选择最优的方案进行执行SQL
(6) 提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
(7) 提供日志记录（日志管理章节）：binlog，默认是没开启的。

3.2.3 存储引擎层（简单介绍）

相当于文件系统,将数据取出,再交给SQL层结构化成表,返回给用户.

4. MySQL的逻辑结构 ***

4.1 库：

库名
库属性

4.2 表

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

5. MySQL的物理存储结构引入

5.1 库的物理存储结构

用文件系统的目录来存储

5.2 表的物理存储结构 ***

MyISAM（一种引擎）的表：
user.frm
user.MYD
user.MYI
InnoDB(默认的存储引擎)的表：
time_zone.frm：存储列相关信息
time_zone.ibd：数据行+索引

5.3 InnoDB表底层存储结构引入(扩展)*****

段 : 一个表就是一个的段(非分区表)
区 : 固定大小1M, 连续的64个页
页 : 最小的IO单元,默认16K

6. MySQL基础管理

6.1 用户的管理

6.1.1 用户的作用

6.1.2 用户的定义

6.1.3 用户管理操作

create user oldguo@'10.0.0.%';
drop user oldguo@'10.0.0.%';
alter user oldguo@'10.0.0.%' identified by '123';
select user,host from mysql.user;
create user oldboy@'10.0.0.%' identified by '123';
注:
(1)8.0以前,以上命令可以忽略,grant可以自动创建用户并授权.
(2)8.0以后必须先建用户后授权,grant只做授权功能.

6.2 权限管理

6.2.1 作用

6.2.2 权限定义

6.2.3 授权范围

6.2.4 授权管理命令

grant all on . to oldguo@'10.0.0.%' identified by '123';

ALL:
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 : 普通管理员
ALL+ with grant option 超级管理员 .

grant select on . to oldguo@'10.0.0.%' identified by '123';
grant SELECT,INSERT, UPDATE, DELETE on . to oldguo@'10.0.0.%' identified by '123';

6.2.5 授权管理生产需求

(1) 授权一个管理员用户admin ,密码admin,能够通过10网段管理数据库
oldguo[(none)]>grant all on . to admin@'10.0.0.%' identified by 'admin';
(2) 开放一个wordpress应用的用户,密码123,nginx服务器使172.16.1.%网段.
应用用户需要权限?
select,insert,update,delete
grant select,insert,update,delete on wordpress.* to wordpress@'172.16.1.%' identified by '123';
(3) 中小公司,开发人员leader,需要开发用户dev通过10网段对wordpress进行开发和管理
grant SELECT,INSERT, UPDATE, DELETE,CREATE,ALTER,CREATE VIEW,SHOW VIEW,CREATE ROUTINE, ALTER ROUTINE,EVENT, TRIGGER on wordpress.* to dev@'10.0.0.%' identified by '123';

6.2.6 权限回收

oldguo[(none)]>show grants for dev@'10.0.0.%';
oldguo[(none)]>revoke delete on wordpress.* from dev@'10.0.0.%';

6.2.7 面试问题

作为开发人员开用户需要怎么沟通?
以邮件形式,进行开用户的申请
向谁申请?
DBA权限管理人员
分管部门领导
DBA主管
提供哪些信息?
需要哪些权限
操作对象
从哪来访问MySQL

最为DBA,开发人员找你开用户需要怎么沟通?
提供哪些信息?
需要哪些权限
操作对象
从哪来访问MySQL

开发人员找你私下要root用户权限?
不给!
金融类:
非本业务部分,不得提供超级管理员用户的权限

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

(1) 关闭数据库

(2) 将数据库启动到无验证模式

[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &

(3) 修改密码

oldguo[(none)]>flush privileges;
oldguo[(none)]>alter user root@'localhost' identified by '123456';

(4)重启数据库为正常模式

[root@db01 ~]# /etc/init.d/mysqld restart

(5)测试新密码

[root@db01 ~]# mysql -uroot -p123456

6.3 MySQL连接管理

6.3.1 MySQL自带命令

mysql
-u
-p
-h
-P
-S
-e
-V
<
--help
socket :
mysql -uroot -p123 -S /tmp/mysql.sock
mysql -uoldguo -p -S /tmp/mysql.sock
注意:本地登录的用户,需要提前授权localhost相关用户
TCP/IP :
mysql -uoldguo -p -h10.0.0.51 -P3306

[root@db01 ~]# mysql -uroot -p123456 -e "show databases;"
[root@db01 ~]# mysql -uroot -p123456 <world.sql

6.3.2 客户端工具连接

oldguo[(none)]>grant all on . to root@'10.0.0.%' identified by '123';
sqlyog
navicat

6.4 MySQL的启动和关闭方式

6.5 MySQL的初始化配置

6.5.1 提供的方法

1. 预编译 **

2. 初始化配置文件*****

3. 命令行 ***

注意: 优先级 3>2>1

6.5.2 初始化配置文件的默认读取顺序

[root@db01 /tmp]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf ----> /tmp/mysql.sock
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnf ----> /tmp/a.sock

注意:一旦使用--defaults-file,以上的默认配置就不会生效了
[root@db01 ~]# mysqld_safe --defaults-file=/opt/oldguo.cnf &

6.5.3 初始化配置文件作用

数据库的启动
客户端的登录

6.5.4 初始化配置文件格式

[标签1]
xxxx=yyyy
[标签2]
xxxx=yyyy
[标签3]
xxxx=yyyy

标签 :
服务端
[mysqld]
[mysqld_safe]
[server]
客户端
[mysql]
[mysqldump]
[mysqladmin]
[client]

6.5.5 配置文件模板

[mysqld]
user=mysql
server_id=6
port=3306
basedir=/application/mysql
datadir=/data/3306/data
log_error=/data/3306/data/3306.log
socket=/data/3306/mysql.sock
[mysql]
socket=/data/3306/mysql.sock

6.6 MySQL的多实例管理

6.6.1 准备多个目录

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

6.6.2 准备配置文件

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

6.6.3 初始化三套数据

mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql

6.6.4 systemd管理多实例

cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

vim mysqld3307.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

6.6.5 授权

chown -R mysql.mysql /data/*

6.6.6 启动

systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

6.6.7 验证多实例

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