2019-06-14

1. MySQL體系結(jié)構(gòu)與管理

1.1 MySQL C/S結(jié)構(gòu)介紹 ***

兩種連接方式：

TCP/IP：mysql -uroot -poldboy123 -h 10.0.0.51 -P3306

Socket：mysql -uroot -poldboy123 -S /tmp/mysql.sock

1.2 MySQL實(shí)例的構(gòu)成 ***

公司：老板? +? 經(jīng)理? + 員工 + 辦公區(qū)

實(shí)例： mysqld +? master thread? + 干活的Thread? + 預(yù)分配的內(nèi)存

1.3 MySQL中mysqld服務(wù)器進(jìn)程結(jié)構(gòu)

1.3.1 SQL語(yǔ)句引入

結(jié)構(gòu)化的查詢語(yǔ)言

DQL

DDL

DML

DCL

mysql> select user,host from mysql.user;

1.3.2 連接層

（1）提供連接協(xié)議

Socket?

TCPIP

（2）驗(yàn)證用戶名（root@localhost）密碼合法性肥卡，進(jìn)行匹配專門的授權(quán)表。

（3）派生一個(gè)專用連接線程（接收SQL，返回結(jié)果）

? mysql> show processlist;

思考：

忘記密碼的參數(shù)在哪做的手腳冷溃？

--skip-grant-tables?

--skip-networking ?

1.3.3 SQL層（優(yōu)化方面至關(guān)重要的）

（1）驗(yàn)證SQL語(yǔ)法和SQL_MODE

（2）驗(yàn)證語(yǔ)義

（3）驗(yàn)證權(quán)限

（4）解析器進(jìn)行語(yǔ)句解析桐款，生成執(zhí)行計(jì)劃（解析樹(shù)）

（5）優(yōu)化器（各種算法虹茶，基于執(zhí)行代價(jià)）桥爽，根據(jù)算法舌胶，找到代價(jià)最低的執(zhí)行計(jì)劃升敲。

代價(jià)：CPU? IO? MEM

（6）執(zhí)行器按照優(yōu)化器選擇執(zhí)行計(jì)劃答倡，執(zhí)行SQL語(yǔ)句，得出獲取數(shù)據(jù)的方法驴党。

（7）提供query cache(默認(rèn)不開(kāi))瘪撇，一般不開(kāi)，會(huì)用redis

（8）記錄操作日志（binlog）港庄，默認(rèn)沒(méi)開(kāi)

未完待續(xù)

1.3.4 存儲(chǔ)引擎層

真正和磁盤打交道的一個(gè)層次

根據(jù)SQL層提供的取數(shù)據(jù)的方法倔既，拿到數(shù)據(jù)，返回給SQL鹏氧，結(jié)構(gòu)化成表渤涌，再又連接層線程返回給用戶。

1.4 邏輯結(jié)構(gòu)

庫(kù)? ---> 表 ----》數(shù)據(jù)行（記錄）把还，字段（列）实蓬，屬性（列屬性，列約束吊履，列的數(shù)據(jù)類型等安皱，表屬性，權(quán)限）

表=====>數(shù)據(jù)行+列+元數(shù)據(jù)

1.5 物理結(jié)構(gòu)（引入）

1.5.1 宏觀

庫(kù)艇炎，存儲(chǔ)在操作系統(tǒng)的目錄中

表：

user表： MyISAM

user.frm? ? -----> 列的定義信息

user.MYD -----> 數(shù)據(jù)行

user.MYI? ? -----> 索引信息

time_zone表：InnoDB

time_zone.frm? ---->列的定義信息

time_zone.ibd? ---->數(shù)據(jù)行和索引

面試題：

說(shuō)明MyISAM和InnoDB在存儲(chǔ)方式上的異同酌伊？

1.5.2 微觀

段，區(qū)缀踪，頁(yè)

一個(gè)表就是一個(gè)段腺晾，mysql分配空間時(shí)至少分配一個(gè)區(qū)

每個(gè)區(qū)默認(rèn)是1M（64 個(gè) pages），MySQL最小的IO單元是PAGE(16K)

文件句柄辜贵，恢復(fù)rm的數(shù)據(jù)悯蝉。

2. 基礎(chǔ)管理

2.1 用戶管理

2.1.1 作用

登錄

管理數(shù)據(jù)庫(kù)對(duì)象

2.1.2 用戶的定義

用戶名@'白名單'

白名單？

oldguo@'10.0.0.51'

oldguo@'10.0.0.%'

oldguo@'10.0.0.5%'

oldguo@'10.0.0.0/255.255.254.0'

oldguo@'%'

oldguo@'oldguo.com'

oldguo@'localhost'

oldguo@'db01'

常用的：

oldguo@'10.0.0.%'

oldguo@'10.0.0.5%'

oldguo@'10.0.0.0/255.255.254.0'

oldguo@'localhost'

2.1.3 用戶管理

創(chuàng)建用戶

mysql> create user oldguo@'10.0.0.%' identified by '123';

查詢用戶

mysql> select user,host ,authentication_string from mysql.user;

