【SQL実践】MariaDBコマンドツール完全ガイド：知っておくべき全コマンドとその活用法

概要

データベース管理者は、日々の業務において様々なデータベース操作をコマンドラインから実行する必要があります。MariaDBも例外ではなく、その強力なコマンドラインクライアントである`mysql`コマンドは、データベースの管理、操作、保守に不可欠なツールです。本記事では、MariaDBコマンドツールから実行できる主要なコマンドを網羅的に紹介し、それぞれのコマンドの基本的な使い方から、より高度な活用方法、そして実務で役立つアドバイスまでを詳細に解説します。このガイドをマスターすることで、MariaDBの操作効率を飛躍的に向上させ、より熟練したDBAへとステップアップできるでしょう。

MariaDBコマンドツールの基本

MariaDBコマンドラインクライアントは、通常`mysql`という名前で提供されます。このクライアントを通じて、SQLクエリの実行、データベースの管理、ユーザー管理、バックアップ・リストア、レプリケーション設定など、多岐にわたる操作が可能です。基本的な接続方法は以下の通りです。

mysql -u [ユーザー名] -p -h [ホスト名] [データベース名]
  • -u [ユーザー名]: 接続するユーザー名を指定します。
  • -p: パスワード入力を求めます。セキュリティのため、パスワードを直接指定するのではなく、このオプションを使用することが推奨されます。
  • -h [ホスト名]: 接続先のMariaDBサーバーのホスト名を指定します。ローカルホストの場合は省略可能です。
  • [データベース名]: 接続時にデフォルトで使用するデータベースを指定します。省略すると、どのデータベースにも接続されません。

接続に成功すると、MariaDBプロンプト（通常はMariaDB [(none)]>）が表示され、コマンドを入力できるようになります。

主要なコマンドとその実行方法

1. データベースの管理コマンド

データベースの作成、削除、一覧表示

新しいデータベースを作成したり、既存のデータベースを削除したり、現在存在するデータベースの一覧を表示したりするコマンドです。

  • **データベースの作成:** CREATE DATABASE [データベース名];
  • **データベースの削除:** DROP DATABASE [データベース名];
  • **データベースの一覧表示:** SHOW DATABASES;
MariaDB [(none)]> CREATE DATABASE my_app_db;
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_app_db          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
MariaDB [(none)]> DROP DATABASE my_app_db;
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
テーブルの管理

データベース内のテーブルを作成、削除、一覧表示、構造確認などを行います。

  • **テーブルの一覧表示:** SHOW TABLES; (現在のデータベースに接続している必要があります)
  • **テーブルの構造表示:** DESCRIBE [テーブル名]; または SHOW COLUMNS FROM [テーブル名];
  • **テーブルの作成:** CREATE TABLE [テーブル名] (カラム定義);
  • **テーブルの削除:** DROP TABLE [テーブル名];
  • **テーブルのコピー:** CREATE TABLE [新しいテーブル名] AS SELECT * FROM [元のテーブル名];
  • **テーブルの追加 (構造のみ):** CREATE TABLE [新しいテーブル名] LIKE [元のテーブル名];
-- my_app_db に接続
MariaDB [(none)]> USE my_app_db;
Database changed
MariaDB [my_app_db]> CREATE TABLE users (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     username VARCHAR(50) NOT NULL,
    ->     email VARCHAR(100) UNIQUE,
    ->     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.04 sec)

MariaDB [my_app_db]> SHOW TABLES;
+-----------------+
| Tables_in_my_app_db |
+-----------------+
| users           |
+-----------------+
MariaDB [my_app_db]> DESCRIBE users;
+-----------+-------------+------+-----+-------------------+----------------+
| Field     | Type        | Null | Key | Default           | Extra          |
+-----------+-------------+------+-----+-------------------+----------------+
| id        | int         | NO   | PRI | NULL              | auto_increment |
| username  | varchar(50) | NO   |     | NULL              |                |
| email     | varchar(100)| YES  | UNI | NULL              |                |
| created_at| timestamp   | YES  |     | CURRENT_TIMESTAMP |                |
+-----------+-------------+------+-----+-------------------+----------------+
MariaDB [my_app_db]> DROP TABLE users;
Query OK, 0 rows affected (0.02 sec)

2. データ操作コマンド

データの挿入、更新、削除、参照

テーブルにデータを追加したり、既存のデータを変更したり、不要なデータを削除したり、データを検索したりします。

  • **データの挿入:** INSERT INTO [テーブル名] (カラム1, カラム2, ...) VALUES (値1, 値2, ...);
  • **データの更新:** UPDATE [テーブル名] SET カラム1 = 値1, カラム2 = 値2, ... WHERE 条件;
  • **データの削除:** DELETE FROM [テーブル名] WHERE 条件;
  • **データの参照:** SELECT カラム1, カラム2, ... FROM [テーブル名] WHERE 条件;
  • **全件削除 (テーブル構造は残す):** TRUNCATE TABLE [テーブル名]; (DELETEよりも高速ですが、ロールバックができません)
