MySQL Command.md

MySQL Command List

Dump 不要なテーブルは「--ignore-table=(テーブル名)」で排除

mysqldump -u <user> -p<password> dbname --ignore-table=dbname.table > dump.sql

DDL取得

mysqldump -u <user> -p<password> --no-data dbname > ddl.sql

データ（INSERTクエリ）取得

mysqldump -u <user> -p<password> --no-create-info dbname > data.sql

DBインポート

mysql -u <user> -p<password> dbname < data.sql

DDLなし + CSV はきだし

mkdir ./csv
chmod o+x ./csv
mysqldump -u <user> -p<password> --tab=./csv --fields-terminated-by=, --fields-optionally-enclosed-by=\" --lines-terminated-by="\r\n" dbname

SELECT文からCSVデータで出力

use dbname
SELECT * INTO OUTFILE'/tmp/hoge.csv' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES STARTING BY '' TERMINATED BY '\r\n' FROM table;

テーブル指定し出力

mysqldump -u <user> -p<password> -p -t dbname table1 tabl2 > no_data.sql 

output by CSV format

mysql -u user -ppassword
> use dbname
> LOAD DATA INFILE "<CSVFile>" INTO TABLE table FIELDS TERMINATED BY ',' ENCLOSED BY '"';

全テーブルTRUNCATE

mysql -u root dbname -N -e 'show tables' | while read table; do mysql -u root -e "truncate table $table" dbname; done

AUTO_INCREMENT値設定

ALTER TABLE <table> auto_increment=<int val>;

AUTO_INCREMENT値取得

SHOW TABLE STATUS LIKE '<table>';

DBの文字コード確認

SHOW CREATE DATABASE dbname

gz形式で圧縮状態のファイルを特定DBスキーマへ実行

zcat dump.sql.gz | mysql -u <user> -p<password> dbname

全テーブルの統計情報をサイズ順に一覧表示

SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length + index_length) / 1024 / 1024) AS allMB, floor((data_length) / 1024 / 1024) AS dMB, floor((index_length) / 1024 / 1024) AS iMB FROM information_schema.tables WHERE table_schema = database() ORDER BY (data_length + index_length) DESC;