Edited at

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 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ログファイルに出力されていました。


login

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を使うというものがあります。

テストデータ

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;