Database/MySQL

MySQL

Database

CONTENTS

• MySQL（MariaDB）について
  • ローマルマシンへのインストール
  • レンタルサーバーにインストール
  • プレーンなサーバーマシンにインストール
• MariaDB導入事例
  • 準備
  • リポジトリの追加
  • インストールとサービスの起動
  • 初期パスワードの設定
  • 参考：rootユーザーのパスワード初期化手順
  • 接続と動作確認
• MySQL導入事例
  • 準備
  • リポジトリの追加
  • インストールとサービスの起動
  • 初期パスワードの変更
  • 接続と動作確認
• 文字コードについて
  • 文字コードの確認
  • 設定ファイルの変更
  • 確認作業
• phpMyAdmin で 管理
  • ダウンロードとセットアップ
  • 参考：phpMyAdmin の configファイル（必要に応じて変更）
  • ローカルで XAMPP を利用している場合の DBへの接続と操作
  • レンタルサーバー・外部サーバのDBへの接続と操作
  • ERR：mysqli 拡張がありません・・について
  • MySQL 8 で phpMyAdmin にログインできない場合
• Terminal 接続で管理
  • 接続と基本操作
  • データベースのバックアップとリストア
• MySQL(Mariadb)のデータ領域

MySQL（MariaDB）について

MySQLは、オープンソースのリレーショナル型データベース管理システム (RDBMS) の一つで、世界でもっとも普及しているものです。また、MariaDBは、MySQLをフォークして立ち上げられたプロジェクトが開発する派生版です。いずれもオープンソースとして開発されています。

• MySQL：https://www.mysql.com/jp/
• MariaDB：https://mariadb.com/

付記：MariaDB について
MySQLはOracle社によって開発やソースコード管理が行われているのに対し、MariaDBは、純粋なオープンソースプロジェクトです。MariaDBのシェアは、MySQLには及びませんが、多くのLinuxディストリビューションが MySQL に代わって MariaDB を採用している点、また一般ユーザーが使いやすいという点で、MariaDB は世界中で急速にシェアを拡大しています。基本的なコマンド・操作方法は同じなので、学習目的で個人で実装する場合は、MariaDBの方がいいかもしれません。

ローマルマシンへのインストール

個人のPCにインストールする場合、XAMPP パッケージの導入が最も簡単です（同梱されています）。 XAMPPについては、LocalServer のページにも記載しています。DBサーバーの起動は、インストール後に、Manager ツールから MySQL の起動を選択するだけです。

レンタルサーバーにインストール

レンタルサーバーでは、DBサービスそのものはほぼインストール済みです。管理画面のメニューにそってサービスを起動することになりますが、管理画面の仕様はサービスによって異なるので、データベースのセットアップに関する基本的な用語を理解していることが前提となります。

プレーンなサーバーマシンにインストール

遠隔のLinuxマシン（CentOS）に Database をインストールするには、手元のPCの Terminal からSSH接続して、すべての操作をCUI（コマンド入力）で行う必要があります。

以下、MariaDBの場合と、MySQL8の２つの導入手順のメモを紹介します。
あくまでもメモです。この作業は、OS、そのバージョン、導入するDBの種類・バージョンによってそれぞれ異なります。インストール後の設定ファイル（my.cnf）の扱いや、認証方法の変更など、仕様の変更もあるので、同じ手順でうまくとは限りません

付記
説明の行頭に # があるのは rootでログインしていることを意味します。

# コマンドライン

一般ユーザでログインした場合は、コマンドの前に sudo が必要になります。

$ sudo コマンドライン

MariaDB導入事例

フラットな状態で提供された Linux（CentOS7）環境に、ターミナルからSSH接続でMariaDBをインストールする方法を紹介します。SSH接続って何？ という方はこちら

準備

インストールの前に、サーバーの環境を確認します。

• すでにMySQLが存在していないか確認

  # yum list installed | grep mysql

• すでに存在している場合は、例えば以下のようにして削除

  # yum remove mysql-libs

