MySQLリファレンス

< MariaDB

システム情報

コマンド	説明
¥?	サーバステータス、クライアントコマンド一覧
SHOW STATUS	システム変数の表示
SET	システム変数の変更

起動

コマンド	説明
mysql_secure_installation	初期化。10.4以降は必要なし
mysql -u user -p db_name	ログイン
status	セッション情報を表示
SHOW TABLE STATUS¥G	auto_increment値の確認
ALTER TABLE hoge AUTO_INCREMENT=3;	auto_increment値のセット

auto_increment値の初期化

mysql > CREATE TABLE tmp LIKE hoge;    ← カラム定義だけ同じテーブルを作成
mysql > CREATE TABLE tmp AS SELECT * FROM hoge WHERE 1=2;    ← この書き方もできる
mysql > SET @a = 0;
mysql > INSERT INTO tmp SELECT @a := @a + 1 AS id, name FROM hoge

＊ 主キーが削除できない時はその列にインデックスを追加すると削除できるようになる

ユーザ管理

コマンド	説明
SELECT user, host FROM mysql.user;	ユーザリストを表示
GRANT ALL ON sampledb.* TO user@localhost IDENTIFIED BY 'passwd';	ユーザの追加・権限付与
GRANT INSERT, DELETE ON sampledb.* TO user@localhost;	権限付与
REVOKE INSERT, DELETE ON sampledb.* FROM user@localhost;	権限の取り消し
SHOW GRANTS FOR user@localhost¥G	権限を表示
FLUSH PRIVILEGES;	権限の更新
[CREATE/DROP] USER user@localhost;	ユーザを作成/削除
RENAME USER old_name TO new_name;	ユーザ名の変更
DROP USER user@localhost;	ユーザの削除
SET PASSWORD FOR user@localhost = password('new_passwd');	パスワード変更（rootで操作）
SET PASSWORD = password('new_passwd');	パスワード変更（ユーザで操作）

データベース操作

コマンド	説明
[CREATE/ALTER/DROP] DATABASE db_name;	データベースを作成/属性変更/削除
SHOW DATABAES;	データベースをリスト表示
USE db_name	データベースを選択

テーブル操作

コマンド	説明
SHOW TABLES;	テーブルをリスト表示
CREATE TABLE hoge (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, name char(10) DEFAULT 'hoge' NOT NULL, birth date NOT NULL);	テーブルの作成
CREATE TALBE new_tb LIKE org_tb → INSERT INTO new_tb SELECT * FROM org_ot	テーブルのコピー
RENAME TABLE old_name TO new_name;	テーブル/ビューの名前変更
ALTER TABLE hoge CHANGE old_row new_row int NOT NULL;	列の名前変更
ALTER TABLE hoge MODIFY row_name char(50) NOT NULL;	列の属性変更
ALTER TABLE hoge ADD new_row char(50) NOT NULL;	列の追加
ALTER TABLE hoge DROP row_name;	列の削除
ALTER TABLE customerlist ADD FOREIGN KEY (companycode) REFERENCES companylist (companycode);	外部キーの追加（参照先フィールドには一意性が必要）
ALTER TABLE customerlist DROP FOREIGN KEY customerlist_ibfk_1;	外部キーの削除（制約名は SHOW CREATE TABLE で別途参照）
CREATE/ALTER/DROP VIEW v_name;	ビューを作成/属性変更/削除
SHOW CREATE VIEW v_name;	作成したビューを表示

データ操作

コマンド	説明
[INSERT/UPDATE/DELETE]	データを挿入/更新/削除
SELECT	データを取得
SELECT DISTINCT	重複は１つにまとめてデータを取得
INSERT INTO SELECT	SELECTした結果を挿入
[LOCK/UNLOCK] TABLES	テーブルをロック/ロック解除
[CREATE/SHOW/DROP] INDEX	インデックスを作成/リスト表示/削除
CREATE FUNCTION	ストアドファンクションを作成
SHOW CREATE FUNCTION	ストアドファンクションを表示
SHOW FUNCTION STATUS	ストアドファンクションの状態を表示
[CREATE/DROP] TRIGGER	トリガを作成/削除
SHOW TRIGGERS	トリガのリスト表示
[START/STOP] SLAVE	スレーブ機能を開始/停止
START TRANSACTION	トランザクション機能を開始

＊外部キーを追加するときは結びつけるカラムが相互に同じ型である事、挿入データが既にある場合は値が全て一致している必要がある

ダンプとリストア

ダンプとリストアには４つの方法がある。

1. データファイル群のコピー - 古いディレクトリを tar で圧縮してから新しいディレクトリで展開
2. mysqldump による論理バックアップとリストア - テーブル生成とデータ挿入の SQL文の塊として
3. XtraBackup による物理バックアップとリストア - Percona社が提供しているオンライン物理バックアップツール。無料ながら非常に高機能
4. LVM によるスナップショットとリストア - Linux の LVM機能を利用してバックアップを取る。fdisk や pvcreate などを利用

mysqldump の例：

# mysqldump -u root -B sampledb > dump.sql    ← ダンプ（-Bオプションを付けると同じデータベース名になる。代わりに -p）
# mysql -u root -D sampledb < dump.sql    ← リストア

Mysqlクライアント

説明	コマンド
データベースの作成	mysqladmin -u root -p create sampledb
ステータスの表示	mysqladmin -u root -p status
稼働状況の表示	mysqladmin -u root -p status
スレッドリストの表示	mysqladmin -u root -p processlist
スレッド番号3を kill	mysqladmin -u root -p kill 3
パスワードを変更	mysqladmin -u root -p password 'new-password'
スレーブ機能を開始	mysqladmin -u root -p start-slave