小練習(xí)：

1. 創(chuàng)建一個(gè)oldboy用戶托慨，密碼oldboy123 能夠通過(guò)172.16.1網(wǎng)段登錄數(shù)據(jù)庫(kù)鼻由，并且查看用戶

刪除用戶

mysql> drop user oldguo@'10.0.0.%';

修改用戶

mysql> alter user root@'localhost' identified by '123';

2.2 權(quán)限管理

2.2.1 權(quán)限作用

控制用戶登錄之后能對(duì)MySQL對(duì)象做哪些命令。

2.2.2 權(quán)限的定義

MySQL的權(quán)限定義就是SQL語(yǔ)句。

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 : 以上所有權(quán)限蕉世，一般是普通管理員擁有的

with grant option：超級(jí)管理員才具備的蔼紧，給別的用戶授權(quán)的功能

8.0 版本新特性（了解）

加入了role的概念。

2.2.3 授權(quán)管理

（1）語(yǔ)法：

grant ALL? ? on? ? wordpress.* to? ? wordpress@'10.0.0.%'? identified by '123';

grant 權(quán)限? on? 范圍? to? 用戶? ? identified by '密碼' ?

grant select,update,insert,delete? ? on? 范圍? to? 用戶? ? identified by '密碼'

范圍：

*.*

wordpress.*

wordpress.t1

（2）例子：

1. 從windows中的navicat軟件使用root管理mysql數(shù)據(jù)庫(kù)

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

2. 創(chuàng)建 zhihu業(yè)務(wù)用戶能夠?qū)hihu業(yè)務(wù)庫(kù)進(jìn)行業(yè)務(wù)操作

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

（3）思考一個(gè)問(wèn)題（課后自己進(jìn)行驗(yàn)證）：

1. grant select,update on *.* to oldboy@'10.0.0.%';

2. grant delete on wordpress.* to oldboy@'10.0.0.%';

3. grant insert on wordpress.t1 to oldboy@'10.0.0.%';

問(wèn)狠轻，oldboy@'10.0.0.%' 能對(duì)t1表具備什么權(quán)限奸例？

MySQL中的權(quán)限是可以繼承，多次授權(quán)是疊加的向楼。

所以查吊，想要取消某個(gè)權(quán)限，必須通過(guò)回收的方式實(shí)現(xiàn)湖蜕，而不能多次重復(fù)授權(quán)逻卖。

（4）查看用戶權(quán)限

mysql> show grants for root@'localhost';

（5）回收權(quán)限

mysql> revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';

面試題：

1.開(kāi)發(fā)人員找DBA開(kāi)用戶，需要DBA和開(kāi)發(fā)人員溝通什么昭抒？

（1）你要做哪些權(quán)限的操作

（2）你要從什么地址來(lái)登數(shù)據(jù)庫(kù)

（3）要對(duì)什么對(duì)象進(jìn)行管理操作评也？

2.開(kāi)發(fā)人員找DBA要管理員root用戶的密碼，作為DBA你怎么處理灭返？（金融公司）

情況一：

（1）不給

（2）在金融公司嚴(yán)令禁止盗迟，舉報(bào)！Ｎ鹾罚缕！

情況二（小公司）

root用戶是亂用，學(xué)會(huì)保護(hù)自己婆芦。

可以提意見(jiàn)怕磨，生成流程。

3. MySQL 連接管理

3.1 自帶的客戶端工具

mysql 參數(shù)

-u

-p

-S

-h

-P

