20201120のMySQLに関する記事は6件です。

MySQL Shell Dump UtilityとDump Loading Utilityの使い方

  • Oracle MySQL Database Serviceの使い方メモ〜番外編その3
  • MySQL Shell Dump Utility / Dump Loading Utility
  • ボリュームが多く読み辛かったので別記事に分けました

はじめに

MySQL Shellは、ORACLEが提供するMySQL用のクライアントツールで、従来のMySQL Clientが有しているSQL実行機能に加えて、JavaScriptおよびPythonの実行機能を有しており、これらのスクリプト言語で実装されたMySQLを操作するためのAPIを実行できます。また、 mysqldumpに相当するMySQL Shell Dump Utility群 (Instance Dump Utility, Schema Dump Utility, and Table Dump Utility) ならびにDump Loading Utilityを実行することができます。これらのデータ移行ユーテリティは、従来型のmysqldumpやmysqlpumpに比べて圧倒的なスループット性能を有していることが報告されています。1 MySQL Shell Dump UtilityとDump Loading Utilityでは、OCI Object Storageへのダンプファイルのエクスポート・インポートがサポートおり、この機能を利用してクラウドバックアップ・リストアやOracle Cloudのマネージド・サービスであるOracle MySQL Database Serviceへのデータ移行を実行できます。

MySQL Shell Dump UtilityとDump Loading Utilityを使用する前提条件

  • MySQL Shellのバージョンによって利用できるDump Utilityは下記
    • MySQL Shell 8.0.22 : Instance Dump Utility, Schema Dump Utility, Table Dump Utility
    • MySQL Shell 8.0.21 : Instance Dump Utility, Schema Dump Utility
  • データの移行元であるソースMySQLインスタンスと移行先である宛先MySQLインスタンスの両方にMySQL5.7以降であること
  • インスタンスまたはスキーマのオブジェクト名は、latin1またはutf8文字セットに含まれていること
  • データの整合性は、InnoDBデータベースエンジンを利用している場合のみ保証される
  • Dump Utilityの実行に使用されるユーザ・アカウントが、エクスポートするすべてのスキーマに対してBACKUP_ADMIN, EVENT, RELOAD, SELECT, SHOW VIEW, and TRIGGER権限を持っている必要 (データ一貫性を保つconsistent オプションが無効falseの場合、 BACKUP_ADMINおよびRELOAD権限は必要ない。consistentオプションがデフォルトで設定されている有効trueである場合は、RELOAD権限の代わりにLOCK TABLES権限を利用することが可能)
  • Dump Utilityは、テキスト形式で安全に保存できないデータ型(BLOBなど)の列をBase64に変換する。そのため、これらのカラムのサイズはターゲット MySQL インスタンスで設定されている max_allowed_packetシステム変数の値(バイト数)の約 0.74 倍を超えてはいけない
  • Table Dump Utilityでは、エクスポートされるビューとトリガーは、他のビューやテーブルを参照するために修飾名を使用しないこと
  • エクスポート先にOCI Object Storageを利用する場合は、MySQL Shellを実行する環境からOCI CLIを利用してOCI Object Storageバケット (インターネット) に直接アクセスできること
  • エクスポート先にOCI Object Storageを利用する場合は、アップロードされるDumpファイルの容量に制限あり
    • MySQL Shell 8.0.22 : 1.2 TB
    • MySQL Shell 8.0.21 : 約640 GB

MySQL Shell Dump UtilityとDump Loading Utilityの実行コマンド

Instance Dump Utility

  • 基本構文
util.dumpInstance(outputUrl[, options]) 
  • 引数

    • outputUrl : エクスポート・ダンプファイルを配置するディレクトリ・パス
    • options : オプション変数、Dump Utilityのオプション変数の項を参照
  • コマンド例

ローカルにエクスポート
util.dumpInstance("/Users/username/dumps")
ObjectStorageバケットにエクスポート
util.dumpInstance("ObjectStorageBucketPrefix", {osBucketName: "ObjectStorageBucketName", osNamespace: "ObjectStorageNamespace", threads: 4})

Schema Dump Utility

  • 基本構文
util.dumpSchemas(schemas, outputUrl[, options])
  • 引数

    • schemas : エクスポート対象となるスキーマ名 (対象スキーマが1つの場合でもリスト形式の値が必要)
    • outputUrl : エクスポート・ダンプファイルを配置するディレクトリ・パス
    • options : オプション変数、Dump Utilityのオプション変数の項を参照
  • コマンド例

ローカルにエクスポート
util.dumpSchemas(["test"], "/Users/username/dumps")
ObjectStorageバケットにエクスポート
util.dumpSchemas(["test"], "ObjectStorageBucketPrefix", {osBucketName: "ObjectStorageBucketName", osNamespace: "ObjectStorageNamespace", threads: 4})

Table Dump Utility

  • 基本構文
util.dumpTables(schema, tables, outputUrl[, options])
  • 引数

    • schema : エクスポート対象となるテーブルが含まれるスキーマ名
    • tables : エクスポート対象となるテーブルが含まれるスキーマ名 (対象テーブルが1つの場合でもリスト形式の値が必要)
    • outputUrl : インポート対象となるダンプファイルが配置されたディレクトリ・パス
    • options : オプション変数、Dump Loading Utilityのオプション変数の項を参照
  • コマンド例

ローカルにエクスポート
util.dumpTables("test", ["users"], "/Users/username/dumps")
ObjectStorageバケットにエクスポート
util.dumpTables("test", ["users"], "ObjectStorageBucketPrefix", {osBucketName: "ObjectStorageBucketName", osNamespace: "ObjectStorageNamespace", threads: 4})

Dump Loading Utility

  • 基本構文
util.loadDump(url[, options])
ローカルディレクトリからインポート
util.loadDump("/Users/username/dumps")
ObjectStorageバケットからインポート
util.loadDump("ObjectStorageBucketPrefix",{osBucketName:"ObjectStorageBucketName",osNamespace:"ObjectStorageNamespace",threads:4,progressFile:"/Users/username/progressfile.json"})

オプション変数

Dump Utilityのオプション変数

