MySQLの最近のブログ記事

MySQL を使った Web システムの移行作業をした。

何年か前に作成したもので、DB とのやり取りをする部分は Perl による CGI 形式になっている。
枯れた技術最高!(笑)
あ、今回は Perl ではなく MySQL の話ね(^^;

移行先にも MySQL は入っていたので、早速、移行元で、

# mysqldump -u root -p --default-character-set=binary hoge_db > hoge_db.sql

と、バイナリデータとしてバックアップ。
このデータを移行先サーバに持ち込み、

# mysql -u root --default-character-set=binary hoge_db < hoge_db.sql

と、DB にリストアした。(もちろん、予め空の DB を作っておく)

早速 mysql コマンドで SELECT をしてみると、文字化け~。表示できなかった文字が ? に置換されとる。

DB を確認すると、

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

なんか、文字コードが「ラテン語」になっとるやないか。

/etc/my.cnf の mysqld、client セクションに、それぞれ、

[mysqld]
character-set-server = utf8

[client]
default-character-set = utf8

を追加し、DB 確認。

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

お~。この時点で、mysql コマンド上での文字化けはなくなった。

しかし、CGI からのアクセスでは相変わらず文字が化ける。
こういう場合は、だいたい DBI/DBD モジュールの問題だ。

案の定、このサーバ上で make された DBD::MySQL は mysql_enable_utf8 を設定されていないようで、そのため「DBに格納されている通常の utf8 文字列は、エンコード失敗の ? に置換されDBから取り出される。」ようだ。

その証拠に、mysql_enable_utf8 を設定した場合と同じ効果のある「SET NAMES utf8」という SQL 文を、本来の SQL 文を処理する前に DB に送るようにしたら文字化けは解消した。(db_tool::directExec というのは当社独自の DB アクセス関係の関数)

# 先に SET NAMES utf8 を発行
my $set_sql =<<EOS;
SET NAMES utf8
EOS

@db = &db_tool::directExec($Conn, $set_sql);

# 次に SELECT を実行
$sql  =<<EOS;
SELECT
COUNT(id) AS count
FROM
hoge_data_list
WHERE
ID = $USER_ID
EOS

@db = &db_tools::directExec($Conn, $sql);

<以下略>

こんな感じで。

これで CGI でも文字化けは解消された。

ちなみに、db_tools::directExec 関数の中で「SET NAMES utf8」を発行するようにすれば、CGI 側で修正する必要はなくなるんだけど、この関数、他の人も使ってるからなあ・・・あんまり触りたくない。
仕事で ZABBIX のインストールをしたのだが、zabbix-server の起動で、

 17793:20130813:104837.723 Database is down. Reconnecting in 10 seconds.
 17793:20130813:104847.723 [Z3001] connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

というエラーが出て正常に起動出来ない。

多分ググれば「MySQL が起動していない可能性がある」とか出てると思うんだけど、実際のところ MySQL は起動している。

オチを書いちゃうと、単に、ソケットファイルが /var/lib/mysql/mysql.sock に作られていないだけの話だ。

RPM で MySQL を入れると /var/lib/mysql/mysql.sock に作られるが、ソースから make して入れると(明示的に指定しない限り)、/tmp/mysql.sock に作られる。

なので、/etc/zabbix/zabbix_server.conf の

### Option: DBSocket
#       Path to MySQL socket.
#
# Mandatory: no
# Default:
# DBSocket=/tmp/mysql.sock

DBSocket=/var/lib/mysql/mysql.sock

この部分を、

DBSocket=/tmp/mysql.sock

に直すだけでOK

「MySQL は立ち上がってるのに、なんでぇ~???」と半べそになってる人もいるかもしれないので、一応書いときます。
ははは。←なぜ急に笑い出したのか自分でもわからないが(^^;

今回契約した IIJ GIO ホスティングでは、基本容量が 30GB しかないので、MySQL のバイナリログを作ってたら、すぐにディスクが一杯になっちゃう。

ということで、ログの出力止めるのどうだっけ?と思って、「MySQL mysql-binを作らない」でググると、真っ先に俺のブログの「MySQLのバイナリログの作成を止める」ってエントリーが引っかかるな(^^;

毎回同じ問題に直面してるんなら、いい加減やり方覚えとけって話だけど、ま、どこからでも Google を始めとした検索エンジンが使える昨今、こういうたまにしかやらないことは無理に覚えなくてもね。

人間、覚えとかなきゃいけない情報は他に沢山あるんだよ!!

嫁の誕生日とか!

忘れたら折檻されるでしょ?(^^;

ということで、/etc/my.cnf の、

log-bin=mysql-bin
binlog_format=mixed

この 2行をコメントにして、MySQL の再起動。

これでバッチリです。

つーか、次回から、MySQL をインストールする時に、バイナリログ吐かないオプション付けて make しよう。
それが正しい道(笑)
やれやれ・・・。

運用元が vinstall で入れろとうるさいし、当初 vinstall でセットアップされた既存環境を色々いじるのも面倒なので、昨日、ソースから手動インストールした MySQL を削除するハメになった。

アンインストールと言うても、全部ファイルを削除して、自動起動設定消したり、ライブラリの読み込み設定とかを元に戻したり、細々とすることがあるな。面倒くせえ(^^;

取り敢えず、やったことをメモっとこ。

(1) まず、現在起動中の MySQL を停止

# service mysql stop
Shutting down MySQL........ SUCCESS!
# ps auxww|grep mysql
root     22152  0.0  0.0  1780  500 pts/0    S+   12:13   0:00 grep mysql

(2) 自動起動設定の削除

# chkconfig mysql off
# chkconfig --del mysql
# chkconfig --list mysql
service mysql supports chkconfig, but is not referenced in any runlevel (run 'chkconfig --add mysql')
# ls -la /etc/rc.d/init.d/mysql
-rwxr-xr-x 1 root root 12302 May 18 17:30 /etc/rc.d/init.d/mysql
# mv /etc/rc.d/init.d/mysql /etc/rc.d/init.d/mysql_old
# chmod 0 /etc/rc.d/init.d/mysql_old

(3) ライブラリをロードしないよう設定削除

# ls -la /etc/ld.so.conf.d/mysql-5.conf
-rw-r--r-- 1 root root 27 May 18 16:14 /etc/ld.so.conf.d/mysql-5.conf
# \rm /etc/ld.so.conf.d/mysql-5.conf

(4) DB の削除

# \rm /var/lib/mysql/mysql/*

(5) そういえば /etc/my.cnf も触ってたな。元に戻す。

# \cp /etc/my.cnf_20110518 /etc/my.cnf

(6) /usr/local/bin の下にシンボリックリンク貼ってるのを削除

# \rm /usr/local/bin/mysql
# \rm /usr/local/bin/mysqladmin

(7) /usr/local/mysql も全て消しちゃう

# \rm -r /usr/local/mysql

と、こんなところか。
mysql ユーザー/グループは残しておかないといかんからな。

ああ、面倒くせえ(^^;;;

何か、うちのサーバのうちの一台が、そんなに使い込んでいるわけでもないのにパーティションの使用率が 63% とかなってて、調べてみたら MySQL のバイナリログが大量に作られていた。

# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/simfs            40960000  26685216  14274784  66% /
none                   9222092         4   9222088   1% /dev
# ls -la ./usr/local/mysql/var
total 13688436
<略>
-rw-rw----  1 mysql mysql 1073791652 Apr  1  2010 mysql-bin.000015
-rw-rw----  1 mysql mysql  190093790 Apr  5  2010 mysql-bin.000016
-rw-rw----  1 mysql mysql     286974 Apr  5  2010 mysql-bin.000017
-rw-rw----  1 mysql mysql  171114418 Apr  9  2010 mysql-bin.000018
-rw-rw----  1 mysql mysql  866200268 Apr 30 09:39 mysql-bin.000019
-rw-rw----  1 mysql mysql       1859 Apr 30 09:43 mysql-bin.000020
-rw-rw----  1 mysql mysql        125 Apr 30 09:43 mysql-bin.000021
-rw-rw----  1 mysql mysql     142644 Apr 30 09:48 mysql-bin.000022
-rw-rw----  1 mysql mysql 1073817436 May 14 14:16 mysql-bin.000023
-rw-rw----  1 mysql mysql 1073803242 May 27 14:05 mysql-bin.000024
-rw-rw----  1 mysql mysql 1073793089 Jun 10 02:51 mysql-bin.000025
-rw-rw----  1 mysql mysql 1073767183 Jun 26 08:23 mysql-bin.000026
-rw-rw----  1 mysql mysql 1073818211 Jul 14 14:48 mysql-bin.000027
-rw-rw----  1 mysql mysql  776652896 Jul 28 06:56 mysql-bin.000028
-rw-rw----  1 mysql mysql 1073830771 Aug 17 10:07 mysql-bin.000029
-rw-rw----  1 mysql mysql 1073840585 Sep  4 00:07 mysql-bin.000030
-rw-rw----  1 mysql mysql 1073889708 Sep 15 19:45 mysql-bin.000031
-rw-rw----  1 mysql mysql 1073831622 Oct  2 02:53 mysql-bin.000032
-rw-rw----  1 mysql mysql 1073807767 Oct 19 01:51 mysql-bin.000033
-rw-rw----  1 mysql mysql  157164502 Oct 21 17:57 mysql-bin.000034
-rw-rw----  1 mysql mysql        646 Oct 19 01:51 mysql-bin.index
<略>

これは、レプリケーションをする時に必要なバイナリログなので、実際にはいらんのよねえ。レプリケーションしてないし。

つーことで、最後の3ファイルくらい(念のため)残して、他は全部削除した。
おかげで使用率は 35% に。よしよし。

# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/simfs            40960000  14229292  26730708  35% /
none                   9222092         4   9222088   1% /dev

で、my.cnf 修正して、log-bin を作らないようした。

# cp /etc/my.cnf /etc/my.cnf_20101021
# vi /etc/my.cnf
# diff /etc/my.cnf /etc/my.cnf_20101021
60,61c60
< # 2010/10/21
< #log-bin=mysql-bin
---
> log-bin=mysql-bin

んで、再起動。(一応、今回は一旦 STOP してから START した)

# service mysqld stop
Stopping MySQL:                                            [  OK  ]
# service mysqld start

Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]

起動出来なかったし。(^^;

ログ見てみると、

# tail hogehoge.exsample.com.err
101021 18:09:09 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

101021 18:09:09 mysqld_safe mysqld from pid file /usr/local/mysql/var/hogehoge.exsample.com.pid ended
101021 18:09:13 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
101021 18:09:14 [ERROR] You need to use --log-bin to make --binlog-format work.
101021 18:09:14 [ERROR] Aborting

101021 18:09:14 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

101021 18:09:14 mysqld_safe mysqld from pid file /usr/local/mysql/var/hogehoge.exsample.com.pid ended

もう一度 my.cnf 見てみると、ああ、log-bin の他に、binlog_format という設定項目もあったのか。こいつもコメントに。

# vi /etc/my.cnf
# diff /etc/my.cnf /etc/my.cnf_20101021
60,61c60
< # 2010/10/21
< #log-bin=mysql-bin
---
> log-bin=mysql-bin
64,65c63
< # 2010/10/21
< #binlog_format=mixed
---
> binlog_format=mixed

これで、もう一度起動してみる。

# service mysqld start
Starting MySQL:                                            [  OK  ]

OK。問題なく起動した。

つーことで、一安心、一安心。

MySQL でデータを管理する CGI を書いていたのだが、EUC-JP で保存したデータが化ける。

う~む・・・

DB も、

DEFAULT CHARACTER SET ujis;

で作ってるし、その中の TABLE も、

DEFAULT CHARSET=ujis;

で作成している。

そこに、Jcode.pm で EUC-JP に変換したデータを突っこんでるのだが、例えば「事務局」が「???局」みたいに化けてしまう。

う~ん・・・(^^;

結局、DB と TABLE の CHARSET を latin1 で作り直したら解消した。

どうも、character_set_client が latin1 で、character_set_database が ujis のためのようだな。
↓ここに詳しく書いてある。
http://www.mysql.gr.jp/frame/modules/bwiki/index.php?FAQ#content_1_44

う~む・・・
自分ところのサーバなら、mysqld 他全ての charset を統一することも出来るが、お客さんところの MySQL の設定は、他のシステムがどうなってるのかわからないので触れんしなあ・・・

ということで、取りあえず今回はこのままで。

 

Perl で、DBD::mysql モジュールを使って MySQL のデータベースに接続しているのだが、

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at hogehoge.pl line 97

というエラーになって接続出来ず。
この間書いたエントリー「MTOS で MySQL の Socket が見つからない時」と同じパターンじゃね。

これは、DB に接続する時に、

$dbname = 'hogehoge';
$user = 'hogeuser';
$passwd = 'hogepass';
$host = 'localhost';
$port = '3306';
$option = 'mysql_socket=/tmp/mysql.sock';

$dbh = DBI->connect("dbi:mysql:$dbname:$host:$port;$option", "$user", "$passwd");

と、オプションでソケットの位置を明示的に指定してやればOK。

しかし、何で my.cnf の socket 指定は読んでくれんのかね?
/etc/my.cnf には、

socket  = /tmp/mysql.sock

と書いているのだが。この値を使ってくれないの?

/etc/my.cnf に、

mysql_socket  = /tmp/mysql.sock

なんて書くと

[ERROR] /usr/local/mysql/libexec/mysqld: unknown variable 'mysql_socket=/tmp/mysql.sock'

って怒られるしね。

まあ、取りあえず動いているので良いが、釈然としないな。誰かエロい、いや、エラい人、MySQL のこと教えて!

一応、(本気でハックしたことは無いが)MovableType も 3 の頃からの付き合いなので、先日 MTOS5 をインストールした時もついつい今までの習慣で mt-config.cgi を自分で編集して put したが、MT5 からは Web 画面の「環境設定ウィザード」で自動生成するのが推奨されているようである。

つーことで、先日入れた MTOS5 を一旦全部消して入れ直してみた。

なるほど。
全ファイルをサーバ上に put して、cgi に実行権与えて(必要であれば、httpd の実行ユーザで更新が行われるディレクトリやファイルの権限を修正して)、mt.cgi とかにアクセスすれば、セットアップが済んでいないので

http://www.exsample.jp/mt/mt-wizard.cgi

に強制的にリダイレクトされて、環境設定ウィザードの開始である。

なるほど、簡単だな。(もちろん、まったく知識の無い人間では厳しいが(^^;)


20100409_db_test.jpg

この間「MTOS で MySQL の Socket が見つからない時」で書いた MySQL の Socket ファイルのパスも(「高度な設定」リンクをクリックしなければいけないが)、画面に「データベースソケット」という項目があるのでここで入力すれば良い。
Socket のパスが /var/lib/mysql/mysql.sock ではない人は必ず設定しようね!

まあ、ただ、こういう具合に「一見簡単」なインタフェースを用意されると、かえってうちらのような業者は面倒くさいことになるんだよな。

「中途半端に知識があるお客さんのチャレンジ」が始まるからだ。(^^;

絶対、こういうインタフェースがあると「自分でやる」と言い出すお客さんが出てくるのだ。それはかまわない。俺らも、やってない作業に金をくれ」とは言わないので。是非自分で頑張ってコスト削減に励んでほしい。
・・・でも、「質問はタダ」とは思わないでほしい。(^^;

絶対、これ、「DB の接続テストが OK にならんのじゃけど、なんで?」と聞いてくるお客さん、いるよなあ。(^^;
そこから原因調べて「こうすればいいですよ」とアドバイスするところ、そこが正に俺らの仕事なわけで「タダではない」んですよ!俺らに相談したところで「既に自分の力だけでやっているのではない」んですよ!

・・・ってことを、ちゃんと認識してほしいな。社会人として。:-)

と、そんなことを思っているうちに作業は終了した。楽になったものだ。

MovableType をベースにする案件があるので、自社サーバに MTOS5 をセットアップしていたのだが、対話式インストールウィザードの実行で

Connection error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

というエラーが出てしまう。

まあ、読んで字の如く「Socket が見つからないので MySQL デーモンと Socket 通信が出来ませんぜ」ということだろう。

そりゃそうだ。
Socket の位置は /var/lib/mysql/mysql.sock ではないもの。

現在テスト機で動かしている MySQL は、

socket = /tmp/mysql.sock

という設定で実行している。
ちなみに、同じサーバで某地域 SNS(OpenPNE ベース)を動かしていて、PHP から MySQL の利用は問題なく行われているし、拡張機能のいくつかを Perl で組んで実装しているので Perl からの接続も問題無い。

ということで、これは MovableType 側の問題だ。

で、ざっと grep かけて Socket のパスをどこで指定しているか探してみたが分からなかったので、何か情報は無いかと思ってググってみたら・・・とんでもねえ。
この件に関して記事を書いてるブログはけっこうあるのだが、ほとんどが、

/etc/my.cnf の socket の値を /var/lib/mysql/mysql.sock に変更して MySQL を再起動しましょう

と説明しているのだ。(驚)

ええ???アプリケーション側の事情にデーモン側が設定変更して合わせるの?(^^;
とんでもねえ!そんな対応、下の下の対応だ。
どこぞの素人が書いた記事が、これまた素人によってどんどん拡散していってるんじゃないのか?こえ~(笑)

正解は、mt-config.cgi に

DBSocket /tmp/mysql.sock

という記述を追加する・・・だ。

最近 MySQL では UTF-8 しか扱ってなかったのだが、今回作成したプログラムは「Shift_JIS で書かれた CSV ファイルを読み込んで DB に登録。もちろん NEC 特殊文字や NEC 選定 IBM 拡張文字もガンガン混ざってますぜ!」という香ばしいもの。

ソース自体は EUC-JP で書いたので、DB の中身も EUC-JP で保管するようにして、自前のコード変換関数で Shift_JIS→EUC-JP に変換している。
CREATE TABLE するときにも、ちゃんと DEFAULT CHARSET=ujis 付けてるしね。

なのに、データを登録した後で phpMyAdmin でチェックすると、日本語化け化けやん。

どうも、MySQL ではサーバ側で勝手に文字コードのエンコードをしてしまうので、UTF-8 以外のコードではこういう文字化けが発生してしまうという。
日頃 PostgreSQL ばかり使ってて、いきなり MySQL に来たら絶対ハマっちゃうよなあ、これは。

で、解決方法としては、本来の SQL を投げる前に、

SET NAMES ujis

という SQL を発行すること。

具体的には、

$query    =<<EndOfQuery;
SET NAMES ujis
EndOfQuery

$sth = $dbh->prepare($query);
$sth->execute();

としてから、本来の SQL を

$query    =<<EndOfQuery;
INSERT
INTO
  hogehoge
~本来の SQL~
EndOfQuery

$sth = $dbh->prepare($query);
$sth->execute();

と実行すれば良いと。

なんだかなぁ(^^; テーブル作る時に DEFAULT CHARSET=ujis なんて宣言している意味がなかったな。(^^;
まあ、DB に詳しい偉い人たちが文句を言ってないってことは、この実装も間違いではないんだろうけど、なんだかなあ(^^;

ちなみに、MySQL の設定ファイルや実行時のオプションであれこれする方法もあるようなのだが、今回使用しているのが安いレンタルサーバで、MySQL の設定ファイルは触れない仕様らしいので、毎度 SET NAMES ujis を発行する方法しかないようだ。

とほほ。またも無駄な時間を・・・

このアーカイブについて

このページには、過去に書かれたブログ記事のうちMySQLカテゴリに属しているものが含まれています。

次のカテゴリはPostgreSQLです。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。


月別 アーカイブ

電気ウナギ的○○ mobile ver.

携帯版「電気ウナギ的○○」はこちら