mysql數據庫三：

MySQL用戶和權限管理   （show processlist :查看mysql的進程 kill+進程編號：殺掉進程）常用

用戶管理 ；

查看用戶賬號及密碼：

select user,password,host from mysql.user;

創建用戶；

create user 'lv'@'192.168.60.2'   identified by 'centos'; （指定用戶名并指定在哪臺主機登陸，設置密碼為centos）此用戶只能在192.168.60.2 的主機登陸且用戶只能為lv 。

連接：mysql  -ulv -p密碼 -h192.168.60.4（數據庫主機的IP地址）

用戶重命名；

RENAME USER old_user_name@host TO new_user_name @host

rename user wang@192.168.60.5 to lv@192.168.60.8;

刪除用戶；

DROP USER 'USERNAME'@'HOST'

drop user 'lv'@'192.168.60.5';

示例：刪除默認的空用戶 (所謂的匿名賬號)

DROP USER   '  '@'localhost'

新建的用戶雖然可以登陸數據庫，但無法查看庫中的表因為沒有權限。

FLUSH PRIVILEGES  （刷新數據）

修改密碼；

mysql>SET PASSWORD FOR 'user'@'host' = PASSWORD(‘new_password');

set password for lv@'192.168.60.8'=password('msd');

mysqladmin -u root –poldpass password ‘newpass‘  （也可以更改舊口令）

或者使用update更改表的方式來修改密碼；

忘記管理員密碼的解決辦法：

vim /etc/my.cnf  (編輯此文件在下面的標題下添加下面一行)

[mysqld]

skip-grant-tables  （意思是跳過授權）

systemctl restart mariadb  （重新啟動服務）

就可以登陸了，但僅限這一次登陸后要修改密碼。

進入到use mysql庫里查看用戶表：select user,password,host from user;

使用UPDATE命令修改管理員密碼

 

MySQL權限管理

權限類別：

數據庫級別

表級別

字段級別 管理類 程序類
授權；

1 . 直接授權：

grant all on hellodb.* to lv@'192.168.60.%';

all 代表所有權限；192.168.60.% ：這樣寫代表，192.168.60這個網段的所有主機，hellodb.* 為只對hellodb下的表有操作權限，但對其他庫沒有操作權限。

可以授權單獨的某一項操作命令：

grant select，insert  on hellodb.* to lv@'192.168.60.2';  (lv用戶對hellodb庫下的所有表，只有查詢和插入的權限)

grant select，insert on hellodb.students to lv@'192.168.60.2'

(對students單個表授權操作，對hellodb庫下的其他表沒有權限)

grant select（name,id)  on hellodb.students to lv@'192.168.60.2' 

(針對特定表的某個列進行查詢)

2 . 連創建用戶帶授權：

grant select,insert on hellodb.* to mage@'192.168.33.3' identified by 'centos';

（權限設定）                                                                                            （設定密碼）

3 . 回收授權：REVOKE

revoke delete on hellodb.* from lv@'192.168.60.%'; （回收某一項權限；delete:刪除權限）

revoke all on hellodb.* from lv@'192.168.60.%';         （回收所有的權限；all；代表剩余所有的權限）

4 . 查看已授權的用戶所擁有的權限：

show grants;（查看本機root用戶的權限）

show grants for lv@'192.168.60.%' ； （查看某個用戶所擁有的權限）

 

注意：

MariaDB服務進程啟動時會讀取mysql庫中所有授權表至內存 (1) GRANT或REVOKE等執行權限操作會保存于系統表中，MariaDB的服務進程通常會自 動重讀授權表，使之生效

(2) 對于不能夠或不能及時重讀授權表的命令，可手動讓MariaDB的服務進程重讀授權 表：

mysql> FLUSH PRIVILEGES;

 

 

索引；

索引是特殊數據結構：定義在查找時作為查找條件的字段

優點：提高查詢速度，

缺點：占用額外空間，影響插入速度

索引類型： ?

聚簇（集）索引、非聚簇索引：數據是否與索引存儲在一起 ?

主鍵索引、輔助索引 ?

稠密索引、稀疏索引：是否索引了每一個數據項 ?

B+ TREE、HASH、R TREE ?

簡單索引、組合索引

左前綴索引：取前面的字符做索引

覆蓋索引：從索引中即可取出要查詢的數據，性能高

 

B+TREE索引 ：（常用的索引）

B+ Tree索引：順序存儲，每一個葉子節點到根結點的距離是相同的；左 前綴索引，適合查詢范圍類的數據

全值匹配：精確所有索引列，如：姓wang，名xiaochun，年齡30

