タグ: mysql

phpMyAdminでDATETIME型が「BLOB」と表示される

  • PHP5.6
  • Apache 2.2
  • MySQL 5.6

をソースからビルドし

  • phpMyAdmin4.6.4

を設置・設定したうえで、DATETIME型を含むテーブルを作りデータを投入後、phpMyAdminから内容を確認すると、

DATETIME型がBlog表示

と表示されてしまいました。

MySQLコマンドでのSELECTやMySQL Workbenchを使って確認したところ、日付として表示されており問題がない状況。

調べたところ、

php – phpMyAdmin: Date Fields Display as BLOB – Stack Overflow

が見つかり、phpinfo()でPHPのMySQLモジュールに何が使われているのか確認したところ、mysqlモジュールが使われていました。
そこで、mysqliモジュールを使うようにビルドしなおしたところ、

DATETIME型が正常に表示

と正常に表示されるようになりました。

広告

DockerのOfficial imageのMySQLを使って初回起動時にカスタムSQLを実行させる

DockerのOfficial RepositoriesMySQLを使って、初回のDBデータ作成時に、カスタムSQLを実行する。

MySQLイメージは起動時にMySQLデータディレクトリ(/var/lib/mysql)の中身がない場合、mysql_install_dbなどが実行されます。その処理の中で、 /docker-entrypoint-initdb.dディレクトリにシェルスクリプトや、SQLが存在する場合、それを実行してくれる仕組みがあります。

サンプル

サンプルとして、Docker Composeを使って、PHPMyAdminとMySQLサーバを構築してみます。
その際、初回起動時に2つのデータベースと1つのテーブルを作成します。
うち1つは環境変数MYSQL_DATABASEをセットすることによって自動的に作成されるデータベース、それ以外がdocker-entrypoint-initdb.dによるものです。

ローカルにinitdb.dフォルダを作成し、それをMySQLイメージ中のdocker-entrypoint-initdb.dにマウントさせます。

initdb.dフォルダには、create_another_db.shとcreate_table_in_exampledb.sqlを置いておきます。

docker-compose upでMySQLが立ち上がると、最後にそれらのファイルが実行されます。

create_another_db.shはDBを新規作成します。シェルなので、環境変数を参照できます。
MySQLへの流し込み方法は、docker-entrypoint.shを参考にパスワードを追記した形です。

create_table_in_exampledb.sqlは環境変数MYSQL_DATABASEによって既に作成されているDBに対してテーブルを作成します。

全部入りはこちら→mistymagich/docker-mysql-sample

参考

Docker MySQLイメージのクエリログ(General Query Log)出力

mysql Repository | Docker Hub Registry – Repositories of Docker Imagesのクエリログを見る方法

方法1.クエリログをファイル出力して、ログディレクトリをデータボリュームでマウント

  1. log.cnf(クエリログを出力するように設定したMySQLの設定ファイル)とDockerfileを作成する

    log.cnf

    [mysqld]
    log_output=FILE
    general_log=1
    general_log_file=/var/log/mysql/query.log
    

    Dockerfile

    FROM mysql
    ADD log.cnf /etc/mysql/conf.d/log.cnf
    RUN chmod 644 /etc/mysql/conf.d/log.cnf
    
  2. ビルド
    $ docker build -t mysql_custom .
    
  3. ログディレクトリを作成
    $ mkdir mysqllog
    
  4. Docker側のMySQLが書き込みできるように権限を与える
    $ chmod 777 mysqllog
    
  5. Docker実行
    $ docker run -e MYSQL_ROOT_PASSWORD=my-secret-pw -v `pwd`/mysqllog:/var/log/mysql -p "3306:3306" -d mysql_custom
    
  6. クエリログの参照
    $ sudo tail -f mysqllog/query.log
    
  7. 上記出力したまま、別端末でMySQLサーバにログインしてクエリを発行し、記録していることを確認
    $ MYSQL_PWD=my-secret-pw mysql -u root -h 127.0.0.1 -P 3306
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    3 rows in set (0.00 sec)
    
    150729 12:02:11     6 Connect   root@172.17.42.1 on
                        6 Query     select @@version_comment limit 1
    150729 12:02:12     6 Query     show databases
    150729 12:02:14     6 Quit
    

Dockerfileを使わない方法

mysqlイメージは、/etc/mysql/conf.dに追加の設定ファイルを入れておけば、起動時に読み込む仕組みのため、docker run 時にマウントすることでDockerfileを省略できる。

