Memo/MySQL

http://dexlab.net/pukiwiki/index.php?Memo/MySQL
 

MySQL

  • 検索処理や更新処理を高速にできること
  • 高速でない機能は実装されていない(*は部分的に使用可能)
    • カーソル
    • 副問い合わせ
    • トランザクション機能(*)
    • 結合機能(*)
    • トリガ、ストアドプロシージャ
    • 複製機能(*)

SQLの正規化

SQLから値や、改行等を除いて正規化する。
SQLをtd-agentで集めたり、ログとして残したいが、値はマスクしたい等にも使える。

  • pt-fingerprint
  • インストール
    1. sudo yum install https://www.percona.com/downloads/percona-toolkit/2.2.20/RPM/percona-toolkit-2.2.20-1.noarch.rpm
  • テスト
    1. echo "SELECT name, password FROM user WHERE id='abc12823' and password='k#eyAW37';" | pt-fingerprint
    2. select name, password from user where id=? and password=?

mysqldiff


OPTIMIZE TABLE

innodb_file_per_table=1の時、DELETEでデータ削除後に「OPTIMIZE TABLE」実行でOS上のディスクサイズを小さくできる。
5.6.17 より前はALTER TABLEと同様にテーブルロックがかかる。
5.6.17以後はオンライン DDLが有効で処理前後に短いロックがあるだけと書いてあるが、細かい制限も多いので注意。


PostgreSQLとのコマンド比較


SQLにsleepをいれる

slow queryのテスト等で使える

  1. mysql> SELECT sleep(5);
  2. +----------+
  3. | sleep(5) |
  4. +----------+
  5. |        0 |
  6. +----------+
  7. 1 row IN SET (5.01 sec)

テーブルのengineの一覧を得る

  • MyISAM engineを使っているテーブルの一覧を取得
    1. mysql -u readonly -p -h localhost information_schema
    2. mysql> select table_schema, table_name, engine from tables where engine = 'MyISAM';
    3. +--------------------+---------------------------+--------+
    4. | table_schema       | table_name                | engine |
    5. +--------------------+---------------------------+--------+
    6. | information_schema | COLUMNS                   | MyISAM |
    7. | information_schema | EVENTS                    | MyISAM |
    8. ...

remiリポジトリからmysqlをインストールする場合

  • エラー: libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by postfix-2:2.6.6-6.el6_7.1.x86_64
  • postfixが依存しているので同時に更新する
    1. sudo yum update mysql-libs postfix
  • mysql5.1の互換性モジュールもある
    1. sudo yum install compat-mysql51

mysqldumpslowでスロークエリを解析する

スロークエリのログは数が多すぎて見難い。平均クエリー時間でソートしたり、件数をまとめてくれるコマンドがある。
分析後のファイルはvalueの値も隠してくれる。

  • "-s t"オプションでクエリー時間でソート
    1. mysqldumpslow -s t mysql-slow.log > mysql-slow.analytics.log
  • zip圧縮しているファイルを展開せずに分析する場合
    1. zcat mysql-slow.zip | mysqldumpslow -s t - > mysql-slow.analytics.log

my.cnfメモ


MySQL 5.7

  • 記事
    • MySQL 5.7.4で導入されたdefault_password_lifetimeがじわじわくる(MySQL 5.7.11でFIX!!) MySQL 5.7.11でデフォルトが「default_password_lifetime=0」になり、ユーザのパスワードが360日で切れる罠から解放された
    • MySQL 5.7の罠があなたを狙っている
      • 5.6の暗黙のデフォルトとなるべく同じにするためのタレ
        1. [mysqld]
        2. sql_mode=NO_ENGINE_SUBSTITUTION
        3. secure_file_priv=""
        4. binlog_format=STATEMENT
        5. innodb_buffer_pool_dump_at_shutdown=0
        6. innodb_buffer_pool_load_at_startup=0
        7. innodb_buffer_pool_dump_pct=100
        8. loose-default_password_lifetime=0
        9. ### Remove log_warnings!!
        10. loose-log_error_verbosity=3
        11. loose-log_timestamps=SYSTEM
        12. loose-internal_tmp_disk_storage_engine=MyISAM
        13. loose-show_compatibility_56=ON

ユーザ認証をしない

  • CentOS6.xの mysql-server-5.5.44-1.el6.remi.x86_64 では startsos で起動する
    1. service mysqld stop
    2. service mysqld startsos
    • /etc/rc.d/init.d/mysqld を見ると mysqld_safe 起動時に以下のオプションが追加される
      1. --skip-grant-tables --skip-networking

クエリ結果を見やすく表示

  • \G : クエリ(レコード)を縦方向に出力する
    1. mysql> select * from user limit 1\G
    2. *************************** 1. row ***************************
    3.          id: 1
    4.        name: xMlc9XNzqu
    5.         age: 249
    6. description: MGSaPuSA3dtPUS7o77FaO1XN9UPNwxBEQAEKpeEJfVOspqtKgU
    7.  created_at: 2013-07-05 13:14:12
    8. 1 row in set (0.00 sec)
  • less -i -S : 画面からはみ出す行を折り返さず、横スクロール可能にする
    1. mysql> pager less -i -S
    2. +----+------------+------+----------------------------------------------------+---------------------+
    3. | id | name       | age  | description                                        | created_at          |
    4. +----+------------+------+----------------------------------------------------+---------------------+
    5. |  1 | xMlc9XNzqu |  249 | MGSaPuSA3dtPUS7o77FaO1XN9UPNwxBEQAEKpeEJfVOspqtKgU | 2013-07-05 13:14:12 |
    6. +----+------------+------+----------------------------------------------------+---------------------+
    7.  
    8. # 元に戻す
    9. mysql> nopager
    10.  
    11. # コマンドラインから指定する場合
    12. mysql test --pager='less -i -S'
  • tee ファイル名 : 実行結果をファイルに出力
    1. mysql> tee output.log
    2.  
    3. # 停止
    4. mysql> notee
  • system コマンド : システムコマンドを実行
    1. mysql> system date '+%Y-%m-%d'
    2. 2013-08-01

