ドキュメント

Document

自分のための備忘録です。

MySQL

設定ファイル（ my.cnf ）
文字セットと照合順序
タイムゾーン確認
UNIX ドメインソケットファイル（ mysql.sock ）
データ（物理）ファイル
ログ
バッククォートとシングルクォート
ホストのワイルドカード
バージョン確認
mysqlクライアントで接続
データベース作成
テーブル作成
テーブル定義参照
ユーザー
パスワード
権限
SQLダンプ
検索
Bool/Boolean型
日付操作
mysql.pid
トラブルシューティング

設定ファイル（ my.cnf ）

MySQL 設定ファイル（ my.cnf ）

文字セット

文字セットと照合順序

タイムゾーン

UNIX ドメインソケットファイル（ mysql.sock ）

UNIX ドメインソケットファイル

データディレクトリ(物理ファイル)

データファイル

ログ

バッククォートとシングルクォート

バックォート
- MySQL の予約語をエスケープします
  $ select * form `table`;
- テーブル名/カラム名のハイフン（ - ）をエスケープします
  $ select * from `example-hyphen`;
シングルクォート/ダブルクォート
MySQL の文字列リテラルはシングルクォートまたはダブルクォート文字です

ホストのワイルドカード

_: 任意の1文字
%: 0以上の任意の文字

MySQLはアカウント（ ユーザー + ホスト ）でクライアントを識別します。 _ 、 % はホストのワイルドカードとして使用できます。

バージョンを確認

$ mysql --version

mysql クライアントで接続

$ mysql -u example -p -D example_database

上記はユーザー example 、ホスト localhost で example_databse へ接続します。
接続先ホストを指定するときは -h オプションを使います。

$ mysql -h 127.0.0.1 -u example -p -D example_database

データベース作成

CREATE DATABASE <database> DEFAULT CHARACTER SET utf8mb4;

テーブル作成

CREATE TABLE sample (
    id INT AUTO_INCREMENT NOT NULL,
    created_at DATE,
    category VARCHAR(255),
    name  VARCHAR(255),
    amount INT,
    PRIMARY KEY(id)
);

レコード挿入例。

INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-10-01', '通信', '携帯', '7000');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-10-01', '通信', 'インターネット', '2500');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-11-01', '通信', '携帯', '6500');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-11-01', '通信', 'インターネット', '4000');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-12-01', '通信', '携帯', '8000');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-12-01', '通信', 'インターネット', '3000');

INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-10-01', '光熱', '水道', '3000');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-10-01', '光熱', 'ガス', '3000');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-11-01', '光熱', '水道', '1800');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-11-01', '光熱', 'ガス', '2000');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-12-01', '光熱', '水道', '1600');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-12-01', '光熱', 'ガス', '2100');

INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-10-01', '食費', '昼食', '10000');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-11-01', '食費', '昼食', '20000');
INSERT INTO sample (created_at, category, name, amount) VALUES ('2018-12-01', '食費', '昼食', '30000');

UNITON ALLを使った総計。

SELECT
       DATE_FORMAT(us.created_at, '%Y-%m')  AS 年月,
       us.分類,
       us.金額
FROM
    (
     SELECT
            1 AS ソートキー,
            created_at,
            DATE_FORMAT(created_at, '%Y-%m') as 年月,
            category AS 分類,
            sum(amount) AS 金額
     FROM
          union_sample
     GROUP BY
              DATE_FORMAT(created_at, '%Y-%m'),
              category
     UNION ALL
     SELECT
            2 AS ソートキー,
            created_at,
            DATE_FORMAT(created_at, '%Y-%m') AS 年月,
            '総計' AS 分類,
            sum(amount) AS 金額
     FROM
          union_sample
     GROUP BY
              DATE_FORMAT(created_at, '%Y-%m')
     ) us
ORDER BY
    DATE_FORMAT(us.created_at, '%Y-%m'),
    ソートキー;

テーブル定義参照

SHOW CREATE TABLE example_database;
DESCRIBE example_database;

ユーザー

ユーザー一覧

