Mysql營運管理-生產情境Mysql主從複製讀寫分離授權方案及實戰15

標籤：注意   ase   resolve   exists   https   插入   read   man   復原事務   

1.生產MySQL複製環境使用者授權方案

那麼怎麼才能達到上述效果呢？

(1) 生產環境主庫使用者授權

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘ identified ‘123456‘;

提示：特殊業務可能許可權會略多，如果業務安全性不高也可以all privileges

(2) 生產環境從庫使用者的授權

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘ identified ‘123456‘;mysql> REVOKE INSERT,UPDATE,DELETE ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘;

(3) 生產授權案例說明：這裡表示給192.168.10.%的使用者blog管理blog資料庫的所有表（*表示所有表）唯讀許可權（SELECT），密碼為123456。

GRANT SELECT,INSERT,UPDATE,DELETE ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘ identified ‘123456‘;

生產環境主從庫使用者授權

主庫：

GRANT SELECT,INSERT,UPDATE,DELETE ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘ identified ‘123456‘;

從庫：

GRANT SELECT ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘ identified ‘123456‘;

如何?上述授權方案

最簡單的方法是在主庫配置binlog-ignore-db=mysql2.忽略授權表的方式防止資料寫從庫的方法及實踐

生產環境中一般採用忽略授權表的方式同步，然後對從伺服器（slave）上的使用者僅授權select讀許可權，不同步mysql庫，這樣我們就保證主庫和從庫相同的使用者可以授權不同的許可權。指定mysql庫不同步。
忽略mysql和information_schema庫的主從同步。

replicate-ignore-db=mysqlbinlog-ignore-db = mysqlbinlog-ignore-db = performance_schemabinlog-ignore-db = information_schema

提示：如何在主庫上忽略mysql庫的同步，方法：

（1）只有在[主從]庫上分別設定replicate-ignore-db=mysql才可以做到從庫不同步mysql庫。

（2）在主庫上設定binlog-ignore-db=mysql不記錄有關mysql庫更新的binlog來達到從庫不同步mysql庫。3.通過read-only參數防止資料庫寫從庫的方案

除了上面從庫僅做SELECT的授權外，還可以在slave伺服器啟動選項增加參數或者在my.cnf設定檔中加read-only參數來確保從庫唯讀，使用授權使用者和read-only參數二者同時操作效果更佳。
注意read-only參數可以讓slave伺服器只允許來自slave伺服器線程或具有SUPER許可權的使用者的更新。可以確保slave伺服器不接受來自普通使用者的更新。

（1）配置從庫my.cnf設定檔mysqld下重啟從資料庫

[[email protected] ~]# egrep "\[mysqld]|read-only" /data/3307/my.cnf [mysqld]read-only[[email protected] ~]# /data/3307/mysql stopStoping MySQL....[[email protected] ~]# /data/3307/mysql startStarting MySQL......

（2）read-only參數對SUPER許可權的使用者無效，用SUPER使用者登入建立一個普通使用者

mysql> grant select,insert,update,delete on *.* to ‘nana‘@‘localhost‘ identified by ‘123456‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

（3）在school庫中建立一個表退出用普通使用者登入，在建立的表中插入一條記錄，示範read-only的效果。

[[email protected] ~]# mysql -unana -p123456 -S /data/3307/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.5.32 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> select user();+----------------+| user() |+----------------+| [email protected] |+----------------+1 row in set (0.00 sec)mysql> use school;Database changedmysql> show tables;+------------------+| Tables_in_school |+------------------+| t|+------------------+1 row in set (0.00 sec)mysql> insert into t values(2);ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

（4）最後我們看一下是否能同步主庫

主庫中插入資料

