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"

情報参照 †

mysqldの設定値を見る †

コンフィグの確認。show variablesステートメントを使う。

mysql> show variables;
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| auto_increment_increment        | 1                           | 
| auto_increment_offset           | 1                           | 
| automatic_sp_privileges         | ON                          | 
| back_log                        | 50                          | 
| basedir                         | /usr/                       | 
| binlog_cache_size               | 32768                       | 
| bulk_insert_buffer_size         | 8388608                     | 
| character_set_client            | utf8                        | 
| character_set_connection        | utf8                        | 
| character_set_database          | utf8                        | 
| character_set_filesystem        | binary                      | 
| character_set_results           | utf8                        | 
| character_set_server            | utf8                        | 
| character_set_system            | utf8                        | 
| character_sets_dir              | /usr/share/mysql/charsets/  | 
| collation_connection            | utf8_general_ci             | 
| collation_database              | utf8_general_ci             | 
| collation_server                | utf8_general_ci             | 
(snip)

mysqldの動作統計を見る †

show statusステートメントを使う。

mysql> show status;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 86         | 
| Aborted_connects                  | 2          | 
| Binlog_cache_disk_use             | 0          | 
| Binlog_cache_use                  | 0          | 
| Bytes_received                    | 788        | 
| Bytes_sent                        | 68986      | 
(snip)

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

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;

テーブルの物理サイズを確認する †

SELECT
    table_name,
    engine,
    table_rows AS tbl_rows,
    avg_row_length AS rlen,
    floor((data_length+index_length)/1024/1024) AS allMB,
    floor((data_length)/1024/1024) AS dMB,
    floor((index_length)/1024/1024) AS iMB
  FROM information_schema.tables
 WHERE table_schema=database()
 ORDER BY (data_length+index_length) DESC;

関数 †

日付の操作 †

日付を丸める †

SELECT DATE_FORMAT(now(), '%Y-%m-%d');

チューニング †

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

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

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

mysql> SELECT SQL_NO_CACHE column_a, column_b FROM table;

大きなクエリを投げたい †

Bulk insertなどで大きなクエリを投げた場合、Packet too largeエラーを食らうことが有る。これはmysqld側でパケットサイズを制限していることが原因で、パラメタ変更で対応することが出来る。

[mysqld]
max_allowed_packet=128MB

設定可能な値には制限があります。

* MySQL 3.23 で使用できる最も大きなパケットは 16M です
* MySQL 4.01 以上では、パケットの大きさは、サーバ上のメモリ容量でのみ制限されます（理論上は最大 2G）

障害リカバリ †

レプリケーションの復旧 †

レプリケーションの動作状態はshow slave statusの値を確認する。

以下はSlave_SQL_Runningとなっていて、スレーブデータベースへの反映動作が停止している。 また、動作停止の原因は、カラムが重複していることが解る（ALTERをマスタ、スレーブノードの両方で実行しちゃったの…）。

mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: db1
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: db1-bin.000111
        Read_Master_Log_Pos: 507718673
             Relay_Log_File: db3-relay-bin.000332
              Relay_Log_Pos: 503708915
      Relay_Master_Log_File: db1-bin.000111
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1060
                 Last_Error: Error 'Duplicate column name 'category_sub_id'' on query. Default database: 'serv'. Query: 'alter table community add category_sub_id smallint(6) default null after category_id'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 503708780
            Relay_Log_Space: 507718808
            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: NULL
1 row in set (0.00 sec)

1件スキップするようにSQL_SLAVE_SKIP_COUNTERを設定し、スレーブスレッドを再開すればよい。

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
mysql> START SLAVE SQL_THREAD; 

事故防止 †

基本中の基本ですが、まとめの意味で記述。

バックアップ †

更新対象が広範囲、カラムの変更などが行われる場合、バックアップをあらかじめ行うこと。

$ mysqldump hogepro_key > db.out

特定のテーブルのみに絞るには、テーブルを検索条件に加える。

$ mysqldump hogepro_key user > db_user_tbl.out

INSERT / DELETE / UPDATE時はトランザクションをかける †

更新クエリを投げるときにはBEGIN 〜 COMMITしようね。

(root@localhost) [hogepro_key]> BEGIN;
（いろいろ更新する）
(root@localhost) [hogepro_key]> COMMIT;

もし更新範囲が誤っていたらROLLBACKする。

(root@localhost) [hogepro_key]> ROLLBACK;

更新クエリの影響範囲を確認 †

例えばupdateクエリを投げると次のメッセージが出る。

(root@localhost) [hogepro_key]> update user set permissions=1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

" 1 row affected ", " Rows matched: 1 ", " Changed: 1 "という部分が影響した行数。この場合、primary keyを元に更新しているので、1行しか変更がないはず。もし1よりも大きな値が出力された場合、条件式が誤っていることになる。トランザクションを開始していればROLLBACKできる。