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の項目を確認しダンプ時のログのファイルとポジションを確認するとよい