MySQL メモ

homebrew

編集(MAC)
sudo vi /usr/local/etc/my.cnf

brew services start mysql@5.7

MySQLのインストール
$ brew install mysql
起動
$ mysql.server start
停止
$ mysql.server stop
ステータス確認
$ mysql.server status

自動起動有効化
brew services start mysql
> Successfully started 'mysql' と表示されれば成功。

自動起動無効化
brew services stop mysql
> Successfully stopped 'mysql' と表示されれば成功。

自動起動を有効化しているサービスを確認
brew services list

キャッシュクリア

mysql> RESET QUERY CACHE;

現在のキャッシュ状況を確認

mysql> SHOW STATUS LIKE 'Qcache%';
+--------------------------------+----------+
| Qcache_total_blocks            | 500      |

MySQL8

MySQL 8 で、デフォルトの認証方式が変更

Warning: mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

認証方式変更

ALTER USER 'wp_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'wp_pass';

my.cnf で、デフォルトを従来形式のパスワードを使う（要再起動）

default_authentication_plugin=mysql_native_password

MySQL 5.7 設定

[mysqld]
validate-password=OFF
performance_schema=off
table_definition_cache=400
innodb_ft_min_token_size=1

MySQL 設定

/usr/local/etc/my.cnf

動作モード注意 STRICT_TRANS_TABLES 推奨
カラムに BINARY オプション
utf8mb4_bin の場合 VARCHAR の INDEX は 190 まで
フルテキストINDEX 利用時の my.conf 設定

MAC での利用

MAC homebrew

mysql.server stop
mysql.server restart
mysql.server start

動作モード

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

mysql> SELECT @@SESSION.sql_mode;

NO_ENGINE_SUBSTITUTION
デフォルトのストレージエンジンの自動置換 (substitution) を防ぐ。これは、CREATE TABLE のようなステートメントが、無効化した、またはコンパイルしたストレージエンジンを指定するときのこと。エラーで知らせる。

STRICT_TRANS_TABLES
指定された値をトランザクションテーブルに挿入できない場合、クエリの実行を中断する。非トランザクションテーブルでは、値が 1 行ステートメントの場合、または複数行ステートメントの最初の行である場合に、クエリを中断する。

http://gihyo.jp/dev/serial/01/mysql-road-construction-news/0018

DSN で SOCKET 指定

mysql:host=localhost;dbname=test;charset=utf8;unix_socket=/tmp/mysql.sock

フルテキストINDEX

インデックスが付けられる単語の最小長設定

// my.conf
innodb_ft_min_token_size=1

確認

mysql> SHOW VARIABLES like 'innodb_ft_min_token_size';

特殊文字対応

`code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,

utf8mb4 の場合は INDEX 出来るのは 190 まで
  `title` varchar(190) DEFAULT NULL,

PDOに複文実行を禁止するオプション

PHP 5.5.21、PHP 5.6.5 以降

if (defined('PDO::MYSQL_ATTR_MULTI_STATEMENTS')) {
    $options[PDO::MYSQL_ATTR_MULTI_STATEMENTS] = false;
}

BINARY属性

大文字小文字 別扱い
ALTER TABLE `table_name` MODIFY `hoge_id` VARCHAR(20) BINARY NOT NULL;

コネクション数確認

mysql> use mysql;
mysql> show global status like 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 2     |
+-------------------+-------+

test table

CREATE TABLE IF NOT EXISTS `test` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(125) NOT NULL,
 `status` TINYINT NOT NULL DEFAULT '0',
 `created_at` DATETIME NOT NULL,
 `updated_at` DATETIME NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `UNIQUE_name` (`name`)
) ENGINE = InnoDB;

InnoDBテーブルしかないDBを1つダンプする場合

mysqldump --quote-names --skip-lock-tables --single-transaction --flush-logs --master-data=2 <database> > dump.sql

mysqldump --quote-names --skip-lock-tables --single-transaction --flush-logs --master-data=2 <database> <table> > table.sql

条件指定でdump

mysqldump -u user DB名 --where 'is_delete = 0' > dump.sql

※ テーブル初期化SQLも含まれるので注意

オプション

よく使うmysqldumpのオプションと使用例

SELECT 結果ファイル保存

mysql -uroot -p -e "SELECT * FROM table_name" db_name > /tmp/mysql.tsv

Mac Mysql Socket 対応

DSN

 'dsn' => 'mysql:unix_socket=/tmp/mysql.sock;dbname=NAME;host=localhost;charset=utf8'

php.ini

mysql.default_socket = '/tmp/mysql.sock'
mysqli.default_socket = '/tmp/mysql.sock'
pdo_mysql.default_socket = '/tmp/mysql.sock'

gzip dump

mysqldump -u user -p password dbname | gzip > dbname.dump.gz

zcat dbname.dump.gz | mysql -u user -p password dbname

パーティショニング

ALTER TABLE events DROP PRIMARY KEY, ADD PRIMARY KEY(id, created_at);

ALTER TABLE events
  PARTITION BY RANGE COLUMNS(created_at) (
  PARTITION p201303 VALUES LESS THAN ('2013-04-01 00:00:00'),
  PARTITION p201304 VALUES LESS THAN ('2013-05-01 00:00:00'),
  PARTITION p201305 VALUES LESS THAN ('2013-06-01 00:00:00'),
  PARTITION p201306 VALUES LESS THAN ('2013-07-01 00:00:00')
);

確認

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'word';

開発機でクエリー確認

https://dev.mysql.com/doc/refman/5.6/ja/query-log.html

SET GLOBAL general_log = 'ON';

ログの出力先調べる

mysql> show variables like '%general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+

ストレージ要件

https://dev.mysql.com/doc/refman/5.6/ja/storage-requirements.html

バッファクエリと非バッファクエリ

http://php.net/manual/ja/mysqlinfo.concepts.buffering.php

    $dbh = new PDO('mysql:host=localhost;dbname=testdb','user','pass');
    //非バッファクエリの設定
    $dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,false);

    $stmt = $dbh->prepare('select * from members');
    $stmt->execute();

    while($row = $stmt->fetch()) {
        echo $row['name']."<br>";
    }

Laravel
Laravelでは、QueryLogの記憶をdisableにする。

DB::connection()->disableQueryLog();

Laravel Eloquent ORMでMySQLの非バッファクエリを使う
https://qiita.com/Fea/items/ec1f263beeda3815875f

メモリ使用量

パフォーマンススキーマ
mysql> SHOW VARIABLES LIKE 'perf%';

[mysqld]
performance_schema=off

table_definition_cache
mysql> show variables like 'table_definition_cache';

[mysqld]
table_definition_cache=400

編集(MAC)
sudo vi /usr/local/etc/my.cnf

リスタート(MAC)
mysql.server restart

■ MySQLでtable_definition_cacheの値を変更する
http://blog.ybbo.net/2015/12/26/change-table_definition_cache-on-mysql/

■ MySQL5.6にてperformance_schemaを停止したところ、メモリの使用量が圧倒的に減少した
http://blog.ybbo.net/2015/07/20/performance_schema-off-on-mysql5-6/

■ 5分で出来るMySQLのお手軽チューニング
http://nick.hateblo.jp/entry/2014/01/26/183822

■ MySQLパフォーマンスチューニング
https://qiita.com/mamy1326

■ MySQL 5.7 トラブルシューティング性能解析入門編
https://www.slideshare.net/nippondanji/mysql-57-77003983