HAテスト用にinsertするテストスクリプト

  • filemysql-insert-test.zip 1秒1回のペースでinsertするスクリプト。1時間、insertをテストする例。ダウンタイムを計測する
    1. nohup ./mysql-insert-test.sh mysql-host user password 3600 1>result.csv 2>result.error.log &
    2.  
    3. tail -f result.csv
    4. datetime,function,is_up,is_down,down time(sec)
    5. 2016-11-24T16:42:45+0900,tearDown
    6. 2016-11-24T16:42:45+0900,setUp
    7. 2016-11-24T16:46:33+0900,write_loop,0,1,1
    8. 2016-11-24T16:46:34+0900,write_loop,0,1,2
    9. ...

innodb_file_per_table=1の時にdelete > alter tableしたらファイルサイズは小さくなるか?

  • 結論
    • innodb_file_per_table=1の時にdelete > alter tableでDBName/TableName?.ibdのファイルサイズは小さくなる。Data_freeはinsert時に増え、delete > alter table時に減る。
    • innodb_file_per_table=0の時にdelete > alter tableでibdata1のファイルサイズは変わらない。Data_freeはdeleteした時に増える
    • alter table中はRead only lockになる。
  • mysql-5.5.30-1.el6.remi.x86_64
  • /etc/my.cnf
    1. [mysqld]
    2. ...
    3. character-set-server=utf8
    4. default-storage-engine=InnoDB
    5. innodb_log_file_size=128M
    6. innodb_data_file_path=ibdata1:1G
    7. innodb_file_per_table=1
    8. innodb_file_format=Barracuda
    9. innodb_stats_on_metadata=0
  • innodb_file_per_table=1の時
    • create table直後
      1. mysql -u root -p test
      2. CREATE TABLE user (
      3.  id int(11) NOT NULL AUTO_INCREMENT,
      4.  name varchar(50) DEFAULT NULL,
      5.  age int(11) DEFAULT NULL,
      6.  description text,
      7.  created_at datetime DEFAULT NULL,
      8.  PRIMARY KEY (`id`)
      9. ) ENGINE=InnoDB;
      10.  
      11. ll /var/lib/mysql/test/
      12. ...
      13. -rw-rw---- 1 mysql mysql  8700  7月  5 12:40 2013 user.frm
      14. -rw-rw---- 1 mysql mysql 98304  7月  5 12:40 2013 user.ibd
      15.  
      16. mysql -u root -p test -e "show table status where name = 'user'\G" | grep Data_free                                                                          
      17.       Data_free: 0
    • 18MB程のSQLをinsert後。Data_freeが増える
      1. mysql -u root -p -D test -e "load data local infile 'dummy.csv' into table user fields terminated by ',';"
      2. ll /var/lib/mysql/test/
      3. ...
      4. -rw-rw---- 1 mysql mysql     8700  7月  5 12:40 2013 user.frm
      5. -rw-rw---- 1 mysql mysql 29360128  7月  5 12:45 2013 user.ibd
      6.  
      7. mysql -u root -p test -e "show table status where name = 'user'\G" | grep Data_free                                                                          
      8.       Data_free: 4194304
    • delete後。ファイルサイズは変わらず。Data_freeも変わらず
      1. mysql -u root -p test -e "delete from user;"
      2. ll /var/lib/mysql/test/
      3. ...
      4. -rw-rw---- 1 mysql mysql     8700  7月  5 12:40 2013 user.frm
      5. -rw-rw---- 1 mysql mysql 29360128  7月  5 12:45 2013 user.ibd
      6.  
      7. mysql -u root -p test -e "show table status where name = 'user'\G" | grep Data_free                                                                          
      8.       Data_free: 25165824
    • alter table。Read OK/Write lock。ファイルサイズは小さくなった。Data_freeは0になった
      1. mysql -u root -p test -e "alter table user engine innodb;"
      2. ll /var/lib/mysql/test/
      3. ...
      4. -rw-rw---- 1 mysql mysql  8700  7月  5 12:53 2013 user.frm
      5. -rw-rw---- 1 mysql mysql 98304  7月  5 12:53 2013 user.ibd
      6.  
      7. mysql -u root -p test -e "show table status where name = 'user'\G" | grep Data_free                                                                          
      8.       Data_free: 0
  • innodb_file_per_table=0の時
    • create table直後。Data_freeは10MB
      1. mysql -u root -p test
      2. CREATE TABLE user (
      3.  id int(11) NOT NULL AUTO_INCREMENT,
      4.  name varchar(50) DEFAULT NULL,
      5.  age int(11) DEFAULT NULL,
      6.  description text,
      7.  created_at datetime DEFAULT NULL,
      8.  PRIMARY KEY (`id`)
      9. ) ENGINE=InnoDB;
      10.  
      11. ll /var/lib/mysql/
      12. ...
      13. -rw-rw---- 1 mysql mysql 18874368  7月  5 13:06 2013 ibdata1
      14.  
      15. mysql -u root -p test -e "show table status where name = 'user'\G" | grep Data_free                                                                          
      16.       Data_free: 10485760
    • 18MB程のSQLをinsert後。ibdata1のサイズは増加。Data_freeは 13MB
      1. mysql -u root -p -D test -e "load data local infile 'dummy.csv' into table user fields terminated by ',';"
      2. ll /var/lib/mysql/
      3. ...
      4. -rw-rw---- 1 mysql mysql 44040192  7月  5 14:23 2013 ibdata1
      5.  
      6. mysql -u root -p test -e "show table status where name = 'user'\G" | grep Data_free                                                                          
      7.       Data_free: 13631488
    • delete後。ファイルサイズは変わらず。Data_freeは26MBに増加
      1. mysql -u root -p test -e "delete from user;"
      2. ll /var/lib/mysql/
      3. ...
      4. -rw-rw---- 1 mysql mysql 44040192  7月  5 14:31 2013 ibdata1
      5.  
      6. mysql -u root -p test -e "show table status where name = 'user'\G" | grep Data_free                                                                          
      7.       Data_free: 27262976
    • alter table。Read OK/Write lock。ファイルサイズは変わらず。Data_freeは26MBのまま。
      1. mysql -u root -p test -e "alter table user engine innodb;"
      2. ll /var/lib/mysql/
      3. ...
      4. -rw-rw---- 1 mysql mysql 44040192  7月  5 14:31 2013 ibdata1
      5.  
      6. mysql -u root -p test -e "show table status where name = 'user'\G" | grep Data_free                                                                          
      7.       Data_free: 27262976

