MySQL (MariaDB)の基本的な操作方法・コマンドまとめ

MariaDB-MySQL

こんにちはフロントエンドエンジニアのまさにょんです!

今回は、MySQL (MariaDB)の基本的な操作方法・コマンドについてまとめて解説します。

よく使うMySQLコマンド&構文集 さんをメインにしながら、よく使用するコマンドをまとめています。

目次

Login/Logout関連の操作

MySQL (MariaDB)にLoginする

MySQL (MariaDB)を直接操作するために、Loginします。

-pオプションを付与すると、Passwordの入力を求められるので、入力してEnterをします。

(-pに続けて、パスワードを設定すると省略できます)

注意点として、localhostに接続する場合は、127.0.0.1を指定するようにしましょう。

環境によってはlocalhostのままだとErrorが発生してログインできないことがあります。

# localhost(127.0.0.1)のMySQLサーバに接続する場合
mysql -u root -p

# DataBase名指定でログインする
mysql -u root -p RobotamaDB

# Host指定で、接続先を指定する Ver. localhost
mysql -u Robotama -p -h 127.0.0.1

# rootユーザーで、127.0.0.1 の 3308ポートの RobotamaDB にログインする(パスワード入力済み)
mysql -u root -ppassword -h 127.0.0.1 -P 3308 RobotamaDB

# 外部MySQLサーバに接続する場合
$ mysql -u [ユーザー名] -p -h [host名] -P [ポート番号]

MySQL (MariaDB)からLogoutする

次の3つのコマンドのいずれかでログアウトできます。

mysql> \q

mysql> quit

mysql> exit

ヘルプ

MySQL (MariaDB)にLoginしている状態で、次のどちらかのコマンドでヘルプの確認ができます。

mysql> help

mysql> \h

User情報の操作(rootログイン後)

User情報を取得する

mysql> select user from mysql.user;
+-------------+
| User        |
+-------------+
| root        |
| root        |
| root        |
| mariadb.sys |
| root        |
+-------------+
5 rows in set (0.001 sec)

User情報とHost情報を取得する

mysql> select user, host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| root        | 127.0.0.1 |
| root        | ::1       |
| root        | robotama  |
| mariadb.sys | localhost |
| root        | localhost |
+-------------+-----------+
5 rows in set (0.001 sec)

User情報とHost情報、Password を取得する

mysql> select user, host, Password from mysql.user;
+-------------+-----------+-------------------------------------------+
| User        | Host      | Password                                  |
+-------------+-----------+-------------------------------------------+
| mariadb.sys | localhost |                                           |
| root        | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root        | robotama  | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root        | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root        | ::1       | *9E1AA81F840662856BA1AFA79A5DB9B99140499C |
+-------------+-----------+-------------------------------------------+
5 rows in set (0.001 sec)

新しいUserを作成する

新しくユーザを作成するためには、CREATE USER文を使用します。

ユーザ名、パスワードには、任意の文字列を設定してください。

ホスト名には、MySQLへ接続するホストを指定してください。

ホスト名の代わりにIPアドレスやlocalhostを指定することも可能です。

CREATE USER 'ユーザ名'@'ホスト名' IDENTIFIED BY 'パスワード';

試しに、Userを作成してみる

mysql> create user Robotama@localhost identified by 'robotama-nanoda';
Query OK, 0 rows affected (0.000 sec)

mysql>  select user, host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| Robotama      | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.000 sec)

Userのパスワードを変更する

MySQL内部からパスワードを変更する

パスワードを文字列として渡すことに注意してください。

# SET PASSWORD FOR ユーザ名@ホスト名=password('新しいパスワード');

# 現在接続しているユーザーに新しいパスワードを設定する場合
set password = password('new-pass');

# 特定のユーザーのパスワードを設定する場合場合
set password for Robotama@localhost = password('new-pass');

MySQLにログインせずにパスワードを変更する

パスワードをクオーテーションで囲む必要はありません。

# mysqladmin password 新しいパスワード -u ユーザー名 -p

mysqladmin password new-pass -u Robotama@localhost -p

User を削除する

