1.1 ストレージエンジンの概要 1.1.1 ファイルシステムストレージ ファイル システム: オペレーティング システムがデータを整理してアクセスするために使用するメカニズム。ファイルシステムはソフトウェアの一種です。 タイプ: ext2 3 4、xfs データ。 どのファイル システムを使用しても、データの内容は変わりません。異なるのは、ストレージ領域、サイズ、および速度です。 1.1.2 MySQLデータベースストレージ MySQL エンジン: MySQL の「ファイル システム」として理解できますが、より強力な機能を備えています。 MySQL エンジン機能: 基本的なアクセス機能の提供に加えて、トランザクション機能、ロック、バックアップとリカバリ、最適化、特殊機能などの機能も備えています。 1.1.3 MySQL ストレージ エンジンの種類 MySQL は次のストレージ エンジンを提供します。 InnoDB、MyISAM(最もよく使用される2つ) メモリ、アーカイブ、フェデレーション、例 ブラックホール、マージ、NDBCLUSTER、CSV さらに、サードパーティのストレージエンジンも使用できます。 1.1.4 InnoDBとMyISAMの比較 InnoDb エンジン ACID トランザクションと 4 つのトランザクション分離レベルをサポートします。 行レベルのロックと外部キー制約をサポートしているため、同時書き込みをサポートできます。 行の合計数は保存されません。 InnoDb エンジンは、1 つのファイル スペース (共有テーブル スペース、テーブル サイズはオペレーティング システムによって制御されず、1 つのテーブルが複数のファイルに分散される可能性があります) または複数のファイル (独立したテーブル スペースに設定され、テーブル サイズはオペレーティング システムのファイル サイズによって制限され、通常は 2G) に格納されます。複数のファイルはオペレーティング システムのファイル サイズによって制限されます。 主キー インデックスはクラスター化インデックス (インデックスのデータ フィールドにはデータ ファイル自体が格納されます) を使用し、補助インデックスのデータ フィールドには主キーの値が格納されます。したがって、補助インデックスからデータを検索するには、まず補助インデックスを通じて主キーの値を見つけ、次に補助インデックスにアクセスする必要があります。B+ ツリー構造を維持するためにデータを挿入するときにファイルに大きな調整が行われないように、自動増分主キーを使用するのが最適です。 Innodb のプライマリ インデックス構造は次のとおりです。 MyISAM エンジン トランザクションはサポートされていませんが、各クエリはアトミックです。 テーブル レベルのロックをサポートします。つまり、各操作でテーブル全体がロックされます。 ストレージ テーブル内の行の合計数。 MYISAM テーブルには、インデックス ファイル、テーブル構造ファイル、データ ファイルの 3 つのファイルがあります。 クラスター化インデックスを使用すると、インデックス ファイルのデータ フィールドにデータ ファイルへのポインターが格納されます。セカンダリ インデックスは基本的にプライマリ インデックスと同じですが、セカンダリ インデックスは一意である必要はありません。 MYISAM のプライマリ インデックス構造は次のとおりです。 インデックス データ検索プロセスは次の 2 つです。 1.2 innodb ストレージ エンジン MySQL バージョン 5.5 以降ではデフォルトのストレージ エンジンとなり、高い信頼性と高いパフォーマンスを実現します。 1.2.1 Innodbエンジンの利点 a) トランザクションの安全性(ACID準拠) b) MVCC (マルチバージョン同時実行制御) c) InnoDB 行レベルロック d) 外部キー参照整合性制約のサポート e) 障害後の高速自動リカバリ(クラッシュセーフリカバリ) f) バッファプール(データバッファページ、ログバッファページ、UNDOバッファページ)は、メモリ内のデータとインデックスをキャッシュするために使用されます。 g) 大容量データで最大のパフォーマンス h) 異なるストレージエンジンを持つテーブルに対するクエリの混合 i) Oracle スタイルの一貫性のある非ロック読み取り (共有ロック) j) テーブルデータは主キーベースのクエリを最適化するように構成されています(クラスター化インデックス) 1.2.2 Innodb機能の概要
1.2.3 ストレージエンジンのクエリ方法 1. SELECT を使用してセッション ストレージ エンジンを確認します。 @@default_storage_engine を選択します。 または '%engine%' のような変数を表示します。 2. SHOW を使用して各テーブルのストレージ エンジンを確認します。 表示 CREATE TABLE City_G 'CountryLanguage' のようなテーブルステータスを表示\G 3. INFORMATION_SCHEMA を使用して、各テーブルのストレージ エンジンを確認します。 TABLE_NAME、ENGINEを選択 情報スキーマ.テーブル WHERE TABLE_NAME = 'City' かつ TABLE_SCHEMA = 'world_innodb'\G 4. バージョン5.1からバージョン5.5以上に移行する バージョン 5.1 データベースのすべての本番テーブルが MyISAM であるとします。 mysqldump を使用してバックアップした後、バックアップ ファイル内の engine フィールドを myisam から innodb に置き換える必要があります (sed コマンドを使用できます)。そうしないと、移行は無意味になります。 データベースをアップグレードするときは、他のサポート機能との互換性と、コードが新しい機能と互換性があるかどうかに注意してください。 1.2.4 ストレージエンジンの設定 1. 起動構成ファイルでサーバーのストレージ エンジンを設定します。 [mysqld] default-storage-engine=<ストレージエンジン> 2. SET コマンドを使用して、現在のクライアント セッションを設定します。 SET @@storage_engine=<ストレージエンジン>; 3. CREATE TABLE ステートメントで以下を指定します。 CREATE TABLE t (i INT) ENGINE = <ストレージ エンジン>; 1.3 InnoDB ストレージ エンジンのストレージ構造 1.3.1 InnoDB システムテーブルスペースの機能 デフォルトでは、InnoDB メタデータ、UNDO ログ、およびバッファはシステムの「テーブルスペース」に保存されます。 これは、1 つ以上のファイルを含めることができる単一の論理ストレージ領域です。 各ファイルは、通常のファイルまたは生のパーティションにすることができます。 最終ファイルは自動的に拡張されます。 1.3.2 表領域の定義 テーブルスペース: MySQL データベースの保存方法 表領域にはデータファイルが含まれています MySQlテーブルスペースとデータファイルは1:1の関係にあります 共有テーブルスペースを除き、1:Nの関係が可能 1.3.3 表領域タイプ 1. 共有テーブルスペース: ibdata1~ibdataN、通常2~3 2. 独立テーブルスペース: data/world/ディレクトリのcity.ibdなど、指定されたライブラリディレクトリに保存されます。 テーブルスペースの場所 (データディレクトリ): data/ディレクトリ内 1.3.4 システム表領域の記憶内容 共有テーブルスペース(物理ストレージ構造) ibdata1~Nは通常システムテーブルスペースと呼ばれ、データの初期化によって生成される。 システム メタデータ、基本テーブル データ、テーブル コンテンツ データ以外のデータ。 tmp テーブルスペース (一般的にはあまり注目されない) 元に戻すログ: データ - ロールバック データ (ロールバック ログで使用) REDO ログ: ib_logfile0~N には、システムの innodb テーブルのいくつかの REDO ログが保存されます。 注: UNDO ログはデフォルトで ibdata に保存され、5.6 以降では個別に定義できます。 tmpテーブルスペースはバージョン5.7以降ibdata1から移動され、ibtmp1になりました。 バージョン 5.5 より前では、すべてのアプリケーション データはデフォルトで ibdata に保存されていました。 独立したテーブルスペース(ストレージ エンジンの機能) 5.6 以降では、デフォルトで各テーブルは個別のテーブルスペース ファイルに保存されます。 InnoDB は、システム テーブルスペースに加えて、各 InnoDB テーブルの .ibd ファイル用の追加のテーブルスペースをデータベース ディレクトリに作成します。 InnoDB によって作成される新しいテーブルごとに、テーブルの .frm ファイルに対応する .ibd ファイルがデータベース ディレクトリに設定されます。 この設定は innodb_file_per_table オプションを使用して制御でき、これを変更すると、作成される新しいテーブルのデフォルトのみが変更されます。 。 1.3.5 共有テーブルスペースの設定 現在の共有テーブルスペース設定を表示する mysql> 'innodb_data_file_path' のような変数を表示します。 +----------------------+------------------------+ | 変数名 | 値 | +----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:自動拡張 | +----------------------+------------------------+ セット内の行数 (0.00 秒) 共有テーブルスペースを設定します。 この数値は通常、環境を最初に構築するときに設定され、デフォルト値は通常 1G です。最後の値は自動的に拡張されます。 [root@db02 world]# vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:76M;ibdata2:100M:自動拡張 現在の共有テーブルスペース設定を表示するには、サービスを再起動します。 mysql> 'innodb_data_file_path' のような変数を表示します。 +----------------------+-------------------------------------+ | 変数名 | 値 | +----------------------+-------------------------------------+ | innodb_data_file_path | ibdata1:76M;ibdata2:100M:自動拡張 | +----------------------+-------------------------------------+ セット内の行数 (0.00 秒) 1.3.6 独立した表領域の設定 バージョン 5.6 では、独立した表領域がデフォルトで有効になっています。 独立表領域に関する注意事項: 独立表領域が開かれていない場合、共有表領域が大きな領域を占有することになります。 mysql> '%per_table%' のような変数を表示します。 +-----------------------+-------+ | 変数名 | 値 | +-----------------------+-------+ | innodb_file_per_table | オン | +-----------------------+-------+ セット内の行数 (0.00 秒) 独立した表領域はパラメータファイル/etc/my.cnfで制御できます。 独立した表領域を閉じる(0は閉じ、1は開く) [root@db02 clsn]# vim /etc/my.cnf [mysqld] innodb_file_per_table=0 独立したテーブルスペースの構成を表示する mysql> '%per_table%' のような変数を表示します。 +-----------------------+-------+ | 変数名 | 値 | +-----------------------+-------+ | innodb_file_per_table | オフ | +-----------------------+-------+ セット内の行数 (0.00 秒) まとめ: innodb_file_per_table=0 独立したテーブルスペースを閉じる innodb_file_per_table=1 独立したテーブルスペースを開く、単一テーブルストレージ 1.4 MySQL のトランザクション 作業単位とみなされるデータ操作実行ステップのセット 複数のステートメントをグループ化するために使用され、複数のクライアントが同じテーブル内のデータに同時にアクセスする場合に使用できます。 すべてのステップは成功または失敗します すべてのステップが正常であれば実行され、ステップにエラーがあったり不完全な場合はキャンセルされます。 簡単に言えば、トランザクションは、作業単位内のステートメントが同時に成功または失敗することを保証するものです。 トランザクション処理フロー図 1.4.1 トランザクションとは何ですか? トランザクションを定義するよりも、トランザクションの特性について話す方がよいでしょう。ご存知のとおり、トランザクションは 4 つの ACID 特性を満たす必要があります。 A (原子性) 原子性。 トランザクションの実行は、分割できない最小単位とみなされます。トランザクション内の操作は、全体が正常に実行されるか、失敗してロールバックされる必要があります。操作の一部のみを実行することはできません。 すべてのステートメントが正常に実行されるか、すべてが単位としてキャンセルされます。 t1を更新し、money=10000-17を設定します。id=wxid1です。 t1を更新し、money=10000+17を設定します。id=wxid2です。 C (一貫性) 一貫性。 トランザクションの実行は、データベースの整合性制約に違反してはなりません。上記の例の 2 番目の操作が実行された後にシステムがクラッシュした場合、A と B の合計金額は変更されないことが保証されます。 トランザクションの開始時にデータベースが一貫した状態にある場合、そのトランザクションの実行中もデータベースは一貫した状態を維持します。 t1を更新し、money=10000-17を設定します。id=wxid1です。 t1を更新し、money=10000+17を設定します。id=wxid2です。 上記の操作中にアカウントを確認すると、まだ10000です I(隔離)隔離。 一般的に言えば、トランザクションの動作は互いに影響を及ぼさないはずです。ただし、実際には、トランザクションが相互に影響を与える程度は、分離レベルによって影響を受けます。これについては、この記事の後半で詳しく説明します。 トランザクションは相互に影響を与えません。操作を実行する際、他のユーザーが異なる分離条件下でこれら 2 つのアカウントに対して任意の操作を実行する可能性があり、一貫性の保証が異なる場合があります。 分離レベル 分離レベルは一貫性に影響します。 読み取りアンコミット X read-commit 使用できるレベル repeatable-read デフォルトのレベル、Oracle と同じ SERIALIZABLE 厳密なデフォルト、通常は使用されない このルールは、分離レベルによって制御されるだけでなく、ロックによっても制御されます。NFS の実装を考えることができます。 D(耐久性)耐久性。 トランザクションがコミットされた後、コミットされたトランザクションをディスクに永続化する必要があります。システムがクラッシュしても、送信されたデータは失われません。 データが配信されて初めて、取引は真に安全になります 1.4.2 トランザクション制御文 よく使用されるトランザクション制御ステートメント: START TRANSACTION (または BEGIN): 明示的に新しいトランザクションを開始します。COMMIT: 現在のトランザクションによって行われた変更を永続的に記録します (トランザクションは正常に終了します) ROLLBACK: 現在のトランザクションによって行われた変更を取り消します (トランザクションは失敗します) 知っておくべきトランザクション制御ステートメント: SAVEPOINT: 将来の参照のためにトランザクション内の場所を割り当てます。ROLLBACK TO SAVEPOINT: セーブポイント後に行われた変更をキャンセルします。RELEASE SAVEPOINT: セーブポイント識別子を削除します。SET AUTOCOMMIT: 現在の接続のデフォルトの自動コミット モードを無効または有効にします。 1.4.3 自動コミットパラメータ MySQL 5.5 以降では、トランザクションを開始するために begin または start transaction ステートメントは不要になりました。また、自動コミット モードはデフォルトで有効になっており、各ステートメントがトランザクションとして暗黙的にコミットされます。 忙しいビジネス シナリオでは、この構成によってパフォーマンスに大きな影響が出る可能性があります。ただし、セキュリティは大幅に向上します。今後は、ビジネスニーズを考慮して、自動送信するかどうかを調整する必要があります。 注: 本番環境では、実際のニーズに基づいて有効にするかどうかを選択してください。通常、銀行サービスは閉鎖されます。 現在の自動コミットのステータスを表示します。 mysql> '%autoc%' のような変数を表示します。 +---------------+-------+ | 変数名 | 値 | +---------------+-------+ | 自動コミット | オン | +---------------+-------+ セット内の行数 (0.00 秒) 設定ファイルを変更して再起動します [root@db02 world]# vim /etc/my.cnf [mysqld] 自動コミット=0 自動コミットのステータスを再度確認する mysql> '%autoc%' のような変数を表示します。 +---------------+-------+ | 変数名 | 値 | +---------------+-------+ | 自動コミット | オフ | +---------------+-------+ セット内の行数 (0.00 秒) mysql> @@autocommit を選択します。 +--------------+ | @@自動コミット | +--------------+ | 0 | +--------------+ セット内の行数 (0.00 秒) 注: 自動コミットをオンに設定した場合の比較 利点: 優れたデータセキュリティ、すべての変更が実装されます デメリット: 銀行取引ができず、多数の小さなIOが生成される 1.4.4 コミットを引き起こす非トランザクションステートメント: DDL ステートメント: (ALTER、CREATE、および DROP) DCL ステートメント: (GRANT、REVOKE、SET PASSWORD) ロックステートメント: (LOCK TABLES および UNLOCK TABLES) 暗黙的なコミットを引き起こすステートメントの例: テーブルを切り捨てる データをINFILEにロード 更新を選択 暗黙的なコミットの SQL ステートメント: 取引を開始 自動コミットを 1 に設定する 1.5 やり直しと元に戻す 1.5.1 トランザクションログの元に戻す 元に戻す原則: Undo ログの原理は非常にシンプルです。トランザクションの原子性を満たすために、データを操作する前に、データをある場所にバックアップする必要があります (データのバックアップが保存される場所は Undo ログと呼ばれます)。次にデータを変更します。 エラーが発生した場合、またはユーザーが ROLLBACK ステートメントを実行した場合、システムは Undo ログ内のバックアップを使用して、トランザクションが開始される前の状態にデータを復元できます。 トランザクションのアトミック性を保証することに加えて、Undo ログはトランザクションの永続性の完了を支援するためにも使用できます。 元に戻すとは何ですか? Undo は、その名前が示すように、「ロールバック ログ」と呼ばれるトランザクション ログの一種です。 機能は何ですか? トランザクション ACID プロセスでは、「A」の原子性の役割が実現されます。 Undo Logを使用してアトミックおよび永続的なトランザクションを実装するプロセスを簡素化します。 それぞれ値が 1 と 2 である 2 つのデータ A と B があるとします。 A.取引が開始されます。 B. 元に戻すログに A=1 を記録します。 C. A=3 を変更します。 D. B=2 を UNDO ログに記録します。 E. B=4 を変更します。 F. UNDO ログをディスクに書き込みます。 G. データをディスクに書き込みます。 H. トランザクションコミット ここでは暗黙の前提があります: 「データは最初にメモリに読み込まれ、次にメモリ内で変更され、最後にディスクに書き戻されます。原子性と永続性が同時に保証される理由は、次の特性によるものです。 A. データを更新する前に、Undo ログを記録します。 B. 耐久性を確保するには、トランザクションがコミットされる前にデータをディスクに書き込む必要があります。トランザクションが正常にコミットされている限り、データは永続化されている必要があります。 C. データの前に、UNDO ログをディスクに保存する必要があります。 G と H の間でシステムがクラッシュした場合、UNDO ログはそのまま残り、トランザクションをロールバックするために使用できます。 D. データがディスクに保存されていないため、AF 間でシステムがクラッシュした場合。したがって、ディスク上のデータはトランザクションが開始される前の状態のままになります。 欠陥: 各トランザクションがコミットされる前に、データと Undo ログがディスクに書き込まれるため、大量のディスク IO が発生し、パフォーマンスが非常に低下します。データを一定期間キャッシュできれば、IO を削減し、パフォーマンスを向上させることができます。しかし、これによりトランザクションの耐久性が失われます。 したがって、永続性を実現するために、Redo ログという別のメカニズムが導入されます。 1.5.2 トランザクションログの再実行 やり直しの原則: Undo ログとは異なり、Redo ログは新しいデータのバックアップを記録します。トランザクションがコミットされる前に、REDO ログを永続化するだけで十分であり、データを永続化する必要はありません。システムがクラッシュした場合、データは保持されませんが、REDO ログは保持されます。 システムは、REDO ログの内容に基づいてすべてのデータを最新の状態に復元できます。 Redoとは何ですか? 名前が示すように、「REDO ログ」はトランザクション ログの一種です。 機能は何ですか? トランザクション ACID プロセスでは、「D」永続性の役割が実現されます。 元に戻す + やり直しトランザクションの簡素化されたプロセス それぞれ値が 1 と 2 である 2 つのデータ A と B があるとします。 A.取引が開始されます。 B. 元に戻すログに A=1 を記録します。 C. A=3 を変更します。 D. REDOログにA=3を記録します。 E. B=2 を UNDO ログに記録します。 F. B=4 を変更します。 G. B=4 を REDO ログに記録します。 H. REDO ログをディスクに書き込みます。 I. トランザクションのコミット 元に戻す + やり直しトランザクションの特徴 A. 耐久性を確保するには、トランザクションがコミットされる前に Redo ログを永続化する必要があります。 B. トランザクションがコミットされる前にデータをディスクに書き込む必要はなく、メモリにキャッシュされます。 C. Redo ログはトランザクションの永続性を保証します。 D. Undo ログはトランザクションの原子性を保証します。 E. データは REDO ログよりも後に永続ストレージに書き込まれる必要があるという暗黙の特性があります。 やり直しがディスクに保持されるかどうか innodb_flush_log_at_trx_commit=1/0/2 1.5.3 トランザクションにおけるロック 「ロック」とは何ですか? 「ロック」とは、その名の通り、ロックすることを意味します。 「ロック」の機能は何ですか? トランザクションの ACID プロセスでは、「ロック」と「分離レベル」が連携して、「I」分離の役割を果たします。 ロックの粒度: 1. MyIasm: 低同時実行ロック - テーブルレベルロック 2. Innodb: 高同時実行ロック - 行レベルロック 4 つの分離レベル: READ UNCOMMITTED を使用すると、トランザクションは他のトランザクションによって行われたコミットされていない変更を表示できます。READ COMMITTED を使用すると、トランザクションは他のトランザクションによって行われたコミットされた変更を表示できます。REPEATABLE READ****** は、各トランザクションの SELECT 出力の一貫性を保証します。InnoDB のデフォルト レベル SERIALIZABLE は、1 つのトランザクションの結果を他のトランザクションから完全に分離します。 オーバーヘッド、ロック速度、デッドロック、粒度、同時実行パフォーマンス テーブル レベルのロック: オーバーヘッドが低く、ロックが高速で、デッドロックがなく、ロックの粒度が大きく、ロック競合の可能性が最も高く、同時実行性が最も低い。 行レベル ロック: オーバーヘッドが高く、ロックが遅く、デッドロックが発生する可能性があります。ロックの粒度は最小で、ロック競合の可能性は最も低く、同時実行性は最も高くなります。 ページ ロック: オーバーヘッドとロック時間はテーブル ロックと行ロックの中間です。デッドロックが発生する可能性があります。ロックの粒度はテーブル ロックと行ロックの中間で、同時実行性は平均的です。 上記の特性から、どのロックが優れているかを一概に言うのは難しいことがわかります。特定のアプリケーションの特性に基づいて、どのロックがより適しているかを言うことしかできません。 ロックの観点からのみ見ると、テーブル レベルのロックは、Web アプリケーションなど、クエリ指向でインデックス条件に従って少量のデータのみを更新するアプリケーションに適しています。一方、行レベルのロックは、一部のオンライン トランザクション処理 (OLTP) システムなど、インデックス条件と同時クエリに従って少量の異なるデータを多数同時に更新するアプリケーションに適しています。 1.6 MySQL ログ管理 1.6.1 MySQL ログタイプの紹介 ログタイプの説明:
1.6.2 設定方法 ステータス エラー ログ: [mysqld] ログエラー=/data/mysql/mysql.log 設定方法を表示します: mysql> '%log%error%' のような変数を表示します。 効果: MySQLデータベースの一般的なステータス情報とエラー情報を記録することは、 一般的なデータベース エラー処理のための共通ログ。 mysql> '%log%err%' のような変数を表示します。 +---------------------+----------------------------------+ | 変数名 | 値 | +---------------------+----------------------------------+ | binlog_error_action | IGNORE_ERROR | | log_error | /application/mysql/data/db02.err | +---------------------+----------------------------------+ セット内の行数 (0.00 秒) 1.6.3 一般クエリログ 設定方法: [mysqld] 一般ログイン=オン 一般ログファイル=/data/mysql/server2.log 設定方法を表示します: '%gen%' のような変数を表示します。 効果: 監査の目的で、MySQL で正常に実行されたすべての SQL ステートメントを記録しますが、これを有効にすることはほとんどありません。 mysql> '%gen%' のような変数を表示します。 +------------------+----------------------------------+ | 変数名 | 値 | +------------------+----------------------------------+ | 一般ログ | オフ | | 一般ログファイル | /application/mysql/data/db02.log | +------------------+----------------------------------+ セット内の行数 (0.00 秒) 1.7 バイナリログ バイナリ ログはストレージ エンジンに依存しません。 SQLレイヤーに依存し、SQL文に関連する情報を記録します Binlog ログ機能: 1. バックアップ機能を提供する 2. マスタースレーブレプリケーションを実行する 3. 時点に基づく回復 SQL レイヤーで実行されたステートメントを記録します。トランザクションの場合は、完了したトランザクションを記録します。 機能: ポイントインタイムバックアップとポイントインタイムリカバリ、マスタースレーブ バイナリログの「正門」 効果: 1. 有効にするかどうか 2. バイナリログのパス /data/mysql/ 3. バイナリログファイル名のプレフィックス mysql-bin 4. ファイル名は「prefix.000001~N」で始まります。 ログ bin = /data/mysql/mysql-bin バイナリログ「スイッチ」: これはメインスイッチがオンになっている場合にのみ意味を持ち、デフォルトではオンになっています。 臨時休業する場合がございます。 現在のセッションにのみ影響します。 sql_log_bin=1/0 1.7.1 バイナリログ形式 ステートメント、ステートメントモード: 記録される情報は簡潔で、SQL ステートメント自体のみが記録されます。ただし、ステートメント内に関数演算が含まれる場合、記録されたデータが不正確になる可能性があります。 これは 5.6 のデフォルト モードですが、実稼働環境では注意して使用する必要があります。 row に変更することをお勧めします。 行、行モード テーブル内の行データを変更するプロセス。 記録されたデータは詳細であり、IO パフォーマンスに対する要件が高くなります。記録されたデータはどのような状況でも正確です。 これは通常、生産時のモードです。 5.7 以降のデフォルト モード。 混合、混合モード 判定後、行+文の混合記録モードが選択されます。 (通常は使用されません) 1.7.2 バイナリログを有効にする mysql> '%log_bin%' のような変数を表示します。 +---------------------------------+-------+ | 変数名 | 値 | +---------------------------------+-------+ | log_bin | オフ | | ログ ビン ベース名 | | | ログ ビン インデックス | | | log_bin_trust_function_creators | オフ | | log_bin_use_v1_row_events | オフ | | sql_log_bin | オン | +---------------------------------+-------+ セット内の行数 (0.00 秒) バイナリログを有効にするために構成ファイルを変更する [root@db02 tmp]# vim /etc/my.cnf [mysqld] ログ bin = /application/mysql/data/mysql-bin コマンドラインの変更方法 mysql> グローバルbinlog_format = 'ステートメント'を設定します mysql> SET GLOBAL binlog_format = 'ROW'; mysql> SET GLOBAL binlog_format = 'MIXED'; バイナリログファイルの種類を表示する [root@db02 data]# ファイル mysql-bin.* mysql-bin.000001: MySQL レプリケーション ログ mysql-bin.index: ASCII テキスト MySQL 構成を表示します。 mysql> '%log_bin%' のような変数を表示します。 +---------------------------------+-----------------------------------------+ | 変数名 | 値 | +---------------------------------+-----------------------------------------+ | log_bin | オン | | log_bin_basename | /application/mysql/data/mysql-bin | | log_bin_index | /application/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | オフ | | log_bin_use_v1_row_events | オフ | | sql_log_bin | オン | +---------------------------------+-----------------------------------------+ セット内の行数 (0.00 秒) 1.7.3 記録方法の定義 現在のフォーマットを表示 mysql> '%format%' のような変数を表示します。 +--------------------------+-------------------+ | 変数名 | 値 | +--------------------------+-------------------+ | binlog_format | ステートメント | | 日付形式 | %Y-%m-%d | | 日付時刻形式 | %Y-%m-%d %H:%i:%s | | デフォルトの週形式 | 0 | | innodb_file_format | アンテロープ | | innodb_file_format_check | オン | | innodb_file_format_max | アンテロープ | | 時間形式 | %H:%i:%s | +--------------------------+-------------------+ セット内の行数 (0.00 秒) フォーマットを変更する [root@db02 データ]# vim /etc/my.cnf [mysqld] binlog_format=行 変更後の確認 mysql> '%format%' のような変数を表示します。 +--------------------------+-------------------+ | 変数名 | 値 | +--------------------------+-------------------+ | binlog_format | 行 | | 日付形式 | %Y-%m-%d | | 日付時刻形式 | %Y-%m-%d %H:%i:%s | | デフォルトの週形式 | 0 | | innodb_file_format | アンテロープ | | innodb_file_format_check | オン | | innodb_file_format_max | アンテロープ | | 時間形式 | %H:%i:%s | +--------------------------+-------------------+ セット内の行数 (0.00 秒) 1.8 バイナリログ操作 1.8.1 表示 オペレーティング システム レベルでの表示 [root@db02 data]# ll mysql-bin.* -rw-rw---- 1 mysql mysql 143 12月20日 20:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 120 12月20日 20:17 mysql-bin.000002 -rw-rw---- 1 mysql mysql 82 12月 20 20:17 mysql-bin.index ログを更新 mysql> ログをフラッシュします。 更新が完了した後のログディレクトリ [root@db02 data]# ll mysql-bin.* -rw-rw---- 1 mysql mysql 143 12月20日 20:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 167 12月20日 20:24 mysql-bin.000002 -rw-rw---- 1 mysql mysql 120 12月20日 20:24 mysql-bin.000003 -rw-rw---- 1 mysql mysql 123 12月20日 20:24 mysql-bin.index [root@db02 データ]# 現在使用中のバイナリログファイルを表示する mysql> マスターステータスを表示します。 +------------------+----------+--------------+------------------+------------------+ | ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------+ | mysql-bin.000003 | 120 | | | | +------------------+----------+--------------+------------------+------------------+ セット内の行数 (0.00 秒) すべてのバイナリログファイルを表示 mysql> バイナリログを表示します。 +------------------+-----------+ | ログ名 | ファイルサイズ | +------------------+-----------+ |mysql-bin.000001 | 143 | |mysql-bin.000002 | 167 | | mysql-bin.000003 | 120 | +------------------+-----------+ セット内の行数 (0.00 秒) 1.8.2 バイナリログの内容の表示 用語集: 1. イベント バイナリログの定義方法: コマンド発生の最小単位 2. 位置 バイナリ ファイル全体において各イベントが対応する位置番号が位置番号です。 mysql> マスターステータスを表示します。 +------------------+----------+--------------+------------------+------------------+ | ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------+ | mysql-bin.000003 | 120 | | | | +------------------+----------+--------------+------------------+------------------+ セット内の行数 (0.00 秒) [root@db02 データ]# mysqlbinlog mysql-bin.000003 >/tmp/aa.ttt すべての情報をエクスポートする [root@db02 データ]# mysqlbinlog mysql-bin.000003 >/tmp/aa.ttt binlog を表示する方法: 1. 元のバイナリログ情報を表示する mysqlbin.000002 です。 2. 行モードでは、ステートメントに変換します mysqlbinlog --base64-output='デコード行' -v mysql-bin.000002 3. バイナリログイベントを表示する バイナリログを表示します。使用中のすべてのバイナリログ情報を表示します。'ログファイル' 内のバイナリログイベントを表示します。 4. バイナリログの内容を傍受し、必要に応じて復元する方法(従来の考え方) (1)バイナリログを表示し、マスターステータスを表示する。 (2)バイナリログイベントを「最後から先頭まで読み取り、誤った操作のあるトランザクションを見つけ、トランザクションの開始位置と終了位置を決定する」で表示する (3)誤った操作を排除し、正常な操作を2つのSQLファイルに残す (4)まず、テストデータベースを復元し、誤操作によって発生したデータをエクスポートしてから、本番環境を再開します。 上記の方法を使用すると発生する問題: 回復イベントの長期化 生産データに一定の影響を与え、冗長なデータが表示される可能性がある より良い解決策。 1. フラッシュバック機能 2. データベースからバックアップまでの遅延 1.8.3 mysqlbinlog を使用してバイナリ ログを傍受する方法 mysqlbinlog の一般的なオプションは次のとおりです。
バイナリログファイルの例: mysqlbinlog --start-position=120 --stop-position=end number 1.8.4 バイナリログの削除 デフォルトでは、古いログ ファイルは削除されません。 経過時間に基づいてログを削除します: グローバルexpire_logs_daysを7に設定します。 または、now() より前のバイナリ ログを消去 - 間隔 3 日; ファイル名に従ってログを削除します。 バイナリログを 'mysql-bin.000010' に消去します。 バイナリ ログ カウントをリセットし、1 からカウントを開始し、元のバイナリ ログを削除します。 マスターをリセット 1.9 MySQL スロークエリログ (スローログ) 1.9.1 これは何ですか? slow-logは条件内のすべての遅いSQL文を記録します。 最適化のためのツールログ。問題の特定に役立ちます。 1.9.2 スロークエリログ MySQLサーバのデータベースパフォーマンスに影響を与える関連SQL文をログファイルに記録します。 これらの特殊な SQL ステートメントを分析して改善することで、データベースのパフォーマンスを向上させるという目的を達成できます。スローログ設定 long_query_time: スロークエリのしきい値を設定します。設定値を超えるSQLはスロークエリログに記録されます。デフォルト値は10秒です。 slow_query_log: スロークエリログを有効にするかどうかを指定します slow_query_log_file: スローログファイルを保存する場所を指定します。空にすることもできます。その場合、システムはデフォルトのファイル host_name-slow.log を指定します。 min_examined_row_limit: クエリチェックで指定されたパラメータよりも少ない行を返すSQLはスロークエリログに記録されません。log_queries_not_using_indexes: インデックスを使用しないスロークエリログがインデックスに記録されるかどうか スロークエリログの設定 [root@db02 htdocs]# vim /etc/my.cnf slow_query_log=オン slow_query_log_file=/tmp/slow.log long_query_time=0.5 # 遅いログレコードのしきい値を制御する log_queries_not_using_indexes 設定が完了したら、サービスを再起動します... スロー クエリ ログが有効になっているかどうか、およびその場所を確認します。 mysql> '%slow%' のような変数を表示します -> ; +---------------------------+---------------+ | 変数名 | 値 | +---------------------------+---------------+ | log_slow_admin_statements | オフ | | log_slow_slave_statements | オフ | | 遅い起動時間 | 2 | | slow_query_log | オン | | slow_query_log_file | /tmp/slow.log | +---------------------------+---------------+ セット内の行数 (0.00 秒) 1.9.3 mysqldumpslow コマンド /path/mysqldumpslow -sc -t 10 /database/mysql/slow-log これにより、最も多くのレコードを含む上位 10 個の SQL ステートメントが出力されます。
1.9.4 binlog と redolog でコミットされたトランザクションの一貫性を確保する方法 binlog が有効になっていない場合、コミットを実行すると、REDO ログがディスク ファイルに永続化されているとみなされ、コミット コマンドは成功します。 binlog パラメータを書き込みます: mysql> '%sync_binlog%' のような変数を表示します。 +---------------+-------+ | 変数名 | 値 | +---------------+-------+ | sync_binlog | 0 | #binlogコミットフェーズの制御+---------------+-------+ セット内の行数 (0.00 秒) sync_binlog は、コミットされたすべてのトランザクションが binlog に書き込まれることを保証します。 1.9.5 MySQLのダブルワン標準: 2 つのパラメータ innodb_flush_log_at_trx_commit と sync_binlog は、MySQL ディスク書き込み戦略とデータ セキュリティを制御するための重要なパラメータです。 パラメータの意味: innodb_flush_log_at_trx_commit=1 innodb_flush_log_at_trx_commit が 0 に設定されている場合、ログ バッファは 1 秒ごとにログ ファイルに書き込まれ、同時にログ ファイルのフラッシュ操作が実行されます。このモードでは、トランザクションがコミットされると、ディスクへの書き込み操作はアクティブにトリガーされません。 innodb_flush_log_at_trx_commit が 1 に設定されている場合、MySQL はトランザクションがコミットされるたびにログ バッファー内のデータをログ ファイルに書き込み、ディスクにフラッシュします。 innodb_flush_log_at_trx_commit が 2 に設定されている場合、MySQL はトランザクションがコミットされるたびにログ バッファー内のデータをログ ファイルに書き込みます。ただし、フラッシュ操作は同時に実行されません。このモードでは、MySQL は 1 秒ごとにフラッシュ操作を実行します。 知らせ: プロセス スケジューリング戦略により、この「フラッシュ操作は 1 秒あたり 1 回実行されます」は、100%「1 秒あたり」であることが保証されません。 パラメータの意味: 同期バイナリログ=1 sync_binlog のデフォルト値は 0 です。他のファイルを更新するためのオペレーティング システムのメカニズムと同様に、MySQL はディスクに同期せず、バイナリ ログの更新をオペレーティング システムに依存します。 sync_binlog = N (N>0) の場合、MySQL はバイナリ ログを N 回書き込むたびに fdatasync() 関数を使用してバイナリ ログをディスクに同期します。 注記: 自動コミットが有効になっている場合は、ステートメントごとに 1 つの書き込み操作が行われます。そうでない場合は、トランザクションごとに 1 つの書き込み操作が行われます。 安全上の注意 innodb_flush_log_at_trx_commit と sync_binlog が両方とも 1 の場合、最も安全です。mysqld サービスまたはサーバー ホストがクラッシュした場合、バイナリ ログで失われる可能性があるのは、最大で 1 つのステートメントまたは 1 つのトランザクションのみです。ただし、両方を同時に実現することはできません。11 を 2 回実行すると IO 操作が頻繁に発生するため、このモードは最も遅い方法でもあります。 innodb_flush_log_at_trx_commit が 0 に設定されている場合、mysqld プロセスがクラッシュすると、最後の 1 秒間のすべてのトランザクション データが失われます。 innodb_flush_log_at_trx_commit が 2 に設定されている場合、オペレーティング システムがクラッシュするか、システムの電源が失われた場合にのみ、最後の 1 秒間のすべてのトランザクション データが失われる可能性があります。 Double 1 は、注文、取引、再チャージ、支払い消費システムなどのサービスをサポートするために、非常に高いデータ セキュリティ要件と十分なディスク IO 書き込み容量を備えた企業に適しています。ダブル 1 モードでは、11.11 イベントの圧力など、ディスク IO がビジネス ニーズを満たせない場合。推奨される方法は、innodb_flush_log_at_trx_commit=2、sync_binlog=N (N は 500 または 1000) を設定し、システムの電源停止を防ぐためにバッテリ バックアップ電源を備えたキャッシュを使用することです。 システム パフォーマンスとデータ セキュリティは、ビジネス システムの高可用性と安定性に必要な要素です。システム最適化のバランスポイントを見つける必要があります。正しいものが最適です。さまざまなビジネス シナリオの要件に応じて、2 つのパラメーターを組み合わせて調整し、DB システムのパフォーマンスを最適化できます。 上記のMySQLベースのストレージエンジンとログの説明(包括的な説明)は、編集者が皆さんと共有するすべての内容です。参考になれば幸いです。また、123WORDPRESS.COMを応援していただければ幸いです。 以下もご興味があるかもしれません:
|
<<: デスクトップ仮想化を実現するために Hyper-V を展開する手順 (グラフィック チュートリアル)
>>: Vue プロジェクトで垂直テーブルを 2 つの方法で実装するアイデアの分析
この記事では、例を使用して、MySQL トリガーの追加、削除、変更、およびクエリ操作について説明しま...
LOFTER のコンテストで、ログイン ボックスを再設計できると言及されているのを見ました。過去 2...
まず、ブロガーはコミュニティ バージョンをプレイしていますが、学習とテストにはこれで十分です。 Bl...
目次序文1. ロックとは何ですか? 2. InnoDBストレージエンジンのロック2.1 ロックの種類...
「/」はルートディレクトリ、「~」はホームディレクトリです。 Linux ストレージはツリー状にマウ...
ページ上で PDF をプレビューすると、一部のファイルは印刷またはダウンロードできません。現時点では...
1. ファイルを現在のディレクトリに解凍しますコマンド: tar -zxvf mysql....ta...
弊社の Web プロジェクトの 1 つでは、新しい都市の増加によりトラフィックと DB 負荷が増加し...
序文コードを書く過程で、必然的にコードに何らかの変更を加えることになります。しかし、変更を加えるとき...
フロントエンドとバックエンドを完全に分離する場合、Vue プロジェクトでトークン検証を実装する一般的...
CSS を使用して 3D フォト ウォールを作成します。具体的なコードは次のとおりです。 <!...
@vue+echarts は中国地図のフロー効果を実現します#レンダリングを見てみましょう手順:コ...
目次1. インストール2.APi 3. react-beautiful-dnddemo 3.1dem...
しばらくReactを勉強した後、実践してみたいと思います。そこで、個人のブログのウェブサイトを再構築...
Centos6.4 で mysql5.7.18 をインストールするための具体的な手順が全員に共有され...