[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock

[root@db01 ~]# mysql -uoldguo -p -h10.0.0.51 -P3306

問(wèn)題：

怎么證明你的數(shù)據(jù)庫(kù)是可用的消约？

1. 證明進(jìn)程和端口存在

ps -ef |grep mysqld

netstat -lnp|grep mysqld

2. 驗(yàn)證用戶遠(yuǎn)程連接性

[root@db01 ~]# mysql -uoldguo -p -h10.0.0.51

-e

[root@db01 ~]# mysql -uoldguo -p123? -h10.0.0.51 -e "show databases;"

[root@db01 ~]# mysql -uroot -p <world.sql

3.2 遠(yuǎn)程的客戶端工具

navicat? sqlyog

略肠鲫。

4. MySQL 啟動(dòng)方式介紹

輔助腳本方式（普通的啟動(dòng)關(guān)閉）：

sys-v? : /etc/init.d/mysqld?

/etc/init.d/mysqld? start

----> /application/mysql/bin/mysqld_safe

----> /application/mysql/bin/mysqld

systemd : /etc/systemd/system/mysqld.service

systemctl start mysqld?

---->/application/mysql/bin/mysqld

維護(hù)性的啟動(dòng)方式：

/application/mysql/bin/mysqld_safe? --skip-grant-tables --skip-networking &

/application/mysql/bin/mysqld &

5. MySQL 初始化配置

5.1 預(yù)編譯時(shí)進(jìn)行設(shè)置（略）

只能在編譯安裝中實(shí)現(xiàn)

硬編碼配置到程序中

5.2 初始化配置文件(my.cnf)

5.2.1 初始化配置文件默認(rèn)讀取位置

[root@db01 ~]# mysqld --help --verbose |grep "my.cnf"

/etc/my.cnf --》 /etc/mysql/my.cnf --》 /usr/local/mysql/etc/my.cnf? --》 ~/.my.cnf

--defaults-file=/opt/my.cnf

mysqld_safe

mysqld?

5.3 命令行模式

[root@db01 ~]# mysqld_safe --defaults-file=/opt/my.cnf --socket=/tmp/asdad &

5.4 初始化配置文件應(yīng)用

5.4.1 作用

數(shù)據(jù)庫(kù)的啟動(dòng)：mysqld? mysqld_safe? ?

客戶端的連接：mysql? mysqldump? mysqladmin

5.4.2 書(shū)寫(xiě)格式

[程序名]

配置項(xiàng)=xxx

服務(wù)端

[server]

[mysqld]

[mysqld_safe]

客戶端

[client]

[mysql]

[mysqldump]

5.4.3 mysql初始化配置常用參數(shù)(通用模板)

[mysqld]

user=mysql

basedir=/application/mysql

datadir=/data/mysql/data

socket=/tmp/mysql.sock

server_id=6

port=3306

log_error=/data/mysql/data/mysql.log

log_bin=/data/mysql/data/mysql-bin? ?

[mysql]

socket=/tmp/mysql.sock

5.5 MySQL 多實(shí)例

5.5.1 創(chuàng)建目錄

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

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

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

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

5.5.5 授權(quán)

chown -R mysql.mysql /data/*

5.5.6 啟動(dòng)

systemctl start mysqld3307.service

systemctl start mysqld3308.service

systemctl start mysqld3309.service

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

最后編輯于：2019.06.20 15:43:40

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市或粮，隨后出現(xiàn)的幾起案子导饲，更是在濱河造成了極大的恐慌，老刑警劉巖氯材，帶你破解...
沈念sama閱讀 218,122評(píng)論 6贊 505
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件渣锦，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡氢哮，警方通過(guò)查閱死者的電腦和手機(jī)袋毙，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,070評(píng)論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)冗尤，“玉大人听盖，你說(shuō)我怎么就攤上這事胀溺。” “怎么了皆看？”我有些...
開(kāi)封第一講書(shū)人閱讀 164,491評(píng)論 0贊 354
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵仓坞，是天一觀的道長(zhǎng)。經(jīng)常有香客問(wèn)我腰吟，道長(zhǎng)无埃，這世上最難降的妖魔是什么？我笑而不...
開(kāi)封第一講書(shū)人閱讀 58,636評(píng)論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任毛雇，我火速辦了婚禮嫉称，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘禾乘。我一直安慰自己澎埠，他們只是感情好虽缕，可當(dāng)我...
茶點(diǎn)故事閱讀 67,676評(píng)論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布始藕。她就那樣靜靜地躺著，像睡著了一般氮趋。火紅的嫁衣襯著肌膚如雪伍派。梳的紋絲不亂的頭發(fā)上，一...
開(kāi)封第一講書(shū)人閱讀 51,541評(píng)論 1贊 305
城市分裂傳說(shuō)
那天剩胁，我揣著相機(jī)與錄音诉植，去河邊找鬼。笑死昵观，一個(gè)胖子當(dāng)著我的面吹牛晾腔，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播啊犬，決...
沈念sama閱讀 40,292評(píng)論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼灼擂，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來(lái)了觉至？” 一聲冷哼從身側(cè)響起剔应，我...
開(kāi)封第一講書(shū)人閱讀 39,211評(píng)論 0贊 276
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎语御，沒(méi)想到半個(gè)月后峻贮，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,655評(píng)論 1贊 314
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡应闯，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,846評(píng)論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年纤控，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片碉纺。...
茶點(diǎn)故事閱讀 39,965評(píng)論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡船万，死狀恐怖细层，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情唬涧，我是刑警寧澤疫赎，帶...
沈念sama閱讀 35,684評(píng)論 5贊 347
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站碎节，受9級(jí)特大地震影響捧搞，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜狮荔，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,295評(píng)論 3贊 329
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一胎撇、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧殖氏，春花似錦晚树、人聲如沸。這莊子的主人今日做“春日...
開(kāi)封第一講書(shū)人閱讀 31,894評(píng)論 0贊 22
一樁弒父案爵憎，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)。三九已至婚瓜，卻和暖如春宝鼓，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背巴刻。一陣腳步聲響...
開(kāi)封第一講書(shū)人閱讀 33,012評(píng)論 1贊 269
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工愚铡，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人胡陪。一個(gè)月前我還...
沈念sama閱讀 48,126評(píng)論 3贊 370
代替公主和親
正文我出身青樓沥寥，卻偏偏與公主長(zhǎng)得像，于是被迫代替她去往敵國(guó)和親柠座。傳聞我的和親對(duì)象是個(gè)殘疾皇子邑雅，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,914評(píng)論 2贊 355