More than 3 years have passed since last update.

株式会社トラストバンク

MySQL入門　インストール・アーキテクチャ基礎編

Last updated at 2020-11-11Posted at 2015-06-26

#経緯
とある勉強会の内容の復習＆整理

#タイトル

インストール・アーキテクチャ基礎編　←今回はこちら
レプリケーション編
バックアップ編
チューニング基礎編

#インストール・アーキテクチャ基礎編アジェンダ

MySQLについて
インストール
変数について
各種ログファイルについて
基本操作
MySQLのセキュリティ
ストレージエンジンについて
Tips

1. MySQLについて

1.1. 歴史

1995年スウェーデンにてMySQL LAB設立
2005年 OracleがInnobaseOyを買収
2008年 SunMicrosystemsがMySQL LABを買収
2010年 OracleがSunMicrosystemsを買収

現在の最新バージョンは5.6で現在5.7が開発中

2. インストール

2.1. インストール方法(Linux/.rpmファイル)

必要なパッケージをrpmでインストール
事前にMySQL-shared-compat-5.6.rpmをインストールすることで、ライブラリの競合を解消できるケースあり

# yum -y install MySQL-shared-compat-5.6.252-1.el6.x86_64.rpm
# yum -y install MySQL-server-5.6.25-1.el6.x86_64.rpm MySQL-client-5.6.25-1.el6.x86_64.rpm

2.1.1. Tips

MySQL5.6をrpmでインストールした場合、rootのパスワードは自動的に設定され、後で変更する必要がある。

　−　パスワードは~/.mysql_secretファイルに記載されている
　−　パスワードを変更するまではrootユーザで何も実行できない

2.2. インストール方法(Linux/yum)

2.2.1. 各ディストリビューションに対応したyumリポジトリをダウンロード

2.2.2. リポジトリのインストール

# yum localinstall mysql-community-release-xxx.noarch.rpm

2.2.3. MySQLのインストール

# yum repolist enabled | grep "mysql*-community*"
# yum install mysql-community-server

2.2.4. MySQL起動/停止

起動

# service mysqld start

停止

# service mysqld stop

##2.3. インストール方法(Linux/.tarファイル)
###2.3.1. mysqlユーザ、mysqlグループを作成する

# groupadd mysql
# useradd -r -g mysql mysql

###2.3.2. .tarファイルを展開し、所有者をmysqlにする

# cd /usr/local
# tar zxvf /path/to/[mysql-VERSION-OS].tar.gz
# ln -s [full-path-to-mysql-VERSION-OS] mysql
# cd mysql
# chown -R mysql .
# chgrp -R mysql .

###2.3.3. mysql_install_dbを実行して、データベースを作成する

# scripts/mysql_install_db --user=mysql

###2.3.4. dataディレクトリの所有者をmysql、その他ディレクトリの所有者をrootに変更する

# chown -R root .
# chown -R mysql data

###2.3.5. 必要に応じて自動起動の設定を行う、環境変数PATHを設定する

# cp support-files/mysql.server /etc/init.d/mysql.server

#3. 変数について
##3.1. システム変数

MySQLサーバの設定は、システム変数で設定する

###3.1.1. 設定方法

オプションファイルで設定

Linux

my.cnf

Windows

my.ini

一時的な設定

セッション単位（LOCAL）、サーバ全体（GLOBAL）での変更が可能
※システム変数によっては、動的に変更できないものがある

SET [GLOBAL|SESSION] <variable>=<value>

###3.1.2. システム変数の確認方法

SHOW [GLOBAL|SESSION] VARIABLES LIKE '%<システム変数名>%';
SELECT @@global.<システム変数名>, @@session.<システム変数名>;

##3.2. ステータス変数

MySQLサーバの動作を監視するために、ステータス変数を確認する

###3.2.1. ステータス変数の確認方法

SHOW [GLOBAL|SESSION] STATUS LIKE '%<ステータス変数名>%';

###3.2.2. 特定のクエリ（SQL）について調査する場合

mysql> FLUSH STATUS; <クエリ実行>; SHOW STATUS;

###3.2.3. 定期的に確認する例

15秒間隔で、ステータス変数の差分のみ表示

mysqladmin -u -p ... ex -i 15 -r | grep -v '0'

#4. 各種ログファイルについて
##4.1. エラーログ

起動/停止やサーバサイドでのエラーに関連するログファイル
サーバの問題解析に必要な情報を出力
デフォルトで出力されている（デフォルトの出力先はOSにより異なる）

Windows : host_name.errファイル、イベントログ
Unix : MySQLサーバを起動したコンソール

##4.2. バイナリログ

発行されたクエリのうち、更新系のSQL文のみを記録しているログファイル
バイナリ形式で記録されているが、mysqlbinlogコマンドにてテキスト化が可能
デフォルトでは出力されていない（システム変数log-binを設定して出力）
レプリケーションで重要なログ

##4.3. 一般クエリログ

