MySQL體系結(jié)構(gòu)和管理學(xué)習(xí)筆記day02

1.MySQL C/S模型

Server : mysqld
Client :
socket : 僅本地連接使用
tcp/ip : 應(yīng)用連接使用
TCP/IP方式（遠(yuǎn)程、本地）：
mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket方式(僅本地)：
mysql -uroot -poldboy123 -S /tmp/mysql.sock

2. 實(shí)例 **

實(shí)例= mysqld ----> master thread ----> 干活的線程 + 預(yù)分配的內(nèi)存結(jié)構(gòu)
公司= boss 經(jīng)理 員工 辦公室

3.mysql的程序結(jié)構(gòu)

3.1 MySQL的專(zhuān)用管理和操作命令SQL

DDL 數(shù)據(jù)定義語(yǔ)言
DCL 數(shù)據(jù)控制語(yǔ)言
DML 數(shù)據(jù)操作語(yǔ)言
DQL 數(shù)據(jù)查詢(xún)語(yǔ)言

3.2 SQL語(yǔ)句的執(zhí)行過(guò)程

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

3.2.1 連接層

(1) 提供鏈接協(xié)議
socket
TCP/IP
(2) 驗(yàn)證 user password host port
(3) 提供連接線程
show [full] processlist;
功能: 接收SQL語(yǔ)句,返回結(jié)果.]

3.2.2 SQL層 *****

(1) 語(yǔ)法檢查和SQL_MODE檢查
(2) 語(yǔ)義檢查和權(quán)限檢查
(3) 解析預(yù)處理,生成解析樹(shù)(執(zhí)行計(jì)劃樹(shù))
(4) 優(yōu)化會(huì)根據(jù)自帶算法,選擇最優(yōu)的方案(代價(jià)模型算法)
代價(jià)? cpu io mem
(5) 選擇最優(yōu)的方案進(jìn)行執(zhí)行SQL
(6) 提供查詢(xún)緩存（默認(rèn)是沒(méi)開(kāi)啟的），會(huì)使用redis tair替代查詢(xún)緩存功能
(7) 提供日志記錄（日志管理章節(jié)）：binlog，默認(rèn)是沒(méi)開(kāi)啟的。

3.2.3 存儲(chǔ)引擎層（簡(jiǎn)單介紹）

相當(dāng)于文件系統(tǒng),將數(shù)據(jù)取出,再交給SQL層結(jié)構(gòu)化成表,返回給用戶(hù).

4. MySQL的邏輯結(jié)構(gòu) ***

4.1 庫(kù)：

庫(kù)名
庫(kù)屬性

4.2 表

表名
屬性
列:列名(字段),列屬性(數(shù)據(jù)類(lèi)型,約束等)
數(shù)據(jù)行(記錄)

5. MySQL的物理存儲(chǔ)結(jié)構(gòu)引入

5.1 庫(kù)的物理存儲(chǔ)結(jié)構(gòu)

用文件系統(tǒng)的目錄來(lái)存儲(chǔ)

5.2 表的物理存儲(chǔ)結(jié)構(gòu) ***

MyISAM（一種引擎）的表：
user.frm
user.MYD
user.MYI
InnoDB(默認(rèn)的存儲(chǔ)引擎)的表：
time_zone.frm：存儲(chǔ)列相關(guān)信息
time_zone.ibd：數(shù)據(jù)行+索引

5.3 InnoDB表底層存儲(chǔ)結(jié)構(gòu)引入(擴(kuò)展)*****

段 : 一個(gè)表就是一個(gè)的段(非分區(qū)表)
區(qū) : 固定大小1M, 連續(xù)的64個(gè)頁(yè)
頁(yè) : 最小的IO單元,默認(rèn)16K

6. MySQL基礎(chǔ)管理

6.1 用戶(hù)的管理

6.1.1 用戶(hù)的作用

6.1.2 用戶(hù)的定義

6.1.3 用戶(hù)管理操作

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可以自動(dòng)創(chuàng)建用戶(hù)并授權(quán).
(2)8.0以后必須先建用戶(hù)后授權(quán),grant只做授權(quán)功能.

