MySQL マスタースレーブレプリケーション 読み書き分離の設定方法の詳細説明

egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf

[mysqld]
データディレクトリ=/data1/mysqldb
ソケット=/tmp/mysql.sock
キーバッファサイズ = 16M
最大許容パケット = 16M
スレッドスタック = 192K
スレッドキャッシュサイズ = 8
クエリキャッシュ制限 = 1M
クエリキャッシュサイズ = 64M
クエリキャッシュタイプ = 1
シンボリックリンク=0
innodb_file_per_table=オン
スキップ名解決=オン

サーバーID = 1
log_bin = /data1/mysqldb/mysql-bin.log
[mysqld_safe]
ログエラー=/usr/local/mysql/logs/error.log
pidファイル=/data1/mysqldb/mysql.pid
!includedir /usr/local/mysql/etc/my.cnf.d

mysql > 'replpass' で識別される 'repluser'@'172.16.3.%' に、*.* 上のレプリケーション クライアント、レプリケーション スレーブを許可します。
mysql > 権限をフラッシュします。
mysql >マスターログを表示;
+------------------+-----------+
| ログ名 | ファイルサイズ |
+------------------+-----------+
|mysql-bin.000001 | 622 |

egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf

[mysqld]
データディレクトリ=/data1/mysqldb
ソケット=/data1/mysqldb/mysql.sock
キーバッファサイズ = 16M
最大許容パケット = 16M
スレッドスタック = 192K
スレッドキャッシュサイズ = 8
クエリキャッシュ制限 = 1M
クエリキャッシュサイズ = 64M
クエリキャッシュタイプ = 1
シンボリックリンク=0

innodb_file_per_table=オン
スキップ名解決=オン

server-id = 11 #スレーブノードID。各スレーブノードには異なるrelay_log = relay-logがあります
読み取り専用=オン

[mysqld_safe]
ログエラー=/usr/local/mysql/log/error.log
pidファイル=/var/run/mysql/mysql.pid
!includedir /usr/local/mysql/etc/my.cnf.d

mysql > マスターを MASTER_HOST="172.16.3.175",MASTER_USER="repluser",MASTER_PASSWORD="replpass",MASTER_PORT=3306,MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=622 に変更します。
mysql > start slave; #スレーブノードを起動します ()

#スレーブノードのステータスを表示しますmysql > SHOW SLAVE STATUS;
************************** 1. 行 ****************************
    Slave_IO_State: マスターがイベントを送信するのを待機中
     Master_Host: 172.16.3.175 #マスターノードMaster_User: repluser #同期アカウントMaster_Port: 3306
    接続再試行: 60
    マスターログファイル:mysql-bin.000001
   読み取りマスターログ位置: 622
    リレーログファイル: リレーログ.000001
    リレーログ位置: 582
  リレーマスターログファイル: mysql-bin.000001
    Slave_IO_Running: はい #同期スレッドは正常です Slave_SQL_Running: はい #ローカル書き込みスレッドは正常です Replicate_Do_DB: #同期フィルターは空です (1 つまたは複数のライブラリのみを同期できます)
   Replicate_Ignore_DB: #同期されていないデータベース Replicate_Do_Table:      
  無視テーブルを複製: 
  Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
     Last_Errno: 0 #最後の同期エラー 0 は正常な同期を示します Last_Error: 
     スキップカウンタ: 0
   実行マスターログポジション: 622
    リレーログスペース: 615
    Until_Condition: なし
    ログファイルまで: 
    ログ位置まで: 0
   マスターSSL許可: いいえ
   マスターSSLCAファイル: 
   マスターSSLCAパス: 
    マスターSSL証明書: 
   マスターSSL暗号: 
    マスターSSLキー: 
  マスターより遅れている秒数: 0
Master_SSL_Verify_Server_Cert: いいえ
    最終IOエラー番号: 0
    最後のIOエラー: 
    最終SQLエラー番号: 0
    最後のSQLエラー: 
 Replicate_Ignore_Server_Ids: 
    マスターサーバーID: 1
     マスター_UUID: 57017c43-36e3-11e8-ac76-080027393fc7
    マスター情報ファイル: /data1/mysqldb/master.info
     SQL_遅延: 0
   SQL_残り遅延: NULL
  Slave_SQL_Running_State: スレーブはすべてのリレーログを読み取りました。さらに更新を待機しています。
   マスター再試行回数: 86400
     マスターバインド: 
  最終IOエラータイムスタンプ: 
  最終SQLエラータイムスタンプ: 
    マスターSSL証明書: 
   マスターSSLCrlパス: 
   取得済み_Gtid_Set: 
   実行されたGtidセット: 
    自動位置: 0
   Replicate_Rewrite_DB: 
     チャンネル名: 
   マスター TLS バージョン: 
セット内の 1 行 (0.00 秒)
エラー： 
クエリが指定されていません