MariaDB [my_app_db]> INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
Query OK, 1 row affected (0.01 sec)

MariaDB [my_app_db]> INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');
Query OK, 1 row affected (0.00 sec)

MariaDB [my_app_db]> SELECT * FROM users;
+----+----------+-------------------+---------------------+
| id | username | email             | created_at          |
+----+----------+-------------------+---------------------+
|  1 | alice    | alice@example.com | 2023-10-27 10:00:00 |
|  2 | bob      | bob@example.com   | 2023-10-27 10:01:00 |
+----+----------+-------------------+---------------------+
MariaDB [my_app_db]> UPDATE users SET email = 'alice.wonderland@example.com' WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [my_app_db]> SELECT * FROM users WHERE username = 'alice';
+----+----------+----------------------------+---------------------+
| id | username | email                      | created_at          |
+----+----------+----------------------------+---------------------+
|  1 | alice    | alice.wonderland@example.com | 2023-10-27 10:00:00 |
+----+----------+----------------------------+---------------------+
MariaDB [my_app_db]> DELETE FROM users WHERE id = 2;
Query OK, 1 row affected (0.01 sec)

MariaDB [my_app_db]> SELECT * FROM users;
+----+----------+----------------------------+---------------------+
| id | username | email                      | created_at          |
+----+----------+----------------------------+---------------------+
|  1 | alice    | alice.wonderland@example.com | 2023-10-27 10:00:00 |
+----+----------+----------------------------+---------------------+

3. ユーザー管理コマンド

ユーザーの作成、削除、権限管理

データベースへのアクセス権限を持つユーザーを作成・管理します。

  • **ユーザーの作成:** CREATE USER '[ユーザー名]'@'[ホスト名]' IDENTIFIED BY '[パスワード]';
  • **ユーザーの削除:** DROP USER '[ユーザー名]'@'[ホスト名]';
  • **権限の付与:** GRANT [権限リスト] ON [データベース名].[テーブル名] TO '[ユーザー名]'@'[ホスト名]'; (例: GRANT ALL PRIVILEGES ON my_app_db.* TO 'app_user'@'localhost';)
  • **権限の剥奪:** REVOKE [権限リスト] ON [データベース名].[テーブル名] FROM '[ユーザー名]'@'[ホスト名]';
  • **権限の確認:** SHOW GRANTS FOR '[ユーザー名]'@'[ホスト名]';
  • **権限のフラッシュ:** FLUSH PRIVILEGES; (権限変更後に実行することで、即座に反映させます)
-- rootユーザーなどで実行
MariaDB [(none)]> CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE ON my_app_db.* TO 'app_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'app_user'@'localhost';
+---------------------------------------------------------------------------+
| Grants for app_user@localhost                                             |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost`                              |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `my_app_db`.* TO `app_user`@`localhost` |
+---------------------------------------------------------------------------+
MariaDB [(none)]> REVOKE DELETE ON my_app_db.* FROM 'app_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'app_user'@'localhost';
+-----------------------------------------------------------------+
| Grants for app_user@localhost                                   |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost`                    |
| GRANT SELECT, INSERT, UPDATE ON `my_app_db`.* TO `app_user`@`localhost` |
+-----------------------------------------------------------------+
MariaDB [(none)]> DROP USER 'app_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

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

データベースのデータを保全するために、バックアップは非常に重要です。MariaDBでは、`mysqldump`コマンドラインユーティリティを使用して論理バックアップを取得できます。

バックアップ (mysqldump)

指定したデータベースまたは全てのデータベースをSQLファイルとしてエクスポートします。

  • **単一データベースのバックアップ:** mysqldump -u [ユーザー名] -p [データベース名] > [バックアップファイル名].sql
  • **全データベースのバックアップ:** mysqldump -u [ユーザー名] -p --all-databases > [バックアップファイル名].sql
  • **特定テーブルのみのバックアップ:** mysqldump -u [ユーザー名] -p [データベース名] [テーブル名1] [テーブル名2] > [バックアップファイル名].sql
  • **構造のみのバックアップ:** mysqldump -u [ユーザー名] -p --no-data [データベース名] > [バックアップファイル名].sql
  • **データのみのバックアップ:** mysqldump -u [ユーザー名] -p --no-create-info [データベース名] > [バックアップファイル名].sql
-- my_app_db のバックアップ
mysqldump -u root -p my_app_db > my_app_db_backup_$(date +%Y%m%d).sql

-- 全データベースのバックアップ
mysqldump -u root -p --all-databases > all_databases_backup_$(date +%Y%m%d).sql
リストア

