#
ドキュメント

Document

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

MySQL

my.cnf

$ mysql --help | grep my.cnf
// 出力例
// order of preference, my.cnf, $MYSQL_TCP_PORT,
// /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf 

読み込み順

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. /usr/etc/my.cnf
  4. ~/.my.cnf

上記の順に読み込まれ上書きされていきます。

my.cnf の読み込む順番でハマったのでまとめる - Qiita<

文字コード

utfmb4へ設定

文字コード確認

> show variables like "chara%";

utfmb4の設定は、~/.my.cnfで行います。私のMacでは読み込み順の1-4の設定ファイルはすべてが未制作でしたので~/.my.cnfを作成し下記を追加しました。

[mysqld]
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

mysql.sock

mysql.sockはUNIXドメインソケットファイルです。MacではデフォルトでMySQLサーバー(mysqld)起動時に/tmpフォルダへ作成されます。
ローカル内では/tmp/mysql.sock(UNIXドメインソケットファイル)を通してクライアント(例PHPプログラムやmysqlコマンド)とサーバーは通信します。

mysql.sockの場所はMySQLが起動した状態で下記SQLで確認できます。

mysql > SHOW VARIABLES LIKE '%sock%';

「Can't connect to local MySQL server through socket」エラーについて 【mysql】mysql.sockの場所の確認とmysql_configの注意点 - tweeeetyのぶろぐ的めも MySQL クライアントからサーバーへの接続

UNIXドメインソケット - Wikipedia

タイムゾーン

mysql > show variables like '%time_zone%';

JSTは日本時間

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+

mysql.pid

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

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

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

mysql > SHOW VARIABLES LIKE 'datadir';

MacへHomebrewでインストールしたときの物理ファイルは下記のようになります。  

/usr/local/var/mysql

エラーログ(xxx.local.err)

/usr/local/var/mysqlディレクトリにXXXXX.local.errファイルが作成されます。

/usr/local/var/mysql/foo.local.err

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

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

ホストのワイルドカード%(パーセント)

MySQLはホストとユーザー名でクライアントを識別します。 %はホストのワイルドカードとして使用されます。

バージョン確認

$ mysql --version

mysqlクライアントで接続

$ mysql -u example -p -A example_database

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

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

ローカル環境で試した限り127.0.0.1を指定してもexample@localhostで接続されます。

データベース作成

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 union_sample (created_at, category, name, amount) VALUES ('2018-10-01', '通信', '携帯', '7000');
INSERT INTO union_sample (created_at, category, name, amount) VALUES ('2018-10-01', '通信', 'インターネット', '2500');
INSERT INTO union_sample (created_at, category, name, amount) VALUES ('2018-11-01', '通信', '携帯', '6500');
INSERT INTO union_sample (created_at, category, name, amount) VALUES ('2018-11-01', '通信', 'インターネット', '4000');
INSERT INTO union_sample (created_at, category, name, amount) VALUES ('2018-12-01', '通信', '携帯', '8000');
INSERT INTO union_sample (created_at, category, name, amount) VALUES ('2018-12-01', '通信', 'インターネット', '3000');

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

INSERT INTO union_sample (created_at, category, name, amount) VALUES ('2018-10-01', '食費', '昼食', '10000');
INSERT INTO union_sample (created_at, category, name, amount) VALUES ('2018-11-01', '食費', '昼食', '20000');
INSERT INTO union_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>';

権限

権限確認

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;

SQLダンプ

ダンプ

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

リストア

$ mysql -u <user> -p <db> < example.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;

タイムゾーン

準備 タイムゾーン用のデータロード

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

タイムゾーンの確認

SELECT @@global.time_zone, @@session.time_zone;

タイムゾーンの設定

MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.6 MySQL Server でのタイムゾーンのサポート

以下の2つの方法で設定できます。

  1. .my.cnf
  2. SQL発行

.my.cnf

グローバルタイムゾーンは、~/.my.cnfで設定できます。

[mysqld]
default-time-zone = 'Asia/Tokyo'

SQL発行

グローバルタイムゾーンを設定します。

SET GLOBAL time_zone = 'Asia/Tokyo';

セッションタイムゾーンを設定します。

SET time_zone ='Asia/Tokyo';

日付操作

トラブルシューティング

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

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

発生した問題

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

問題の解決策

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

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

SHOW VARIABLES LIKE 'datadir';

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

/usr/local/var/mysql/ 

commnad

mysql> set transaction isolation level repeatable read;