alucepsの日記

ソフトウェアエンジニアをしているおっさんが生きている中でメモしたいと思ったことを記録します。

mysql に関する作業まとめ

dump 作成

mysqldump -h hostname -u username -p databasename > backup.dump

MySQLのデータベースをmysqldumpでバックアップ/復元する方法 | WEB ARCH LABO

例えばバックアップの対象となるデータが膨大で、バックアップに1時間かかるとしましょう。 そうした場合でもトランザクションを利用してくれるので、バックアップを開始した時点のデータも(1時間後に)終了する間際のデータも全く整合性のとれたデータであることを保証してくれます。 ですので、 –single-transaction オプションは何も考えずにつけておくと良いと思います。

大量のデータを持っているデータベースを対象にする場合はこのようにする。

mysqldump --single-transaction -h hostname -u username -p databasename > backup.dump

dump ファイルの import

mysql -h hostname -u username -p -D databasename < backup.dump

sql の実行

mysql -h hostname -u username -p -D databasename -e 'show databases;'

sql ファイルの実行

mysql -h hostname -u username -p -D databasename < create_tables.sql

csv ファイルからデータを import

ダブルクォーテーションで囲まれているカンマ区切りのデータをインポートする想定。

mysqlimport -h hostname -u username -p --fields-terminated-by=, --fields-enclosed-by=\" databasename

このようなエラーがでる時はローカルにあるファイルが読み込みできていない。

mysqlimport: Error: 13, Can't get stat of '/opt/rh/mysql55/root/var/lib/mysql/cotrip_web/city.csv' (Errcode: 2)

以下オプションを付加するとうまくいく。

--local クライアントホストから入力ファイルをローカルで読み取ります

ヘッダ行がある場合は --ignore-lines=1 を付加する。

番外編

field1,field2,...csv"field1","field2",... にする。

cat file.csv | sed -e 's/,/","/g' | sed -e 's/^/"/g' | sed -e 's/.$/"/g' > file.mod.csv

チューニング

mysql の設定値をいくつか変更した。ちなみに mysql が動作している環境はメモリが 1GB。

名称 デフォルト値 変更後の値
innodb_buffer_pool_size 128M 800M
innodb_log_file_size 5M 64M
innodb_flush_log_at_trx_commit 1 2
table_open_cache 400 1024
thread_cache_size 0 16

innodb_log_file_size を変更したら rm -f /var/lib/mysql/ib_logfile* をしておく。じゃないと mysql が起動できない。

参考:MySQLを高速化したいときのチューニング方法

初めてのPHP & MySQL 第2版

初めてのPHP & MySQL 第2版