drop user 'ユーザー名'@'ホスト';

Userの権限の確認

show grants for 'ユーザー名'@'ホスト名';

root User の権限確認をしてみます。

mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

作ったばかりの一般ユーザーの権限を確認してみます。

mysql> show grants for 'Robotama'@'localhost';
+----------------------------------------------+
| Grants for Robotama@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'Robotama'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)

権限の付与

GRANT 権限 ON データベース名.テーブル名 TO 'ユーザー名';

作ったばかりの一般ユーザーにすべての操作権限を与えてみます。

# すべてのDB・すべてのテーブルに対する権限を付与する場合
mysql> GRANT ALL PRIVILEGES ON * . * TO Robotama@localhost;
Query OK, 0 rows affected (0.01 sec)

# 特定のDBのすべてのテーブルに権限を付与する場合
mysql> grant all on RobotamaDB.* to Robotama@localhost;
Query OK, 0 rows affected (0.03 sec)


mysql> show grants for 'Robotama'@'localhost';
+------------------------------------------------------------------+
| Grants for Robotama@localhost                                    |
+------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'Robotama'@'localhost'            |
| GRANT ALL PRIVILEGES ON `robotamadb`.* TO 'Robotama'@'localhost' |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

付与した権限設定の反映がされない時は、次のコマンドを実行して、リロードしてください。

FLUSH PRIVILEGES;

データベースの操作系統

データベース一覧の表示・確認

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| express1           |
| mysql              |
| next_ts_ec         |
| performance_schema |
| sys                |
| todo_app           |
| ts_todo            |
+--------------------+
8 rows in set (0.00 sec)

データベースの追加

mysql> create database RobotamaDB;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| RobotamaDB         |
| express1           |
| mysql              |
| next_ts_ec         |
| performance_schema |
| sys                |
| todo_app           |
| ts_todo            |
+--------------------+
9 rows in set (0.01 sec)

データベースの選択

mysql> use robotamadb;
Database changed

Tableの操作系統

テーブル一覧の表示

mysql> show tables;
# テーブルの詳細情報
mysql > show table status;

テーブルの作成

