MySQL 5.7.27(ZIP板)をWindows7にセットアップ

概要

複数PCにMySQLのセットアップが必要な場合、データコピーだけで構築できると便利が良い。
過去に構築した環境がいい加減古くなったので、最新バージョンで再構築する。
バージョン選定の際、最新の8.0系にするか迷った。
8.0系は速度が魅力的だが、現システムでは速度が必要でない為、安定重視で5.7系を採用とした。
構築にあたり、戸惑う部分が多く、思ったよりも情報が少なく感じた為、備忘録として残しておく。

・環境
MySQL：MySQL Community Server 5.7.27
OS：Windows 7 32bit

「Windows (x86, 32-bit), ZIP Archive」をダウンロード

以下urlより、zipファイルをダウンロード

url：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

ダウンロードしたzipをd:\に解凍

zipを解凍するが、「my-default.ini」ファイルがない。
どうやら「5.7.18」から無くなったようだ。

参考：MySQL Community Server 5.7.19 - Installing on Windows 10

「my-default.ini」ファイル入手

ダウンロードした、5.7.17のzipを解凍し、「my-default.ini」を入手した。
しかし、シンプルすぎてあまり参考にならなかった。
これなら、過去のmy.iniを再加工しても良いかもしれない。

・my-default.ini

--------------------------------------------------------------------
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

--------------------------------------------------------------------

「my.ini」ファイルの作成

「my-default.ini」の記載を変更し、「my.ini」に変更した。
basedir、datadirの2行を変更している。
「basedir」は、MySQLのフォルダを指定し、「datadir」は、MySQLフォルダのdataフォルダを指定した。(この時点でdataフォルダは存在しない)
また、実際には「\」は￥マークで記載している。

・my.ini

--------------------------------------------------------------------
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir = D:\mysql-5.7.27-win32
datadir = D:\mysql-5.7.27-win32\data
#port = .....
# server_id = .....


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

--------------------------------------------------------------------

「data」フォルダの初期化

コマンドプロンプトで以下コマンド実行し、dataフォルダに初期データを作成する。

D:\mysql-5.7.27-win32\bin>mysqld --defaults-file=D:\mysql-5.7.27-win32\my.ini --
initialize

コマンドを実行すると、dataフォルダ・初期データが作成される。

rootユーザーのパスワード変更

最近のMySQLは初期ユーザーrootにランダムなパスワードが設定されているようだ。
「data」フォルダの「[コンピュータ名].err」ファイルにランダムに生成されたパスワードがある。
実際には、以下のように記載があり、「H4:tF6voe5?=」がrootの初期パスワードである。

・[コンピュータ名].err
--------------------------------------------------------------------

2019-08-04T01:59:18.424458Z 1 [Note] A temporary password is generated for root@localhost: H4:tF6voe5?=

--------------------------------------------------------------------

参考：MySQLインストール直後にログインできない場合の対処

サービス登録

以下サイトを参考にWindowsサービスとして登録した。
サービスとして登録しておけば、再起動後も自動的に起動する。

参考：MySQLをWindowsサービスに登録する


以上で、環境構築は完了。
あとは、データを投入後、フォルダごと他のPCにコピー・サービス登録を行えば、他のPCでも使えるようになる。

MySQLのパフォーマンス改善

過去の経験でMySQLのパフォーマンスが改善出来たものをご紹介。

1. MySQLのインデックス設定忘れ
   小規模なサイトであれば、インデックスを忘れても気付かないものです。
   データが蓄積されると少しずつレスポンスが悪くなるので、たちが悪いです。
   インデックスの設定忘れは、レコード数が100とか1000レベルなら体感できないのですが、10000レコードを超えたあたりから、少しずつレスポンスが悪くなっていきます。
   
   小規模なサイトであれば、どれにインデックスを設定しよう？と悩むことなく、設定できるものは設定した方が良いと思います。
   インデックスを設定した際のデメリットは、データ追加コストの増加とデータの肥大化です。
   1回1回の追記処理にインデックス作成に時間がかかると言っても微々たるものですし、データが肥大化と言っても大したことはありません。
2. DB接続の多用
   1つのphpファイルの中でDBへの接続・切断を繰り返すとレスポンスが悪くなります。
   接続は1回のみ、切断の処理は入れないほうがレスポンスが改善されます。
   接続の処理は入れなくても、phpの処理が終わると自動的に切断されます。
3. DBの参照をlocalhostではなく、127.0.0.1に変更(OSがWindowsの場合)
   これは、Windowsで環境を構築している際に問題が発生します。
   WindowsVista、WindowsServer2008移行はIPv6が有効になっており、localhostで参照するとIPv6優先で接続を行おうとします。
   結果として、IPv6が使える環境は少ないので、v6で接続できずIPv4で接続を行います。
   このv6参照不可からv4で参照に切り替えるまでの時間がロスとなります。
   DBへの接続設定は、127.0.0.1をお勧めします。
   
   ・最初の1回だけサイトを開くのが遅い。
   ・OSをWindowsXPからWindowsVistaやWindows7に変更したら全体的に遅くなった。
   ・OSをWindowsServer2003から2008等に変更したら遅くなった。
   
   上記はサイト毎の設定ですが、OS側で対処を行えば、サイト毎の対処は必要でなくなります。
