概要

Windows10に開発・検証用にMySQL Community Server 5.7.19をインストールし、初歩的な設定を行うまでの作業メモです。
インストーラー版は使わずZIP Archive版で手動インストールします。

環境

Windows 10 Professional
MySQL Community Server 5.7.19 (zip archive)

参考

MySQL 5.7 Reference Manual / 2.3.5 Installing MySQL on Microsoft Windows Using a noinstall Zip Archive
MySQL 5.7 Reference Manual / 5.1.5 Server System Variables

MySQL Community Server インストール

アーカイブファイルのダウンロード

ダウンロードページよりアーカイブファイルをダウンロードします。
今回ダウンロードしたアーカイブファイルはmysql-5.7.19-winx64.zipです。

アーカイブファイルの展開

アーカイブファイルを適当な場所へ展開します。今回はD:\dev\mysql-5.7.19-winx64にしました。
次に下記ディレクトリを作成します。

D:\dev\mysql-5.7.19-winx64\data
D:\dev\mysql-5.7.19-winx64\logs
D:\dev\mysql-5.7.19-winx64\tmp

my.iniの準備

my-default.iniがアーカイブに同梱されていなかったので、5.6.xで使用していたiniファイルを参考に作成しました。（5.7.18よりmy-default.iniはパッケージに含まれなくなったようです）

D:\dev\mysql-5.7.19-winx64\my.ini

今回使用したmy.iniファイルは下記の通りです。デフォルト値を使う設定はコメントアウトしています。

[mysqld]
basedir = D:\\dev\\mysql-5.7.19-winx64
datadir = D:\\dev\\mysql-5.7.19-winx64\\data
tmpdir = D:\\dev\\mysql-5.7.19-winx64\\tmp
#plugin_dir = D:\\dev\\mysql-5.7.19-winx64\\lib\\plugin

#port = 3306
#server_id = 0
#connect_timeout = 10
max_connections = 30

#max_allowed_packet = 4194304
#table_open_cache = 2000
#table_definition_cache = -1

#key_buffer_size = 8388608

#default_storage_engine = innodb
#default_tmp_storage_engine = innodb

#default-time-zone = Asia/Tokyo
#default_password_lifetime = 0

#sql_mode = ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

#tx_isolation = REPEATABLE-READ
#autocommit = 1

#secure_file_priv = NULL
#lock_wait_timeout = 31536000

explicit_defaults_for_timestamp = 1

#lc_time_names = en_US


## innodb

#innodb_buffer_pool_size = 134217728
#innodb_file_per_table = 1
innodb_status_output = 1
innodb_status_output_locks = 1

#lower_case_table_names = 0


## log_bin

#log_bin = mysql-bin


## character-set

character_set_server = utf8
collation_server = utf8_general_ci


## logging

#log_output = FILE
general_log = 1
general_log_file = D:\\dev\\mysql-5.7.19-winx64\\logs\\general_query_all.log
#log_error_verbosity = 3
log_error = D:\\dev\\mysql-5.7.19-winx64\\logs\\mysqld_error.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_syslog = 0
log_timestamps = SYSTEM
long_query_time = 3
slow_query_log = 1
slow_query_log_file = D:\\dev\\mysql-5.7.19-winx64\\logs\\slow_query.log


[mysql]
default-character-set = utf8
show-warnings

prompt = "\u@\h [\d] > "

データディレクトリの初期化

initialize

D:\dev\mysql-5.7.19-winx64> bin\mysqld --defaults-file=D:\dev\mysql-5.7.19-winx64\my.ini --initialize

mysql serverの起動と接続

起動

startup

D:\dev\mysql-5.7.19-winx64> bin\mysqld --defaults-file=D:\dev\mysql-5.7.19-winx64\my.ini --console

rootユーザーでログイン

初回ログイン時のパスワードは一時パスワードを使用します。一時パスワードはerrorログファイルに出力されていました。

D:\dev\mysql-5.7.19-winx64> bin\mysql -u root -p
Enter password: "temporary password"

一時パスワードでログインして、パスワードを更新します。

> alter user 'root'@'localhost' identified by 'new_password';

停止

shutdown