匹配最左前綴：即只使用索引的第一列，如：姓wang

匹配列前綴：只匹配一列值開頭部分，如：姓以w開頭的

匹配范圍值：如：姓ma和姓wang之間

精確匹配某一列并范圍匹配另一列：如：姓wang,名以x開頭的 只訪問索引的查詢

B-Tree索引的限制：

如果不從最左列開始，則無法使用索引：如：查找名為xiaochun，或姓為g結 尾 ?

不能跳過索引中的列：如：查找姓wang,年齡30的，只能使用索引第一列

如果查詢中某個列是為范圍查詢，那么其右側的列都無法再使用索引：如： 姓wang,名x%,年齡30，只能利用姓和名上面的索引 ?

 

Hash索引

Hash索引：基于哈希表實現，只有精確匹配索引中的所有列的查詢才有效，索 引自身只存儲索引列對應的哈希值和數據指針，索引結構緊湊，查詢性能好 ?

只支持等值比較查詢，包括=, IN(), <=>

 

管理索引 ；

1 . 創建索引

create index index_age on students(age); （對students表添加以age列的索引；index_age;是定義索引的新名稱）

2 . 刪除索引

drop index index_age on students; （對students表刪除索引，索引的名字為index_age ）

3 . 查看索引

show indexes from students\G;（查看此表students中的索引）Column_name為索引的稱號前；

4 .優化表空間

OPTIMIZE TABLE tb_name

 

并發控制 ；

鎖的含義：當一個數據庫服務器的某一個表同時被多個用戶修改表數據時，存儲引擎會自動對此表進行加鎖；其他用戶只能對此表進行讀，而且讀的是未被修改的數據。只有數據修改完成，同步之后，才可以看到修改后的數據。

1 .鎖粒度：

表級鎖 （myisun只支持表級的加鎖）

行級鎖  （innodb支持行級鎖）

讀鎖：共享鎖，只讀不可寫，多個讀互不阻塞， 自己也不可以對其進行修改。

寫鎖：獨占鎖,排它鎖，一個寫鎖會阻塞其它讀和它鎖 ，自己可以讀，寫

實現

存儲引擎：自行實現其鎖策略和鎖粒度

服務器級：實現了鎖，表級鎖；用戶可顯式請求

顯示使用鎖 ：

 

添加鎖；

lock tables students write;  （添加寫鎖）

lock tables students read;  (對某個表添加讀鎖)

解鎖：

unlock tables;

 

 

 

事務和日志

事務Transactions：一組SQL語句，或一個獨立工作單元的一組 SQL語言

事務日志：記錄事務信息，實現undo,redo等故障恢復功能

ACID特性：

A：atomicity原子性；整個事務中的所有操作要么全部成功執行，要么全部失 敗后回滾 ?

C：consistency一致性；數據庫總是從一個一致性狀態轉換為另一個一致性狀 態 ?

I：Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務 所見；隔離有多種隔離級別，實現并發 ?

D：durability持久性；一旦事務提交，其所做的修改會永久保存于數據庫中 ：（如果沒有提交的話則不會寫入磁盤的。）

啟動事務：START TRANSACTION （start transaction）

結束事務：

(1) COMMIT：提交

(2) ROLLBACK: 回滾 (事物開啟后如果沒提交之前是可以撤銷之前所做的操作的)

注意：只有事務型存儲引擎方能支持此類操作 （既innodb支持myision是不支持的）

隱性事物：（mysql默認使用）每一個select語句為一個事物，每一個事物提交執行一次

顯性事物：有明顯的開始結束標志，中間多個select語句寫完一起執行。

show variables like 'autocommit';  （查看自動提交事物處于什么狀態）

set global autocommit=0 (全局) 但在別的終端使用時，需要重新登陸。

set autocommit=0   （僅是再此次開啟的mysql生效）在mysql里進行設置。

永久開啟需要寫進配置文件：vim /etc/my.cnf

[mysqld]
autocommit=0  (將自動提交事物關閉)
只要不提交
rollbock(撤銷操作)；此撤銷操作僅局限對于表的的增刪改查操作；如果刪除表和庫以及新建表和庫都無法撤銷操作的
commit (提交操作)

 

單獨開啟事物：

start transaction；  (開啟臨時事物)

………

……….（中間為SQL語句）

……..

…….

commit； (提交操作)/rollback； (撤銷操作)

 

事務隔離級別

READ UNCOMMITTED 可讀取到未提交數據，產生臟讀 ?

READ COMMITTED 可讀提交數據，但未提交數據不可讀，產生不可重 復讀，即可讀取到多個提交數據，導致每次讀取數據不一致 ?

