LoginSignup
59
67

More than 3 years have passed since last update.

@Tocyukiin株式会社トラストバンク

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

Last updated at Posted at 2015-06-26

経緯

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

タイトル

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

  1. MySQLについて
  2. インストール
  3. 変数について
  4. 各種ログファイルについて
  5. 基本操作
  6. MySQLのセキュリティ
  7. ストレージエンジンについて
  8. 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

  • 以下をまとめて実行
  1. rootアカウントのパスワードを設定
  2. localhost以外からrootアカウントでのアクセスを無効化
  3. アノニマスユーザアカウントを削除
  4. testデータベース削除
  • MySQL5.6では、mysql_install_db実行時に--random-passwordが指定でき、 DB作成時に合わえて以下の操作を実行可能
  1. rootユーザにランダムなパスワードを設定し、パスワードを変更するまではrootユーザで何も実行できない（パスワードは$HOME/.mysql_secretファイルに記載）
  2. 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スクリプトファイルである為、テキストエディタで内容を確認可能

  1. ダウンロード

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

  1. インストール
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触ってない自分にとってはかなり濃い内容でした。まだ実際に全部コマンド確認してみてないので復習しつつ修正していくとこあれば修正していきたいと思います！

59
67
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
59
67