D:\dev\mysql-5.7.19-winx64> bin\mysqladmin -u root -p shutdown

インストール後の環境確認、設定

mysqldのオプションの確認と設定値の出力
(mysqlが起動している状態で実行、結果が長いのでtextファイルに出力しています。)

option_dump

D:\dev\mysql-5.7.19-winx64> bin\mysqld.exe --defaults-file=D:\dev\mysql-5.7.19-winx64\my.ini --help --verbose > option_dump.txt

version

D:\dev\mysql-5.7.19-winx64> bin\mysqladmin.exe -u root -p version
Enter password: ********
bin\mysqladmin.exe  Ver 8.42 Distrib 5.7.19, for Win64 on x86_64
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.7.19-log
Protocol version        10
Connection              localhost via TCP/IP
TCP port                3306
Uptime:                 1 min 10 sec

Threads: 1  Questions: 3  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.042

mysqlshow

D:\dev\mysql-5.7.19-winx64> bin\mysqlshow -u root -p
Enter password: ********
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

不要アカウントの削除

今回のバージョンでは特に不要なアカウントは作られていなかったのでそのままにしています。

root@localhost [(none)] > SELECT User, Host FROM mysql.user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql.sys

予約済みアカウント

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html
sys schema objects now have a DEFINER of 'mysql.sys'@'localhost'. (Previously, the DEFINER was 'root'@'localhost'.) Use of the reserved mysql.sys account avoids problems that occur if a DBA renames or removes the root account.

mysql.session

予約済みアカウント

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-19.html
During data directory initialization or upgrade, MySQL now creates a 'mysql.session'@'localhost' reserved account. This account is used internally by plugins to access the server. It is locked so that it cannot be used for client connections. (Bug #25642343)

補足

JSON型

MySQL 5.7.8からJSON型をサポートしています。

CREATE TABLE IF NOT EXISTS json_test (
  id INT NOT NULL AUTO_INCREMENT,
  contents JSON NOT NULL,
  create_at DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id)
)
DEFAULT CHARSET = UTF8MB4;

なお、json型にはindexが貼れません。

> CREATE INDEX idx1_json_test ON json_test (contents);
ERROR 3152 (42000): JSON column 'contents' cannot be used in key specification.

代替策として、Generated Columnsを使うというものがあります。

Indexing JSON documents via Virtual Columns

テストデータ

insert into json_test (contents) values('{"color": "red", "status": "done"}');
insert into json_test (contents) values('{"color": "green", "shape": "rectangle"}');
insert into json_test (contents) values('{"color": "blue", "shape": "triangle", "status": "not started"}');
insert into json_test (contents) values('{"color": "red", "status": "in progress"}');
insert into json_test (contents) values('{"status": "waiting"}');
insert into json_test (contents) values('{"shape": "pentagon", "status": "waiting"}');
insert into json_test (contents) values('{"color": "green", "shape": "rectangle", "status": "not started"}');
insert into json_test (contents) values('{"color": "red", "shape": "hexagon", "status": "waiting"}');
insert into json_test (contents) values('{"color": "blue"}');

select * from json_test order by id;
+----+-------------------------------------------------------------------+---------------------+
| id | contents                                                          | create_at           |
+----+-------------------------------------------------------------------+---------------------+
| 19 | {"color": "red", "status": "done"}                                | 2017-09-14 00:56:17 |
| 20 | {"color": "green", "shape": "rectangle"}                          | 2017-09-14 00:56:17 |
| 21 | {"color": "blue", "shape": "triangle", "status": "not started"}   | 2017-09-14 00:56:17 |
| 22 | {"color": "red", "status": "in progress"}                         | 2017-09-14 00:56:17 |
| 23 | {"status": "waiting"}                                             | 2017-09-14 00:56:17 |
| 24 | {"shape": "pentagon", "status": "waiting"}                        | 2017-09-14 00:56:17 |
| 25 | {"color": "green", "shape": "rectangle", "status": "not started"} | 2017-09-14 00:56:17 |
| 26 | {"color": "red", "shape": "hexagon", "status": "waiting"}         | 2017-09-14 00:56:17 |
| 27 | {"color": "blue"}                                                 | 2017-09-14 00:56:17 |
+----+-------------------------------------------------------------------+---------------------+
9 rows in set (0.00 sec)

