2020年9月1日 / 最終更新日時 : 2024年10月24日 officeの杜 windows

MySQLをインストールしてデータを集中管理しよう

オープンソースで無償利用可能、レンタルサーバの標準装備でもあるMySQL。一家に一台あって損はないと言えるデータベースサーバであるMySQLですが、中小企業に於いても脱Excelを図る上、またGoogleスプレッドシートのみに頼った手法よりワンランク上の環境となると、どうしても中央集権的なデータベースサーバが必要になります。

Google CloudでもCloud SQLというMySQLサービスが利用できる様になっております。今回Ubuntu Linux 20.04にMySQL 8.0をインストールしてみて、業務に利くノウハウをここにちょっと溜めてみよう思います。MySQL Serverは色々設定をしないと、rootでのログインすらままならない状況です。そんなMySQLをインストール時に躓くポイントをまとめました。

1 今回利用するアプリケーション
2 MySQLのインストールと設定
3 活用ノウハウ
4 関連リンク

今回利用するアプリケーション

今回は前回作成した仮想環境を利用しています。

MySQLのインストールと設定

インストール

macOSの場合

Homebrewを使ってインストールするのが最も手軽です。

brew install mysql

1	brew install mysql

インストールが完了したら自動起動しないので手動で起動が必要です。brewで起動指定すると次回以降自動で起動するようになります。

//手動で起動
mysql.server start

//次回以降自動起動
brew services start mysql

//手動で起動

mysql.server start

//次回以降自動起動

brew services start mysql

メンテナンスで停止したり再始動する場合には

//停止する場合
mysql.server stop

//再始動する場合
mysql.server restartそ

//停止する場合

mysql.server stop

//再始動する場合

mysql.server restartそ

そして、my.cnfの配置場所ですが、以下の場所に設定ファイルが配置されています。

//データの配置場所
/opt/homebrew/var/mysql

//設定ファイルの配置場所
/opt/homebrew/etc/.mycnf

//データの配置場所

/opt/homebrew/var/mysql

//設定ファイルの配置場所

/opt/homebrew/etc/.mycnf

Linuxの場合

インストールそのものはとても簡単で、ターミナルより以下のコマンドを入力して実行するだけ。

sudo apt install mysql-server mysql-client
mysql --version

1 2	sudo apt install mysql-server mysql-client mysql --version

Version表示がきちんと出たら、インストールは完了です。日常のMySQL稼働状況を確認するには、以下のコマンドを入力します。

sudo service mysql status

1	sudo service mysql status

また、メンテナンス等で、停止・再起動・開始、自動起動を必要とする場合には、以下のコマンドを入力

//再起動する場合
sudo service mysql restart

//停止する場合
sudo service mysql stop

//開始する場合
sudo service mysql start

//システム開始時に自動起動
sudo service enable mysql

//再起動する場合

sudo service mysql restart

//停止する場合

sudo service mysql stop

//開始する場合

sudo service mysql start

//システム開始時に自動起動

sudo service enable mysql

そして、Ubuntuの場合のMySQLの設定ファイルは以下の場所に配置されています（v24.04基準）。mysql.confでもmy.confでもないので注意が必要です。

//データの配置場所
/var/lib/mysql/

//設定ファイル保存先
/etc/mysql/mysql.conf.d/mysqld.conf

//データの配置場所

/var/lib/mysql/

//設定ファイル保存先

/etc/mysql/mysql.conf.d/mysqld.conf

図：インストール完了

図：緑色の粒が出たら正常稼働しています

設定関係

rootパスワード設定

完全な管理者であるrootのアカウントのみが存在していますが、パスワードが設定されていません。そこで、以下の順番でまずはrootのパスワードを設定します。その後、ユーザを追加しますがそちらはHeidiSQLで行うことにします。

ターミナルよりsudo mysql_secure_installationを実行する
Press y|Y for Yes, any other key for No:と聞かれるので、Yと入力
パスワード強度としてはMediumを選んでおけば良いでしょう。今回は1を入力（英語・数字・記号の3つで組み合わせる）
New Passwordと確認の為の再入力でパスワードを設定します。
Do you wish to continue ...と聞かれるので、Yを入力する
Remove anonymous users?と聞かれるので、Yを入力する
Disallow root login remotely?と聞かれるので、Yを入力する（localhostからのみ接続可になります）
Remove test database and access to it?と聞かれるので、Yを入力する
Reload privilege tables now?と聞かれるので、Yを入力する
All Doneと出て、これでrootのパスワード設定は完了です

ユーザの追加

ここから先はroot権限で暫く作業を行います。まずは、以下のコマンドでMySQLへとログインします。

sudo mysql -u root

1	sudo mysql -u root

ログインをするとプロンプトの表記がmysql>に変更されるので、ここからはMySQLのコマンドで作業を行います。rootで作業を続けるのはよろしく無いので、特権管理者を1名追加する事にしましょう。

