MySQLを使ったデータベース管理の基本

はじめに

この実験では、MySQLを使ってデータベース管理の基本概念を探求します。データベースの作成と削除、データベースの選択、およびメタデータの取得について学びます。これらのスキルは、初心者から経験豊富な開発者まで、データベースを扱うすべての人にとって欠かせないものです。この実験が終了するまでに、MySQLデータベースの基本操作に関する堅牢な基礎を築くことができます。

学習目標

この実験を完了することで、以下のことができるようになります。

• 様々な方法を使ってデータベースを作成および削除する
• データベースを選択して切り替える
• MySQLサーバーとデータベースに関する重要なメタデータを取得する
• MySQLにおけるデータベース名の大文字小文字の区別を理解する

Skills Graph

データベースの作成と一覧表示

このステップでは、MySQLコマンドラインクライアントを使ってデータベースを作成し、既存のデータベースを一覧表示する方法を学びます。これは、データベース管理者や開発者にとって基本的なスキルです。

まず、ターミナルウィンドウを開きましょう。LabEx環境では、Ubuntu Linuxシステムを使用します。ターミナルは既に開いているはずですが、開いていない場合は、ターミナルアイコンをクリックして開くことができます。

次に、MySQLクライアントを起動しましょう。

sudo mysql -u root

LabEx VMでは、rootユーザーのパスワードを入力する必要はありません。

MySQLクライアントに入ると、このようなプロンプトが表示されます。MariaDB [(none)]>。これは、MariaDBサーバー（MySQLの派生版）に接続していることを示していますが、まだ特定のデータベースを選択していません。

使用可能なデータベースの一覧表示

まず、MySQLサーバー上の使用可能なデータベースを一覧表示しましょう。

SHOW DATABASES;

以下のような出力が表示されるはずです。

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.000 sec)

このコマンドは、サーバー上で現在使用可能なすべてのデータベースを一覧表示します。これらのデフォルトのデータベースについて解説しましょう。

• information_schema：データベースのメタデータにアクセスするためのデータベース。
• mysql：ユーザーアカウントと権限情報を含むシステムデータベース。
• performance_schema：MySQLサーバーの実行を低レベルで監視するためのデータベース。
• sys：Performance Schemaで収集されたデータを解釈するのに役立つオブジェクトのセットを提供するデータベース。

データベースの作成

次に、'Hello_World'という新しいデータベースを作成しましょう。MySQLでは、これにはCREATE DATABASEコマンドを使用します。

CREATE DATABASE Hello_World;

このコマンドを実行すると、MySQLは以下のように応答します。

Query OK, 1 row affected (0.000 sec)

これは、データベースが正常に作成されたことを意味します。確認するために、もう一度データベースを一覧表示しましょう。

SHOW DATABASES;

この時点で、一覧に'Hello_World'が表示されるはずです。

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.000 sec)

素晴らしい！ あなたは初めてのデータベースを作成しました。

データベース名の大文字小文字の区別

MySQLで理解すべき重要な概念は、データベース名が大文字小文字を区別することです。これは、'Hello_World'と'hello_world'が2つの異なるデータベースとして扱われることを意味します。これを示しましょう。

CREATE DATABASE hello_world;
SHOW DATABASES;

この時点で、一覧に'Hello_World'と'hello_world'の両方が表示されるはずです。

MariaDB [(none)]> CREATE DATABASE hello_world;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| hello_world        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.000 sec)

これは、'Hello_World'と'hello_world'が2つの別個のデータベースとして扱われることを示しています。データベースの設計では、混乱を避けるために一貫した命名規則を使用するのが一般的な良い慣習です。多くの開発者は、潜在的な大文字小文字の区別の問題を避けるために、データベース名にすべて小文字を使用することを好みます。

mysqladminを使ったデータベースの作成

このステップでは、mysqladminツールを使ってデータベースを作成する別の方法を探求します。このツールはコマンドラインユーティリティで、MySQLのプロンプトに入らずにいくつかの管理操作を実行できます。

まず、MySQLクライアントを終了しましょう。これは、exitまたはquitと入力してEnterキーを押すことでできます。

exit

通常のコマンドプロンプトに戻ったので、mysqladminを使って新しいデータベースを作成しましょう。

mysqladmin -u root -p create hello_world2

