MySQLの運用系コマンドとかをまとめてみた

MySQLの運用系を勉強する中で、よくでてくるコマンドたちをメモ代わりにまとめてみた。
(ほとんど、「やさしく学べるMySQL運用・管理入門」、「15時間でわかるMySQL集中講座」のコマンドまとめみたいな感じになっています。)
※随時アップデート予定
2018/08/28更新

## インストール(Yum・5.7)

```
sudo rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
sudo yum install mysql-community-server

#デフォルトパスワードの確認
sudo systemctl start mysqld
sudo cat /var/log/mysqld.log | grep password
```


## インストールならびに初期設定時

```
mysql_secure_installation
```

MySQLの初期設定をするコマンド

+ rootのパスワード設定
+ anonymousユーザの削除
+ rootユーザによるリモートからのアクセスを拒否
+ テスト用データベーススキーマの削除
+ 権限テーブルの更新

を設定します。


## ユーザー作成

```
CREATE USER <ユーザ名> IDENTIFIED BY '<パスワード>'
```

## 権限設定

```
GRANT (ALL PRIVILEGES|SELECT|INSERT|UPDATE|DELETE) ON <DB名>.<テーブル名> TO <ユーザー>.<接続可能範囲>
 ```

## ロール作成(8.0)

```
CREATE ROLE <ロール名>

#ロール名に対して権限を設定しておき、ユーザを割り当てることができる
GRANT <権限> ON <DB>.<テーブル> TO <ロール名>
GRANT <ユーザ> TO '<ユーザ名>'@'<接続元IP>'

## 処理エンジンの確認

MySQLクライアントにアクセス後、

```
mysql > SHOW ENGINES;
```

テーブルごとにエンジンを指定する場合は、

```
mysql > CREATE TABLE test (i INT) ENGINE = InnoDB;
```

みたいな感じで設定できる。

デフォルトはInnoDBだが変更する場合はmy.cnfに
`default-storage-engine=<エンジン名>`を指定。

## 設定ファイル(my.cnfとか)の読み込み先を探す

現在の変数設定情報も見れる

```
mysqld --verbose --help | less
```

lessとかのコマンドを入れないとずらーっとなる。


## mysql内で設定値の確認

```
#全体の変数
mysql > SHOW GLOBAL VARIABLES;
#接続単位の変数
mysql > SHOW SESSION VARIABLES;
```

```
#さらに詳細を確認する
mysql > SELECT * FROM performance_schema.variables_info;
```
ここの設定値(デフォルト)を変更するときに、my.cnfを使う。

## ステータスの確認
```
mysql > show global status
```

## 変数の一時的設定変更

```
mysql > SET GLOBAL <設定名> = <設定値>
mysql > SET SESSION <設定名> = <設定値>

#slow_query_logの設定を変更
mysql > SET GLOBAL slow_query_log = 'ON';
```

## オンラインでの変数変更永続化
```
SET PERSIST <設定名> = <設定値>

## mysqlのコマンドヘルプ

```
mysql > HELP

#UPDATE構文を確認する場合

mysql > HELP UPDATE
```

## 接続状況の確認
```
mysql > SHOW FULL PROCESSLIST;
```

## テーブル情報

```
mysql > SHOW TABLE STATUS FROM <DB名>
```

## InnoDBのステータス
```
SHOW ENGINE INNODB STATUS
```

## クエリの実行結果を表形式ではなく、行単位で表示させる
```
mysql > SELECT * FROM test \G;

# logを含む設定項目を行単位で表示する
mysql> show global variables LIKE '%log%'\G;
*************************** 1. row ***************************
Variable_name: back_log
        Value: 80
*************************** 2. row ***************************
Variable_name: binlog_cache_size
        Value: 32768
*************************** 3. row ***************************
Variable_name: binlog_checksum
        Value: CRC32
```

## クエリの実行結果をlessで表示させる

```
#設定
mysql > pager less

#設定を解除する
mysql > nopager
```

## ベンチマークを取得する

```
./mysqlslap --query <クエリ> -concurrency <多重度> -iterations <クエリの実行回数> ・・・

#自動でSQL文を設定し、指定したエンジンでどれくらいのスピードで実行されるか計測
多重度5,繰り返し実行回数10

mysqlslap -uroot -p -a --auto-generate-sql-add-autoincrement --number-int-cols=3 --number-char-cols=2 --engine=InnoDB,MEMORY,MyISAM  --concurrency=5 --iterations=10

```

## 論理バックアップ

```
mysqldump --database <データベース名> --table <テーブル名>

#demoデータベース全体をバックアップ
mysqldump -uroot -p --database demo　> dbdump

#demoデータベースのtestテーブルのみをバックアップ
mysqldump -uroot -p --database demo --table test > dbdump
```

## バイナリログを読む

```
mysqlbinlog --verbose <binlogのdir>
```

## バイナリログを使ったリカバリ

```
mysqlbinlog <binlog名(複数の場合は古いものから順番に)> | mysql -u <ユーザ> -p

#オプションに以下が有効
--stop-datatime=復元したいチェックポイントの時間
--stop-position=復元したいチェックポイントポジション

```
## 現在のbinlogのファイルとポジションを確認
```

mysql > show master status;
```

## フルバックアップ時のbinlogファイルとpositionの確認
```
grep MASTER_LOG_POS /tmp/dump_pitr_example.sql | head -1
```

## テーブル定義の情報を取得する
```
mysql > SHOW CREATE TABLE <テーブル名>;
```



## テーブルの情報を確認する

```
mysql > SHOW TABLE STATUS;
または
mysql > SELECT * FROM information_schema.TABLES;
## usersテーブルの情報を取得

mysql > SHOW TABLE STATUS LIKE 'users'\G;

*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 29
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 7
    Create_time: 2018-07-15 15:37:20
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 

```

## INDEXの利用状況を確認する

```
EXPLAIN SELECT ・・・
```

## WARNINGSを確認する

```
SHOW WARNINGS;
```


## レプリケーション
```
#(マスタ)レプリケーション用ユーザ権限設定

GRANT REPLICATION SLAVE ON *.* TO <ユーザ>@<接続元>

#(スレーブ)マスタ先の設定とスレーブ開始
CHANGE MASTER TO
  MASTER_HOST=<マスタIP>
  MASTER_USER=<レプリケーション用ユーザ>
  MASTER_PASSWORD=<レプリケーション用ユーザパスワード>
  MASTER_LOG_FILE=<スレーブ開始のbinlogファイル>
  MASTER_LOG_POS=<スレーブ開始のbinlogポジション>
# 指定ポジション以降のデータをリレーログする
# マスタでmysqldump -> dumpファイルからCHANGE MASTERの項目を確認しダンプ時のログのファイルとポジションを確認するとよい

```