まだ重たいCMSをお使いですか?
毎秒 723リクエスト を捌く超高速CMS「adiary

2012/12/19(水)MySQL設定メモ

InnoDBでMySQLを運用することを前提に書きます。

my.cnfの設定

MySQL 5.5以降ならばInnoDBの新しいエンジンが入っているが、5.1.xの場合はプラグインとなっているのでそれを有効化する。

[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

また同時に、デフォルトエンジンとしてInnoDBの設定、及びutf8を設定する。

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
default-storage-engine=InnoDB

mysqlコマンド

コマンドライン書式の例。

# mysql -u root -p
# mysql -u user -pPASS dbname
# mysql -h db-server.dummy.xxx -P 3306 -u user -pPASS dbname

DBとユーザーの作成

CREATE DATABASE dbname DEFAULT CHARSET=utf8;
GRANT all privileges ON dbname.* TO dbuser@localhost IDENTIFIED BY 'PASSWORD';
GRANT SELECT,INSERT,UPDATE,DELETE ON privileges ON dbname.* TO dbuser@localhost IDENTIFIED BY 'PASSWORD';

IPアドレス等を指定する場合は次のようにする。

書式意味
uname@localhostUNIXドメインソケット通信を許可
uname@127.0.0.1ローカルホスト(TCP/IP)を許可
uname@'%'全てのホストを許可
uname@192.168.1.12IPアドレス指定
uname@'192.168.%'IPアドレスが192.168.* のホストを許可

この他、Firawall(iptables)を設定するのはもちろんのこと、MySQLはhosts.allowも参照する

mysqld: ALL

ユーザー情報の確認

use mysql;
select host,user,password from user;

パスワード以外は直接書き換えれば良い。

パスワードは次のようにする。

SET PASSWORD FOR user@localhost=PASSWORD('password');
SET PASSWORD FOR user@your.host=PASSWORD('password');

文字コードの確認

show variables like 'char%';

2010/01/27(水)PostgreSQL関連メモ

あとで適当に追記。

共有メモリ設定

共有メモリの確認

# ipcs -m
# cat /proc/sys/kernel/shmmax

共有メモリの変更

1G
# echo 1099511627776 >/proc/sys/kernel/shmmax
1.5G
# echo 1649267441664 >/proc/sys/kernel/shmmax

起動時に設定するには。

# /etc/sysctl.conf
kernel.shmmax = 1649267441664

制約やカラムの操作

Ver8.3で確認。テーブル名、カラム名が変更されていても作成時のものとなるので注意。

制約の種類制約名
PRIMARY KEY[table]_pkey
UNIQUE[table]_[col]_key
REFERENCES[table]_[col]_fkey
ALTER TABLE table ADD CONSTRAINT table_[col]_fkey FOREIGN KEY ([col]) REFERENCES usr([ref_col]);
ALTER TABLE table DROP CONSTRAINT table_[col]_key;
目的SQL
NOT NULLを付けるALTER TABLE table ALTER COLUMN [col] SET NOT NULL
NOT NULLを外すALTER TABLE table ALTER COLUMN [col] DROP NOT NULL
DEFAULTを付けるALTER TABLE table ALTER COLUMN [col] SET DEFAULT [value]
DEFAULTを外すALTER TABLE table ALTER COLUMN [col] DROP DEFAULT
カラム名を変えるALTER TABLE table RENAME [old_col] TO [new_col]
テーブル名を変えるALTER TABLE table RENAME TO table2

2009/09/04(金)MySQL/PostgreSQLでのシリアル値まとめ

adiaryではすべてのテーブルに pkey というシリアル値(PRIMARY KEY)を設定しています。その扱いについて。

特にMySQLで特別な加工せずに安全なシリアル値を取得する方法。

PostgreSQLの場合

PostgreSQLではそのままserial型というものがあり、

CREATE TABLE test(pkey SERIAL PRIMARY KEY, x INT);
INSERT INTO test(x) VALUES(10);

とすることで、pkeyをプライマリキーとして自動的に生成することができます。

PostgreSQLではシーケンス操作関数というものがあり、SERIAL型を定義すると自動的に作成されます。

例えば、現在の値を取得したければ

SELECT currval(pg_catalog.pg_get_serial_sequence('test', 'pkey'))

とします。pkeyに値を設定して INSERT したとき、シーケンス関数は自動的に再定義されないため次の関数で再設定する必要があります。

SELECT setval(pg_catalog.pg_get_serial_sequence('test', 'pkey'), (SELECT max(pkey) FROM test))

現在の値(最後に生成された値)を取得するには次のようにします。

SELECT currval(pg_catalog.pg_get_serial_sequence('test', 'pkey'), (SELECT max(pkey) FROM test))

同じセッション内でテーブル問わず最後に生成された値を取得する場合は次で済みます。

SELECT lastval()

安全に(他のセッションともかぶらない唯一無二な)次のシリアル値を取得するには、次のようにします。

SELECT nextval(pg_catalog.pg_get_serial_sequence('test', 'pkey'))

MySQLの場合

MySQLにも5.1以降(それより前から?)SERIAL型があります。

CREATE TABLE test(pkey SERIAL PRIMARY KEY, x INT);
INSERT INTO test(x) VALUES(10);

これによりpkeyに自動的にシリアル値を設定することができます。

  • SERIALは「BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE」のエイリアスです。
  • AUTO_INCREMENTは1つのテーブルに1カラムしか指定できない。
  • AUTO_INCREMENTを指定したカラムに、例えばpkeyを指定してINSERTしても、AUTO_INCREMENTがよきにはからってくれる(PostgreSQLのように値を再設定する必要なし)。

現在の値(最後に生成された値)を取得するには次のようにします。

SELECT LAST_INSERT_ID();

ただしC API等では mysql_insert_id()というものがあるため、こちらを使用するほうが効率が良いようです。例えばPerl DBIならば $sth->{mysql_insertid}。

追記。

SELECT * FROM test WHERE pkey IS NULL;

以前はこの方法でも取得が可能でしたが、いつの間にか使えなくなっているようなので注意しましょう。

値を取得、値の変更

SHOW TABLE STATUS WHERE NAME = 'test';

で得られたテーブル情報の中からAuto_incrementカラムの値を参照します。得られるのは次に挿入される値です*1

何かの都合で値を設定するときは次のようにします。

ALTER TABLE test AUTO_INCREMENT=1;

安全に次の値を取得

MySQLでもっとも厄介なのはこれです。auto_incrementには安全に次の値を得る方法が提供されていません。別にシーケンステーブルを作る方法などがありますが、面倒です。

INSERTでしか得られないならINSERTしてしまえばいいやという単純な方法です。

INSERT INTO test() VALUES();
DELETE FROM test WHERE pkey=LAST_INSERT_ID();
SELECT LAST_INSERT_ID();

NOT NULL制約等が付いているとINSERTが失敗するので、CREATE TABLE等であらかじめ制約を満たすDEFAULT値を設定しておく必要があります。MyISAMではトランザクションが使えないため、NOT NULLかつUNIQUE制約が付いていると、複数のプロセスが同時に"INSERT INTO test() VALUES();"を実行したとき一方が失敗します。

MySQLの場合はINSERTしてUPDATEした方がスマートなのはたしかなのですが、PostgreSQLではシーケンス型の次の値を安全に取得できるし、INSERTに成功してUPDATEに失敗した場合を考えるとややこしいので。

*1 : この値を使って次のデータをINSERTするのは危険です。

OK キャンセル 確認 その他