masalibの日記

システム開発、運用と猫の写真ブログです

トップ > mysql > MySQLの設定とかコマンド

MySQLの設定とかコマンド

mysql

忘れっぽいので設定とかコマンドを記載をする

前提としては
OS:CentOS7
mysqlのバージョン：8以上
SELinux：停止
FireWall：停止
事前インストールパッケージ：zip zlib zlib-devel openssl-devel sqlite-devel gcc-c++ glibc-headers libyaml-devel readline readline-devel zlib-devel libffi-devel epel-release wget

インストール

# デフォルトで入っているmariadbを削除する
$ yum remove mariadb* -y
$ yum localinstall -y https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
$ yum install -y mysql-community-server

インストール後の起動や設定など

サーバ起動

$ systemctl start mysqld

サーバ停止

$ systemctl status mysqld

サーバ停止

$ systemctl stop mysqld

サーバ接続開始

$ mysql -h[ホスト名orIP] -u[ユーザー名] -p[パスワード]

特定ユーザで接続し外部SQLファイルを投入する場合

$ mysql -h[ホスト名orIP] -u[ユーザー名] -p[パスワード] -e “[実行コマンド]”

コマンド直接実行

$ mysql -h[ホスト名orIP] -u[ユーザー名] -p[パスワード] DB名 < input.sql

特定ユーザで接続しやりとりを外部SQLファイルに記録

$ mysql -h[ホスト名orIP] -u[ユーザー名] -p[パスワード] DB名 > output.sql

サーバ接続終了

$ mysql> exit

パスワード設定

mysql8の初期パスワードは初期起動のログ(/var/log/mysqld.log)に出力される

$ DB_PASSWORD=$(grep "A temporary password is generated" /var/log/mysqld.log | sed -s 's/.*root@localhost: //')
$ echo $DB_PASSWORD
$ mysql> set password for 'ユーザ名’@’接続元ホスト’=’パスワード’;

初期設定について

設定ファイルは/etc/my.cnfにある

default-authentication-pluginについて MySQL8.0.4以降 のログイン認証方式は caching_sha2_password がデフォルトになっている PHPのMySQL接続ライブラリが未対応のため接続不可になる 解決策としては認証方式を mysql_native_password にする

default-authentication-plugin=mysql_native_password

スロークエリログ X秒以上かかるSQLをログに出力する。 専用のテーブルに保存するのとログに出力するのがある

# スロークエリログのフラグをON
slow_query_log=1
 
# 出力先ファイル名
slow_query_log_file=/var/log/mysqld/slow_query.log

# 指定した秒数以上なら出力(0秒なら、全てログ出力)
long_query_time=3

sql_modeについて

MySQL Server は異なる SQL モードで動作でき、sql_mode システム変数の値に応じて異なるクライアントにこれらの異なるモードを適用できます。 MySQL 5.6.6 以降でのデフォルトの SQL モードは NO_ENGINE_SUBSTITUTION で、MySQL 5.6.5 以前では、これは空白です (モードの設定なし)

よく使う設定

  • ONLY_FULL_GROUP_BY：
    GROUP BY句で名前が指定されていない非集約カラムをSELECT，Having条件，Order条件で指定しているクエリを拒否します。なのでONLY_FULL_GROUP_BYはオフにします
  • STRICT_TRANS_TABLES：
    　オフの場合
    　　timstampカラムで、範囲外の時間を指定するとエラー
    　NOT NULL補完されず。エラーになる
    　varcharの文字列溢れた場合、エラーになる
    　オンの場合
    　　timstampカラムで、範囲外の時間を指定すると調整される
    　idに挿入される
    　'text'のデータが切り捨てられました
  • ERROR_FOR_DIVISION_BY_ZERO：
    　0除算(MOD(N, 0)や1/0等)を含むINSERTやUPDATEがあった場合の動作の設定になります。
    　オフの場合はNULLを挿入します。
    　オンの場合は警告を出力し，NULLを挿入します。
    　厳密モードを指定する設定が有効な場合はエラーとなり挿入がされません。
  • NO_ENGINE_SUBSTITUTION：
    選択したストレージエンジンがCREATE TABLEやALTER TABLEが無効だった場合や利用できないストレージエンジンだった場合に，エラーを返す設定です。
    こちらの設定を無効にすると，利用できないストレージエンジンだった場合はデフォルトに設定されているストレージエンジンが選択されます。

