MySQL

FrontPage

設定 †

mysqlコマンドのプロンプト変更 †

環境変数MYSQL_PS1を変更する。

• \u username
• \h hostname
• \d database name

例。

$ export MYSQL_PS1=(\u@\h) [\d]> 

デフォルトcharset定義 †

my.confの[mysqld]エントリにdefault-character-setを書く。指定したcharsetがデフォルトの値となる。

[mysqld]
default-character-set=utf8

mysqlコマンドでの設定は[client]セクションに書いておく。

[client]
default-character-set=utf8

権限 †

プロセスリストを見たい †

PROCESS権限があればよい。

テーブル単位では操作できないのでグローバル権限として指定する。

mysql> GRANT PROCESS ON *.* TO 'user'@'host' IDENTIFIED BY 'password';

RESET MASTERしたい †

RELOAD権限があればよい。

テーブル単位では操作できないのでグローバル権限として指定する。

mysql> GRANT RELOAD ON *.* TO 'user'@'host' IDENTIFIED BY 'password';

レプリケーション †

要点は以下。

• server-idを設定する
• server-idはレプリケーションを構成するノード毎に別の値を設定する
• レプリケーション用ユーザを作成する
• レプリケーション用ユーザにはREPLICATION SLAVE権限をあたえる

node-master:/etc/my.cnf

[mysqld]
server-id=100

node-slave1:/etc/my.cnf

[mysqld]
server-id=200

node-slave2:/etc/my.cnf

[mysqld]
server-id=201

操作 †

レプリケーション †

MASTERの切り替え †

1. STOP SLAVE IO_THREAD
2. SHOW PROCESSLIST
3. STOP SLAVE
4. CHANGE MASTER TO〜
5. START SLAVE

レプリケーション状態の確認(master) †

mysqlプロンプトからSHOW MASTER STATUS;を実行する。

$ mysql exampledb
mysql> show master status\G
*************************** 1. row ***************************
            File: example-host-bin.000007
        Position: 98
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

レプリケーション状態の確認(slave) †

mysqlプロンプトからSHOW SLAVE STATUS;を実行する。

$ mysql exampledb
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.222.130
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: example-host-bin.000007
        Read_Master_Log_Pos: 98
             Relay_Log_File: example-slave-relay-bin.000002
              Relay_Log_Pos: 242
      Relay_Master_Log_File: example-host-bin.000007
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: exampledb
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 242
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

バックアップ †

blob型カラムのバックアップ †

mysqldumpで--hex-blobオプション, --default-character-setを使う。

$ mysqldump --hex-blob --default-character-set=utf8 blobdatabase > backup.sql

リストアするときはそのままmysqlコマンドに食わせればOK。

$ mysql blobdatabase < backup.sql

ダンプ時のINSERTを1行ずつにする †

• skip-extended-insertオプションを利用する。

$ mysqldump --skip-extended-insert dbname

CSV/TSVでダンプする †

• tabオプションを使う。

• tabオプションの引数は出力先ディレクトリ名、--fields-terminated-byオプションの引数はデリミタを指定する。

$ mysqldump --fields-terminated-by=, --tab=/tmp database

任意のクエリの出力結果を得る †

• Bでバッチモード。CSVのような出力になる。
• Nでカラム名を表示しないようになる。

• tabオプション使ってダンプするより使い勝手が良い。

  $ mysql -Ddbname -B -N -e "statement"

情報参照 †

テーブルのインデックスを見る †

mysql> SHOW INDEX FROM tbl_name;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_name |          0 | PRIMARY  |            1 | column_name | A         |      103527 |     NULL | NULL   |      | BTREE      |         | 
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 rows in set (0.00 sec)

Key_name PRIMARYはプライマリキーのみが指定されているということ。 他の行が無いので、プライマリキー以外のインデックスは指定されていない。

クエリプランを見る †

mysql> explain query;
+----+-------------+----------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | tbl_name | index | NULL          | PRIMARY | 27      | NULL | 99213 | Using where | 
+----+-------------+----------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)

possible_keysがNULLの場合には利用可能なインデックスが無いということ。 インデックスを張れば性能向上の可能性がある。

テーブルの情報を見る †

mysql> SHOW TABLE STATUS LIKE 'tbl_name';

LIKE部分はSQLのLIKEと同様の構文で、%を使うとワイルドカード指定ができる。

テーブルのCREATE文を見る †

mysql> SHOW CREATE TABLE tbl_name;

AUTO_INCREMENTの現在の値を見る †

mysql> SHOW TABLE STATUS WHERE name='table_name';

AUTO_INCREMENTの値をつけかえる †

mysql> ALTER TABLE table_name AUTO_INCREMENT=1;

チューニング †

SQLキャッシュを強制的に無効 †

パフォーマンス計測時などで、キャッシュなしのSQLリクエストを発行したいときがある。

SQL_NO_CACHEをはさむことでSQLキャッシュを強制的に無効にできる。

mysql> SELECT SQL_NO_CACHE column_a, column_b FROM table;