[[email protected] ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock <<EOF> use linzhongniao> insert into test1 values(4,‘不認識‘),(5,‘你是誰‘);> exit> EOF

從庫同步情況

[[email protected] ~]# mysql -unana -p123456 -S /data/3307/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.5.32 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> select * from linzhongniao.test1;+----+-----------+| id | name  |+----+-----------+|  1 | 張三  ||  2 | 張三  ||  3 | 我是誰||  4 | 不認識||  5 | 你是誰|+----+-----------+5 rows in set (0.00 sec)

MySQL從庫資料衝突導致同步停止

Show slave status報錯
且show slave status\G

Slave_IO_Running: YesSlave_SQL_Running: NOSeconds_Behind_Master: NULLLast_Error:Error ‘Can’t create database ‘linzhongniao’;database exists’on query.Default database:’linzhongniao’.Query：‘create database linzhongniao’

解決方案：

方法一：

Stop slaveSet global sql_slave_skip_counter=1Start slave

（1）對於普通的互連網業務，忽略問題不是很大，當然要確認不影響公司業務的前提下。

（2）企業情境解決主從同步，比主從不一致對當前業務更重要。如果主從資料一致很重要找個時間恢複一下這個從庫。

方法二：根據錯誤號碼跳過指定的錯誤

[[email protected] ~]# grep skip /data/3306/my.cnfskip-name-resolve 忽略名稱解析解決mysql串連慢的問題。slave-skip-errors = 1032,1062 一般由於入庫重複導致的失敗就可以忽略。

當然也可以將slave-skip-errors的值設定為all,這樣就會導致主從庫資料同步資料缺失。我們可以參考mysql常見錯誤碼來設定我們想要忽略的錯誤號碼，還可以通過錯誤號碼解決mysql出現故障。2.mysql常見錯誤碼

1005：建立表失敗

1006：建立資料庫失敗

1007：資料庫已存在，建立資料庫失敗<=================可以忽略

1008：資料庫不存在，刪除資料庫失敗<=================可以忽略

1009：不能刪除資料庫檔案導致刪除資料庫失敗

1010：不能刪除資料目錄導致刪除資料庫失敗

1011：刪除資料庫檔案失敗

1012：不能讀取系統資料表中的記錄

1020：記錄已被其他使用者修改

1021：硬碟剩餘空間不足，請加大硬碟可用空間

1022：關鍵字重複，更改記錄失敗

1023：關閉時發生錯誤

1024：讀檔案錯誤

1025：更改名字時發生錯誤

1026：寫檔案錯誤

1032：記錄不存在<=============================可以忽略

1036：資料表是唯讀，不能對它進行修改

1037：系統記憶體不足，請重啟資料庫或重啟伺服器

1038：用於排序的記憶體不足，請增大排序緩衝區

1040：已到達資料庫的最大串連數，請加大資料庫可用串連數

1041：系統記憶體不足

1042：無效的主機名稱

1043：無效串連

1044：目前使用者沒有訪問資料庫的許可權

1045：不能串連資料庫，使用者名稱或密碼錯誤

1048：欄位不可為空

1049：資料庫不存在

1050：資料表已存在

1051：資料表不存在

1054：欄位不存在

1062：欄位值重複，入庫失敗<==========================可以忽略

1065：無效的SQL語句，SQL語句為空白

1081：不能建立Socket串連

1114：資料表已滿，不能容納任何記錄

1116：開啟的資料表太多

1129：資料庫出現異常，請重啟資料庫

1130：串連資料庫失敗，沒有串連資料庫的許可權

1133：資料庫使用者不存在

1141：目前使用者無權訪問資料庫

1142：目前使用者無權訪問資料表

1143：目前使用者無權訪問資料表中的欄位

1146：資料表不存在

1147：未定義使用者對資料表的存取權限

1149：SQL語句語法錯誤

1158：網路錯誤，出現讀錯誤，請檢查網路連接狀況

1159：網路錯誤，讀逾時，請檢查網路連接狀況

1160：網路錯誤，出現寫錯誤，請檢查網路連接狀況

1161：網路錯誤，寫逾時，請檢查網路連接狀況

1169：欄位值重複，更新記錄失敗

1177：開啟資料表失敗

1180：提交事務失敗

1181：復原事務失敗

1203：目前使用者和資料庫建立的串連已到達資料庫的最大串連數，請增大可用的資料庫連接數或重啟資料庫

1205：加鎖逾時

1211：目前使用者沒有建立使用者的許可權

1216：外鍵約束檢查失敗，更新子表記錄失敗

1217：外鍵約束檢查失敗，刪除或修改主表記錄失敗

1226：目前使用者使用的資源已超過所允許的資源，請重啟資料庫或重啟伺服器

1227：許可權不足，您無權進行此操作

1235：MySQL版本過低，不具有本功能

Mysql營運管理-生產情境Mysql主從複製讀寫分離授權方案及實戰15