基本

  • dryRun:[true | false]
    コマンドを検証を行うが、インポートは行わない
    デフォルトはfalse

  • showProgress: [ true | false ]

    エクスポートの進捗情報を表示 (true) または非表示 (false) を指定。デフォルトは、stdoutがターミナル(tty)である場合(MySQLシェルが対話型モードの場合など)はtrueで、そうでない場合はfalse

  • excludeSchemas: "string"

    (Instance Dump Utilityのみ) Dumpから除外するスキーマ名を指定。ただし、information_schema、mysql、ndbinfo、performance_schema、および sysスキーマは常にインスタンスダンプから除外される。指定されたスキーマが存在しない場合、指定した項目を無視する

  • excludeTables: "string"

    (Instance Dump Utility, Schema Dump Utilityのみ) Dumpから除外するテーブル名を指定。テーブル名は有効なスキーマ名で修飾し、必要に応じて引用符で囲む必要がある。excludeTables オプションで指定されたテーブルは、ダンプ内にDDLファイルやデータファイルを持たない。一方で、mysql.apply_status、mysql.general_log、mysql.schema、および mysql.slow_log テーブルのデータは、それらの DDL ステートメントは含まれるが、常にスキーマダンプから除外される。指定したテーブルがスキーマに存在しない場合、指定した項目を無視する

  • all: [ true | false ]

    (Table Dump Utilityのみ) このオプションを true に設定すると、指定したスキーマのすべてのビューとテーブルがダンプに含まれる。このオプションを使用する場合は、tablesパラメータを空の配列に設定する。デフォルトはfalse

  • users: [ true | false ]

    (Instance Dump Utilityのみ) Dumpにユーザーとそのrolesおよびgrantsを含める (true) か除外する (false) かを指定します。デフォルトはtrue。MySQL Shell 8.0.22 以降では、 excludeUsers または includeUsers オプションを使用して、ダンプファイルに除外または含めるユーザアカウントを個別に指定することができる。これらのオプションは、MySQL ShellDump Loading Utilityを使用して、インポート時に個々のユーザアカウントを除外または含める指定も可能 (MySQL Shell 8.0.21ではユーザーのインポートに非対応)

  • excludeUsers: array of strings

    (Instance dump utilityのみ) Dumpに除外するユーザー名を指定。ユーザ名とホスト名で定義されたアカウントの場合は "'user_name'@'host_name'"、ユーザ名のみで定義されたアカウントの場合は "'user_name'" という形式で各ユーザアカウントの文字列を指定。 ("'user_name'@'%'" と同等)。指定されたユーザアカウントが存在しない場合、ユーティリティはその項目を無視。 (MySQL Shell 8.0.21ではユーザーのインポートに非対応)

  • includeUsers: array of strings

    (Instance dump utilityのみ) Dumpに除外するユーザー名を指定。特定のユーザーのみDumpに含めたい場合にexcludeUsersの代わりに利用。excludeUsersと共に指定することも可能で、重複しているユーザーがある場合は除外される (MySQL Shell 8.0.21ではユーザーのインポートに非対応)

  • events: [ true | false ]

    (Instance dump utility, Schema dump utilityのみ) Dumpに各スキーマのイベントを含める (true) か除外する (false) かを指定。デフォルトはtrue

  • routines: [ true | false ]

    (Instance dump utility, Schema dump utilityのみ) Dumpに各スキーマのファンクションとストアド・プロシージャを含める (true) か除外する (false) かを指定。既定値はtrueだが、routinestrueに設定されている場合でも、ユーザー定義関数は含まれないことに注意

  • triggers: [ true | false ]

    Dumpにトリガーを含める(true)か除外する(false)かを指定。デフォルトはtrue

  • defaultCharacterSet: "string"

    MySQL ShellがDump Utilityのために開くセッション接続で使用する文字セット。システム変数 character_set_clientcharacter_set_connectioncharacter_set_results のセッション値は、接続ごとにこの値に設定される。指定する文字セットは、システム変数 character_set_client で許可され、MySQL インスタンスでサポートされている必要がある。デフォルトは utf8mb4

  • tzUtc: [ true | false ]

    Dumpの冒頭に、タイムゾーンをUTCに設定するステートメントを含めるかどうかを指定。ダンプ出力のすべてのtimestampデータは、UTCに変換される。デフォルトはtrue。このオプションをfalseに設定すると、元のタイムスタンプを保持する

  • consistent: [ true | false ]

    Dump中にバックアップ用のインスタンスをロックすることで、一貫性のあるデータダンプを有効(true)または無効(false)に設定。既定値はtruetrueが設定されている場合、ユーティリティは、FLUSH TABLES WITH READ LOCKステートメントを使用してグローバル読み取りロックが設定され、各スレッドのトランザクションは、SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READおよびSTART TRANSACTION WITH CONSISTENT SNAPSHOTステートメントを使用して開始される。すべてのスレッドがトランザクションを開始すると、インスタンスはバックアップのためにロックされ、グローバル読み取りロックが解除される

  • ddlOnly: [ true | false ]

    このオプションをtrueに設定すると、DDLファイルのみがエクスポートされ、データはエクスポートされない。既定値はfalse

  • dataOnly: [ true | false ]

    このオプションをtrueに設定すると、Dumpにはデータファイルのみが含まれ、DDLファイルは含まれない。既定値はfalse

パフォーマンス・チューニング関連

  • threads: int

    MySQLインスタンスからデータをエクスポートするために使用する並列スレッドの数使用する並列スレッドの数。並列数を増やすことでネットワーク帯域を効率的に利用できるが、CPU使用率は増加する。デフォルトは4

  • maxRate: "string"

    エクスポート中のデータ読み取りスループットのスレッドあたりの最大バイト数を指定。KByteの場合は k、MByteの場合は M、GByteの場合は Gを利用。例えば、100Mと指定した場合、スレッドあたり1秒あたり 100 MByteの読み込みに制限。0(デフォルト値) を設定するか、このオプションを空文字列に設定すると、無制限となる

  • compression: "string"

    ダンプ用のデータファイルを書き込むときに使用する圧縮タイプ。デフォルトは zstd 圧縮 (zstd) を使用。zstd 圧縮 (zstd) の他に gzip 圧縮 (gzip) や圧縮なし (none) を指定可能

  • chunking: [ true | false ]

    エクスポート・データのchunkingを有効 (true) または無効 (false) にして、各テーブルのデータを複数のファイルに分割する。デフォルトはtrueで、chunkingが有効になっています。チャンク サイズを指定するには bytesPerChunkオプションを使用。chunkingの利用には、テーブルの主キーまたは一意のインデックスを定義する必要があり、テーブルにいずれも含まれていない場合は、警告が表示され、chunkingされずテーブルデータは1つのファイルに書き込まれる。chunking オプションをfalseに設定すると、chunkingは行われず、ユーティリティは各テーブルに1つのDumpファイルを作成します。chunkingを利用することでthreadsで指定された複数スレッドを効率的に稼働させることができる

  • bytesPerChunk: "string"

    chunkingが有効な場合に各データファイルに書き込むバイト数の目安を設定。キロバイトの場合はk、メガバイトの場合はM、ギガバイトの場合はGという単位の接尾辞を使用する。デフォルトは MySQL Shell 8.0.22 の 64 MB (64M)、最小は 128 KB (128k)。このオプションを指定すると、暗黙でchunkingが有効 (true) に設定される。threadsとの組み合わせがDump Utilityのパフォーマンス・チューニングに関わる重要な要素。