[root@db1 ~]# mysql < Testdb.sql
データベースにログインします [root@db1 ~]# mysql -uroot -p
mysql> データベースを表示します。
+--------------------+
| データベース |
+--------------------+
 勉強 |
+--------------------+
セット内の行数は 5 です (0.00 秒)
### study テストデータベースが正常にインポートされましたmysql> use study;
データベースが変更されました
mysql> テーブルを表示します。
+-----------------+
| 研究中の表 |
+-----------------+
| クラス |
| コース |
| パート |
| スコア |
| 学生 |
|tb31|
|tb32|
| 先生 |
| テスト1 |
| テスト2 |
| ユーザー情報 |
+-----------------+
セット内の行数は 11 です (0.00 秒)
#test1 test2テーブルを削除する

mysql> テーブルを表示します。
+-----------------+
| 研究中の表 |
+-----------------+
| クラス |
| コース |
| パート |
| スコア |
| 学生 |
|tb31|
|tb32|
| 先生 |
| ユーザー情報 |
+-----------------+
セット内の行数は 9 です (0.00 秒)

[root@proxysql ~]# yum install proxysql-1.3.6-1-centos7.x86_64.rpm -y
[root@proxysql ~]# rpm -ql proxysql
プロキシSQL
/etc/proxysql.cnf #メイン設定ファイル /usr/bin/proxysql
プロキシSQLツール
プロキシSQLツール

[root@proxysql ~]# egrep -v '(^$|^#)' /etc/proxysql.cnf

データディレクトリ="/var/lib/proxysql"
管理者変数=
{ 
 admin_credentials="admin:admin" #proxysql 独自の管理者ユーザー名とパスワード mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
 threads=4 #スレッド数。CPUコア数と一致させることを推奨 max_connections=2048 #最大接続 default_query_delay=0
 デフォルトクエリタイムアウト=36000000
 圧縮あり=true
 ポーリングタイムアウト=2000
 インターフェース="0.0.0.0:3306;/tmp/proxysql.sock" #外部インターフェース default_schema="information_schema"
 スタックサイズ=1048576
 サーバーバージョン="5.5.30"
 接続タイムアウトサーバー=3000
 monitor_username="モニター"
 monitor_password="モニター"
 モニター履歴=600000
 モニター接続間隔=60000
 モニターのping間隔=10000
 モニター読み取り専用間隔=1500
 モニター読み取り専用タイムアウト=500
 ping_interval_server_msec=120000
 ping_timeout_server=500
 コマンド統計=true
 セッションソート=true
 失敗時の接続再試行=10
}