パスワードを求められたら、Enterキーを押してください（覚えておいてください、LabEx VMでは、root MySQLユーザーにはパスワードが設定されていません）。

先ほど使ったmysqladminコマンドは、以下のように解釈できます。

• -u root：これは、rootユーザーとして接続することを指定しています。
• -p：これは、mysqladminにパスワードを求めるように指示しています。
• create：これは、実行している操作です。
• hello_world2：これは、作成しているデータベースの名前です。

次に、新しいデータベースが作成されたことを確認するために、再びMySQLクライアントにログインしましょう。

sudo mysql -u root

MySQLクライアントに入ったら、データベースを一覧表示します。

SHOW DATABASES;

一覧に'hello_world2'が表示されるはずです。

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| hello_world        |
| hello_world2       |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.000 sec)

mysqladminツールは、コマンドラインから直接データベースを作成するための迅速な方法を提供します。これは、スクリプト作成や自動化タスクに便利です。より大きなスクリプトや自動化プロセスの一部としてデータベースを作成する必要がある場合には、特に便利です。

データベースの削除

これまでにいくつかのデータベースを作成したので、それらを削除する方法を学びましょう。データベース管理においては、作成する方法と同じくらい、削除する方法を知ることが重要です。'Hello_World'データベースのみを残し、それ以外を削除します。

MySQLクライアントを使ったデータベースの削除

まだMySQLクライアントに入っていない場合は、そこにログインします。

sudo mysql -u root

次に、'hello_world'データベースを削除しましょう。

DROP DATABASE hello_world;
SHOW DATABASES;

'hello_world'が一覧にもはや表示されないことが確認できるはずです。

MariaDB [(none)]> DROP DATABASE hello_world;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| hello_world2       |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.000 sec)

注：DROP DATABASEコマンドを使用する際には、極めて注意する必要があります。このコマンドは確認を求めることなく、データベースとそのすべてのコンテンツを永久に削除します。本番環境では、通常、このような操作を行う前にセーフガードとバックアップを行います。

mysqladminを使ったデータベースの削除

次に、mysqladminを使って'hello_world2'データベースを削除しましょう。この方法は、データベースを削除する前に確認を求めるため、より安全と考えられています。

exitまたはquitと入力してMySQLクライアントを終了し、次にターミナルで以下のコマンドを実行します。

mysqladmin -u root -p drop hello_world2

操作を確認するように促されます。

Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'hello_world2' database [y/N] y
Database "hello_world2" dropped

'y'を入力してEnterキーを押して確認します。この追加の確認ステップにより、誤ったデータベースの削除を防ぐことができます。

次に、再びMySQLクライアントにログインして、'hello_world2'が削除されたことを確認します。

SHOW DATABASES;

以下のように表示されるはずです。

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.000 sec)

不要なデータベースを正常にクリーンアップし、必要なもののみを残しました。

データベースの選択と使用

これまでにデータベースを作成および削除したので、特定のデータベースを選択して使用する方法を学びましょう。これは重要なスキルであり、MySQLでの作業のほとんどは特定のデータベース内での操作に関係します。

データベースの選択

使用するデータベースを選択するには、USEコマンドに続けてデータベース名を指定します。

USE Hello_World;

以下のように表示されます。

Database changed

これは、現在'Hello_World'データベース内で作業していることを示しています。その後実行するコマンドは、別途指定しない限り、このデータベースのコンテキストで実行されます。

データベース内のテーブルの一覧表示

現在のデータベース内のテーブルを表示するには、SHOW TABLES;コマンドを使用します。

SHOW TABLES;

'Hello_World'データベースにまだテーブルを作成していないため、以下のように表示されます。

Empty set (0.00 sec)

新しいデータベースではこれが正常です。テーブルを作成すると、この一覧に表示されます。

別のデータベースに切り替えて、そのテーブルを一覧表示して、データが入ったデータベースでどのように機能するかを見てみましょう。

USE mysql;
SHOW TABLES;

'mysql'システムデータベース内のテーブルの一覧が表示されます。

MariaDB [mysql]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| global_priv               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.001 sec)

これは、データベース間を切り替えてその内容を表示する方法を示しています。'mysql'データベースには、MySQLが権限、ユーザーアカウント、その他の内部データを管理するために使用するシステムテーブルが含まれています。独自のアプリケーションでは、カスタムデータベース内で独自のテーブルを作成および管理します。

MySQLメタデータの取得