CREATE USER 'ユーザー'@'ホスト' IDENTIFIED BY 'パスワード';

1	CREATE USER 'ユーザー'@'ホスト' IDENTIFIED BY 'パスワード';

ユーザは適当なログインユーザ名
ホストはデフォルトだとlocalhost。%を指定する事で、外部からの接続を許可出来ます。
パスワードは指定のパスワード強度による設定（今回はMediumに基づく:8文字の英語に数字と記号を加えたもの）
Query OKと出れば登録完了
Your password does not satisfy...と出たら、パスワード強度に達していないので、やり直しです。

また、以下のコマンドで現在登録済みのユーザ一覧を確認可能です。

select user,host from mysql.user;

1	select user,host from mysql.user;

図：ユーザを追加してみた

ユーザの権限変更

このままではただの一般ユーザなので、全データーベースを操作できる特権を与えます。以下のコマンドをターミナルから実行します。

GRANT all ON *.* TO 'ユーザ'@'ホスト';

1	GRANT all ON . TO 'ユーザ'@'ホスト';

Query OKが出れば権限設定変更完了です。以降はこの特権ユーザを利用して様々な設定変更や操作を行うようにします。また、以下のコマンドで設定されている権限を確認出来ます。

SHOW GRANTS FOR 'ユーザ'@'ホスト';

1	SHOW GRANTS FOR 'ユーザ'@'ホスト';

尚、通常行いませんが、root@localhostをroot@%に変更する場合には以下のような入力になります。権限追加したら、flush privilegesにて設定を反映させます。このコマンドは全権限をGRANT権限付きで与えるものなので、他のユーザに実行すると特権管理者になってしまうので要注意。

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
flush privileges;

1 2	GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION; flush privileges;

MySQL 5.7と8.0では特にユーザ作成や権限付与周りが厳格になっているので、古いドキュメントでは出来ない事が多いので要注意です

デフォルトのパスワード認証方式を変更する

MySQL 8.0よりパスワード認証方式に新たにcaching_sha2_passwordというものが追加され、デフォルトに設定されています。しかし、この方式の場合対応していないクライアントの場合、いわゆる素のパスワードでユーザを追加しても認証で弾かれてしまいます。そのため、ログインができずシステムが利用できなくなるケースがあります。

この設定を5.x系の設定であるmysql_native_passwordに戻す設定をこの場合はして置かなければなりません。以下の手順で設定を変更します。

ターミナルを開く
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnfを開く
一番最後の行に「default_authentication_plugin=mysql_native_password」を追記して保存する
mysqlを再起動する
ターミナルからsudo mysql -u rootでログインする
show variables like 'default_authentication_plugin';を実行する
valueがmysql_native_passwordになっていれば成功

これで、ユーザを追加したらその際に設定したパスワードですぐに接続が可能です。また、この設定の場合、MySQL Workbench上からはユーザの追加時にauthentication typeで「Standard」という表示が出てくるようになります。

※ちなみに、Amazon RDSのMySQL 8.0のデフォルトパスワード認証方式は、mysql_native_passwordのようです。

図：設定変更自体はテキストエディタで行う必要がある

図：Authentication TypeがStandardならばOK

パスワード強度を弱くする

デフォルトのパスワード強度であるMediumだと、運用が大変ということで、社内で運用するので強度を落としたいというケースがあります。その場合には、rootの権限でなければ変更することはできません。ターミナルを起動して、以下の作業を行います。

sudo mysql -u root
SET GLOBAL validate_password.length = 4;
SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.check_user_name = OFF;

sudo mysql -u root

SET GLOBAL validate_password.length = 4;

SET GLOBAL validate_password.policy = LOW;

SET GLOBAL validate_password.check_user_name = OFF;

最後に、SHOW VARIABLES LIKE 'validate_password%';を実行する事で、現在の設定を見ることが可能です。終了する時はquitで抜けられます。

尚、password.lengthを0にすると0以上でOKとなるので、パスワードなしでも可になります。ただしこのままだと再起動後に設定がデフォルトに戻るので、以下の設定をmysqld.cnfに記述して再起動すれば、恒久的に設定が有効化されます。

[mysqld]
validate_password.length=4
validate_password.policy=0
validate_password.check_user_name=0

[mysqld]

validate_password.length=4

validate_password.policy=0

validate_password.check_user_name=0

図：LOWになっていれば強度は一番下になります

外部接続の許可

サーバ上のユーザの権限上はOKでも、これでは外部から接続が出来ません。閉じた世界のサーバです。そこで、以下の手順で色々設定を変更してあげる必要があります。Ubuntu 20.04はufwなども標準で入っているのでなおのことです。

ファイアウォールの通信許可

UbuntuのファイアウォールであるufwにMySQLの通信ポートである3306番の許可を与えます。ターミナルより以下のコマンドを実行します。使っていない場合には設定は不要です。