OCI Object Storage関連

  • osBucketName: "string"

    ダンプファイルが配置されているOCI ObjectStorageバケットの名前
    デフォルトでは、OCI CLI構成ファイルのDEFAULTプロファイルを使用

  • osNamespace: "string"

    osBucketNameで指定されたObjectStorageバケットが配置されているOCIのネームスペース。 Object Storageバケットのネームスペースは、OCIコンソールのバケット詳細ページの「バケット情報」タブに表示される。または、OCI CLIを使用して取得

  • ociConfigFile: "string"

    デフォルトの~/.oci/config以外のOCI CLIのcofigファイルを利用する場合にPATHを指定

  • ociProfile: "string"

    OCI CLI構成ファイルのDEFAULTプロファイルを以外のプロファイルを使用する際にプロファイル名を指定

  • ociParManifest: [ true | false ]

    (Instance dump utility, Schema dump utilityのみ) このオプションを true に設定すると、OCI Object Storageに出力された全てのオブジェクトに対して事前認証済リクエスト (Object Read PAR) が生成され、すべての事前認証済リクエストURLをリストしたマニフェストファイルが生成されます。Dumpのインポート時には、事前認証済リクエストが利用されるため、OCI Object Storageの利用にはセキュリティ要件の確認が必須。(事前認証済リクエストURLを使用すると、URLを持つ誰もが、リクエストで特定されたターゲットにアクセスできるようになる) また、Object Storage バケットへの接続に使用されるOCI CLIのconfigファイル内のプロファイルで指定されたOCIユーザーが、事前認証済みリクエストの作成者となる。このユーザは PAR_MANAGEパーミッション等、適切なパーミッションが必要(OCIドキュメント参照)。任意のオブジェクトの事前認証済みリクエストURLの作成ができない場合、Dump utilityが停止する。

  • ociParExpireTime: "string"

    (Instance dump utility, Schema dump utilityのみ) ociParManifest オプションが true に設定されているときに生成される事前認証済みリクエストURLの有効期限を設定。既定値は、エクスポート実行日時から1週間後。有効期限は、RFC3339のタイムスタンプの形式で指定する必要があり、書式は、YYYY-MM-DDTHH-MM-SSの後にZ(UTC)の文字、またはローカル時間のUTCオフセットを[+|-]hh:mmで表現したもので、例えば2020-10-01T00:09:51.000+02:00のように指定する (MySQL Shell 8.0.22以降で利用可能)

Oracle MySQL Database Service関連

  • ocimds: [ true | false ]

    (Instance dump utility, Schema dump utilityのみ) MySQL Database Service との互換性のチェックと変更を有効 (true) 可能。デフォルトは false
    このオプションをtrueに設定すると、互換性の問題がないかをチェックし、不適合なSQL文が見つかった場合、例外を発生させる。エクスポート処理を開始する前に、dryRunオプションを使用して問題をすべてリストアップする事が可能。問題を自動的に修正するには、下記のcompatibilityオプションを使用。
    (MySQL Shell 8.0.22 以降では、このオプションを true に設定し、osBucketName オプションを使用して Object Storage バケット名を指定すると、 ociParManifest オプションもデフォルトで true に設定される)

  • compatibility: array of strings

    (Instance dump utility, Schema dump utilityのみ) エクスポート対象となるすべてのテーブルに対して、MySQL Database Serviceとの互換性のために指定された要件を適用し、必要に応じてダンプ・ファイルを変更することが可能。以下のパラメータをカンマ区切りのリストとして指定

    • force_innodb

      CREATE TABLE文を変更して、InnoDBデータベースエンジンをまだ使用していないテーブルに対してもInnoDBデータベースエンジンを使用するように変更を行う

    • strip_definers

      ビュー、ルーチン、イベント、およびトリガから DEFINER節を削除し、これらのオブジェクトがデフォルトの定義者 (スキーマを呼び出すユーザ) で作成されるようにし、ビューおよびルーチンの SQL SECURITY 節を DEFINER の代わりに INVOKER を指定するように変更する。MySQL Database Serviceは、スキーマをロードしているユーザ以外の定義者でこれらのオブジェクトを作成するために特別な権限を必要とする

    • strip_restricted_grants

      MySQL Database Service によって制限されている特定の権限を GRANT ステートメントから削除 (MySQL Shell 8.0.22 以降では、OCI Compute インスタンス上の管理ユーザーアカウントが削除する権限を持っていない場合、このオプションはシステムスキーマ (mysql および sys) のREVOKEステートメントも削除する)

    • strip_role_admin

      GRANT ステートメントから ROLE_ADMIN 権限を削除

    • strip_tablespaces
      GRANT ステートメントから TABLESPACE 句を削除し、すべてのテーブルがデフォルトのテーブルスペースで作成されるように変更

Dump Loading Utilityのオプション変数