設定情報確認

MySQLバージョン確認

$ mysql -V

バックアップ

全DBのバックアップ(データ + 定義情報)

$ mysqldump -u’ユーザー名’ -p’パスワード’ –all-databases > backup.sql

定義情報のみ

$ mysqldump -u’ユーザー名’ -p’パスワード’ -d –all-databases > backup.sql

特定DBのバックアップ(データ + 定義情報)

$ mysqldump -u’ユーザー名’ -p’パスワード’ DB名 > backup.sql

定義情報のみ

$ mysqldump -u’ユーザー名’ -p’パスワード’ -d DB名 > backup.sql

実行時にエラーが出た場合 以下エラーが出た場合はmy.cnfを修正 「mysqldump: unknown variable 'symbolic-links=0’」 変更箇所 MySQLの設定ファイル「my.cnf」にて「symbolic-links」をコメントアウト

スロークエリをoff

バックアップ時によくでるのでストップする

$ mysql -u’ユーザー名’ -p’パスワード’ -e "set global slow_query_log = OFF;"

スロークエリをon

バックアップが終了したら開始する

$ mysql -u’ユーザー名’ -p’パスワード’ -e "set global slow_query_log = ON;"

スロークエリーのローテション

#!/bin/sh
DAY=`env TZ=JST+15 date "+%Y%m%d"`
echo "start $DAY">>/var/tmp/slow_query_db.log

# スロークエリをoff
mysql -u’ユーザー名’ -p’パスワード’ -e "set global slow_query_log = OFF;"

mv -f /var/log/mysqld/slow_query.log /var/log/mysqld/slow_query$DAY.log
touch /var/log/mysqld/slow_query.log
chmod 666 /var/log/mysqld/slow_query.log

# スロークエリをon
mysql -u’ユーザー名’ -p’パスワード’ -e "set global slow_query_log = ON;"

echo "end $DAY">>/var/tmp/slow_query_db.log

バイナリーログのローテション

ログをフラッシュしてローテションする

mysqladmin  -u’ユーザー名’ -p’パスワード’ flush-logs

デフォルトではbinlog_expire_logs_seconds=2592000（30日）になっている

リストア

全DBのリストア

$ mysql -u’ユーザー名’ -p’パスワード’ < backup.sql

特定DBのリストア

$ mysql -u’ユーザー名’ -p’パスワード’ DB名 < backup.sql

グローバルレベルの権限情報一覧

$ mysql> select * from information_schema.user_privileges;

DBスキーマレベルの権限情報一覧

$ mysql> select * from information_schema.schema_privileges;

テーブルレベルの権限情報一覧

$ mysql> select * from information_schema.table_privileges;

カラムレベルの権限情報一覧

$ mysql> select * from information_schema.column_privileges;

環境変数の確認

MySQLで扱う環境変数は以下2種類がある。 セッション変数：一時設定。現在の接続だけが影響する。

$ mysql> show session variables;

グローバル変数：恒久設定。変数設定後のすべての接続が影響する。

$ mysql> show global variables;

絞り込み表示したい場合は以下

$ mysql> show variables like ’%キーワード%’

各DBの全テーブルのストレージエンジンの一覧取得

$ mysql> SELECT table_name,engine from information_schema.tables where table_schema=’データベース名’;

ユーザー設定

ユーザー作成

$ mysql> create user ユーザー名@接続元ホスト IDENTIFIED BY パスワード;

ちなみに全ホストへのアクセス許可は*ではなく%なので注意。