SELECT user, host FROM mysql.user;

ユーザー作成

CREATE USER 'example'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'example'@'127.0.0.1' IDENTIFIED BY 'password';
CREATE USER 'example' IDENTIFIED BY 'password'; // ホストがワイルドカード(%)でユーザーが作成される

hostを省略したときはすべてのホストからアクセスできるワイルドカード%が指定されます。

ユーザー作成はできる限りホストを指定してください(例開発環境 localhost,127.0.0.1)。
ホストを指定せず作成したユーザーではlocalhostでアクセスできません。
詳しくは下記記事を参照ください。

ワイルドカード%の詳細と注意点は下記記事を参照してください。
MySQL ユーザのホストをワイルドカードで指定してもlocalhostは含まれない - b.l0g.jp

ユーザー削除

DROP USER 'example'; // ホストがワイルドカードのexampleユーザー削除
DROP USER 'example'@'localhost'; ホストがlocalhostのexampleユーザー削除
DROP USER 'example'@'127.0.0.1'; ホストが127.0.0.1のexampleユーザー削除

パスワード変更

SET PASSWORD = PASSWORD('<password>'); // ログインユーザーのパスワード変更
SET PASSWORD FOR '<user>' = PASSWORD('<password>'); // 指定ユーザーのパスワード変更

// または
// この方法ではユーザー名とパスワードを同じにしたときログインできなくなりました。
UPDATE mysql.user SET password=password('<password>') WHERE user = '<user>';

権限

MySQL の権限は各ユーザーごとに mysql.user テーブルのカラムで管理されています。

ref. 6.2.2 MySQL で提供される権限

権限確認

SHOW GRANTS FOR '<user>'@'<host>';

rootの例

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        |
+---------------------------------------------------------------------+

権限が付与されていないときの例

+----------------------------------------------------------------------------------------------------------------+
| Grants for example@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'example'@'localhost' IDENTIFIED BY PASSWORD '*57237BB49761F29AB9724BA084E811D70C12393D' |
+----------------------------------------------------------------------------------------------------------------+

権限権限付与

権限追加

GRANT ALL PRIVILEGES ON example_db.* TO 'example'@'localhost';
GRANT ALL PRIVILEGES ON example_db.* TO 'example'@'127.0.0.1';
FLUSH PRIVILEGES;

ユーザー新規作成および権限付与

ユーザー新規作成と権限付与を同時に行います。

GRANT ALL PRIVILEGES ON `<db>`.`*` TO '<user>'@'<host>' IDENTIFIED BY '<password>' WITH GRANT OPTION;
FLUSH PRIVILEGES;

hostはローカルのときはlocalhostまたはループバックアドレス127.0.0.1が多い。

例1)

ホスト192.168.33.1, ユーザーroot, パスワードwordpressで全てのテーブルへ全ての処理権限を追加します。

GRANT ALL PRIVILEGES ON *.* TO root@"192.168.33.1" IDENTIFIED BY 'wordpress' WITH GRANT OPTION;
FLUSH PRIVILEGES;

権限削除

REVOKE ALL ON  `example_db`.* FROM 'example';

SQLダンプ

ダンプ

$ mysqldump -u {{db user}} -p {{db name}} > dump.sql

ダンプのオプション

--single-transaction
--skip-lock-tables
--flush-logs

ref. https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html

運用を止めずにダンプする

$ mysqldump  -u {{db user}} -h {{db host}} -p --skip-lock-tables --single-transaction  --no-tablespaces {{db name}} > dump.sql

mysqldumpのデフォルトでは、ダンプ処理中の書き込み処理をロックします（--lock-tablesが暗黙的に有効になっている）。
そのためにタンプ処理中はデータベースに書き込みができないため、（メンテナンスモードなどで）サイトを停止する必要があります。

しかし --single-transaction と --skip-lock-tables オプションを付与することでサイトを停止せずにダンプすることができます。

