Mysql運行維持管理-生産シーンMysql主従複製読み書き分離授権方案及び実戦15
8141 ワード
1.生産MySQLレプリケーション環境ユーザー認証方案
では、どのようにして上記の効果を達成することができますか?
(1)生産環境メインライブラリユーザーの授権
ヒント:特殊なビジネスは権限がやや多く、ビジネスのセキュリティが高くない場合はall privilegesも使用できます.
(2)生産環境のライブラリユーザーからの授権
(3)生産授権事例説明:ここでは192.168.10.%のユーザーblogはblogデータベースのすべてのテーブル(*はすべてのテーブルを表す)の読み取り専用権限(SELECT)を管理し、パスワードは123456です.
本番環境のマスター・スレーブ・ユーザーの権限
メインライブラリ:
ライブラリから:
上記のライセンススキームの実装方法
最も簡単な方法は、メインライブラリでbinlog-ignore-db=mysqlを構成することです.
2.授権表を無視することによるデータベースからのデータ書き込みを防止する方法及び実践
本番環境では、通常、ライセンステーブルを無視して同期し、サーバ(slave)上のユーザーにselect読み取り権限のみを付与し、mysqlライブラリを同期しません.これにより、プライマリ・ライブラリとセカンダリ・ライブラリの同じユーザーに異なる権限を付与できることを保証します.mysqlライブラリが同期していないことを指定します.mysqlと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でデータベースから再起動
(2)read-onlyパラメータはSUPER権限のユーザーに対して無効で、SUPERユーザーでログインして普通のユーザーを作成する
(3)schoolライブラリにテーブルを作成して通常のユーザーでログインし、作成したテーブルにレコードを挿入し、read-onlyの効果を示す.
(4)最後に、マスターライブラリを同期できるかどうか見てみましょう
メインライブラリにデータを挿入
ライブラリからの同期状況
MySQLライブラリデータ競合による同期停止
Show slave statusエラーとshow slave statusG
解決方法:
方法1:
(1)一般的なインターネット事業については,無視の問題は大きくないが,当然会社の業務に影響を及ぼさないことを確認する前提である.
(2)企業シーン解決主従同期は,主不一致よりも現在の業務に重要である.プライマリ・スレーブ・データが一致している場合は、このスレーブ・ライブラリをリカバリする時間を探すことが重要です.
メソッド2:指定したエラーをエラー番号に基づいてスキップする
もちろん、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のバージョンが低すぎて、この機能がありません
転載先:https://www.cnblogs.com/zywu-king/p/8563459.html
では、どのようにして上記の効果を達成することができますか?
(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=mysqlを構成することです.
2.授権表を無視することによるデータベースからのデータ書き込みを防止する方法及び実践
本番環境では、通常、ライセンステーブルを無視して同期し、サーバ(slave)上のユーザーにselect読み取り権限のみを付与し、mysqlライブラリを同期しません.これにより、プライマリ・ライブラリとセカンダリ・ライブラリの同じユーザーに異なる権限を付与できることを保証します.mysqlライブラリが同期していないことを指定します.mysqlとinformationを無視schemaライブラリのプライマリ・スレーブ同期.
replicate-ignore-db=mysql
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-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でデータベースから再起動
[root@mysql ~]# egrep "\[mysqld]|read-only" /data/3307/my.cnf
[mysqld]
read-only
[root@mysql ~]# /data/3307/mysql stop
Stoping MySQL....
[root@mysql ~]# /data/3307/mysql start
Starting 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の効果を示す.
[root@mysql ~]# mysql -unana -p123456 -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, 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.
mysql> select user();
+----------------+
| user() |
+----------------+
| nana@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> use school;
Database changed
mysql> 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)最後に、マスターライブラリを同期できるかどうか見てみましょう
メインライブラリにデータを挿入
[root@mysql ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock < use linzhongniao
> insert into test1 values(4,' '),(5,' ');
> exit
> EOF ライブラリからの同期状況
[root@mysql ~]# mysql -unana -p123456 -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, 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.
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 statusG
Slave_IO_Running: Yes
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL
Last_Error:Error ‘Can’t create database ‘linzhongniao’;database exists’on query.
Default database:’linzhongniao’.Query：‘create database linzhongniao’解決方法:
方法1:
Stop slave
Set global sql_slave_skip_counter=1
Start slave(1)一般的なインターネット事業については,無視の問題は大きくないが,当然会社の業務に影響を及ぼさないことを確認する前提である.
(2)企業シーン解決主従同期は,主不一致よりも現在の業務に重要である.プライマリ・スレーブ・データが一致している場合は、このスレーブ・ライブラリをリカバリする時間を探すことが重要です.
メソッド2:指定したエラーをエラー番号に基づいてスキップする
[root@mysql ~]# grep skip /data/3306/my.cnf
skip-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のバージョンが低すぎて、この機能がありません
転載先:https://www.cnblogs.com/zywu-king/p/8563459.html