基本

  • dryRun:[true | false]

    コマンドを検証を行うが、インポートは行わない
    デフォルトはfalse

  • progressFile: "string"

    インポートの進行状況を出力するログファイルの出力ディレクトリと出力ファイル名を指定。progressFileを空の文字列に設定すると、進行状況の追跡が無効になり、Dump Loading Utilityを途中終了した場合に再開できない。デフォルトのファイル名はload-progress.server_uuid.jsonで、Dumpディレクトリに作成

  • showProgress: [ true | false ]

    インポートの進行状況情報の表示(true)または非表示(false)を指定。デフォルトは、MySQL Shellがインタラクティブモードの場合など、stdoutがターミナル(tty)の場合はtrue、それ以外の場合はfalse

  • resetProgress:[true | false]

    このオプションをtrueに設定すると、進行状況がリセットされ、インポートが最初から再開する。デフォルトはfalse

  • waitDumpTimeout: int

    エクスポート先にアップロードされたすべてのチャンクが処理された後、ユーティリティがデータの追加を待機するタイムアウト(秒単位)を指定して、同時読み込みを有効化する。これにより、ユーティリティは、エクスポート作業中に出力されたダンプをインポートすることができる。デフォルト値は0で、チャンクがアップロードされた時点でダンプを完了したものと判断し、追加のデータを待たないことを意味する

  • ignoreExistingObjects: [ true | false ]

    MySQLインスタンスのターゲットスキーマに既に存在するオブジェクトが含まれていてもインポートを継続する。デフォルトはfalse

  • ignoreVersion: [ true | false ]

    データをダンプしたMySQLインスタンスのメジャーバージョン番号と、データをアップロードするMySQLインスタンスのメジャーバージョン番号が異なっていても、ダンプをインポートを実行する。デフォルトは false で、メジャーバージョン番号が異なる場合はエラーが発生してインポートが進まないことを意味する

  • updateGtidSet: [ off | append | replace ]

    ダンプメタデータに記録されているソースMySQLインスタンスのgtid_executed GTIDセットを、ターゲットMySQLインスタンスのgtid_purged GTIDセットに適用する。このオプションは MySQL Shell 8.0.22 から利用可能だが、(2020年11月時点で) MySQL DB System ではサポートされていない。デフォルトはoff

  • skipBinlog: [ true | false ]

    SET sql_log_bin=0ステートメントを発行することで、インポート中にユーティリティが使用したセッションのターゲットMySQLインスタンスのバイナリロギングをスキップする。デフォルトはfalseなので、デフォルトではバイナリロギングがアクティブになります。このオプションはOracle MySQL Database Serviceでは使用されない

  • loadIndexes: [ true | false ]

    テーブルのセカンダリインデックスを作成する(true)か、作成しない(false)かを指定。既定値は true

  • deferTableIndexes: [ off | fulltext | all ]

    セカンダリインデックスの作成を、テーブルデータがロードされるまで延期する。これにより、読み込み時間を短縮することが可能。 offは、すべてのインデックスがテーブル読み込み中に作成されることを意味し、allはすべてのセカンダリインデックスを遅延させ、デフォルトのfulltextは、フルテキストインデックスのみを遅延させる。テーブルのロード中にプライマリ インデックスのみを作成し、(MySQL Shell 8.0.21では、オートインクリメント値を含む一意のキーカラムがある場合はallを設定しないこと)

  • analyzeTables: [ off | on | histogram ]

    onはすべてのテーブルを解析し、histogramはDumpに保存されたヒストグラム情報を持つテーブルのみを解析する。デフォルトはoff

  • characterSet: "string"

    ターゲットMySQLインスタンスへのインポートに使用する文字セット。デフォルトは、Instance dump utility、Schema dump utility、またはTable dump utilityによってダンプが作成されたときに使用されたダンプ・メタデータで指定された文字セットで、デフォルトでは utf8mb4 を使用

  • schema: "string"

    Table dump utilityによってロードする対象となる既存のターゲットスキーマ。このオプションが指定されていない場合、グローバルシェルセッションの現在のスキーマがターゲットスキーマとして使用される

  • excludeSchemas: array of strings

    指定されたスキーマをインポートから除外。information_schema、mysql、ndbinfo、performance_schema、およびsysスキーマは、Instance dump utilityによって作成されるダンプから常に除外される

  • includeSchemas: array of strings

    ロード対象となるスキーマ名を指定。includeSchemasexcludeSchemasの両方に重複するスキーマが存在する場合は除外される

  • excludeTables: array of strings

    インポートから除外するテーブル名を指定。有効なスキーマ名で修飾し、必要に応じて引用符で囲む必要。mysql.apply_status、mysql.general_log、mysql.schema、およびmysql.slow_logテーブルのデータはSchema dump utilityによって常に除外される

  • includeTables: array of strings

    ロード対象となるテーブル名を指定。includeTablesexcludeTablesの両方に重複するテーブルが存在する場合は除外される

  • loadDdl: [ true | false ]

    trueに設定すると、ダンプから DDL ファイルのみをインポートし、データはインポートしない。既定値はfalse

  • loadData: [ true | false ]

    trueに設定すると、ダンプからデータ ファイルのみをインポートし、DDLファイルをインポートから除外。既定値はfalse

  • loadUsers: [ true | false ]

    ターゲットのMySQLインスタンスにユーザとそのrolesおよびgrantsをインポートする(true)か、インポートしない(false)かを指定。デフォルトはfalseなので、デフォルトではユーザはインポートされません。MySQL Shell 8.0.21では、MySQL DB システムにユーザをインポートしようとすると、ダンプファイルにルートユーザアカウントまたは別の制限付きユーザアカウント名が存在するとインポートに失敗するため、MySQL Database ServiceのユーザのインポートはMySQL Shell 8.0.21ではサポートされていない

  • excludeUsers: array of strings

    指定されたユーザアカウントをインポートから除外。このオプションは MySQL Shell 8.0.22 から利用可能で、MySQL DB システムへのインポートを受け付けないユーザアカウントや、ターゲットの MySQL インスタンス上に既に存在するか不要なユーザアカウントを除外するために使用される。ユーザ名とホスト名で定義されたアカウントの場合は "'user_name'@'host_name'"、ユーザ名のみで定義されたアカウントの場合は "'user_name'" という形式で各ユーザアカウントの文字列を指定 (これは "'user_name'@'%'" と同じ)

  • includeUsers: array of strings

    指定されたユーザーアカウントのみをインポートに含める。各ユーザアカウントの文字列は、excludeUsersオプションの場合と同様に指定。このオプションは MySQL Shell 8.0.22 から利用可能で、ターゲットの MySQL インスタンスで少数のユーザアカウントのみが必要な場合に excludeUsers の代替として使用可能。

パフォーマンス・チューニング関連

  • threads: int

    データのチャンクをターゲットMySQLインスタンスにロードするために使用する並列スレッドの数。並列数を増やすことでネットワーク帯域を効率的に利用できるが、CPU使用率は増加する。デフォルトは4

OCI Object Storage関連

  • osBucketName: "string"

    ダンプファイルが配置されているOCI ObjectStorageバケットの名前
    デフォルトでは、OCI CLI構成ファイルのDEFAULTプロファイルを使用

  • osNamespace: "string"

    osBucketNameで指定されたObjectStorageバケットが配置されているOCIのネームスペース。 Object Storageバケットのネームスペースは、OCIコンソールのバケット詳細ページの「バケット情報」タブに表示される。または、OCI CLIを使用して取得

  • ociConfigFile: "string"

    デフォルトの~/.oci/config以外のOCI CLIのcofigファイルを利用する場合にPATHを指定

  • ociProfile: "string"

    OCI CLI構成ファイルのDEFAULTプロファイルを以外のプロファイルを使用する際にプロファイル名を指定

関連情報

MySQL Technology Café #7 MySQL Shellを使ってみよう!

Oracle Cloud Infrastructureドキュメント オブジェクト・ストレージの概要

Oracle Cloud Infrastructure Documentation MySQL Database Importing and Exporting Databases

