数据库day02-MySQL体系结构和基础管理

数据库课程day02
MySQL基础管理

1. MySQL的逻辑结构

库(database schema)
    相当于linux的目录
    库名字
    库的属性
表(tabale segment)
    相当于linux文件
    表名
    表属性
    表的数据行 row,记录(类似于excle表格)
    列(字段)
用户(user)
    用户名
    白名单(主机范围)
其他
    权限(user privileges)

2. MySQL基础物理结构

库
物理存储就是Linux中的目录，以目录存储

表
元数据 表名 表属性 表列
数据行

2.1 MyISAM

user.myi
    索引相关信息
user.myd
    存储数据行
user.frm
    列的信息

2.2 InnoDB

time_zone.frm
    列的信息
time_zone.ibd
    数据行和索引 (IOT)

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

此知识点为面试题☆☆☆☆☆

段 segment

  • 一个非分区表就是一个段

区 extent

  • 连续的64个页，固定大小为1M

页 page

  • MySQL最小的 IO 单元，默认16KB

4. MySQL基础管理

4.1 用户管理

4.1.1. 用户的作用

登录MySQL
管理MySQL的对象

4.1.2 用户的定义

用户名
白名单(主机列表) 可被允许的主机 IP

4.1.3 用户定义的几种方式

    oldboy@'10.0.0.1'
    oldboy@'localhost'
    oldboy@'10.0.0.%'
        netmask:255.255.255.0
    oldboy@'10.0.0.5%'
    oldboy@'10.0.0.0/255.255.254.0'
    oldboy@'oldboy.com'
    oldboy@'db01'
    oldboy@'%'

4.1.4 用户的管理操作

    #创建用户
        create user oldli@'10.0.0.%';
    #删除用户
        drop user oldli@'10.0.0.%';
    #修改用户
        alter user oldli@'10.0.0.%' identified by '123';
    #查询用户
        select user,host from mysql.user;
        select concat(user,"@","'",host,"'") from mysql.user;
    #设定密码
        create user oldli@'10.0.0.%' identified by '123';

注：
8.0以前，以上命令可以忽略，grant可以自动创建用户并授权
8.0以后必须先建用户后授权，grant只做授权功能

4.2 权限

4.2.1 作用

开启用户的管理对象的能力
mysql -uoldli -p -h10.0.0.51 -P 3306

4.2.2 权限的定义(8.0以前)

按命令进行定义,例如 select update insert drop create ...

4.2.3 授权的范围

*.*  库级别授权
wordpress.*  单库级别授权
wordpress.t1  单表级别授权

4.2.4 授权管理命令

grant all on *.* to oldli@'10.0.0.%' identified by '123'
注意:
ALL 普通管理员
ALL+with grant option   超级管理员

权限解释说明

ALL或ALL PRIVILEGES  代表指定权限等级的所有权限。
ALTER   允许使用ALTER TABLE来改变表的结构，ALTER TABLE同时也需要CREATE和INSERT权限。重命名一个表需要对旧表具有ALTER和DROP权限，对新表具有CREATE和INSERT权限。
ALTER ROUTINE   允许改变和删除存储过程和函数
CREATE  允许创建新的数据库和表
CREATE ROUTINE  允许创建存储过程和包
CREATE TABLESPACE   允许创建、更改和删除表空间和日志文件组
CREATE TEMPORARY TABLES 允许创建临时表
CREATE USER 允许更改、创建、删除、重命名用户和收回所有权限
CREATE VIEW     允许创建视图
DELETE  允许从数据库的表中删除行
DROP    允许删除数据库、表和视图
EVENT   允许在事件调度里面创建、更改、删除和查看事件
EXECUETE    允许执行存储过程和包
FILE        允许在服务器的主机上通过LOAD DATA INFILE、SELECT ... INTO OUTFILE和LOAD_FILE()函数读写文件
GRANT OPTION    允许向其他用户授予或移除权限
INDEX   允许创建和删除索引
INSERT  允许向数据库的表中插入行
LOCK TABLE  允许执行LOCK TABLES语句来锁定表
PROCESS 允许显示在服务器上执行的线程信息，即被会话所执行的语句信息。这个权限允许你执行SHOW PROCESSLIST和mysqladmin processlist命令来查看线程，同时这个权限也允许你执行SHOW ENGINE命令
PROXY   允许用户冒充成为另外一个用户
REFERENCES  允许创建外键
RELOAD  允许使用FLUSH语句
REPLICATION CLIENT  允许执行SHOW MASTER STATUS,SHOW SLAVE STATUS和SHOW BINARY LOGS命令
REPLICATION SLAVE   允许SLAVE服务器连接到当前服务器来作为他们的主服务器
SELECT  允许从数据库中查询表
SHOW DATABASES  允许账户执行SHOW DATABASE语句来查看数据库。没有这个权限的账户只能看到他们具有权限的数据库。
SHOW VIEW   允许执行SHOW CREATE VIEW语句
SHUTDOWN    允许执行SHUTDOWN语句和mysqladmin shutdown已经mysql_shutdown() C API函数
SUPER   允许用户执行CHANGE MASTER TO,KILL或mysqladmin kill命令来杀掉其他用户的线程，允许执行PURGE BINARY LOGS命令，通过SET GLOBAL来设置系统参数，执行mysqladmin debug命令，开启和关闭日志，即使read_only参数开启也可以执行update语句，打开和关闭从服务器上面的复制，允许在连接数达到max_connections的情况下连接到服务器。
TRIGGER 允许操作触发器
UPDATE  允许更新数据库中的表
USAGE   代表没有任何权限，只能登陆