テーブルスペースの空き領域の確認

  • Data_freeが割り当てられたけれど使用されていないバイト数。
    1. root@localhost[test]> show table status \G
    2.  
    3. *************************** 1. row ***************************
    4.            Name: test
    5.          Engine: InnoDB
    6.         Version: 10
    7.      Row_format: Compact
    8.            Rows: 5
    9.  Avg_row_length: 3276
    10.     Data_length: 16384
    11. Max_data_length: 0
    12.    Index_length: 0
    13.       Data_free: 0
    14.  Auto_increment: NULL
    15.     Create_time: 2013-01-01 15:00:00
    16.     Update_time: NULL
    17.      Check_time: NULL
    18.       Collation: utf8_general_ci
    19.        Checksum: NULL
    20.  Create_options:
    21.         Comment:

Specified key was too long; max key length is 767 bytes

  • インデックスに使うkeyは767 byteまでしか指定できない。
    1. CREATE TABLE test (
    2.   id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    3.   value varchar(256) NOT NULL,
    4.   UNIQUE KEY (value)
    5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    6.  
    7. ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
  • utf8(3byte), utf8mb4(4byte)はすぐにこの上限にひっかかる。utf8は255文字、utf8mb4は191文字まで。
  • utf8mb4を使う場合、サイズが大きくなりやすいので、デフォルト文字セットはutf8, 必要なテーブルのみutf8mb4にしたほうが良さそう

mysqlコマンドの結果をテキスト形式で取得

--batch, -B
タブとして、カラムセパレータを使用することで、各行が新しいライン上に配置されるように、結果をプリントします。
--skip-column-names, -N
結果にカラム名を記述しません。
--execute=statement, -e statement
ステートメントを実行し、終了
  1. mysql -u root -p mysql -BNe "select concat(User, '@',Host) as User from mysql.user;"
  2. root@127.0.0.1
  3. root@::1
  4. @localhost
  5. root@localhost

rootパスワードを忘れたとき

  • シングルユーザモードで起動。誰でもログインできるようになるので、mysql再起動して元に戻すのを忘れずに。
    1. service mysqld stop
    2. service mysqld startsos
    3. echo "UPDATE mysql.user SET Password=PASSWORD('new password') WHERE User='root';FLUSH PRIVILEGES;" | mysql -u root -h localhost mysql
    4. service mysqld restart
  • 全ユーザが消えて(消して)しまった場合。userレコードが無くなるため、grantが "with grant tables disabled (was started with --skip-grant-tables)." エラーで失敗する。
    • パスワードなしのrootユーザを作成する場合
      1. service mysqld stop
      2. service mysqld startsos
      3. mysql -u root -h localhost mysql
      4. mysql> INSERT INTO `user` VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','');
      5. mysql> flush privileges;
      6. service mysqld restart

DBの新規作成と既存ユーザにアクセス権限追加

  • 新しく test_db を作成。既存ユーザuser1にアクセス権限を与える
    1. mysql -u root -p -h mysql.example.com mysql
    2. mysql> CREATE DATABASE `test_db` DEFAULT CHARACTER SET utf8;
    3. mysql> GRANT ALL PRIVILEGES ON `test_db`.* TO 'user1'@'%';
    4. mysql> flush privileges;

4バイトUTF8

絵文字等の4バイトUTF8を扱いたい場合、MySQL5.5以上で、"utf8mb4"を指定する。
DBの再構築が必要のため、ダンプ/インポート。

  • collation-server= を指定すると文字化けするケースがあったので削除
  • skip-character-set-client-handshake: utf8とutf8mb4をテーブル毎に併用し、クライアント側で切り替えている場合に問題となる場合がある。
  1. [mysqld]
  2. ...
  3. character-set-server=utf8mb4
  4.  
  5. [mysql]
  6. default-character-set=utf8mb4
  7.  
  8. [client]
  9. default-character-set=utf8mb4
  10.  
  11. [mysqldump]
  12. default-character-set=utf8mb4

全てのホストから接続できるユーザの作成は'%','localhost'が必要

  • 全てのホストにマッチさせるつもりで 'user1'@'%' をユーザを作っても、localhost接続だとエラーになる。しかし、他のホストからだと接続できる
    1. mysql -uroot mysql
    2. mysql> grant select on db1.* to 'user1'@'%' identified by 'password';
    3. mysql> flush privileges;
    4.  
    5. mysql -u user1 -p db1
    6. ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
  • localhost用のユーザを作成すると接続できる。
    1. mysql -uroot mysql
    2. mysql> grant select on db1.* to 'user1'@'localhost' identified by 'password';
    3. mysql> flush privileges;

Percona Server

  • http://www.percona.com/
    • 公式ビルドに含まれていない便利なパッチが含まれているMySQLサーバ
    • オープンソース。サポートは有償
    • ライセンス GPLv2
    • ストレージエンジン XtraDB がある。InnoDBベースで最新のマルチプロセッサでパフォーマンスが出る
    • yumでCentOS等にインストール可能
    • XtraBackup?でリストアが早い

死活確認

  • mysqlを使う。正常ならreturn status=0, 異常なら1
    1. echo "" | mysql -A -u username -p -h mysql.example.com dbname
    2. Enter password:
    3.  
    4. echo $?

mysqlコマンドから巨大なテーブルの取得に失敗する場合「--quick」オプションを使用


rootユーザなのにgrant権限が無い場合

  • rootユーザなのにGrant_priv=Nの場合がある
  • 対処
    1. update mysql.user set Grant_priv='Y' where user='root'; FLUSH PRIVILEGES;" | mysql -uroot -p mysql

MySQL-MHA


チューニング


インポート/エクスポート

  • 「LOAD DATA INFILE ...」で"Access denied for user..." が出る場合「LOAD DATA LOCAL INFILE ...」を使うと良い
    1. # 1行目にヘッダ行、カラムは「"」で括ってあるcsvをインポートする
    2. mysql -u$db_user -p$db_password -h $db_host $db_database \
    3.  -e "LOAD DATA LOCAL INFILE 'hoge.csv' INTO TABLE hoge CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES;"

トラブルシューティング

  • テーブルの検証(Check): -c
    1. # test DB, table01だけをチェック
    2. mysqlcheck -c test table01 -u root -p
    3.  
    4. # test DB丸ごとチェック
    5. mysqlcheck -c test -u root -p
    6.  
    7. # 全てのDBをチェック
    8. mysqlcheck -c -u root -p --all-databases
  • テーブルの分析(Analyze): -a
  • テーブルの最適化(Optimize): -o
  • テーブルの修復(Repair): -r
  • エラーチェック、最適化、修復: -c -o --auto-repair

[Note] Slave: received end packet from server, apparent master shutdown:

  • /var/log/mysqld.logに以下のログが延々と出る場合がある。最悪ディスクを食いつぶし、mysqlが停止する
    1. tail -f /var/log/mysqld.log
    2. ----
    3. 111215 21:57:03 [Note] Slave: received end packet from server, apparent master shutdown:
    4. 111215 21:57:03 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysqld-bin.000277' at position 106
  • 原因
    • /etc/my.cnfのserver-idが重複している。slave同士で異なるIDを付与すれば良い
      1. vi /etc/my.cnf
      2. ----
      3. server-id=3
      4. ----
      5. service mysqld restart

テスト用ダミーデータ生成

  • 例:ユーザテーブル
    1. CREATE TABLE user (
    2.  id int(11) NOT NULL AUTO_INCREMENT,
    3.  name varchar(50) DEFAULT NULL,
    4.  age int(11) DEFAULT NULL,
    5.  description text,
    6.  created_at datetime DEFAULT NULL,
    7.  PRIMARY KEY (`id`)
    8. ) ENGINE=InnoDB;
    • filemysql_insert_dummy.sh
    • 実行例:100件ダミーデータを作成する場合。aws m1.smallで3.4s程
      1. time bash ./create_dummy.sh 100 > dummy.csv
      2. mysql -u root -p -D test -e "load data local infile 'dummy.csv' into table user fields terminated by ',';"

5.1から5.5へアップグレードする場合の変更点

  • /etc/my.cnf
    • ソート順の指定。ci:"A","a","A","a"を同じ文字として扱う。 / cs:"A","a","A","a"を違う文字として扱う
      1. +collation-server=utf8_general_ci
    • InnoDBに圧縮形式のサポート
      1. +innodb_file_format=Barracuda
      2. +innodb_file_per_table = 1
      • 圧縮機能を使用するにはDDLでも指定する必要がある。使用量は約半分に押さえられるが若干CPUを使う
        1. CREATE TABLE user (
        2. ...
        3. ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
    • default-table-type が default-storage-engine に変更
      1. -default-table-type=InnoDB
      2. +default-storage-engine=InnoDB
    • 文字コード指定が分かれた
      1. #クライアント側
      2. +default-character-set=utf8
      3.  
      4. #サーバ側
      5. +character-set-server=utf8
    • master-* 廃止。「CHANGE MASTER TO」を使用
      1. -master-host=master.example.com
      2. -master-user=repl
      3. -master-password=
      4. -master-port=3306
      1. mysql
      2.  
      3. mysql> CHANGE MASTER TO
      4.   MASTER_HOST='master.example.com',
      5.   MASTER_USER='repl',
      6.   MASTER_PASSWORD='',
      7.   MASTER_PORT=3306;
      8.  
      9. mysql> slave start;

[ERROR] Error message file '/usr/share/mysql/english/errmsg.sys'

  • atomicリポジトリの 「mysql-server-5.5.17-1.el5.art」 では発生しなくなった
  • mysql 5.1 から 5.5へアップデートした所発生した。
  • 環境:CentOS 5.7 x86_64
    1. # rpm -qa | grep mysql
    2. mysqlclient16-5.1.57-1.el5.art
    3. mysql-5.1.59-1.el5.art
    4. mysql-server-5.5.16-2.el5.art
    5. mysql-5.5.16-2.el5.art
    6. mysqltuner-1.2.0-1.el5.art
    7. mysql-libs-5.5.16-2.el5.art
    8. mysql-libs-5.1.59-1.el5.art
    9. mysqlclient15-5.0.90-1.el5.art
    10.  
    11. service mysqld restart
    12. ...
    13. [ERROR] Error message file '/usr/share/mysql/english/errmsg.sys' 
    14. had only 641 error messages, but it should contain at least 714 error messages.
    15.  Check that the above file is the right version for this program!
    16. [ERROR] /usr/libexec/mysqld: unknown variable 'default-table-type=InnoDB'
    17. [ERROR] Aborting
  • /etc/my.cnfを編集。default-table-typeが無くなったのでdefault-storage-engineに変更
    1. -default-table-type=InnoDB
    2. +default-storage-engine=InnoDB
  • 正しいファイルで上書きするか、comp_err コマンドでerrmsg.sys を作り直せばいいようだ。comp_errはrpmには入っていないのでソースから取得する

ユーザ作成

  • 接続
    1. mysql -u root mysql
  • モニタリング用ユーザ作成
    1. MYSQL_HOST=<mysql host>
    2. MYSQL_PASSWD=$(mkpasswd -l 16 -s 0)
    3. echo "GRANT SELECT, REPLICATION CLIENT, SHOW DATABASES, PROCESS ON *.* TO 'readonly'@'%' IDENTIFIED BY '$MYSQL_PASSWD';flush privileges;" | mysql -A -u root -p -h $MYSQL_HOST mysql
    4. mysqladmin status -u readonly -p -h $MYSQL_HOST
  • adminユーザ追加
    1. GRANT ALL ON *.* TO 'admin'@'%' IDENTIFIED BY '';
    2. FLUSH privileges;
  • demoユーザ追加。super権限はread_onlyモードでも書き込めて危険なため与えない。
    1. GRANT
    2.   SELECT,INSERT,DELETE,UPDATE,CREATE,DROP,file,ALTER,INDEX,CREATE routine,CREATE VIEW,LOCK TABLES ON *.* TO 'demo'@'%' IDENTIFIED BY '';
    3. FLUSH privileges;
  • demoユーザの権限削除
    1. REVOKE ALL privileges ON * . * FROM demo;
    2. FLUSH privileges;
  • demoユーザ削除
    1. DELETE FROM mysql.user WHERE user='demo';
    2. FLUSH privileges;
  • 一括で適用する場合
    1. cat >> update.sql << EOS
    2. REVOKE ALL PRIVILEGES ON * . * FROM demo;
    3. GRANT
    4.   SELECT,INSERT,DELETE,UPDATE,CREATE,DROP,file,ALTER,INDEX,CREATE routine,CREATE VIEW,lock tables ON *.* TO 'demo'@'%' IDENTIFIED BY '';
    5. FLUSH privileges;
    6. EOS
    7.  
    8. mysql -u root mysql < update.sql

データサイズ確認

  • DBサイズ、TABLEサイズ出力スクリプト filemysql-show-db-size.sh
    1. ./mysql-show-db-size.sh host user passwd > output.tsv

テーブル毎のサイズ確認


データベース毎のサイズ確認


ibdata1のサイズを縮小

InnoDBのテーブルスペース(/var/lib/mysql/ibdata1)が肥大し続ける。
※innodb_file_per_table追加して再起動後、innodb_file_per_tableを削除して再起動すると、データが全て消えるので注意

  1. データを全てダンプ
  2. /etc/my.cnfに追加
    1. [mysqld]
    2. innodb_data_file_path=ibdata1:1G
    3. innodb_file_per_table
  3. mysqld停止
  4. 以下ファイルを削除
    /var/lib/mysql/ibdata1
    /var/lib/mysql/ib_logfile0
    /var/lib/mysql/ib_logfile1
  5. mysqld起動
  6. データをリストア

レプリケーション

  • マスターサーバの変更
    • slave上で、masterからダンプやrsyncでコピーしておく
      1. #master, slave上でmysqld停止
      2. service mysqld stop
      3.  
      4. # slave上でmasterからコピー
      5. rsync -avz -e "ssh -i db01.example.com.pem" db01.example.com:/var/lib/mysql/ /var/lib/mysql/
    • masterでの作業。現在のポジション確認
      1. service mysqld restart
      2. mysql -e "show master status\G"
      3. ************ 1. row ***************************
      4.             File: mysqld-bin.000238
      5.         Position: 106
      6.     Binlog_Do_DB:
      7. Binlog_Ignore_DB:
    • slaveは合わせて変更
      1. mysql
      2. mysql> CHANGE MASTER TO
      3.   MASTER_HOST='db01.example.com',
      4.   MASTER_USER='repl',
      5.   MASTER_PASSWORD='',
      6.   MASTER_PORT=3306,
      7.   MASTER_LOG_FILE = 'mysqld-bin.000238',
      8.   MASTER_LOG_POS = 106;
      9.  
      10. mysql> slave start;
      11.  
      12. mysql> show slave status\G
      13.  
      14. レプリケーションが正常に可動しているか確認
      15.            Slave_IO_Running: Yes
      16.           Slave_SQL_Running: Yes

Host 'hostname' is blocked

  • max_connect_errors(default:10)以上、同一ホストから接続エラーがあった場合にブロックされる
  • Amazon ELBやHAProxy等のヘルスチェックでtcp 3306を監視している場合にも出る
  • 解除
    1. mysqladmin -u root flush-hosts
  • my.cnfに設定
    1. vi /etc/my.cnf
    2. ----
    3. [mysqld]
    4. max_connect_errors=999999999
    5. ----
    6. service mysqld restart
  • 動作中のサーバに設定
    1. mysql -u root mysql
    2. mysql> show global variables like 'max_connect_errors';
    3. mysql> set global max_connect_errors=999999999;
    4. mysql> quit

バイナリログを削除

  • レプリケーションのバイナリログがディスクを食いつぶす事がある
  • db slave側でどこまで使用しているか確認
    1. mysql -u root
    2.  
    3. mysql> show slave status\G
    4. mysql> Master_Log_File: mysqld-bin.000366
  • db master側で一つ前までを削除
    1. mysql -u root
    2.  
    3. mysql> purge master logs to 'mysqld-bin.000365';
    4.  
    5. 日付で指定したい場合
    6. mysql> purge master logs before '2011-01-01';
  • 自動削除設定 expire_logs_days 英語ドキュメントにしかない
    1. vi /etc/my.cnf
    2. ----
    3. [mysqld]
    4. # バイナリログを自動削除する日数
    5. expire_logs_days=7
    6. ----
    7. service mysqld restart
    • 動的設定
      1. mysql
      2. mysql> set global expire_logs_days=14;
      3. mysql> show global variables like 'expire_logs_days';

スロークエリログ

  • mysql 5.1 `slow_query_log`と`slow_query_log_file`にオプションが分かれた
    1. vi /etc/my.cnf
    2. ----
    3. [mysqld]
    4.  
    5. # slow logの設定
    6. slow_query_log=1
    7. slow_query_log_file = /var/log/mysql-slow.log
    8. # 指定秒以上かかったクエリは上記ログファイルに出力
    9. long_query_time = 3
    10. # n行以上の行をテーブルから読み込んだクエリをスロークエリログに記録する(1000ならそれ未満のクエリは出力しない)
    11. #min_examined_row_limit=1000
    12. # INDEXを使用しないクエリをスロー クエリ ログに記録
    13. #log-queries-not-using-indexes
    14. # 管理用コマンドの実行が遅かった場合もログを出力
    15. #log-slow-admin-statements
    16. ----
    17. service mysqld restart
  • mysql 5.0
    1. vi /etc/my.cnf
    2. ----
    3. [mysqld]
    4.  
    5. # slow logの設定
    6. slow_query_log = /var/log/mysql-slow.log
    7. # 指定秒以上かかったクエリは上記ログファイルに出力
    8. long_query_time = 3
    9. # INDEXを使用しないクエリをスロー クエリ ログに記録
    10. #log-queries-not-using-indexes
    11. # 管理用コマンドの実行が遅かった場合もログを出力
    12. #log-slow-admin-statements
    13. ----
    14. service mysqld restart

order by rand()の高速化

数万件のレコードがあるテーブルに大して、rand()を使うと非常に遅い。これを解決するには?

  1. indexを張ってあるキーを対象にしてrand()を使う
  2. 取り出したキーを使い、欲しいカラムをinで取り出す
    1. SELECT my_table.COLUMN1, my_table.COLUMN2
    2.   FROM ( SELECT my_table.ID FROM my_table ORDER BY RAND() LIMIT 0, 10 ) AS random
    3.   WHERE my_table.ID = random.ID
    4.   LIMIT 0, 10

ツール

  • phpMyAdmin php+apacheで動作するGUI。無償。日本語対応
  • heidisql Windowsで動作するGUIクライアント。無償。英語

innotop

  • CentOS5.5にrpmとしてインストールする場合
    1. yum install perl-DBI perl-DBD-MySQL perl-TermReadKey --enablerepo=rpmforge
    2. wget "http://downloads.sourceforge.net/project/innotop/innotop/1.6.0/innotop-1.6.0.tar.gz?r=http%3A%2F%2Fsourceforge.net%2Fprojects%2Finnotop%2Ffiles%2Finnotop%2F1.6.0%2Finnotop-1.6.0.tar.gz%2Fdownload%3Fuse_mirror%3Djaist&ts=1293523599&use_mirror=jaist"
    3. rpmbuild -tb --clean innotop-1.6.0.tar.gz
    4. rpm -ivh RPMS/noarch/innotop-1.6.0-0.noarch.rpm
    5.  
    6. innotop -d 1
    7.  
    8. vi .innotop/innotop.ini
    9. ----
    10. [connections]
    11. default=user=root have_user=root have_pass= dsn=DBI:mysql:database=:host=localhost:port=3306 savepass= dl_table=
    12. ----

カラムコメント

  • カラムにコメントを付与
    1. mysql> CREATE TABLE item ( name text comment 'item name');
  • コメントを表示
    1. mysql> SHOW FULL COLUMNS FROM item;
    2. +-------+------+-----------------+------+-----+---------+-------+---------------------------------+-----------+
    3. | FIELD | Type | Collation       | NULL | KEY | DEFAULT | Extra | Privileges                      | Comment   |
    4. +-------+------+-----------------+------+-----+---------+-------+---------------------------------+-----------+
    5. | name  | text | utf8_general_ci | YES  |     | NULL    |       | SELECT,INSERT,UPDATE,REFERENCES | item name | 
    6. +-------+------+-----------------+------+-----+---------+-------+---------------------------------+-----------+

Spiderエンジン


tmpdirの指定

  • メモリを食いつぶす程アクセスがあると、tmpdirに沢山のファイルができる(21GBまで確認)。tmpdirを移動する
  • 現在の設定を確認
    1. mysqladmin variables | grep -e tmpdir -e datadir
  • 設定変更
    1. vi /etc/my.cnf
    2. ----
    3. tmpdir=/tmp/
    4. ----
    5. service mysqld restart

圧縮しながらダンプ/リストア

  • gzipを使うと30%程になる
  • リストアがダンプの数倍遅い。
    • --all-databases --databases DB1 DB2 とするとmysql DBのデータはダンプされず、user が紛失するので注意
    • 大きいテーブルをダンプする場合 --no-autocommit オプションを付けると改善する場合がある
  • --single-transaction オプションを付けないと、全ロックになる。selectも出来ない
  • --skip-optを付けると--create-options が外れ、CREATE TABLE文からAUTO_INCREMENTが消える。--skip-optは使わずに除外したいオプションに"--skip"を付与した --skip-add-drop-table のように指定すると良い。
  • 高速リストアオプション付けた場合の例
    1. #log-bin
    2. skip_innodb_doublewrite
    3. innodb_flush_log_at_trx_commit=0
    • Amazon EC2 m1.small
    • ダンプ SQL:1.6GB / gz圧縮:504M / 約10分
    • gzファイルからレストア: 約20分
  • ダンプ
    1. mysqldump -u [ユーザ名] -h [ホスト名] [DB名] | gzip > [DB名].gz
    2.  
    3. # 全データベースをロックせずにバックアップ(MySQL5.1以降、--optはデフォルト)
    4. mysqldump --opt --all-databases --single-transaction --no-autocommit --flush-logs --hex-blob | gzip > mysql.`date +%Y%m%d`.gz
    5.  
    6. # マスターの状態(binary log filename, log position)も保存する場合
    7. mysqldump --opt --all-databases --single-transaction --no-autocommit --flush-logs --hex-blob --master-data=2 --order-by-primary | gzip > mysql.YYYYMMDD.gz
  • ダンプ中のgzipのCPU負荷が高い場合
    1. sudo renice -n 10 $(pgrep gzip)
  • リストア
    1. zcat [DB名].gz | mysql -u [ユーザ名] -h [ホスト名] [DB名]
    2. # または
    3. zcat [DB名].gz | mysql -u [ユーザ名] -h [ホスト名] [DB名]
    4. # リストア後mysqldを再起動しないとユーザ情報が適切に読み込まれなかった
    5. service mysqld restart

  • 高速にリストア
  • リストア前
    1. vi /etc/my.cnf
    2. ----
    3. #log-bin
    4. skip_innodb_doublewrite
    5. innodb_flush_log_at_trx_commit=0
    6. ----
    7. service mysqld restart
  • リストア実行
    1. mysql -u root -e "set sql_log_bin=0;SOURCE dump.sql;set sql_log_bin=1;"
  • リストア後。log-binはマスターのみ
    1. vi /etc/my.cnf
    2. ----
    3. log-bin
    4. #skip_innodb_doublewrite
    5. #innodb_flush_log_at_trx_commit=0
    6. ----
    7. service mysqld restart

動的に設定

  • 取得
    1. mysql -uroot mysql
    2. mysql> SHOW VARIABLES LIKE 'max_connections';
    3. mysql> SHOW global VARIABLES LIKE 'key_buffer_size';
  • 設定
    1. mysql -uroot mysql
    2. mysql> SET global max_connections = 1000;
    3. mysql> SET global table_cache = 1024;
    4. mysql> SET global sort_buffer_size = 2097152;
    5. mysql> SET global query_cache_size = 33554432;
    6. mysql> SET global thread_cache_size = 500;
    7. mysql> SET wait_timeout = 3600;
    8. mysql> SET interactive_timeout = 3600;
    9. mysql> SET global key_buffer_size = 32*1024*1024;

設定値の確認

  • innodb_log関係の値を見たいとき。罫線が不要なら「-s」オプションを付ける
    1. mysql -uroot -e "show variables like 'innodb_log%'"
    2. +---------------------------+---------+
    3. | Variable_name             | Value   |
    4. +---------------------------+---------+
    5. | innodb_log_arch_dir       |         | 
    6. | innodb_log_archive        | OFF     | 
    7. | innodb_log_buffer_size    | 1048576 | 
    8. | innodb_log_file_size      | 5242880 | 
    9. | innodb_log_files_in_group | 2       | 
    10. | innodb_log_group_home_dir | ./      | 
    11. +---------------------------+---------+

プロセスを強制的に停止する

  • 停止手段
    1. 通常の手段
    2. service mysqld stop
    3.  
    4. プロセスリストの表示
    5. mysqladmin processlist
    6.  
    7. 該当プロセスを停止
    8. mysqladmin kill プロセスID1,プロセスID2,プロセスID3 -u root -p
    9.  
    10. 停止を試みる
    11. service mysqld stop
    12.  
    13. それでも止まらない(最終手段)
    14. ps aux | grep mysql
    15. kill -9 mysqldのプロセスid
  • 複数のプロセスを一括kill:DB: example, Command: Sleepのプロセスを殺す
    1. # $F[1] : ID
    2. # $F[3] : User
    3. # $F[5] : Host
    4. # $F[7] : DB
    5. # $F[9] : Command
    6. # $F[11] : Time
    7. mysql_user=hoge
    8. mysql_pw=hogehoge
    9. mysql_host=localhost
    10. mysqladmin processlist -u $mysql_user -p$mysql_pw -h $mysql_host | perl -ane 'if($F[1]!="" && $F[7]=="example" && $F[9]=="Sleep"){printf "$F[1]\n";}' | xargs -i mysqladmin kill {} -u $mysql_user -p$mysql_pw -h $mysql_host

Could not initialize master info structure

  • slave開始時に以下のエラーが出る場合、リセットす | grep Data_free
         Data_free: 10485760
    }}
    • 18MB程のSQLをinsert後。ibdata1のサイズは増加。Data_freeは 13MB
      1. mysql -u root -p -D test -e る
      2.  
      3. -ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
      4. #geshi(bash){{
      5. mysql> reset slave;

