mysql日常維護

?1.mysql權(quán)限管理

1.1mysql賬戶權(quán)限信息被存儲在mysql數(shù)據(jù)庫的user 、db、host、tables_priv、column_priv和procs_priv表中，mysql啟動時服務(wù)器將這些數(shù)據(jù)庫表內(nèi)容讀入內(nèi)存。推薦grant和revoke語句

例如：

mysql>grant select,update on account.users to user@ip identified by 'passwd';

mysql>flush privileges;

創(chuàng)建成功后查看mysql數(shù)據(jù)庫表的變化

mysql>select * from user where user='user1'G

mysql>select * from db where user='user1'G

mysql>select * from tables_priv where user='user1'G

mysql權(quán)限按照user 、db、tables_priv、column_priv檢查順序，如果對應(yīng)表權(quán)限為Y，則不會檢查后面的表

1.2.查看或修改賬戶權(quán)限

例如：

mysql>show grants for user1@ip G

1.3 回收賬戶權(quán)限

mysql>revoke insert on *.* from test3@'%';

mysql>drop user test3@'%'

2.mysql日志管理

mysql服務(wù)支持的日志有二進制日志、錯誤日志、訪問日志和滿查詢?nèi)罩尽?/p>

2.1 二進制日志（binlog）

記錄所有DDL和DML操作，但不包括數(shù)據(jù)查詢語句，通過mysqlbinlog查看 例如：mysqlbinlog mysql-bin.000005|cat -n

2.1.1 刪除binlog，用purge binary logs 例如：

purge binary logs to 'mysql-bin.010'; ?//刪除指定序號之前的二進制日志

purge binary logs before '2016-04-02 22:46:26'; //刪除指定時間之前的二進制日志

2.1.2 指定參數(shù)設(shè)置二進制文件保留天數(shù)“expire_logs_days=#”

mysql>set global expire_logs_days=7; //自動清理7天之前的二進制日志文件

2.2 操作錯誤日志

通過設(shè)置“--logs-error=[file-name]” 指定錯誤日志存放位置，如沒有設(shè)置，則錯誤日志默認(rèn)位于mysql服務(wù)的datadir目錄下

例如：cat /dataa/master/dbdata/CentOS.err

2.3 訪問日志

記錄了所有關(guān)于客戶端發(fā)起的鏈接，查詢和更新語句，由于記錄了所有的操作，在相對繁忙的系統(tǒng)中建議關(guān)閉

在配置文件“--log=[file-name]” 指定訪問日志的位置，另外一種方法可以在登陸mysql實例后通過變量啟用此日志，如

mysql>set global general_log=on;

mysql>show variables like '%general_log%'; ?//查詢?nèi)罩疚恢?br />

2.4 慢查詢?nèi)罩?/p>

記錄了時間超過參數(shù)long_query_time（單位是秒）所設(shè)定值的SQL語句日志，對于審核和開發(fā)者發(fā)現(xiàn)性能問題及時優(yōu)化有重要意義。

如需啟用該日志可以在配置文件中設(shè)置 “slow_query_log" ，沒有指定文件名，則默認(rèn)hostname-slow.log作為文件名，并存放在數(shù)據(jù)目錄中

log-slow-queries=/usr/local/mysql....#定義慢查詢?nèi)罩韭窂?/p>

慢查詢?nèi)罩痉治龉ぞ?mysqldumpslow?

[-s] 排序參數(shù)，可選al:平均鎖定時間 ar:平均返回記錄數(shù) at：平均查詢時間；

[-t] 只顯示指定的行數(shù)；

3. mysql備份與恢復(fù)

3.1 備份方式可以通過直接備份數(shù)據(jù)文件或使用mysqldump命令將數(shù)據(jù)庫數(shù)據(jù)導(dǎo)出到文本文件，直接備份數(shù)據(jù)庫文件適用于MyISAM和InnoDB存儲引擎，由于備份時數(shù)據(jù)庫表正在讀寫，備份出的文件可能損壞無法使用，不推薦直接使用此方法。

mysqldump -u root test>test.sql //導(dǎo)出整個數(shù)據(jù)庫

mysqldump -u root test TBL_2 >test.TBL_2.sql //導(dǎo)出一個表

mysqldump -u root -d --add-drop-table test>test.sql //只導(dǎo)出數(shù)據(jù)庫表結(jié)構(gòu) -d沒有數(shù)據(jù) --add-drop-table 在每個create語句前增加一個drop table

mysql -uroot test<test.sql //恢復(fù)數(shù)據(jù)方法1

mysql -uroot -p

mysql>source /root/test.sql //恢復(fù)數(shù)據(jù)方法2

3.2 另外一種可以實時備份的開源工具為xtrabackup

http://www.percona.com/downloads/提供下載

4.mysql復(fù)制

復(fù)制功能可以經(jīng)濟高效地提高應(yīng)用程序的性能、擴展力和高可用性。既支持簡單的主從拓?fù)洌部梢?實現(xiàn)復(fù)雜、極具可伸縮性的鏈?zhǔn)郊骸?/p>

使用mysql復(fù)制時，所有對復(fù)制表的更新必須在主服務(wù)器上進行，否則可能引起主服務(wù)器上的表進行的更新與對從服務(wù)器上的表所進行的更新產(chǎn)生沖突。

好處：（1）主從數(shù)據(jù)同步，主服務(wù)器故障時，從服務(wù)器可作為主服務(wù)器接管服務(wù)。

（2）負(fù)載均衡。實現(xiàn)讀寫分離，主服務(wù)器記錄更新、刪除、插入等操作；從服務(wù)器只查詢請求；

（3）數(shù)據(jù)備份。從服務(wù)器可設(shè)置在異地，增加容災(zāi)的健壯性，為避免傳輸過慢，可設(shè)置參數(shù)slave_compressed_protocol 啟用binlog壓縮傳輸

mysql使用3個線程來執(zhí)行復(fù)制功能，1個在主服務(wù)器上，2個在從服務(wù)器上。當(dāng)執(zhí)行START SLAVE時，主服務(wù)器創(chuàng)建一線程負(fù)責(zé)發(fā)送二進制日志。從服務(wù)器創(chuàng)建一個I/O線程，負(fù)責(zé)讀取主服務(wù)器上的二進制日志，然后將該數(shù)據(jù)保存到從服務(wù)器數(shù)據(jù)目錄中的中繼日志文件中。從服務(wù)器的SQL線程負(fù)責(zé)讀取中繼日志并重做日志中包含的更新，從而達(dá)到主從數(shù)據(jù)庫數(shù)據(jù)的一致性。#在主從服務(wù)器上輸入命令查詢狀態(tài)：mysql>show processlist G 其中time列的值可以顯示從服務(wù)器比主服務(wù)器滯后多長時間。