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

操作

定義変更

ユニークインデックスを落とす

外部キー付きのカラムにユニークインデックスが張ってあると、 そのままではインデックスを落とせない(落とそうとするとERROR 1025 (HY000): Error on rename of〜といったエラーになる)。例えば次のようなテーブルの場合。

CREATE TABLE `photos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `filepath` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  CONSTRAINT `user_id_refs_id_4b9db746aab76395` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
)

次のような手順で処理する必要がある。

  1. 外部キー制約を落とす
  2. ユニークインデックスを落とす
  3. 外部キーを貼り直す
ALTER TABLE `photos` DROP FOREIGN KEY `user_id_refs_id_4b9db746aab76395`;
ALTER TABLE `photos` DROP INDEX `user_id`;
ALTER TABLE `photos` ADD CONSTRAINT `my_new_FK` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

PRIMARY KEYを後付け

mysql> ALTER TABLE ADD PRIMERY KEY (column_name);

AUTO_INCREMENTを後付け

対象カラムにインデックスがないとだめ。

mysql> ALTER TABLE table_name MODIFY column_name INT AUTO_INCREMEMT;

AUTO_INCREMENTの値を設定

mysql> ALTER TABLE table_name AUTO_INCREMENT=xx;

レプリケーション

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';

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

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できる。


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2012-12-21 (金) 16:00:02 (1648d)