#####マスターノードとスレーブノードの構成mysql_servers =      
（
 {
  address = "172.16.3.175" # デフォルトなし、必須。ポートが 0 の場合、アドレスは Unix ソケットドメインとして解釈されます
  port = 3306 # デフォルトなし、必須。ポートが0の場合、アドレスはUnixソケットドメインとして解釈されます
  hostgroup = 1 # グループ番号を設定します status = "ONLINE" # デフォルト: ONLINE
  重み = 1 # デフォルト: 1
  圧縮 = 0 # デフォルト: 0
  max_connections = 200 ###最大接続数を定義する},
 {
  address = "172.16.3.235" # デフォルトなし、必須。ポートが 0 の場合、アドレスは Unix ソケットドメインとして解釈されます
  port = 3306 # デフォルトなし、必須。ポートが0の場合、アドレスはUnixソケットドメインとして解釈されます
  hostgroup = 2 # デフォルトなし、必須
  status = "ONLINE" # デフォルト: ONLINE
  重み = 1 # デフォルト: 1
  圧縮 = 0 # デフォルト: 0
  最大接続数=1000
 },
 {
  address = "172.16.3.241" # デフォルトなし、必須。ポートが 0 の場合、アドレスは Unix ソケットドメインとして解釈されます
  port = 3306 # デフォルトなし、必須。ポートが0の場合、アドレスはUnixソケットドメインとして解釈されます
  hostgroup = 2 # デフォルトなし、必須
  status = "ONLINE" # デフォルト: ONLINE
  重み = 1 # デフォルト: 1
  圧縮 = 0 # デフォルト: 0
  最大接続数=1000
 }
）
mysql_users:
（
 {
  username = "myadmin" # デフォルトなし、必須
  password = "mypass" # デフォルト: ''
  default_hostgroup = 1 # デフォルト: 0
  最大接続数=1000
  default_schema="テスト"
  active = 1 #アクティブ化するかどうか}
）
mysql_クエリルール:
（
）
スケジューラ=
（
）
mysql_replication_hostgroups=
（
  {
    writer_hostgroup=1 #書き込みグループ番号1を定義
    reader_hostgroup=2 #読み取りグループ番号2を定義
    comment="テスト repl 1" #コメント内容}
）

proxysql を介してデータベースの使用をシミュレートします [root@proxysql ]# mysql -h172.16.3.175 -umyadmin -pmypass
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
MySQL モニターへようこそ。コマンドは ; または \g で終わります。
MySQL接続IDは17406です
サーバーバージョン: 5.7.21-log MySQL コミュニティサーバー (GPL)
Copyright (c) 2000, 2018, Oracle およびその関連会社。無断複写・転載を禁じます。
OracleはOracle Corporationおよびその関連会社の登録商標です。
その他の名称は各社の商標である場合があります。
所有者。
ヘルプを表示するには、「help;」または「\h」と入力します。現在の入力ステートメントをクリアするには、「\c」と入力します。
マイSQL> 

mysql > データベースを表示;
mysql> データベースを表示します。
+--------------------+
| データベース |
+--------------------+
| 情報スキーマ |
|mysql |
| パフォーマンススキーマ |
| 勉強 |
|システム|
+--------------------+
セット内の行数は 5 です (0.00 秒)
### 研究データベース user_info の 6 から 12 の間のデータを削除します。削除前:
mysql> user_info から * を選択します。
+-----+-------+-------+--------+----------+
| nid | 名前 | 年齢 | 性別 | part_nid |
+-----+-------+-------+--------+----------+
| 1 | さん | 20 | 男性 | 1 |
| 2 | ドン | 29 | 男性 | 2 |
| 4 | リン | 28 | 男性 | 4 |
| 5 | 歳 | 28 | 男性 | 3 |
| 6 | ドン | 30 | 男性 | 1 |
| 7 | b | 11 | 女性 | 1 |
| 8 | c | 12 | 女性 | 1 |
| 9 | d | 18 | 女性 | 4 |
| 10 | e | 22 | 男性 | 3 |
| 11 | 女性 | 23 | 男性 | 2 |
| 12 | ドンギー | 22 | 男性 | 1 |
+-----+-------+-------+--------+----------+
セット内の行数は 11 です (0.00 秒)
削除後:
mysql> nid >6 かつ nid <12 の場合、user_info から削除します。
クエリは正常、5 行が影響を受けました (0.03 秒)

mysql> user_info から * を選択します。
+-----+-------+-------+--------+----------+
| nid | 名前 | 年齢 | 性別 | part_nid |
+-----+-------+-------+--------+----------+
| 1 | さん | 20 | 男性 | 1 |
| 2 | ドン | 29 | 男性 | 2 |
| 4 | リン | 28 | 男性 | 4 |
| 5 | 歳 | 28 | 男性 | 3 |
| 6 | ドン | 30 | 男性 | 1 |
| 12 | ドンギー | 22 | 男性 | 1 |
+-----+-------+-------+--------+----------+
セット内の 6 行 (0.00 秒)

マスターノード:
次のようなもの:
[root@db1 ~]# tcpdump -i enp0s3 -nn tcp ポート 3306
tcpdump: 詳細な出力は抑制されています。完全なプロトコル デコードには -v または -vv を使用してください。
enp0s3 でリッスン、リンク タイプ EN10MB (イーサネット)、キャプチャ サイズ 262144 バイト
18:04:34.678861 IP 172.16.3.254.42191 > 172.16.3.175.3306: フラグ [S]、シーケンス 3385407732、win 29200、オプション [mss 1460、sackOK、TS 値 17576713 ecr 0、nop、wscale 7]、長さ 0
18:04:34.678908 IP 172.16.3.175.3306 > 172.16.3.254.42191: フラグ [S.]、シーケンス 1579426335、ack 3385407733、win 28960、オプション [mss 1460、sackOK、TS 値 29413673 ecr 17576713、nop、wscale 7]、長さ 0
18:04:34.680902 IP 172.16.3.254.42191 > 172.16.3.175.3306: フラグ [.]、ack 1、win 229、オプション [nop、nop、TS val 17576715 ecr 29413673]、長さ 0
18:04:34.681264 IP 172.16.3.175.3306 > 172.16.3.254.42191: フラグ [P.]、シーケンス 1:83、ack 1、win 227、オプション [nop、nop、TS 値 29413675 ​​ecr 17576715]、長さ 82
....
ノードから:
次のようなもの:
[root@db2 data1]# tcpdump -i enp0s3 -nn tcp ポート 3306
tcpdump: 詳細な出力は抑制されています。完全なプロトコル デコードには -v または -vv を使用してください。
enp0s3 でリッスン、リンク タイプ EN10MB (イーサネット)、キャプチャ サイズ 262144 バイト
18:02:57.932043 IP 172.16.3.254.42733 > 172.16.3.235.3306: フラグ [S]、シーケンス 76520456、win 29200、オプション [mss 1460、sackOK、TS 値 17479189 ecr 0、nop、wscale 7]、長さ 0
...........

 [root@proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
管理者> データベースを表示;
+-----+---------+-------------------------------+
| シーケンス | 名前 | ファイル |
+-----+---------+-------------------------------+
| 0 | メイン | |
| 2 | ディスク | /var/lib/proxysql/proxysql.db |
| 3 | 統計 | |
| 4 | モニター | |
+-----+---------+-------------------------------+
セット内の 4 行 (0.00 秒)