この最後のステップでは、MySQLからさまざまな種類のメタデータを取得する方法を探求します。メタデータは「データに関するデータ」であり、データベースシステム自体に関する情報を提供し、その中に格納されているデータではありません。この情報は、MySQL環境を理解し管理する際に重要です。

サーバーバージョン

MySQLサーバーのバージョンを取得するには、次のコマンドを使用します。

SELECT VERSION();

以下のような出力が表示されます。

MariaDB [mysql]> SELECT VERSION();
+----------------------------------+
| VERSION()                        |
+----------------------------------+
| 10.6.12-MariaDB-0ubuntu0.22.04.1 |
+----------------------------------+
1 row in set (0.000 sec)

この情報は、現在のMySQLバージョンで利用可能な機能を知る必要がある場合や、特定のバージョンに関連する問題のトラブルシューティングに役立ちます。

現在のデータベース

現在使用しているデータベースを確認するには、次のコマンドを使用します。

SELECT DATABASE();

出力は、現在のデータベース名を表示します。データベースが選択されていない場合はNULLが表示されます。

MariaDB [mysql]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mysql      |
+------------+
1 row in set (0.000 sec)

複数のデータベースを使用している場合に、現在どのデータベースで作業しているかを確認する際に便利です。

現在のユーザー

現在のユーザーを確認するには、次のコマンドを使用します。

SELECT USER();

以下のような出力が表示されます。

MariaDB [mysql]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

これは、ログインしているMySQLユーザーと接続元のホストを示します。現在の権限と接続詳細を確認するのに役立ちます。

サーバーのステータスと変数

すべてのサーバーステータス指標を表示するには、次のコマンドを使用します。

SHOW STATUS;

このコマンドは、MySQLサーバーの現在の状態に関する豊富な情報を提供します。さまざまなカウンターや統計情報が含まれます。

すべてのサーバー構成変数を表示するには、次のコマンドを使用します。

SHOW VARIABLES;

このコマンドは、MySQLサーバーの構成方法を示します。メモリ使用量、接続制限、その他多くのパラメータの設定が含まれます。

これらのコマンドは両方とも大量の出力を生成します。ターミナルでスクロールして確認するか、特定の情報をフィルタリングすることができます。たとえば、バッファプールに関連する変数を表示するには、次のコマンドを使用します。

SHOW VARIABLES LIKE '%buffer%';

これらのコマンドは、MySQLサーバーの構成と現在の状態に関する詳細な情報を提供します。このメタデータを理解することは、パフォーマンス最適化、問題のトラブルシューティング、サーバーが必要に応じて正しく構成されていることを確認するために重要です。

たとえば、クエリの実行回数を確認するには、SHOW STATUSを使用することができます。

SHOW STATUS LIKE 'Questions';

以下のような出力が表示される場合があります。

MariaDB [mysql]> SHOW STATUS LIKE 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 15    |
+---------------+-------+
1 row in set (0.001 sec)

これは、サーバーが起動してから実行されたステートメントの数を示します。

同様に、最大許容パケットサイズなどの重要な構成設定を確認するには、SHOW VARIABLESを使用することができます。

SHOW VARIABLES LIKE'max_allowed_packet';

以下のような出力が表示される場合があります。

MariaDB [mysql]> SHOW VARIABLES LIKE'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.001 sec)

これは、1つのパケットまたは生成された/中間の文字列の最大サイズを示しており、大量のデータを扱う際に重要です。

このメタデータを取得して解釈する方法を理解することは、データベース管理者や開発者にとって貴重なスキルです。データベースシステムの動作とパフォーマンスに洞察を得ることができ、最適化とトラブルシューティングにとって重要です。

まとめ

この実験では、MySQLにおけるデータベース管理の基本的な側面を学びました。

1. MySQLクライアントとmysqladminツールの両方を使ってデータベースを作成する
2. 既存のデータベースを一覧表示し、その目的を理解する
3. MySQLにおけるデータベース名の大文字小文字の区別を理解する
4. 安全にデータベースを削除することと、その際の注意の重要性
5. データベースを選択して切り替える
6. MySQLサーバーとデータベースに関する重要なメタデータを取得する

これらのスキルは、MySQLデータベースを操作するための基礎を形成します。データベースの学習を進めるにつれて、これらの基本を基にして、より複雑なデータベース操作や管理タスクを行うようになります。