sudo ufw allow 3306

1	sudo ufw allow 3306

設定状況を確認するには、ターミナルより以下のコマンドでアクティブかどうかも含めて確認可能です。

sudo ufw status

1	sudo ufw status

外部からの接続許可

以下の手順で設定ファイルの特定行をコメントアウトします。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
bind-address = 127.0.0.1の行を#でコメントアウトする。上書き保存実行。
ターミナルより、mysqlを再起動する

図：デフォルトはlocalhostからだけ接続可能

デフォルト文字コードを変更する

インストール直後だとデータベースを作った際のデフォルト文字コードが8.0より「utf8mb4_0900_ai_ci」です。このままでも使えるのですが、以下の設定でデフォルト文字コードを変更できます。以前はlatin1_swedish_ci（スウェーデン語）だったりして、この作業は必須でした。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に一行「character-set-server=utf8」を追加する
ターミナルより、mysqlを再起動する
データベース作成時の文字コードが「utf8_general_ci」に変更されてるのを確認できる
ターミナルからmysqlにログインし「show variables like '%char%';」にて、character_set_databaseの値がutf8になっていればOK

図：きちんと設定が変更されているか確認

図：テーブル作成時にもutf8のgeneralになってるのが確認できる

ポートフォワーディング

仮想環境で尚且つNATで運用している場合には、外部から仮想環境内のサーバへそのままではアクセスが出来ません。ブリッジの場合は外からサーバが見えるので、この設定は不要です。

今回はVMware Workstation Playerを使っているので、以下の手順でポートフォワーディングを有効化します。ホストOSはWindows10です。

ifconfigコマンドでゲストOSのIPアドレスを調べておく
C:\ProgramData\VMwareにあるvmnetnat.confをテキストエディタで開く
[incomingtcp]の行を探す
3306 = ゲストOSのIPアドレス:3306を下に追記する
コントロールパネル⇒管理ツール⇒サービスを開く
VMware DHCP ServiceとVMware NAT Serviceを再起動する
HeidiSQLなどの外部ツールなどから仮想環境上のMySQLへアクセス可能になる

アクセスする場合はホストOSのIPアドレス:3306で接続する事になります。ホストOSからは127.0.0.1:3306で接続出来ます。

図：ポートの転送がこれで可能になる

Node.jsで接続対策

Node.jsでpromise-mysqlなどのモジュールにてMySQL8.0へアクセスしようとすると、「Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client」とエラーが出て、接続が拒否されます。

これは5.7系⇒8.0系になるにあたって、MySQL側のパスワード認証方式に変更が加えられており、Node.jsのモジュール側が対応できていない為に発生します。これに対する対応策は以下の手順で、MySQL側に対象ユーザに対して、旧式のパスワード認証方式をアクティブにしてあげる必要があります。

ターミナルを起動して、sudo mysql -u rootでログイン
以下のコマンドを入力して反映させる
ターミナルを閉じる

ALTER USER 'ユーザ名'@'ホスト' IDENTIFIED WITH mysql_native_password BY 'パスワード'

1	ALTER USER 'ユーザ名'@'ホスト' IDENTIFIED WITH mysql_native_password BY 'パスワード'

MySQL側に登録されているアカウントのユーザ名とホスト（localhostや%）、そしてパスワードを当てはめて実行します。mysql_native_passwordが今回の対応策の部分です。

HeidiSQLの活用

CUIな人にとってターミナルからの各種テーブルの作成、権限追加などはお手の物であっても、そうではない人にとっては苦痛です。そこで使うのがMySQLのGUIクライアントであるHeidiSQL。公式にもMySQL Workbench 8.0 CEというものがあるにはあるのですが、ちょっと扱いにくい。このツールは軽量で高機能なので、管理のお供に最高です。ちなみに、macOS上のWineで起動して接続も出来ました。

以下の手順で設定を作り接続します。

接続設定

HeidiSQLを起動し、接続設定をまず作ります。以下の手順で作りましょう。

右下の新規をクリックして新規設定を追加します
ネットワーク種別は「MariaDB or MySQL(TCP/IP)」を選択
ホスト名はlocalhostを指定
設定した特権ユーザのユーザとパスワードを入力
ポート番号はそのまま3306でOK
保存をクリックする

あとはこの設定を選んで、開くを実行すると接続されてMySQLのテーブル情報やユーザ情報を操作することが出来ます。

図：テーブル画面が出たら成功

DB・テーブル作成

MySQLは初期状態だと管理用データベースがいくつかあるだけで、他のデータベースは存在していません。まずは、空のデータベースを作成しそれから、テーブルをその中に複数作っていく事になります。

