MySQL体系结构和管理

最新推荐文章于 2022-06-02 12:28:43 发布

可惜我不是圣人

最新推荐文章于 2022-06-02 12:28:43 发布

阅读量117

点赞数

本文链接：https://blog.csdn.net/weixin_44695383/article/details/103742428

版权

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

网站连接数据库使用的是TCP/ip方式
运维在数据上本机查看数据库使用的是socket方式

实例 **

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

一个已经安装好的数据库就可以理解为是一个实例。
在实例下有进程 -》主线程-》多线程+加固定的分配的内存提供着工作

mysqld的程序结构

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

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

SQL语句的执行过程

select user,host from mysql.user;

连接层

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

SQL层 *****

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

存储引擎层(简单介绍)

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

MySQL的逻辑结构

库
- 库名
- 库属性
表
- 数据行 +元数据
- 元数据=表属性，列（列名，列属性，约束等）
列:列名(字段),列属性(数据类型,约束等)数据行(记录)

库的物理存储结构

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

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

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

MySQL基础管理

用户的管理

用户的作用

用户的定义

用户管理操作

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 超级管理员 .

授权管理生产需求

(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';

权限回收

oldguo[(none)]>show grants for dev@'10.0.0.%';   ##权限查询
oldguo[(none)]>revoke delete on wordpress.* from dev@'10.0.0.%'; ##权限回收

本地管理员用户密码忘记

(1) 关闭数据库

mysqladmin -uroot -p shutdown

(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

MySQL连接管理

MySQL自带命令

mysql命令常用参数

-u	指定用户	-p	指定密码
-h	指定远程连接ip	-S	指定socet文件
-P	指定端口	-e	面交互模式执行seq语句

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

免交互执行sql语句

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

MySQL的启动和关闭方式

sys-V

/etc/init.d/mysqld  [start|stop|restart]
service mysqld  [start|stop|restart]

前提是需要把软件存放目录下的mysql/support-files/mysql.server 拷贝到/etc/init.d/下

systemd方式

systemctl mysqld [start|stop|restart]

mysqld 只能启动
万能关闭方法：

mysqladmin -uroot -p shutdown

维护模式启动

mysqld_safe --skip-grant-tables --skip-networking &

--skip-grant-tables ==跳过权限验证的表
--skip-networking == 跳过tcp/ip登陆

MySQL的初始化配置

提供的方法

预编译 **
初始化配置文件*****
命令行 ***
注意: 优先级 3>2>1

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

[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 &

初始化配置文件作用

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

初始化配置文件格式

标签 :

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

配置文件模板

[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

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"