1 體系結構
1.1 C/S模型的體系結構
登錄方式：
# TCP方式(遠程本地)
mysql -u root -p 123 -h 192.168.1.1 -P 3306
# Socket方式（僅本地）
mysql -u root -p 123 -S /tmp/mysql.sock
默認不允許root用戶遠程登錄數據庫
1.2 實例介紹
實例=mysqld后臺守護進程+Master Thread+干活的Thread+預分配的內存
公司=老板+經理+員工+辦公室
1.3 mysqld程序運行原理
1.3.1 mysqld程序結構
1.3.2 一條SQL語句的執(zhí)行過程
1.3.2.1 連接層
（1）提供連接協(xié)議：TCP/IP、SOCKET
（2）提供驗證：用戶、密碼、IP、SOCKET
（3）提供專用連接線程：接收用戶SQL，返回結果
通過以下語句可以查看到連接線程的基本情況
3306 [(none)]>show processlist ;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
1.3.2.2 SQL層
（1）接收上層傳送的SQL語句
（2）語法驗證模塊：驗證語句語法
（3）語義檢查：判斷SQL語句的類型
DDL：數據定義語言
DCL：數據控制語言
DML：數據操作語言
DQL：數據查詢語言
（4）權限檢查：用戶對庫表有沒有權限
（5）解析器：語句執(zhí)行前，進行預處理，生成解析樹（執(zhí)行計劃，也就是多種執(zhí)行方案）
（6）優(yōu)化器：根據解析器得出的多種執(zhí)行計劃，進行判斷，選擇最優(yōu)的執(zhí)行計劃代價模型：資源（CPU IO MEM）的耗損評估性能好壞
（7）執(zhí)行器：根據最優(yōu)的執(zhí)行計劃，執(zhí)行SQL語句，產生執(zhí)行結果
執(zhí)行結果：在磁盤XXX位置上
（8）提供查詢緩存（默認沒開啟），會使用redis、tair替代查詢緩存
（9）提供日志記錄（默認沒開啟）：binlog
1.3.2.3 存儲引擎層
類似于文件系統(tǒng)，根據SQL層的執(zhí)行結果，從磁盤拿數據。將16進制的磁盤數據，交由SQL架構化成表，由連接層的專用線程返回給用戶。
1.4 邏輯結構
1.4.1 庫
庫名，庫屬性(字符集和校對規(guī)則)
1.4.2 表（二維表）
表名
表屬性
列：列名（字段），列屬性（數據類型，約束等）
數據行（記錄）
1.5 物理存儲結構
1.5.1 庫的物理存儲結構
用文件系統(tǒng)的目錄來存儲
1.5.2 表的物理存儲結構
MyISAM（一種引擎）的表：
-rw-r----- 1 mysql mysql 10816 Nov 9 21:30 user.frm #列的相關信息
-rw-r----- 1 mysql mysql 396 Nov 10 20:27 user.MYD #記錄（數據行）的相關信息
-rw-r----- 1 mysql mysql 4096 Nov 10 22:54 user.MYI #索引信息
InnoDB（默認的存儲引擎）的表：
-rw-r----- 1 mysql mysql 8636 Nov 9 21:30 time_zone.frm #列的相關信息
-rw-r----- 1 mysql mysql 98304 Nov 9 21:30 time_zone.ibd #數據行+索引
1.5.3 InnoDB的存儲結構
InnoDB 存儲引擎存儲數據行和索引的是.ibd文件，.ibd文件的最小IO單元是頁（16KB），數據存儲時分配一到多個連續(xù)的頁，這些連續(xù)的頁組成區(qū)，一張表就是一個段，其中包括多個區(qū)。
2 基礎管理
2.1 用戶、權限管理
2.1.1 用戶
作用：登錄，管理數據庫的邏輯對象（庫、表）
定義：用戶名@'白名單'
白名單：允許哪些IP能夠登錄數據庫，不寫白名單默認是@'%'
支持的配置方式：
bigsky@'%' # 所有地址
bigsky@'10.0.0.%' # 10.0.0.0/24網段地址
bigsky@'localhost' # 本地主機
bigsky@'10.0.0.5%' # 10.0.0.50~10.0.0.59
bigsky@'10.0.0.0/255.255.254.0'
bigsky@'node1' # node1是域名
管理操作：
增：
create user bigsky000@'10.0.1.%' identified by '123' ;
查：
desc mysql.user； #查詢 authentication_string
select user ,host,authentication_string from mysql.user ;
改：
alter user bigsky000@'10.0.1.%' identified by '456' ; #改密碼
update mysql.user set host='localhost' where user='root'; #改用戶的登錄ip
flush privileges;
刪：
drop user bigsky000@'10.0.1.%' ;
2.1.2 權限
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：以上所有權限，一般授予普通管理員
with grant option：超級管理員具備的，給別的用戶授權的功能
SELECT,INSERT, UPDATE, DELETE：一般授予應用用戶
2.1.3 授權命令
grant 權限 on 范圍 to 用戶 (with grant option)
范圍：
*.* ：管理員用戶
app.*,app.table：開發(fā)和應用用戶
1、授權管理員用戶遠程登錄
grant all on *.* to root@'10.0.1.%' ;
# grant all on *.* to root@'10.0.1.%' identified by '123'; # 8.0版本后不可在授權時同時配置密碼
2、授權應用用戶遠程登錄，并能操作app庫
grant select,insert,update, delete on app.* to app@'10.0.1.%' ;
2.1.4 查看授權
show grants for 用戶
3306 [(none)]> show grants for bigsky@'10.0.1.%' ;
+-----------------------------------------------------------+
| Grants for bigsky@10.0.1.% |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bigsky'@'10.0.1.%' |
| GRANT ALL PRIVILEGES ON `bigsky`.* TO 'bigsky'@'10.0.1.%' |
+-----------------------------------------------------------+
2.1.5 回收權限
revoke 權限 on 范圍 from 用戶
revoke create on bigsky.* from bigsky@'10.0.1.%' ;
2.1.6 本地管理員用戶密碼找回
必須停止數據庫，使用安全模式mysqld_safe啟動數據庫，加上參數--skip-grant-tables使連接層不加載授權表，此時登錄數據庫不進行驗證。推薦加上參數--skip-networking不讓TCP/IP方式登錄，只能本地socket登錄，防止在此時遭受攻擊。
[root@node1 ~]$ mysqld_safe --skip-grant-tables --skip-networking &
此時可以用任意密碼登錄
[root@node1 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
3306 [(none)]>
因啟動數據庫沒有加載授權表，不能直接用alter命令修改密碼，
3306 [(none)]> alter user root@'localhost' identified by '456' ;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
需要手工加載授權表flush privileges，再用alter修改密碼
3306 [(none)]>flush privileges ;
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>alter user root@'localhost' identified by '456' ;
Query OK, 0 rows affected (0.00 sec)
之后再重啟數據庫就可以了
2.2 連接管理
使用mysql命令連接數據庫，常見參數如下：
-u 用戶
-p 密碼
-h IP地址
-P 端口號
-S socket文件
-e 免交互執(zhí)行命令
< 導入SQL腳本
mysql -u root -p123 -h 192.168.1.1 -P 3306
mysql -u root -p123 -S /tmp/mysql.sock
mysql -u root -p123 -e 'select user,host,authentication_string from mysql.user'
2.3 多種啟動方式
mysqld_safe和mysqld一般是在臨時維護時使用。
還可以編寫systemd的service腳本，使用systemd來啟動mysqld進程
2.4 初始化配置
2.4.1 初始化配置的方法
預編譯
初始化配置文件（適用于所有的啟動方式）
命令行參數(僅限于mysqld_safe和mysqld)
2.4.2 初始化配置文件
初始化配置文件的默認讀取路徑
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
默認情況下，會依次讀取以上配置文件，如果有重復選項，會以最后一個文件的設置為準。但是如果啟動時加入了--defaults-file=xxxx時，以上所有文件都不會讀取。
配置文件的書寫方式：
[標簽]
配置項=xxxx
標簽類型：服務器端、客戶端
服務器標簽:
[mysqld]
[mysqld_safe]
[server] #包含以上兩種
客戶端標簽：
[mysql]
[mysqldump]
[client] #包含以上兩種
配置文件示例展示：
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
server_id=6 #多實例時用到，唯一標識一個實例，范圍1-65535
port=3306
socket=/tmp/mysql.sock
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt= [\\d]> #提示符格式
2.5 多實例的應用
以3個實例為例
2.5.1 準備多個數據庫目錄
創(chuàng)建3個數據庫文件夾
mkdir -p /data/330{7,8,9}/data
2.5.2 準備配置文件
準備3個my.cnf文件放在不同實例的文件夾下
cat > /data/3307/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/3307/data/
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
EOF
cat > /data/3308/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/3308/data/
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
EOF
cat > /data/3309/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/3309/data/
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
EOF
2.5.3 初始化3套數據庫
需要將/etc/my.cnf 文件進行重命名，防止初始化時讀取
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --user=mysql --basedir=/app/mysql --datadir=/data/3307/data --initialize-insecure
mysqld --user=mysql --basedir=/app/mysql --datadir=/data/3308/data --initialize-insecure
mysqld --user=mysql --basedir=/app/mysql --datadir=/data/3309/data --initialize-insecure
mv /etc/my.cnf.bak /etc/my.cnf
2.5.4 systemd管理多實例
復制原先配置的service文件
cp /etc/systemd/system/mysqld.service /etc/systemd/system/mysqld3307.service
cp /etc/systemd/system/mysqld.service /etc/systemd/system/mysqld3308.service
cp /etc/systemd/system/mysqld.service /etc/systemd/system/mysqld3309.service
修改其中的--defaults-file配置
sed -i 's/-file=.*/-file=\/data\/3307\/my.cnf/g' /etc/systemd/system/mysqld3307.service
sed -i 's/-file=.*/-file=\/data\/3308\/my.cnf/g' /etc/systemd/system/mysqld3308.service
sed -i 's/-file=.*/-file=\/data\/3309\/my.cnf/g' /etc/systemd/system/mysqld3309.service
2.5.5 授權
chown -R mysql:mysql /data/*
2.5.6 啟動多實例
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
2.5.7 驗證多實例
ss -lnp | grep 330
mysql -u
