mysql

出典: wiki.nodoka.org

MySQL-5.0.51のインストール

mecab, openssl, sennaが既にインストールされている前提。
全文索引にsenna、通信用にSSLが使えるように設定する。

cd /usr/local/src
wget http://osdn.dl.sourceforge.jp/tritonn/31597/tritonn-1.0.10-mysql-5.0.51a.tar.gz
tar xvzf tritonn-1.0.10-mysql-5.0.51a.tar.gz
cd tritonn-1.0.10-mysql-5.0.51
export LD_LIBRARY_PATH=/usr/local/mecab/lib
./configure \
    --prefix=/usr/local/mysql \
    --with-charset=ujis \
    --with-extra-charsets=all \
    --with-senna=/usr/local/senna \
    --with-mecab=/usr/local/mecab \
    --enable-assembler \
    --enable-local-infile \
    --with-mysqld-user=mysql \
    --with-pic \
    --enable-thread-safe-client \
    --with-ssl=/usr/local/openssl \
    --with-readline
make
make install

設定ファイル作成

vi /usr/local/mysql/etc/my.cnf

[mysqld]
default-character-set = ujis
report-host        = db1
server-id          =   1

#log-bin
#log-slave-updates
#expire-logs-days   = 3

log=sql.log
log-warnings
log-error
log-slow-queries
long-query-time    = 1
#log-queries-not-using-indexes
senna-log

#skip-network
#skip-slave-start
skip-name-resolve
skip-character-set-client-handshake
wait-timeout       = 100

max_connect_errors = 10000
max_connections    = 200
thread_cache       = 200
thread_concurrency = 8
table_cache        = 400

key_buffer_size         = 256M
sort_buffer_size        = 1M
myisam_sort_buffer_size = 64K
read_buffer_size        = 1M

[mysql]
default-character-set = ujis
[mysqldump]
default-character-set = ujis

データ作成

cd /usr/local/mysql
mkdir data
chown -R root:mysql .
chown -R mysql data
/usr/local/mysql/bin/mysql_install_db --user=mysql

起動停止試験

/usr/local/mysql/bin/mysqld_safe --user=mysql &
/usr/local/mysql/bin/mysqladmin -u root shutdown

起動ファイル作成

cp /usr/local/mysql/share/mysql/mysql.server /etc/init.d/mysql
chkconfig --add mysql
chkconfig --list mysql
service mysql start

TIPS

パスワードの変更

set password for root@localhost=PASSWORD('PASSWORD');

ログファイルに書き出す

mysql -uUSER -pPASS --tee=update.log

データディクショナリの確認

use information_schema;
SELECT * FROM tables WHERE table_schema='DATABASE';

変数の確認と変更

show global variables like '%timeout';
set global wait_timeout=100;

権限の付与

grant all on testdb.* to writer@localhost identified by 'XXXXXXXX';
grant all on testdb.* to writer@'192.168.0.%' identified by 'XXXXXXXX';
grant select on testdb.* to reader@localhost identified by 'YYYYYYYY';
grant select on testdb.* to reader@'192.168.0.%' identified by 'YYYYYYYY';

SELECT INTO ... OUTFILEやLOAD DATA INFILEの権限付与

※GLOBAL PRIVILEGESなのでDB単位での権限付与は不可。

grant file on *.* to writer;

バックアップとリストア

mysqldump -u root -p -A > mysql.dmp
mysqldump -u root -p NEWDB > newdb.dmp
mysql -u root -p NEWDB< newdb.dmp

特定のテーブルのみmysqldump

mysqldump -u root -t newdb newtable > newtable.dmp;

create table文の確認

show create table newtable;

エンジンの一覧

show plugins;

binlogの削除

purge binary logs to 'mysql-bin.000006';

slow.logの解析

処理時間順にソートする。

mysqldumpslow -s t mysqld-slow.log

実行回数順にソートする。

mysqldumpslow -s c mysqld-slow.log

binlogの確認

binlogを頭から確認する。

mysqlbinlog /data/mysql/db1-bin.000093

binlogを特定ポジションから確認する。

mysqlbinlog --position=699128200 /data/mysql/db1-bin.000093

レプリを1つ進める

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

SQL

日付加算

SELECT ADDDATE(NOW(),-30);

時間の差分を取得

SELECT TIMEDIFF(end_date,start_date) FROM test_data WHERE TIMEDIFF(end_date,start_date)>'00:05:00';

UNIXTIMEへの変換

SELECT UNIX_TIMESTAMP('2010-07-07 16:00:00');

UNIXTIMEからの変換

SELECT FROM_UNIXTIME(start) FROM test_data;

日付フォーマット

SELECT DATE_FORMAT(NOW(),'%Y/%m/%d %H:%i:%s');

変数の使用

set @target_id=10000;
SELECT * FROM test WHERE id=@target_id;

行番号の付与

set @i=0;
SELECT @i:=@i+1 row,id FROM test_data limit 100;

idを1から振り直す

set @i=0;
UPDATE test_data SET id=@i:=@i+1;

複数の索引を削除

ALTER TABLE test_data DROP INDEX ind1, DROP INDEX ind2, DROP INDEX ind3;

ファイルへtsv出力

SELECT * FROM test_data INTO OUTFILE '/tmp/test.tsv';

tsvファイルからデータロード

LOAD DATA INFILE '/tmp/test.tsv' INTO TABLE test_data;