MySQL Community Server 5.7.19 - Installing on Windows 10

# 概要

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] (https://dev.mysql.com/doc/refman/5.7/en/windows-install-archive.html)
* [MySQL 5.7 Reference Manual / 5.1.5 Server System Variables] (https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html)

# MySQL Community Server インストール

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

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

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

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

```text
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はパッケージに含まれなくなったようです）

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

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

```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] > "
```

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

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

## mysql serverの起動と接続

### 起動

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

**rootユーザーでログイン**

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

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

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

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

### 停止

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

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

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

```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
```

```text: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
```

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

### 不要アカウントの削除

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

```text
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型をサポートしています。

```sql
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が貼れません。

```sql
> 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] (http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/)


**テストデータ**

```sql
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"}');
```

```sql
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)
```

**検索**

```sql
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を使用する

```sql
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)
```

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

```sql
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)
```

**検索条件の指定**

```sql
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)
```

または

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

**更新**

JSON_SETを使用する

```sql
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を使用する

```sql
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、アカウントの作成

**データベース**

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

**アカウント**

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

**権限**

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

**テーブル**

```text
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;
```