前回記事:MySQLでテーブルを作成・削除・変更する方法では、MySQLにおけるテーブルの追加・変更・削除についてご紹介しました。今回は、MySQLを便利にしている機能のうちの一つである、CSVファイル形式でデータを入出力する方法についてご紹介したいと思います。
こんにちは。 開発チームのワイルド担当、まんだいです。 MySQLのデータをエクセルで編集したい時はないですか? 今回は、そう言った時にMySQLのデータをCSVに変換して、エクセルで読める形式にする方法をまとめてみました。 mysqlの内容をファイルに書き落とす場合によく使うのが、mysqldumpコマンドですが、特に指定しない場合は、SQLの状態で出力されます。 別のDBへデータを移す際は、これで問題ないですが、このファイルをエクセルで加工したい場合はどうでしょう? mysqldumpコマンドには、この要望にも応えるべく、--tabオプションが用意されています。 今回検証用に用意したMySQLのバージョンは、MySQL Community Server 5.6.35で、mysqlの公式リポジトリを経由してインストールしたものです。 mysqldumpコマンド単品で取得してみる mysq
問題 今、10GBぐらいあるダンプファイルをMySQLのデータベースにリストアしているんだけど、 いったいいつ頃終わるんでしょうね? 答え1 私がよく触るある環境では1GBあたり2分ぐらいかかります。 そんな感じで、いつも作業するときに、時間を気にする癖をつけておくとよいです。 SSDとHDD、物理サーバーとクラウド環境などで違うと思うので、日ごろから気にしておくのが良いです。 答え2 ダンプを投入するMySQLが、バイナリログが出力される設定になっていたら、バイナリログの容量が目安になります。 MySQLのダンプファイルはSQLのテキストデータで、バイナリログも実行されたSQLが書かれていくので、投入が終わるころには、バイナリログがだいたいダンプファイルの容量分増えます。 15分でバイナリログが5GB増えていたら、10GBのダンプが入り終わるのはあと15分、トータルで30分ぐらいかなと予
進捗表示は pv コマンドで出来る。 macの場合は brew install でインストール出来る。 $ brew install pv リストア時の進捗を表示させる時は下記のようにパイプで繋げばOK。 $ pv hogehoge.dump | mysql -u root fugafuga 2.04GiB 0:06:06 [5.82MiB/s] [==================================> ] 37% ETA 0:10:16 dumpの時はこんな感じ。 -s オプションでデータのサイズを指定してあげないと進捗表示してくれない。 $ mysqldump -u root hogehoge | pv -s 16K > fugafuga.dump 15.6KiB 0:00:00 [ 210KiB/s] [================================
ファイル内容:insert.sqlINSERT INTO tbl_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U001','花子','鈴木','f',21); INSERT INTO tbl_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U002','太郎','田中','m',30); INSERT INTO tbl_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U003','一郎','鈴木','m',45); INSERT INTO tbl_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U004','萌子','山口','f',18); INS
$ mysqldump -u<ユーザー> -p<パスワード> 【データベース】 【テーブル1】 【テーブル2】 ... --master-data --single-transaction > hogehoge.dump データベースの後にテーブル名を並べれば良いのですが、数が増えてくると大変です。。。そんな時はテーブルリストを作成し、それを使ってdumpを行うと簡単になります。 実践 実際のデータベースを使って試してみます。 まずはrepltestというデータベースに以下のテーブルが存在します。 $ mysql -uroot repltest -e "show tables" +--------------------+ | Tables_in_repltest | +--------------------+ | hogehoge1 | | hogehoge2 | | hogehoge
概要 今回は、既存のデータベースをエクスポートして、そのあとインポートするお話を綴ります。 ※平素よりMySQLをご利用の方には今更感溢れる内容となっております。 m(_ _)m 手順 エクスポート編 この1行でできるよ! $ mysqldump -u testuser -p testdb > export.sql [testuser] には任意のデータベースユーザー名を、 [testdb] にはエクスポートしたいデータベース名を、 [export.sql] にはエクスポートするファイル名を指定してね! Enter password: パスワードを求められたら入力してね! export.sql が出力されたかな? インポート編 インポートする前に、受け入れ先のデータベースを作っておく必要があるよ! MySQLにログイン! $ mysql -u root -p パスワードを入力してね。 En
こんにちは、下條です。今日はSQLの軽い (しかし重要な) 話題について書いてみようと思います。 まずは以下の通りユニークでない col カラムを含む test テーブルを作成し、データを投入するSQLをご覧ください。 (MySQLでの例です。) create table test(id INT, col INT); insert into test values(1,1); insert into test values(2,1); insert into test values(10,1); insert into test values(3,1); そして、以下の2つのSQLを実行した場合、結果はどうなるでしょうか?ここで何が言いたいかが分かる方はこの先は読まなくてかまいません。 select * from test order by col limit 2 offset 0; se
LOAD DATA FROM S3 AWSのMySQL互換DBであるところのAuroraにはデータをS3から直接ロードする構文が用意されている。 Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード これの使用例はクラスメソッドさんなどもブログにしているのでそこまで話題にしない。 【新機能】Amazon AuroraにS3からの直接データロード機能が追加。 SQLの構文 詳しい設定方法は上の公式ドキュメントを見てほしい。結構大変で、DB側に専用のIAMロールと、DBのパラメーターグループ、そしてアクセスするユーザにパーミッションを設定する必要がある。 そしてなんやかんやで 'S3-URI' にファイルパスを指定すれば読み込める 便利なもんですなあ LOAD DATA FROM S3 [FILE | PREFIX |
しょっちゅう忘れるのでメモ。 mysqlに切り替え USE mysql 登録されているユーザを確認 SELECT user, host FROM user; 権限を表示 SHOW GRANTS for 'hoge'@'%'; ユーザを作成 CREATE USER user_name; CREATE USER user_name IDENTIFIED BY [PASSWORD] 'password'; ※ユーザ指定の書式例 user_name@host_name ※ワイルドカードをホストに使うときはシングルクォートでくくる user_name@'%' 'username'@'192.168.128.%' 権限付与 GRANT ALL PRIVILEGES ON `DB名`.テーブル TO 'ユーザ名'@'ホスト名'; GRANT SELECT,UPDATE,INSERT,DELETE ON
CSV ファイルのデータを MySQL データベースにインポートする方法。 大量のデータを、高速に取り込むことができるという。 構文 [] に囲まれているのは省略可。 LOAD DATA LOCAL INFILE 'ファイル名' [REPLACE | IGNORE] INTO TABLE テーブル名 [FIELDS [TERMINATED BY '区切り文字'] [[OPTIONALLY] ENCLOSED BY '囲み文字'] [ESCAPED BY 'エスケープシーケンス' ] ] [LINES TERMINATED BY '行の改行文字'] [IGNORE 数値 LINES] [(@CSVの項目値を格納する変数, ...)] [SET DBのカラム名 = @CSV変数 または 任意の値, ...] REPLACE : 主キーやユニークキーが重複した場合、入力値で書き換えられる。( D
システム運用をしているとs3にデータベースのバックアップを定期にアップロードをするケースがあります。 ただ、データベースのサイズがある程度大規模になってくると、もし一旦ローカルにファイルを保存してバックアップを行う場合、そもそもサーバーにバックアップファイルを置く容量がない事態が発生してしまいがちです。 そこで、今回はローカルにファイルを作成せず直接s3にアップロードすることで、サーバーの容量を圧迫せずにバックアップ&リストアを行う方法をご紹介します。 mysqldumpをローカルにファイルを保存せずs3に保存する方法 以下コマンドで、データベースとテーブルを指定してs3の特定の場所にバックアップをアップロードできます。 本サンプルはDBの入っているサーバーローカル上での実行例です。 (圧縮方法はbzip2) mysqldump -u root db_name table_name | b
Amazon Aurora MySQL は、データを S3 にアンロード(SELECT INTO OUTFILE S3) S3 からロード(LOAD DATA FROM S3) できます。 実際にやってみます。 他のDBエンジンについては、以下の記事を参照ください。 Oracle MySQL PostgreSQL SQL Server セットアップ 構成イメージ 手順概要 RDS が S3 にアクセスするための IAM ロールを作成 DB クラスターパラメーターグループのパラメーター(aws_default_s3_role)に IAM ロールの ARN を指定 Aurora クラスターを起動。IAM ロールをアタッチ mysql クライアントから SELECT INTO OUTFILE S3/LOAD DATA FROM S3 を実行 RDS 向け IAM ロールの作成 Aurora イン
AWSに於けるビッグデータ分析環境構築に於いては、DWHであるAmazon Redshiftにデータを貯めこむ為に様々な経路からCSVやJSONファイルをS3等の環境に集約する作業が必要となります。そしてその作業の中にはオンプレ環境を始めとした外部データソースをAWSに集約するという経路がある一方で、AWS環境下にあるデータをAmazon Redshiftに投入させるために一旦ファイルとして出力させる(そしてAmazon Redshiftに投入する)という経路もあるかと思います。そこで当エントリでは、『RDS(MySQL)からAmazon Redshiftにデータを移行する際にどのような手段を取ることが出来るのか』という点について、幾つか洗い出してまとめてみたいと思います。 目次 EC2上でmysqlコマンドによるクエリ実行結果をファイルとして出力 mysqlコマンドでTSVファイルを生成
MySQLには mysqldumpというダンプツールがあります。 基本的な使い方は以下のようになります。 出力形式はSQLのCREATE文とINSERT文になりますが、オプションを指定することで、 csvや tsv形式にすることが可能です。 csv,tsv出力のオプション csv(tsv)で出力するには、 --tabと --fields-terminated-by の2つのオプションを使用します。 --tabオプション ファイルの出力先ディレクトリを指定するオプションです。 --tab=/tmpと指定すれば、/tmp以下にファイルが出力されます。 このオプションは、csv(tsv)形式で出力する場合は必須です。 --fields-terminated-byオプション 区切り文字を指定するオプションです。 デフォルトはタブになりますので、tsvファイルで出力する場合はこのオプションは不要です
挿入の速度を最適化するには、多くの小さな操作を 1 つの大きな操作に組み合わせます。理想的には、単一の接続を作成し、多くの新しい行のデータを一度に送信し、すべてのインデックスの更新と一貫性チェックを最後まで延期します。 行の挿入に必要な時間は、次の要因によって決まります。ここでの数はおよその割合を示しています。 接続: (3) サーバーへのクエリーの送信: (2) クエリーの解析: (2) 行の挿入: (1 ×行サイズ) インデックスの挿入: (1 ×インデックス数) クローズ: (1) これには、テーブルを開く初期オーバーヘッドを考慮に入れていません。これは同時実行クエリーごとに 1 回実行されます。 テーブルのサイズによって、log N だけインデックスの挿入が遅くなります (B ツリーインデックスであるとして)。 次の方法を使用して、挿入を高速化できます。 同じクライアントから同時に
よく使うやつ LOAD DATA INFILE '/tmp/foo.csv' INTO TABLE foo FIELDS TERMINATED BY ',' IGNORE 1 LINES; csvを読み込むにはFIELDS TERMINATED BY ','が必要 LOAD DATA INFILEは、タブ区切りがデフォルトになっているらしい。カンマ区切りであるcsvを読み込むにはFIELDS TERMINATED BY ','でカンマ区切りである事を指定します。 1行目にフィールド名を入れてあるので、それを無視する IGNORE 1 LINESを付けることで、先頭1行を無視することが出来ます。インポート用のcsvを作る際、フィールド名がないと分かりにくいし、事後に修正して再利用することを考えると、インポートする時だけ消すのも面倒くさいので。 主キーであるidも必要 インポートするcsvには
概要 データ抽出で調べていたらいくつか方法があるけど一つにまとまっている記事がなかったのでまとめてみました。 また、OUTFILE/INFILE使用時に注意することも補足としてまとめました。 ※mysqlを使っています。 mysqldump DB外からDBやテーブルをオプション指定して出力する ・データバックアップとか移動に使うことが多い。 参照 mysqldumpまとめ mysqldumpオプション # dumpアウトプット例:db全体(db_nameを並べれば複数可能) $ mysqldump -h 127.0.0.1 -u user_name -p -P 3306 db_name > test.sql # dumpアウトプット例:特定table(table_nameを並べれば複数可能) $ mysqldump -h 127.0.0.1 -u user_name -p -P 3306
ある別のMySQLサーバーからデータを移行する為Dumpし、そのDumpデータを他のMyDQLデータベース・サーバーに移しインポートしています。 しかし、4日間経過しますがまだインポートが終わっていません。 データ量は50GBで、レコード数は3億くらいです。 インデックスも複数貼っていますので、その生成にも時間がかかっていると思います。 そこで質問なのですが、このインポート処理を爆速で終わらせたいのですが、何か良い方法はありますでしょうか? CPUの使用率をみたら、MySQLが12%であり、アイドルCPUが80%以上あるので、できればCPUを100%に近い形で使い切りインデックス作成処理を早く終わらせたいと思っています。 インポート元先共に同じ環境: XEON-5 Memori:8GB SSD Windows Server MySQL 5.6 MyiSAM よろしくお願いいたします。
場合によっては、ライブアプリケーションをサポートする外部の MariaDB または MySQL データベースから MariaDB DB インスタンス、MySQL DB インスタンス、または MySQL マルチ AZ DB クラスターにデータをインポートする必要があります。次の手順を使用して、アプリケーションの可用性への影響を最小限に抑えることができます。この手順は、巨大なデータベースを使用する場合にも役立ちます。この手順を使用すると、ネットワーク経由で AWS に渡されるデータ量を削減することで、インポートのコストを削減できます。 この手順では、データベースデータのコピーを Amazon EC2 インスタンスに送信し、そのデータを新しい Amazon RDS データベースにインポートします。次に、レプリケーションを使用して、Amazon RDS データベースをライブ外部インスタンスで最新の
リリース、障害情報などのサービスのお知らせ
最新の人気エントリーの配信
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く