HeidiSQLの左サイドパネル内の一番上の項目にて、右クリック⇒「新規作成」⇒「データベース」をクリック
名前をつけてOKボタンクリックでデータベースを作成できる
そのデータベース内のテーブル項目をク選択し、右クリック⇒「新規作成」⇒テーブルをクリック
名前を適当につける
下の「カラム」にて、列を作っていく。今回はまずIDとusernameだけのtesttableを作ります
右クリック⇒カラムの追加にて、カラム名はID、データ型はINTのままでOK。
作ったID列を右クリック⇒インデックスの作成⇒Primaryをクリック。Accessで言うところの主キーになります。
ID列のデフォルト列の値をダブルクリックし、Auto Incrementにチェックします（Accessで言うところのオートナンバーです）
６．と同じ作業でカラム名をUsername、データ型はVarcharを選び追加（Accessで言うところの短いテキスト型です）
保存を実行するとテーブルが作成されて保存される。

図：Accessよりも列作成は厳格です

新規ユーザ登録

ユーザの追加作業はターミナルで行うよりもHeidiSQLのほうが遥かに楽で、知識がなくとも担当者レベルでユーザ管理も行えるようになります。自動処理で追加や削除はターミナルのほうが楽ですが。。さて、この新規追加作業ですがHeidiSQLでは以下のような手順で行います。小規模組織ならば作成したデータベース内全部にアクセス権限つけても良いとは思いますが、ある程度の組織になった場合、テーブル単位でアクセス権限を切る必要もあります。このあたりも含めてユーザ登録します。

今回は予め、testdatabaseというデータベースを作成し、そこにtesttableというテーブルを作って置きました。

HeidiSQLのツールバーにある「ユーザ認証と特権の管理」という人間のアイコンをクリック
ユーザマネージャが起動するので、右上の「追加」をクリック
右サイドのユーザ名がログインIDになります。
デフォルトのホストがlocalhostになっているのですが、ここを%にして外部接続可にします。ウェブアプリ等の場合はlocalhostのままでも良いでしょう。
パスワードを入力。今回はMediumのパスワード強度なので、8文字以上の英語と数値+記号の組み合わせで登録します。
アクセス許可ですが、グローバル特権でチェックすると特権管理者になってしまいます。今回は一般ユーザで尚且testtableのみにアクセスをさせるのでこのままではNG
オブジェクトの追加をクリック
testtableを選択して、OKする。これでtesttableのみアクセスが可能になります。
追加されたデータベースを開くと、CREATEやらSELECTやらSQLのメソッドが出てきます。一般ユーザで最低限必要なのはSELECT, DELETE, INSERT, UPDATEの４つあれば十分なので、それだけにチェックを入れます。
保存をクリックするとこれでユーザの追加が完了します。

この作業は権限としてGRANT権限を持ったユーザでないと出来ません。

図：ユーザ追加がらっくらく

SQLのエクスポート

HeidiSQLではテーブルデータの塊をSQLファイルとしてエクスポートする事（もちろんインポートすることも）可能です。定期的なバックアップなどはNode.jsのNodeCron等よりターミナルから定期的にバックアップコマンドを投げるように仕掛けておけば、良いと思いますが手動で取っておきたい場合にはHeidiSQLが楽です。

HeidiSQLにて全体、特定データベースもしくはテーブルを右クリック
データベースをSQLとしてエクスポートをクリック
テーブルツールが起動します。デフォルトではドロップにチェックが入っていませんが、これはインポート時にすでにある同名のテーブルがあった時に削除するかどうかのオプションになります。
おなじく作成のオプションにはチェックが入っていますがこれは、インポート時にテーブルが無い場合作成するかどうかのオプションになります。
データは削除して挿入のままで良いでしょう。
最大Insertサイズはデータベースのサイズに合わせてお好みで調整
出力は単一の.SQLファイルでOK
保存先を指定して、エクスポートを実行するとSQLファイルというテキストファイルの形式でデータがエクスポートされます。
インポート時はファイル⇒SQLファイルを読み込みを実行
ツールバーのSQLを実行をクリックするとデータが流し込まれます。

図：エクスポートの画面

SQLのインポート

前述のエクスポートで出力したものをインポートする手順です。新しいMySQL環境に対してインポートをします。前述で「テーブルをドロップ」や「Create」にチェックが入っていない場合、データのみのInsertとなるため、あらかじめ新MySQLサーバに対象のDBとテーブルが用意されていないとエラーになってしまいます。

今回テーブルは用意しておき、前述でデータだけのSQLをエクスポートしておいたものをインポートしてみました。注意点として、HeidiSQLで対象のDBのデータを開いたままにしておくとエラーとなるので要注意。

対象となるDBをクリックしてから、メニューからファイル→SQLファイルを読み込みをクリック、エンコードを今回はUTF-8で指定。そのまま読み込むと警告文が出るので了解しすすめるとSQLが実行されて対象のDBにたいしてデータが次々とインサートされていきます。

図：インポート中の様子

macOSでも動かしてみた

OracleのMySQL Workbenchといったマルチプラットフォームのツールもあるのですが、個人的にWindowsの頃から使ってるHeidiSQLがお気に入りなので、macOSでもCrossOver Macを使って活用しています。