MySQL manager or server PID file could not be found!

  1. # service mysql restart
  2. MySQL manager or server PID file could not be found!       [失敗]
  3. Starting MySQL...Manager of pid-file quit without updating [失敗]
  • プロセスが起動している場合は停止
    1. service mysqld stop
    2. ps aux | grep -i mysql
    3. kill -9 pid
  • データを消していい場合は削除して初期化
    1. rm -rf /var/lib/mysql
    2. mysql_install_db --user mysql
  • /var/lib/mysql のオーナー、パーミッションも確認 mysql:mysqlになっているか
  • 再起動
    1. service mysqld restart

my.cnfサンプル


mysqlbench

  • mysqlbench v0.1
    1. yum install mysql-server mysql-devel
    2. service mysqld start
    3. cd /usr/local/src
    4. wget -O mysqlbench-0.1.tgz "http://www.mysql.gr.jp/frame/modules/bwiki/index.php?plugin=attach&refer=Contrib&openfile=mysqlbench-0.1.tgz"
    5. tar xvfz mysqlbench-0.1.tgz
    6. cd mysqlbench-0.1
    7. make
    8.  
    9. mysqladmin create mysqlbench
    10. ./mysqlbench -i -U root mysqlbench
    11. 100クライアント(-c)100トランザクション(-t)を実行した場合のベンチマーク
    12. ./mysqlbench -U root -E innodb -c 100 -t 100 mysqlbench
    13.  
    14. mysqladmin drop mysqlbench
  • 64bit環境ではコンパイルエラーになるので修正
    1. vi Makefile
    2. ----
    3. MYSQL_LFLAGS= -L/lib -L/usr/lib64/mysql
    4. --- flush privileges;
    5.  
    6. mysql -u user1 -p db1
    7. ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
  • localhost用のユーザを作成すると接続できる。
    1. mysql -uroot mysql
    2. mysql-

