接続と切断
MySQLサーバーへの接続方法と切断方法の色々なパターンです。
基本的な接続
ユーザー名のみを指定して接続します。パスワードは後で聞かれます。
mysql -u [ユーザー名] -p例: ユーザー `root` で接続
mysql -u root -pホスト、ポート、ユーザー、パスワードを指定して接続
接続に必要な情報をすべてコマンドラインで指定します。-pの直後にパスワードを続けると、プロンプトなしで接続できます（履歴に残るため注意が必要です⚠️）。
mysql -h [ホスト名/IPアドレス] -P [ポート番号] -u [ユーザー名] -p[パスワード]例: ホスト `192.168.1.100`、ポート `3307`、ユーザー `admin`、パスワード `secret123` で接続
mysql -h 192.168.1.100 -P 3307 -u admin -psecret123パスワードを安全に入力する場合:
mysql -h 192.168.1.100 -P 3307 -u admin -p特定のデータベースに接続
接続と同時に使用するデータベースを指定します。
mysql -h [ホスト名] -u [ユーザー名] -p [データベース名]例: ホスト `localhost`、ユーザー `appuser`、データベース `myapp_db` に接続
mysql -h localhost -u appuser -p myapp_dbUnixソケットファイルを使用して接続
ローカルホストで、TCP/IPではなくソケットファイルを使って接続する場合に指定します。
mysql -u [ユーザー名] -p --socket=[ソケットファイルのパス]例: ユーザー `root` で `/var/run/mysqld/mysqld.sock` を使用して接続
mysql -u root -p --socket=/var/run/mysqld/mysqld.sockSSLを使用して接続
SSL/TLSで暗号化された接続を使用する場合のオプションです。サーバー側の設定も必要です。
mysql -u [ユーザー名] -p -h [ホスト名] --ssl-ca=[CA証明書パス] --ssl-cert=[クライアント証明書パス] --ssl-key=[クライアント秘密鍵パス]SSL接続を必須にする場合:
mysql -u [ユーザー名] -p -h [ホスト名] --ssl-mode=REQUIRED利用可能なSSLモード: DISABLED, PREFERRED, REQUIRED, VERIFY_CA, VERIFY_IDENTITY
切断
MySQLプロンプトからサーバーとの接続を切断します。
exitまたは
quitまたは Ctrl+D (多くの環境で)
データベース操作 📂
データベースのリスト表示、作成、選択、削除に関するコマンドです。
データベース一覧表示
サーバー上に存在するデータベースの一覧を表示します。
SHOW DATABASES;データベース作成
新しいデータベースを作成します。
CREATE DATABASE [データベース名];文字コードや照合順序を指定して作成する場合:
CREATE DATABASE [データベース名] CHARACTER SET [文字コード名] COLLATE [照合順序名];例: 文字コード `utf8mb4`、照合順序 `utf8mb4_general_ci` で `my_new_db` を作成
CREATE DATABASE my_new_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;既に存在しない場合のみ作成 (エラー回避):
CREATE DATABASE IF NOT EXISTS [データベース名];データベース選択
これ以降のSQL操作の対象となるデフォルトデータベースを選択（切り替え）します。
USE [データベース名];例: `myapp_db` を選択
USE myapp_db;現在選択中のデータベースを確認:
SELECT DATABASE();データベース削除
指定したデータベースを完全に削除します。注意: データベース内のすべてのテーブルとデータが失われます！ 復元は困難です。🚨
DROP DATABASE [データベース名];例: `old_db` を削除
DROP DATABASE old_db;存在する場合のみ削除 (エラー回避):
DROP DATABASE IF EXISTS [データベース名];テーブル操作 🏗️
テーブルの構造に関する操作（一覧、定義表示、作成、変更、削除）です。
テーブル一覧表示
現在選択中のデータベース内にあるテーブルの一覧を表示します。
SHOW TABLES;特定のデータベースのテーブル一覧を表示:
SHOW TABLES FROM [データベース名];例: `information_schema` データベースのテーブル一覧
SHOW TABLES FROM information_schema;テーブル構造表示
指定したテーブルのカラム定義（データ型、NULL許容、キー情報など）を表示します。
DESCRIBE [テーブル名];または
DESC [テーブル名];または、より詳細な情報:
SHOW COLUMNS FROM [テーブル名];テーブルを作成したSQL文を表示 (ストレージエンジンや文字コードも確認可能):
SHOW CREATE TABLE [テーブル名];テーブル作成 (CREATE TABLE)
新しいテーブルを作成します。
CREATE TABLE [テーブル名] (
[カラム名1] [データ型] [制約],
[カラム名2] [データ型] [制約],
...
[テーブルレベルの制約]
) ENGINE=[ストレージエンジン名] DEFAULT CHARSET=[文字コード名];例: `users` テーブルを作成
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;主なデータ型:
- 数値型: `INT`, `BIGINT`, `SMALLINT`, `TINYINT`, `FLOAT`, `DOUBLE`, `DECIMAL`
- 文字列型: `VARCHAR(n)`, `CHAR(n)`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT`
- 日付/時刻型: `DATE`, `DATETIME`, `TIMESTAMP`, `TIME`, `YEAR`
- バイナリ型: `BLOB`, `BINARY`, `VARBINARY`
- その他: `ENUM`, `SET`, `JSON`
主な制約:
- `NOT NULL`: NULL値を許可しない
- `NULL`: NULL値を許可する (デフォルト)
- `UNIQUE`: カラム内で一意な値を持つ
- `PRIMARY KEY`: 主キー (NOT NULL と UNIQUE を含む)
- `FOREIGN KEY`: 外部キー (他のテーブルを参照)
- `AUTO_INCREMENT`: 自動連番 (主に整数型の主キーで使用)
- `DEFAULT [値]`: デフォルト値を設定
- `CHECK`: 値の条件を指定 (MySQL 8.0.16以降)
既に存在しない場合のみ作成:
CREATE TABLE IF NOT EXISTS [テーブル名] (...);テーブル変更 (ALTER TABLE)
既存のテーブル構造を変更します。
| 操作 | 構文 | 例 |
|---|---|---|
| カラム追加 | |
|
| カラム型変更 | |
|
| カラム名変更 | |
|
| カラム削除 | |
|
| 主キー追加 | |
|
| 主キー削除 | |
|
| インデックス追加 | |
|
| ユニーク制約追加 | |
|
| インデックス削除 | |
|
| 外部キー追加 | |
|
| 外部キー削除 | |
|
| テーブルオプション変更 | |
|
テーブル削除 (DROP TABLE)
指定したテーブルを完全に削除します。注意: テーブル定義とデータがすべて失われます！ 復元は困難です。🚨
DROP TABLE [テーブル名];例: `temporary_data` テーブルを削除
DROP TABLE temporary_data;存在する場合のみ削除 (エラー回避):
DROP TABLE IF EXISTS [テーブル名];複数のテーブルを一度に削除:
DROP TABLE [テーブル名1], [テーブル名2], ...;テーブル名変更 (RENAME TABLE)
テーブルの名前を変更します。
RENAME TABLE [旧テーブル名] TO [新テーブル名];例: `customers` テーブルを `clients` に変更
RENAME TABLE customers TO clients;複数のテーブル名を一度に変更:
RENAME TABLE old_users TO users, old_products TO products;一時テーブル (TEMPORARY TABLE)
現在のセッション内でのみ有効な一時テーブルを作成します。セッション終了時に自動的に削除されます。
CREATE TEMPORARY TABLE [一時テーブル名] ( ... );例: 一時的なユーザーリストを作成
CREATE TEMPORARY TABLE temp_user_list (
user_id INT PRIMARY KEY,
name VARCHAR(100)
);既存のテーブルと同じ構造の一時テーブルを作成:
CREATE TEMPORARY TABLE temp_users LIKE users;SELECT結果から一時テーブルを作成:
CREATE TEMPORARY TABLE active_users AS SELECT id, username FROM users WHERE status = 'active';データ操作 (CRUD) 📝
テーブル内のデータを操作するコマンド (作成、読み取り、更新、削除) です。
データ挿入 (INSERT)
テーブルに新しい行（レコード）を追加します。
基本的なINSERT (全カラムに値を指定): カラムリストを省略する場合は、テーブル定義のカラム順にVALUES句で値を指定します。
INSERT INTO [テーブル名] VALUES ([値1], [値2], ...);例: `users` テーブルにデータを挿入 (idはAUTO_INCREMENT)
INSERT INTO users VALUES (NULL, 'john_doe', 'john@example.com', NOW());カラムを指定してINSERT: 特定のカラムに値を挿入します。指定しないカラムにはデフォルト値やNULLが入ります。
INSERT INTO [テーブル名] ([カラム名1], [カラム名2], ...) VALUES ([値1], [値2], ...);例: `users` テーブルの `username` と `email` を指定して挿入
INSERT INTO users (username, email) VALUES ('jane_smith', 'jane@example.com');複数行INSERT: 一度のINSERT文で複数の行を追加します。効率的です✨。
INSERT INTO [テーブル名] ([カラム名1], [カラム名2], ...) VALUES
([行1の値1], [行1の値2], ...),
([行2の値1], [行2の値2], ...),
...;例: `products` テーブルに複数の商品を追加
INSERT INTO products (name, price, category) VALUES
('Laptop', 120000, 'Electronics'),
('Mouse', 3000, 'Electronics'),
('Keyboard', 5000, 'Electronics');INSERT IGNORE: 主キーやユニークキーの重複エラーが発生した場合、その行の挿入を無視して処理を続行します。
INSERT IGNORE INTO [テーブル名] (...) VALUES (...);ON DUPLICATE KEY UPDATE: 主キーやユニークキーが重複した場合、挿入の代わりに指定したカラムを更新します。
INSERT INTO [テーブル名] ([カラム名1], [カラム名2], ...) VALUES ([値1], [値2], ...)
ON DUPLICATE KEY UPDATE [更新カラム名1] = [更新値1], [更新カラム名2] = [更新値2], ...;例: ユーザーが存在すればメールアドレスを更新、存在しなければ新規作成
INSERT INTO users (username, email) VALUES ('existing_user', 'new_email@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email); -- VALUES(カラム名)で挿入しようとした値を使用SELECT結果をINSERT: 他のテーブルからSELECTした結果を挿入します。
INSERT INTO [ターゲットテーブル名] ([カラム名1], ...)
SELECT [ソースカラム名1], ... FROM [ソーステーブル名] WHERE [条件];例: `active_users` テーブルに `users` テーブルのアクティブユーザーをコピー
INSERT INTO active_users (user_id, name)
SELECT id, username FROM users WHERE status = 'active';データ取得 (SELECT)
テーブルからデータを検索・取得します。
全カラム取得: テーブルのすべての行とカラムを取得します。
SELECT * FROM [テーブル名];例: `users` テーブルの全データを取得
SELECT * FROM users;特定カラム取得: 指定したカラムのみを取得します。
SELECT [カラム名1], [カラム名2], ... FROM [テーブル名];例: `users` テーブルから `username` と `email` を取得
SELECT username, email FROM users;カラムに別名をつける (AS):
SELECT username AS ユーザー名, email AS メールアドレス FROM users;条件指定 (WHERE): 特定の条件に一致する行のみを取得します。
SELECT [カラム名リスト] FROM [テーブル名] WHERE [条件];例: `users` テーブルから `id` が 1 のユーザーを取得
SELECT * FROM users WHERE id = 1;例: `products` テーブルから価格が 10000 より大きい商品を取得
SELECT * FROM products WHERE price > 10000;比較演算子: `=`, `!=` (<>), `>`, `<`, `>=`, `<=`
論理演算子: `AND`, `OR`, `NOT`
例: カテゴリが ‘Electronics’ で価格が 5000 未満の商品
SELECT * FROM products WHERE category = 'Electronics' AND price < 5000;その他の条件:
- `IN`: リスト内のいずれかの値に一致
WHERE status IN ('active', 'pending') - `BETWEEN`: 範囲内にある
WHERE price BETWEEN 1000 AND 5000 - `LIKE`: パターンマッチング (`%`: 任意文字列, `_`: 任意1文字)
WHERE email LIKE '%@example.com' - `IS NULL`: NULL値である
WHERE deleted_at IS NULL - `IS NOT NULL`: NULL値でない
WHERE email IS NOT NULL
結果の並び替え (ORDER BY): 取得した結果を指定したカラムで並び替えます。
SELECT [カラム名リスト] FROM [テーブル名] WHERE [条件] ORDER BY [カラム名1] [ASC|DESC], [カラム名2] [ASC|DESC], ...;`ASC`: 昇順 (デフォルト), `DESC`: 降順
例: `users` テーブルを作成日時の降順で取得
SELECT * FROM users ORDER BY created_at DESC;例: `products` をカテゴリ昇順、価格降順で取得
SELECT * FROM products ORDER BY category ASC, price DESC;取得件数制限 (LIMIT): 取得する行数を制限します。
SELECT [カラム名リスト] FROM [テーブル名] LIMIT [件数];例: 最新のユーザーを5件取得
SELECT * FROM users ORDER BY created_at DESC LIMIT 5;オフセット指定 (ページネーション等で使用):
SELECT [カラム名リスト] FROM [テーブル名] LIMIT [オフセット], [件数]; -- オフセットは0始まり例: 11件目から10件取得 (2ページ目)
SELECT * FROM products ORDER BY id LIMIT 10, 10;集計関数: データの集計を行います。
- `COUNT()`: 行数をカウント
SELECT COUNT(*) FROM users; - `SUM()`: 合計値を計算
SELECT SUM(price) FROM products; - `AVG()`: 平均値を計算
SELECT AVG(price) FROM products; - `MAX()`: 最大値を取得
SELECT MAX(price) FROM products; - `MIN()`: 最小値を取得
SELECT MIN(created_at) FROM users;
例: アクティブユーザーの数をカウント
SELECT COUNT(*) AS active_user_count FROM users WHERE status = 'active';グループ化 (GROUP BY): 特定のカラムの値ごとにデータをグループ化し、集計関数を適用します。
SELECT [カラム名1], [集計関数]([カラム名2]) FROM [テーブル名] GROUP BY [カラム名1];例: カテゴリごとの商品数をカウント
SELECT category, COUNT(*) FROM products GROUP BY category;グループ化した結果に条件を指定 (HAVING): WHERE句はグループ化前の行に適用され、HAVING句はグループ化後の結果に適用されます。
SELECT category, AVG(price) AS avg_price FROM products GROUP BY category HAVING avg_price > 5000;テーブル結合 (JOIN): 複数のテーブルを関連付けてデータを取得します。
| 種類 | 説明 | 構文例 |
|---|---|---|
| INNER JOIN | 両方のテーブルに結合キーが存在する行のみを取得 (内部結合) | |
| LEFT JOIN (または LEFT OUTER JOIN) | 左テーブルのすべての行と、右テーブルの一致する行を取得。一致しない場合は右テーブルのカラムはNULL (左外部結合) | |
| RIGHT JOIN (または RIGHT OUTER JOIN) | 右テーブルのすべての行と、左テーブルの一致する行を取得。一致しない場合は左テーブルのカラムはNULL (右外部結合) | |
| CROSS JOIN | 両テーブルの行のすべての組み合わせを取得 (デカルト積)。結合条件なし。 | |
結合条件は `ON` 句で指定します。テーブル名が長い場合は別名 (エイリアス) を使うと便利です。
サブクエリ: クエリの中に別のクエリを埋め込みます。
例: 平均価格より高い商品を取得
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);例: 注文履歴のあるユーザーを取得
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);結果の結合 (UNION / UNION ALL): 複数のSELECT文の結果を縦に結合します。
- `UNION`: 重複行を除外して結合します。
- `UNION ALL`: 重複行を含めて結合します (高速)。
結合するSELECT文のカラム数とデータ型は一致させる必要があります。
SELECT id, name FROM active_users
UNION ALL
SELECT id, name FROM inactive_users;データ更新 (UPDATE)
既存の行のデータを変更します。注意: WHERE句を省略すると、テーブル内のすべての行が更新されてしまいます！ 必ず条件を指定してください。🚨
UPDATE [テーブル名] SET [カラム名1] = [新しい値1], [カラム名2] = [新しい値2], ... WHERE [条件];例: `id` が 1 のユーザーのメールアドレスを更新
UPDATE users SET email = 'new_john@example.com' WHERE id = 1;例: 全商品の価格を10%上げる
UPDATE products SET price = price * 1.1; -- WHERE句がないので全件更新される他のテーブルの値を使って更新 (JOINを使用):
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.user_email = u.email
WHERE o.user_email IS NULL;データ削除 (DELETE)
テーブルから行を削除します。注意: WHERE句を省略すると、テーブル内のすべての行が削除されてしまいます！ 必ず条件を指定してください。🚨
DELETE FROM [テーブル名] WHERE [条件];例: `status` が ‘inactive’ のユーザーを削除
DELETE FROM users WHERE status = 'inactive';例: 特定の注文を削除
DELETE FROM orders WHERE order_id = 1001;他のテーブルの条件を使って削除 (JOINを使用):
DELETE o FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'deleted';全件削除 (TRUNCATE TABLE): テーブル内のすべての行を高速に削除します。DELETEと異なり、WHERE句は使えません。トランザクションログに記録されない場合があり、ロールバックできないことがあります。AUTO_INCREMENTの値もリセットされます。
TRUNCATE TABLE [テーブル名];例: `logs` テーブルの全データを削除
TRUNCATE TABLE logs;インデックス操作 🔍
検索パフォーマンスを向上させるためのインデックスに関する操作です。
インデックス表示
テーブルに設定されているインデックスの一覧を表示します。
SHOW INDEX FROM [テーブル名];または
SHOW INDEXES FROM [テーブル名];または
SHOW KEYS FROM [テーブル名];例: `users` テーブルのインデックスを表示
SHOW INDEX FROM users;インデックス作成
新しいインデックスを作成します。
CREATE INDEX [インデックス名] ON [テーブル名] ([カラム名1], [カラム名2], ...);例: `users` テーブルの `email` カラムにインデックスを作成
CREATE INDEX idx_email ON users (email);ユニークインデックスを作成 (重複を許さない):
CREATE UNIQUE INDEX [インデックス名] ON [テーブル名] ([カラム名]);例: `products` テーブルの `product_code` にユニークインデックスを作成
CREATE UNIQUE INDEX uidx_product_code ON products (product_code);フルテキストインデックスを作成 (全文検索用、MyISAMやInnoDBで使用可能):
CREATE FULLTEXT INDEX ft_idx_description ON articles (title, body);インデックス作成は `ALTER TABLE` でも可能です:
ALTER TABLE [テーブル名] ADD INDEX [インデックス名] ([カラム名]);インデックス削除
既存のインデックスを削除します。
DROP INDEX [インデックス名] ON [テーブル名];例: `users` テーブルから `idx_email` インデックスを削除
DROP INDEX idx_email ON users;インデックス削除は `ALTER TABLE` でも可能です:
ALTER TABLE [テーブル名] DROP INDEX [インデックス名];ユーザーと権限管理 👤🔑
MySQLユーザーの作成、権限の付与・剥奪、削除、パスワード変更などを行います。
ユーザー一覧表示
MySQLに登録されているユーザーの一覧を表示します。
SELECT user, host FROM mysql.user;ユーザー作成
新しいMySQLユーザーを作成します。`IDENTIFIED BY` でパスワードを設定します。
CREATE USER '[ユーザー名]'@'[ホスト名]' IDENTIFIED BY '[パスワード]';`ホスト名`には、接続を許可するホストを指定します。
- `localhost`: ローカルホストからの接続のみ許可
- `’%’`: 任意のホストからの接続を許可 (セキュリティに注意⚠️)
- `’192.168.1.%’`: 特定のIPアドレス範囲からの接続を許可
例: ローカルホストからのみ接続可能な `appuser` を作成
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password123';例: 任意のホストから接続可能な `remoteuser` を作成
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'strongPassword!@#';存在しない場合のみ作成:
CREATE USER IF NOT EXISTS 'appuser'@'localhost' IDENTIFIED BY 'password123';権限付与 (GRANT)
ユーザーにデータベースやテーブルへのアクセス権限を与えます。
GRANT [権限リスト] ON [データベース名].[テーブル名] TO '[ユーザー名]'@'[ホスト名]';主な権限:
- `ALL PRIVILEGES`: すべての権限
- `SELECT`: データ読み取り
- `INSERT`: データ挿入
- `UPDATE`: データ更新
- `DELETE`: データ削除
- `CREATE`: データベースやテーブルの作成
- `ALTER`: テーブル構造の変更
- `DROP`: データベースやテーブルの削除
- `INDEX`: インデックスの作成・削除
- `EXECUTE`: ストアドプロシージャの実行
- `GRANT OPTION`: 他のユーザーに権限を付与する権限
`[データベース名].[テーブル名]` の指定:
- `*.*`: すべてのデータベースのすべてのテーブル
- `[データベース名].*`: 特定のデータベースのすべてのテーブル
- `[データベース名].[テーブル名]`: 特定のテーブル
例: `appuser` に `myapp_db` データベースのすべてのテーブルに対する SELECT, INSERT, UPDATE, DELETE 権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'appuser'@'localhost';例: `adminuser` にすべての権限を付与 (GRANT OPTION付き)
GRANT ALL PRIVILEGES ON *.* TO 'adminuser'@'localhost' WITH GRANT OPTION;権限設定を反映させる:
FLUSH PRIVILEGES;(通常、GRANT文実行時に自動で反映されますが、手動で反映させたい場合に実行します)
権限確認 (SHOW GRANTS)
指定したユーザーに付与されている権限を確認します。
SHOW GRANTS FOR '[ユーザー名]'@'[ホスト名]';例: `appuser@localhost` の権限を確認
SHOW GRANTS FOR 'appuser'@'localhost';現在ログインしているユーザー自身の権限を確認:
SHOW GRANTS;権限剥奪 (REVOKE)
ユーザーから権限を取り消します。GRANT文と対になる構文です。
REVOKE [権限リスト] ON [データベース名].[テーブル名] FROM '[ユーザー名]'@'[ホスト名]';例: `appuser` から `myapp_db` に対する DELETE 権限を剥奪
REVOKE DELETE ON myapp_db.* FROM 'appuser'@'localhost';例: `adminuser` から GRANT OPTION を剥奪
REVOKE GRANT OPTION ON *.* FROM 'adminuser'@'localhost';権限設定を反映させる:
FLUSH PRIVILEGES;ユーザー削除 (DROP USER)
指定したユーザーをMySQLから削除します。
DROP USER '[ユーザー名]'@'[ホスト名]';例: `olduser` を削除
DROP USER 'olduser'@'localhost';存在する場合のみ削除:
DROP USER IF EXISTS 'olduser'@'localhost';パスワード変更
既存ユーザーのパスワードを変更します。
自分のパスワードを変更 (MySQL 5.7.6以降):
ALTER USER USER() IDENTIFIED BY '[新しいパスワード]';他のユーザーのパスワードを変更 (管理者権限が必要):
ALTER USER '[ユーザー名]'@'[ホスト名]' IDENTIFIED BY '[新しいパスワード]';例: `appuser@localhost` のパスワードを変更
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'newSecurePassword456';古いバージョン (MySQL 5.7.5以前) でのパスワード変更:
SET PASSWORD FOR '[ユーザー名]'@'[ホスト名]' = PASSWORD('[新しいパスワード]');PASSWORD() 関数はMySQL 8.0で廃止されました。
バックアップとリストア 💾🔄
データベースのバックアップ (エクスポート) とリストア (インポート) 方法です。主にシェルコマンド `mysqldump` と `mysql` を使用します。
バックアップ (mysqldump)
`mysqldump` コマンドは、データベースの構造やデータをSQL形式で出力します。
全データベースをバックアップ:
mysqldump -u [ユーザー名] -p --all-databases > backup_all.sql特定のイベントやルーチンも含める場合:
mysqldump -u [ユーザー名] -p --all-databases --routines --events > backup_all_full.sql特定のデータベースをバックアップ:
mysqldump -u [ユーザー名] -p [データベース名] > backup_[データベース名].sql例: `myapp_db` をバックアップ
mysqldump -u root -p myapp_db > backup_myapp_db.sql複数のデータベースをバックアップ:
mysqldump -u [ユーザー名] -p --databases [データベース名1] [データベース名2] ... > backup_multi_db.sql特定のテーブルのみをバックアップ:
mysqldump -u [ユーザー名] -p [データベース名] [テーブル名1] [テーブル名2] ... > backup_[テーブル名].sql例: `myapp_db` の `users` と `orders` テーブルをバックアップ
mysqldump -u root -p myapp_db users orders > backup_users_orders.sql構造のみバックアップ (データなし):
mysqldump -u [ユーザー名] -p --no-data [データベース名] > backup_structure.sqlデータのみバックアップ (構造なし):
mysqldump -u [ユーザー名] -p --no-create-info [データベース名] > backup_data.sql圧縮しながらバックアップ (gzipを使用):
mysqldump -u [ユーザー名] -p [データベース名] | gzip > backup_[データベース名].sql.gzリストア (mysqlコマンド)
`mysql` コマンドを使用して、`mysqldump` で作成したSQLファイルを実行し、データを復元します。
SQLファイルからリストア:
まず、リストア先のデータベースが存在しない場合は作成します (mysqldumpにCREATE DATABASE文が含まれていない場合)。
mysql -u [ユーザー名] -p -e "CREATE DATABASE IF NOT EXISTS [データベース名];"SQLファイルを実行してリストア:
mysql -u [ユーザー名] -p [データベース名] < backup_[データベース名].sql例: `myapp_db` にリストア
mysql -u root -p myapp_db < backup_myapp_db.sql全データベースのバックアップファイルからリストア:
mysql -u [ユーザー名] -p < backup_all.sql圧縮されたバックアップファイルからリストア (gzipを使用):
gunzip < backup_[データベース名].sql.gz | mysql -u [ユーザー名] -p [データベース名]注意: リストア操作は既存のデータを上書きする可能性があります。実行前に必ず確認し、必要であればリストア先のバックアップを取得してください。🚨
その他便利なコマンド・機能 ✨
MySQLクライアントやサーバーに関する情報表示や、操作を補助するコマンドです。
SQLファイル実行
SQL文が記述されたファイルを実行します。
mysqlクライアント内から:
source [ファイルパス];または
\. [ファイルパス]例:
source /path/to/my_script.sql;コマンドラインから (リストアと同様):
mysql -u [ユーザー名] -p [データベース名] < [ファイルパス]コマンド履歴
mysqlクライアント内で過去に実行したコマンドを確認できます。
履歴ファイルの場所は環境によって異なりますが、通常は `~/.mysql_history` です。
クライアント内で上下矢印キーを使うことで履歴を呼び出せます。
履歴を無効にするには:
mysql --histignore="*" ... (接続オプション)または、クライアント内で環境変数 `MYSQL_HISTIGNORE` を設定します。
サーバー状態表示 (STATUS)
現在の接続情報、サーバーバージョン、稼働時間などのステータスを表示します。
STATUS;または
\s変数表示 (SHOW VARIABLES)
MySQLサーバーのシステム変数の値を表示します。
すべての変数を表示:
SHOW VARIABLES;特定の変数を表示 (LIKEを使用):
SHOW VARIABLES LIKE '[パターン]';例: 文字コード関連の変数を表示
SHOW VARIABLES LIKE '%char%';例: `max_connections` の値を表示
SHOW VARIABLES LIKE 'max_connections';セッション変数とグローバル変数を区別して表示:
SHOW SESSION VARIABLES LIKE '...'; -- 現在のセッションのみ
SHOW GLOBAL VARIABLES LIKE '...'; -- サーバー全体の設定変数設定 (SET)
セッション変数やグローバル変数の値を変更します。
セッション変数を設定:
SET SESSION [変数名] = [値];または (SESSIONはデフォルト)
SET [変数名] = [値];例: SQLモードを変更
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,...';グローバル変数を設定 (SUPER権限が必要): サーバー全体の設定を変更します。再起動後も有効にするには設定ファイル (my.cnf / my.ini) の変更が必要です。
SET GLOBAL [変数名] = [値];例: 最大接続数を変更
SET GLOBAL max_connections = 500;ヘルプ表示 (help)
mysqlクライアント内でSQL文やコマンドのヘルプを表示します。
help [キーワード];例: SELECT文のヘルプを表示
help SELECT;例: データ型のヘルプを表示
help data types;利用可能なヘルプトピック一覧:
help contents;実行計画表示 (EXPLAIN)
SELECT, DELETE, INSERT, REPLACE, UPDATE文がどのように実行されるか（インデックスの使用状況、テーブルの結合順序など）を表示します。クエリのパフォーマンスチューニングに不可欠です🚀。
EXPLAIN [SQL文];例: usersテーブルの検索クエリの実行計画を表示
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';より詳細な情報を表示 (JSON形式など):
EXPLAIN FORMAT=JSON SELECT ...;EXPLAIN ANALYZE SELECT ...; -- MySQL 8.0.18以降: 実際にクエリを実行して計測情報も表示
コメント