設定ファイルを保存するディレクトリを作り、先ほどのlog.cnfを移動する。

mkdir mysqlconf
mv log.cnf mysqlconf/

/etc/mysql/conf.dにマウントさせて起動させる。

docker run -e MYSQL_ROOT_PASSWORD=my-secret-pw -v `pwd`/mysqllog:/var/log/mysql -v `pwd`/mysqlconf:/etc/mysql/conf.d -p "3306:3306" -d mysql

方法2.クエリログの出力先を標準出力にする

kolypto/docker-mysqlを参考に

log.cnfを以下のようにする

[mysqld]
log_output=FILE
general_log=1
general_log_file=/dev/stdout

起動する

docker run -e MYSQL_ROOT_PASSWORD=my-secret-pw -v `pwd`/mysqlconf:/etc/mysql/conf.d -p "3306:3306"  mysql

しかし、起動中に

[ERROR] Could not open /dev/stdout for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

となり、クエリログは出力されませんでした。

Dockerのバージョンは

$ docker --version
Docker version 1.7.1, build 786b29d

docker execで実行中のMySQLイメージに入り、コマンドラインで書き込みをテストしてみる。

$ docker ps
CONTAINER ID        IMAGE               COMMAND                CREATED             STATUS              PORTS                    NAMES
bf5335a829bf        mysql               "/entrypoint.sh mysq   25 seconds ago      Up 24 seconds       0.0.0.0:3306->3306/tcp   boring_engelbart
$ docker exec -it bf5335a829bf bash
root@bf5335a829bf:/# apt-get update
root@bf5335a829bf:/# apt-get install -y sudo
root@bf5335a829bf:/# sudo -u mysql sh -c "echo test > /dev/stdout"
sh: 1: cannot create /dev/stdout: Permission denied

/dev/stdoutの権限を確認する。

root@bf5335a829bf:/# ls -l /dev/stdout
lrwxrwxrwx 1 root root 15 Jul 30 04:04 /dev/stdout -> /proc/self/fd/1
root@bf5335a829bf:/# ls -l /proc/self/fd/1
lrwx------ 1 root root 64 Jul 30 04:12 /proc/self/fd/1 -> /dev/pts/4
root@bf5335a829bf:/# ls -l /dev/pts/
total 0
crw-rw-rw- 1 root root 5, 2 Jul 30 04:04 ptmx

/dev/stderrも同様で、以下のissueが出ています。
Permission denied on /dev/stderr · Issue #6880 · docker/docker

Dockerで作るPHPの実行環境サンプル

ファイル一式: https://github.com/mistymagich/vagrant-docker-php

php,nginx,mysqlのコンテナを動かして、PHPを実行させるサンプル環境です。

vagrantとvirtualboxもしくはdockerがインストールされているlinuxから起動できます。

必要なもの

  • Vagrantを使う方式
  • Dockerから起動する方式
    • DockerがインストールされたLinux
      バージョン 1.5.0 以上

セットアップ

  • Vagrantを使う方式
        > git clone https://github.com/mistymagich/vagrant-docker-php.git
        > cd vagrant-docker-php
        > vagrant up
    

    CoreOSが立ち上がるので、PuTTYなどでSSHでログインする (接続先は vagrant ssh-configで確認)

        $ cd /vagrant
        $ /vagrant/docker-build.sh
    
  • Dockerから起動する方式
        $ git clone https://github.com/mistymagich/vagrant-docker-php.git
        $ cd vagrant-docker-php
        $ chmod +x *.sh
        $ sudo ./docker-build.sh
        $ sudo echo '127.0.0.1 sandbox.local' >> /etc/hosts
    

    Ubuntu/Debianでdockerコマンドが docker.io1 になっている場合、docker-build.sh、docker-run.shの

    DOCKER=docker
    

    DOCKER=docker.io
    

    に変更したのち実行する。

起動

$ sudo ./docker-run.sh

正常に起動できれば、ブラウザで

http://sandbox.local

でアクセスするとPHPInfoが表示されます。

MySQLコンテナとの接続サンプルは

http://sandbox.local/dbconnect.php

にあります。

構造

Nginxコンテナ

コンテナ名:sandbox-nginx