問題なく動作しますし、フォント的にも問題なく。

他にもA5:SQL Mk-2といったツールがありますが、Wineで動作が確認とれてるという点からもmacOSでのMySQL操作用アプリとしてオススメです。直近でも、Compute Engine上で立てたMySQLから、macOS上にSQLデータの引越しで活躍してくれました（6GB程度のデータですが、バッチリでした）。

図：シンプルで使いやすいのでmacOSでも使ってます

macOSはWineを活用するともっと便利になる

MySQLの設定変更

さて、ここまでで既に、MySQLを使っての社内データベース基盤は用意できました。必要に応じて更にオプション設定や自動バックアップの準備などをしておくと尚良いでしょう。

確かにこれで基盤は用意できましたが、小規模ならこれで問題ありませんが、ある程度の規模で利用する場合、デフォルトの設定のままだとパフォーマンスが悪かったり、問題が発生した時にトレースが出来ません。そこで、mysqld.cnfにある設定を変更します。

スロークエリのログを出すようにする

スロークエリとは、実行にやけに時間の掛かってる重たいクエリを見つける為の設定で、デフォルトではオフになっています。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
以下の設定を追記する
ターミナルより、mysqlを再起動する

slow_query_log = 1
slow_query_log_file = /tmp/mysql-slow.log
long_query_time = 5

slow_query_log = 1

slow_query_log_file = /tmp/mysql-slow.log

long_query_time = 5

slow_query_log_fileは、ログファイルの出力場所
long_query_timeは何秒以上掛かってるクエリなのかを秒数で指定
slow_query_logはONで出力がなされます。

以下のMySQLのコマンドで設定状況を確認可能です。

show variables like '%slow%'

1	show variables like '%slow%'

最後のコマンドで現在のスロークエリの設定を確認可能です。

図：slow_query_logがONになってるのを確認

タイムアウト時間の変更

MySQLは接続してきたクライアントとの接続をデフォルトでは8時間もの間維持してしまいます。これはちょっと長過ぎるのとそのままにしておくと、コネクションがどんどん溜まっていき、サーバ負荷増大に繋がります。以下の行を追加し、wait_timeoutの値をセットします。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に一行「wait_timeout=15」を追加する
ターミナルより、mysqlを再起動する

これで、15秒間接続が維持されそして破棄されます。但しこのままだと毎回スレッドを作成しては作業をして破棄するというプロセスが動きまくることになるので、合わせて次項の設定も追記しましょう。

スレッドキャッシュサイズの変更

スレッドキャッシュはスレッドを作っては捨てるではなく、作ってキャッシュしておき別のクライアントからの接続時に利用するもので、デフォルトでは無効になっています。wait_timeoutなどを短めに設定している場合には合わせて設定が必要になります。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に一行「thread_cache_size = 100」を追加する
ターミナルより、mysqlを再起動する

これで100本分キャッシュしますので、足りないようであればこの値を少しずつ増やしていくと良いでしょう。設定内ではコメントアウトの形で入っていたりするので、コメントアウト外して100を設定しても良いでしょう。

クエリキャッシュサイズの変更

クライアントから実行されるクエリですが、例えば毎度同じようなクエリを実行するケース（変動の少ないマスタテーブル等や読み取り専用のレプリカ）では毎回取得しにいっては返すではパフォーマンスが悪くなります。そこで、このクエリキャッシュを設定し、キャッシュから返せばその分レスポンスが向上します。しかし、大きければ良いというわけでもないので、概ね256MB～512MBで設定（例えばRAM4GBのマシンで）するケースが多いです。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に一行「query_cache_limit=16M」を追加する
末尾に一行「query_cache_size=512M」を追加する
末尾に一行「query_cache_type=1」を追加する
ターミナルより、mysqlを再起動する

変動の多いテーブルが多い場合、クエリキャッシュのヒット率が下がって逆にパフォーマンスが下がることもあるので、場合によりけりです。

※但しこの設定は、MySQL8.0からは利用できないです。設定してしまうとMySQLが以下のエラーを吐いて起動しなくなります。MySQLの5.6以前までのパフォーマンス・チューニングとしてよく利用されていたものですが、設定してはいけません。

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

1 2	Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

innodbをテーブル単位で区切るようにする

MySQLは現在デフォルトでは、innodbを利用していますが、データベースの単位で区切られているものをテーブル単位にする事でパフォーマンスが向上する場合があります。この設定はテーブルを作る前に行わないと反映されないので、既に作ってしまってる場合にはテーブルを作り直してあげないとテーブル単位になりません。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に一行「innodb_file_per_table=ON」を追加する
ターミナルより、mysqlを再起動する

innodbのバッファプールサイズを変更する