• 同様にMariaの5系が存在していないかも確認

  # yum list installed | grep mariadb

• すでに存在している場合は、例えば以下のようにして削除

  # yum remove mariadb*

• 古い情報を削除

  # rm -rf /var/lib/mysql/

リポジトリの追加

MariaDBの10系をインストールするためにはMariaDB専用レポジトリの追加が必要になります。

• まず、以下の方法で [利用可能なパッケージ] に10系がないか確認

  # yum list maria*
  あればそのままインストール可能ですが、なければ以下で追加

• 10系のリポジトリの追加は以下の一行で可能です

  # curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

• インストール可能になったか確認
  以下のように 10系の候補が出てくればOK

  # yum list maria*
  
  読み込んだプラグイン:fastestmirror, langpacks
  Loading mirror speeds from cached hostfile
   * base: ftp.tsukuba.wide.ad.jp
  　 ：
  利用可能なパッケージ
  MariaDB-backup.x86_64　10.3.15-1.el7.centos　mariadb-main
  MariaDB-backup-debuginfo.x86_64　10.3.15-1.el7.centos　mariadb-main
  MariaDB-cassandra-engine.x86_64　10.3.15-1.el7.centos　mariadb-main
  MariaDB-cassandra-engine-debuginfo.x86_64　10.3.15-1.el7.centos　mariadb-main
  MariaDB-client.x86_64　10.3.15-1.el7.centos　mariadb-main
  　 ：

  
  

インストールとサービスの起動

• MariaDBをインストール

  # yum install MariaDB-server MariaDB-client

• インストールされたか確認

  # yum list installed | grep MariaDB
  MariaDB-client.x86_64　10.3.15-1.el7.centos　 @mariadb   
  MariaDB-common.x86_64　10.3.15-1.el7.centos　@mariadb   
  MariaDB-compat.x86_64　10.3.15-1.el7.centos　@mariadb   
  MariaDB-server.x86_64　10.3.15-1.el7.centos　@mariadb 

• インストールされた場所の確認

  # which mysqld
  /usr/sbin/mysqld

• サービスの開始

  # systemctl start mariadb

  参考：一般的なコマンド形式は以下

  systemctl [Unitコマンド] [Unit名]

• psコマンドでMariaDBの本体 mysqldが起動していることを確認

  # ps -aux | grep mysqld

• 自動起動の設定
  サーバーの再起動や停止の復旧時に、MariaDBが自動的に起動するように設定

  # systemctl enable mariadb

  
  

初期パスワードの設定

• MariaDBは、インストール直後、root での起動は No Pass で可能です。

  # mysql  ← これで起動
  MariaDB [(none)] >   となる

  一旦 quit

  MariaDB [(none)] > quit
  Bye

• 以下のコマンドでパスワード設定可能です

  # mysql_secure_installation
  ：
  Enter current password for root (enter for none): 

  ここでは Enter のみ

  OK, successfully used password, moving on...
  Setting the root password ensures that nobody can log into the MariaDB
  root user without the proper authorisation.
  Set root password? [Y/n] 

  ここで　Y

  New password:  新しいパスワードを入力
  Re-enter new password: もういちど入力
  Password updated successfully!

  以下、いろいろ尋ねられるが、基本すべて Y で完了
  一旦 quit します。

• 以下で PW の有効性を確認

  # mysql -u root -p

  参考：データベース一覧表示

  MariaDB[(none)]>show databases;

参考：rootユーザーのパスワード初期化手順

• MariaDB停止

  # systemctl stop mariadb

• 環境変数セット “–skip-grant-tables”という環境編集オプションを設定

  # systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

• 環境変数がセット出来たら、MariaDBを起動

  # systemctl start mariadb

• rootパスワードが変更出来たら、MariaDB停止

  # systemctl stop mariadb

• 先ほど設定した、”–skip-grant-tables”オプション解除。

  # systemctl unset-environment MYSQLD_OPTS

• MariaDB起動

  # systemctl start mariadb