Tritonn MySQL+全文検索エンジンパッケージ

  • Tritonn
    • ソース、バイナリ(rpm)の配布あり

定価と安値から価格の安い順に表示

create table item (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL ,
	name CHAR(30) NOT NULL,
	normalprice int default 0,
	saleprice int default 0,
	PRIMARY KEY(id)
);

insert into item (name, normalprice, saleprice) values ('item1', 100, 100);
insert into item (name, normalprice, saleprice) values ('item2', 200, 10);
insert into item (name, normalprice, saleprice) values ('item3', 300, 5);

select
	item.*,
	tmp.price
from
	item
	, (
		select id, if (saleprice < normalprice, saleprice, normalprice) as price from item
	) tmp
where
	item.id = tmp.id
	order by tmp.price

----
+----+-------+-------------+-----------+-------+
| id | name  | normalprice | saleprice | price |
+----+-------+-------------+-----------+-------+
|  3 | item3 |         300 |         5 |     5 | 
|  2 | item2 |         200 |        10 |    10 | 
|  1 | item1 |         100 |       100 |   100 | 
+----+-------+-------------+-----------+-------+
3 rows in set (0.00 sec)

SQLの小技

DNS使用時にデッドロック

常に低速の DNS と多数のホストがある場合は、--skip-name-resolve で DNS ルックアップを無効化するか、HOST_CACHE_SIZE の定義(デフォルト値: 128)を拡張し、mysqld を再コンパイルすることで、パフォーマンスを改善できます。

MySQL5で文字化け

クエリーを投げる前に、設定してやると良い。

SET CHARACTER SET SJIS

mysqldump時にテーブルロックがかかる

デフォルトでは、mysqldump実行中はテーブルにロックがかかってしまいます。 mysqldump中はDB操作が一切できない状態になります。(テーブル単位ではなく DB全体)ので注意が必要。

  • 変更方法
    • 「--single-transaction」オプションを付けないと、全ロックになる
    • ダンプ時にロックがかからなくなるとデータの整合性が取れなくなるので、変更するかどうかは注意すること。
    • /etc/my.cnf
      1.  [mysqldump]
      2.  lock-tables=false
      3.  create table item ( name text comment 'item name');

添付ファイル: filemysql-insert-test.zip 54件 [詳細] filemy.cnf.5.5 126件 [詳細] filemysql-show-db-size.sh 440件 [詳細] filemysql_insert_dummy.sh 652件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2017-05-25 (木) 12:56:42 (453d)