#author("2023-11-14T13:53:32+09:00","default:inoue.ko","inoue.ko") #author("2023-11-16T10:11:45+09:00;2023-11-14T13:53:32+09:00","default:inoue.ko","inoue.ko") *MySQL [[Database]] ~ ***CONTENTS #contents2_1 ~ **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>Google:XAMPP]] パッケージの導入が最も簡単です（同梱されています）。 XAMPPについては、[[LocalServer]] のページにも記載しています。DBサーバーの起動は、インストール後に、Manager ツールから MySQL の起動を選択するだけです。 ~ ***レンタルサーバーにインストール レンタルサーバーでは、DBサービスそのものはほぼインストール済みです。管理画面のメニューにそってサービスを起動することになりますが、管理画面の仕様はサービスによって異なるので、データベースのセットアップに関する基本的な用語を理解していることが前提となります。 ~ ***プレーンなサーバーマシンにインストール 遠隔のLinuxマシン（CentOS）に Database をインストールするには、手元のPCの [[Terminal]] から[[SSH接続>SSH]]して、すべての操作をCUI（コマンド入力）で行う必要があります。 ''以下、MariaDBの場合と、MySQL8の２つの導入手順のメモを紹介します。'' &color(red){あくまでもメモです。この作業は、OS、そのバージョン、導入するDBの種類・バージョンによってそれぞれ異なります。インストール後の設定ファイル（my.cnf）の扱いや、認証方法の変更など、仕様の変更もあるので、同じ手順でうまくとは限りません}; ''付記'' 説明の行頭に # があるのは rootでログインしていることを意味します。 # コマンドライン 一般ユーザでログインした場合は、コマンドの前に sudo が必要になります。 $ sudo コマンドライン ~ ~ **MariaDB導入事例 フラットな状態で提供された Linux（CentOS7）環境に、ターミナルからSSH接続でMariaDBをインストールする方法を紹介します。SSH接続って何？ という方は[[こちら>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 CENTER:'''その後、上記の手順でパスワードをセット''' -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 にログインして // # mysql -u root -p //以下の方法で root 用のパスワードを変更 // mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'ここに新しいパスワード'; ~ ***接続と動作確認 -接続（ルートのパスワードで） # 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>Vim]] が使えない場合は、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%"; &aname(phpMyAdmin); ~ ~ **phpMyAdmin で 管理 phpMyAdmin は MySQL サーバーをブラウザから管理するためのDB接続クライアントツールで、PHPで書かれています。[[SQL>Google:SQL]] の文法を知らなくてもデータベースに対する様々な操作が可能です（もちろん SQL を記述して実行することもできます）。 ~ ***ダウンロードとセットアップ -ローカルで XAMPP を利用しているケース すでに同梱されています。以下のアドレスで確認できるはずです。 http://localhost/phpmyadmin/ -レンタルサーバーを利用するケース すでに置いてあるケースが大半です。サーバーの管理ページのメニュー項目に phpMyAdmin がないか確認してください。 -プレーンな外部サーバーを利用しているケース 自分で設置する必要があります。 --以下の公式サイトから phpMyAdmin をダウンロード https://www.phpmyadmin.net/ --FTP接続で phpMyAdmin フォルダをドキュメントルートにアップロード &color(red){公開サーバーの場合、ドキュメントルートの 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を置いたパス）/ 以下、上記と同様です。 //例えば 自分のローカルホスト内の phpMyAdmin → 大学DBサーバに繋げる場合、configファイルで [接続先] localhost ではなく、 // $cfg['Servers'][$i]['host'] = '＊＊＊.ip.kyusan-u.ac.jp'; //などとするだけです。 //データベースアクセス用のID、PW設定等は同様に設定します。 ~ ***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/ ~ ~ ~