MySQL Shell 8.0 7.5 Instance Dump Utility, Schema Dump Utility, and Table Dump Utility

MySQL Shell 8.0 7.6 Dump Loading Utility

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

AWS RDSにローカルから接続できない

エラー内容

データの検証のため、RDSにローカルのwindowsから接続しようと試みました。

mysql -u root -p -P 3306 -h <RDS>

すると・・・

ERROR 2003 (HY000): Can't connect to MySQL server on '<RDS>' (10060)

ncも試してみる。

nc <RDS>
nc: can't connect to remote host (<RDS IP>)

ncもダメ・・・

解決方法

RDSインスタンスの「パブリックアクセス可能」を「あり」にする
e124141.PNG

参考:https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-connectivity-instance-subnet-vpc/

これでローカルから接続できるようになりました。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQL Shellを利用したクラウドバックアップ/リストア

はじめに

MySQL ShellのDump Utility / Dump Loading Utilityを利用してローカル環境で稼働しているMySQLインスタンスからOracle Cloud Infrastructure (OCI) Object Storageへダンプバックアップの取得、データロードによるリストア手順の検証を行いました。ダンプバックアップの取得先となるOCI Object StorageはOracle Cloudで利用可能な安価なオブジェクトストレージで、Amazon S3、Azure BLOBに相当するクラウド・ストレージ・サービスになります。Oracle Cloudでは東京リージョンの他に大阪リージョンを利用できるため、容易にデータ保護・災害対策を実現することができます。本記事中のリストアの検証では、ダンプバックアップの取得を行ったソースMySQLインスタンスへのリストア以外に、異なるローカル環境で稼働している上位バージョンのMySQLインスタンスへのリストア手順も確認しました。検証の構成イメージは下記を確認してください。

20-11-21-00-19-32.png

検証環境

  • macOS 10.15.7 Catalina : クライアント環境
  • MySQL 5.7.32 : クライアント上で稼働・ソース/ターゲットMySQLインスタンスとして利用
  • MySQL 8.0.22 : クライアント上で稼働・リストアを行うターゲットMySQLインスタンスとして利用
  • MySQL Shell 8.0.22 : クライアント上で利用

事前準備

前提条件

  • MySQのバージョンが5.7以上であること
  • エクスポート先にOCI Object Storageを利用する場合は、MySQL Shellを実行する環境からOCI CLIを利用してOCI Object Storageバケット (インターネット) に直接アクセスできること
  • OCI CLIでアクセスするOCIユーザーはOCI Object Storageを操作する権限を持つこと
  • OCI Object Storageの事前認証済リクエストが利用されるため、利用するデータベースに格納されているデータのセキュリティ要件の確認必須 1

詳細は別記事のMySQL Shell Dump Utilityを使用する前提条件の項をご確認ください

OCI CLIの準備

OCI CLIのインストールについては下記のドキュメント・チュートリアル・Qiita記事をご参照ください。

Oracle Cloud Infrastructureドキュメント コマンドライン・インタフェース(CLI)

チュートリアル : Oracle Cloud Infrastructure を使ってみよう コマンドライン(CLI)でOCIを操作する - Oracle Cloud Infrastructureアドバンスド

Oracle Cloud : コマンド・ライン・インタフェース(CLI) をインストールしてみた

ここでハマったのはOCI CLIの構成ファイルであるconfig内の記述、API KeyのファイルパスはFull PATHで記載しないとMySQL Shellでエラーが発生しました。

MySQL Shellのインストール

公式のダウンロードサイトよりインストーラをダウンロードして実行してください。
Linux環境の場合は 前回の記事 も参照ください。

バックアップ先となるOCI Object Storage バケットの作成

OCIダッシュボードメニューから、コア・インフラストラクチャ>オブジェクト・ストレージ>オブジェクト・ストレージにアクセスし、コンソール左下のコンパートメント・セレクタメニューより作業するコンパートメントを選択します。その後、バケットの作成ボタンを選択し、下記の設定項目入力を入力してバケットを作成します。
- バケット名 : 任意 (MySQLBackup)
- ストレージ層 : 標準
- オブジェクト・イベント : 任意 (設定しない)
- オブジェクト・バージョニング : 任意 (設定しない)
- 暗号化 : ORACLE管理キーを使用した暗号化

バケットが作成されたらバケット名を選択し、バケットの詳細画面からネームスペースの値をメモしておいてください。
20-11-20-18-15-52.png

MySQLServerのObject Storageへのバックアップの実行

バックアップ対象となるtestスキーマの準備

ローカルで稼働しているMySQLインスタンスにMySQL Shellから接続します。下記では、MySQL Shellからアクセスし、SQLモードに移行、バックアップ対象となるtestスキーマを作成後、MySQL Shell Dump Utilityを実行するために再度Javascriptモードに戻っています。

mysqlsh -uroot -p
MySQL Shell 8.0.22

Copyright (c) 2016, 2020, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 5
Server version: 5.7.32 Homebrew
No default schema selected; type \use <schema> to set one.

 MySQL  localhost  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost  SQL > CREATE DATABASE test;
Query OK, 1 row affected (0.0014 sec)

 MySQL  localhost  SQL > USE test;
Default schema set to `test`.
Fetching table and column names from `test` for auto-completion... Press ^C to stop.

 MySQL  localhost  test  SQL > CREATE TABLE users (id INT AUTO_INCREMENT, usr_name TEXT, PRIMARY KEY (id));
Query OK, 0 rows affected (0.0173 sec)

 MySQL  localhost  test  SQL > INSERT INTO users(usr_name) VALUES ('hoge'), ('hogehoge'), ('hogehogehoge');
Query OK, 3 rows affected (0.0072 sec)
Records: 3  Duplicates: 0  Warnings: 0

 MySQL  localhost  test  SQL > CREATE TABLE revenue (id INT AUTO_INCREMENT, usr_id INT, revenue INT, INDEX usr_index(usr_id), FOREIGN KEY usr_id (usr_id) references users(id), PRIMARY KEY (id));
Query OK, 0 rows affected (0.0178 sec)

 MySQL  localhost  test  SQL > INSERT INTO revenue(usr_id, revenue) VALUES (1,10000), (2, 5000), (1,1000);
Query OK, 3 rows affected (0.0008 sec)
Records: 3  Duplicates: 0  Warnings: 0

 MySQL  localhost  test  SQL > SELECT users.id AS 'ID', users.usr_name AS 'CUSTOMER NAME', SUM(revenue.revenue) AS 'REVENUE' FROM users LEFT OUTER JOIN revenue on users.id = revenue.usr_id GROUP BY id;