innodbのデータとインデックスをどれだけメモリ上でキャッシュしておくかを設定する項目で、そのマシンがMySQL専用である場合には概ねマシンの搭載RAM容量の80%を指定という話もありますが、専用ではないまたは潤沢にRAMが搭載されているわけではない、利用する規模が小さめなどそれぞれケースに応じて設定する必要があります。

今回の仮想マシンは4GBのRAMを割り当ててますが、専用として使ってるわけじゃないので、1GB程度を割り当てることにしました。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に一行「innodb_buffer_pool_size=1G」を追加する
ターミナルより、mysqlを再起動する

innodbの更新ログのサイズ指定

innodbの更新ログはどんどん溜まりますが、そのログのサイズは無制限だとディスク容量が食い尽くされてしまう日がいつか来ます。設定基準はおよそinnodb_buffer_pool_sizeの1/4とのことなので、今回は256MB割り当てることにしました。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に一行「innodb_log_file_size=256M」を追加する
ターミナルより、mysqlを再起動する

メモリーテーブルの最大サイズを抑止する

メモリ上にテーブルを読み込み普段はそこから読み書きを実現するとパフォーマンスの向上になりますが、マシンのリソースをそれだけ消費します。この設定を加えることでその最大サイズを抑止することが可能です。超えた場合にはディスク上に作成され読み書きスピードは落ちます。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に一行「max_heap_table_size=16M」を追加する
末尾に一行「tmp_table_size=16M」を追加する
ターミナルより、mysqlを再起動する

スレッドバッファ関係の値

ソートやインデックスの有無でのスキャンなどで利用するバッファサイズで、小さめに設定するのが定石になっています

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に以下の設定を追加する
ターミナルより、mysqlを再起動する

read_buffer_size = 1M
sort_buffer_size = 1M
read_rnd_buffer_size = 4M

read_buffer_size = 1M

sort_buffer_size = 1M

read_rnd_buffer_size = 4M

パケットサイズの変更

巨大なデータを送る場合、MySQLのデフォルト設定では1Mで設定しているため、制限に引っかかってしまうことがあります。その場合には最大16MBまでこのサイズを拡張する事が可能です。

/etc/mysql/mysql.conf.d/に存在する「mysqld.cnf」をエディタで開く
末尾に一行「max_allowed_packet=8M」を追加する
ターミナルより、mysqlを再起動する

活用ノウハウ

Accessから接続して利用する

大昔から存在し、そして現在も定番のやり方の一つがクラサバ。MySQLサーバをバックエンドのデータ置き場とし、クライアント側にAccessを利用してアプリケーションを構築する技法です。Access自身がファイルサイズ最大2GBなどのいろいろな制限があったりして、そろそろ列数やクエリ数などで限界が来てるのは事実ですが、中規模なアプリケーションであれば、これがもっとも高速に開発が出来る手法になります。

AccessとMySQL 8.0を接続させるためにはODBCドライバとその設定（DSNと言う）が必要です。以下の手順で作成したサーバに接続してみます。

あらかじめ対象のクライアントにMySQL ODBC Connector 8.0をインストールしておく
タスクバーの検索窓よりODBCで検索し、今回は64bitのほうを選びます。
ODBCアドミニストレータ64bitが起動するので、ユーザDSNにて追加をクリック
MySQL ODBC 8.0 Unicode Driverを選択します
DataSource Nameは分かりやすい名前をつけます。
TCP/IP Serverは今回は仮想環境なので127.0.0.1やlocalhostを入力
Userとpasswordを入れて、テストをクリックしSuccessfullyと出れば成功
下のDatabaseでは使用するDBを選びます。今回は作っておいたtestdatabaseを指定
但し今回wait_timeoutを15秒でセットしてしまってるので、これだけではすぐ接続できなくなります。
そこで、Detailをクリックする
Connectionにある「Enable Automatic Reconnect」にチェックを入れましょう。

図：ODBC設定画面

これでユーザDSNが出来たので、Access側でこれを利用してテーブルをリンクさせます。

リボンの外部データにある新しいデータソース⇒他のソースから⇒ODBCデータベースを選択
リンクテーブルを作成してソースデータにリンクするにチェックを入れて、OKをクリック
コンピュータデータソースのタブをクリックし、自分が作ったDSNを選びOKをクリック
テーブル一覧が出てくるので、選択しOKをクリック
リンクテーブルが作成されて、ローカルテーブルであるかのように扱えます

図：MySQLサーバデータの読み書きがこれで可能になります

独自ツールを構築する

AccessなどのOffice製品の場合、利用者全員のPCにODBC Connectorをインストールした上に、DSN設定をしてあげなければなりません。ある程度の規模の企業になるとこの辺りはWSUSなどがなければ、一個ずつ手作業で行う必要があります。負担になりますね。かといって、各エンドユーザにやってもらうというのも不毛な作業ですし、間違えて問い合わせが乱発しかねません。

自分の場合これまでこのケースで独自のツールを社内で必要とした場合には、以下のような形でより簡便に実現させました。独自ツールを作る上でMySQLは低コストで高速に環境を構築可能です。

