HOME » 備忘録 » 2017 » MySQLのあれこれ

MySQLのあれこれ

そんな頻繁に起きない程度の作業のメモ

作成日時:2017-10-17 / 最終更新:2018-02-16

稼働中のmysqldのシステム変数を確認する

$ mysqladmin -u root -p variables
接続時は下記
sql > SHOW VARIABLES;

発行されたSQL文を確認する

デバッグや開発などでSQL文を確認したい時に。
出力される場所についてはシステム変数の「general_log_file」の項目の値になります。
デフォルトでは「/var/run/mysqld/mysqld.log」
また使用後はOFFにしないとログがストレージを圧迫するので、本番環境では特に後始末を忘れず行う。

sql> SET GLOBAL general_log=ON;

テーブル構造の確認

DBをUSEしてから。DESCで省略できる模様。

mysql> DESCRIBE modx_hoge_history;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(10) | NO   | PRI | NULL    | auto_increment |
| updatedon   | int(10) | NO   |     | 0       |                |
~省略

# コマンド情報
sql> help describe
Name: 'DESCRIBE'
Description:
Syntax:
{DESCRIBE | DESC} tbl_name [col_name | wild]

DESCRIBE provides information about the columns in a table. It is a
shortcut for SHOW COLUMNS FROM. (See [HELP SHOW COLUMNS].)

テーブルの文字コード確認

化けてでるぞ!

sql> show table status from データベース名 like 'テーブル名';
+-------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name              | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+-------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| テーブル名 | MyISAM |      10 | Fixed      |    1 |              9 |           9 | 2533274790395903 |         1024 |         0 |           NULL | 2017-11-09 09:24:19 | 2017-11-09 09:54:17 | 2017-11-09 09:54:17 | utf8_general_ci |     NULL |                |         |
+-------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

IPごとにカウント

DBに流し込んだアクセスログなどに使用。

sql> SELECT remote_host, COUNT(*) AS Count
FROM `table_name`
GROUP BY remote_host
HAVING (COUNT(*) > 1)
ORDER BY remote_host;

権限の確認

現在ログイン中のユーザの場合はFOR移行なしで確認可能

sql> SHOW GRANTS FOR 'ユーザ名'@'localhost';

rootパスワードの再設定

コンソール上でのroot権限が必要になります。
デーモンを停止してからセーフモードで起動を下記のコマンドで行う
# mysqld_safe --skip-grant-tables &

パスワードの確認が行われないのでそのままrootで接続後パスワードを再設定する
sql> use mysql
sql> update user set password=PASSWORD("再設定したいパスワード") where User='root';
sql> flush privileges;

設定完了後デーモンの再起動してセーフモードを終わらせる

別サーバーからMySQLへ接続できるように設定

通常の権限付与とほぼ同様、ホスト部分を接続元のIPを指定するだけで接続可能。
ワイルドカードはLIKE句同様「%」を使用する、加えてIPTABLESやFIREWALLDなどの設定を行う。

sql> GRANT ALL PRIVILEGES ON *.* TO ユーザ名@'192.168.%' IDENTIFIED BY 'パスワード' WITH GRANT OPTION;

mysqlコマンドで別サーバーのMySQLへログイン

-hのオプションの後にIPを指定するだけ、ポートを変更している場合は加えて「-P 4649」で指定

$ mysql -h 192.168.1.2 -u root -p

WITH GRANT OPTIONについて

例えば、rootがユーザ(A)に対して権限を付与する時に、このオプションを指定しておくと、ユーザ(A)が他のユーザ(B)に権限を与えることについて制御することが可能。
「WITH GRANT OPTION」のオプションをつけると、ユーザ(A)がユーザ(B)に権限を与えることができるようになる。
複数のroot権限と同等のユーザを作成する際や、統合管理するようなシステムでデータベース単位での拡張性をプログラムで実装する際は便利そうなオプション

sql> GRANT ALL ON `database`.* TO 'user'@'%' WITH GRANT OPTION