検索

select id, contents->"$.color"
  from json_test
 order by id;
+----+---------------------+
| id | contents->"$.color" |
+----+---------------------+
| 19 | "red"               |
| 20 | "green"             |
| 21 | "blue"              |
| 22 | "red"               |
| 23 | NULL                |
| 24 | NULL                |
| 25 | "green"             |
| 26 | "red"               |
| 27 | "blue"              |
+----+---------------------+
9 rows in set (0.00 sec)

または、JSON_EXTRACTを使用する

select id, JSON_EXTRACT(contents, '$.color')
  from json_test
 order by id;
+----+-----------------------------------+
| id | JSON_EXTRACT(contents, '$.color') |
+----+-----------------------------------+
| 19 | "red"                             |
| 20 | "green"                           |
| 21 | "blue"                            |
| 22 | "red"                             |
| 23 | NULL                              |
| 24 | NULL                              |
| 25 | "green"                           |
| 26 | "red"                             |
| 27 | "blue"                            |
+----+-----------------------------------+
9 rows in set (0.00 sec)

複数指定することもできる

select id, JSON_EXTRACT(contents, '$.color', '$.shape')
  from json_test
 order by id;
+----+----------------------------------------------+
| id | JSON_EXTRACT(contents, '$.color', '$.shape') |
+----+----------------------------------------------+
| 19 | ["red"]                                      |
| 20 | ["green", "rectangle"]                       |
| 21 | ["blue", "triangle"]                         |
| 22 | ["red"]                                      |
| 23 | NULL                                         |
| 24 | ["pentagon"]                                 |
| 25 | ["green", "rectangle"]                       |
| 26 | ["red", "hexagon"]                           |
| 27 | ["blue"]                                     |
+----+----------------------------------------------+
9 rows in set (0.00 sec)

検索条件の指定

select id, contents->"$.color"
  from json_test
 where contents->"$.color" IS NOT NULL
 order by id;
+----+---------------------+
| id | contents->"$.color" |
+----+---------------------+
| 19 | "red"               |
| 20 | "green"             |
| 21 | "blue"              |
| 22 | "red"               |
| 25 | "green"             |
| 26 | "red"               |
| 27 | "blue"              |
+----+---------------------+
7 rows in set (0.00 sec)

または

select id, contents->"$.color"
  from json_test
 where JSON_EXTRACT(contents, '$.color') IS NOT NULL
 order by id;

更新

JSON_SETを使用する

select contents from json_test where id = 27;
+-------------------+
| contents          |
+-------------------+
| {"color": "blue"} |
+-------------------+
1 row in set (0.00 sec)

update json_test
   set contents = JSON_SET(contents, '$.shape', 'triangle')
 where id = 27;

Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select contents from json_test where id = 27;
+----------------------------------------+
| contents                               |
+----------------------------------------+
| {"color": "blue", "shape": "triangle"} |
+----------------------------------------+
1 row in set (0.00 sec)

削除

JSON_REMOVEを使用する

update json_test
   set contents = JSON_REMOVE(contents, '$.shape')
 where id = 27;

Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select contents from json_test where id = 27;
+-------------------+
| contents          |
+-------------------+
| {"color": "blue"} |
+-------------------+
1 row in set (0.00 sec)

開発用のDB、アカウントの作成

データベース

CREATE DATABASE IF NOT EXISTS sample_db
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci
;

アカウント

CREATE USER IF NOT EXISTS 'test_user'@'localhost'
  IDENTIFIED BY 'test_user'
  PASSWORD EXPIRE NEVER
;

権限

GRANT ALL ON sample_db.* TO 'test_user'@'localhost';

テーブル

CREATE TABLE IF NOT EXISTS memo (
  id BIGINT AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  done BOOLEAN DEFAULT FALSE NOT NULL,
  updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (id)
) CHARACTER SET = utf8mb4, COLLATE utf8mb4_general_ci;

MySQL Community Server 5.7.19 - Installing on Windows 10

概要