docs » database
#
ドキュメント

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 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>';

権限

権限確認

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 {{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;

タイムゾーン

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

$ 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;