公式Nginxコンテナをもとに、ホスト名sandbox.localに対して、PHPコンテナを参照する設定追加しています。
ドキュメントルートはsrc/public
拡張子がPHPならPHPコンテナにあるPHP-FPMを通して実行されます。

MySQLコンテナ

コンテナ名:sandbox-mysql

公式MySQLコンテナをそのまま利用しています。

データの永続化はしていません。

docker-run.sh実行時にsrcディレクトリにあるinit.sqlを実行することで、データベース・テーブル・データのインポートを行っています。

sanbox.localに対して3306にアクセスすることでMySQLコンテナにアクセスできます。

ユーザー名はroot、パスワードはdocker-run.shでMySQLコンテナ起動時にMYSQL_ROOT_PASSWORDで指定している値です。(編集していない場合、mysecretpw)

PHP(FPM)コンテナ

コンテナ名:sandbox-php

公式PHPコンテナのFPMをもとに、以下のモジュールを追加しています。

  • pdo
  • pdo_mysql
  • gd
  • mysqli
  • mcrypt
  • mbstring
  • iconv

関係図

関係図

PHP(FPM)コンテナはホスト名mysqlでMySQLコンテナにアクセスできます。
Nginxコンテナはホスト名phpでPHP(FPM)コンテナを参照しています。

PHPMyAdminを動かすサンプル

  1. PHPMyAdminをダウンロード
  2. 解凍して、中にあるPHPファイルをsrc/publicにコピー(すでにあるファイルは削除する)
  3. config.sample.incをconfig.incにリネーム
  4. config.incを編集

    ホスト名を変更

     /* Authentication type */
     $cfg['Servers'][$i]['auth_type'] = 'cookie';
     /* Server parameters */
    -$cfg['Servers'][$i]['host'] = 'localhost';
    +$cfg['Servers'][$i]['host'] = 'mysql';
     $cfg['Servers'][$i]['connect_type'] = 'tcp';
    

    末尾に追加

     $cfg['CheckConfigurationPermissions'] = false;
    
  5. http://sandbox.localにアクセス
  6. ID:root / PW:mysecretpw (docker-run.shのMySQLコンテナ起動時のMYSQL_ROOT_PASSWORDで指定している値)

その他

  • docker-run.sh実行時にすべてのコンテナを削除します。
  • コンテナが正常に動かない場合、docker ps -aでコンテナIDを調べ、docker logs コンテナID で原因となるメッセージが出力されることがあります。

  1. Ubuntu/Debianの場合、dockerコマンドがdocker.ioになっているため、いまさら聞けないDocker入門(2):ついに1.0がリリース! Dockerのインストールと主なコマンドの使い方 (1/3) – @ITにあるようにしてdockerコマンドを利用できるようにする方法もあります。 

MySQLでNULLを含む列をORDER BYしたときの挙動

NULL値を許容するカラムをORDER BYで取り出す際、

MySQL :: MySQL 4.1 リファレンスマニュアル :: 3.3.4.6 NULL 値の使用
より

ORDER BY を処理する場合、NULL 値は、ORDER BY ... ASC では先頭に表示され、ORDER BY ... DESC では最後に表示されます。

となる。

昇順(ASC)でNULLを最後に表示したい場合は、

ORDER BY created_at IS NULL ASC, created_at ASC;

のようにする。

 

DBが存在しないときにDBを作成するChefレシピを作ったときの試行錯誤メモ

開発サーバ用のcookbookを作る際に、MySQLのDBが存在しないときにDBを作成したかったときの試行錯誤メモ

opscode-cookbooks/wordpressのDB作成部分を参考にというかほぼそのまま流用

使用したboxは

http://developer.nrel.gov/downloads/vagrant-boxes/CentOS-6.4-i386-v20130731.box

やりたいこと

  1. MySQLのインストールと起動
  2. DBユーザの作成
  3. DBの作成
  4. テーブルの作成

1.MySQLのインストールと起動

package "mysql-server" do
    action :install
end

service "mysqld" do
    action [ :enable, :start ]
    supports :status => true, :restart => true, :reload => true
end

2.DBユーザの作成

GRANT文をまとめたgrants.sql.erbをtemplatesに格納しておき、/tmpにUPの後、mysqlコマンドで流し込む。

grants.sql.erbの中身

GRANT ALL ON <%= @database %>.* TO '<%= @user %>'@'localhost' IDENTIFIED BY '<%= @password %>';
GRANT ALL ON <%= @database %>.* TO '<%= @user %>'@'%' IDENTIFIED BY '<%= @password %>';
FLUSH PRIVILEGES;