+----+---------------+---------+
| ID | CUSTOMER NAME | REVENUE |
+----+---------------+---------+
|  1 | hoge          |   11000 |
|  2 | hogehoge      |    5000 |
|  3 | hogehogehoge  |    NULL |
+----+---------------+---------+
3 rows in set (0.0007 sec)

 MySQL  localhost  test  SQL > SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| revenue        |
| users          |
+----------------+
2 rows in set (0.0004 sec)

 MySQL  localhost  test  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.0008 sec)

 MySQL  localhost  test  SQL > \js
Switching to JavaScript mode...

 MySQL  localhost  test  JS > 

Instance Dump Utilityを利用したObject Storageへのバックアップ

次にInstance Dump Utilityを利用してOCI Object Storageにバックアップを取得します。利用したInstance Dump Utilityのコマンドは下記です。

util.dumpInstance("dumpinstance_onp2onp", {osBucketName: "MySQLBackup", osNamespace: "MyNamespace", threads: 4, dryRun: true})

ここで"dumpinstance_onp2onp"はObject Storage上オブジェクトを識別するための接頭辞、osBucketName: "MySQLBackup"上記手順で確認したObject Storageバケットが存在するネームスペース、threads:エクスポートを実行するスレッド数、dryRun: trueは実行前にコマンドの検証を行うことを意味しています。
はじめにdryRunを実施し、Utilityのコマンドを検証、2回目の実行で実際にバックアップの取得を行っています。

 MySQL  localhost  test  JS > util.dumpInstance("dumpinstance_onp2onp", {osBucketName: "MySQLBackup", osNamespace: "MyNamespace", threads: 4, dryRun: true})
Acquiring global read lock
Global read lock acquired
All transactions have been started
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Global read lock has been released
Writing global DDL files
Writing users DDL
Writing DDL for schema `test`
Writing DDL for table `test`.`revenue`
Writing DDL for table `test`.`users`
Preparing data dump for table `test`.`revenue`
Data dump for table `test`.`revenue` will be chunked using column `id`
Preparing data dump for table `test`.`users`
Data dump for table `test`.`users` will be chunked using column `id`

 MySQL  localhost  test  JS > util.dumpInstance("dumpinstance_onp2onp", {osBucketName: "MySQLBackup", osNamespace: "MyNamespace", threads: 4})
Acquiring global read lock
Global read lock acquired
All transactions have been started
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Global read lock has been released
Writing global DDL files
Writing users DDL
Writing DDL for schema `test`
Writing DDL for table `test`.`users`
Writing DDL for table `test`.`revenue`
Preparing data dump for table `test`.`revenue`
Data dump for table `test`.`revenue` will be chunked using column `id`
Preparing data dump for table `test`.`users`
Data dump for table `test`.`users` will be chunked using column `id`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `test`.`revenue` will be written to 2 files
Data dump for table `test`.`users` will be written to 2 files
4 thds dumping - 33% (2 rows / ~6 rows), 1.00 row/s, 17.00 B/s uncompressed, 0.001 thds dumping - 100% (6 rows / ~6 rows), 1.00 row/s, 17.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:01s
Schemas dumped: 1
Tables dumped: 2
Uncompressed data size: 61 bytes
Compressed data size: 0 bytes
Compression ratio: 61.0
Rows written: 6 
Bytes written: 0 bytes
Average uncompressed throughput: 51.87 B/s
Average compressed throughput: 0.00 B/s

MySQL  localhost  test  JS > 

OCIコンソールのObject Storageバケットの詳細画面からも取得されたダンプファイルのオブジェクトを確認することができます。
20-11-20-22-57-39.png

Object Storage上のバックアップのリストア

バックアップ取得元と同一のMySQLServerにデータロード

バックアップを取得したMySQLインスタンスにInstance Dump Utilityで取得したダンプファイルをロードします。
下記では、testスキーマを削除した後、Dump Loading Utilityを用いてデータロードを行い、元に戻っていることを確認しました

 MySQL  localhost  test  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost  test  SQL > DROP DATABASE test;
Query OK, 2 rows affected (0.0109 sec)

 MySQL  localhost  test  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0043 sec)

 MySQL  localhost  test  SQL > \js
Switching to JavaScript mode...

 MySQL  localhost  JS > util.loadDump("dumpinstance_onp2onp",{osBucketName:"MySQLBackup",osNamespace:"MyNamespace",threads:4})
Loading DDL and Data from OCI ObjectStorage bucket=MySQLBackup, prefix='dumpinstance_onp2onp' using 4 threads.
Opening dump...
Target is MySQL 5.7.32. Dump was produced from MySQL 5.7.32
Fetching dump data from remote location...
Fetching 2 table metadata files for schema `test`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `test`
[Worker003] Executing DDL script for `test`.`revenue`
[Worker000] Executing DDL script for `test`.`users`
[Worker002] test@users@0.tsv.zst: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] test@revenue@0.tsv.zst: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] test@users@@1.tsv.zst: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] test@revenue@@1.tsv.zst: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL                                         

4 chunks (6 rows, 61 bytes) for 2 tables in 1 schemas were loaded in 2 sec (avg throughput 30.50 B/s)
0 warnings were reported during the load.

 MySQL  localhost  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  localhost  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.0006 sec)
 MySQL  localhost  SQL > USE test;
Default schema set to `test`.
Fetching table and column names from `test` for auto-completion... Press ^C to stop.

 MySQL  localhost  test  SQL > SELECT users.id AS 'ID', users.usr_name AS 'CUSTOMER NAME', SUM(revenue.revenue) AS 'REVENUE' FROM users LEFT OUTER JOIN revenue on users.id = revenue.usr_id GROUP BY id;
+----+---------------+---------+
| ID | CUSTOMER NAME | REVENUE |
+----+---------------+---------+
|  1 | hoge          |   11000 |
|  2 | hogehoge      |    5000 |
|  3 | hogehogehoge  |    NULL |
+----+---------------+---------+
3 rows in set (0.0009 sec)

 MySQL  localhost  test  SQL > 

testスキーマ、usersテーブルとrevenueテーブルが元に戻っていることを確認できました。

バックアップ取得元とは異なるMySQL 8.0インスタンスにデータロード

バックアップを取得したMySQLインスタンスとは異なるMySQLインスタンスにOCI Object Storage上に取得したダンプファイルをDump Loading Utilityでロードします。別環境で用意したMySQL ServerはMySQL 8.0.22であり、ダンプファイルを取得したMySQL Serverの上位バージョンとなっています。

 MySQL  localhost:33060+ ssl  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost:33060+ ssl  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0010 sec)

 MySQL  localhost:33060+ ssl  SQL > \js