ElectronでMySQLへ接続するアプリを作る

Access + Node.js + MySQLでリモート接続

前者のElectronはNode.jsを基盤に作られており、ODBCではなく直接Promise-mysqlなどのモジュールが接続関係を実現してくれます。プログラムに組み込んでリッチなHTML5なアプリケーションを作成する事が可能です。これで社内のフリーアドレスの問題点である誰がどこに座ってる問題などを解決すべく座席表アプリを作成現在使ってもらっています。

後者はクライアント側はこれまで通りのAccessを利用しますが、DSNやODBC Connectorを利用せずにサーバ側でNode.jsのサーバプログラムを稼動。REST APIを用意してそれを介して、Access⇔MySQLの読み書きを実現しています。またこの方法の場合、ODBCを使った場合に生じるデータ量が多いがためにスクロールがもたつくであったり、クライアント側でNode.jsを使うようなこともないので、開発スピードも確保可能です。但し、リンクテーブルという形で接続するわけではないのと、VBA上でJSONを扱う、メモリ上で基本処理を行うなどの高度なテクニックが必要になります。

WordPressを利用し社内マニュアル構築

概要

オープンソースで高度なウェブサービスを構築するアプリケーションの多くが、MySQLをバックエンドに利用しています。最近でこそMongoDBなどをバックエンドに利用してるものも存在しますが。

その中でも無償で利用が出来、もはや世界標準であるブログサービスがWordPressです。業務ではこれを導入しオンラインマニュアルや就業規則、社内イントラとして簡単に構築する事が可能です。特にマニュアル関係はExcelやらWordやらに依存しすぎて、NASのどこに最新があるのか？そもそも人によって版の違うマニュアルを使ってる、あげくにはそのまま放置プレイし作り直す、といった業務マニュアルの様々な問題点がWordpressで解決可能です。

構築する為にはApacheウェブサーバー、PHPが必要になるので別途インストールが必要です。ApacheやPHPの導入については過去にも以下のエントリーで紹介しています。ApacheはPort80を使うのでポートフォワーディングでゲストOSのPort80に転送するように、vmnetnat.confに転送設定を追加しておくのを忘れずに。自分の場合、ホストの3500番ポートをゲストの80番ポートに転送しています。

UbuntuにphpMyFAQをインストールしてみる

図：社内マニュアルはWordpressで決まり

ApacheとPHPをインストール

ここでは特別なチューニングをせず動かせるようにする為に最低限必要なWebサーバおよびPHPのインストールをしておきます。以下の手順でターミナルからインストールを行います。

sudo apt update
sudo apt install apache2
sudo apt install -y php
sudo apt install php7.4-fpm php7.4-mysql php7.4-mbstring php7.4-zip php7.4-xml
sudo apt install php-curl
sudo apt install php-gd

sudo apt update

sudo apt install apache2

sudo apt install -y php

sudo apt install php7.4-fpm php7.4-mysql php7.4-mbstring php7.4-zip php7.4-xml

sudo apt install php-curl

sudo apt install php-gd

/var/wwwがドキュメントルートになりますので、ゲストOS側でIPアドレス:80番（http://localhost:80など）でアクセスすると、Apacheがきちんと動いてるかどうかわかります。このフォルダにwpというフォルダを作ってWordpress一式を自分は入れています。wpをドメイン直下にしたい場合には、/etc/apache2/site-available/000-default.confがDocumentrootの設定ファイルになります（この設定関係のファイル、apache2のバージョンによってあっちこっち違うのなんとかならないのか？apache2.confだったりhttpd.confだったり）。

12行目にDocumentrootとあるので自分の場合は

DocumentRoot /var/www/wp

1	DocumentRoot /var/www/wp

と記述し、ターミナルからsudo service apache2 restartで再起動して反映しました。

図：apacheのデフォルトページが表示されたら成功

データベースを作っておく

WordPressで利用するデータベースを予め、HeidiSQLで作っておくと良いでしょう。テーブルはWordressが勝手に作ってくれるので、空のデータベースを作るだけでOKです。同時にこのデータベースにアクセスする専用のアカウントをMySQL側に作っておくと良いですね。一般ユーザではなくテーブルなども作る必要があるので、特権アカウントよりちょっと厳しい制限の権限で作ると良いです。

今回はmanualという名前のデータベースを作りました。

WordPressのセットアップ

現時点で最新版のWordPressは5.5がリリースされています。以下の手順でセットアップしましょう。