• ログインの確認

  # mysql -u root -p

• 以上
  
  

接続と動作確認

• 接続（MariaDBの場合も、接続コマンドは mysql です）

  # mysql -u root -p

  以下のように表示されれば接続OKです。

  Welcome to the MariaDB monitor.  Commands end with ; or \g.   
  Your MariaDB connection id is 8                    
  ・・途中略・・  
  
  MariaDB [(none)]> 

• ユーザーの確認

  MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user;

• 以下、DB作成の事例
  • データベース（DB名：wordpress）をクリエイト

    MariaDB [(none)]> create database wordpress;

  • できたか確認

    MariaDB [(none)]> show databases;

• 接続を切る

  MariaDB [(none)] > quit
  Bye

MySQL導入事例

SSH接続で遠隔のCentOSに MySQL 8 をインストール

準備

インストールの前に、サーバーの環境を確認します。

• MariaDBがいないか確認＞存在した場合は削除する
  • MariaDBの存在確認

    # yum list installed | grep MariaDB

  • 何かあれば、例えば以下のようにして削除

    # yum remove mariadb-libs

• 古いものがあれば削除

  # rm -rf /var/lib/mysql/

  
  

リポジトリの追加

単にインストールコマンドを打っただけでは、欲しいバージョンのものが入らない場合があります。バージョンによってリポジトリ情報の追加が必要です。

# rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

• リポジトリリストの確認

  # yum repolist all | grep mysql

  リスト中で「有効」のものが採用される前提

  mysql80-community/x86_64  MySQL 8.0 Community Server 有効: 113

  
  

インストールとサービスの起動

• MySQL8 をインストール

  # yum install mysql-community-server

• MySQLを起動

  # systemctl start mysqld.service

• サービスの永続化（再起動時に自動起動します）

  # systemctl enable mysqld.service

  
  

初期パスワードの変更

• MySQL8 は、インストール直後、No Pass での root の起動はできません。

  # mysql  ← これでは起動できません。

  初期パスワードを変更して設定する作業が必要になります。

• 初期パスワードがつくられているので以下で確認

  # cat /var/log/mysqld.log | grep 'password is generated'

  ＞ 最後の部分に初期パスワードが表示される

• パスワードの変更
  以下のコマンドから初期設定を行う

  # mysql_secure_installation

• 以下の順で入力を求められますので、順番に入力
  • rootパスワードの入力。先に調べたデフォルトのrootユーザーのパスワードを入力
  • 新しく設定するrootユーザーのパスワードを入力
  • パスワードの確認 ＞もう１度入力
  • 匿名ユーザーを削除するか＞そのまま [ enter ]（匿名ユーザーを削除）
  • リモートからのrootログインを禁止するか聞かれる > お好きな方
  • testデータベースを削除するか　＞　削除でOK
  • 権限の変更を更新するか聴かれるので、そのまま [ enter ]（不要）

接続と動作確認

• 接続（ルートのパスワードで）

  # mysql -u root -p
  [Enter password:  パスワードを入力

• ユーザーの確認

  mysql> SELECT Host,User,Password FROM mysql.user;

• 以下、DB作成の事例
  • データベース（DB名：wordpress）をクリエイト

    mysql> create database wordpress;

  • できたか確認

    mysql> show databases;

• 接続を切る

  mysql> quit
  Bye

  
  
  
  

文字コードについて

日本語文字化け回避のため、DBが使用する文字コードは正しく設定されていることが必要です。以下、文字コードを確認・変更する方法です。

文字コードの確認

mysql> show variables like "chara%"; 

+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.10 sec)

• クライアント側の設定
  • character_set_client
  • character_set_connection
  • character_set_results
• サーバ側の設定
  • character_set_database
  • character_set_server
  • character_set_system
    
    

設定ファイルの変更