Switching to JavaScript mode...

 MySQL  localhost:33060+ ssl  JS > util.loadDump("dumpinstance_onp2onp",{osBucketName:"MySQLBackup",osNamespace:"MyNamespace",threads:4,ignoreVersion: true})
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.loadDump: local_infile disabled in server (RuntimeError)

 MySQL  localhost:33060+ ssl  JS > 

ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.と怒られました。local-infileパラメータをonに設定する必要があるみたいです。SQLモードに移行後、'local-infile'を有効にして再度Dump Loading Utilityを用いてデータロードを行いました。

  MySQL  localhost:33060+ ssl  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost:33060+ ssl  SQL > SET GLOBAL local_infile=on; 
Query OK, 0 rows affected (0.0003 sec)

 MySQL  localhost:33060+ ssl  SQL > \js
Switching to JavaScript mode...

 MySQL  localhost:33060+ ssl  JS > util.loadDump("dumpinstance_onp2onp",{osBucketName:"MySQLBackup",osNamespace:"MyNamespace",threads:4,ignoreVersion: true})
Loading DDL and Data from OCI ObjectStorage bucket=MySQLBackup, prefix='dumpinstance_onp2onp' using 4 threads.
Opening dump...
Target is MySQL 8.0.22. Dump was produced from MySQL 5.7.32
WARNING: Destination MySQL version is newer than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. 'ignoreVersion' option is enabled, so loading anyway.
Fetching dump data from remote location...
Fetching 2 table metadata files for schema `test`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `test`
[Worker003] Executing DDL script for `test`.`revenue`
[Worker001] Executing DDL script for `test`.`users`
[Worker002] test@users@0.tsv.zst: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] test@revenue@0.tsv.zst: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] test@users@@1.tsv.zst: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] test@revenue@@1.tsv.zst: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL                                          

4 chunks (6 rows, 61 bytes) for 2 tables in 1 schemas were loaded in 1 sec (avg throughput 61.00 B/s)
0 warnings were reported during the load.

 MySQL  localhost:33060+ ssl  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost:33060+ ssl  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.0009 sec)

 MySQL  localhost:33060+ ssl  SQL > USE test;
Default schema set to `test`.
Fetching table and column names from `test` for auto-completion... Press ^C to stop.

 MySQL  localhost:33060+ ssl  test  SQL > SELECT users.id AS 'ID', users.usr_name AS 'CUSTOMER NAME', SUM(revenue.revenue) AS 'REVENUE' FROM users LEFT OUTER JOIN revenue on users.id = revenue.usr_id GROUP BY id;
+----+---------------+---------+
| ID | CUSTOMER NAME | REVENUE |
+----+---------------+---------+
|  1 | hoge          |   11000 |
|  2 | hogehoge      |    5000 |
|  3 | hogehogehoge  |    NULL |
+----+---------------+---------+
3 rows in set (0.0011 sec)

元のMySQLインスタンスとは別のインスタンスにデータをロードできることを確認できました。

関連情報

MySQL Technology Café #7 MySQL Shellを使ってみよう!

Oracle Cloud Infrastructureドキュメント オブジェクト・ストレージの概要

Oracle Cloud Infrastructure Documentation MySQL Database Importing and Exporting Databases

MySQL Shell 8.0 7.5 Instance Dump Utility, Schema Dump Utility, and Table Dump Utility

MySQL Shell 8.0 7.6 Dump Loading Utility

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQLからPostgresの移行時にやったこと(AWS Aurora)

はじめに

今携わっているWebアプリケーションではMySQL(AWS Aurora)を使用していますが、昨今のユーザー数増加により少しずつ問題が起き始めています。
元々Indexの作りがよくないこともあるのですが、PostgreSQL(AWS Aurora)に移行したらどうだろう?という検証を行った結果、数倍どころの問題じゃないぐらいの性能差が出たため、移行作業を行うこととしました。
今回はその際にやったことをまとめてみました。

環境

移行前

AWS Aurora MySQL 5.7

移行後

AWS Aurora PostgreSQL 11.8

DBの移行方法

AWS DMSという移行を簡単にできるサービスがあるため、それを使用して移行しました。
一度きりの移行も継続的なレプリケーションも対応しているため、移行がものすごく楽になりました。

移行時にテストしてクエリを修正した際のポイント

MySQLとPostgreSQLではクエリが少し変わります。ここではその違いによって変更した点を記載していきます。(これがメインコンテンツ)

シングルクォーテーションとダブルクォーテーション

文字列を囲む際ですが、MySQLは特にシングルとダブルどちらでも問題ありません。
しかし、PostgreSQLはシングルクォーテーションしか受け付けません。
なので、その違いがあれば吸収してあげる必要があります。
こういう時のためにシングルクォーテーションに統一しておくというのはアリかと思います。検索でどうこう出来る修正ではないため、結構修正は面倒くさかったです。

# MYSQL
name = "hogehoge"
# PostgreSQL
name = 'hogehoge'

関数の違い

例えばMySQLのifnullはPostgreSQLではCOALESCEといった違いがそこそこあります。
これも1つ1つ調べるのも手間なので、動かしてエラーになった箇所を書き換えるというやり方で修正しました。
また、同じ関数があった場合でも引数の型の問題でエラーになることもあります。
例えばlpad関数は、MySQLでは数字でも問題なく動作してくれますが、PostGreSQLでは文字を渡して上げなければいけないといった違いが存在します。
分かりづらいですが、このような違いによるエラーはかなり発生しやすいのと普段から両方に対応した書き方はしづらい箇所なので、移行時の手間として許容するしかなさそうです。こういう処理を全てアプリケーション側でやれば移行時の手間は無くなりますが、それはそれで手間です・・・

# MySQL
lpad(id, 4, "0")
# PostGreSQL
lpad(cast(id as character varying), 4, '0')

日付の扱い方について

一番大きく修正したのは日付(正確には時刻)周りです。
ここら辺はMySQLの方が便利になっていて、例えば日付の差分を取得する方法は以下となります。

# MySQL
DATEDIFF(NOW(), created_at)
# PostGreSQL
extract(epoch from (now() - created_at)) / (60 * 60 * 24)

また、時刻に秒とか分を足すというロジックにも違いがあります。
例えば秒を足すというロジックには以下の違いがあります。

# MySQL
DATE_ADD(time, INTERVAL value SECOND)
# PostGreSQL
time + make_interval(secs => value)

こうした違いはどうしても存在するため、これも手間として許容する必要があります。

group byの違い

MySQLは結果に一意性があれば良いのですが、PostGreSQLはクエリの段階で一意性を求めてくるようです。
なので、sumだったりで集計する箇所かつjoin絡むような少し複雑なクエリを書いている箇所でgroup byするカラム不足であるエラーが多発しました。
単純なクエリであれば特に問題ありませんが、joinが絡むとこの問題が多発します。

