MySQL体系结构与管理

发表于 更新于 分类于

一、MySQL结构介绍

MySQL是一种C/S的结构，决定的他的两种连接方式

1
2
TCP/IP: mysql -uroot -p123456 -h 10.0.0.2 -P3306
Socket；mysql -uroot -p123456 -S /tmp/mysql.sock

二、MySQL实例的构成

1
mysqld+master+干活的Thread+预分配的内存（mysql启动时就会向系统拿取一定足够的内存）

三、MySQL中mysqld服务器进程结构

image-20211121103808917

image-20211116165107006

1
2
3
4
5
1、结构化的查询语句
DQL		数据查询语言
DDL		数据定义语言
DML		数据操作语言
DCL		数据控制语言
1
2
3
4
5
6
7
8
9
2、sql层（优化方面至关重要）
验证SQL语法和SQL_MODE
验证语义
验证权限
解析器进行语句解析，生成执行计划（解析树）
优化器（各种算法，基于执行代价），根据算法找到代价最低的执行计划。（cpu、io、mem）
执行器按照优化器选择执行计划，执行SQL语句，得出获取数据的方法
提供query cache(默认不开)
记录操作日志（binlog）,默认没开。
1
2
3
3、存储引擎层
真正和磁盘打交道的一个层次
根据SQL层提供的取数据的方法，拿到数据，返回给SQL，结构化表，再又连接层线层返回给用户。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
4、mysql逻辑储存结构
库（相当于目录）
create database yu0l;
show databases;
use yu0l;
表（相当于文件）

列（字段）
数据行（记录）
表属性
5、mysql物理储存结构
库：使用FS上的目录来表示
表：
	MyISAM(存储引擎,相当于ext2),慢慢的淘汰了 
举例：		  user.frm:存储的表结构（列，列属性）
			user.MYD:存储的数据记录
			user.MYI:存储索引
	InnoDB（存储引擎，相当于xfs）
举例：
			time_zone.frm:存储的表结构（列，属性）
			time_zone.ibd:存储的数据记录和索引
			ibdata1:数据字典
1
2
3
4
5
7、innodb 段 区 页
一般情况下（非分区表）
一个表就是一个段
一个段有多个区构成
一个区，64个连续的页（16K）,1M大小

image-20211121162048447
四、用户的权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
1、用户的管理（增、删、改、查）
create user yu0l@'10.0.0.%' identified by '123456';		#创建用户
grant all on *.* to yu0l1@'10.0.0.%' identified by '123456';	#8.0版本以前可以授权时创建用户

mysql> create user yu0l@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to yu0l1@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,host from mysql.user;	#查询用户
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| yu0l          | 10.0.0.%  |
| yu0l1         | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+


alter user yu0l@'10.0.0.%' identified by '654321';	#修改用户密码
mysql> drop user yu0l@'10.0.0.%';	#删除用户
Query OK, 0 rows affected (0.00 sec)
1
2、权限管理

权限列表image-20211121171854525

1
2
3
4
授权命令
# grant  权限   on  作用目标   to  用户  identified by 密码  with grant option;
#权限：All+with grant option（给其他用户授权） = root;可以是单个权限（如select）或所有(all)
#作用目标：*.*(所有)  wordpress.*(wordpress仅这个库) wordpress.t1(仅这个表)

需求：创建一个管理员用户root,可以通过10.0.0.0/24网段访问来管理数据库。再创建一个应用数据库用户可以通过10.0.0.0/24网段进行增、删、改、查。

1
2
grant all on *.* to root@'10.0.0.%' identified by '123456';
grant SELECT,UPDATE,INSERT,DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '654321';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
回收权限
show grants for wordpress@'10.0.0.%';	#查询用户权限
revoke delete on wordpress.* from wordpress@'10.0.0.%';		#回收DELETE的权限

mysql> show grants for wordpress@'10.0.0.%';
+---------------------------------------------------------------------------------+
| Grants for wordpress@10.0.0.%                                                   |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'10.0.0.%'                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO 'wordpress'@'10.0.0.%' |
+---------------------------------------------------------------------------------+
mysql> revoke delete on wordpress.* from wordpress@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for wordpress@'10.0.0.%';
+-------------------------------------------------------------------------+
| Grants for wordpress@10.0.0.%                                           |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'10.0.0.%'                            |
| GRANT SELECT, INSERT, UPDATE ON `wordpress`.* TO 'wordpress'@'10.0.0.%' |
+-------------------------------------------------------------------------+
#DELETE的权限没有了
1
2
关于生产中开用户
是否有邮件的批复；对那些库和表操作；做什么操作；从哪里登录数据库。

五、MySQL的基础管理

1
2
3
4
5
6
7
8
9
10
1、mysql的启动过程
日常启停
mysql.server start(service mysqld start ) ----> mysql_safe -----> mysqld
mysql.server start(systemctl start mysqld) -----> mysqld 依赖于/etc/my.cnf
维护性任务
一般会将需要的参数临时添加待命令行，也会读取/etc/my.cnf的内容，如果有冲突，命令行优先（优先级高）
mysqld_safe --skip-grant-tables --skip-networking &
补充：
mysqld_safe &                         #启动数据库
mysqladmin -uroot -p123456 shutdown   #关闭数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
2、初始化配置
a、作用
影响数据库的启动，影响到客户端的功能。

初始化配置的方法
初始化配置文件(例如/etc/my.cnf);启动命令上进行的设置（例如mysqld_safe ）,预编译时的设置（编译安装）。

b、配置文件的格式
[标签]
xxx=xxx
[标签]
xxx=xxx

c、配置文件标签的归类
服务器端：
[mysqld]
[mysald_safe]
[server]  #不建议这种统一模式
客户端：
[mysql]
[mysqladmin]
[client]  #不建议这种统一模式

d、配置文件/etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
e、配置文件读取的顺序（后面的后将前面相同的配置覆盖）
[root@localhost ~]# mysql --help --verbose|grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
强制使用自定义配置文件
--defaults-file
mysqld_safe --defaults-file=/etc/my.cnf &  #使用自定义配置文件启动数据库

f、与配置文件有关的告警
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
出现这个原因要么是数据库没有启动，要么就是有多个配置文件导致*.sock冲突。
1
2
3
4
5
6
7
8
3、mysql的连接管理
a、mysql命令
要提前给登录用户授权
TCP/IP: mysql -uroot -p123456 -h 10.0.0.2 -P3306
Socket；mysql -uroot -p123456 -S /tmp/mysql.sock
b、客户端工具
sqllog
navicat
1
2
3
4
5
6
7
8
4、多实例管理
a、准备多个目录
b、准备配置文件
c、初始化数据
d、systemd管理多实例
e、授权
f、启动
g、验证