文字コードの変更を行うには、設定ファイルに指定を加える必要があります。
Linuxの場合 /etc/my.cnf が設定ファイルです。vi が使えない場合は、FTP でDLして、手元で編集して戻す・・という作業になります。
　my.cnf をエディタで開いて、[mysqld] [client] [mysql]それぞれ「default-character-set」の文字コードを変更、あるいは追記を行います。

• サーバ側の文字コード設定

  [mysqld]
     ：
  default-character-set=文字コード

• クライアント側の文字コード設定

  [client]
     ：
  default-character-set=文字コード

• コマンドラインクライアントの文字コード設定

  [mysql]
     ：
  default-character-set=文字コード

確認作業

• MySQLを再起動

  mysql> RESTART　（MySQL８からこれができるようになりました）

• コードが変更されたか再確認

  mysql> show variables like "chara%"; 

phpMyAdmin で 管理

phpMyAdmin は MySQL サーバーをブラウザから管理するためのDB接続クライアントツールで、PHPで書かれています。SQL の文法を知らなくてもデータベースに対する様々な操作が可能です（もちろん SQL を記述して実行することもできます）。

ダウンロードとセットアップ

• ローカルで XAMPP を利用しているケース
  すでに同梱されています。以下のアドレスで確認できるはずです。
  http://localhost/phpmyadmin/

• レンタルサーバーを利用するケース
  すでに置いてあるケースが大半です。サーバーの管理ページのメニュー項目に phpMyAdmin がないか確認してください。

• プレーンな外部サーバーを利用しているケース
  自分で設置する必要があります。
  • 以下の公式サイトから phpMyAdmin をダウンロード
    https://www.phpmyadmin.net/
  • FTP接続で phpMyAdmin フォルダをドキュメントルートにアップロード
    公開サーバーの場合、ドキュメントルートの phpMyAdmin というフォルダ名は攻撃を受けやすいので、別名に変更する方がよいでしょう。
  • 設定ファイルは　config.inc.php（以下参考）
    必要であれば、conig.sample.inc.phpをコピーして作成。
    
    

参考：phpMyAdmin の configファイル（必要に応じて変更）

<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
 * phpMyAdmin sample configuration, you can use it as base for
 * manual configuration. For easier setup you can use scripts/setup.php
 *
 * All directives are explained in Documentation.html and on phpMyAdmin
 * wiki <http://wiki.phpmyadmin.net>.
 *
 * @version $Id$
 */

/*
 * This is needed for cookie based authentication to encrypt password in
 * cookie
 */
$cfg['blowfish_secret'] = 'ここには適当な文字列を入れる'; 
 /* YOU MUST FILL IN THIS FOR COOKIE  UTH! */

/*
 * Servers configuration
 */
$i = 0;

/*
 * First server
 */
$i++;

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost または　サーバーアドレス';　←これ
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = '********';　　←これ
$cfg['Servers'][$i]['controlpass'] = '********';　　←これ
/* Advanced phpMyAdmin features */
以下コメントアウト 

/*
 * End of servers configuration
 */

/*
 * Directories for saving/loading files from server
 */
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

?>

ローカルで XAMPP を利用している場合の DBへの接続と操作

• 以下のアドレスにアクセスします。
  http://localhost/phpmyadmin/
  • データベースユーザー：root
  • パスワード：なし

• データベースの新規作成
  • 左袖のメニューから New をクリック
  • データベース名を（半角英数）で書きます。
  • 文字コードには utf8-general-ci を選択します。
  • 作成ボタンをクリック　＞　以上
    WordPress 等のCMSで利用する場合は、データベースを作成するだけでOKです。あとは CMS が操作します。

レンタルサーバー・外部サーバのDBへの接続と操作

• サーバー管理画面にメニューがある場合
  当該ボタンをクリック

• 自分で設置した場合
  http://（url）/（phpMyAdminを置いたパス）/

以下、上記と同様です。

ERR：mysqli 拡張がありません・・について

MySQLiとは MySQL improved extension の略で、PHPからMySQL/MariaDBを操作するインターフェースを提供するPHPの拡張機能です。システムにこれが無い場合は、追加でインストールする必要があります。