REPEATABLE READ 可重復讀，多次讀取數據都一致，產生幻讀，即讀 取過程中，即使有其它提交的事務修改數據，仍只能讀取到未修改前 的舊數據。此為MySQL默認設置 ；直到自己的事物提交后再次查看數據，才能看到已經更新的數據了。?

SERIALIZABILE 可串行化，未提交的讀事務阻塞修改事務，或者未提交 的修改事務阻塞讀事務和修改事物。導致并發性能差

 

系統默認隔離狀態下

死鎖狀態；1用戶更改a表；2用戶更改b表，都沒有提交時；1用戶再去更改b表的同一行，2用戶再去更改1表同一行；此狀態進入死鎖狀態。

如果進入到死鎖狀態系統會自動殺掉等待時間短的事物

查看mysql數據庫的執行的那些進程：show processlist :
如果事物一直未提交，影響其他用戶訪問時；
kill +進程編號 （殺掉進程）
殺掉沒提交的事物，修改的數據不會生效。

 

 

日志

事務日志：transaction log ?

錯誤日志：error log ?

查詢日志：query log ?

慢查詢日志：slow query log ?

二進制日志：binary log ?

中繼日志：reley log

 

1 .  事物日志
事物日志寫入類型是追加的，不是動態更新的，只有退出mysql數據庫時才寫入。只是記錄本機的操作。
事務日志和數據庫文件一般不存放到一張磁盤里的。
redo log （事物已經做完了，沒寫到磁盤里，斷電了，重啟后會自動再次執行已經做完的事物，并寫入到磁盤里去）
undo log (沒提交的事物，斷電重啟后會撤銷未執行的事物)

 

show variables like '%innodb_log%';  （查看事物日志的相關變量的信息）

事物日志默認5M大小，2個文件；當第一個文件寫滿了，去第二個文件里寫，第二個文件寫滿后，回去覆蓋第一個的文件。（存放的路徑/var/lib/mysql/ib_logfile0;ib_logfile1）
生產中將文件的容量調大，文件個數加多
需要修改配置文件：vim /etc/my.cnf

[mysqld]
innodb_log_files_in_group=3 (原有日志文件存在可能不行，需要將原有的文件移走或者刪除，就可以再生成了)  增加文件個數

或者：innodb_log_files_in_group=3
innodb_log_group_home_dir=/data/msq/ （重新指定存放的目錄）

重啟服務：如果起不來可能是因為selinux或者防火墻：getenforce(查看selinux的當前狀態)

setenforce=0 (臨時關閉selinux)

 

2 . 錯誤日志：

log-error=/var/log/mariadb/mariadb.log  (默認路徑)
在配置文件里 /etc/my.cnf可以修改路徑：
修改路徑的文件存放錯誤日志，要確保此文件對mysql有寫的權限。

重啟服務：如果起不來可能是因為selinux或者防火墻：getenforce(查看selinux的當前狀態)
setenforce=0 (臨時關閉selinux)

 

3  .通用日志：（默認不啟用）
記錄所有用戶執行命令的記錄（包括遠程用戶的主機登陸本機的數據庫）

查看當前的狀態：show variables like 'general_log%';
啟用： set general_log=ON|OFF (非全局改)

set  global general_log=ON|OFF (全局更改)

記錄日志的文件：/var/lib/mysql/centos7.log (當開啟此服務時此文件才生成)

show variables like 'log_output'   (查看此時此日志是以什么方式來存放的)

set global log_output="table"  （將此日志文件以表的方式來存放，易于讀看）

記錄以表形式的文件路徑： 在mysql庫里有張general_log的一張表
(一般不啟用，數據太大會占用磁盤空間)

 

 

4  .  慢查詢記錄日志

show variables like 'slow_query_log';（查看慢查詢的當前狀態）
set  global  slow_query_log=ON|OFF （開啟或者關閉慢查詢記錄）

show variables like 'long_qu%'(查看當前慢查詢的閾值)
long_query_time=N (記錄慢查詢的閾值，默認10s;超過10s的查詢記錄就會被記錄到慢查詢日志里)

slow_query_log_file=HOSTNAME-slow.log

當發現一個SQL語句執行超過10s會被記錄到慢查詢日志里去。

查詢時沒有使用索引的查詢記錄也會被記錄到慢查詢日志里讓你可以再次優化它。

慢查詢日志的文件路徑：/var/lib/mysql/centos7-slow.log  （此文件也是在開啟慢查詢是生成的文件）

profiling=on|off (默認關閉的)

show variables like 'profiling';  （查看此服務的狀態）