バックアップファイルからデータベースを復元します。`mysql`コマンドを使用します。

  • **データベースの作成 (必要であれば):** CREATE DATABASE [データベース名]; (リストア対象のデータベースが存在しない場合)
  • **バックアップファイルからのリストア:** mysql -u [ユーザー名] -p [データベース名] < [バックアップファイル名].sql
-- my_app_db を復元 (事前に my_app_db が存在しないことを確認)
mysql -u root -p my_app_db < my_app_db_backup_20231027.sql

5. その他の便利なコマンド

サーバー情報の表示

MariaDBサーバーのバージョンやステータスなどを確認できます。

  • **バージョン表示:** SELECT VERSION();
  • **ステータス表示:** SHOW STATUS; (多くのサーバー情報が表示されます。SHOW GLOBAL STATUS; でグローバルなステータスも確認できます。)
  • **変数の表示:** SHOW VARIABLES; (サーバー設定変数を確認できます。SHOW GLOBAL VARIABLES; でグローバルな変数を表示します。)
MariaDB [(none)]> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 10.11.4-MariaDB |
+-----------+
MariaDB [(none)]> SHOW STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected |    10 |
+-------------------+-------+
クエリの実行時間計測

SHOW PROFILEコマンドを使用すると、クエリの実行にかかった時間の内訳を確認でき、パフォーマンスチューニングに役立ちます。

  1. まず、プロファイリングを有効にします: SET profiling = 1;
  2. 実行したいクエリを実行します。
  3. プロファイル結果を表示します: SHOW PROFILES; (実行したクエリのIDが表示されます)
  4. 特定のクエリの実行計画を表示します: SHOW PROFILE FOR QUERY [クエリID];
MariaDB [my_app_db]> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [my_app_db]> SELECT * FROM users WHERE username = 'alice';
+----+----------+----------------------------+---------------------+
| id | username | email                      | created_at          |
+----+----------+----------------------------+---------------------+
|  1 | alice    | alice.wonderland@example.com | 2023-10-27 10:00:00 |
+----+----------+----------------------------+---------------------+
MariaDB [my_app_db]> SHOW PROFILES;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00008200 | SELECT * FROM users WHERE username = 'alice' |
+----------+------------+-----------------------------------+
MariaDB [my_app_db]> SHOW PROFILE FOR QUERY 1;
+-----------------+-----------+
| Status          | Duration  |
+-----------------+-----------+
| starting        |  0.000012 |
| checking permissions |  0.000001 |
| Opening tables  |  0.000003 |
| init            |  0.000003 |
| optimizing      |  0.000002 |
| statistics      |  0.000002 |
| preparing       |  0.000003 |
| executing       |  0.000004 |
| Sending data    |  0.000007 |
| end             |  0.000003 |
| query end       |  0.000003 |
| closing tables  |  0.000003 |
| freeing items   |  0.000004 |
| cleaning up     |  0.000004 |
+-----------------+-----------+

実務アドバイス

  • **パスワード管理:** コマンドラインでパスワードを直接指定することは避け、-pオプションを使用するか、設定ファイル（~/.my.cnfなど）に安全に保存してください。
  • **権限の最小化:** ユーザーには、その業務に必要な最小限の権限のみを付与するようにしてください。これにより、セキュリティリスクを低減できます。
  • **バックアップ戦略:** 定期的なバックアップはもちろんのこと、バックアップデータの保存場所（ローカル、リモート、クラウドなど）や、リストアテストを定期的に実施することが重要です。
  • **ログの活用:** MariaDBのエラーログやスロークエリログなどを活用することで、問題の早期発見やパフォーマンス改善に繋がります。
  • **バージョン管理:** データベーススキーマの変更履歴を管理するために、バージョン管理システム（Gitなど）と連携させることを検討してください。
  • **スクリプト化:** 定型的な操作はシェルスクリプトやSQLスクリプトにまとめておくことで、作業の自動化とミスの削減に繋がります。
  • **エディタ連携:** `mysql`コマンドの--auto-rehashオプションや、pager設定（例: \P less）を利用すると、コマンド補完や長大な結果の確認が容易になります。

まとめ

MariaDBのコマンドラインツールは、データベースの管理と運用において非常に強力かつ柔軟な機能を提供します。本記事で紹介したコマンドを習得し、日々の業務で活用することで、データベース管理の効率を大幅に向上させることができます。特に、データベース、テーブル、ユーザーの管理、そしてバックアップ・リストアといった基本的な操作は、DBAにとって必須のスキルです。さらに、サーバー情報の確認やパフォーマンスチューニングのためのプロファイリング機能も理解しておくことで、より高度なトラブルシューティングや最適化が可能になります。

今回紹介したコマンドはMariaDBコマンドツールの一部ですが、これらを基盤として、さらに多くの高度な機能やオプションが存在します。公式ドキュメントを参照しながら、これらのツールを使いこなし、安全で効率的なMariaDB環境の構築・運用を目指してください。

スポンサーリンク

コメント

タイトルとURLをコピーしました