4.2.5 授权管理生产需求（试题）

1.授权一个管理员用户admin ,密码admin,能够通过10网段管理数据库

grant all on *.* to admin@'10.0.0.%' identified by 'admin';

2.开放一个wordpress应用的用户，密码123，nginx服务器的网段为172.16.1.%，应用用户需要哪些权限？

grant select,insert,update,delete on wordpress.* to wordpress@'172.16.1.%' identified by '123';

3.中小公司(没有专业DBA人员)，开发人员或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';

4.2.6 权限回收

#查询用户权限
show grants for dev@'10.0.0.%';

#收回delete权限
revoke delete on wordpress.* from dev@'10.0.0.%';

4.2.7 面试问题(授权用户的规范)

4.2.8 本地管理员用户密码忘记如何解决

#关闭数据库
/etc/init.d/mysqld stop

#将数据库启动到无验证模式
mysqld_safe --skip-grant-tables --skip-networking &

#修改密码
flush privileges;
alter user root@'localhost' identified by '123';

#重启数据库为正常模式
/etc/init.d/mysqld restart 

#测试新密码是否可以登录
mysql -uroot -p123

4.3 MySQL连接管理

4.3.1 MySQL自带命令

-u                   用户
-p                   密码
-h                   IP
-P                   端口
-S                   socket文件
-e                   免交互执行命令
<                    导入SQL脚本
--help

4.3.2 socket连接

mysql -uroot -p123 -S /tmp/mysql.sock

本地登录的用户，需要提前授权localhost相关用户
    grant all on *.* to oldli@'localhost' identified by '123';
    mysql -uoldli -p123 -S /tmp/mysql.sock

4.3.3 TCP/IP连接

mysql -uoldli -p123 -h10.0.0.51 -P3306

4.3.4 免交互执行命令 mysql -e

# mysql -e
mysql -uroot -p123456 -e "show databases"

4.3.5 导入数据 <

[root@db01 ~]# ls
world.sql

mysql -uroot -p123456 <world.sql

4.3.6 使用SQLyog客户端软件登录mysql

#授权root用户远程登录
grant all on *.* to root@'10.0.0.%' identified by '123';

4.3.7 第二种连接方法 Navicat客户端软件

功能更强大一些



4.4 启动和关闭方式

1.sys-V
    mysql.server

2.systemd
    mysqld

3.mysqld &

4.mysqld_safe &
    mysqld_safe --skip-grant-tables --skip-networking &

5.万能关闭方法: 可以关闭任何方式
    mysqladmin -uroot -p123456 shutdown

4.5 MySQL的初始化配置

4.5.1提供的方法

1.预编译☆☆
2.初始化配置文件☆☆☆☆☆
3.命令行☆☆☆

注意：优先级 3>2>1

4.5.2 初始化配置文件的管理

[root@db01 ~]# 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 &

4.5.3 初始化配置文件作用

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

4.5.4 初始化配文件格式

[标签1]
xxxx=yyyy
[标签2]
xxxx=yyyy
[标签3]
xxxx=yyyy
标签：
服务端
  [mysqld]
  [myslq_safe]
  [server]
客户端：
  [mysql]
  [mysqldump]
  [mysqladmin]
  [client]

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

mysqld_safe --defaults-file=<配置文件的路径> &

4.6 MySQL的多实例管理

4.6.1 准备多个目录

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

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

4.6.3 初始化三套数据

cp /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

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

4.6.5 授权

chown -R mysql.mysql /data/*

4.6.6 启动

systemctl restart mysqld3307.service
systemctl restart mysqld3308.service
systemctl restart mysqld3309.service

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

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 159,015评论 4赞 362
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,262评论 1赞 292
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 108,727评论 0赞 243
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,986评论 0赞 205
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,363评论 3赞 287
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,610评论 1赞 219
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,871评论 2赞 312
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,582评论 0赞 198
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,297评论 1赞 242
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,551评论 2赞 246
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,053评论 1赞 260
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,385评论 2赞 253
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,035评论 3赞 236
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,079评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,841评论 0赞 195
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,648评论 2赞 274
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,550评论 2赞 270