クライアントからの接続及び、実行されたすべてのSQL文を出力する
デフォルトでは出力されていない（システム変数general-logを設定して出力）
サーバ稼働中に出力の開始/停止を制御可能
OS上のファイル以外に、テーブルへも出力可能

##4.4. スロークエリログ

チューニングの際に便利なログ
実行時間が指定した時間以上のクエリを出力する
デフォルトでは出力されていない（システム変数log-slow-queriesを指定して出力）

long-query-time : 秒単位での指定（0.5と指定すれば500ms)
log-queries-not-using-indexes : インデックスを使っていないクエリをすべて出力

#5. 基本操作
##5.1. データベースの起動

起動時に--defaults-file=<my.cnfへのパス>を指定すると、オプションで指定したパラメータのみで起動させることができる

起動

# mysqld --defaults-file=<> &

停止

# mysqladmin -u root -p shutdown

##5.2. データベースへの接続

ユーザにパスワードが設定されていない場合

# mysql -u root

ユーザにパスワードが設定されている場合

# mysql -u root -p
Enter password:

リモートホスト、特定のポートに接続する場合

# mysql -u root -h 127.0.0.1 -P 3306 -p
Enter password:

##5.3. データベース（スキーマ）の作成

mysql> CREATE DATABASE world;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| world              |
+--------------------+
4 rows in set (0.00 sec)

mysql> USE world;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

##5.4. テーブルの作成

テーブルの作成