レシピに追加

execute "mysql-create-user" do
    command "/usr/bin/mysql -u root --password=\"#{node['example']['db']['rootpass']}\"  < /tmp/grants.sql"
    action :nothing
end

template "/tmp/grants.sql" do
    owner "root"
    group "root"
    mode "0600"
    variables(
        :user     => node['example']['db']['user'],
        :password => node['example']['db']['pass'],
        :database => node['example']['db']['database']
    )
    notifies :run, "execute[mysql-create-user]", :immediately
end

executeリソースではaction :nothingで実行せず、templateリソースでnotifiesを使ってexecuteリソースを実行させる

3.DBの作成

Mysql::list_dbsを利用して、DBの存在チェックを行い、なければmysqladminコマンドでDBを作成する。

package "mysql-devel" do
    action :install
end.

chef_gem "mysql" do
    action :nothing
    subscribes :install, "package[mysql-devel]", :immediately
end

execute "mysql-create-database" do
    command "/usr/bin/mysqladmin -u root create #{node['example']['db']['database']}"
    not_if do
        require 'rubygems'
        Gem.clear_paths
        require 'mysql'
        m = Mysql.new(node['example']['db']['host'], "root", node['example']['db']['rootpass'])
        m.list_dbs.include?(node['example']['db']['database'])
    end
end

executeリソースのnot_ifにあるrubyプログラム部で、Mysqlライブラリを使うためには、chefを動かしているrubyに対して、インストールする必要がある。
そこで、chef_gemリソースを使う。

またgem install msyqlの際にmysqlのヘッダファイルが必要になるので、mysql-develもインストールする。

しかしそれだけだと、最初のpackage “mysql-devel”よりも先に、chef_gemリソースが実行されてしまうので、

================================================================================
Error executing action `install` on resource 'chef_gem[mysql]'
================================================================================

Gem::Installer::ExtensionBuildError
-----------------------------------
ERROR: Failed to build gem native extension.

というエラーになってしまう。
そこで、subscribesを使って、package mysql-develを実行した後に、即時実行するように指定する。

4.テーブルの作成

2.と同様にCREATE TABLES文をまとめたtables.sqlをfilesに格納しておき、/tmpにUPの後、mysqlコマンドで流し込む。
3.と同様にテーブルがなければ実行するようにしたが、tables.sqlがアップ済みなら実行されないので冗長かもしれない。

execute "mysql-create-tables" do
    command "/usr/bin/mysql -u root #{node['example']['db']['database']} < /tmp/tables.sql"
    action :nothing
    only_if do
        require 'rubygems'
        Gem.clear_paths
        require 'mysql'
        m = Mysql.new(node['example']['db']['host'], "root", node['example']['db']['rootpass'])
        begin
            m.select_db(node['example']['db']['database'])
            m.list_tables.empty?
        rescue Mysql::Error
            return false
        end
    end
end

cookbook_file "/tmp/tables.sql" do
    owner "root"
    group "root"
    mode "0600"
    notifies :run, "execute[mysql-create-tables]", :immediately
end

注釈

  • node[‘example’]~はattributesのdefault.rbで定義した値
    default['example']['db']['user'] = 'username'
    default['example']['db']['pass'] = 'password'
    default['example']['db']['database'] = 'database-name'
    default['example']['db']['host'] = 'localhost'
    default['example']['db']['rootpass'] = ''
    

    exampleのところはクックブック名に置き換える。
    rootpassが何もないのは、インストール後のデフォルトではrootのパスワードはないため

  • grants.sqlやtables.sqlは/tmpではなくてきちんとしたところにUPしたほうがいい
  • 同様のことは、opscode-cookbooks/databaseを使えばできる。

参考になったページ

MySQLのダンプファイルをPostgreSQL用に変換

このツールを使いました。
pgFoundry: mysql2pgsql: Project Home

  1. mysql2pgsqlをダウンロード
  2. chmod +x で実行権限を付与
  3. mysqldumpコマンドでSQLファイルをダンプ(export-mysql.sqlとする)
  4. ./mysql2pgsql export-mysql.sql import-pgsql.sql で変換
  5. pgsql importdatabase < import-pgsql.sqlでインポート

参考サイト

Converting from other Databases to PostgreSQL – PostgreSQL wiki