# yum -y install php-mysql

失敗するときは、以下のように正しいリポジトリを見に行くように指示。

# yum install --enablerepo=remi,remi-php73 php-mysql

有効化のため再起動が必要です。

# systemctl restart httpd.service

参考：リポジトリ選択の重要性
リポジトリを指定せずにインストールしようとすると、デフォルトで有効化されていないリポジトリからのインストールは行われません。リポジトリ情報は /etc/yum.repos.d にあって、それぞれ有効/無効が記載されています。有効化したリポジトリの xxxx.repo ファイルを編集し、enabled=1 にして実行することで、それができるようになります。

MySQL 8 で phpMyAdmin にログインできない場合

• MySQL 8 はそのままでは phpMyAdmin でログインできません。認証方法が変わっていて、phpMyAdminが対応していないので、前提としてMySQLの認証方法を以下のように書き換える必要があります。 コンフィグファイルは /etc/my.cnf です。以下の行、コメントを外して、mysql_native_password を有効にします。

  default_authentication_plugin=mysql_native_password

• すでにできてしまっているものは、mysql で認証方式の変更が必要です。まずは状態を確認します。

  mysql> SELECT user, host, plugin FROM mysql.user;
  +------------------+-----------+-----------------------+
  | user             | host      | plugin                |
  +------------------+-----------+-----------------------+
  | mysql.infoschema | localhost | caching_sha2_password |
  | mysql.session    | localhost | caching_sha2_password |
  | mysql.sys        | localhost | caching_sha2_password |
  | root             | localhost | caching_sha2_password |
  +------------------+-----------+-----------------------+

• phpMyAdmin に root でアクセスしようとしても、 caching_sha2_password になっているので、できません。そこで変更します。

  mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '（ここにパスワード）';

• 変更されたことを確認します。

  mysql> SELECT user, host, plugin FROM mysql.user;
  +------------------+-----------+-----------------------+
  | user             | host      | plugin                |
  +------------------+-----------+-----------------------+
  | mysql.infoschema | localhost | caching_sha2_password |
  | mysql.session    | localhost | caching_sha2_password |
  | mysql.sys        | localhost | caching_sha2_password |
  | root             | localhost | mysql_native_password |
  +------------------+-----------+-----------------------+

Terminal 接続で管理

ローカルマシンの場合も、リモートにSSH接続した場合も同様の操作です。

接続と基本操作

• データベースへの接続
  XAMPP 等でインストールした場合は、以下のコマンドで接続可能です

  $ mysql

  パスワードが設定されている場合は、以下のように入力し、その後、パスワードを入力することで接続が完了します。

  $ mysql -u root -p

• 以下のように表示されて、以後 DBコマンドが入力できます。

  MariaDB [(none)] > 

• データベースの新規作成

  MariaDB [(none)] >create database [DB名];

• 既存のデータベース一覧の表示

  MariaDB [(none)] >show databases;

• データベースユーザーの追加

  MariaDB [(none)] > create user [ユーザー名] identified by [パスワード];
  MariaDB [(none)] > grant all on [DB名].* to [ユーザー名];

• データベースの削除

  MariaDB [(none)] > drop database [DB名];

• 接続を切る

  MariaDB [(none)] > quit
  Bye

  
  

データベースのバックアップとリストア

• データベースのバックアップ

  $ mysqldump -u  [ユーザ名] -p  [DB名] > [バックアップ名（拡張子 .sql）]

• データベースのリストア（コマンドの違いに注意：こちらは mysql）

  $ mysql -u  [ユーザ名] -p [DB名] < [バックアップ名（拡張子 .sql）]

  注）上記、いずれも要求されるパスワードは DB利用者のパスワードです。
  
  
  
  

MySQL(Mariadb)のデータ領域

• デフォルトデータ領域は以下です。

  /var/lib/mysql

• Macにインストールした XAMPP でローカル環境を作っている場合は以下。

  /Application/XAMPP/xamppfiles/var/mysql/