6.2 權(quán)限管理

6.2.1 作用

6.2.2 權(quán)限定義

6.2.3 授權(quán)范圍

6.2.4 授權(quán)管理命令

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 超級(jí)管理員 .

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 授權(quán)管理生產(chǎn)需求

(1) 授權(quán)一個(gè)管理員用戶(hù)admin ,密碼admin,能夠通過(guò)10網(wǎng)段管理數(shù)據(jù)庫(kù)
oldguo[(none)]>grant all on . to admin@'10.0.0.%' identified by 'admin';
(2) 開(kāi)放一個(gè)wordpress應(yīng)用的用戶(hù),密碼123,nginx服務(wù)器使172.16.1.%網(wǎng)段.
應(yīng)用用戶(hù)需要權(quán)限?
select,insert,update,delete
grant select,insert,update,delete on wordpress.* to wordpress@'172.16.1.%' identified by '123';
(3) 中小公司,開(kāi)發(fā)人員leader,需要開(kāi)發(fā)用戶(hù)dev通過(guò)10網(wǎng)段對(duì)wordpress進(jìn)行開(kāi)發(fā)和管理
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 權(quán)限回收

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

6.2.7 面試問(wèn)題

作為開(kāi)發(fā)人員開(kāi)用戶(hù)需要怎么溝通?
以郵件形式,進(jìn)行開(kāi)用戶(hù)的申請(qǐng)
向誰(shuí)申請(qǐng)?
DBA權(quán)限管理人員
分管部門(mén)領(lǐng)導(dǎo)
DBA主管
提供哪些信息?
需要哪些權(quán)限
操作對(duì)象
從哪來(lái)訪問(wèn)MySQL

最為DBA,開(kāi)發(fā)人員找你開(kāi)用戶(hù)需要怎么溝通?
提供哪些信息?
需要哪些權(quán)限
操作對(duì)象
從哪來(lái)訪問(wèn)MySQL

開(kāi)發(fā)人員找你私下要root用戶(hù)權(quán)限?
不給!
金融類(lèi):
非本業(yè)務(wù)部分,不得提供超級(jí)管理員用戶(hù)的權(quán)限

6.2.8 本地管理員用戶(hù)密碼忘記

(1) 關(guān)閉數(shù)據(jù)庫(kù)

(2) 將數(shù)據(jù)庫(kù)啟動(dòng)到無(wú)驗(yàn)證模式

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

(3) 修改密碼

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

(4)重啟數(shù)據(jù)庫(kù)為正常模式

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

(5)測(cè)試新密碼

[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
注意:本地登錄的用戶(hù),需要提前授權(quán)l(xiāng)ocalhost相關(guān)用戶(hù)
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 客戶(hù)端工具連接

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

6.4 MySQL的啟動(dòng)和關(guān)閉方式

6.5 MySQL的初始化配置

6.5.1 提供的方法

1. 預(yù)編譯 **

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

3. 命令行 ***

注意: 優(yōu)先級(jí) 3>2>1

6.5.2 初始化配置文件的默認(rèn)讀取順序

[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,以上的默認(rèn)配置就不會(huì)生效了
[root@db01 ~]# mysqld_safe --defaults-file=/opt/oldguo.cnf &

6.5.3 初始化配置文件作用

數(shù)據(jù)庫(kù)的啟動(dòng)
客戶(hù)端的登錄

6.5.4 初始化配置文件格式

[標(biāo)簽1]
xxxx=yyyy
[標(biāo)簽2]
xxxx=yyyy
[標(biāo)簽3]
xxxx=yyyy

標(biāo)簽 :
服務(wù)端
[mysqld]
[mysqld_safe]
[server]
客戶(hù)端
[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的多實(shí)例管理

6.6.1 準(zhǔn)備多個(gè)目錄

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

6.6.2 準(zhǔn)備配置文件

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 初始化三套數(shù)據(jù)

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管理多實(shí)例

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 授權(quán)

chown -R mysql.mysql /data/*

6.6.6 啟動(dòng)

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

6.6.7 驗(yàn)證多實(shí)例

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"

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容