WordPressから最新版のWordpressのZIPファイルをダウンロードする
ターミナルより、sudo nautilusにてファイラーを起動し、/var/www/htmlまで移動する
wwwの中に今回はwpというフォルダを作り、ZIPの中身をここに解凍する
wp-config-sample.phpをコピーして、wp-config.phpとする
wp-config.phpをテキストエディタで開く
WordPress のためのデータベース名には、自分で作成したデータベース名を入れる（今回ならばmanual）
MySQL データベースのユーザー名には、MySQLにアクセスさせるユーザ名
上書き保存して閉じる
自分はlocalhostの3500をゲストに転送してるので、http://localhost:3500/wp/でアクセスしてみる
WordPressのセットアップ画面が出てくるので、サイト名やWordpressにログインするユーザアカウントなどをセットアップ
WordPressをインストールをクリックする
成功しましたと出たら、MySQLに自動的にテーブルが構築されているので、あとはWordpressにプラグインやテーマ、そしてマニュアルをゴリゴリ作成してけばOKです。
管理画面へのログインはhttp://localhost:3500/wp/wp-adminにてアクセスするとログインが可能です。

図：皆で共有し皆で作成できるスグレモノです

WordPressを社内でプロキシ越しに使う場合の追加設定

WordPressは社内で使うには実は色々と問題点があり、公式に修正されていない問題のせいで、Version 5.2.4以降プラグインや自動アップデートが使えなくなるというものがあります。また、社内プロキシを超える為の設定は様々なブログで記述があるのですが、これだけでは足りないのです。

wp-config.phpに追記

ここでは、プロキシの設定およびFTPがどうたら問題を解決する為の設定を追加しています。まず、プロキシの通過設定は以下の通り。wp-config.phpの一番最後に記述します。

/** Proxy setting */
define("WP_PROXY_HOST", "http://プロキシURL");
define("WP_PROXY_PORT", "プロキシのポート番号");
define('WP_PROXY_USERNAME', '');
define('WP_PROXY_PASSWORD', '');
define("WP_USEPROXY", "TRUE"); 
define("WP_PROXY_BYPASS_HOSTS","localhost");

/** Proxy setting */

define("WP_PROXY_HOST", "http://プロキシURL");

define("WP_PROXY_PORT", "プロキシのポート番号");

define('WP_PROXY_USERNAME', '');

define('WP_PROXY_PASSWORD', '');

define("WP_USEPROXY", "TRUE");

define("WP_PROXY_BYPASS_HOSTS","localhost");

これで、プラグイン情報などが取得できるようになります。ただ、インストールをしようとすると、FTPアカウントがどうたらといった謎のダイアログが出てしまい、詰みます。そこで、以下の設定を記述しますが、90行目付近にある「if ( ! defined( 'ABSPATH' ) ) {」よりも上に記述が必要です。

define('FS_METHOD', 'direct');

1	define('FS_METHOD', 'direct');

class-snoopy.phpの修正

前項の記述だけではたりないので、今度はwp-incluedsフォルダ内にあるclass-snoopy.phpをテキストエディタで開きます。このファイルの46行目付近にある$proxy_hostなどに値を追加します。この設定は、Wordpressをアップデートすると上書きされて消えてしまうので、アップデートのたびに再設定が必要です。

var $proxy_host = WP_PROXY_HOST;
var $proxy_port	= WP_PROXY_PORT;

1 2	var $proxy_host = WP_PROXY_HOST; var $proxy_port = WP_PROXY_PORT;

さらに、117行目付近にある設定も修正しておきます

var $_isproxy = WP_USEPROXY;

1	var $_isproxy = WP_USEPROXY;

http.phpの修正

前項までの設定は各種ウェブサイトでも紹介されてる、プロキシー超えの設定なのですが、超えられるだけでプラグインのインストールが出来ない場合があります。しかしその原因はWordpressでChangeset 46475にて変更されてしまった部分。これが原因でプラグインをインストールできず「インストールに失敗しました: ダウンロードに失敗しました。有効な URL ではありません」が出て、プラグインのインストールもWordpress自体のアップデートも出来ないという間抜けな事になってしまっています。

ウェブを漁ってみたところ、#48432にてバグ報告がなされており、以下のような修正を行うとプロキシ越しにプラグインのインストールやアップデートができるようになりました。かなり困ってる人も多いと思いますが、未だにWordpress側は対応しようとしてはいません。

この設定は、Wordpressをアップデートすると上書きされて消えてしまうので、アップデートのたびに再設定が必要です。

wp-includesを開く
中に存在するhttp.phpをテキストエディタで開く
552行目付近に移動する
if文で$ip = $hostを比較してる部分に於いて、Changeset 46475の内容を元に戻すように記述をし直す
具体的には、return falseをコメントアウトし、$ip = false;を追加するようにします
改めて、プラグインをインストールを実行してみたところ、普通にインストールができるようになりました。

$ip = gethostbyname( $host );
if ( $ip === $host ) { // Error condition for gethostbyname().
	$ip = false;
	/* return false; */
}

$ip = gethostbyname( $host );

if ( $ip === $host ) { // Error condition for gethostbyname().

$ip = false;

/* return false; */

}

図：無事にプロキシ越しにプラグイン追加が出来ました