4. Where句で日付を検索する際は、LIKE演算子ではなく、BETWEENを使う
   データの中から、今月のデータを参照する際、LIKE演算子を使うよりBETWEENが速いです。
   (インデックスを使用する為)
   
   以下は、2013年1月のデータを検索する際の例です。
   
   ・LIKE演算子を使う場合
   $sql = 'select * from table_name where date_field like "2013-01%"';
   
   ・BETWEEN演算子を使う場合
   $sql = 'select * from table_name where date_field between "2013-01-01" and "2013-01-31";
   
   betweenを使用する際の問題点としては、月末の日付を設定するのが面倒という事です。
   date関数で月の日数が取得できるので、以下のように指定しています。
   
   $year = 2013;
   $month = 1;
   
   $start_date = $year.'-'.$month.'-01';
   $end_date = $year.'-'.$month.'-'.date('t',strtotime($year.'-'.$month.'-01'));
   
   $sql = 'select * from table_name where date_field between "'.$start_date.'" and "'.$end_date.'"';

php+mdbファイルの取り扱いで困った

最近、作っているサイトにmdbで作られたDBの取り込みで困る事が多い。
ODBCデータソースとしてmdbファイルを登録すればアクセスできるようなので、既存のphpの記述を一部変更して試していると簡単に移行できるものもあれば、移行できなくて困る事もある。

phpにて、MDBファイルを読み込んだ際に、以下のような記述がうまくできなくて、困った困った。 

※mdbファイルは、ODBCデータソースとして登録済みの前提

$conn_id = odbc_connect($db_name,$db_user,$db_pass);
$sql = 'select * from table_name';
$result = odbc_exec($conn_id,$sql);

if(odbc_num_rows($result)){
//処理を実行(これが実行できない)
}

結論としては、「odbc_num_rows」が使えないという話ですが、phpマニュアルには以下の注記があります。

注意:

SELECT の後に利用可能な行の数を定義するために odbc_num_rows() を使用した場合、 多くのドライバにおいて、-1 が返されます。

最初は気にもしなかったのですが、これが原因でした。
アクセスのファイルは、MySQLにインポートしてから処理する方向で検討します。

レンタルサーバーがPHPを5.3系にアップグレードを予告したので、ローカル環境も5.3系に変更しました。
OS：Windows Vista
Apache：2.2.22
PHP：5.3.5
MySQL：5.0.7

以下の2つの設定が必要だったので、覚書として残しておきます。

・既存スクリプトがエラーを吐き出すようになった。
・DB(MySQL)を使用するソースで、処理が遅くなった。


それぞれの対処(設定等)については、以下のように行った。

・既存スクリプトがエラーを吐き出すようになった。
PHPのエラー出力設定が厳しくなっている為、変数を宣言せずに使ったり、非推奨関数を利用したりしている場合にエラーが出るようになった。
php.iniにて、error_reportingの設定を以下のように設定した。

error_reporting  =  E_ALL & ~E_NOTICE

・DB(MySQL)を使用するソースで、処理が遅くなった。
DBに接続する処理が多いソースほど、開けなくなる状態になった。
Windows VsitaからIPv6が有効になっている為、localhostの参照がIPv6優先になっているのが原因。
hostsファイルを以下のように、IPv6の記述をコメントアウトし、症状は改善した。

127.0.0.1       localhost
#::1             localhost

MySQLでインポートする際の記述

phpMyAdminでデータをエクスポート後、インポートする際にスムーズにできなかった。
レコードが多いと途中でスクリプトが停止し、インポート不可でした。

慣れないコマンドラインからのインポートを行いました。

phpMyAdminでインポートした際には、300秒でスクリプトエラーになった作業が、コマンドラインでは5秒程度・・・。

記述はそんなに難しくなく、以下を実行しました。
ファイル名は、フルパス表記で指定します。
今回は、phpMyAdminでエクスポートしたsql文をそのままインポートして問題ありませんでした。

mysql -u ユーザー名 -p DB名 < インポートファイル名
Enter password:パスワード

組織やグループ等の階層構造をDBから読み込み表示する例

組織、グループ、カテゴリ等、階層構造を持っているデータをデータベースから読み込み、HTMLで表示するPHPのサンプルです。
意外と頭を悩ませたので、覚書として残しておきます。
ネットを探しても、クエリをその都度発行して表示する例はありましたが、何度もクエリを発行するのは避けたかったので、配列に一回読み込ませたものを表示するようにしております。

サンプルソースは、組織の階層構造を表示する例です。

DBには、以下3つのフィールドがあります。

code：組織のコード
parent：組織の親コード
section_name：部門の名称

サンプルソース
・SQLにて階層構造のデータを読み込み
・配列に格納
・配列から階層構造を吐き出し

<?php
  db_connect();
  $result = mysql_query($sql);
  db_close();

  while($result_array = mysql_fetch_array($result)){
    $parent_code[$result_array['parent']][] = array($result_array['code'] => $result_array['section_name']);
  }
  mysql_free_result($result);

function display_tree($parent,$level){
  global $parent_code,$html;
  if(is_array($parent_code[$parent])){
    foreach($parent_code[$parent] as $section){
      foreach($section as $code => $section_name){
        if($code <> $parent and $code <> 0){
          $html .= str_repeat('  ',$level).$section_name.'
'."\n";
          display_tree($code,$level+1);
        }
      }
    }
  }
}

display_tree(0,0);
echo $html;
?>

phpMyAdminの進化に感動

久しぶりにphpMyAdminをダウンロードし、3.3.7にしたが、Excelから直接インポートできるのに感動した！！。

ExcelベースのデータをMySQLに移行する際に、日本語の文字コードの関係から、インポートしても文字化けする事が多く困っていたが、新しいバージョンに移行して、Excelから直接インポートできたので特に問題も起きなかった。

注意点としては、各シートごとにテーブルができるので、最小限のシートにしておくといいでしょう。
また、データベースの構造はインポートしたデータをもとに作成されるので、データのタイプや文字数、インデックスの設定はもちろん必要です。

それにしても、Webの時代になりましたね。
Excelも便利ですが、Excelを超えるツールが誕生するのも、もうすぐなのかもしれないですね。