デフォルトのストレージエンジンをInnoDBにする

LeopardにdmgからインストールしたMySQLのデフォルトのストレージエンジンがMyISAMだってことに最近気付いた。
デフォルトのストレージエンジンを変更するにはmy.cnfでdefault-storage-engineを指定すればOK

$ sudo vi /etc/my.cnf
$ cat /etc/my.cnf
[mysqld]
default-storage-engine=InnoDB

mysqlを再起動して、実際にInnoDBになったか確認

$ mysql -uroot
mysql> create database foo;
mysql> use foo
mysql> create table bar(id int not null);
mysql> show table status bar ¥G
*************************** 1. row ***************************
      Table: bar
Create Table: CREATE TABLE `bar` (
 `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MySQL 主キーの追加・削除

忘れるのでメモ

主キーの追加

ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名1[,カラム名2...]);

主キーの削除

ALTER TABLE テーブル名 DROP PRIMARY KEY

削除時下のようなエラーがでたら制約を疑う

OperationalError: (1025, "Error on rename of './foo/#sql-b6f_3958' to './foo/bar' (errno: 150)")

制約を確認

mysql> SHOW CREATE TABLE foo;

AmazonRDSでslow queryを出力するようにする方法

AmazonRDSは、デフォルトの設定ではslow queryを出力するようになっていない。
また、MySQLが動いているサーバにアクセスすることができないので、slow queryをログファイルに出力したとしてもそれを閲覧する術がない。
RDSでも採用されているMySQL 5.1からは、slow queryをテーブルに保存できるようになったので、それを使う。

方針

• ログはテーブル(mysql.slow_log)に出力する。


• 実行に1秒以上かかったクエリを記録する


• 100行以上を読み込んだクエリを記録する

普通なら以下のオプションをmy.cnfに設定すればOK。

[mysqld]
slow_query_log = ON
long_query_time = 1.000
log_output = TABLE
min_examined_row_limit=100

RDSの場合は、db-parameter-groupを作成し、パラメータを編集し、db-instanceに適用する。
今回はacme-param-grpというdb-parameter-groupを作成し、acmeというdb-instanceに適用する。

db-parameter-groupを作成

$ rds-create-db-parameter-group acme-param-grp -e MySQL5.1 -d "parameter group for acme"

db-parameter-groupのパラメータを編集
(log_outputはデフォルトでTABLEになっているので設定しない。設定しようとしてもできない)

$ rds-modify-db-parameter-group acme-param-grp \
--parameters="name=slow_query_log, value=ON, method=immediate" \
--parameters="name=long_query_time, value=1, method=immediate" \
--parameters="name=min_examined_row_limit, value=100, method=immediate"

methodに指定できる値はimmediate もしくは pending-reboot

編集したパラメータの確認

$ rds-describe-db-parameters acme-param-grp --source user
DBPARAMETER  long_query_time         1    user  integer  dynamic  true
DBPARAMETER  min_examined_row_limit  100  user  integer  dynamic  true
DBPARAMETER  slow_query_log          1    user  boolean  dynamic  true

sourceに指定できる値はuser 、system、 engine-default

db-parameter-groupをdb-instanceに適用

$ rds-modify-db-instance acme --db-parameter-group-name=acme-param-grp

db-instanceを再起動

$ rds-reboot-db-instance acme

db-instanceにdb-parameter-groupが適用されているか確認

$ rds-describe-db-instances
DBINSTANCE  acme  2009-11-04T10:59:28.860Z  db.m1.large    mysql5.1  100  xxxx  available  acme.xxxxxxx.us-east-1.rds.amazonaws.com  3306  us-east-1d  1
     SECGROUP  xxxx  active
     PARAMGRP  acme-param-grp  in-sync

PARAMGRPがacme-param-grpになっていて、ステータスがin-syncになっていればOK。

さらにmysqlにアクセスしパラメータが有効になっているか確認する。

mysql > show global variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show global variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 100   |
+------------------------+-------+
1 row in set (0.00 sec)

MySQL 外部キー制約の追加、削除

忘れるのでメモ

MySQL 5.1で確認した。

外部キー制約の確認

SHOW CREATE TABLE テーブル名;

show create table bbs_thread;

外部キー制約の追加

ALTER TABLE テーブル名 ADD FOREIGN KEY (制約を張りたいカラム) REFERENCES 張りたいテーブル(張りたいカラム);

alter table bbs_thread add foreign key (creator_id) references accounts_user(id);

外部キー制約の削除

ALTER TABLE テーブル名 DROP FOREIGN KEY 制約名;

ALTER TABLE bbs_thread drop foreign key creator_id_refs_id_75448b6c;

mysqldumpで文字化けしないためのメモ

ローカルの開発環境で使っていたMySQLなんですが、何も考えずデフォルトの設定で使っていたら、mysqldumpした際に、データが文字化けして、ちょっとは待ったのでメモ。

MySQLのバージョンは4.1.20。
文字化けする原因は、mysqldumpがデフォルトでは、文字コードをUTF-8で出力するようになっていて、フィールドの型がUTF-8でない場合は、自動でUTF-8に変換するためのようです。

僕が使っていたDBは文字コードについて特に何も設定していなかったので、デフォルトの文字コードであるlatin1になっていました。

なので、これもmysqldumpするとlain1 -> UTF-8な変換が自動で行われ文字化けしたということのようです。納得。

この自動変換を行わないようにすれば解決するはず。ということで調べてみると、--default-character-setというオプションを使うとよいことがわかりました。これを使ってデフォルトの文字コードをDBの文字コードとあわせてやることで、自動変換が行われなくなり、文字化けしないようです。
で、結局以下のコマンドで文字化けせずdumpすることができました。

>

mysqldump --default-character-set=latin1 -uroot --all-databses > db.dump

<

これでデータ自体は文字化けしなくなりますが、これをそのまま、UTF-8なDBに取り込むと、取り込んだデータが文字化けしてしました。
ダンプしたデータを見てみると、所々にSET NAMES latin1とかDEFAULT CHARSET=latin1のように「latin1]の文字が。。
これが原因だったようで、ワンライナーでlatin1をutf8に変更してからインポートしたところ、文字化けせずに取り込むことができました。

>

perl -pi -e 's/latin1/utf8/' db.dump

<