レコード毎のINSERT文になるようにmysqldumpでダンプする方法

先日、WordPressで作成したサイトのサーバ移設作業をしていたのですが、mysqldumpで作成したダンプファイルを移設先サーバのMySQLにロードしようとしたところ、「Got a packet bigger than ‘max_allowed_packet’ bytes」というエラーが出ました。

解決策を調べたところ、MySQLサーバの「max_allowed_packet」という値を変更する方法が見つかったのですが、今回その方法は使えませんでした。mysqldumpで生成したダンプファイルのエラーになった箇所を調べたところ、大きいテーブルのすべてのレコードをひとつの「INSERT INTO」文によって挿入するようになっていたため、この文のサイズが「max_allowed_packet」を超えているのがエラーの原因であることがわかりました。

そこで、mysqldumpでダンプファイルを生成するときに、レコード毎のINSERT文にする方法を調べました。具体的には、

のように生成されるのを、

のように生成されるようにする方法です。

その方法ですが、mysqldumpを実行するときに、「--skip-extended-insert」というオプションを付けるだけです。例えば、以下のように実行します。

このように生成したダンプファイルの難点は、ロードするのに時間がかかることですが、場面により、適宜使っていこうと思います。

MySQLのTEXT型データで大文字と小文字を区別できるようにするには

既に使用を開始しているシステムで、TEXT型で定義しているデータの大文字と小文字を区別しなければならなくなりました。その時に調べた内容のまとめです。

まず、非バイナリ文字列とバイナリ文字列の性質の比較です。

文字列の種類 文字列検索 大文字/小文字の区別
非バイナリ文字列 CHAR、VARCHAR、TEXT 比較オペランドの照合順序
を使用
なし
バイナリ文字列 BINARY、VARBINARY、BLOB オペランドのバイトの数値
を使用
あり

参照:B.5.5.1 文字列検索での大文字/小文字の区別

バイナリ文字列に区分されている型を使用していれば問題なかったのですが、既に使用を開始していたため、TEXT型のまま大文字と小文字を区別できるようにしました。

まず前提として、「exmtable01」という名前のテーブルがあるとします。

exmtable01のテーブル定義情報は以下のようになっています。

valueフィールドの型を「TEXT BINARY」に変更します。

そうすると、exmtable01のテーブル定義は以下のように変更されます。

以上の変更により、TEXT型でも大文字と小文字を区別することができるようになりました。

また、テーブルを作成する時に「TEXT BINARY」を指定すると同様の定義となります。

phpMyAdminを使用している場合は、変更したいフィールドを選択し属性を「binary」に指定すれば、同様の変更を行うことができます。

MySQLをインストールする

Python+MySQLの組み合わせでCGIシステムを作成するために、MySQLとPythonとのインターフェースをインストールします。MySQLの文字コードはutf8とします。

  • mysql-serverパッケージをインストールする。MySQLのrootユーザのパスワードを設定する。
  • /etc/mysql/my.cnfを変更し、文字コードを設定する。
  • PythonとMySQLのインターフェース関連のpython-mysqldbパッケージをインストールする。Pythonで、import MySQLdbが実行できれば準備完了です。

MySQLのテーブルを作成するまでの基本操作まとめ

MySQLをインストール後、テーブルを作成するまでに行なう基本操作のまとめです。
例として、下記の内容でデータベースおよびテーブルを作成するとします。

データベース名 exmdb
ユーザ名 exmuser
ユーザ:exmuserのパスワード exmDbPassword
テーブル exmtable
テーブルの作成等を記述したファイル名 init.sql
  • rootでmysqlを実行
  • データベースを作成(文字コードをutf8に指定)
  • 作成したデータベースを確認
  • データベースの一覧表示
  • データベースを削除
  • ユーザを作成
  • ユーザの一覧表示
  • ユーザを削除
  • 作成したデータベースに作成したユーザを指定して接続
  • テーブルを作成
  • テーブルの一覧表示
  • ファイルを読み込み、テーブル、インデックス等を作成

MySQLのload data infile文で文字化けする場合の対策

システムおよびデータベースの文字コードはすべて utf-8 を使用する前提とします。