mysql > CREATE TABLE [テーブル名] (
  [フィールド名] [データ型] [オプション]
) ENGINE=[InnoDB/MyISAM] DEFAULT CHARSET=[文字コード];
mysql > CREATE TABLE `m_users` (
        `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "ID",
        `user_name` VARCHAR(100) NOT NULL COMMENT "ユーザー名",
        `mail_address` VARCHAR(200) NOT NULL COMMENT "メールアドレス",
        `password` VARCHAR(100) NOT NULL COMMENT "パスワード",
        `created` datetime DEFAULT NULL COMMENT "登録日",
        `modified` datetime DEFAULT NULL COMMENT "更新日"
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

テーブルの削除

mysql > DROP TABLE [テーブル名]

TABLEが存在しない時にエラーで止めたくなければ、
次のように IF EXISTS を追加することでTABLEが存在する時のみ DROP TABLE 文を実行するようにできます。

mysql > DROP TABLE IF EXISTS [テーブル名]

テーブル名の変更

mysql > ALTER TABLE [旧テーブル名] RENAME [新テーブル名]

テーブルにカラムの追加

mysql > ALTER TABLE [テーブル名] ADD [追加カラム名] [型] [必要であればオプション等];

テーブル設計の確認

# desc [テーブル名]

mysql> desc users;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name    | varchar(100) | NO   |     | NULL    |                |
| mail_address | varchar(200) | NO   |     | NULL    |                |
| password     | varchar(100) | NO   |     | NULL    |                |
| created      | datetime     | YES  |     | NULL    |                |
| modified     | datetime     | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

もっと詳細が知りたければ

# show full columns from [テーブル名];

mysql> show full columns from users;
+--------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+-----------------------+
| Field        | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment               |
+--------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+-----------------------+
| id           | int(11)      | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references | ID                    |
| user_name    | varchar(100) | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | ユーザー名              |
| mail_address | varchar(200) | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | メールアドレス           |
| password     | varchar(100) | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | パスワード              |
| created      | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references | 登録日                 |
| modified     | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references | 更新日                 |
+--------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+-----------------------+
6 rows in set (0.01 sec)

レコード操作関連

レコードの追加

mysql > INSERT INTO [テーブル名] [フィールド名] VALUES [値]

レコードの更新

mysql > UPDATE [テーブル名] SET [フィールド名]=[値] WHERE [条件式]

すべてのレコードを削除する

mysql > DELETE FROM [テーブル名]

一部レコード削除

mysql > DELETE FROM [テーブル名] WHERE [条件式]

DBのダンプを取る (ログイン不要・外部から取れる)

Database の Dumpファイルを取る際は、MySQL・MariaDBにログインしなくても、ダンプファイルを取れます。

.dump や .sql どちらかの拡張子で Dumpファイルを取ることが多いです。

すべてのデータベースを対象とする

mysqldump -u [ユーザー名] -p -x --all-databases > [出力ファイル名]

特定のデータベースを対象とする

# mysqldump -u [ユーザー名] -p -x [DB名] > [出力ファイル名]

mysqldump -u root -p RobotamaCMS > robotama.dump

test_dbデータベースのusersテーブルを対象とする

mysqldump -u [ユーザー名] -p -x test_db users > [出力ファイル名]

Restoreする

# mysql -u[ユーザー名] -p new_db < [ダンプファイル名]

mysql -u root -p RobotamaCMS < C:\robotama.dump

SQL実行結果をファイルに出力する

# mysql -u ユーザー名 -p -e "SQLの実行文" SQLの対象DB > 出力先のPath

mysql -u root -p -e "select * from users" test_db > /tmp/mysql.txt

mysql -u root -p -e "select * from users" RobotamaDB > C:\Users\Robotama\Desktop\robotama.txt

MySQL (MariaDB)のポートを確認する

MariaDBが接続するときに使うポート番号を確認します。デフォルトのままであれば、3306です。

mysql > show variables like 'port';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

MySQLログイン状態の見え方の変更

mysql> \R \d(\U) >\_
PROMPT set to '\d(\U) >\_'
(none)(root@localhost) > 

MySQL システム変数(DB設定値)を表示する

# MySQL システム変数の値を表示します。
SHOW GLOBAL VARIABLES;

LIKE文で特定の項目のみに絞れます。

# SHOW GLOBAL VARIABLES LIKE '%「検索項目」%';

mysql> show global variables like 'character%';
+--------------------------+----------------------------------------------------------+
| Variable_name            | Value                                                    |
+--------------------------+----------------------------------------------------------+
| character_set_client     | utf8                                                     |
| character_set_connection | utf8                                                     |
| character_set_database   | utf8                                                     |
| character_set_filesystem | binary                                                   |
| character_set_results    | utf8                                                     |
| character_set_server     | utf8                                                     |
| character_set_system     | utf8                                                     |
| character_sets_dir       | /usr/local/Cellar/mysql@5.7/5.7.36/share/mysql/charsets/ |
+--------------------------+----------------------------------------------------------+
8 rows in set (0.01 sec)

ストレージエンジンの種類を確認する

SHOW ENGINES;

# データベースのストレージエンジンの種類を確認する
SHOW TABLE STATUS FROM データベース名;

SHOW TABLE STAUS WHERE NAME = [テーブル名] \G;
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

robotamadbの ストレージエンジンがInnoDBであることがわかります。

mysql> show table status from robotamadb;
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| users | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              4 | 2022-11-14 10:24:11 | 2022-11-14 10:44:39 | NULL       | utf8_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.001 sec)

DB・SQL関連書籍

参考・引用

  1. よく使うMySQLコマンド&構文集
  2. 【MariaDB/MySQL】外部接続の設定方法
  3. MySQLで新しいユーザーを作成して権限を付与する方法
  4. 【MySQL入門】ユーザー作成の方法を解説!8.0からの変更点も紹介
  5. MySQLでユーザを作成し、権限を設定する方法
  6. MySQLでパスワードを変更する「SET PASSWORD」

最近の投稿