MySQL データベース管理の基礎

はじめに

この実験では、MySQL を使用したデータベース管理の基本概念を探求します。データベースの作成と削除、データベースの選択、およびメタデータの取得方法について学びます。これらのスキルは、初心者から経験豊富な開発者まで、データベースを扱うすべての人にとって不可欠です。この実験を終える頃には、MySQL データベースの基本的な操作に関する強固な基礎が身についているはずです。

学習目標

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

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

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

このステップでは、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: パフォーマンススキーマによって収集されたデータを解釈するのに役立つオブジェクトセットを提供するデータベース。

データベースの作成

次に、「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」が別々のデータベースとして扱われることを示しています。データベース設計においては、混乱を避けるために一貫した命名規則を使用するのが一般的です。多くの開発者は、大文字と小文字の区別による問題を避けるため、データベース名にはすべて小文字を使用することを好みます。

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

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

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

exit

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

sudo mysqladmin -u root create hello_world2

この LabEx VM では、root データベースアカウントがローカルソケット認証を使用しているため、mysqladmin で sudo を使用してください。

今使用したコマンドの内訳は以下の通りです。

• sudo: 必要なシステム権限でコマンドを実行します。
• -u root: root データベースユーザーとして接続することを指定します。
• 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 クライアントを終了し、ターミナルで次のコマンドを実行します。

sudo mysqladmin -u root drop hello_world2

操作の確認を求められます。

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 データベースを扱うための基礎となります。データベースの学習を進めるにつれて、これらの基本を積み重ねて、より複雑なデータベース操作や管理タスクを実行できるようになるでしょう。