既存のシステムからデータを移行するために「load data infile」文を使用すると、読み込んだデータが文字化けしてしまう場合があります。

原因は、「character_set_database」が utf-8 に設定されていないためです。
「load data infile」文は「character_set_database」の設定に従います。

「character_set_database」の設定を確認します。

以上のように出力された場合、「character_set_database」の設定は「latin1」となっているため、「utf8」に変更します。

もう一度「character_set_database」の設定を確認します。

「character_set_database」が「utf8」に変更されたため、文字化けを回避できます。

関連記事:PostgreSQL(EUC_JP)からMySQL(UTF-8)へのデータ移行

MySQLでtext型の列にインデックスを作成する

MySQLでtext型の列にインデックスを作成する場合、サイズを指定する必要があります。

例えば、以下の内容のテーブルを作成します。

サイズの指定をせずにインデックスを作成するとエラーになります。

そこでサイズを指定します。
以下の指定では、name列の最初の100文字を使用したインデックスが作成されます。

データをダンプ、リストアするには(02)

最高気温予想36度、今日も伊勢崎は暑いです。
ここ数日は、暑さ+WordPressと戦っております。

以前、データをダンプ、リストアするには(01)を書きました。

その時は、ホスティングサービス上にデータをリストアしなければならなかったのですが、どうしてもダンプファイルをそのまま読み込むことができず、苦肉の策としてダンプファイルからINSERT文だけ取り出して何とかリストアしました。

かなりの力技だと思います…。
作業はなるべくスマートに行ないたいものです。

それでは本題です。

ダンプデータをリストアしようとすると以下のエラーが発生。

エラーの原因としては、ホスティングサービスで「LOCK」「UNLOCK」ができないことでした。

以前の記事で書いたように、以下を実行してダンプファイルを作成した場合、作成されたファイル中に「LOCK」「UNLOCK」が含まれます。

この場合は、作成したダンプファイルの「LOCK」「UNLOCK」をコメントアウトしてからリストアするとエラーになりません。

また、オプション「–skip-add-locks」を追加すると、「LOCK」「UNLOCK」を含まずダンプファイルを作成することができます。

こちらの方法であれば、作成したダンプファイルの修正も必要なく、修正によるミスも減らせますね。

PostgreSQL(EUC_JP)からMySQL(UTF-8)へのデータ移行

localeがUTF-8の環境でEUC_JPのデータベースを作成するの続きです。

テーブル数がそれほど多くない環境での作業です。
もっと良い方法があったらぜひ教えてください。

それぞれの環境は以下の通りです。
【旧システム】 PostgreSQL、EUC_JP(locale、データベースの文字コードとも)
【新システム】 MySQL、UTF-8(locale、データベースの文字コードとも)

まず、【旧システム】での作業です。

  1. テーブル毎にcsvファイルを作成します。例として、テーブル名をcrestboz、csvファイル名をcrestboz.csvとします。

    この時点で、crestboz.csvの文字コードはEUC_JPです。

  2. 作成したcrestboz.csvから、先頭のカラム名が記述された行と最後尾の行数が記述された行を削除します。
  3. ファイルの文字コードをUTF-8に変換します。

次に、【新システム】での作業です。

  1. データのインポート対象のテーブルが既存の場合はDELETEします。
    (*テーブルをDROPして作成し直すと、ID値がリセットされます。)
  2. csvファイルをインポートします。

    関連記事:load data infile文で文字化けする場合の対策

以上の作業を、データを移行するすべてのテーブルについて実行します。

データをダンプ、リストアするには(01)

ここ10年ほど、データベースは PostgreSQLを使ってきました。
使用するサーバの関係で、今年から MySQLを使っています。

まだまだ不慣れなため、勉強したことを少しずつ書いていきたいと思います。

* データをダンプ

  • データベース全体を保存

  • テーブルを指定して保存

* ダンプデータをリストア

* いろいろ試したこと

  • ダンプ時に指定したオプション(--single-transaction)について
    オプションなしで実行したところ、エラーが発生。

    【原因】テーブルロック権限(LOCK TABLES)がないため。
    【回避方法】オプション --single-transaction の指定。

  • リストア時の文字コード指定(--default-character-set=utf8 -u)について
    オプションなしで実行したところ、エラーが発生。