$ mysql> create user 'ユーザー名’@’%’ IDENTIFIED BY 'パスワード’;

登録済ユーザー一覧

$ mysql> select host,user from mysql.user;

ユーザー削除

$ mysql> drop user 'ユーザー名’@’接続元ホスト’;

ユーザー毎の権限情報確認

$ mysql> show grants for 'ユーザー名’@’接続元ホスト’;

ユーザーへの権限追加

$ GRANT 権限 ON DB名.テーブル名 TO ユーザー名@ホスト名 **

アクセス許可

$ mysql> grant all privileges on . to 'ユーザー名’@’ホスト名’ identified by '’ with grant option;

管理者ユーザを作る

$ GRANT ALL ON . TO 管理ユーザ名@’%’ IDENTIFIED BY 'パスワード’ WITH GRANT OPTION;

ユーザーへの権限削除

要注意 ユーザーの追加、削除、権限操作の後は必ずFLUSH PRIVILEGESで反映が必要。

$ REVOKE 権限 ON DB名.テーブル名 FROM ユーザー名@ホスト名; $ REVOKE ALL ON testdb.* FROM 'testuser’@’testhost’;

ユーザー毎の権限情報確認

$ mysql> grant all privileges on [DB2名].[テーブル名] to 'ユーザー名’@’接続元ホスト’ WITH GRANT OPTION;

DB操作

作成済DBの一覧表示

$ mysql> show databases;

作成済DBの作成用SQL出力(文字コードを確認)

$ mysql> show create database DB名;

DB作成

$ mysql> create database DB名;

DB削除

$ mysql> drop database DB名;

使用するDBの指定

$ mysql> use DB名;

指定したDBへの特定ユーザーの権限付与

$ mysql> grant all on DB名.テーブル名 to 'ユーザー名’;

テーブル関連

作成済テーブルの一覧表示

$ mysql> show tables;

作成済テーブルの作成用SQL出力(文字コードを確認)

$ mysql> show create table テーブル名;

テーブル定義の確認

$ mysql> desc テーブル名;

テーブル作成

$ mysql> create table テーブル名 (カラム内容 );

テーブル削除

$ mysql> drop table テーブル名;

テーブルの中身を全て表示

$ mysql> select * from テーブル名;

テーブルの中身の追加

$ mysql> insert into テーブル名 フィールド名 values フィールドの値;

テーブルの中身の削除

$ mysql> delete from テーブル名 where 条件文;

テーブルの中身の全消去

$ mysql> truncate テーブル名; 例) $ mysql> truncate table1;

テーブルの中身の更新

$ mysql> update テーブル名 set フィールド名 where 条件;

テーブルのロック/アンロック

$ mysql> lock tables テーブル名 ロック種類(write/read)

テーブルの最適化

$ mysql> analize table テーブル名;

トラブル時

アクティブプロセス一覧

重いSQL（終わらないレベル）を発行してしまった時にここにたまる

$ mysql> show full processlist;

現在のINNODBの状態確認

$ mysql> show engine innodb status;

統計情報の確認

$ mysql> show global status; 例)過去の最大コネクション数 
$ mysql> show status like '%Max_used%’; 
$ mysql> show status like '%Threads_connected%’;

MySQLプラグイン一覧確認

$ mysql> show plugins;

エラー一覧

$ mysql> show warnings;

レプリケーション

マスター稼働状況

$ mysql> show master status \G

スレーブ稼働状況

$ mysql> show slave status \G

参考

https://oshou.github.io/mysql-command-cheatsheet/ https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html https://qiita.com/ucan-lab/items/3ae911b7e13287a5b917 https://hit.hateblo.jp/entry/MYSQL/8.0/MY.CNF https://yoku0825.blogspot.com/2018/04/mysql-803-expirelogsdays.html https://qiita.com/peutes/items/23eaa7976270f1d0d672 http://gihyo.jp/dev/serial/01/mysql-road-construction-news/0018