mysql> CREATE TABLE `Country` (
    -> `Code` char(3) NOT NULL DEFAULT '',
    -> `Name` char(52) NOT NULL DEFAULT '',
    -> `Population` int(11) NOT NULL DEFAULT '0',
    -> PRIMARY KEY (`Code`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

##5.5. データの挿入/参照/更新/削除

データの挿入

mysql> INSERT INTO `Country` VALUES ('ABW','Aruba',103000);
Query OK, 1 row affected (0.00 sec)

データの参照

mysql> SELECT Code,Name,Population FROM Country;
+------+-------+------------+
| Code | Name  | Population |
+------+-------+------------+
| ABW  | Aruba |     103000 |
+------+-------+------------+
1 row in set (0.00 sec)

データの更新

mysql> UPDATE Country SET Population=105000 WHERE Code='ABW';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

データの削除

mysql> DELETE FROM Country WHERE Code='ABW';
Query OK, 1 row affected (0.00 sec)

##5.6. ユーザ作成、権限付与

ユーザ作成

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

権限付与

mysql> GRANT SELECT, INSERT ON world.* TO 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

権限剥奪

mysql> REVOKE SELECT, INSERT ON world.* FROM 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

#6. MySQLのセキュリティ
##6.1. MySQLサーバのセキュリティ設定

以下の5つのアクセスレベルから構成される

NO	TYPE	DESCRIPTION
1	user	グローバルアクセス権
2	db	データベース毎のアクセス権
3	tables	テーブル毎のアクセス権
4	columns	カラム毎のアクセス権
5	procs	ストアドプロシージャとファンクション毎のアクセス権

デフォルト状態からセキュリティを向上させるスクリプト

$ mysql_secure_installation

以下をまとめて実行

rootアカウントのパスワードを設定
localhost以外からrootアカウントでのアクセスを無効化
アノニマスユーザアカウントを削除
testデータベース削除

MySQL5.6では、mysql_install_db実行時に--random-passwordが指定でき、
DB作成時に合わえて以下の操作を実行可能

rootユーザにランダムなパスワードを設定し、パスワードを変更するまではrootユーザで何も実行できない（パスワードは$HOME/.mysql_secretファイルに記載）
anomymousユーザを削除

##6.2. MySQL Enterprise Editionのセキュリティ強化機能

MySQL Enterprise Authentication（外部認証）

―　MySQL Serverのユーザ認証をLDAPやAD等と連携可能

MySQL Enterprise Encryption（非対称暗号化）

―　商用版のみの強度の高い暗号化関数
―　暗号化鍵と復号化鍵を別の鍵に出来る

MySQL Enterprise Firewall（SQLインジェクション対策）

―　アプリケーションを変更することなく、MySQL ServerだけでSQLインジェクション対策可能

MySQL Enterprise Audit（監査ログ取得）

―　ログオン/クエリの情報を監査可能
―　Oracle Audit Vaultとも連携可能

#7. ストレージエンジンについて
##7.1. ストレージエンジンとは

他のRDBMSに実装されていないMySQLだけの機能
ストレージエンジンの役割（ストレージエンジン毎に以下が異なる）

機能	差異部分
データ保管	どこに格納するか？、データレイアウト
インデックス	実装方法（Btree、B+、T、 etc）
メモリ利用	データキャッシュ、バッファリング
トランザクション	ACID、XA、MVCC、分離レベル
同時実行性	ロック、排他制御

テーブル単位でストレージエンジンを選択可能

指定しない場合は、システムのデフォルトストレージエンジンが使用される

##7.2. 代表的なストレージエンジン

ストレージエンジン	特徴・用途概要
InnoDB	MySQL5.5からのデフォルト、トランザクション対応、外部キー、圧縮
MyISAM	以前のデフォルト、トランザクション非対応、ファイルシステムの置き換え
MEMORY	高速ルックアップ、データ参照の頻度が高いアプリ向け
ARCHIVE	SELECTおよびINSERTのみ可能、圧縮、ロギング、監査など
NDBCLUSTER	非共有型Active-Activeクラスタ、トランザクション対応
CSV	SQLでフラットファイルデータ（CSVファイル）を参照
MERGE	MySQL5.1より前のパーティショニング的機能
FEDERATED	分散配置したデータに透過的にアクセス
EXAMPLE	エンジンを開発するためのベース
BLACKHOLE	書き込んだデータを全て飲み込む"/dev/null"的なエンジン

※InnoDBが現在のスタンダード

##7.3. InnoDB
###7.3.1. InnoDBの特徴

MySQL5.5からのデフォルトストレージエンジン
ACID特性に沿ったトランザクション処理・クラッシュリカバリ

ANSI/ISOで定義されている4つのトランザクション分離レベルを全てサポート

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ(デフォルト)
SERIALIZABLE

行レベルロック、読み取り一貫性、デッドロック検知
外部キー参照整合性のサポート
表の構造はClustered Index（≒索引構成表）

###7.3.2. InnoDBのファイル構成

共有テーブルスペースファイル：ibdata1,ibdata2,・・・

－　各種メタデータ、ロールバックセグメントなどが格納される
－　innodb_file_per_table=OFFの場合、実データも格納される

InnoDBログファイル：ib_logfile0,ib_logfile1,・・・

－　クラッシュリカバリの為のログファイル

InnoDBデータファイル：テーブル名.ibdファイル

－　テーブルに保持している実データが格納されているファイル
－　innodb_file_per_table=ON(MySQL5.6からのデフォルト値)の場合、テーブル単位でファイルが作成される

テーブル定義ファイル：テーブル名.frmファイル

###7.3.3. InnoDB関連のシステム変数
※ファイル構成、メモリサイズに関係する主要なシステム変数のみ

変数名	説明
innodb_buffer_pool_size	InnoDBバッファプールのサイズ
innodb_log_buffer_size	InnoDBログバッファのサイズ
innodb_data_file_path	InnoDBのデータファイル名やサイズを指定
innodb_file_per_table	有効（ON）にすると、テーブル単位で.idbファイルが作成される
innodb_log_files_in_group	ログファイルの数を指定（多くの場合デフォルトの2で問題無し）
innodb_log_file_size	ログファイルのサイズを指定

##7.4. ストレージエンジンの指定方法/確認方法
###7.4.1. ストレージエンジンの指定方法

テーブル作成時CREATE TABLE文で指定

CREATE TABLE t(i INT) ENGINE = InnoDB;

指定がない場合はシステムのデフォルトを利用

--default-storage-engine
@@storage_engine

ALTER TABLE文で変更可能

ALTER TABLE t ENGINE = MEMORY;

###7.4.2. ストレージエンジンの確認方法

テーブルに設定したエンジンの確認

SHOW CREATE TABLE City\G
or
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

メタデータ管理スキーマINFORMATION_SCHEMAにて確認

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'City' AND TABLE_SCHEMA = 'world'\G

#8. Tips
##8.1. サンプルデータベースのインストール

DBを作ってもデータが入ってないと意味無いですね！そんな時に便利なサンプルデータベースのインストール手順です。
world databaseがMySQL検収や認定試験の問題などでも利用されているサンプルデータベース

－　通常は、InnoDB versionをダウンロードして利用
－　実態はSQLスクリプトファイルである為、テキストエディタで内容を確認可能

ダウンロード

以下ページのExample Databases部分からサンプルデータベースをダウンロード可能

インストール

worldデータベースの作成

mysql> CREATE DATABASE world;
mysql> SHOW DATABASES;
mysql> exit;

sql流し込み

# mysql -u root world < world.sql
# mysql -u root

確認

mysql> show databases;
mysql> use world;
mysql> show tables;

##8.2. MySQLクライアントプログラム

名称	説明
mysql	MySQLサーバに接続してSQL等を実行するCUI
mysqladmin	MySQLサーバの管理作業を行うCUI（サーバ停止等）
mysqldump	MySQLサーバからデータを抽出できるCUI
mysqlimport	MySQLサーバにデータをロードするためのCUI
MySQL Workbench	公式GUIツール

MySQL Workbench

MySQLサーバの管理
MySQLデータベースを使った開発作業
MySQLデータベースの設計
他DBからMySQLへのデータ移行等に役立つ機能を搭載
商用版と比較してもほとんど差異なし
めっちゃ便利そう

##8.3. MySQL日本語マニュアルリリース

2015年6月からリリースしたとのことです。

#後記
入門編と言ってもほとんどMySQL触ってない自分にとってはかなり濃い内容でした。まだ実際に全部コマンド確認してみてないので復習しつつ修正していくとこあれば修正していきたいと思います！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

MySQL入門 インストール・アーキテクチャ基礎編