set profiling=on; （臨時開啟此服務）

show profiles (查看執行的進程信息)
show profile for qurey 10023 （查看單個進程哪個部分花費的時間）

 

5  .  中繼日志：relay log 主從復制架構中，從服務器用于保存從主服務器的二進制日志中讀取到的事件

 

 

6  . 二進制日志：注意：建議二進制日志和數據文件分開存放
記錄對數據庫操作的命令；（SQL語句）
記錄已提交的事物，未完成的事物不會記錄到二進制文件日志里
通過‘重放’來對來生成數據副本

a   .二進制日志記錄三種格式 ;

基于“語句”記錄：statement，記錄語句，默認模式

基于“行”記錄：row，記錄數據，日志量較大  (推薦使用)

混合模式：mixed, 讓系統自行判定該基于哪種方式進行

查看現在基于什么記錄：show variables like '%binlog_format%';

更改記錄的模式：

set binlog_format=row

set binlog_format=mixed

b  .二進制日志文件的構成
有兩類文件；
日志文件：mysql-bin.000001
索引文件：mysql-bin.index

c  . 二進制文件的啟用

set sql_log_bin=ON|OFF：是否記錄二進制日志，默認ON啟用(select @@sql_log_bin;查看當前狀態)

（0代表禁用 ；1代表啟用）

log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默認OFF，表示不啟用二進制 日志功能(select @@log_bin;查看當前狀態  ；0代表禁用；1代表啟用)

此項不支持命令行的修改，需要到配置文件里去修改。

vim /etc/my.cnf

[mysqld]

log_bin=/data/msq/mysql-bin   (此為指定路徑，mysql-bin為指定的前綴)

重啟服務后就會在/data/msq/mysql-bin.000001(生成此文件)并生成mysql-bin.index（索引文件）
上述兩項都開啟才可

d   二進制文件里的變量：

max_binlog_size=1073741824：單個二進制日志文件的最大體積，到達最大值 會自動滾動，自動生成第二個文件，默認為1G

(show variables like 'max_binlog_size';查看現在設置的大小)

（set global max_binlog_size=222222;設定文件的大小，但最大只能為1G不能設定超過1G的。（只在全局）

 

sync_binlog=1|0：設定是否啟動二進制日志即時同步磁盤功能，默認0，由操 作系統負責同步日志到磁盤

（ show variables like 'sync_binlog';查看此時的狀態值；set global sync_binlog=1;更改設置 ）

 

expire_logs_days=N：二進制日志可以自動刪除的天數。 默認為0，即不自動 刪除

（show variables like 'expire_logs%'，查看此時狀態值；set global expire_logs_days=3；設定自動刪除的天數）

 

e  二進制文件的查看和使用

show master logs;  （查看二進制文件的個數和大?。?/span>

show master status;（查看當前二進制文件處于什么狀態，里面的數值就是它改變的位置值）

show binlog events in 'mysql-bin.000001'; （查看此二進制文件里記錄的事件）

show binlog events in 'mariadb-bin.000001' from 6516 limit 2,3

（limit 2,3 從6516的位置開始讀取跳過開始的2個位置，讀取之后的3個位置的）

專業的二進制查看工具：

mysqlbinlog mysql-bin.000001  -v （此命令需要在linux命令行里操作，直接打開二進制文件）

at245…………at336(中間為一個事物；at245為一個事物的開始標記；at336為此事物的結束標記)

mysqlbinlog mysql-bin.000001 > sss.sql（將此二進制文件導入到一個文件里，可供使用）

mysql < sss.sql  (重新走一遍二進制的腳本之后在此腳本記錄的更改的內容就會還原了)

mysqlbinlog mysql-bin.000001 -v –start-position=309 –stop-position=416;  （具體查看某一位置的內容）

–start-datetime="2018-01-30 20:30:10" –stop-datetime="2018-01-30 20:35:22"  （指定時間段）

 

f . ?清除指定二進制日志：

先進入到數據庫里：

show master logs;  （查看二進制文件的個數和大?。?/p>

purge binary logs to 'mysql-bin.000002'; （刪除到002的二進制文件不包括002文件）

PURGE BINARY LOGS BEFORE '2017-01-23';   (刪除此日期之前的二進制日志)

PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30'; （刪除此日期之前的二進制日志）

刪除所有二進制日志，index文件重新記數 ：

reset master;（刪除所有二進制日志文件，重新生成一號文件開始記錄）

切換日志文件： FLUSH LOGS；（原來的二進制日志文件不在記錄，重新生成一個二進制文件記錄內容）

（此命令在數據庫里執行）