MySQL主從復(fù)制讀寫分離授權(quán)方案及實(shí)踐

當(dāng)配置好MySQL主從復(fù)制后，由于數(shù)據(jù)復(fù)制是單向的，所有對數(shù)據(jù)庫的更新操作都必須在主服務(wù)器上進(jìn)行，只有在主庫上更新，才能避免用戶對主服務(wù)器上數(shù)據(jù)庫內(nèi)容的更新與對從服務(wù)器上數(shù)據(jù)庫內(nèi)容的更新一致，而不會發(fā)生沖突。

MySQL復(fù)制環(huán)境用戶授權(quán)方案

生產(chǎn)授權(quán)方案1

方案1、2對比推薦使用方案1，生產(chǎn)環(huán)境中推薦使用忽略授權(quán)表方式授權(quán)

說明：
從庫的用戶名和密碼：主庫的web用戶同步到從庫，然后通過revoke權(quán)限回收將insert,delete,update權(quán)限回收。

主庫用戶授權(quán)示例：

grant select,insert,update,delete on dbName.* to 'userName'@'localhost' identified by 'password';

提示：特殊的業(yè)務(wù)可能權(quán)限會比較多，如果業(yè)務(wù)安全性要求不高，也可以給與all privileges權(quán)限。

從庫用戶授權(quán)示例：

grant select,insert,update,delete on dbName.* to 'userName'@'localhost' identified by 'password';

revoke insert,update,delete on on dbName.* from 'userName'@'localhost';
當(dāng)然也可以不回收權(quán)限，通過配置文件在[mysqld]區(qū)域設(shè)置read-only或者在mysql啟動時指定read-only參數(shù)。修改完記得重啟服務(wù)
另外：read-only參數(shù)可以讓slave服務(wù)器只允許來自slave服務(wù)器線程或者具有super權(quán)限（即對用戶授權(quán)時不能指定有super或all privileges權(quán)限）的用戶更新。
但可以確保slave服務(wù)器不接受來自普通用戶的更新，slave服務(wù)器啟動選項增加--read-only也是同樣的功能。

生產(chǎn)授權(quán)方案2

說明：
在主庫授權(quán)web_w賬號、從庫授權(quán)web_r賬號對應(yīng)權(quán)限權(quán)限；
對于開發(fā)而言，使用多套用戶名密碼不專業(yè)。

通過忽略授權(quán)表的方式防止數(shù)據(jù)寫入從庫的方法

在生產(chǎn)環(huán)境中，一般會采取忽略授權(quán)表方式同步，然后對從服務(wù)器(slave)上的用戶僅授權(quán)select讀權(quán)限。不同步mysql庫，這樣的話我們就保證了主庫和從庫相同的用戶可以授權(quán)不同的權(quán)限。在主從庫分別進(jìn)行如下授權(quán)：

忽略mysql庫和information_schema庫的主從同步參數(shù)

缺陷：從庫切換主庫時，連接用戶權(quán)限問題。可以保留一個從庫專門準(zhǔn)備接替主。

[mysqld]
binlog-do-db=dbName # 要同步的某個庫
replicate-ignore-db=mysql
binlog-ignore-db=mysql  #　不同步的庫
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

另外：忽略記錄binlog日志的參數(shù)binlog-ignore-db一般用于系統(tǒng)的庫和表。(修改完my.cnf后重啟服務(wù))

replication中忽略binlog數(shù)據(jù)量

master端參數(shù)：

--binlog-do-db 二進(jìn)制日志記錄的數(shù)據(jù)庫(多個數(shù)據(jù)庫用,分割)
--binlog-ignore-db 二進(jìn)制日志忽略的數(shù)據(jù)庫(多個數(shù)據(jù)庫用,分割)

slave端參數(shù)：

--replication-do-db 設(shè)定需要復(fù)制的數(shù)據(jù)庫(多個數(shù)據(jù)庫用,分割)
--replication-ignore-db 設(shè)定忽略復(fù)制的數(shù)據(jù)庫(多個數(shù)據(jù)庫用,分割)
--replocation-do-table 設(shè)定需要復(fù)制的表(多個表用,分割)
--replication-ignore-table 設(shè)定忽略復(fù)制的表(多個表用,分割)
--replication-wild-do-table和--replocation-do-table功能一樣，但是可以加通配符。
--replication-wild-ignore-table和--replication-ignore-table功能一樣，但是可以加通配符。