例としてhogehogemembersというテーブルをjoinしてデータを取得したいとします。
その際のクエリを書きましたが、このクエリはMySQLだと動作しますが、PostGreSQLではエラーになってしまいます。
PostGreSQLでは、このエラーとなったmembers.nameカラムもgroup byに含める必要があるのです。

# MySQL
select
  hogehoge.member_id,
  members.name,
  hogehoge.date,
  sum(hogehoge.value)
from
  hogehoge
  INNER JOIN members on members.id = hogehoge.member_id
group by hogehoge.member_id, hogehoge.date

# エラー内容
ERROR:  column "members.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:   members.name,
          ^
SQL state: 42803
Character: 47
# PostGreSQL
select
  hogehoge.member_id,
  members.name,
  hogehoge.date,
  sum(hogehoge.value)
from
  hogehoge
  INNER JOIN members on members.id = hogehoge.member_id
group by hogehoge.member_id, hogehoge.date

Railsの補足

Railsでfirstでデータを取得するとクエリにorder by {primary key}が付与されます。
これを今回のようなケースで書いているとこの{primary key}group byに含める必要が出てくるため、自分で書いたクエリ部分は追加したけどエラーになるというような状況が出てきてしまいます。
ご注意ください。

selectの返却順

PostgreSQLはMySQLと違って計算過程で見つけた順番でselectした結果を返却してくるため、思ってない順番で返却されることがあります。
まとめて取得するというような場合はきちんとsortしてあげるようにしないと、思った通りの取得順番にはならないため注意が必要です。

終わりに

MySQL5.7のスタートってもう7年前なんですね・・・

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

プライベートIPを持ったCloud SQLインスタンスのIPレンジを予め決める方法

Cloud SQLのインスタンスはデフォルトでパブリックIPも持った状態で立ち上がりますが、プライベートIPを割り当てるように設定することもできます。
しかし、ドキュメントのチュートリアルに従ってコマンドを実行したり、GUIからぽちぽちすると気づいたらプライベートIPがランダムに割り振られて起動していることもあります。
GCPのみを利用する場合はこれでも問題なることは少ないですが、オンプレやawsとの間でVPNで接続する場合にはCIDRの重複を起こす恐れがあります。
この記事では、インスタンスのCIDRを予め指定する方法を紹介します。

プライベートIPを持たせる仕組み

こちらのドキュメントのネットワーク図にあるように、実はプライベートIPをもったインスタンスは自分たちのプロジェクトのVPC内には存在しません。
Googleによって管理されているVPC内にインスタンスが存在し、そのVPCとの間でVPC Peeringが設定されています。

https://cloud.google.com/sql/docs/mysql/private-ip

では、このGoogle管理のVPC内のサブネットのCIDRはどのようにして決まるのでしょうか?
それを理解するためにはPrivate services accessの仕組みを理解する必要があります。

https://cloud.google.com/vpc/docs/private-access-options#service-networking

Private service accessは本来ならパブリックIPのみを持つGCPのリソースに対してプライベートIPを割り当て、VPC内からプライベートIPで接続できるようにするサービスです。
Cloud SQLのプライベートIP機能はこの機能の上に成り立っています。
また、Cloud SQLの他にMemoryStoreなどのプライベートIP機能もこの仕組を使っています。

そして、このPrivate service accessはGoogle管理のVPCに対して割り当てるCIDRを予め決める機能があります。
それがallocated IP address ranges機能です。
https://cloud.google.com/vpc/docs/configure-private-services-access

この機能でGoogle側に割り当てたCIDRは予約されたCIDRとなり、それに被るようなサブネットを作成することが不可能になります。
以下のresource定義で 192.168.0.0/20 のCIDRを予約します。

resource "google_compute_global_address" "private_ip_alloc_google_managed_service" {
  name          = "google-managed-services-<VPCの名前>"
  network       = google_compute_network.hoge_network.id
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"  
  address       = "192.168.0.0"
  prefix_length = 20
}

そして、予約したCIDRをGoogleに割り当てます。

resource "google_service_networking_connection" "private_service_connection_google_managed_service" {
  network                 = google_compute_network.hoge_network.id
  service                 = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [google_compute_global_address.private_ip_alloc_google_managed_service.name]
}

Googleはこの割当てられたCIDRの中からサブネットを切り出し、その中にインスタンスを配置していきます。
割り当てるCIDRの広さには十分に気を使う必要があります。
例えばCloud SQLを使用する際にはリージョン毎に /24 のCIDRのサブネットを切り出すため、マルチリージョン構成を取る場合は広めに割り当てるのが良いです。

また、将来的に他のネットワークとの接続をしない場合でも、CIDRを予約してから割り当てたほうが無難です。
以下に書かれているように、Dockerがネットワークをブリッジするために 172.17.0.0/16 をCloud SQLのために使うことはできません。

The IP range 172.17.0.0/16 is reserved for the Docker bridge network. Any Cloud SQL instances created with an IP in that range will be unreachable. Connections from any IP within that range to Cloud SQL instances using private IP will fail.

https://cloud.google.com/sql/docs/mysql/private-ip#network_requirements

一方で、Private service access機能によって自動的に作られるサブネットはランダムであるため、運悪くこのCIDRになる可能性もあります。
予めCIDRを予約し、それをGoogle側に割り当てることでこの悲劇を防ぐことができます。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQLで"Can't connect to local MySQL server through..."って出た時の対処法

"Can't connect to local MySQL server through..."って出た時の対処法

環境

  • MacOS : Catalina - 10.15.7
  • MySQL : 8.0.22 for osx10.15

起きたこと

1、Djangoのサーバが動かない


とある用事でMacBookを再起動させたあとにDjango触ろうとした時のお話。

xxx@yyy $ python manage.py runserver

を叩いてローカルにサーバを立てようとしたら、

django.db.utils.OperationalError: (1130, "Host '10.0.2.2' is not allowed to connect to this MySQL server")

って出てきた。書いてある通り、MySQL関係のエラーっぽい。


2、MySQLに入れない


MySQLを確認したいので下記コマンドを打つが、

xxx@yyy $ mysql -u root -p

って入力したら、

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

とエラーを吐かれてMySQLが使えなかった。

原因

結果として、原因は再起動と同時にMySQLのサーバが止まってしまっていたこと。

そこで、

xxx@yyy $ mysql.server restart

を叩くことで解決した。

xxx@yyy $ mysql -u root -p

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

いけた。

比較的簡単に解決してよかった。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む