初めてのMySQLパフォーマンスチューニングーデータベースは怖くない!/mysql-performance-tuning-basics-in-db-study-chugoku-chiho

3c9c5f9a91cac73073db8bb2903bd968?s=47 mamy1326
February 02, 2019

 初めてのMySQLパフォーマンスチューニングーデータベースは怖くない!/mysql-performance-tuning-basics-in-db-study-chugoku-chiho

中国地方DB勉強会@岡山でお話する資料です。
MySQLのトラブル解析から my.cnf の設定方法、パラメータ解説、レプリケーションの実行手順、チューニング結果、そして伝えたい大事なことをお話します。

3c9c5f9a91cac73073db8bb2903bd968?s=128

mamy1326

February 02, 2019
Tweet

Transcript

  1. 3.

    ✔ MySQLに 興味がある 人    →これから使いたい、知っておきたい ✔ Web アプリケーション エンジニア    →DBAではない、クエリは主にORM

    ✔ RDB操作は 主に ツール を使う    →DBeaver、MySQL Workbench など 想定オーディエンスのみなさま 3
  2. 29.

    トラブルは突然に 29 ・ 秒間 10 アクセスでダウン   →Web -> MySQLサーバーへのアクセス ・

    スロークエリ頻発   →5分以上、1000万レコードフルスキャン ・ スレーブがない   →ダウンするとサービス停止 状況:ノンチューニング
  3. 47.

    起:状況把握、診断 診断:MySQL Tuner を使う ・ チューニングポイントの 診断   →my.cnfに何が不足かを抽出 ・ GNU

    GPL なので無償利用可能   →Perlのアプリケーション ・ インストールして すぐ使える   →設定不要!(8.0系には対応中の模様) 47
  4. 51.

    起:状況把握、診断 MySQL Tuner 利用方法 $ cd ~/ $ git clone

    git@github.com:major/ MySQLTuner-perl.git $ cd MySQLTuner-perl $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root -- pass='mamy1326' 51
  5. 52.

    起:状況把握、診断 MySQL Tuner 診断結果 (5.5) -------- Recommendations ----------------------------- General recommendations:

    Enable the slow query log to troubleshoot badqueries Set thread_cache_size to 4 as a starting value Variables to adjust: query_cache_size (>= 8M) thread_cache_size (start at 4) innodb_file_per_table=ON innodb_buffer_pool_size (>= 12G) if possible. innodb_log_file_size should be equals to 1/4 of buffer pool size (=128M) if possible. 52
  6. 53.

    起:状況把握、診断(参考) MySQL Tuner 診断結果 (5.7.24) -------- Recommendations --------------------------------------------------------------------------- General recommendations:

    Control warning line(s) into /var/log/mysql/error.log file Control error line(s) into /var/log/mysql/error.log file MySQL was started within the last 24 hours - recommendations may be inaccurate Configure your accounts with ip or subnets only, then update your configuration with skip-name- resolve=1 Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/ 2TcGgtU Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_limit (> 1M, or use smaller result sets) innodb_buffer_pool_size (>= 3.2G) if possible. innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. 53
  7. 54.

    起:状況把握、診断(参考) MySQL Tuner 診断結果 (8.0.13) -------- Recommendations ----------------------------- General recommendations:

    Control warning line(s) into /var/log/mysqld.log file Control error line(s) into /var/log/mysqld.log file MySQL was started within the last 24 hours - recommendations may be inaccurate Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: innodb_buffer_pool_size (>= 3.4G) if possible. innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. 54
  8. 58.

    起:状況把握、診断 MySQL Tuner 診断結果 (5.5) -------- Recommendations ----------------------------- General recommendations:

    Enable the slow query log to troubleshoot badqueries Set thread_cache_size to 4 as a starting value Variables to adjust: query_cache_size (>= 8M) thread_cache_size (start at 4) innodb_file_per_table=ON innodb_buffer_pool_size (>= 12G) if possible. innodb_log_file_size should be equals to 1/4 of buffer pool size (=128M) if possible. 58
  9. 59.

    起:状況把握、診断 MySQL Tuner 診断結果 ① Enable the slow query log

    to troubleshoot bad queries ✔ スロークエリ出力設定を有効にして… ✔ クエリのトラブルシュートをしよう 59
  10. 60.

    起:状況把握、診断 MySQL Tuner 診断結果 ② query_cache_size (>= 8M) ✔ クエリキャッシュを設定しよう

    ✔ ただし MySQL 8.0 から無くなる機能 ✔ 今から別の機能を検討しましょう  (いずれ8.0系にする日がきます) 60
  11. 62.

    クエリキャッシュ廃止の理由 1/2 62 ・ クエリキャッシュの限界    →マルチコアマシンでの高スループットな負荷の場合      スケールしない ( MySQL

    5.6 から無効化 (2013年) ) ・ クライアントでキャッシュすべき    →DBサーバでクエリキャッシュ             = 性能向上はヒットするクエリだけ    →全般的な性能向上             = アプリケーションで適切にキャッシュ
  12. 66.

    MySQL Tuner 診断結果 ④ innodb_buffer_pool_size (>= 12G) if possible. ✔

    データとindexを キャッシュ に載せよう ✔ 物理メモリの 7〜8割 が基準 ✔ 大きいメモリの場合は基準が変わる 66 起:状況把握、診断
  13. 67.

    MySQL Tuner 診断結果 ⑤ innodb_log_file_size should be equals to 1/4

    of buffer pool ✔ InnoDBログファイルのサイズ ✔ バッファプールの 4分の1 に設定しよう 67 起:状況把握、診断
  14. 71.

    71

  15. 73.

    Appendix:Charaset, Collation mysql> show variables like "chara%"; +--------------------------+----------------------------+ | Variable_name

    | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 73 悲しいね(懐メロ)
  16. 74.

    Appendix:Charaset, Collation [mysqld] # mysqld ログ出力設定 log-error = /var/log/mysql/error.log #

    新規作成するデータベースのCharaset character-set-server = utf8mb4 # 新規作成するデータベースのCharaset collation_server = utf8mb4_bin [client] # クライアント内の文字処理とサーバーとの接続のCharaset loose-default-character-set = utf8mb4 74
  17. 75.

    Appendix:Charaset, Collation [mysqld] # mysqld ログ出力設定 log-error = /var/log/mysql/error.log #

    新規作成するデータベースのCharaset character-set-server = utf8mb4 # 新規作成するデータベースのCharaset collation_server = utf8mb4_bin [client] # クライアント内の文字処理とサーバーとの接続のCharaset loose-default-character-set = utf8mb4 75 (ついで)エラーログは 明確にしましょう
  18. 76.

    Appendix:Charaset, Collation [mysqld] # mysqld ログ出力設定 log-error = /var/log/mysql/error.log #

    新規作成するデータベースのCharaset character-set-server = utf8mb4 # 新規作成するデータベースのCharaset collation_server = utf8mb4_bin [client] # クライアント内の文字処理とサーバーとの接続のCharaset loose-default-character-set = utf8mb4 76 ここで設定していれば DB、テーブル、カラムで 指定する必要がない
  19. 77.

    Appendix:Charaset, Collation [mysqld] # mysqld ログ出力設定 log-error = /var/log/mysql/error.log #

    新規作成するデータベースのCharaset character-set-server = utf8mb4 # 新規作成するデータベースのCharaset collation_server = utf8mb4_bin [client] # クライアント内の文字処理とサーバーとの接続のCharaset loose-default-character-set = utf8mb4 77 Charaset同様 Collationを設定しておく
  20. 78.

    Appendix:Charaset, Collation [mysqld] # mysqld ログ出力設定 log-error = /var/log/mysql/error.log #

    新規作成するデータベースのCharaset character-set-server = utf8mb4 # 新規作成するデータベースのCharaset collation_server = utf8mb4_bin [client] # クライアント内の文字処理とサーバーとの接続のCharaset loose-default-character-set = utf8mb4 78 ただし CHARACTER SETすると 無視されるので注意
  21. 79.

    Appendix:Charaset, Collation [mysqld] # mysqld ログ出力設定 log-error = /var/log/mysql/error.log #

    新規作成するデータベースのCharaset character-set-server = utf8mb4 # 新規作成するデータベースのCharaset collation_server = utf8mb4_bin [client] # クライアント内の文字処理とサーバーとの接続のCharaset loose-default-character-set = utf8mb4 79 ここで設定していれば 通信時にも 文字化けを防止できる
  22. 80.

    Appendix:Charaset, Collation mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name

    | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 80 嬉しい楽s(以下略
  23. 85.

    承:my.cnf 設定と解説 共有領域とは ・ 実データ を 1ファイル で扱う   →/var/lib/mysql/ibdata1 ・

    ファイルサイズが増え続ける   →削除領域は減らず、再利用される 85
  24. 91.

    承:状況把握、仮説、診断 ・ ファイルディスクリプタ使用増加   →HDD -> SSD で性能向上   →問題あれば他の技術を検討 ・ 大きな削除はロックの可能性

      →DROP TABLE, TRUNCATEに限る   →計画的に削除すべき ・ 5.6.6 からデフォルトである テーブル個別領域のデメリット 91
  25. 96.

    承:my.cnf 設定と解説 SELECT の動き ・ バッファプール を見る   →メモリから取ろうとする ・ なければ

    実データ を読む   →ディスクI/Oが発生する ・ その後バッファプールに載せる   →データから吸い上げてくれる 96
  26. 97.

    承:my.cnf 設定と解説 INSERT, UPDATE, DELETE の動き ・ バッファプール に書く   →空きがなければ、古いページを押し出す

    ・ その後、ログファイル に書く   →commit反映待ちのログへ ・ 実データファイルへ書く   →実データ+ログで完全なデータ 97
  27. 98.

    承:my.cnf 設定と解説 DROP TABLE の動き ・ バッファプール から削除   →テーブル全てのページ ・

    その後、ログファイル に書く   →commit反映待ちのログへ ・ 実データファイルを削除   →順番は更新系と同じ 98
  28. 101.

    バッファプール ・設定内容 設定のポイント ・ メモリ 8G の 75% = 6G

    ・ mysql再起動 で反映される ・ 割り当て状況を監視して運用を [mysqld] innodb_buffer_pool_size=6G 101
  29. 106.

    承:my.cnf 設定と解説 大まかな commit時 の動き バッファ プールへ 書き込み InnoDB ログへ

    書き込み 実ファイル へ 書き込み 106 プロセスの処理はここまで 非同期書き込み
  30. 113.

    my.cnf 設定・反映手順 ① DBへのアクセスを停止 ② dump取得 ③ 更新後の my.cnf に差し替え

    ④ MySQL 再起動 ⑤ バックアップからリストア ⑥ 動作検証・経過観察 113
  31. 115.

    115

  32. 117.

    Appendix:slow query 関連 [mysqld] # slow log 出力設定 slow_query_log =

    ON # slow.log の場所 slow_query_log_file = /var/log/mysql/slow.log # 出力対象秒数 long_query_time = 3 # no index のクエリも出すかどうか log_queries_not_using_indexes = 1 # no index をログに出力する間隔(分) log_throttle_queries_not_using_indexes = 1 # スロークエリで、SELECT行数が指定数以下のクエリもログに出力 min_examined_row_limit = 0 117
  33. 118.

    Appendix:slow query 関連 [mysqld] # slow log 出力設定 slow_query_log =

    ON # slow.log の場所 slow_query_log_file = /var/log/mysql/slow.log # 出力対象秒数 long_query_time = 3 # no index のクエリも出すかどうか log_queries_not_using_indexes = 1 # no index をログに出力する間隔(分) log_throttle_queries_not_using_indexes = 1 # スロークエリで、SELECT行数が指定数以下のクエリもログに出力 min_examined_row_limit = 0 118 普通のスロークエリ設定
  34. 119.

    Appendix:slow query 関連 [mysqld] # slow log 出力設定 slow_query_log =

    ON # slow.log の場所 slow_query_log_file = /var/log/mysql/slow.log # 出力対象秒数 long_query_time = 3 # no index のクエリも出すかどうか log_queries_not_using_indexes = 1 # no index をログに出力する間隔(分) log_throttle_queries_not_using_indexes = 1 # スロークエリで、SELECT行数が指定数以下のクエリもログに出力 min_examined_row_limit = 0 119 no index のクエリを ログに出す
  35. 120.

    Appendix:slow query 関連 [mysqld] # slow log 出力設定 slow_query_log =

    ON # slow.log の場所 slow_query_log_file = /var/log/mysql/slow.log # 出力対象秒数 long_query_time = 3 # no index のクエリも出すかどうか log_queries_not_using_indexes = 1 # no index をログに出力する間隔(分) log_throttle_queries_not_using_indexes = 1 # スロークエリで、SELECT行数が指定数以下のクエリもログに出力 min_examined_row_limit = 0 120 no index のクエリを ログに出す間隔(分)
  36. 121.

    Appendix:slow query 関連 [mysqld] # slow log 出力設定 slow_query_log =

    ON # slow.log の場所 slow_query_log_file = /var/log/mysql/slow.log # 出力対象秒数 long_query_time = 3 # no index のクエリも出すかどうか log_queries_not_using_indexes = 1 # no index をログに出力する間隔(分) log_throttle_queries_not_using_indexes = 1 # スロークエリで、SELECT行数が指定数以下のクエリもログに出力 min_examined_row_limit = 0 121 SELECT の 行数指定
  37. 126.

    転:レプリケーションで冗長化 マスターの役割 ・ データを 更新 する側   →スレーブは参照する側 ・ 更新内容を スレーブが取り

    に来る   →スレーブへ更新イベントを通知 ・ 複数のスレーブ を持てる   →単一でも複数でもマスター設定変更不要 126
  38. 127.

    転:レプリケーションで冗長化 スレーブの役割 ・ データを 参照 する側   →スレーブは参照する側 ・ マスターのバイナリログを 取得

      →イベント受取 -> マスターbinlog取得 -> 更新 ・ マスターに 昇格 できる   →マスターと同じ設定が必要 127
  39. 129.

    可用性 の高い構成の実現 129 レプリケーションのメリット➁ ✔ スレーブでバイナリログを出力   →my.cnf に設定する マスターが 障害

    でダウン   →スレーブがないと解決までサービス停止 スレーブ -> マスターへ 昇格 できる
  40. 134.

    レプリケーションユーザー作成 ・ マスター側に 作成   →スレーブから参照できるユーザー ・ マスターのバイナリログ 取得用   →スレーブから接続して取得 ・

    レプリケーション 専用ユーザー   →適切な権限設定、マスター更新しないように 134 レプリケーション事前準備
  41. 135.

    ユーザー作成コマンド 135 レプリケーション事前準備 mysql(master)> CREATE USER mysql(master)> ’repl’@’[スレーブのホスト名]’ mysql(master)> IDENTIFIED

    BY PASSWORD ’***’; ・ 適切なユーザー名: ‘repl’ など ・ 適切なパスワード ・ マスターで実行
  42. 136.

    ユーザー権限付与コマンド 136 レプリケーション事前準備 mysql(master)> GRANT REPLICATION SLAVE mysql(master)> ON *.*

    TO mysql(master)> ’repl’@’[スレーブのホスト名]’; ・ 適切な権限: ‘REPLICATION SLAVE’ ・ 必要であればDB名なども適切に ・ 先ほど作ったユーザーに対し実行
  43. 138.

    マスターの my.cnf に設定追加 138 レプリケーション事前準備 [mysqld] # スレーブに通知するバイナリログ(実際はナンバリングされる) log-bin=mysql-bin #

    レプリケーションを行う、サーバーのユニークID server-id=1001 # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3
  44. 139.

    マスターの my.cnf に設定追加 139 レプリケーション事前準備 [mysqld] # スレーブに通知するバイナリログ(実際はナンバリングされる) log-bin=mysql-bin #

    レプリケーションを行う、サーバーのユニークID server-id=1001 # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3 commitを通知後 スレーブが接続して取得 (/var/lib/mysql/mysql-bin.XXXXXX)
  45. 140.

    マスターの my.cnf に設定追加 140 レプリケーション事前準備 [mysqld] # スレーブに通知するバイナリログ(実際はナンバリングされる) log-bin=mysql-bin #

    レプリケーションを行う、サーバーのユニークID server-id=1001 # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3 スレーブから見た マスターのID
  46. 141.

    マスターの my.cnf に設定追加 141 レプリケーション事前準備 [mysqld] # スレーブに通知するバイナリログ(実際はナンバリングされる) log-bin=mysql-bin #

    レプリケーションを行う、サーバーのユニークID server-id=1001 # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3 多くしすぎると ストレージ圧迫注意
  47. 143.

    マスターバイナリログ出力 ・ マスターの mysqld 再起動   →設定を反映し、バイナリログを出力 ・ commit 履歴 出力

      →履歴の差分をスレーブが取得する ・ ファイル名、位置情報を確認   →現在のファイル&どこまでcommitしたかの位置 143 レプリケーション事前準備
  48. 144.

    マスターのステータス確認 144 レプリケーション事前準備 mysql> SHOW MASTER STATUS\G ********* 1. row

    ********* File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec)
  49. 145.

    マスターのステータス確認 145 レプリケーション事前準備 mysql> SHOW MASTER STATUS\G ********* 1. row

    ********* File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) commitを通知後 スレーブが接続して取得 (/var/lib/mysql/mysql-bin.XXXXXX)
  50. 146.

    マスターのステータス確認 146 レプリケーション事前準備 mysql> SHOW MASTER STATUS\G ********* 1. row

    ********* File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) マスターで commit が 実行された位置情報
  51. 147.

    マスターのステータス確認 147 レプリケーション事前準備 mysql> SHOW MASTER STATUS\G ********* 1. row

    ********* File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) スレーブの設定で必要のため メモしておきましょう
  52. 150.

    スレーブの my.cnf に設定追加 150 レプリケーション事前準備 [mysqld] # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値) server-id=1002 #

    マスター昇格のため、バイナリログを出力 log-bin=mysql-bin # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3 # スレーブのバイナリログ出力設定(上記だけでは出力されない) log_slave_updates=1 # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない) read_only=1
  53. 151.

    [mysqld] # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値) server-id=1002 # マスター昇格のため、バイナリログを出力 log-bin=mysql-bin # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3

    # スレーブのバイナリログ出力設定(上記だけでは出力されない) log_slave_updates=1 # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない) read_only=1 スレーブの my.cnf に設定追加 151 レプリケーション事前準備 スレーブの サーバID
  54. 152.

    [mysqld] # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値) server-id=1002 # マスター昇格のため、バイナリログを出力 log-bin=mysql-bin # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3

    # スレーブのバイナリログ出力設定(上記だけでは出力されない) log_slave_updates=1 # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない) read_only=1 スレーブの my.cnf に設定追加 152 レプリケーション事前準備 スレーブでの バイナリログ出力設定
  55. 153.

    [mysqld] # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値) server-id=1002 # マスター昇格のため、バイナリログを出力 log-bin=mysql-bin # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3

    # スレーブのバイナリログ出力設定(上記だけでは出力されない) log_slave_updates=1 # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない) read_only=1 スレーブの my.cnf に設定追加 153 レプリケーション事前準備 スレーブでは これが必要(次ページで説明)
  56. 156.

    スレーブの my.cnf に設定追加 156 レプリケーション事前準備 [mysqld] # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値) server-id=1002 #

    マスター昇格のため、バイナリログを出力 log-bin=mysql-bin # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3 # スレーブのバイナリログ出力設定(上記だけでは出力されない) log_slave_updates=1 # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない) read_only=1 スレーブで更新を禁止 (SUPER権限は防げない)
  57. 157.

    スレーブの my.cnf に設定追加 157 レプリケーション事前準備 [mysqld] # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値) server-id=1002 #

    マスター昇格のため、バイナリログを出力 log-bin=mysql-bin # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3 # スレーブのバイナリログ出力設定(上記だけでは出力されない) log_slave_updates=1 # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない) read_only=1 MySQL 5.7.8 以降では super_read_only で解決
  58. 160.

    スレーブの確認 (ダメな例) 160 レプリケーション事前準備 mysql> SHOW SLAVE STATUS\G **************** 1.

    row **************** Slave_IO_State: Master_Host: [Ϛελʔͷϗετ໊] Master_User: repl Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 303456264 Slave_IO_Running: No Slave_SQL_Running: No Seconds_Behind_Master: NULL Master_Server_Id: 0 1 row in set (0.01 sec)
  59. 166.

    マスターのステータス確認【再掲】 166 レプリケーション事前準備 mysql> SHOW MASTER STATUS\G ********* 1. row

    ********* File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) 大きくズレている
  60. 169.

    スレーブのステータス 解説 ⑧ マスターに比べ遅延した秒数 ・NULL=レプリケーション 停止 ・0 = 遅延なし ・差分が多いと

    秒数が増える   →重いクエリを流す、過去のdumpでスレーブ作る Seconds_Behind_Master: NULL 169
  61. 170.

    スレーブのステータス 解説 ⑨ 接続対象のマスターのID ・マスターの my.cnf で 設定したID ・0 =

    接続 できていない   →この場合は何度もマスターが落ちていた結果 Master_Server_Id: 0 170
  62. 171.

    マスターの my.cnf に設定追加【再掲】 171 レプリケーション事前準備 [mysqld] # スレーブに通知するバイナリログ(実際はナンバリングされる) log-bin=mysql-bin #

    レプリケーションを行う、サーバーのユニークID server-id=1001 # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3 スレーブから見た マスターのID
  63. 174.

    174

  64. 176.

    Appendix:暗黙的な属性付与は非推奨 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please

    use —explicit_defaults_for_timestamp server option (see documentation for more details). 176 暗黙的な属性付与は非推奨 ▶いわゆるゼロタイムスタンプ  →'0000-00-00 00:00:00'  →暗黙的な属性付与で意図しない値になる
  65. 177.

    Appendix:暗黙的な属性付与は非推奨 mysql> SELECT cuntomer_name -> FROM customers -> WHERE create_at

    != '0000-00-00 00:00:00' -> AND create_at < NOW(); 177 見たことありませんか…(震え声 ▶やむなく対応の地獄  →やむなくプログラムでなんとかする  →やむなくWHERE 苦 句でなんとかする
  66. 178.

    [mysqld] # timestamp型の暗黙的なデフォルト値を使わない explicit_defaults_for_timestamp = ON 178 Appendix:暗黙的な属性付与は非推奨 ▶設定 ▶確認

    mysql> show variables like 'explicit_defaults_for_timestamp'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | ON | +---------------------------------+-------+ 1 row in set (0.00 sec)
  67. 180.

    レプリケーション手順 ① マスターdump取得 ② マスターバイナリログ名、ポジション取得 ③ スレーブ mysqld 再起動 ④

    スレーブ DROP & CREATE DATABASE ⑤ スレーブにdumpをリストア ⑥ レプリケーション設定・実行 ➆ 実行状況を監視 180
  68. 181.

    レプリケーション手順 ① マスターdump取得 ② マスターバイナリログ名、ポジション取得 ③ スレーブ mysqld 再起動 ④

    スレーブ DROP & CREATE DATABASE ⑤ スレーブにdumpをリストア ⑥ レプリケーション設定・実行 ➆ 実行状況を監視 181
  69. 182.

    マスターのステータス確認 182 レプリケーション設定・実行 mysql> SHOW MASTER STATUS\G ********* 1. row

    ********* File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) マスターの バイナリログファイル名と ポジション
  70. 183.

    スレーブ設定 183 レプリケーション設定・実行 mysql> STOP SLAVE; mysql> RESET SLAVE; mysql>

    CHANGE MASTER TO -> MASTER_HOST = '192.168.1.xxx', -> MASTER_USER = 'repl', -> MASTER_PASSWORD = 'xxxxx', -> MASTER_LOG_FILE = 'mysql-bin.000075', -> MASTER_LOG_POS = 588201136; mysql> START SLAVE;
  71. 184.

    スレーブ設定 184 レプリケーション設定・実行 mysql> STOP SLAVE; mysql> RESET SLAVE; mysql>

    CHANGE MASTER TO -> MASTER_HOST = '192.168.1.xxx', -> MASTER_USER = 'repl', -> MASTER_PASSWORD = 'xxxxx', -> MASTER_LOG_FILE = 'mysql-bin.000075', -> MASTER_LOG_POS = 588201136; mysql> START SLAVE; マスターへの接続
  72. 185.

    スレーブ設定 185 レプリケーション設定・実行 mysql> STOP SLAVE; mysql> RESET SLAVE; mysql>

    CHANGE MASTER TO -> MASTER_HOST = '192.168.1.xxx', -> MASTER_USER = 'repl', -> MASTER_PASSWORD = 'xxxxx', -> MASTER_LOG_FILE = 'mysql-bin.000075', -> MASTER_LOG_POS = 588201136; mysql> START SLAVE; スレーブに設定します
  73. 186.

    mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO

    -> MASTER_HOST = '192.168.1.xxx', -> MASTER_USER = 'repl', -> MASTER_PASSWORD = 'xxxxx', -> MASTER_LOG_FILE = 'mysql-bin.000075', -> MASTER_LOG_POS = 588201136; mysql> START SLAVE; レプリケーション開始 186 レプリケーション設定・実行 レプリケーションを 開始
  74. 187.

    mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO

    -> MASTER_HOST = '192.168.1.xxx', -> MASTER_USER = 'repl', -> MASTER_PASSWORD = 'xxxxx', -> MASTER_LOG_FILE = 'mysql-bin.000075', -> MASTER_LOG_POS = 588201136; mysql> START SLAVE; レプリケーション開始 187 レプリケーション設定・実行 レプリケーションを 開始 設定したファイル、ポジ ションから同期実行
  75. 188.

    レプリケーション実行確認 188 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G ***************** 1. row

    ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001
  76. 189.

    mysql> SHOW SLAVE STATUS\G ***************** 1. row ***************** Slave_IO_State: Waiting

    for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001 レプリケーション実行確認 189 レプリケーション設定・実行 マスターからの 更新イベント待ち = スレーブ稼働
  77. 190.

    レプリケーション実行確認 190 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G ***************** 1. row

    ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001 マスターの現在の バイナリログファイル名
  78. 191.

    レプリケーション実行確認 191 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G ***************** 1. row

    ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001 マスターのバイナリログ から、I/Oスレッドが 読み取った位置
  79. 192.

    レプリケーション実行確認 192 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G ***************** 1. row

    ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001 I/Oスレッド、SQLスレッド が動作している
  80. 193.

    レプリケーション実行確認 193 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G ***************** 1. row

    ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001 I/Oスレッドが読み取った SQLが実行された位置
  81. 194.

    レプリケーション実行確認 194 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G ***************** 1. row

    ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001 マスターに比べ 遅延している秒数
  82. 195.

    レプリケーション実行確認 195 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G ***************** 1. row

    ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001 差分が 遅延している トランザクション
  83. 196.

    レプリケーション実行確認 196 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G ***************** 1. row

    ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001 I/Oスレッド、SQLスレッド 共にエラーなし
  84. 197.

    レプリケーション実行確認 197 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G ***************** 1. row

    ***************** Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001 マスターの サーバーID
  85. 198.

    レプリケーション実行状況の監視 198 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G **************** 1. row

    ************** Read_Master_Log_Pos: 878873047 Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615
  86. 199.

    レプリケーション実行状況の監視 199 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G **************** 1. row

    ************** Read_Master_Log_Pos: 878873047 Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 mysql> SHOW SLAVE STATUS\G **************** 1. row ************** Read_Master_Log_Pos: 879066686 Exec_Master_Log_Pos: 879066686 Seconds_Behind_Master: 0
  87. 200.

    ① スレーブが稼働  ・複製を利用可能になった ② バックアップ環境完成  ・スレーブで mysqldump を実行できる ③ 更新、参照の分割が可能

     ・分析系の重いクエリをスレーブへ レプリケーションまとめ 200 転:レプリケーションで 冗長化
  88. 206.

    結:設定結果のパフォーマンス ✔ 秒間 1000 アクセス 達成 ✔ ストレージ 圧迫解消 ✔

    バッファプールとログで 高速化 my.cnf チューニング 総評 206
  89. 207.

    結:設定結果のパフォーマンス ✔ 秒間 1000 アクセス 達成 ✔ ストレージ 圧迫解消 ✔

    バッファプールとログで 高速化 ✔ レプリケーションで 冗長化 my.cnf チューニング 総評 207
  90. 208.

    結:設定結果のパフォーマンス ✔ 秒間 1000 アクセス 達成 ✔ ストレージ 圧迫解消 ✔

    バッファプールとログで 高速化 ✔ レプリケーションで 冗長化 ✔ サービスと事業が 軌道に my.cnf チューニング 総評 208
  91. 215.
  92. 220.

    220

  93. 221.

    221