--single-transaction は --lock-tables を使用ぜずに整合性を保ってダンプするためのオプション
--single-transaction を使用せずに整合性を保ってダンプするには --lock-tables が必要（ --lock-tables はデフォルトで有効化される場合が多い）
--lock-tables はダンプ中の書き込み処理をロックする
--skip-lock-tables は --lock-tables を打ち消す（ mysqldump バージョンの多くが --lock-tables をデフォルトで有効化しているので明示的に打ち消すのが安心）

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

-- man mysqldump

--no-tablespaces

MySQL 5.7.31以降から、--no-tablespaces も必要になりました。

MySQL 5.7.31以降では --no-tablespaces を指定しないと PROCESS 権限がない旨のエラーが発生します

Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
根本的には PROCESS 権限を付与して解消することが望ましいが、レンタルサーバなどの権限付与ができない場合は --no-tablespaces を付与します

例えばさくらのレンタルサーバでは PROCESS 権限を付与することはできなかった。

> GRANT PROCESS ON *.* TO 'example';
ERROR 1045 (28000): Access denied for user 'example'@'%' (using password: YES)

--no-tablespaces を付けて良いか

以下リンクを読む限り tablespace は DB の論理情報と物理データを結びつける情報を格納しているようなので、新サーバでリストアする時には必要はなさそう。よって --no-tablespaces を付けても良さそう。

ref. https://anothercoffee.net/how-to-fix-the-mysqldump-access-denied-process-privilege-error/

リストア

$ mysql -u <user> -p <db> < example.sql

さくらのレンタルサーバスタンダードプランのでSSHを使ってリストア

$ mysql -u {{db user}} -h {{db host}} -p {{db name}} < dump.sql

検索

データベースから特定の列(フィールド)をもつテーブルを抽出します。

select table_name, column_name from information_schema.columns where column_name = '検索したいカラム名' and table_schema = '検索対象のデータベース名';

MySQLで指定されたカラム名を持つテーブルを検索する - Qiita

Bool/Boolean型

BOOL、BOOLEAN

これらの型は TINYINT(1) のシノニムです。ゼロの値は false と見なされます。ゼロ以外の値は true と見なされます。

https://dev.mysql.com/doc/refman/5.6/ja/numeric-type-overview.html

CREATE TABLE bool_test(id int, enabled bool);

INSERT INTO bool_test(id, enabled) VALUE(1, true);

id	enabled
1	1

SHOW CREATE TABLE bool_test;

CREATE TABLE `bool_test` (
  `id` int(11) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

-- enableが1のものを抽出
SELECT * FROM bool_test WHERE enabled = true;

日付操作

12.7 日付および時間関数

mysql.pid

本節は Mac の場合です。

MySQL サーバー（ mysqld ）起動時に /usr/local/var/mysql に XXXXX.local.pid が作成されます。

ERROR! MySQL server PID file could not be found!

mysq.server start/stop で ERROR! MySQL server PID file could not be found! が発生したときの対処法です。

$ ps aux | grep mysql

46498   0.0  0.1  3087484   4276   ??  S    10:24AM   0:05.56 /usr/local/Cellar/mysql/5.6.22/bin/mysqld --basedir=/usr/local/Cellar/mysql/5.6.22 --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/Cellar/mysql/5.6.22/lib/plugin --log-error=/usr/local/var/mysql/sawaihiroshi-no-MacBook-Air.local.err --pid-file=/usr/local/var/mysql/sawaihiroshi-no-MacBook-Air.local.pid

$ kill 46498

トラブルシューティング

TimeMachineで移行したデータファイルが壊れる

MacのTimeMachineで移行したデータファイルが壊れるという問題が発生しました(複数回)。

発生した問題

データベースが壊れる
一部のテーブルが壊れる
テーブルは存在するがアクセスできず削除もできない。

問題の解決策

mysqld停止
既存データファイル削除(拡張子frm, ibd)
新規データベースやテーブル作成

既存データファイルの場所は下記SQLで確認できます。

SHOW VARIABLES LIKE 'datadir';

Homebrewでインストールしたときのデータディレクトリの例

/usr/local/var/mysql/

commnad

mysql> set transaction isolation level repeatable read;