MySQL


mysql-shell: mysql-clientより高度なclient

例:


autocommit値の確認

SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 ROW IN SET (0.00 sec)

pt-query-digest: slow queryの分析

他にもたくさんのツールが入っている:


binlog_format はread replicaに引き継がれない


設定の優先度


SSL設定の確認

mysql -ureadonly -h localhost -e "show global variables like 'ssl_%'\G;"

mysql_config_editor: .mylogin.cnfをcli対話式で作成

MySQL client 5.6以降で使用可能。


SQLの正規化

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


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のテスト等で使える

mysql> SELECT sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 ROW IN SET (5.01 sec)

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


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


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

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


my.cnfサンプル


ユーザ認証をしない


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


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


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


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


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

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

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


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


utf8mb4: 4バイトUTF8

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

[mysqld]
...
character-set-server=utf8mb4

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

[mysqldump]
default-character-set=utf8mb4

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


Percona Server


死活確認


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


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

MySQL-MHA


パフォーマンスチューニング


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


トラブルシューティング


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

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


ユーザ作成


データサイズ確認


テーブル毎のサイズ確認


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


ibdata1のサイズを縮小

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

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

レプリケーション


バイナリログを削除


スロークエリログ


order by rand()の高速化

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

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

ツール


innotop


カラムコメント


Spiderエンジン


tmpdirの指定


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



動的に設定


設定値の確認


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


mysqlbench


全文検索機能

記事:


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

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全体)ので注意が必要。


添付ファイル: filemysql-insert-test.zip 1806件 [詳細] filemy.cnf.5.5 1652件 [詳細] filemysql-show-db-size.sh 1933件 [詳細] filemysql_insert_dummy.sh 2113件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2024-01-15 (月) 16:08:08